Hi, Apologies in advance for the large post but I've tried to explain my problem as much as I can.
I'm having load issues with my InnoDB server and am not sure what I should try to resolve this problem. The machine is redhat Linux 7.2 with dual processors, 4GB ram and about 32GB disk space. The MySQL version is 4.0.13. The machine is used purely with InnoDB tables and has a few very large tables acting as cache data. I've allocated 10 data files each 2Gb, when busy the files are between 40% and 60% used. I have clients reading and writing to the tables constantly and these clients do not see any performance degradation which I'm really happy about. But every 10 minutes I run a cron job that deletes expired/duplicated records from the tables. This process seems to hammer the system. I have 3 tables, table A, B and C. Table A has a 1 to many relationship with table B and table B has a 1 to many relationship with table C. Table A is the master record table and holds the timestamp. So, this cron job does a select to get expired records, this query is very fast as retrieves anywhere between 5,000 and 50,000 records. For each expired record it then starts a transaction, deletes all records from C, deletes all records from B, deletes the master record from A and then ends the transaction. That's it. This process takes about 5 minutes if expiring a large number of records and can take the machines load average to about 10. Looking at top when this process runs I can see many mysql process but 1 or 2 are taking alot of CPU, 1 about 50% and 1 about 35%, sometimes the top one can go up as high as 90%. The my.cnf file for innodb is as follows innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 15M innodb_log_file_size = 256M innodb_log_buffer_size = 8M I've read the InnoDB tuning page ( http://dev.mysql.com/doc/mysql/en/InnoDB_tuning.html ), there are a few things I could try but I'd like to make sure that I'm on the right tracks. * Firstly I could put more queries into a single transaction. * Should I increase the innodb_buffer_pool_size ??, ( bare in mind there is a 2GB limit on linux/GLIBC ) * Change the innodb_flush_method to O_DSYNC Any advice on the steps I should take would be greatly appreciated. Please let me know if you require any additional information. Best Regards, Marvin Wright ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
