|
SmpPerformance
SMP Performance Improvement
SMP Performance ImprovementsThis describes performance improvements from changes in the v2 Google patch. While the changes improve performance in many cases, a lot of work remains to be done. It improves performance on SMP servers by:
While tcmalloc makes some of the workloads much faster, we don't recommend its use yet with MySQL as we are still investigating its behavior. Database reloadThis displays the time to reload a large database shard on a variety of servers (HW + SW). Unless otherwise stated, my.cnf was optimized for a fast (but unsafe) reload with the following values. Note that innodb_flush_method=nosync is only in the Google patch and is NOT crash safe (kind of like MyISAM). This uses a real data set that produces a 100GB+ database.
The data to be reloaded was in one file per table on the db server. Each file was compressed and reloaded by a separate client. Each table was loaded by a separate connection except for the largest tables when there was no other work to be done. 8 concurrent connections were used. The smpfix RPM is MySQL 5.0.37 plus the v1 Google patch and the SMP fixes that include:
The base RPM is MySQL 5.0.37 and the v1 Google patch. It does not have the SMP fixes. The servers are:
Sysbench readwritesysbench includes a transaction processing benchmark. The readwrite version of the sysbench OLTP test is measured here using 1, 2, 4, 8, 16, 32 and 64 threads. ConfigurationCommand line # N is 1, 2, 4, 8, 16, 32 and 64 --test=oltp --oltp-table-size=1000000 --max-time=600 --max-requests=0 --mysql-table-engine=innodb --db-ps-mode=disable --mysql-engine-trx=yes --num-threads=N MySQL Options innodb_buffer_pool_size=8192M innodb_log_file_size=1300M innodb_read_io_threads = 4 innodb_write_io_threads = 4 innodb_file_per_table innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size = 200m innodb_thread_concurrency=0 log_bin key_buffer_size = 50m max_heap_table_size=1000M max_heap_table_size=1000M tmp_table_size=1000M max_tmp_tables=100 MySQL serversThe servers are:
sysbench rw, 4-core server
sysbench rw, 8-core server
sysbench rw, 16-core server
Sysbench readonlysysbench includes a transaction processing benchmark. The readonly version of the sysbench OLTP test is measured here using 1, 2, 4, 8, 16, 32 and 64 threads. ConfigurationCommand line # N is 1, 2, 4, 8, 16, 32 and 64 --test=oltp --oltp-read-only --oltp-table-size=1000000 --max-time=600 --max-requests=0 --mysql-table-engine=innodb --db-ps-mode=disable --mysql-engine-trx=yes --num-threads=N MySQL Options innodb_buffer_pool_size=8192M innodb_log_file_size=1300M innodb_read_io_threads = 4 innodb_write_io_threads = 4 innodb_file_per_table innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size = 200m innodb_thread_concurrency=0 log_bin key_buffer_size = 50m max_heap_table_size=1000M max_heap_table_size=1000M tmp_table_size=1000M max_tmp_tables=100 MySQL serversThe servers are:
sysbench ro, 4-core server
sysbench ro, 8-core server
sysbench ro, 16-core server
Concurrent joinsThis test runs a query with a join. It is run using concurrent sessions. The data fits in the InnoDB buffer cache. The query is: select count(*) from T1, T2 where T1.j > 0 and T1.i = T2.i The data for T1 and T2 matches that used for the sbtest table by sysbench. This query does a full scan of T1 and joins to T2 by primary key. The servers are:
joins, 4-core serverTODO joins, 8-core serverNote, lower values for Time are better. With data from the worst case: Without data from the worst case: joins, 16-core serverNote, lower values for Time are better. With data from the worst case: Without data from the worst case: Concurrent insertsThis test reloads tables in parallel. Each connection inserts data for a different table. Tests were run using 1, 2, 4, 8 and 16 concurrent sessions. The regression for 5.0.37 is in the parser and was fixed by 5.0.54. A separate table is used for each connection. DDL for the tables is: create table T$i (i int primary key, j int, index jx(j)) engine=innodb Multi-row insert statements are used that insert 1000 rows per insert statement. Auto-commit is used. The insert statements look like: INSERT INTO T1 VALUES (0, 0), (1, 1), (2, 2), ..., (999,999); The servers are:
MySQL 5.0.37 has a performance regression in the parser. This was fixed in 5.0.54. inserts, 4-core serverNote, lower values for Time are better. inserts, 8-core serverNote, lower values for Time are better. With data from the worst case: Without data from the worst case: inserts, 16-core serverNote, lower values for Time are better. With data from the worst case: Without data from the worst case: |
Sign in to add a comment