MySQL Forums
Forum List  »  Performance

slow overall perfomance
Posted by: N N
Date: May 20, 2011 03:53AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
slow overall perfomance
3093
N N
May 20, 2011 03:53AM
1140
May 21, 2011 08:46AM
1224
N N
May 23, 2011 03:07AM
1033
May 23, 2011 09:49AM
988
N N
August 08, 2011 09:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.