On 5/27/23 14:22, Phil Stracchino wrote:
So first create a new empty table:
CREATE TABLE `File_NEW` (
`FileId` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`FileIndex` int(11) DEFAULT 0,
`JobId` int(10) unsigned NOT NULL,
`PathId` int(10) unsigned NOT NULL,
`Filename` blob NOT NULL,
`DeltaSeq` smallint(5) unsigned DEFAULT 0,
`MarkId` int(10) unsigned DEFAULT 0,
`LStat` tinyblob NOT NULL,
`MD5` tinyblob DEFAULT NULL,
PRIMARY KEY (`FileId`)
) ENGINE=InnoDB;
Incidentally ... this table schema is not ideal, and yours is missing
indexes in any case. This is what a Bacula 13.0.3 File table SHOULD
look like, out of the box:
CREATE TABLE File (
FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
FileIndex INTEGER DEFAULT 0,
JobId INTEGER UNSIGNED NOT NULL,
PathId INTEGER UNSIGNED NOT NULL,
Filename BLOB NOT NULL,
DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
MarkId INTEGER UNSIGNED DEFAULT 0,
LStat TINYBLOB NOT NULL,
MD5 TINYBLOB,
PRIMARY KEY(FileId),
INDEX (JobId),
INDEX (JobId, PathId, Filename(255))
);
I have made a few changes to mine which *significantly* improve
performance without any impact detected so far (in years of use) on
operation. This is what my File table now looks like:
CREATE TABLE `File` (
`FileId` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`FileIndex` int(11) DEFAULT 0,
`JobId` int(10) unsigned NOT NULL,
`PathId` int(10) unsigned NOT NULL,
`Filename` varbinary(255) NOT NULL,
`DeltaSeq` smallint(5) unsigned DEFAULT 0,
`MarkId` int(10) unsigned DEFAULT 0,
`LStat` varbinary(80) NOT NULL,
`MD5` varbinary(130) NOT NULL,
PRIMARY KEY (`FileId`),
KEY `JobId_2` (`JobId`,`PathId`,`Filename`),
KEY `PathId` (`PathId`)
) ENGINE=InnoDB
It's up to you whether you want to try this modification. You can
always modify the table to this spec later. (And I can give you the
rest of the set of schema changes, if you want.)
The change from BLOB columns to VARBINARY greatly increases performance
of JOINS, because the MEMORY storage engine does not support BLOB/TEXT
data types, and as a result any implicit temporary table created by a
JOIN which contains BLOB/TEXT tables will always be forced to disk. It
also avoids the need to put a range constraint on Filename in the second
index.
VARBINARY behaves exactly like BLOB in all important respects except
that it IS supported in the MEMORY storage engine.
--
Phil Stracchino
Babylon Communications
ph...@caerllewys.net
p...@co.ordinate.org
Landline: +1.603.293.8485
Mobile: +1.603.998.6958
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
