Looking at your my.cnf, you have defaults for many things. What you desperately need is a full Cleanup of the InnoDB Infrastructure. Click here to see the Pictorial Representation of the InnoDB Infrastructure.
STEP 01) Run this query
SELECT CEILING(Total_InnoDB_Bytes/POWER(1024,2)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A;
This will tell you how big to set innodb_buffer_pool_size (in MB).
Let's say that answer came up 1024.
STEP 02) Run this query
SELECT CEILING(Total_MYI_Bytes/POWER(1024,2)) RKBS FROM (SELECT SUM(index_length) Total_MYI_Bytes FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','performance_schema','mysql')) A;
This will tell you how big to set key_buffer_size (in MB).
Let's say that answer came up 64.
STEP 03 : Run this query
SET GLOBAL innodb_fast_shutdown = 0;
This will perform a full transactional commit of any Transactions leftover in the Transaction Logs during the time of shutdown. The Transaction Logs are
/var/lib/mysql/ib_logfile0/var/lib/mysql/ib_logfile1
STEP 04 : Dump all data out to a text file
cd /root mkdir datadump cd datadump MYSQLDUMP_CONNECT="-uroot -ppassword" MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers" MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --flush-privileges --all-databases" mysqldump ${MYSQLDUMP_CONNECT} ${MYSQLDUMP_OPTIONS} > MySQLData.sql
STEP 05 : Shutdown MySQL
service mysql stop
STEP 06 : Add the following to my.cnf
If you are using MySQL 5.5 (or MySQL 5.1 with InnoDB Plugin Enabled), add these
[mysqld] key_buffer_size=64M innodb_file_per_table innodb_open_files=1000 innodb_buffer_pool_size=1024M innodb_log_file_size=256M innodb_fast_shutdown=0 innodb_read_io_threads=16 innodb_write_io_threads=16 join_buffer_size=4M read_buffer_size=4M read_rndbuffer_size=4M
If you are using MySQL 5.0, add these
[mysqld] key_buffer_size=64M innodb_file_per_table innodb_open_files=1000 innodb_buffer_pool_size=1024M innodb_log_file_size=256M innodb_fast_shutdown=0 innodb_thread_concurrency=0 join_buffer_size=4M read_buffer_size=4M read_rndbuffer_size=4M
Please note I chose innodb_log_file_size to 25% of innodb_buffer_pool_size
STEP 07 : Make room for new InnoDB Files
cd /var/lib/mysql mv ib_logfile0 ib_logfile0.bak mv ib_logfile1 ib_logfile`.bak mv ibdata1 ib_data1.bak
STEP 08 : Start MySQL
service mysql start
STEP 09 : Load the Data back
mysql -uroot -p < /root/datadump/MySQLData.sql
STEP 10 : If everything is running better, then run this:
rm -f ib_logfile0.bak rm -f ib_logfile1.bak rm -f ibdat1.bak
All done with InnoDB Cleanup.
I have suggested InnoDB improvements like this many times before
Give it a Try !!!
