MySQL Bugs: #74280: covering secondary scans worse than PK scan at high concurrency

Bug #74280	covering secondary scans worse than PK scan at high concurrency
Submitted:	8 Oct 2014 16:41	Modified:	10 Oct 2014 12:01
Reporter:	Mark Callaghan	Email Updates:
Status:	Verified	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:
A lot more detail is in http://smalldatum.blogspot.com/2014/10/details-on-range-scan-performance.html. DimitriK also knows more about this. The problem is that a covering secondary index scan is about as fast as a PK scan at low concurrency while it is about 2X slower at high concurrency -- the context in this case is that all clients access the same rows and the range scan is ~1000 rows.  The problem is mutex contention although the mutex contention isn't visible in perf schema output because the block rw-lock isn't instrumented (see PFS_NOT_INSTRUMENTED in source).

How to repeat:
I run mysqlslap like this:

Test server has 40 hyperthread cores

X=1, 2, 4, 8, ..., 32

# PK index scan
mysqlslap --concurrency=$X --create-schema=test1 --number-of-queries=100000 --create=iddl.sql --query="select j from foo where i >= 100 order by i limit 1000"

# non-covering secondary index scan
mysqlslap --concurrency=$X --create-schema=test1 --number-of-queries=100000 --create=iddl.sql --query="select k from foo FORCE INDEX(`xj`) where j >= 100 order by j limit 1000"

# covering secondary index scan
mysqlslap --concurrency=$X --create-schema=test1 --number-of-queries=100000 --create=iddl.sql --query="select k from foo FORCE INDEX(`xjk`) where j >= 100 order by j limit 1000"

Will attach iddl.sql, but it looks like this and puts 64,000 rows into the table...

create table foo (i int primary key auto_increment, j int, k int, l int) engine=innodb;
insert into foo values (null,0,0,0),(null,0,0,0),(null,0,0,0),(null,0,0,0),(null,0,0,0),(null,0,0,0),(null,0,0,0),(null,0,0,0),(null,0,0,0),(null,0,0,0);
<repeat 99 times to get 1000 rows>
insert into foo select null,0,0,0 from foo;
insert into foo select null,0,0,0 from foo;
insert into foo select null,0,0,0 from foo;
insert into foo select null,0,0,0 from foo;
insert into foo select null,0,0,0 from foo;
insert into foo select null,0,0,0 from foo;
update foo set j=i, k=i;
create index xj on foo(j);
create index xjk on foo(j,k);

Suggested fix:
Wish I knew

create/ddl file for mysqlslap

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

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
# performance_schema_instrument="wait/synch/%=on"

innodb_thread_concurrency=0
# innodb_concurrency_tickets=500

# innodb_adaptive_hash_index=0

binlog_format=row
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

And 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
# innodb_concurrency_tickets=500

# innodb_adaptive_hash_index=0

binlog_format=row
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

related bug - http://bugs.mysql.com/bug.php?id=74283

Hello Mark,

Thank you for the bug report and test case.
Confirmed that that a covering secondary index scan is about as fast as a PK scan at low concurrency while it is about 2X slower at high concurrency.
Also, mysqlslap with provided test case suggest that 5.7.5 is slower than 5.6.21.

I ran mysqlslap like this and stats are in following table:

X=1, 2, 4, 8, ..., 32

# PK index scan
mysqlslap --concurrency=$X --create-schema=test1 --number-of-queries=100000 --create=iddl.sql --query="select j from foo where i >= 100 order by i limit 1000"

# non-covering secondary index scan
mysqlslap --concurrency=$X --create-schema=test1 --number-of-queries=100000 --create=iddl.sql --query="select k from foo FORCE INDEX(`xj`) where j >= 100 order by j limit 1000"

# covering secondary index scan
mysqlslap --concurrency=$X --create-schema=test1 --number-of-queries=100000 --create=iddl.sql --query="select k from foo FORCE INDEX(`xjk`) where j >= 100 order by j limit 1000"

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

--------------------------------------------------------------------------------------------------
Concurrency | PK index scan | non-covering secondary index scan | covering secondary index scan
--------------------------------------------------------------------------------------------------
1           | 53.232 sec    | 123.684  sec                      | 53.791 sec
2           | 28.601 sec    | 98.048   sec                      | 29.018 sec
4           | 14.779 sec    | 70.811   sec                      | 14.669 sec
8           | 7.608  sec    | 75.4334  sec                      | 7.621  sec
16          | 6.017  sec    | 97.444   sec                      | 7.710  sec
32          | 4.503  sec    | 104.539  sec                      | 8.531  sec
--------------------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------------------
Concurrency | PK index scan | non-covering secondary index scan | covering secondary index scan
--------------------------------------------------------------------------------------------------
1           | 48.859 sec    | 100.413  sec                      | 49.600 sec
2           | 24.910 sec    | 91.999   sec                      | 27.385 sec
4           | 13.698 sec    | 70.666   sec                      | 14.012 sec
8           | 7.075  sec    | 63.059   sec                      | 7.378  sec
16          | 4.046  sec    |          sec                      | 5.759  sec
32          | 3.906  sec    | 63.710   sec                      | 6.899  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