>>>>> On Mon, 24 Jun 2019 18:33:10 -0400, Phil Stracchino said: > > The current source has Version 16. My current database is version 16. > It's been fully updated to current. The version 16 make_mysql_tables > file creates a PathHeirarchy table. My schema doesn't contain it, and > neither any of the updatedb/update_mysql_tables_* scripts nor > src/cats/update_mysql_tables creates it. > > Should it even exist? Is it a table for Enterprise-only functionality? > Is it a table for planned FUTURE functionality? If it *should* exist, > shouldn't update_mysql_tables create it?
PathHierarchy seems to be used in bacula/src/cats/bvfs.c and was created by bacula/updatedb/update_mysql_tables_11_to_12.in. PathHeirarchy looks like a typo. > Next, and what spurred this: I was doing routine performance checks on > my DB and noticed that the Bacula schema still has several BLOB columns, > most of them tiny. This is generally a bad idea in MySQL, because it > forces temporary tables to disk no matter how small, because the MEMORY > storage engine does not support BLOB or TEXT types. Are MySQL's temporary tables just something related CREATE TEMPORARY TABLE or are they created by queries? Also, you should look in the sources for uses of CREATE TEMPORARY TABLE (i.e. those that are not part of the permanent schema). > Now, I've modified my schemas a long time ago to replace all but a very > few of the BLOB columns with VARBINARY. (VARBINARY is to VARCHAR as > BLOB is to TEXT.) In general, I have replaced TINYBLOBs with either > varbinary(32), varbinary(64), or varbinary(255); there is one > varchar(100) and one varbinary(4096). I have tried to err on the side > of more space than needed, if in doubt. > > There's a couple of BLOBs left that I haven't touched, two in > RestoreObject, one in Log. The one in Log I have left alone because it > clearly could be long. The two in RestoreObject I have left alone > because I don't know what they're for and don't know what the lengths > might be and what the impact might be. > > (There's also just a few indexing changes, most of them resulting from > changing TINYBLOBs to VARBINARYs.) > > > I have been running with these changes for over a year and have not seen > any resulting problems. I HAVE seen improved database performance due > to almost completely eliminating on-disk temporary tables. > > > Comments welcomed. Some of your changes are related to the configuration. E.g. I have max(length(Job.Job)) = 55, which is quite close to your limit of 64. Also, there are few cases in https://en.wikipedia.org/wiki/Comparison_of_file_systems#Limits that are larger than your limits on Filename.Name and Path.Name. __Martin _______________________________________________ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
