| Bug #38153 | mysql-bench: slowdown for update_of_primary_key_many_keys test | ||
|---|---|---|---|
| Submitted: | 15 Jul 2008 22:13 | Modified: | 28 Feb 2014 18:58 |
| Reporter: | Alexey Stroganov | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
| Version: | 5.0.66, 5.1.34, 5.6.16 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Aug 2008 13:58]
Alexey Stroganov
The similar difference in results of the same test(test-insert) was observed for InnoDB between 6.0.5 and 6.0.6.
6.0.5 Ratio 6.0.6
update_of_primary_key_many_keys 656.44 1.3 858.17
update_of_key_big 21.55 1.32 28.64
delete_big_many_keys 378.29 1.33 505.88
In order to analyze these specific tests I've extracted and attached test case.
[14 Aug 2008 13:19]
Alexey Stroganov
perl bug38153.pl --db-socket=/tmp/605.sock MySQL 6.0.5 alpha, ENGINE: InnoDB Generating random keys Inserting 100000 rows Inserted 0 rows Inserted 10000 rows Inserted 20000 rows Inserted 30000 rows Inserted 40000 rows Inserted 50000 rows Inserted 60000 rows Inserted 70000 rows Inserted 80000 rows Inserted 90000 rows Time for update_of_primary_key_many_keys (): 50 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Deleting rows from the table Time for delete_big_many_keys (10): 53 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) perl bug38153.pl --db-socket=/tmp/606.sock MySQL 6.0.6 alpha, ENGINE: InnoDB Generating random keys Inserting 100000 rows Inserted 0 rows Inserted 10000 rows Inserted 20000 rows Inserted 30000 rows Inserted 40000 rows Inserted 50000 rows Inserted 60000 rows Inserted 70000 rows Inserted 80000 rows Inserted 90000 rows Time for update_of_primary_key_many_keys (): 79 wallclock secs ( 0.00 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.01 CPU) Deleting rows from the table Time for delete_big_many_keys (10): 69 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU)
[14 Aug 2008 13:20]
Alexey Stroganov
Test case for the issue
Attachment: bug38153.pl (application/octet-stream, text), 4.09 KiB.
[16 Apr 2009 12:54]
Alexey Stroganov
We observed similar variance in results for update_of_primary_key_many_keys and delete_big_many_keys while testing 5.1.34 release.
5.1.33 5.1.34
delete_big_many_keys 476.51 564.99
update_of_primary_key_many_keys 834.31 917.36
There is no differences in oprofile output for both servers:
5.1.33
samples % symbol name
4475 33.7049 buf_page_is_corrupted
4334 32.6429 buf_calc_page_new_checksum
761 5.7317 rec_get_offsets_func
445 3.3517 os_aio_simulated_handle
393 2.9600 page_simple_validate
375 2.8244 buf_flush_batch
296 2.2294 cmp_dtuple_rec_with_match
5.1.34
samples % symbol name
5042 34.5792 buf_page_is_corrupted
4734 32.4669 buf_calc_page_new_checksum
803 5.5072 rec_get_offsets_func
516 3.5389 os_aio_simulated_handle
465 3.1891 page_simple_validate
384 2.6336 buf_flush_batch
315 2.1603 cmp_dtuple_rec_with_match
I've suspect some IO specialties on this box so I rerun test with ramfs:
5.1.30 5.1.34
delete_big_many_keys 32 32
update_of_primary_key_many_keys 29 30
and again oprofile output looks almost identical for release:
5.1.30
samples % symbol name
4277 32.2476 buf_page_is_corrupted
4024 30.3400 buf_calc_page_new_checksum
794 5.9866 rec_get_offsets_func
487 3.6719 page_simple_validate
432 3.2572 os_aio_simulated_handle
306 2.3072 buf_flush_batch
261 1.9679 cmp_dtuple_rec_with_match
5.1.34
samples % symbol name
4307 32.7553 buf_page_is_corrupted
3960 30.1164 buf_calc_page_new_checksum
772 5.8712 rec_get_offsets_func
458 3.4832 os_aio_simulated_handle
435 3.3082 page_simple_validate
296 2.2511 buf_flush_batch
290 2.2055 cmp_dtuple_rec_with_match
Will check these results on another box.
[28 Feb 2014 18:58]
Sveta Smirnova
5.0 -> 5.1 regression is not repeatable, but I can repeat some slowdown with the same test in 5.6. I started server with options: ./bin/mysqld --no-defaults --basedir=. --datadir=./data --innodb_log_file_size=128M --innodb_buffer_pool_size=4G --log-error --port=33050 --socket=/tmp/mysql_ssmirnova.sock & I also changed value of $many_keys_loop_count to 1,000,000 (was 100,000). Results: 5.6.16: Time for update_of_primary_key_many_keys (10): 249 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Deleting rows from the table Time for delete_big_many_keys (10): 69 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) 5.5.36: Testing update of keys Time for update_of_primary_key_many_keys (10): 223 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Deleting rows from the table Time for delete_big_many_keys (10): 49 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) 5.1.73: Time for update_of_primary_key_many_keys (10): 212 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Deleting rows from the table Time for delete_big_many_keys (10): 48 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.01 CPU) 5.0.96: Testing update of keys Time for update_of_primary_key_many_keys (10): 213 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Deleting rows from the table Time for delete_big_many_keys (10): 49 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) 5.0.51a: Testing update of keys Time for update_of_primary_key_many_keys (10): 221 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Deleting rows from the table Time for delete_big_many_keys (10): 47 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) So you see noticeable timing increase in 5.6.16: Time for update_of_primary_key_many_keys (10): 249 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Deleting rows from the table Time for delete_big_many_keys (10): 69 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU)
Description: During release testing of 5.0.66 I found out that update_of_primary_key_many_keys test from mysql-bench suite is slower more than 30% than in 5.0.64. (1006 sec vs 740 sec) 5.0.64 Ratio 5.0.66 update_of_primary_key_many_keys 739.73 1.36 1006.07 Purpose of this bug report collect additional information, provide repeatable test case. How to repeat: run test-insert from mysql-bench test suite for 5.0.66/InnoDB