View Revisions: Issue #837

Summary 0000837: performance issues with .bvfs_lsdirs while having a large number of directories
Revision 2017-07-20 22:18 by joergs
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
Revision 2017-07-20 22:17 by joergs
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.