MySQL 8.0 performance degradation compared to 5.7

[JOSEPH MASK]

I realize there are many threads on this subject, and I've read as many as looked relevant and noted the suggestions and findings, and decided to post for help with our specific use case.

We are preparing to upgrade from Percona Server 5.7.41-44 to Percona Server 8.0.32-24, and began with some basic performance comparisons using queries gathered from a production server on 5.7.41-44 -- all on CentOS 7 Linux in GCP.

We created 2 test servers on GCE instances with 4 vCPU, 26GB RAM, and network-attached SSD (GCE pd-ssd). The data disks for these test servers were created from a snapshot of a replica, and then one of them was left on 5.7.41-44 while the other one was upgraded to 8.0.32-24.

We captured 15 minutes of production traffic on the production primary database server to the slow query log by setting long_query_time=0, then filtered the writes out of the log using an in-house tool (we want to make some progress with read performance before addressing writes), and then ran the reads against test servers by simply catting the filtered file to the mysql command line, locally on each test server, 2 iterations each.

The timed results were:
5.7:
iteration 1:

real    10m12.692s
user    0m3.405s
sys     0m3.360s

iteration 2:

real    5m42.894s
user    0m3.362s
sys     0m3.480s

8.0:
iteration 1:

real    16m53.958s
user    0m5.567s
sys     0m3.844s

iteration 2:

real    7m59.726s
user    0m5.099s
sys     0m3.432s

The test servers were also running with long_query_time=0 so we could run pt-query-digest on the resulting slow query logs. The pt-query-digest results shows a fairly even distribution of the ~60% performance degradation across all of the queries that made it into the report.

5.7 pt-query digest summary:

# 57.6s user time, 360ms system time, 36.30M rss, 230.83M vsz
# Current date: Tue Mar 28 12:45:18 2023
# Hostname: db-replica2-106
# Files: /var/app.dbserver/mysqlSlow.log
# Overall: 275.85k total, 193 unique, 106.06 QPS, 0.36x concurrency ______
# Time range: 2023-03-28T16:00:00 to 2023-03-28T16:43:21
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           941s     1us     31s     3ms     1ms    73ms    10us
# Lock time             4s       0   672us    13us    63us    24us       0
# Rows sent        931.63k       0   1.92k    3.46    0.99   49.84       0
# Rows examine     249.14M       0 717.12k  947.04   80.10  11.97k       0
# Rows affecte           0       0       0       0       0       0       0
# Bytes sent        95.28M       0  46.26k  362.19   1.46k   1.45k   10.84
# Merge passes           0       0       0       0       0       0       0
# Tmp tables         9.17k       0       2    0.03       0    0.18       0
# Tmp disk tbl         188       0       1    0.00       0    0.03       0
# Tmp tbl size      11.83G       0   4.00M  44.96k       0 412.40k       0
# Query size         8.04M       0   3.17k   30.57  166.51   62.73       0
# InnoDB:
# IO r bytes        11.46G       0 816.83M 162.07k  62.55k   3.44M       0
# IO r ops         733.40k       0  51.05k   10.13    3.89  219.30       0
# IO r wait           375s       0     27s     5ms     2ms   114ms       0
# pages distin       9.92M       1   8.00k  140.26  964.41  421.29    3.89
# queue wait             0       0       0       0       0       0       0
# rec lock wai           0       0       0       0       0       0       0
# Boolean:
# Filesort       1% yes,  98% no
# Full join      0% yes,  99% no
# Full scan      3% yes,  96% no
# Tmp table      3% yes,  96% no
# Tmp table on   0% yes,  99% no

