Package: bacula-director-mysql
Version: 5.2.6+dfsg-9.3
Severity: normal
Tags: upstream patch
The MySQL statements for adding full backup file list into the BaseFile
table after a full backup of lots of file (say, over a million) after
a succesfull base job can take a VERY long time (I've waited for 10 hours
with no end).
I found this to be caused by INSERT INTO BaseFiles with a non-indexed
join in db_commit_base_file_attributes_record()...
"WHERE A.Path = B.Path AND A.Name = B.Name"
...between temporary tables "basefile%lld" and "new_basefile%lld".
The attached patch corrects this by adding the appropriate indexes to those
temporary tables in sql_cmds.c.
-- System Information:
Debian Release: 7.6
APT prefers stable
APT policy: (990, 'stable'), (500, 'stable-updates'), (500, 'testing')
Architecture: amd64 (x86_64)
Kernel: Linux 3.16.3 (SMP w/4 CPU cores)
Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
Versions of packages bacula-director-mysql depends on:
ii bacula-common 5.2.6+dfsg-9.3
ii bacula-common-mysql 5.2.6+dfsg-9.3
ii bacula-director-common 5.2.6+dfsg-9.3
ii dbconfig-common 1.8.47+nmu1
ii debconf [debconf-2.0] 1.5.49
ii libc6 2.19-11
ii libcap2 1:2.22-1.2
ii libgcc1 1:4.7.2-5
ii libpython2.7 2.7.3-6+deb7u2
ii libssl1.0.0 1.0.1e-2+deb7u12
ii libstdc++6 4.9.1-15
ii libwrap0 7.6.q-24
ii mysql-client 5.5.38-0+wheezy1
ii mysql-client-5.5 [mysql-client] 5.5.38-0+wheezy1
ii ucf 3.0025+nmu3
ii zlib1g 1:1.2.7.dfsg-13
Versions of packages bacula-director-mysql recommends:
ii mysql-server 5.5.38-0+wheezy1
Versions of packages bacula-director-mysql suggests:
ii gawk 1:4.0.1+dfsg-2.1
-- debconf information excluded
--- bacula-5.2.6+dfsg.orig/src/cats/sql_cmds.c
+++ bacula-5.2.6+dfsg/src/cats/sql_cmds.c
@@ -465,7 +465,8 @@
/* Mysql */
"CREATE TEMPORARY TABLE basefile%lld ("
"Path BLOB NOT NULL,"
- "Name BLOB NOT NULL)",
+ "Name BLOB NOT NULL,"
+ "INDEX(Path(255), Name(255)) )",
/* Postgresql */
"CREATE TEMPORARY TABLE basefile%lld ("
@@ -486,7 +487,7 @@
const char *create_temp_new_basefile[] = {
/* Mysql */
- "CREATE TEMPORARY TABLE new_basefile%lld AS "
+ "CREATE TEMPORARY TABLE new_basefile%lld (INDEX (Path(255), Name(255))) AS "
"SELECT Path.Path AS Path, Filename.Name AS Name, Temp.FileIndex AS FileIndex,"
"Temp.JobId AS JobId, Temp.LStat AS LStat, Temp.FileId AS FileId, "
"Temp.MD5 AS MD5 "
