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
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