Bug #107676 MySQL upgrade from 5.7.35 to 8.0.28-commercial is very slow
Submitted: 28 Jun 2022 7:28 Modified: 29 Jun 2022 12:40
Reporter: VISHRUT GOYAL Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:8.0.28 OS:Windows (Windows server 2019 standard (version 1809))
Assigned to: CPU Architecture:Other

[28 Jun 2022 7:28] VISHRUT GOYAL
Description:
Inplace upgrading MySQL from 5.7.35 to 8.0.28-commercial is very slow, it is taking 1 hour and 9 mins to complete the upgrade.

How can we reduce the in-place upgrade time?

Machine Details - 
OS Name	Microsoft Windows Server 2019 Standard
Version	10.0.17763 Build 17763
Other OS Description 	Not Available
OS Manufacturer	Microsoft Corporation
System Manufacturer	Dell Inc.
System Model	PowerEdge R730
System Type	x64-based PC
System SKU	SKU=NotProvided;ModelName=PowerEdge R730
Processor	Intel(R) Xeon(R) CPU E5-2687W v4 @ 3.00GHz, 3000 Mhz, 12 Core(s), 24 Logical Processor(s)
Processor	Intel(R) Xeon(R) CPU E5-2687W v4 @ 3.00GHz, 3000 Mhz, 4 Core(s), 8 Logical Processor(s)
BIOS Version/Date	Dell Inc. 2.1.7, 6/16/2016
SMBIOS Version	2.8
Embedded Controller Version	255.255
BIOS Mode	Legacy
BaseBoard Manufacturer	Dell Inc.
BaseBoard Product	072T6D
BaseBoard Version	A01
Platform Role	Enterprise Server
Secure Boot State	Unsupported
PCR7 Configuration	Not Available
Windows Directory	C:\Windows
System Directory	C:\Windows\system32
Boot Device	\Device\HarddiskVolume1
Locale	United States
Hardware Abstraction Layer	Version = "10.0.17763.1852"
User Name	Not Available
Time Zone	Pacific Daylight Time
Installed Physical Memory (RAM)	128 GB
Total Physical Memory	128 GB
Available Physical Memory	96.7 GB
Total Virtual Memory	136 GB
Available Virtual Memory	25.5 GB
Page File Space	8.00 GB
Page File	D:\pagefile.sys
Kernel DMA Protection	Off
Virtualization-based security	Not enabled
Device Encryption Support	Not Available
Hyper-V - VM Monitor Mode Extensions	Yes
Hyper-V - Second Level Address Translation Extensions	Yes
Hyper-V - Virtualization Enabled in Firmware	Yes
Hyper-V - Data Execution Protection	Yes

How to repeat:
1. Install MySQL 8.0.28-commercial to F:\MySQL
2. Stop MySQL service
3. Rename MySQL 8.0.28 data F:\MySQLData\data directory to  F:\MySQLData\data_backup 
4. Move existing MySQL 5.7.35 data directory from F:\MySQLData_v5735\data to F:\MySQLData\
5. Edit  F:\MySQL\bin\my.ini file to use custom configuration as given in Private comment below
6. Start the MySQL 8.0.28 Service

MySQL service took 1 hour and 9 mins to start, Please refer to below MySQL logs. 

MySQL 8.0.28 logs - 

2022-06-28T02:01:47.511673Z 0 [System] [MY-010910] [Server] F:\MySQL\bin\mysqld.exe: Shutdown complete (mysqld 8.0.28-commercial)  MySQL Enterprise Server - Commercial.
2022-06-28T02:12:28.165740Z 0 [Warning] [MY-010086] [Server] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-06-28T02:12:28.165774Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2022-06-28T02:12:28.165982Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2022-06-28T02:12:28.166070Z 0 [System] [MY-010116] [Server] F:\MySQL\bin\mysqld.exe (mysqld 8.0.28-commercial) starting as process 25108
2022-06-28T02:12:28.186874Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2022-06-28T02:12:28.187123Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-06-28T02:13:34.544515Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-06-28T03:02:47.419189Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2022-06-28T03:11:21.217378Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80028' started.
2022-06-28T03:19:22.475008Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80028' completed.
2022-06-28T03:19:23.766806Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-06-28T03:19:23.767084Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-06-28T03:19:23.956829Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
2022-06-28T03:19:23.957039Z 0 [System] [MY-010931] [Server] F:\MySQL\bin\mysqld.exe: ready for connections. Version: '8.0.28-commercial'  socket: ''  port: 3306  MySQL Enterprise Server - Commercial.

Suggested fix:
No fix available
[28 Jun 2022 7:31] VISHRUT GOYAL
Note: Size of F:\MySQLData_v5735\data directory is 1 TB

We will be having MySQLData directories of more than 2.5 TB in production environments.
[29 Jun 2022 10:39] Rahul Sisondia
There is scope of parallelisation of at least steps in the upgrade process.
That will reduce the upgrade duration significantly.
[29 Jun 2022 10:40] Rahul Sisondia
I meant at least three steps.
[29 Jun 2022 12:40] MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.