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:
None 
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
Triage: Needs Triage: D3 (Medium)

[15 Jul 2008 22:13] Alexey Stroganov
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
[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)