We recently switched one of our tables to innodb and now we are experiencing very slow UPDATE execution times. An update which used to take 0.010-0.030 seconds can now take over 70 seconds. Some queries are dropped because they cannot acquire a lock within the default 50 seconds limit (I realize we can raise this limit).
The table in question has only one index, the primary key itself which is a mediumint value. The table has about 1 million rows. All UPDATE's in this context involve a single row. Usually 4-5 columns from that row are affected in every query.
Current my.cnf is pasted below. Do you see anything which could particularly cause a bad UPDATE performance for innodb?
[mysqld]
set-variable=local-infile=0
datadir=/db/mysql/data
socket=/var/lib/mysql/mysql.sock
#log = /var/log/mysqld.log
log-error = /var/log/mysqld.error.log
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-locking
key_buffer = 1G
query_cache_size = 256M
thread_cache_size = 128
table_cache = 2048
max_connections = 400
query_cache_limit = 1024M
log_slow_queries = /var/log/mysql-slow.log
long_query_time = 1
skip-bdb
skip-locking
skip-name-resolve
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=2
#innodb_log_file_size=250M
innodb_log_buffer_size=8M
innodb_lock_wait_timeout=50
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
UPDATE:
innodb_log_file_size: 5242880
have_innodb: YES
"WHERE" clause always looks only for one column, which is the primary key.
UPDATE - July 26, 2012:
We upgraded our database to mysql 5.5. Now innodb updates are pretty fast, less than 0.010 seconds in our specific case. And, variance is pretty low. My take from this is: innodb should be used with real caution on mysql 5.0.
SHOW VARIABLES LIKE 'innodb_log_file_size';
? What is the output ofSHOW VARIABLES LIKE 'have_innodb';
? Are all UPDATEs based onWHERE id = ...
or are there other columns involved in the WHERE clause of your UPDATEs?query_cache_size=0
. It is is very rare of the query cache to be effective and often it is a source of contention.