Bareos Bug Tracker
Bareos Bug Tracker

View Issue Details Jump to Notes ] Related Changesets ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0000837bareos-core[All Projects] directorpublic2017-07-20 22:172017-10-09 16:34
Reporterjoergs 
Assigned Tostephand 
PrioritynormalSeverityminorReproducibilityalways
StatusresolvedResolutionfixed 
PlatformOSOS Version
Product Version16.2.6 
Target Version16.2.7Fixed in Version 
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.
bareos-master: impactyes
bareos-master: actionwill care
bareos-17.2: impact
bareos-17.2: action
bareos-16.2: impactyes
bareos-16.2: actionwill care
bareos-15.2: impactno
bareos-15.2: action
bareos-14.2: impactno
bareos-14.2: action
bareos-13.2: impactno
bareos-13.2: action
bareos-12.4: impactno
bareos-12.4: action
Attached Files

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

-  Notes
(0002783)
stephand (developer)
2017-10-09 16:34

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

- Related Changesets
bareos: bareos-16.2 fbebd276
Timestamp: 2017-09-29 15:28:53
Author: 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
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 bareos-master: impact => yes
2017-07-20 22:18 joergs bareos-master: action => will care
2017-07-20 22:18 joergs bareos-16.2: impact => yes
2017-07-20 22:18 joergs bareos-16.2: action => will care
2017-07-20 22:18 joergs bareos-15.2: impact => no
2017-07-20 22:18 joergs bareos-14.2: impact => no
2017-07-20 22:18 joergs bareos-13.2: impact => no
2017-07-20 22:18 joergs bareos-12.4: impact => no
2017-07-20 22:18 joergs Additional Information Updated View Revisions
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


Copyright © 2000 - 2017 MantisBT Team
Powered by Mantis Bugtracker