View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000837bareos-coredirectorpublic2017-07-20 22:172019-12-18 15:25
Reporterjoergs Assigned Tostephand  
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Product Version16.2.6 
Summary0000837: performance issues with .bvfs_lsdirs while having a large number of directories
DescriptionIn 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 InformationWorkaround:

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
TagsNo tags attached.

Relationships

related to 0000848 closedstephand Can't restore anything, blank page in webui 
child of 0000836 closedjoergs Release bareos-16.2.7 

Activities

stephand

stephand

2017-10-09 16:34

developer   ~0002783

Fix committed to bareos bareos-16.2 branch with changesetid 7279.

Related Changesets

bareos: bareos-16.2 fbebd276

2017-09-29 17:28

stephand

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

Issue History

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