[Bacula-users] *Extreme* low MySQL performance when prunnig or purging jobs/volumes

[Volker S. <vo...@vo...>]

Hello all,

this might be more a mysql issue than a bacula issue but I'l ask this
list anyway:

I'm running bacula 1.36.2-2sarge1 on Debian Sarge. I'm using mysql
4.0.24-10sarge1. I'm backing up about 20 machines and the database is
grown quite large since I'm covering about half a year of backup
history. The database is now 3.0G in size where the File Table is:

-rw-rw----  1 mysql mysql 2.3G Oct 25 11:32 File.MYD
-rw-rw----  1 mysql mysql 503M Oct 25 11:34 File.MYI
-rw-rw----  1 mysql mysql 8.6K Oct 21 20:04 File.frm

The problem is easy to desribe: it takes about 5 to 10 hours (!) to run
a prune or purge command. Since auto-prune is on, sometimes the bconsole
locks up for a day since bacula runs a SQL DELETE on mysql. Like so:

mysql> show processlist;
+----+--------+-----------+--------+---------+------+----------+-----------=
------------------------+
| Id | User   | Host      | db     | Command | Time | State    | Info
|
+----+--------+-----------+--------+---------+------+----------+-----------=
------------------------+
| 64 | bacula | localhost | bacula | Query   | 31   | updating | DELETE
FROM File WHERE JobId=3D3545 |
| 65 | root   | localhost | NULL   | Query   | 0    | NULL     | show
processlist                  |
+----+--------+-----------+--------+---------+------+----------+-----------=
------------------------+
2 rows in set (0.00 sec)


Is this normal? Is my database missing an index or so??

Regards
Volker
--=20
  Volker Sauer  *  Alexanderstrasse 39/217  *  64283 Darmstadt
  Telefon: 06151-154260  *  Mobil: 0179-6901475 * ICQ#98164307
  mailto:vo...@vo...  *  http://www.volker-sauer.de
  PGPKey-Fingerprint: DB2611C7B12E0B2739992E4F7E354E4D5DD5D0E0