# Profile
# Rank Query ID           Response time  Calls  R/Call V/M   Item
# ==== ================== ============== ====== ====== ===== =============
#    1 0xE45D53D3526B4A6A 182.3551 19.4%    898 0.2031  0.22 SELECT EmailSent EmailOpened
#    2 0x47C1014E6BC437DC 150.3445 16.0%   1508 0.0997  0.15 SELECT EmailOpened EmailSent
#    3 0xF3CB02AFA87DF3DD 127.1444 13.5%    898 0.1416  0.19 SELECT EmailSent SystemEmailClick
#    4 0xBA896C0A2A731734 126.4083 13.4%   1508 0.0838  0.29 SELECT EmailOpened EmailSent
#    5 0x37BEB8B4D67A3132  61.2993  6.5%     16 3.8312 15.56 SELECT EmailAddStatus ContactEmail QueuedEmail
#    6 0xB764C07E653F9B0D  47.6566  5.1%    304 0.1568  0.16 SELECT QueuedEmail
#    7 0x00FB195CE281100A  44.2354  4.7%    450 0.0983  0.20 SELECT EmailSent EmailBounce
#    8 0xF07C847FC8F294F6  41.4871  4.4%    450 0.0922  0.15 SELECT EmailSent SystemEmailClick
#    9 0xF539625A13DB7380  38.4670  4.1%     86 0.4473  0.03 SELECT FlowRecipient QueuedFlowItem
#   10 0x960083D1F0FAF36A  29.8625  3.2%    696 0.0429  0.25 SELECT QueuedEmail
#   11 0x2E6DA6A488025AAB  14.2222  1.5%    316 0.0450  0.00 SELECT QueuedEmail
#   12 0x4EC30753F8843A3E  13.2682  1.4%   1508 0.0088  0.13 SELECT SystemEmailClick EmailSent
#   13 0x00673C385B61092B  10.1354  1.1%   1508 0.0067  0.00 SELECT EmailOpened EmailSent
#   14 0x1FBB03FD3A03E755   6.9112  0.7%   1508 0.0046  0.04 SELECT SystemEmailClick EmailSent SystemEmailLink
#   17 0x50AC30C99210244D   5.3187  0.6%     34 0.1564  1.44 SELECT MailSent EmailSent EmailOpened
# MISC 0xMISC              41.6170  4.4% 264165 0.0002   0.0 <178 ITEMS>

8.0 pt-query digest summary:

# 60s user time, 220ms system time, 36.31M rss, 230.82M vsz
# Current date: Tue Mar 28 12:45:09 2023
# Hostname: db-replica3-106
# Files: /var/app.dbserver/mysqlSlow.log
# Overall: 275.85k total, 193 unique, 106.10 QPS, 0.57x concurrency ______
# Time range: 2023-03-28T16:00:07 to 2023-03-28T16:43:27
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          1473s     1us     48s     5ms     2ms   108ms    14us
# Lock time          114ms       0   138us       0     1us     1us       0
# Rows sent        931.77k       0   1.92k    3.46    0.99   49.85       0
# Rows examine     239.99M       0 717.12k  912.27   59.77  11.47k       0
# Rows affecte           0       0       0       0       0       0       0
# Bytes sent        95.20M       0  46.26k  361.88   1.46k   1.45k   10.84
# Merge passes           0       0       0       0       0       0       0
# Tmp tables         7.71k       0       2    0.03       0    0.17       0
# Tmp disk tbl           0       0       0       0       0       0       0
# Tmp tbl size     863.18M       0   7.97M   3.20k       0 139.16k       0
# Query size         8.04M       0   3.17k   30.57  166.51   62.73       0
# InnoDB:
# IO r bytes         9.08G       0 815.75M 128.40k  31.59k   3.20M       0
# IO r ops         581.05k       0  50.98k    8.03    1.96  203.55       0
# IO r wait           449s       0     42s     6ms     2ms   163ms       0
# pages distin       9.78M       1   8.00k  138.33  964.41  415.50    3.89
# queue wait             0       0       0       0       0       0       0
# rec lock wai           0       0       0       0       0       0       0
# Boolean:
# Filesort       1% yes,  98% no
# Full join      0% yes,  99% no
# Full scan      3% yes,  96% no
# Tmp table      2% yes,  97% no

# Profile
# Rank Query ID           Response time  Calls  R/Call V/M   Item
# ==== ================== ============== ====== ====== ===== =============
#    1 0xE45D53D3526B4A6A 358.2008 24.3%    898 0.3989  0.48 SELECT EmailSent EmailOpened
#    2 0xF3CB02AFA87DF3DD 275.7932 18.7%    898 0.3071  0.37 SELECT EmailSent SystemEmailClick
#    3 0x47C1014E6BC437DC 178.1534 12.1%   1508 0.1181  0.29 SELECT EmailOpened EmailSent
#    4 0xBA896C0A2A731734 164.2586 11.2%   1508 0.1089  0.50 SELECT EmailOpened EmailSent
#    5 0xB764C07E653F9B0D  87.7206  6.0%    304 0.2886  0.26 SELECT QueuedEmail
#    6 0x37BEB8B4D67A3132  69.5797  4.7%     16 4.3487 30.08 SELECT EmailAddStatus ContactEmail QueuedEmail
#    7 0x00FB195CE281100A  61.9901  4.2%    450 0.1378  0.26 SELECT EmailSent EmailBounce
#    8 0xF07C847FC8F294F6  56.4311  3.8%    450 0.1254  0.22 SELECT EmailSent SystemEmailClick
#    9 0xF539625A13DB7380  43.1789  2.9%     86 0.5021  0.05 SELECT FlowRecipient QueuedFlowItem
#   10 0x2E6DA6A488025AAB  39.6519  2.7%    316 0.1255  0.01 SELECT QueuedEmail
#   11 0x960083D1F0FAF36A  29.2407  2.0%    696 0.0420  0.23 SELECT QueuedEmail
#   12 0x4EC30753F8843A3E  18.5346  1.3%   1508 0.0123  0.19 SELECT SystemEmailClick EmailSent
#   13 0x00673C385B61092B  15.7984  1.1%   1508 0.0105  0.00 SELECT EmailOpened EmailSent
#   14 0x3541D156D38C009D   9.2246  0.6%   1508 0.0061  0.07 SELECT EmailAddHistory EmailSent
#   16 0x50AC30C99210244D   8.7379  0.6%     34 0.2570  2.44 SELECT MailSent EmailSent EmailOpened
# MISC 0xMISC              56.1291  3.8% 264166 0.0002   0.0 <178 ITEMS>

