[pkg-bacula-devel] Bug#765680: bacula-director-mysql: Extremely slow MySQL insert after full backup with large BaseJobs

[Jarno Elonen]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: add-mysql-basefile-idx.patch
Type: text/x-diff
Size: 744 bytes
Desc: not available
URL: <http://lists.alioth.debian.org/pipermail/pkg-bacula-devel/attachments/20141017/107e6b20/attachment.patch>