MySQL – Startup Takes More Than an Hour

MySQLmysql-5.7perconapercona-server

I have a mysql (Percona) 5.7 instance with over 1Million tables. When I start the database, it can take more than an hour to start. Errorlog doesn't show anything, but when I trace mysqld_safe, I found out that MySQL is getting a stat on every file in the DB.

Any idea why this may happen? Also, please no suggestion to fix my schema, this is a blackbox. Also keep in mind that I've heard it all regarding how bad the design is….

Thanks

Best Answer

The solution is to drastically decrease the number of files. I see 3 approaches, depending on your version:

Plan A (the only option for 5.6 and earlier):

Set innodb_file_per_table=OFF, restart your client, then do ALTER TABLE ... ENGINE=InnoDB all but the 1000 largest tables. This will shovel all the non-huge tables into ibdata1.

Plan B (if you have at least 5.7, and you have 10-1000 databases):

If you have multiple databases, create a General Tablespace per database. Put non-huge tables into those tablespaces.

Leave the huge tables in their own tablespaces.

To facilitate either Plan A or B, write a SELECT against information_schema that creates the desired ALTER statements, then execute them.

Plan C: Move to 8.0, which might actually work reasonably fast with a million tables due to its Data Dictionary. (Or, you might need a combo of Plan C together with A or B.)

Related Question