View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000837 | bareos-core | director | public | 2017-07-20 22:17 | 2019-12-18 15:25 |
| Reporter | joergs | Assigned To | stephand | ||
| Priority | normal | Severity | minor | Reproducibility | always |
| Status | closed | Resolution | fixed | ||
| Product Version | 16.2.6 | ||||
| Summary | 0000837: performance issues with .bvfs_lsdirs while having a large number of directories | ||||
| Description | In Bareos 16.2.6 the SQL Query used by .bvfs_lsdirs was changed to not show empty directories from accurate jobs. It turned out that that the changed query causes performance issues when larger amounts of directories were backed up. | ||||
| Additional Information | Workaround: When using PostgreSQL, creating the following partial improves the performance sufficiently: CREATE INDEX file_jfnidpart_idx ON File(JobId, FilenameId) WHERE FileIndex = 0; Run su - postgres -c 'echo "CREATE INDEX file_jfnidpart_idx ON File(JobId, FilenameId) WHERE FileIndex = 0; ANALYZE File;" | psql bareos' to create the index. When using MySQL or MariaDB, creating the following index improves the performance: CREATE INDEX PathId_JobId_FileIndex_FileNameId ON File(PathId,JobId,FileIndex,FilenameId); Run echo "CREATE INDEX PathId_JobId_FileIndex_FileNameId ON File(PathId,JobId,FileIndex,FilenameId);" | mysql -u root bareos to create the index. However, with larger amounts of directories and/or involved jobs, even with this index the performance of .bvfs_lsdirs may still be insufficient. We are working on optimizing the SQL query for MySQL/MariaDB to solve this problem. See http://doc.bareos.org/master/html/bareos-manual-main-reference.html#bareos-16.2.6 | ||||
| Tags | No tags attached. | ||||
|
bareos: bareos-16.2 fbebd276 2017-09-29 17:28 Ported: N/A Details Diff |
Improve .bvfs_lsdirs performance In a previous fix, the the SQL query used by .bvfs_lsdirs was changed to not show empty dirs from accurate jobs. This introduced performance problems when .bvfs_lsdirs was used with larger amounts (hundreds) of folders. This commit changes the SQL query to perform better when using MySQL. Note that it is also necesary to create the following index when using MySQL: CREATE INDEX PathId_JobId_FileNameId_FileIndex ON File(PathId,JobId,FilenameId,FileIndex); When using PostgreSQL create the following partial index: CREATE INDEX file_jpfnidpart_idx ON File(PathId,JobId,FilenameId) WHERE FileIndex = 0; Previously, the SQL Scripts for creating the tables added a single-key index for JobId on the File table. Such an index is not necesary if another multi-key index exists with JobId as the first key. As the number of indexes has a performance impact on INSERT and UPATE, it is a good idea to drop the single-key JobId index when adding the above index. Fixes 0000837: performance issues with .bvfs_lsdirs while having a large number of directories |
Affected Issues 0000837 |
|
| mod - src/cats/bvfs.c | Diff File | ||
| mod - src/cats/ddl/creates/mysql.sql | Diff File | ||
| mod - src/cats/ddl/creates/postgresql.sql | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2017-07-20 22:17 | joergs | New Issue | |
| 2017-07-20 22:17 | joergs | Status | new => assigned |
| 2017-07-20 22:17 | joergs | Assigned To | => stephand |
| 2017-07-20 22:17 | joergs | Relationship added | child of 0000836 |
| 2017-07-20 22:18 | joergs | Additional Information Updated | |
| 2017-09-15 15:55 | stephand | Relationship added | related to 0000848 |
| 2017-10-09 16:34 | stephand | Changeset attached | => bareos bareos-16.2 fbebd276 |
| 2017-10-09 16:34 | stephand | Note Added: 0002783 | |
| 2017-10-09 16:34 | stephand | Status | assigned => resolved |
| 2017-10-09 16:34 | stephand | Resolution | open => fixed |
| 2019-12-18 15:25 | arogge | Status | resolved => closed |
