MySQL Bugs: #42908: Performance degradation on SELECT_DISTINCT

Bug #42908	Performance degradation on SELECT_DISTINCT_RANGES (as in Sysbench)
Submitted:	17 Feb 2:20	Modified:	3 Jul 13:12
Reporter:	Elena Stepanova
Status:	Analyzing
Category:	Server	Severity:	S5 (Performance)
Version:	5.0.74, 5.0.76, 5.0.77	OS:	Linux (SLES 10)
Assigned to:	Elena Stepanova	Target Version:
Triage:	Needs Triage: D4 (Minor)

Description:
Sysbench scenario SELECT_DISTINCT_RANGES on InnoDB tables reveals approx. 5-8% throughput
decrease between 5.0.67 - 5.0.77 (community releases), and 5.0.72 - 5.0.74.

The difference between MySQL versions is noticeable on the same hardware and OS version,
which suggests that the problem is different from bug#24637 describing performance
problem on SUSE 10.1.

Other OLTP scenarios do not seem to be affected.

The problem appears reliably enough on SLES 10 and SLES 10 PATCHLEVEL 2, but not so
obviously on RHEL 5 (might not exist there). 

Average txn per second count from several runs for different versions on 128 client
threads:

Box #1
SUSE 10, 2.6.16.60-0.21
5.0.67: 10351
5.0.72: 10339
5.0.74:  9534
5.0.77:  9568

Box #1
RHEL 5.2, 2.6.18-92.1.18.el5
5.0.67: 13268
5.0.72: 12803
5.0.74: 12705 
5.0.77: 13029 

Box #2
SUSE 10.2, 2.6.16.60-0.21
5.0.67: 14865
5.0.72: 14644
5.0.74: 13848
5.0.77: 13953

Box #3
RHEL 5.2, 2.6.18-92.1.10.el5
5.0.67: 14593
5.0.72: 14843
5.0.74: 14438
5.0.77: 14781

defaults-file:

[mysqld]
user=root
port=3306
max_connections=1600
max_connect_errors=10
table_cache=2048
max_allowed_packet=1048576
binlog_cache_size=1048576
max_heap_table_size=67108864
sort_buffer_size=65536
join_buffer_size=1048576
thread_cache=16
thread_stack=64K
query_cache_size=0
ft_min_word_len=4
transaction_isolation=REPEATABLE-READ
tmp_table_size=64M
skip-locking
server-id=1
innodb_status_file=0
innodb_data_file_path=ibdata1:100M:autoextend
innodb_buffer_pool_size=1300M
innodb_additional_mem_pool_size=20M
innodb_log_file_size=100M
innodb_log_files_in_group=2
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=300
innodb_locks_unsafe_for_binlog=0
innodb_thread_concurrency=0
innodb_flush_method=O_DIRECT

How to repeat:
Run sysbench 0.4.x, test oltp, with --oltp-distinct-ranges 1:

sysbench-0.4.8 --num-threads 128 --test oltp --oltp-table-size 1000000 --oltp-dist-type
uniform --oltp-table-name sbtest --init-rng on --max-requests 0 --max-time 300
--mysql-host 127.0.0.1 --mysql-user root --mysql-port 3306 --mysql-db sbtest
--mysql-table-engine INNODB --oltp-point-selects 0 --oltp-simple-ranges 0
--oltp-sum-ranges 0 --oltp-order-ranges 0 --oltp-distinct-ranges 1 --oltp-skip-trx on
--oltp-read-only on run

Just to clarify this, SELECT_DISTINCT_RANGES is a scenario executing queries of the type:
SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c

Note on 'how to repeat':

To pre-configure a test run, sbtest database needs to be created, and sysbench needs to
be executed in prepare mode.

<start server>
<create database sbtest>
sysbench <options> prepare
sysbench <options> run

Creates a database, a table (sysbench-like), and populates the table

Attachment: 42908_prepare.sql (application/octet-stream, text), 784 bytes.

Runs a few sysbench-like SELECT DISTINCT queries along with EXPLAIN

Attachment: 42908_run.sql (application/octet-stream, text), 872 bytes.

Result of 42908_run.sql execution on box #2

Attachment: 42908_run_result.txt (text/plain), 9.62 KiB.

The attached scripts can be used to reproduce the problem without sysbench. They use wider
ranges in SELECT, so the difference is visible on single queries.

- start the server;
- use prepare script to create and populate the table;
     mysql -uroot -n -vvv < ./42908_prepare.sql
- restart the server (not strictly necessary, but it makes the results clearer);
- use run script 
     mysql -uroot -n -vvv < ./42908_run.sql

It executes several queries of the same type as the sysbench scenario does, but with
bigger ranges for BETWEEN.
Each query is preceded by EXPLAIN.

The result is also attached, as it does not fit into the comment size.
In summary, while there seems to be no difference in explain output, execution time shows
that the same queries on 5.0.74 work slower than on 5.0.72.