Ilya Ruprecht wrote:
> Hi all!
> 
> Following problem that i am running in since a couple of months -
> the File-Table grew very big - approx 14 GB. The File-Index is about 10GB big.
> In the File-Table are now about 110 million rows.
> (4 weeks retention period of the IMAP-Server do the main part; about 15 
> millions
> emails on the server. 1 Full backup every week makes 60 millions. +
> All the other servers and stuff).
> 
> The data volume that is backuped by full's is about 8 TB big and fits
> on 5 tapes.
> And the problem is now that my full's are starting at friday 21:00 and
> go till Tuesday 18:00 or so.
> Absolutely unacceptable long...
> The main time is consumed by mysql; after the Files are spooled to
> tape, it takes as good as all the time to do
> 
> "INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat,
> MD5)SELECT batch.FileIndex, batch.Job ..." etc.
> 
> The Index in JobID and PathID in File-Table do exists.
> 
> 
> The hardware used is:
> 
> Pentium D CPU 2.80GHz Dual
> 1GB RAM
> about 5 TB hdd-space (external SATA2-array, Manuf. is "EasyRAID")
> 
> OS is: Debian Lenny
> 
> The server have a max. load of 3 to 4 while doing backups.
> 
> Here the "active" part of my my.cnf
> -------------
> [mysqld]
> #
> # * Basic Settings
> #
> user            = mysql
> pid-file        = /var/run/mysqld/mysqld.pid
> socket          = /var/run/mysqld/mysqld.sock
> port            = 3306
> basedir         = /usr
> datadir         = /var/lib/mysql
> tmpdir          = /tmp
> language        = /usr/share/mysql/english
> skip-external-locking
> key_buffer              = 16M
> max_allowed_packet      = 16M
> thread_stack            = 128K
> thread_cache_size       = 8
> myisam-recover          = BACKUP
> query_cache_limit       = 1M
> query_cache_size        = 16M
> -------------
> 
> 
> What can i do to improve the performace?
> 
> 
> Thanks in advance!
> 
> Ilya
> 

As suggested by other : RAM / Raid 10 with ultra fast harddrive. and put 
storage place, tmp, lib/bacula and mysql on different
drives or array.
As Mysql love multi-proc you should envisage a real new multi-proc config with 
64bits distro.

After that you are using a too poor my.cnf ( seems to be the default one which 
contain very very little amount )

In your distribution you should find a sample of my-huge.cnf which should a 
good start be would be insuffisant
for the size of you database

Think about the indexes, and run frequently the db_check tool.

Follow a example of my.cnf for a serveur which do 1/4 of your jobs
It's AMD X2 6000 with 4Gb Ram. DB reside on a raid of raptor 10.000 trs
Storage is a bunch of 8x750Gb 7200trs WD in raid10.


# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.

# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
#skip-locking
key_buffer = 1024M
max_allowed_packet = 4M
table_cache = 2048
sort_buffer_size = 16M
read_buffer_size = 16M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 256M
thread_cache_size = 16
query_cache_size= 256M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
character-set-server=utf8
default-collation=utf8_general_ci

# skip-networking

server-id       = 1

#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Point the following paths to different dedicated disks
tmpdir          = /data01/mysql_tmp
#log-update     = /path-to-dedicated-directory/hostname


skip-innodb
skip-bdb


[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 4M
write_buffer = 4M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

I'm pretty sure that a "real" guru of mysql could even get this more optimized 
in function of what is doing the DB server.
(This config doesn't use innodb which in certain case have a better throughput)

Last point : In your case, you should perharps envisage to partition your 
catalog into multiple catalog like one catalog for
each domains you backup. Which could increase the concurrent job running.

See documentation and mailing list how to do this.

Hope all of this could help you a bit.
-- 

     Bruno Friedmann


------------------------------------------------------------------------------
Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
-OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
-Strategies to boost innovation and cut costs with open source participation
-Receive a $600 discount off the registration fee with the source code: SFAD
http://p.sf.net/sfu/XcvMzF8H
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users