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
"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.