Probably not related to the purge script but you will gain performance
by giving a size to the MySQL query cache size.
Use "SHOW VARIABLES LIKE '%cache%';" to confirm it has the (stupid)
default value of 0. And then add a "query_cache_size = 8M" to to
[mysqld], it WILL ease the SELECT occuring while you purge.
As of size : in my setup the signature tables wheight around 400Mo for a
few tenth of mailboxes. Granted they are all in TOE mode.
The purge script never took more than a few minutes, even the first time
I ran it for the first time, when I gained around 200M :)
You should consider using InnoDB too. But if you want/need to stick with
MyISAM, be sure to "OPTIMIZE TABLE dspam_token_data" and all the other
tables too.
N
Pointers :
http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html
http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html
dale gallagher a écrit :
You've got huge index file for dpam_token_data. Try to keep this file
down. Note that on slow IO (non-raid) it can be too late for online
optimization as optimizing the table could last MANY hours and has to
be done on an offline copy (provided you can afford loosing training
from one weekend).
I'm also experiencing difficulties with MySQL - running the purge
script is taking forever and seems to seriously delay all other
connections to the database; so after 5 minutes I simple stop the
purge process. Granted, I've been really slack and have never run the
purge script until today. Some info follows. Any pointers would be
appreciated. How long should I expect this process to take? The
machine is running Linux 2.6.x with 512MB RAM and approx 30 DSPAM
filtered mailboxes.
MySQL 4.1.7
DSPAM 3.8.0
# mysql -udspam -p dspam < purge-4.1.sql
# du -hs /var/lib/mysql/dspam | grep token_data
134M dspam_token_data.MYD
109M dspam_token_data.MYI
12k dspam_token_data.frm
# cat /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
#skip-networking
server-id = 1
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data/
innodb_log_arch_dir = /usr/local/mysql/data/
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
