MySQL Bugs: #106269: Startup was very slow on instances with a large number of tables

Bug #106269	Startup was very slow on instances with a large number of tables
Submitted:	25 Jan 2022 5:33	Modified:	25 Jan 2022 16:04
Reporter:	Charles Rodrigues	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.37	OS:	Windows (Server 2012 R2)
Assigned to:		CPU Architecture:	x86 (64 bits)
Tags:	MySQL very very slow to start thousands of tables

Description:
Servidor Windows Server 2012 R2, 128 GB
MySQL 5.7.37
Storage 12.000 Iops
Cluster with 490 schemas with 1.31TB in size
All schemas have month partitioned tables
Tables (87.830) + Partitions (221.612) = 309.442

MySQL very but very slow to start, takes more than 1h8min thousands of tables

I noticed that MySQL takes a long time to check the .ibd files.

Strange is that this error did not occur in version 5.5.x

How to repeat:
start time log:

2022-01-25T03:48:18.364187Z 0 [Note] C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld (mysqld 5.7.37-log) starting as process 3428 ...
2022-01-25T03:48:18.416645Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2022-01-25T03:48:18.419813Z 0 [Note] InnoDB: Uses event mutexes
2022-01-25T03:48:18.422111Z 0 [Note] InnoDB: _mm_lfence() and _mm_sfence() are used for memory barrier
2022-01-25T03:48:18.423552Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-01-25T03:48:18.425227Z 0 [Note] InnoDB: Number of pools: 1
2022-01-25T03:48:18.426309Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2022-01-25T03:48:18.435129Z 0 [Note] InnoDB: Initializing buffer pool, total size = 98G, instances = 8, chunk size = 128M
2022-01-25T03:48:19.285765Z 0 [Note] InnoDB: Completed initialization of buffer pool
2022-01-25T03:48:19.966425Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2022-01-25T04:55:39.008831Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-01-25T04:55:39.011984Z 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-01-25T04:55:39.046340Z 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2022-01-25T04:55:39.101014Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2022-01-25T04:55:39.102477Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2022-01-25T04:55:39.104676Z 0 [Note] InnoDB: Waiting for purge to start
2022-01-25T04:55:39.155742Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4039776ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2022-01-25T04:55:39.169607Z 0 [Note] InnoDB: 5.7.37 started; log sequence number 39192094134153
2022-01-25T04:55:39.171404Z 0 [Note] InnoDB: Loading buffer pool(s) from E:\MySQLDatafiles\Data\ib_buffer_pool
2022-01-25T04:55:39.172522Z 0 [Note] Plugin 'FEDERATED' is disabled.
2022-01-25T04:56:01.317540Z 0 [Note] Auto generated SSL certificates are placed in data directory.
2022-01-25T04:56:02.904770Z 0 [Note] Auto generated RSA key files are placed in data directory.
2022-01-25T04:56:02.906715Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2022-01-25T04:56:02.908208Z 0 [Note] IPv6 is available.
2022-01-25T04:56:02.909195Z 0 [Note]   - '::' resolves to '::';
2022-01-25T04:56:02.910187Z 0 [Note] Server socket created on IP: '::'.
2022-01-25T04:56:02.911634Z 0 [Note] Shared memory setting up listener

Hi Mr. Rimiru,

Thank you, very much, for your bug report.

However, this is not a bug.

With that number of tables and partitions, this is expected behaviour. It is possible to tune MySQL and your OS so that this time gets much shorter. Many of those tips are described throughout our Reference Manual.

Not a bug.

We must also add that 8.0 offers some improvements, especially with variable tuning. MySQL 8.0 does not have to read .frm file, but simply accesses data dictionary. That is also described in 8.0 Reference Manual.

Not a bug.

Hi team,

What adjustments can I make to MySQL and the operating system to make this time much shorter, this has happened since I switched to version 5.7, but now it's unsustainable. I've already made several changes to my.ini and none took effect.

I don't believe it's expected because in MySQL 5.5 with the same tables this didn't happen.

This bug is reported here https://bugs.mysql.com/bug.php?id=96340 and it doesn't seem to be fixed.

Hi,

This is not a bug , but a question about MySQL configuration, hence please read carefully next comment.

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.

I don't understand, as it takes more than an hour for MySQL to call not a performance issue/bug.

Perhaps on 8.0 this feature can help:
https://dev.mysql.com/doc/refman/8.0/en/innodb-disabling-tablespace-path-validation.html

Windows did not work the parameter.

I tested it on Linux and it's ok.

WINDOWS:

2023-09-29T21:09:13.792848Z 1 [Note] [MY-012976] [InnoDB] 8.0.30 started; log sequence number 1507340063444
2023-09-29T21:09:13.813251Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-09-29T21:09:13.843588Z 1 [Note] [MY-011089] [Server] Data dictionary restarting version '80023'.
2023-09-29T21:09:18.540112Z 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files
2023-09-29T21:09:34.862988Z 0 [Note] [MY-012350] [InnoDB] 8threads have validated 2965 out of 235883 tablespaces so far.
2023-09-29T21:09:44.873031Z 0 [Note] [MY-012350] [InnoDB] 8threads have validated 6009 out of 235883 tablespaces so far.
2023-09-29T21:09:54.873167Z 0 [Note] [MY-012350] [InnoDB] 8threads have validated 8180 out of 235883 tablespaces so far.

LINUX:

2023-09-29T20:51:22.708730Z 1 [Note] [MY-012976] [InnoDB] 8.0.32 started; log sequence number 1499332870
2023-09-29T20:51:22.711154Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-09-29T20:51:22.759919Z 1 [Note] [MY-011089] [Server] Data dictionary restarting version '80023'.
2023-09-29T20:51:23.214517Z 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files

2023-09-29T20:51:23.222525Z 1 [Note] [MY-013604] [InnoDB] Skipping InnoDB tablespace path validation. Manually moved tablespace files will not be detected!

2023-09-29T20:51:23.223247Z 1 [Note] [MY-012356] [InnoDB] Scanned 35 tablespaces. Validated 2. Skipped 33.

Hi Mr. Rodriguez,

You should then tune better your Windows OS and / or MySQL server.

You can read more about it in our Reference Manual.

In both cases , what you are reporting is not a bug.

But the innodb_validate_tablespace_paths parameter is not working on Windows

Hi Mr. Rodrigues,

That option does work on Windows as well.

But, you must make sure that it is ON and that log verbosity is 3.

Also, you are not supposed to move manually tablespace files at all !!!!!

Last, but not least, 5.7 is not maintained any more.

Hence, test cases are accepted only for 8.0 and higher.

Hello,

It is connected and I checked when the bank that is in Windows turned on.

But you can see in the log that on Windows the message: Skipping InnoDB tablespace path validation did not appear. Manually moved tablespace files will not be detected!

On Windows it validated all tables even with the variable innodb_validate_tablespace_paths = ON, you can check the log I sent: according to the log 8threads have validated 8180 out of 235883 tablespaces so far.

I tested 3 times to be sure on Windows but it didn't work.

Hi Mr. Rodrigues,

What you describe is possible on 5.7, but not on 8.0.

5.7 is not maintained any more.

I received the MySQL Team Link
Perhaps on 8.0 this feature can help:
https://dev.mysql.com/doc/refman/8.0/en/innodb-disabling-tablespace-path-validation.html

So I tested enabling the variable on Linux and Windows, both systems running MySQL 8 as you can see in the Log. But in MySQL 8 on Windows the variable that the Team used to solve my problem does not work on the Windows Operating System.

You can do the test yourself and you will notice that in Windows the ON variable is not activated.