On 6/25/19 5:29 AM, Martin Simmons wrote: >>>>>> 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.
Ha! indeed it was. My error. Ignore that question... >> 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? MySQL has both explicit temporary tables created by CREATE TEMPORARY TABLE, and implicit temporary tables created during JOINs and sub-SELECTs. By default, the MEMORY storage engine is used for temporary tables wherever possible. However, a single BLOB or TEXT column in a temporary table will force the table to disk, even if it contains only a single row. >> 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. Some of those I was not aware of. I do observe that several of the FAT variants are listed as "No limit defined", but I know from experience that older FAT versions break shockingly quickly if you start recursively creating directories. I'm pretty certain I remember FAT16 breaking at 255 characters PATH length. I'm not sure whether to be amused or aghast at the idea of trying to use a FAT-based filesystem on a 64ZB device. I figured some of the larger fields might need to remain BLOB, but a lot of the smaller ones are BLOB for no sound reason. Replacing as many of the smaller ones as possible with VARBINARY will significantly reduce database forced disk I/O. -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: +1.603.293.8485 Mobile: +1.603.998.6958 _______________________________________________ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
