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
