performance drop on mySQL 8 vs mySQL 5.7 (and mariadb, postgresql)

A massive performance drop (60-90%) is observed on MySQL 8 in comparison to MySQL 5.7 or other databases (MariaDB or PostgreSQL).

Both databases use out of the box configuration except of innodb_flush_log_at_trx_commit=2 being added. But without this setting, we also have big performance difference.

I narrowed down the offending query to UPDATE schema_version on our test setup

• on mysql 5.7 query is made about 1.6 times per 1k processed leases and takes about 224us
• on mysql 8 query is made about 229 times per 1k processed leases and takes about 30ms

Looks like most of the queries are slower.

on mysql 5.7 the longest query was 2s, and 95% of queries were 167us
on mysql 8 the longest query was 2s, and 95% of queries were 31ms - thats huge

on mysql 8 INSERT INTO lease4 takes around 36ms
on mysql 5.7 INSERT INTO lease4 takes around 214us

MySQL 8
229 query per 1k leases, 95percentile 30ms (837 leases processed at 1500/s, drop rate 95%):

# Query 3: 0.06 QPS, 0.00x concurrency, ID 0x9D37850BFEFB2411FC8CD039CCABE4BB at byte 20172
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-01-09T09:55:16 to 2023-01-09T10:44:41
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2     192
# Exec time      4      3s    10ms    37ms    18ms    30ms     6ms    13ms
# Lock time      2     2ms       0    57us     8us    12us     5us     7us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine  30     384       2       2       2       2       0       2
# Query size     0   9.98k      52      57   53.21   56.92    2.16   51.63
# String:
# Databases    keatest
# Hosts        localhost
# Users        keatest
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `keatest` LIKE 'schema_version'\G
#    SHOW CREATE TABLE `keatest`.`schema_version`\G
UPDATE schema_version
SET version = '3', minor = '0'\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select  version = '3', minor = '0' from schema_version \G

MySQL 5.7
1.6 query per 1k leases, 95percentile 224us (about 15k leases processed at 1500/s, drop rate 20%):

# Query 122: 1 QPS, 0.00x concurrency, ID 0x9D37850BFEFB2411FC8CD039CCABE4BB at byte 535872
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-01-09T10:35:48 to 2023-01-09T10:36:12
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      24
# Exec time      0     3ms    86us   242us   143us   224us    43us   131us
# Lock time      0   874us    21us    49us    36us    44us     7us    36us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0      48       2       2       2       2       0       2
# Query size     0   1.25k      52      57   53.21   56.92    2.16   51.63
# String:
# Databases    keatest
# Hosts        localhost
# Users        keatest
# Query_time distribution
#   1us
#  10us  #########
# 100us  ################################################################
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `keatest` LIKE 'schema_version'\G
#    SHOW CREATE TABLE `keatest`.`schema_version`\G
UPDATE schema_version
SET version = '9', minor = '0'\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select  version = '9', minor = '0' from schema_version \G

All queries performance log (using pt-query-digest)
Please note that the test procedure drops and creates a database on test start, so there are queries from this procedure.

mysql8-slow.log

mysql57-slow.log

kea-config.txt

mysql8-slow-full_run.log

mysql57-slow-full_run.log