MySQL Bugs: #106452: Very long upgrade time MySQL from 5.7 to 8.0, both FreeBSD and Ubuntu GNU/Linux

Bug #106452	Very long upgrade time MySQL from 5.7 to 8.0, both FreeBSD and Ubuntu GNU/Linux
Submitted:	14 Feb 2022 10:53	Modified:	21 Apr 2022 7:08
Reporter:	Ludvig Beethoven	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	8.0	OS:	FreeBSD
Assigned to:		CPU Architecture:	x86

Description:
Very long upgrade time

MySQL from 5.7 to 8.0
26GB, 28 thousands tables

FreeBSD X 13.0-RELEASE-p6 FreeBSD 13.0-RELEASE-p6 #2 X: Wed Jan 12 02:29:36 UTC 2022

log:

2021-12-08T05:36:26.476797Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2021-12-08T05:36:26.476860Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-12-08T05:36:31.607469Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-12-08T10:28:50.243784Z 2 [Warning] [MY-012152] [InnoDB] Open files 4006 exceeds the limit 4000
2021-12-08T10:40:34.095317Z 0 [Warning] [MY-012152] [InnoDB] Open files 4006 exceeds the limit 4000
2021-12-08T16:55:13.336394Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2021-12-08T17:04:39.150312Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80026' started.
2021-12-08T23:13:55.141459Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80026' completed.
2021-12-08T23:14:02.574185Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-12-08T23:14:03.503942Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2021-12-08T23:14:04.436519Z 0 [Warning] [MY-013414] [Server] Server SSL certificate doesn't verify: unable to get issuer certificate
2021-12-08T23:14:05.395152Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-12-08T23:14:09.679268Z 0 [System] [MY-010931] [Server] /usr/local/libexec/mysqld: ready for connections. Version: '8.0.26'  socket: '/mysql-dump/mysql-dump.sock'  port: 0  Source distribution.
2021-12-08T23:14:09.679279Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /tmp/mysqlx.sock

Similar long upgrade time was also observed on system:
Linux 0 5.4.0-91-generic #102-Ubuntu SMP Fri Nov 5 16:31:28 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

How to repeat:
Run mysql upgrade.

Upgrade operation is single threaded at the moment. There are many low hanging fruits with them upgrade time could be reduced drastically.

Hi Mr. van Beethoven,

Thank you for your performance improvement report.

However, what you propose is not doable when you upgrade from 5.7 to 8.0.

Multi-threaded performance for this upgrade is not worth the effort, as there are so many locks that are held during the upgrade to the data dictionary system that is the basis for the version 8.0.

The only way to get multi-threaded performance is to use mysqlpump program to dump all the schemas, except for mysql. Then, to upgrade to 8.0 and restore all schema with parallel processes.

Not a bug.

As of versions 8.0.27 and 8.0.28 this issue no longer occurs and actualization of the same data takes only 7 minutes.

Hi Mr. Beethoven,

Thank you for your feedback.