MySQL Bugs: #113711: Mrr implement runs much slower than before

Bug #113711	Mrr implement runs much slower than before
Submitted:	22 Jan 16:35	Modified:	28 Feb 14:19
Reporter:	toki ye (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	8.0.36	OS:	Linux (debian11)
Assigned to:		CPU Architecture:	x86
Tags:	Contribution, regression

Description:
Recently, select statement use mrr implement run slower than before. 

How to repeat:
Firstly, I write a patch to test how much time cost in mrr implement. Two different versions(8.0.35 and 8.0.36) install this patch for comparation. Before testing, I create a table and insert ten million rows which have random secondary index value range from 0 to 10000000 with equal probability. And the table is: 
CREATE TABLE test(
  id INTEGER AUTO_INCREMENT,
  k INTEGER DEFAULT '0' NOT NULL,
  c CHAR(120) DEFAULT '' NOT NULL,
  PRIMARY KEY (id),
  KEY mykey(k))Engine=InnoDB;

All of the version do the same statement for test:
set profiling=1;
set session optimizer_switch="mrr=on";
set session read_rnd_buffer_size=33445532;
select /*+ MRR(test) */ * from test where k between 0 and 500000 limit 500000;

After execution finished, check profile results for query:

MySQL8.0.36:
+--------------------------------+------------+
| Status                         | Duration   |
+--------------------------------+------------+
| starting                       |   0.000061 |
| Executing hook on transaction  |   0.000003 |
| starting                       |   0.000005 |
| checking permissions           |   0.000004 |
| Opening tables                 |   0.000029 |
| init                           |   0.000003 |
| System lock                    |   0.000006 |
| optimizing                     |   0.000007 |
| statistics                     |   0.000059 |
| preparing                      |   0.000016 |
| executing                      |   0.000043 |
| Fetching Primary Key           |   0.124798 |
| Sorting Primary Key            | 572.898504 |
| end                            |   0.000013 |
| query end                      |   0.000004 |
| waiting for handler commit     |   0.000010 |
| closing tables                 |   0.000014 |
| freeing items                  |   0.000265 |
| cleaning up                    |   0.000017 |
+--------------------------------+------------+

MySQL8.0.35:
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000067 |
| Executing hook on transaction  | 0.000002 |
| starting                       | 0.000005 |
| checking permissions           | 0.000004 |
| Opening tables                 | 0.000032 |
| init                           | 0.000004 |
| System lock                    | 0.000007 |
| optimizing                     | 0.000007 |
| statistics                     | 0.000061 |
| preparing                      | 0.000015 |
| executing                      | 0.000050 |
| Fetching Primary Key           | 0.129063 |
| Sorting Primary Key            | 2.684361 |
| end                            | 0.000015 |
| query end                      | 0.000005 |
| waiting for handler commit     | 0.000011 |
| closing tables                 | 0.000017 |
| freeing items                  | 0.007791 |
| cleaning up                    | 0.000020 |
+--------------------------------+----------+

MySQL 8.0.36 costs a lot of time for sorting primary key. And the result's source code is built by Debian 11 with gcc 10.

Suggested fix:
The primary key sort way for mrr implement in file varlen_sort.h has changed since 8.0.36. It seems that in the future version, It'll achieve a primary key sorting method much more suitable for MySQL Server. But now I find it does not run as fast as before when there are a lot of primary keys to deal with.

patch use for the test

Attachment: profile_test.patch (application/octet-stream, text), 2.65 KiB.

Hi Mr. ye,

Thank you for your bug report.

However, we cannot accept reports of the patched versions of our software.

If you can provide us with a full test case, without your patch, we would be happy to attempt to repeat the behaviour. 

Next, you have stated the Linux that you are using and CPU. Does it mean that this phenomena can be observed only on that platform ???

Thanks in advance .....

Thanks for your reply,I'm sorry for my lake of consideration about the test case.Now I download 8.0.35 and 8.0.36 debian 11 packages from www.mysql.com and use two of them for test.I write a lua script which can run by sysbench to repeat the test. 

My test is as follow:
1.Enter the Mysql that installed and create database for sysbench
mysql> create database sysbench;
2.Prepare the ten million rows with random secondary index.
"sysbench --threads=1 --time=600 --report-interval=100 --db-driver=mysql --mysql-host=127.0.0.1 --mysql-user=root --mysql-db=sysbench --mysql-password=password --tables=1 --table-size=10000000 --secondary_start=0 --secondary_end=10000000 --delta=65536 select_mrr_test.lua prepare"
3.Run the script to select data rows with mrr implment.
"sysbench --threads=1 --time=600 --report-interval=100 --db-driver=mysql --mysql-host=127.0.0.1 --mysql-user=root --mysql-db=sysbench --mysql-password=password --tables=1 --table-size=10000000 --secondary_start=0 --secondary_end=10000000 --delta=65536 select_mrr_test.lua run"
4.Clean up the test data
"sysbench --threads=1 --time=600 --report-interval=100 --db-driver=mysql --mysql-host=127.0.0.1 --mysql-user=root --mysql-db=sysbench --mysql-password=password --tables=1 --table-size=10000000 --secondary_start=0 --secondary_end=10000000 --delta=65536 select_mrr_test.lua cleanup"

And their results are:
In MySQL 8.0.36 deb package:
SQL statistics:
    queries performed:
        read:                            60
        write:                           0
        other:                           1
        total:                           61
    transactions:                        60     (0.10 per sec.)
    queries:                             61     (0.10 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          607.3168s
    total number of events:              60

Latency (ms):
         min:                                 9709.85
         avg:                                10121.92
         max:                                19838.67
         95th percentile:                    10158.80
         sum:                               607314.92

Threads fairness:
    events (avg/stddev):           60.0000/0.00
    execution time (avg/stddev):   607.3149/0.00

In MySQL 8.0.35 deb package:
SQL statistics:
    queries performed:
        read:                            1452
        write:                           0
        other:                           2
        total:                           1454
    transactions:                        1452   (2.42 per sec.)
    queries:                             1454   (2.42 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          600.1793s
    total number of events:              1452

Latency (ms):
         min:                                  393.74
         avg:                                  413.34
         max:                                10616.04
         95th percentile:                      411.96
         sum:                               600174.83

Threads fairness:
    events (avg/stddev):           1452.0000/0.00
    execution time (avg/stddev):   600.1748/0.00

The core of the statment that the script do is the format like "select /*+ MRR(`table`) */ * from `table` where `index` between ? and ? limit ?".From their performance, MySQL 8.0.36's Mrr implement runs much slower than MySQL8.0.35.

By the way, to change the number of rows lager for test, it's ok to change "delta" into a much larger number.

For the mechine that I use for test, It's Intel Ice Lake 2.8GHZ Cpu with height speed SSD.To be honest, I only test in debian 11.To find out whether other plamtform has such phenomena is still uncertain.

sysbench lua script for test case. also need oltp_common.lua in directory of sysbench/src/lua to run this script

Attachment: select_mrr_test.lua (application/octet-stream, text), 4.98 KiB.

Is it a personal phenomenon or a common phenomenon? If there any suggestion or practice, I would be glad to listen and learn about.

Hello toki ye,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

Test results - 8.0.36, 8.0.35

Attachment: 113711_8.0.36_8.0.35.results (application/octet-stream, text), 10.20 KiB.

Hello toki ye,

Thank you for the contribution.
Please don't forget to resend the patch via "Contribution" tab of this page otherwise we will not be able to use it. Thank  you.

regards,
Umesh

Sure, I would like to. And finally, I'm looking forward to MySQL becoming better and better! (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: profile_test.patch (application/octet-stream, text), 2.75 KiB.

Thank you for the contribution.

Sincerely,
Umesh

Thank you for your patch, our development team decided to not implement you code as it does not fix the bug, it adds profiling to show more clearly where the bug is. We chose not to add the extra profiling.

Thank you for supporting MySQL.

Hi!
Thanks for your reply about the patch.It was used to test the time cast of Mrr implement between 8.0.35 and 8.0.36. It made some extra profiles to show it's key sorting algorithm changed (8.0.35 uses quick sort that relies on standary c++ ---> 8.0.36 uses bubble sort 
that does not rely on standary c++) that made different results.
The optimizer that makes suitable algorithm not rely on standary c++ for Mrr implement, still needs official as well as open source developer to estimate and test.
I'll make some practice and test.And of cause, if there any progression that is related to this topic, I'll be glad to learn and listen about.

Hi Mr. ve,

We are looking forward to your future contributions.

Documented fix as follows in the MySQL 8.0.37 and 8.4.0 changelogs:

    The multi-range read (MRR) optimization did not perform as well
    as in previous releases.

Closed.

Thank you, Jon.

Hi,
I can't wait for seeing it works in the next new version!

Hi,
I can't wait for seeing it works in the next new version!

Hi,
I can't wait for seeing it works in the next new version!

Oh,
It seems there is some problem occurs for the internet.I submitted for once on the website, however, it made me three comments...

That is OK .....