bareos: bareos-16.2 fbebd276

Diff Back to Repository
Author Committer Branch Timestamp Parent
stephand stephand bareos-16.2 2017-09-29 17:28 bareos-16.2 6cccbd46 Pending
Affected Issues  0000837: performance issues with .bvfs_lsdirs while having a large number of directories
Changeset 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