Hello Phil, On 5/21/21 15:49, Phil Stracchino wrote: > On 5/21/21 3:19 AM, Eric Bollengier via Bacula-devel wrote: > > 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;
We support all kind of MySQL/MariaDB versions, can you confirm that this kind of things will work on all reasonable versions? > 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. Interesting > 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. I believe that it's a question of disk space, the indexes on the File table can be very big, and it's a best practice to have enough space, but we can help users a little bit. > > 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. This is a good news, I would recommend to run some tests, then, if the feedback is good, we can drop it (or advise to drop it). > 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. Thanks for the tips, they are very good. if you can provide a diff of the update_mysql_tables.in script, I will apply it. Best Regards, Eric _______________________________________________ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
