We are planning to upgrade to 5.1, currenlty we are using 5.0. We peformed several different performance tests on 5.1 but each time we found 5.1 performance 2x slower than 5.0. During initial test runs on 5.1 we saw lots of deadlocks, which we do not see on 5.0 with the same test runs, we understand there are significant changes made in innodb lock model
(http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html)
So we thought lets try using innodb_autoinc_lock_mode = 0 (“traditional” lock mode, same as in 5.0?) but this did not solve the issue, so we then used innodb_autoinc_lock_mode = 2 (“interleaved” lock mode) and deadlocks indeed disappeard but we still see lots of LOCK WAIT and thus test takes siginificantly longer to finish.
We performed tests on 5.1 using built-in innodb as well as innodb-plugin but in each case it performed slower than 5.0 and for the test runs on 5.0/5.1 we used similar mysql options such as:
<<snip>>
log-bin
max_allowed_packet = 16M
max_heap_table_size = 128M
tmp_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 8M
innodb_buffer_pool_size = 1600M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_file_per_table = 1
innodb_additional_mem_pool_size = 16M
innodb_log_buffer_size = 8M
innodb_max_dirty_pages_pct = 90
#innodb_thread_concurrency = 16
innodb_sync_spin_loops = 20
innodb_lock_wait_timeout = 240
innodb_doublewrite = 1
innodb_support_xa = 0
innodb_checksums = 0
innodb_locks_unsafe_for_binlog = 0
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 0
innodb_flush_log_at_trx_commit = 1
binlog_cache_size = 4M
query_cache_type = 0
query_cache_size = 0
<<snip>>
but we used few additional options with 5.1:
<<sinp>>
plugin-load =innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
ignore_builtin_innodb
binlog_format = ROW
innodb_io_capacity=500
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_open_files=4000
<<snip>>
Questions:
1. Why do we see LOCK WAIT/DEADLOCK in the innodb-monitor on 5.1 but we do not see same locks/deadlocks in 5.0?
2. I collected SHOW GLOBAL STATUS ouput immediately after the tests on both 5.0/5.1 and Some of %Handler% values are quite different. The first value is strange, there are more 4 times as many internal commits in 5.1? I'm not sure if that would cause the kind of slow down weare experiencing though?
http://bugs.mysql.com/bug.php?id=52453
handler_prepare and handler_update are quite different as well.
5.1
==
| Handler_commit | 1778992 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 508262 |
| Handler_read_first | 7 |
| Handler_read_key | 6523209 |
| Handler_read_next | 762390 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 35326370 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 84711 |
| Handler_write | 35326336 |
5.0
===
| Handler_commit | 424253 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 169654 |
| Handler_read_first | 7 |
| Handler_read_key | 6184250 |
| Handler_read_next | 762390 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 30498998 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 1 |
| Handler_write | 31433188 |
We did enable slow query log to see if there are any slow queries but fortunatley there is not a single slow query that we have found, the threshold for the slow queries are 1 i.e. log-slow-queries=1. We use 100s of stored procedures, all db operations (select, insert, update, delete etc.) are performed via stored procedures.
Edited 7 time(s). Last edit at 05/20/2011 04:10AM by N N.