Here are the explains for the top query, as an example:
5.7

mysql> explain SELECT COUNT(EmailSent.EmailAddress) FROM EmailSent INNER JOIN EmailOpened ON SentId=EmailSent.Id WHERE EmailSent.MailBatchId=1964741;
+----+-------------+-------------+------------+------+---------------------+-------------+---------+--------------------+---------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys       | key         | key_len | ref                | rows    | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------------+-------------+---------+--------------------+---------+----------+-------------+
|  1 | SIMPLE      | EmailSent   | NULL       | ref  | PRIMARY,MailBatchId | MailBatchId | 4       | const              | 1031730 |   100.00 | NULL        |
|  1 | SIMPLE      | EmailOpened | NULL       | ref  | SentId              | SentId      | 9       | tnant.EmailSent.Id |       1 |   100.00 | Using index |
+----+-------------+-------------+------------+------+---------------------+-------------+---------+--------------------+---------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                      |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(`tnant`.`EmailSent`.`EmailAddress`) AS `COUNT(EmailSent.EmailAddress)` from `tnant`.`EmailSent` join `tnant`.`EmailOpened` where ((`tnant`.`EmailOpened`.`SentId` = `tnant`.`EmailSent`.`Id`) and (`tnant`.`EmailSent`.`MailBatchId` = 1964741)) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

8.0

mysql> explain SELECT COUNT(EmailSent.EmailAddress) FROM EmailSent INNER JOIN EmailOpened ON SentId=EmailSent.Id WHERE EmailSent.MailBatchId=1964741;
+----+-------------+-------------+------------+--------+---------------------+-------------+---------+--------------------+---------+----------+-------------+
| id | select_type | table       | partitions | type   | possible_keys       | key         | key_len | ref                | rows    | filtered | Extra       |
+----+-------------+-------------+------------+--------+---------------------+-------------+---------+--------------------+---------+----------+-------------+
|  1 | SIMPLE      | EmailSent   | NULL       | ref    | PRIMARY,MailBatchId | MailBatchId | 4       | const              | 1031730 |   100.00 | NULL        |
|  1 | SIMPLE      | EmailOpened | NULL       | eq_ref | SentId              | SentId      | 9       | tnant.EmailSent.Id |       1 |   100.00 | Using index |
+----+-------------+-------------+------------+--------+---------------------+-------------+---------+--------------------+---------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                      |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(`tnant`.`EmailSent`.`EmailAddress`) AS `COUNT(EmailSent.EmailAddress)` from `tnant`.`EmailSent` join `tnant`.`EmailOpened` where ((`tnant`.`EmailOpened`.`SentId` = `tnant`.`EmailSent`.`Id`) and (`tnant`.`EmailSent`.`MailBatchId` = 1964741)) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain analyze SELECT COUNT(EmailSent.EmailAddress) FROM EmailSent INNER JOIN EmailOpened ON SentId=EmailSent.Id WHERE EmailSent.MailBatchId=1964741;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(EmailSent.EmailAddress)  (cost=2061339.87 rows=1) (actual time=3950.661..3950.662 rows=1 loops=1)
    -> Nested loop inner join  (cost=1958166.87 rows=1031730) (actual time=0.513..3939.475 rows=114611 loops=1)
        -> Index lookup on EmailSent using MailBatchId (MailBatchId=1964741)  (cost=1127024.85 rows=1031730) (actual time=0.479..2336.387 rows=551893 loops=1)
        -> Single-row covering index lookup on EmailOpened using SentId (SentId=EmailSent.Id)  (cost=0.71 rows=1) (actual time=0.003..0.003 rows=0 loops=551893)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (3.95 sec)

