| Bug #33948 | performance issue when using sysbench benchmark on a multiple-core system. | ||
|---|---|---|---|
| Submitted: | 20 Jan 2008 15:16 | Modified: | 31 Jul 2009 10:44 |
| Reporter: | terry tao | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S5 (Performance) |
| Version: | 5.1.22 | OS: | Linux (rhas5) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution, mysql-5.1.22 | ||
[20 Jan 2008 15:18]
terry tao
the patch prototype for test.
Attachment: mysql-5.1.22-open_cache.patch (application/octet-stream, text), 4.72 KiB.
[20 Jan 2008 15:25]
terry tao
the patch is: add three pointer(hash_head,hash_prev,hash_next) to the structure table,they well construct two links for table cached items,the hash_next is always to point to used table cached items,the hash_prev is always to point to unused table cached items.it will decrease the times for finding unused item when the concurrency number is big.
[20 Jan 2008 15:26]
terry tao
the result.
Attachment: sysbench_innodb_1000000.png (image/png, text), 5.91 KiB.
[28 Jan 2008 14:35]
Susanne Ebrecht
Many thanks for writing a bug report.
[5 Feb 2008 15:25]
Heikki Tuuri
Thank you, a very simple way to improve the performance with hundreds of threads. --Heikki
[19 Jun 2008 14:03]
Trudy Pelzer
This performance issue will be solved in 6.x by the implementation of WL#3726, WL#3983, WL#4440 and WL#4441.
[19 Jun 2008 16:10]
Konstantin Osipov
The suggestion have been implemented in WL#3726. The bug needs to be reverified against bzr_mysql-6.0-3726 tree. Sveta, could you do that please?
[26 May 2009 8:58]
Masood Mortazavi
Terry - Since you're interested in contributing to MySQL, you should sign the Sun|MySQL contributor agreement ... the SCA ... the instructions are given here: http://forge.mysql.com/wiki/Sun_Contributor_Agreement
[31 Jul 2009 6:17]
Sveta Smirnova
Kostja,
sorry, missed your comment, because did not follow discussion on this report, because did not do any action on it. Is bzr_mysql-6.0-3726 tree still exists?
Results for 5.1:
$cat ../mysql-5.1/bug33948_64.out.txt | grep 'open_table('
0.37 7402199.00 49431.00 open_table(THD*, TABLE_LIST*, st_mem_root*, bool*, unsigned int)
$cat ../mysql-5.1/bug33948_32.out.txt | grep 'open_table('
0.31 6947035.00 35837.00 open_table(THD*, TABLE_LIST*, st_mem_root*, bool*, unsigned int)
$cat ../mysql-5.1/bug33948_2.out.txt | grep 'open_table('
0.18 3873355.00 9824.00 open_table(THD*, TABLE_LIST*, st_mem_root*, bool*, unsigned int)
Azalea:
$cat bug33948_32.out.txt | grep 'open_table('
0.23 5192177.00 15692.00 open_table(THD*, TABLE_LIST*, st_mem_root*, enum_open_table_action*, unsigned int)
$cat bug33948_2.out.txt | grep 'open_table('
0.29 8891510.00 37572.00 open_table(THD*, TABLE_LIST*, st_mem_root*, enum_open_table_action*, unsigned int)
So some improvement in Azalea exists, although it is not big.
[31 Jul 2009 10:12]
Konstantin Osipov
WL#3726 is in 5.4
[31 Jul 2009 10:14]
Konstantin Osipov
But in any case WL#3726 implemented the optimization suggested in this bug report. If it is still slower than one would expect, it's due to other reasons. So this bug report should be closed.
[31 Jul 2009 10:44]
Sveta Smirnova
Kostja, thank you for the feedback. Report is closed.
[17 Dec 2010 17:08]
Mark Callaghan
This is excellent
Description: we installed mysql on a normal pc server with Intel Quad cpu inside,when the concurreny is more than two hundred,the transaction decreased too quickly. Used oprofile to trace it then found it spended more time on searching a free table entry from open_cache. because the sysbench only opened a single table,all the table entry would link in a single hash list,included freed ones and used ones. when the concurreny number becames too large,it is more expensive on searching a free table entry. look at open_table() in sql_base.cc line 2489--2493 for (table= (TABLE*) hash_first(&open_cache, (uchar*) key, key_length, &state); table && table->in_use ; table= (TABLE*) hash_next(&open_cache, (uchar*) key, key_length, &state)) How to repeat: mysqld startup parameter: --table_cache=2048 --key_buffer=384M --innodb_buffer_pool_size=384M --innodb_additional_mem_pool_size=20M --innodb_log_file_size=100M --innodb_log_buffer_size=8M --innodb_thread_concurrency=128 sysbench version:0.4.8 $sysbench --test=oltp --mysql-table-engine=innodb --mysql-engine-trx=yes --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test prepare $sysbench --num-threads=(1,4,8,16,...,256,600,...) --max-requests=10000000 --max-time=0 --mysql-table-engine=innodb --test=oltp --oltp-test-mode=simple --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test run Suggested fix: Distinguish free table entry and used table entry, it make much more quickly on searching.