Bug #94387 MySQL 8.0.15 is slower than MySQL 5.7.25 in read only workloads
Submitted: 18 Feb 2019 17:41 Modified: 21 Feb 2019 13:39
Reporter: Vadim Tkachenko Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:8.0.15 OS:Ubuntu (18.04)
Assigned to: CPU Architecture:x86

[18 Feb 2019 17:41] Vadim Tkachenko
Description:
this time I decided to test very simple read-only CPU intensive workloads when all data fits memory. In this workload, there are NO IO operations, only memory, and CPU operations.
I am using “x2.xlarge.x86” instances from packet.net hosting, these instances provide 28 Physical Cores @ 2.2 GHz x 2  INTEL XEON GOLD 5120 CPU.

I will test two workloads, sysbench oltp_read_only and oltp_point_select varying amount of threads

sysbench oltp_read_only --mysql-ssl=off --report-interval=1 --time=300 --threads=$i --tables=10 --table-size=10000000 --mysql-user=root run
sysbench oltp_point_select --mysql-ssl=off --report-interval=1 --time=300 --threads=$i --tables=10 --table-size=10000000 --mysql-user=root run

The results
+---------+--------------+--------------+------------------+
| OLTP RO | MySQL 5.7.25 | MySQL 8.0.15 |                  |
+---------+--------------+--------------+------------------+
| threads | throughput   | throughput   | throughput ratio |
+---------+--------------+--------------+------------------+
| 1       | 1241.18      | 1114.4       | 1.11             |
+---------+--------------+--------------+------------------+
| 4       | 4578.18      | 4106.69      | 1.11             |
+---------+--------------+--------------+------------------+
| 16      | 15763.64     | 14303.54     | 1.10             |
+---------+--------------+--------------+------------------+
| 24      | 21384.57     | 19472.89     | 1.10             |
+---------+--------------+--------------+------------------+
| 32      | 25081.17     | 22897.04     | 1.10             |
+---------+--------------+--------------+------------------+
| 48      | 32363.27     | 29600.26     | 1.09             |
+---------+--------------+--------------+------------------+
| 64      | 39629.09     | 35585.88     | 1.11             |
+---------+--------------+--------------+------------------+
| 128     | 38448.23     | 34718.42     | 1.11             |
+---------+--------------+--------------+------------------+
| 256     | 36306.44     | 32798.12     | 1.11             |
+---------+--------------+--------------+------------------+

+--------------+--------------+--------------+------------------+
| point select | MySQL 5.7.25 | MySQL 8.0.15 |                  |
+--------------+--------------+--------------+------------------+
| threads      | throughput   | throughput   | throughput ratio |
+--------------+--------------+--------------+------------------+
| 1            | 31672.52     | 28344.25     | 1.12             |
+--------------+--------------+--------------+------------------+
| 4            | 110650.7     | 98296.46     | 1.13             |
+--------------+--------------+--------------+------------------+
| 16           | 390165.41    | 347026.49    | 1.12             |
+--------------+--------------+--------------+------------------+
| 24           | 534454.55    | 474024.56    | 1.13             |
+--------------+--------------+--------------+------------------+
| 32           | 620402.74    | 554524.73    | 1.12             |
+--------------+--------------+--------------+------------------+
| 48           | 806367.3     | 718350.87    | 1.12             |
+--------------+--------------+--------------+------------------+
| 64           | 1120586.03   | 972366.59    | 1.15             |
+--------------+--------------+--------------+------------------+
| 128          | 1108638.47   | 960015.17    | 1.15             |
+--------------+--------------+--------------+------------------+
| 256          | 1038166.63   | 891470.11    | 1.16             |
+--------------+--------------+--------------+------------------+

We can see that in OLTP read-only workload, MySQL 8.0.15 is slower by 10%, and in point_select workload MySQL 8.0.15 is slower by 12-16%.
Although the difference is not necessary significant, this is enough to notice that MySQL 8.0.15 does not perform as well as MySQL 5.7.25 in variety of workloads I am testing.

How to repeat:
My.cnf file
[mysqld]

datadir= {{ mysqldir }}
ssl=0

skip-log-bin
log-error=error.log

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 character_set_server=latin1
 collation_server=latin1_swedish_ci
 skip-character-set-client-handshake

 innodb_undo_log_truncate=off

# Recommended in standard MySQL setup

# general
  table_open_cache = 200000
  table_open_cache_instances=64
  back_log=3500
  max_connections=4000

# files
  innodb_file_per_table
  innodb_log_file_size=15G
  innodb_log_files_in_group=2
  innodb_open_files=4000

# buffers
  innodb_buffer_pool_size= 40G
  innodb_buffer_pool_instances=8
  innodb_log_buffer_size=64M

