| Bug #59802 | 5.5 performance regressions | ||
|---|---|---|---|
| Submitted: | 28 Jan 2011 18:53 | Modified: | 15 Oct 2012 14:16 |
| Reporter: | Matthew Lord | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server | Severity: | S5 (Performance) |
| Version: | 5.5.9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[28 Jan 2011 18:53]
Matthew Lord
[28 Jan 2011 18:57]
Matthew Lord
google-pprof sample output
Attachment: google-pprof_samples.txt (text/plain), 54.85 KiB.
[28 Jan 2011 18:57]
Matthew Lord
Script to create the test database, tables and data
Attachment: aNumberDB-tables.sql (, text), 11.23 KiB.
[28 Jan 2011 18:58]
Matthew Lord
Test queries for use with mysqlslap
Attachment: aNumQueries.sql (, text), 240 bytes.
[28 Jan 2011 19:11]
Matthew Lord
Here are some sample results, using tcmalloc and it's heap profiler with both binaries: /usr/local/mysql55/bin/mysqlslap --socket=/tmp/mysql51.sock --user root --create-schema=aNumberDB --query=/tmp/aNumQueries.sql --concurrency=32 --number-of-queries=320000 Benchmark Average number of seconds to run all queries: 98.959 seconds Minimum number of seconds to run all queries: 98.959 seconds Maximum number of seconds to run all queries: 98.959 seconds Number of clients running queries: 32 Average number of queries per client: 10000 /usr/local/mysql55/bin/mysqlslap --socket=/tmp/mysql55.sock --user root --create-schema=aNumberDB --query=/tmp/aNumQueries.sql --concurrency=32 --number-of-queries=320000 Benchmark Average number of seconds to run all queries: 117.839 seconds Minimum number of seconds to run all queries: 117.839 seconds Maximum number of seconds to run all queries: 117.839 seconds Number of clients running queries: 32 Average number of queries per client: 10000 Here's the config I used (I'm using mysqld_multi): [mysqld51] skip-grant-tables user=mysql server_id=2 mysqld=/usr/local/mysql51/bin/mysqld basedir=/usr/local/mysql51 datadir=/var/lib/mysql/mysql5.1 innodb_data_home_dir=/var/lib/innodb/5.1 innodb_log_group_home_dir=/var/lib/innodb/5.1 innodb_log_file_size=2000M socket=/tmp/mysql51.sock port=3307 language = /usr/local/mysql51/share/english #binlog_format=ROW #event_scheduler=ON log-bin binlog-format=ROW # plugin settings ignore-builtin-innodb #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 plugin-load=innodb=ha_innodb_plugin.so innodb_file_per_table=1 innodb_file_format=barracuda #innodb_status_file=1 innodb-rollback-on-timeout=1 innodb_strict_mode=1 innodb_autoinc_lock_mode=2 #innodb_force_recovery=4 transaction-isolation=READ-COMMITTED query_cache_type = 0 query_cache_size = 0 #ssl #ssl-key=/opt/mysql/ssl/server-key.pem #ssl-cert=/opt/mysql/ssl/server-cert.pem #ssl-ca=/opt/mysql/ssl/cacert.pem #log-slow-queries #long-query-time=2 [mysqld55] skip-grant-tables user=mysql server_id=4 mysqld=/usr/local/mysql55/bin/mysqld basedir=/usr/local/mysql55 datadir=/var/lib/mysql/mysql5.5 innodb_data_home_dir=/var/lib/innodb/5.5 innodb_log_group_home_dir=/var/lib/innodb/5.5 innodb_log_file_size=2000M socket=/tmp/mysql55.sock port=3355 language = /usr/local/mysql55/share/english #general-log=/var/lib/mysql/bootyjr55-general.log log-bin binlog-format=ROW query_cache_type = 0 query_cache_size = 0 #performance_schema #binlog_format=ROW innodb_autoinc_lock_mode=2 transaction-isolation=READ-COMMITTED innodb_file_per_table=1 innodb_file_format=barracuda innodb_strict_mode=1 #log-slow-queries #long-query-time=2
[28 Jan 2011 19:15]
Matthew Lord
google tcmalloc heap profiler samples from the example results
Attachment: google-pprof_samples.txt (text/plain), 52.81 KiB.
[28 Jan 2011 19:29]
Matthew Lord
Another test run, 5.5.9 is only about 10% slower than 5.1.54: [mysqld51] skip-grant-tables user=mysql server_id=2 mysqld=/usr/local/mysql51/bin/mysqld basedir=/usr/local/mysql51 datadir=/var/lib/mysql/mysql5.1 innodb_data_home_dir=/var/lib/innodb/5.1 innodb_log_group_home_dir=/var/lib/innodb/5.1 innodb_log_file_size=2000M socket=/tmp/mysql51.sock port=3307 language = /usr/local/mysql51/share/english #binlog_format=ROW #event_scheduler=ON #log-bin #binlog-format=ROW # plugin settings ignore-builtin-innodb #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 plugin-load=innodb=ha_innodb_plugin.so innodb_file_per_table=1 innodb_file_format=barracuda #innodb_status_file=1 innodb-rollback-on-timeout=1 innodb_strict_mode=1 innodb_autoinc_lock_mode=2 #innodb_force_recovery=4 transaction-isolation=READ-COMMITTED query_cache_type = 0 query_cache_size = 0 #ssl #ssl-key=/opt/mysql/ssl/server-key.pem #ssl-cert=/opt/mysql/ssl/server-cert.pem #ssl-ca=/opt/mysql/ssl/cacert.pem #log-slow-queries #long-query-time=2 [mysqld55] user=mysql server_id=4 mysqld=/usr/local/mysql55/bin/mysqld basedir=/usr/local/mysql55 datadir=/var/lib/mysql/mysql5.5 innodb_data_home_dir=/var/lib/innodb/5.5 innodb_log_group_home_dir=/var/lib/innodb/5.5 innodb_log_file_size=2000M socket=/tmp/mysql55.sock port=3355 language = /usr/local/mysql55/share/english #general-log=/var/lib/mysql/bootyjr55-general.log #log-bin #binlog-format=ROW query_cache_type = 0 query_cache_size = 0 #performance_schema #binlog_format=ROW innodb_autoinc_lock_mode=2 transaction-isolation=READ-COMMITTED innodb_file_per_table=1 innodb_file_format=barracuda innodb_strict_mode=1 #log-slow-queries #long-query-time=2 ------------------------------- /usr/local/mysql55/bin/mysqlslap --socket=/tmp/mysql51.sock --user root --create-schema=aNumberDB --query=/tmp/aNumQueries.sql --concurrency=32 --number-of-queries=320000 Benchmark Average number of seconds to run all queries: 107.904 seconds Minimum number of seconds to run all queries: 107.904 seconds Maximum number of seconds to run all queries: 107.904 seconds Number of clients running queries: 32 Average number of queries per client: 10000 /usr/local/mysql55/bin/mysqlslap --socket=/tmp/mysql55.sock --user root --create-schema=aNumberDB --query=/tmp/aNumQueries.sql --concurrency=32 --number-of-queries=320000 Benchmark Average number of seconds to run all queries: 116.557 seconds Minimum number of seconds to run all queries: 116.557 seconds Maximum number of seconds to run all queries: 116.557 seconds Number of clients running queries: 32 Average number of queries per client: 10000
[28 Jan 2011 19:31]
Matthew Lord
pprof samples from last test
Attachment: google-pprof_samples.txt (text/plain), 52.55 KiB.
[28 Jan 2011 19:34]
Matthew Lord
If you're interested in a secondary, more complicated, test that shows an even more remarkable difference: mysql --socket=/tmp/mysql51.sock < aNumberDB-procs.sql mysql --socket=/tmp/mysql55.sock < aNumberDB-procs.sql /usr/local/mysql51/bin/mysqlslap --socket=/tmp/mysql51.sock --user root --create-schema=aNumberDB --query=/tmp/call_P_aNum.sql --concurrency=32 --number-of-queries=320000 /usr/local/mysql55/bin/mysqlslap --socket=/tmp/mysql55.sock --user root --create-schema=aNumberDB --query=/tmp/call_P_aNum.sql --concurrency=32 --number-of-queries=320000 I'll attach these two files as well.
[28 Jan 2011 19:35]
Matthew Lord
SPs for a secondary test
Attachment: aNumberDB-procs.sql (, text), 14.46 KiB.
[28 Jan 2011 19:35]
Matthew Lord
a secondary mysqlslap test file
Attachment: call_P_aNum.sql (, text), 66 bytes.
[20 Jun 2011 7:19]
MySQL Verification Team
i notice 5.5.13 is built with -fno-omit-frame-pointer but 5.1.57 doesn't have this option. how much would that influence the binary speed?
[20 Jun 2011 14:43]
Xue Feng Ding
Our customer has the same performance issue when they plan to updrade from 5.0.67 to 5.5.13. - Please see the details SR 3-3409097931 in MOS. Hopefully our engineering team would work it out ASAP.
