Martin,
Thanks for your comments. You point out exactly the kinds of changes
that get us into trouble.
Hopefully Phil will integrate them into his submission ...
Best regards,
Kerrn
On 6/25/19 11: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.
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
_______________________________________________
Bacula-devel mailing list
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
