There are 3 big insert that are running (one for filename, one for path, then one for file). Which one is slow exactly ? (or are they all slow ?)
At first sight, I'd say you 'server' is very small... RAM is really small. For the insert into file, the important indexes aren't on file but on path and filename, because we're joining on them to do the insert into file. the indexes are the ones of path.path and on filename.name. Could the be corrupt ? (I'm by no means a mysql expert :) ) Le Friday 06 March 2009 13:12:00 Ilya Ruprecht, vous avez écrit : > 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 > > --------------------------------------------------------------------------- >--- 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@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users ------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
