3

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.

2
  • What version of MySQL are you using? What is the output of SHOW VARIABLES LIKE 'innodb_log_file_size';? What is the output of SHOW VARIABLES LIKE 'have_innodb'; ? Are all UPDATEs based on WHERE id = ... or are there other columns involved in the WHERE clause of your UPDATEs? Feb 27, 2012 at 17:10
  • What is the output of SHOW FULL PROCESSLIST when these issues are occurring? If you are doing a lot of updating, you might consider turning off the query cache with query_cache_size=0. It is is very rare of the query cache to be effective and often it is a source of contention. Feb 28, 2012 at 13:40

2 Answers 2

2

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

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.

4
  • Thank you! We never update the primary key for this table. I'll focus on #2 and #3. I'll keep you posted on the result.
    – Haluk
    Feb 27, 2012 at 19:32
  • We've set innodb_log_file_size to 250M, and also set innodb_flush_method=O_DIRECT. These really improved performance. However we still, even if it is at a much less rate, receive the "lock wait timeout expired" message. Some update queries last around 10-15 seconds. These are a lot higher compared to our MYISAM experience. We'll stick with MYISAM for now. Maybe later we can upgrade from 5.0 to 5.5 and try INNODB again.
    – Haluk
    Feb 28, 2012 at 2:22
  • When you upgrade to 5.5, there are settings to activate hyperthreading in the presence of multiple CPUs. Please see my earlier post on this : dba.stackexchange.com/a/5670/877 Feb 28, 2012 at 3:05
  • @RolandoMySQLDBA you may be interested in this Q: MySQL UPDATE Hangs with No Error Oct 14, 2013 at 19:49
0
  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.

1
  • Not updating the primary key. It is always fixed. No foreign keys. The table size is less than 50mb. There are about 30-40 tables, all others are myisam and they add up to around 40-50GB including indexes. I'll read about innodb_file_per_table.
    – Haluk
    Feb 27, 2012 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.