Bug #106452 Very long upgrade time MySQL from 5.7 to 8.0, both FreeBSD and Ubuntu GNU/Linux
Submitted: 14 Feb 10:53 Modified: 21 Apr 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

[14 Feb 10:53] Ludvig Beethoven
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.
[14 Feb 12:18] Rahul Sisondia
Upgrade operation is single threaded at the moment. There are many low hanging fruits with them upgrade time could be reduced drastically.
[14 Feb 14:24] MySQL Verification Team
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.
[21 Apr 7:08] Ludvig Beethoven
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.
[27 Apr 12:40] MySQL Verification Team
Hi Mr. Beethoven,

Thank you for your feedback.