Innodb UPDATE performance

Question

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.

Answer 1

PERSPECTIVE #1

When you update by Primary Key only in InnoDB, there is a rare but possible occasion when the clustered index (aka gen_clust_index) can get locked.

I once answered three posts from one individual on this subject

• Trouble deciphering a deadlock in an innodb status log
• Reasons for occasionally slow queries?
• Will these two queries result in a deadlock if executed in sequence?

Please read through these carefully. The poster of these question found his own workaround based on the seeing InnoDB Clustered Index Locking behavior. Sadly, he did not post what the workaround was.

In addition, when you see the queries running slow, log into mysql and run SHOW ENGINE INNODB STATUS\G and starting looking for locks in the Clustered Index.

PERSPECTIVE #2

I see you commented out innodb_log_file_size. You have it at 5MB, the default. Since innodb_buffer_pool_size is set to 1G, innodb_log_file_size needs to be at 256M. Click here to go about setting innodb_log_file_size to 256M.

PERSPECTIVE #3

I see you are not using innodb_file_per_table. You may want to use it in order to have table updates done specifically for the one table with a million rows. Click here to see how to Clean Up InnoDB infrastructure to use innodb_file_per_table.

Answer 2

1. Are you updating the value of the primary key value?

2. Are any of the columns foreign keys to other tables? Since InnoDB also updates foreign keys in the same transaction via the CASCADE.

3. Can you increase the innodb_buffer_pool_size to more than 1GB so that the whole table can fit in memory?

4. How many other tables are in the schmea - since there may be issues resizing the tablespace to carry out the update

5. You may want to try using the innodb_file_per_table where a tablespace file is created for each table. Once you change this parameter you need to restart your server, then create your database afresh for it to take effect.