Hello,
On 25.10.2005 11:39, Volker Sauer wrote:
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:
Hmm. You didn't tell us what server you have behind your cataog
database, and if that machine is usually loaded or not.
Anyway, for reference: I've got an Athlon 500 MHz with 512 MB RAM
working as my all-purpose server. That machines load is usually less
than one, this weeks average was 0.6, for example.
The Bacula catalog File table is a little smaller than yours: 1.3 GB
data, >13500000 rows, and about 800 MB indexes.
Pruning / recycling is also automatic here, but usually takes less than
15 minutes. I haven't any reports with pruning in them to give more
exact times at the moment. Anyway, the DIR never was busier longer than
I expected.
mysql> show processlist;
+----+--------+-----------+--------+---------+------+----------+-----------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+----+--------+-----------+--------+---------+------+----------+-----------------------------------+
| 64 | bacula | localhost | bacula | Query | 31 | updating | DELETE
FROM File WHERE JobId=3545 |
| 65 | root | localhost | NULL | Query | 0 | NULL | show
processlist |
+----+--------+-----------+--------+---------+------+----------+-----------------------------------+
2 rows in set (0.00 sec)
Well, 31 seconds is not exactly a long time.
If it's busy for a really long time, you could analyze the query
mysqladmin reports. That should give yo a clue if you understand the
output of the explain SQL command, which I don't do very good :-)
Perhaps an "analyze table File" also helps, if the indexes distribution
MySQL knows about is not representing the actual table data. (Note that
this command can take really long, and the table in question is locked
read-only.)
Apart from that, make sure that the server isn't overloaded and all the
other stuff you probably know about already.
Is this normal? Is my database missing an index or so??
Well, a "show index from File" gives me 7 result rows, representing 5
indexes.
Arno
Regards
Volker
--
IT-Service Lehmann [EMAIL PROTECTED]
Arno Lehmann http://www.its-lehmann.de
-------------------------------------------------------
This SF.Net email is sponsored by the JBoss Inc.
Get Certified Today * Register for a JBoss Training Course
Free Certification Exam for All Training Attendees Through End of 2005
Visit http://www.jboss.com/services/certification for more information
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
