First up:
Backup to CD has long since been abandoned and, if I'm not mistaken,
*removed*. Why does the Bacula schema still contain a CDImages table?
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?
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.
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.
--
Phil Stracchino
Babylon Communications
ph...@caerllewys.net
p...@co.ordinate.org
Landline: +1.603.293.8485
Mobile: +1.603.998.6958
--- src/cats/make_mysql_tables 2019-06-01 15:29:06.554740304 -0400
+++ src/cats/make_mysql_tables.new 2019-06-24 18:25:27.055863876 -0400
@@ -30,20 +30,20 @@
-- sensitive in sorts, which is what we want, and TEXT
-- is case insensitive.
--
CREATE TABLE Filename (
FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- Name BLOB NOT NULL,
+ Name varbinary(255) NOT NULL,
PRIMARY KEY(FilenameId),
INDEX (Name(255))
);
CREATE TABLE Path (
PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- Path BLOB NOT NULL,
- PRIMARY KEY(PathId),
- INDEX (Path(255))
+ Path varbinary (4096) NOT NULL,
+ PRIMARY KEY (PathId),
+ UNIQUE KEY (Path(255))
);
-- We strongly recommend to avoid the temptation to add new indexes.
-- In general, these will cause very significant performance
-- problems in other areas. A better approch is to carefully check
@@ -64,22 +64,23 @@
JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
MarkId INTEGER UNSIGNED DEFAULT 0,
- LStat TINYBLOB NOT NULL,
- MD5 TINYBLOB,
+ LStat varchar(128) NOT NULL,
+ MD5 varbinary(32) NOT NULL,
PRIMARY KEY(FileId),
- INDEX (JobId),
- INDEX (JobId, PathId, FilenameId)
+ INDEX `FilenameId` (`FilenameId`),
+ INDEX`JobId` (`JobId`,`PathId`,`FilenameId`),
+ INDEX `PathId` (`PathId`,`FilenameId`,`FileIndex`)
);
CREATE TABLE RestoreObject (
RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
ObjectName BLOB NOT NULL,
RestoreObject LONGBLOB NOT NULL,
- PluginName TINYBLOB NOT NULL,
+ PluginName varbinary(255) NOT NULL,
ObjectLength INTEGER DEFAULT 0,
ObjectFullLength INTEGER DEFAULT 0,
ObjectIndex INTEGER DEFAULT 0,
ObjectType INTEGER DEFAULT 0,
FileIndex INTEGER DEFAULT 0,
@@ -99,25 +100,25 @@
# INDEX (FilenameId),
#
CREATE TABLE MediaType (
MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- MediaType TINYBLOB NOT NULL,
+ MediaType varbinary(32) NOT NULL,
ReadOnly TINYINT DEFAULT 0,
PRIMARY KEY(MediaTypeId)
);
CREATE TABLE Storage (
StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- Name TINYBLOB NOT NULL,
+ Name varbinary(64) NOT NULL,
AutoChanger TINYINT DEFAULT 0,
PRIMARY KEY(StorageId)
);
CREATE TABLE Device (
DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- Name TINYBLOB NOT NULL,
+ Name varbinary(64) NOT NULL,
MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
DevMounts INTEGER UNSIGNED DEFAULT 0,
DevReadBytes BIGINT UNSIGNED DEFAULT 0,
DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
@@ -133,12 +134,12 @@
);
CREATE TABLE Job (
JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- Job TINYBLOB NOT NULL,
- Name TINYBLOB NOT NULL,
+ Job varbinary(64) NOT NULL,
+ Name varbinary(64) NOT NULL,
Type BINARY(1) NOT NULL,
Level BINARY(1) NOT NULL,
ClientId INTEGER DEFAULT 0 REFERENCES Client,
JobStatus BINARY(1) NOT NULL,
SchedTime DATETIME DEFAULT NULL,
@@ -158,23 +159,24 @@
PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
PurgedFiles TINYINT DEFAULT 0,
HasBase TINYINT DEFAULT 0,
HasCache TINYINT DEFAULT 0,
Reviewed TINYINT DEFAULT 0,
- Comment BLOB,
+ Comment varchar(255) DEFAULT NULL,
FileTable CHAR(20) DEFAULT 'File',
PRIMARY KEY(JobId),
INDEX (Name(128)),
+ INDEX (StartTime),
INDEX (JobTDate)
);
-- Create a table like Job for long term statistics
CREATE TABLE JobHisto (
JobId INTEGER UNSIGNED NOT NULL,
- Job TINYBLOB NOT NULL,
- Name TINYBLOB NOT NULL,
+ Job varbinary(64) NOT NULL,
+ Name varbinary(64) NOT NULL,
Type BINARY(1) NOT NULL,
Level BINARY(1) NOT NULL,
ClientId INTEGER DEFAULT 0,
JobStatus BINARY(1) NOT NULL,
SchedTime DATETIME DEFAULT NULL,
@@ -194,29 +196,29 @@
PriorJobId INTEGER UNSIGNED DEFAULT 0,
PurgedFiles TINYINT DEFAULT 0,
HasBase TINYINT DEFAULT 0,
HasCache TINYINT DEFAULT 0,
Reviewed TINYINT DEFAULT 0,
- Comment BLOB,
+ Comment varchar(255) DEFAULT NULL,
FileTable CHAR(20) DEFAULT 'File',
INDEX (JobId),
INDEX (StartTime),
INDEX (JobTDate)
);
CREATE TABLE Location (
LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- Location TINYBLOB NOT NULL,
+ Location varbinary(64) NOT NULL,
Cost INTEGER DEFAULT 0,
Enabled TINYINT,
PRIMARY KEY(LocationId)
);
CREATE TABLE LocationLog (
LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Date DATETIME DEFAULT NULL,
- Comment BLOB NOT NULL,
+ Comment varchar(255) DEFAULT NULL,
MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
NewEnabled TINYINT,
@@ -225,12 +227,12 @@
#
CREATE TABLE FileSet (
FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- FileSet TINYBLOB NOT NULL,
- MD5 TINYBLOB,
+ FileSet varbinary(64) NOT NULL,
+ MD5 varbinary(32),
CreateTime DATETIME DEFAULT NULL,
PRIMARY KEY(FileSetId)
);
CREATE TABLE JobMedia (
@@ -249,14 +251,14 @@
);
CREATE TABLE Media (
MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- VolumeName TINYBLOB NOT NULL,
+ VolumeName varbinary(32) NOT NULL,
Slot INTEGER DEFAULT 0,
PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
- MediaType TINYBLOB NOT NULL,
+ MediaType varbinary(32) NOT NULL,
MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
LabelType TINYINT DEFAULT 0,
FirstWritten DATETIME DEFAULT NULL,
LastWritten DATETIME DEFAULT NULL,
LabelDate DATETIME DEFAULT NULL,
@@ -298,20 +300,20 @@
LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
RecycleCount INTEGER UNSIGNED DEFAULT 0,
InitialWrite DATETIME DEFAULT NULL,
ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
- Comment BLOB,
+ Comment varchar(255) default NULL,
PRIMARY KEY(MediaId),
- UNIQUE (VolumeName(128)),
+ UNIQUE (VolumeName),
INDEX (PoolId),
INDEX (StorageId)
);
CREATE TABLE Pool (
PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- Name TINYBLOB NOT NULL,
+ Name varbinary(64) NOT NULL,
NumVols INTEGER UNSIGNED DEFAULT 0,
MaxVols INTEGER UNSIGNED DEFAULT 0,
UseOnce TINYINT DEFAULT 0,
UseCatalog TINYINT DEFAULT 0,
AcceptAnyVolume TINYINT DEFAULT 0,
@@ -324,31 +326,31 @@
AutoPrune TINYINT DEFAULT 0,
Recycle TINYINT DEFAULT 0,
ActionOnPurge TINYINT DEFAULT 0,
PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
LabelType TINYINT DEFAULT 0,
- LabelFormat TINYBLOB,
+ LabelFormat varbinary(255) DEFAULT NULL,
Enabled TINYINT DEFAULT 1,
ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
MigrationTime BIGINT UNSIGNED DEFAULT 0,
- UNIQUE (Name(128)),
+ UNIQUE (Name),
PRIMARY KEY (PoolId)
);
CREATE TABLE Client (
ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
- Name TINYBLOB NOT NULL,
- Uname TINYBLOB NOT NULL, /* full uname -a of client */
+ Name varbinary(64) NOT NULL,
+ Uname varbinary(255) NOT NULL, /* full uname -a of client */
AutoPrune TINYINT DEFAULT 0,
FileRetention BIGINT UNSIGNED DEFAULT 0,
JobRetention BIGINT UNSIGNED DEFAULT 0,
- UNIQUE (Name(128)),
+ UNIQUE (Name),
PRIMARY KEY(ClientId)
);
CREATE TABLE Log (
LogId INTEGER UNSIGNED AUTO_INCREMENT,
@@ -364,31 +366,29 @@
BaseId BIGINT UNSIGNED AUTO_INCREMENT,
BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
FileId BIGINT UNSIGNED NOT NULL REFERENCES File,
FileIndex INTEGER DEFAULT 0,
- PRIMARY KEY(BaseId)
+ PRIMARY KEY(BaseId),
+ INDEX basefiles_jobid_idx (JobId)
);
-CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
-
CREATE TABLE UnsavedFiles (
UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
PRIMARY KEY (UnsavedId)
);
-
CREATE TABLE Counters (
- Counter TINYBLOB NOT NULL,
+ Counter varbinary(64) NOT NULL,
\`MinValue\` INTEGER DEFAULT 0,
\`MaxValue\` INTEGER DEFAULT 0,
CurrentValue INTEGER DEFAULT 0,
- WrapCounter TINYBLOB NOT NULL,
+ WrapCounter varbinary(255) NOT NULL,
PRIMARY KEY (Counter(128))
);
CREATE TABLE CDImages (
MediaId INTEGER UNSIGNED NOT NULL,
@@ -396,11 +396,11 @@
PRIMARY KEY (MediaId)
);
CREATE TABLE Status (
JobStatus CHAR(1) BINARY NOT NULL,
- JobStatusLong BLOB,
+ JobStatusLong varchar(100),
Severity INT,
PRIMARY KEY (JobStatus)
);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
@@ -441,35 +441,32 @@
(
PathId integer NOT NULL,
JobId integer UNSIGNED NOT NULL,
Size int8 DEFAULT 0,
Files int4 DEFAULT 0,
- CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
+ CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId),
+ INDEX pathvisibility_jobid (JobId)
);
-CREATE INDEX pathvisibility_jobid
- ON PathVisibility (JobId);
CREATE TABLE Snapshot (
SnapshotId INTEGER UNSIGNED AUTO_INCREMENT,
- Name TINYBLOB NOT NULL,
+ Name varbinary(64) NOT NULL,
JobId INTEGER UNSIGNED DEFAULT 0,
FileSetId INTEGER UNSIGNED DEFAULT 0,
CreateTDate BIGINT NOT NULL,
CreateDate DATETIME NOT NULL,
ClientId INTEGER UNSIGNED DEFAULT 0,
- Volume TINYBLOB NOT NULL,
- Device TINYBLOB NOT NULL,
- Type TINYBLOB NOT NULL,
+ Volume varbinary(64) NOT NULL,
+ Device varbinary(64) NOT NULL,
+ Type varbinary(64) NOT NULL,
Retention INTEGER DEFAULT 0,
- Comment BLOB,
- primary key (SnapshotId)
+ Comment varbinary(255) DEFAULT NULL,
+ PRIMARY KEY (SnapshotId),
+ UNIQUE INDEX snapshot_idx (Device, Volume, Name)
);
-CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device(255),
- Volume(255),
- Name(255));
CREATE TABLE Version (
VersionId INTEGER UNSIGNED NOT NULL
_______________________________________________
Bacula-devel mailing list
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
