MySQL Bugs: #74325: updates to indexed column much slower in 5.7.5

Bug #74325	updates to indexed column much slower in 5.7.5
Submitted:	10 Oct 2014 19:26	Modified:	22 May 2015 13:02
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	5.7.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Repeating the test done for Heap engine on InnoDB (see http://smalldatum.blogspot.com/2014/10/low-concurrency-performance-for-updates.html) shows a big regression for updates to an indexed column. InnoDB is more than 2X slower than 5.6.21 and 4X slower than 5.0.85. Note that this workload is special -- all updates are to the same row.

From looking at linux perf output there is a lot more time from pessimistic code paths and maybe from old versions of the rows

How to repeat:
will update with URL for blog post
will upload output from linux perf for 5.6.21 and 5.7.5

Suggested fix:
don't know

output from hierarchical linux perf for 5.7.5

Attachment: gperf.575 (application/octet-stream, text), 2.53 MiB.

output from flat linux perf for 5.7.5

Attachment: fperf.575 (application/octet-stream, text), 411.65 KiB.

output from hierarchical linux perf for 5.6.21

Attachment: gperf.5621 (application/octet-stream, text), 1.17 MiB.

output from flat linux perf for 5.6.21

Attachment: fperf.5621 (application/octet-stream, text), 248.04 KiB.

difference in SHOW GLOBAL VARIABLES between 5.6.21 and 5.7.5

Attachment: diff.sgv (application/octet-stream, text), 3.12 KiB.

Test case described at http://smalldatum.blogspot.com/2014/10/low-concurrency-performance-for-updated.html

my.cnf for 5.7.5:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

table-definition-cache=1000
table-open-cache=2000
table-open-cache-instances=8
max_connections=20000
key_buffer_size=200M
metadata_locks_hash_instances=256 
query_cache_size=0
query_cache_type=0
server_id=9
performance_schema=0

innodb_thread_concurrency=0

binlog_format=statement
skip_log_bin
sync_binlog=0

innodb_buffer_pool_instances=8
innodb_io_capacity=1000
innodb_lru_scan_depth=1000
innodb_checksum_algorithm=CRC32
innodb_buffer_pool_size=2G
innodb_log_file_size=1900M
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=0
innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct=80
innodb_file_format=barracuda
innodb_file_per_table
datadir=/data/orig575/var

my.cnf for 5.6.21

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

table-definition-cache=1000
table-open-cache=2000
table-open-cache-instances=8
max_connections=20000
key_buffer_size=200M
metadata_locks_hash_instances=256 
query_cache_size=0
query_cache_type=0
server_id=9
performance_schema=0

innodb_thread_concurrency=0

binlog_format=statement
skip_log_bin
sync_binlog=0

innodb_buffer_pool_instances=8
innodb_io_capacity=1000
innodb_lru_scan_depth=1000
innodb_checksum_algorithm=CRC32
innodb_buffer_pool_size=2G
innodb_log_file_size=1900M
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=0
innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct=80
innodb_file_format=barracuda
innodb_file_per_table

mysqlslap command lines

for t in 1, 2, 4
I use nq = 100,000
value for ddl will be uploaded

mysqlslap --concurrency=$t -h127.0.0.1 --create-schema=test1 --number-of-queries=$(( $t * $nq )) --create=${ddl} --query="update foo set l = l + 2 where i=100" 

mysqlslap --concurrency=$t -h127.0.0.1 --create-schema=test1 --number-of-queries=$(( $t * $nq )) --create=${ddl} --query="update foo set k = k + 2 where i=100"

create file for mysqlslap

Attachment: iddl.sql (application/octet-stream, text), 15.46 KiB.

Hello Mark,

Thank you for the bug report and test case.
With the provided mysqlslap test case, confirmed that updates to indexed column much slower in 5.7.5.

###################################
         MySQL Version 5.7.5
###################################

-----------------------------------------------------------------
Concurrency | l = l + 2     | k = k + 2                         | 
-----------------------------------------------------------------
1           | 10.934 sec    | 62.227   sec                      | 
2           | 13.468 sec    | 107.514  sec                      | 
4           | 19.428 sec    | 173.223  sec                      | 
-----------------------------------------------------------------

###################################
         MySQL Version 5.6.21
###################################

-----------------------------------------------------------------
Concurrency | l = l + 2     | k = k + 2                         | 
-----------------------------------------------------------------
1           | 10.906 sec    | 12.652   sec                      | 
2           | 12.858 sec    | 15.511   sec                      | 
4           | 21.272 sec    | 30.195   sec                      | 
-----------------------------------------------------------------
--------------------------------------------------------------------------------------------------
Average number of seconds to run all queries
Minimum number of seconds to run all queries
Maximum number of seconds to run all queries
--------------------------------------------------------------------------------------------------

Thanks,
Umesh

Posted by developer:
 
The root cause is innodb_fill_factor, whose default value is 100.Comparing to 5.6, we have no empty space in a b-tree leaf page after adding a index in 5.7, we have to split the page. So 5.7 is much slower than 5.6 for the update test.

Add the following SQL in ddl.sql will fix the problem:
SET GLOBAL innodb_fill_factor=90;

Hi Mark, when we build a secondary index in 5.6 by insert, the first leaf page is half full, all other leaf pages are full. You test happens to update a row in the first leaf page. so the update is faster. but if you update a row in other full leaf pages, the update is slower.

try "mysqlslap --concurrency=$t -h127.0.0.1 --create-schema=test1 --number-of-queries=$(( $t * $nq )) --create=${ddl} --query="update foo set k = k + 2 where i=800"

In 5.7, we build index by bulk load, and all leaf pages are full.

fortunately, we have innodb_fill_factor to control the empty space for future update/insert. You can set proper value for it according to your requirement.

thank you

Posted by developer:
 
I investigate a little more on the issue by monitoring two counters in innodb_metrics:
"index_page_merge_successful" and "index_page_splits".

https://dev.mysql.com/doc/refman/5.6/en/innodb-information-schema-metrics-table.html

create table foo (i int primary key auto_increment, j int, k int, l Gint) engine=innodb;
--insert 64000 rows;
update foo set j=i, k=i;
create index xjk on foo(j,k);

Test: mysqlslap --concurrency=1 --socket ./mysql.socket -u root -proot --number-of-queries=100000 --query="update foo set k = k + 2 where i=800"

case 1: update foo set k = k + 2 where i=100
Seconds to run all queries  : 16.195 seconds
Count of index page merge   : 0
Count of index page split   : 0

Analysis:
The record is in the first leaf page and it is half full in 5.6. Purge thread can always keep pace with update. so we always have empty space on the leaf page.

Solution in 5.7:
Set innodb_fill_factor to 90.
http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_fill_factor
e.g. set global innodb_fill_factor=90;

case 2: update foo set k = k + 2 where i=800
Seconds to run all queries  : 54.736 seconds
Count of index page merge   : 92321
Count of index page split   : 92321

Analysis: The split/merge makes the update slow!
The record is in a leaf page, and it's full in 5.6 & 5.7. when we insert a new record to the page, we need to split. The next update(insert) should not need to split, but purge thread works hard and it merges the split pages right after we split them always.

Solution in 5.7:
Set merge_threshold to 40.
https://dev.mysql.com/doc/refman/5.7/en/index-page-merge-threshold.html
e.g. create index xjk on foo(j,k) comment 'merge_threshold=40';

Hi mark, please see the comment just above. Do you expect that we make the default behavior the same as that of 5.6? I mean changing default inndob_fill_factor to a proper number to have some empty space left(5.6 has 1/16 empty space).
or we can just close the bug?

I asked my local InnoDB gurus to respond

Hi Mark, Let's make it more clear.

We'd like to keep the page 15/16 full for cluster index, which means setting default innodb_fill_factor to 95, just like 5.6(create index by insert). See http://dev.mysql.com/doc/refman/5.6/en/innodb-physical-structure.html.

Our question is that do you need us to keep the first leaf page half full as 5.6? Note: innodb_fill_factor applies to secondary indexes too, which means we will have empty space left in all leaf pages.

Just let us know if you have more suggestions.

I have informed the gurus. I expect them to give useful feedback. If they don't then you should assume the community is OK with the changes.

Shaohua,

I really like new variables. But we have regression already (this bug) with changed fill factor. Are you sure there would be not regressions for queries, optimized for old behaviour?

I mean no more regressions. Users can survive single regression, but if significant percentage of queries start work slower there would be unhappy users.

Posted by developer:
 
Keep default behavior compatible with 5.6: reserve 1/16 free space in bulk load for cluster index when innodb_fill_factor is 100.

http://dev.mysql.com/doc/refman/5.7/en/innodb-physical-structure.html

[11 May 2015 15:07] Daniel T Price

Fixed as of the upcoming 5.7.8, 5.8.0 releases, and here's the changelog
entry:

Updates to indexed columns could be slower in MySQL 5.7.5 and higher. In
pre-MySQL 5.7.5 releases, "InnoDB" reserves 1/16 of the space in clustered
index pages for future inserts and updates. This behaviour changed in
MySQL 5.7.5 with the introduction of the "innodb_fill_factor" option. With
"innodb_fill_factor=100", B-tree index pages were completely filled during
sorted index builds, and subsequent updates to index pages resulted in
page splitting. To restore pre-MySQL 5.7.5 behavior, the default setting
of "innodb_fill_factor=100" now leaves 1/16 of the space in clustered
index pages.

Thank you for the bug report.