# tune
  innodb_doublewrite= 1 
  innodb_thread_concurrency=0 
  innodb_flush_log_at_trx_commit= 0
  innodb_flush_method=O_DIRECT_NO_FSYNC
  innodb_max_dirty_pages_pct=90
  innodb_max_dirty_pages_pct_lwm=10
  innodb_lru_scan_depth=2048
  innodb_page_cleaners=4
  join_buffer_size=256K
  sort_buffer_size=256K
  innodb_use_native_aio=1
  innodb_stats_persistent = 1
  #innodb_spin_wait_delay=96

# perf special
  innodb_adaptive_flushing = 1
  innodb_flush_neighbors = 0
  innodb_read_io_threads = 16
  innodb_write_io_threads = 16
  innodb_io_capacity=1500 
  innodb_io_capacity_max=2500
  innodb_purge_threads=4

  innodb_adaptive_hash_index=0

max_prepared_stmt_count=1000000

 innodb_monitor_enable = '%'
 performance_schema = ON

script I am using for testing:
run.sh:

HOST="--mysql-socket=/tmp/mysql.sock"
CONFIG=/etc/mysql/my.cnf

trap "trap - SIGTERM && kill -- -$$" SIGINT SIGTERM EXIT

initialstat(){
  cp $CONFIG $OUTDIR
  cp $0 $OUTDIR
}

collect_mysql_stats(){
  mysqladmin ext -i10 > $OUTDIR/mysqladminext.txt &
  PIDMYSQLSTAT=$!
}
collect_dstat_stats(){
  dstat --output=$OUTDIR/dstat.txt 10 > $OUTDIR/dstat.out &
  PIDDSTATSTAT=$!
}

runid="mysql80.point-select.BP"

# perform warmup
sysbench oltp_read_only --mysql-ssl=off --report-interval=1 --time=600 --threads=24 --tables=10 --table-size=10000000 --mysql-user=root run | tee -a res.warmup.ro.txt

for i in 1 4 16 24 32 48 64 128 256
do

        OUTDIR=res-OLTP-memory/$runid/thr$i
        mkdir -p $OUTDIR

        # start stats collection
        initialstat
        collect_mysql_stats 
        collect_dstat_stats 

        time=300
        sysbench oltp_point_select --mysql-ssl=off --report-interval=1 --time=$time --threads=$i --tables=10 --table-size=10000000 --mysql-user=root run |  tee -a $OUTDIR/res.txt

        # kill stats
        set +e
        kill $PIDDSTATSTAT
        kill $PIDMYSQLSTAT
        set -e

        sleep 30
done

Suggested fix:
Make MySQL 8 Great Again
MySQL 8.0 does not have to be slower than MySQL 5.7
[21 Feb 2019 13:39] MySQL Verification Team
Hi,

Thank you for your report.

Verified as reported.
[21 Feb 2019 13:40] MySQL Verification Team
This is in-memory only benchmark.
[23 May 2019 14:10] zhai weixiang
Is there Any analyze can share ??? We just find same problem while trying to prove that 8.0 is better  than older version  to our boss :)
[22 Sep 2021 9:31] Ajinkya Nopat
Is this issue fixed?
If yes, in which version?
We are facing similar issue, our observation is that MySQL8 is slower in reads than MySQL5.6
[22 Sep 2021 12:37] MySQL Verification Team
Hi,

No, this performance problem has not been addressed yet .....

We have no information when will it be addressed.

Generally, 8.0 is a bit slower then 5.7 in read only workloads. 

Also, it is wise to check improvements that are available in the recent versions.
[23 Sep 2021 15:19] Ajinkya Nopat
Thanks for the reply.
Yes We have tested on almost latest(MySQL8.0.25).
[7 Jun 5:51] Prashant Dixit
Hi,
 any update on this issue? Is it resolved ?

Thanks,
Prashant
[7 Jun 11:30] MySQL Verification Team
Hi,

No, this performance issue is not resolved. Simply, version 8.0 has introduced so many new features that have made the entire code much, much more complex. We are working on optimising some parts of the code, which can be improved, but these will not change much the overall performance.

On the other hand, 8.0 has so many new features, that you can learn them in use them to improve your SQL statements, which would lead to the higher performance of your installation.
[9 Jun 5:06] Radu Nicolae Rau
Greetings,

Could you be so kind to advise if this issue is being worked on?
It seams strange that this Bug is not assigned to anybody.

We are also using sysbench to run workloads, in order to determine if we should upgrade to MySQL8 and see the same issue.

Thank you for your time,
[9 Jun 11:58] MySQL Verification Team
Hi Mr. Rau,

First of all, the "Assigned to" field is not used for the last 15 (fifteen) years. Also, this is public bug reporting system, so the assignments and progress report are not published here, since that is internal matter, which stays out of the eyes of the public.

Regarding performance, version 8.0 has so many new features that would enable you to increase performance of many of your queries. Read our 8.0 Reference Manual, if you want to find out more.