cnf files:
5.7
[mysqld]
auto_increment_increment=2
auto_increment_offset=2
basedir=/usr
binlog_format=ROW
binlog_rows_query_log_events=1
binlog_space_limit=300G
character_set_server=utf8mb3
collation_server=utf8mb3_unicode_ci
connect_timeout=30
core-file
datadir=/var/app.dbserver
default-storage-engine=innodb
default-time-zone=America/New_York
group_concat_max_len=16777215
innodb_buffer_pool_instances=4
innodb_buffer_pool_size=14G
innodb_data_file_path=ibdata1:32M:autoextend
innodb_file_format=barracuda
innodb_file_format_max=barracuda
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_log_buffer_size=8M
innodb_log_file_size=1G
innodb_log_write_ahead_size=4096
innodb_numa_interleave=ON
innodb_old_blocks_time=1000
innodb_purge_threads=1
innodb_stats_on_metadata=0
key_buffer_size=512M
local_infile=1
lock_wait_timeout=600
log_bin=/var/mysql_app_logs/masterLog
log-slave-updates
log_slow_verbosity=full
log_statements_unsafe_for_binlog=OFF
long_query_time=20
max_allowed_packet=32MB
max_connect_errors=9000000
max_connections=2048
max_heap_table_size=128M
max_tmp_tables=128
myisam_sort_buffer_size=32M
performance_schema=0
plugin_dir=/var/mysql-plugins
port=3306
query_cache_size=64M
read_buffer_size=4M
relay-log=/var/mysql_app_logs/relay
replicate-wild-ignore-table=%.tmpi\_%
replicate-wild-ignore-table=%.tmpNoRepl\_%
replicate-wild-ignore-table=%.TMPNOREPL\_%
report-host=db-replica2-106
secure_file_priv=
server-id=176160783
skip-name-resolve
skip-slave-start
slave_compressed_protocol=1
slave_skip_errors=ALL
slow-query-log=1
slow-query-log-file=mysqlSlow.log
socket=/var/run/mysqld/app.sock
sort_buffer_size=8M
sql_mode=NO_ENGINE_SUBSTITUTION
sync_binlog=0
table_definition_cache=20000
table_open_cache=80000
tmpdir=/tmp
tmp_table_size=128M
user=mysql
wait_timeout=1800

8.0
[mysqld]
auto_increment_increment=2
auto_increment_offset=2
basedir=/usr
binlog_format=ROW
binlog_rows_query_log_events=1
binlog_space_limit=300G
character_set_server=utf8mb3
collation_server=utf8mb3_unicode_ci
connect_timeout=30
core-file
datadir=/var/app.dbserver
default-storage-engine=innodb
default-time-zone=America/New_York
group_concat_max_len=16777215
innodb_buffer_pool_instances=4
innodb_buffer_pool_size=14G
innodb_data_file_path=ibdata1:32M:autoextend
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_log_buffer_size=8M
innodb_log_write_ahead_size=4096
innodb_numa_interleave=ON
innodb_old_blocks_time=1000
innodb_purge_threads=1
innodb_redo_log_capacity=2G
innodb_stats_on_metadata=0
key_buffer_size=512M
local_infile=1
lock_wait_timeout=600
log_bin=/var/mysql_app_logs/masterLog
log-slave-updates
log_slow_verbosity=full
log_statements_unsafe_for_binlog=OFF
long_query_time=20
max_allowed_packet=32MB
max_connect_errors=9000000
max_connections=2048
max_heap_table_size=128M
myisam_sort_buffer_size=32M
performance_schema=0
plugin_dir=/var/mysql-plugins
port=3306
read_buffer_size=4M
relay-log=/var/mysql_app_logs/relay
replicate-wild-ignore-table=%.tmpi\_%
replicate-wild-ignore-table=%.tmpNoRepl\_%
replicate-wild-ignore-table=%.TMPNOREPL\_%
report-host=db-replica3-106
secure_file_priv=
server-id=176160800
skip-name-resolve
skip-slave-start
slave_compressed_protocol=1
slave_skip_errors=ALL
slow-query-log=1
slow-query-log-file=mysqlSlow.log
socket=/var/run/mysqld/app.sock
sort_buffer_size=8M
sql_mode=NO_ENGINE_SUBSTITUTION
sync_binlog=0
table_definition_cache=20000
table_open_cache=80000
tmpdir=/tmp
tmp_table_size=128M
user=mysql
wait_timeout=1800

Just for kicks, I created the same tables in MyISAM and found the queries performed the same (worse of course) on both 5.7 and 8.0, so the performance delta seems to be in the innodb engine.

Thanks for looking at this and I'm happy to share more details. The goal is to significantly narrow this performance delta so we can confidently upgrade from 5.7 to 8.0. There aren't any 8.0 features we're reaching for, we just need to get away from 5.7 before it goes EOL, without noticeable performance degradation. The goal is not to refactor the queries from the application, but if there is a specific obvious optimization we should look at, we certainly can.