Here's my points/suggestions on your server setup (I'll leave the bacula 
SQL code observations to the bacula developers)...

In order of what I would try:

1) put mysql-temp and the bacula tables on separate storage. Have that also 
separate from bacula's temp if possible--I use raid-0 for the spool volume. 
  (i.e. SpoolDirectory in the Device section of bacula-sd.conf)

2) on unix, 64 bit OS stuff is kinda the same as 32 bit until you get 
beyond 4 GB of ram, so you may want to consider adding some to take it to 
8GB or more.  You can also use 'top' and other commands to monitor how much 
RAM it's using and adjust the tuning parameters for it.

3) You may want to tune mysql parameters some but I'm no mysql expert.  If 
you add ram, some of the mysql parameters will need to be adjusted to take 
advantage of it.  Even if you don't change mysql parameters at all, the 
extra memory will go into disk cache which may help a lot.

4) That server is dual processor capable so you may want to consider adding 
a 2nd CPU.  Have you monitored the load avg values for the server?  That 
may give you some indication of whether you're lacking CPU power or hitting 
some other bottleneck like memory/disk/or just plane io.  There are other 
utilities to help with this too.

I spool everything and then go to tape w/o any disk/file pools so your 
situation is quite different than mine even though we're both running 
bacula on a PowerEdge 2900 with 64 bit linux.  Have you considered 
upgrading to LTO-2 or better?  If you ever have to get all 5 TB off tape at 
LTO-1 speeds you're probably going to be under a lot of stress.

     Bob

> Hi !
> 
> TL;DR
> When backing up / migrating clients with a high volume of files the
> performance drops to a very low level. This can be traced back to the
> Database inserts.
> 
> Our server:
> Dell PE2900
> 4-Core Xeon 2GHz, 4GB Ram
> Red Hat Enterprise Linux 5.3 64-Bit
> 2.6.18-128.1.1.el5 #1 SMP Mon Jan 26 13:58:24 EST 2009 x86_64 x86_64
> x86_64 GNU/Linux
> PERC 5/i   - 200GB SAS Raid-5 (System + Database)
> PERC 4e/DC - 2x 350GB Raid-5 Disk Enclosure (MySQL-Temp & customer Data)
> LSI1020    - 4x LTO-1 Streamer
> QLA200     - 5 TB Fibre-Channel SAN with SATA Drives (Bacula Storage)
> 
> Bacula 2.4.4 - batch insert enabled
> MySQL 5.0.45


------------------------------------------------------------------------------
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users