Bug #94387 MySQL 8.0.15 is slower than MySQL 5.7.25 in read only workloads
Submitted: 18 Feb 17:41 Modified: 21 Feb 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 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 13:39] Sinisa Milivojevic
Hi,

Thank you for your report.

Verified as reported.
[21 Feb 13:40] Sinisa Milivojevic
This is in-memory only benchmark.
[23 May 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 :)