On 5/21/21 3:19 AM, Eric Bollengier via Bacula-devel wrote:
> Hello Phil,
>
> Thanks for the suggestions. I'm almost ready to apply them
> to the current update script.
>
> The only point that worries me is that if the break is done
> during the drop of the Filename/File tables or during the
> rename of the file_temp table, then if you restart the script
> with your modification, all the data is lost and you need
> to restore the catalog.
Hi Eric,
I actually looked at it further and made additional changes to mine that
resolve that problem. Where the current script does this:
DROP TABLE Filename;
DROP TABLE File;
ALTER TABLE file_temp RENAME TO File;
what we SHOULD be doing is this:
RENAME TABLE File to File_old, file_temp to File;
DROP TABLE File_old;
DROP TABLE Filename;
RENAME TABLE is an atomic operation in MySQL. It either performs ALL of
the specified table rename operations or none. If any rename cannot be
performed, the entire operation is cancelled.
There's also no point in dropping the indexes from the original File
table AT ALL; it doesn't speed up the copy, may slow it down, and we're
about to drop the entire table anyway.
When we create the indexes on the new File table:
CREATE INDEX JobId on File (JobId);
CREATE INDEX JobId_2 on File (JobId, PathId, Filename(255));
First, this could be done as a single operation. But second, those two
indexes are redundant anyway. JobId_2 can be used to resolve any query
resolvable with just JobId, so the JobId index is not needed and just
adds index maintenance overhead. Why maintain two indexes where you
only need one? Scanning the second index is no slower than scanning the
first; mysqld descends only as far into a compound index as it needs to
in order to resolve the query.
There's a lot of places where the script performs multiple ALTER
operations that could be done faster and more efficiently as one. For
example,
ALTER TABLE File ADD PRIMARY KEY (FileId);
ALTER TABLE File MODIFY COLUMN FileId BIGINT UNSIGNED NOT NULL
AUTO_INCREMENT;
CREATE INDEX JobId on File (JobId);
CREATE INDEX JobId_2 on File (JobId, PathId, Filename(255));
can be replaced by:
ALTER TABLE File
ADD PRIMARY KEY (FileId),
MODIFY COLUMN FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD INDEX JobId (JobId),
ADD INDEX JobId_2 (JobId, PathId, Filename(255));
and
ALTER TABLE UnsavedFiles DROP COLUMN FilenameId;
ALTER TABLE UnsavedFiles ADD COLUMN Filename BLOB NOT NULL;
can be replaced by
ALTER TABLE UnsavedFiles
DROP COLUMN FilenameId,
ADD COLUMN Filename BLOB NOT NULL;
If you have multiple changes to apply to the same table, it is
invariably quicker to apply them in a single ALTER operation that makes
a single pass through the table.
--
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
