3

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

Improve this question
12
  • I assume you're using InnoDB?
    – Jacobm001
    Feb 20, 2017 at 16:33
  • Yes sir... innodb_file_per_table is on
    – Younes
    Feb 20, 2017 at 16:34
  • 1
    The evil in me would create my own shortcut stat(), loaded using LD_PRELOAD,. I imagine it'd break things horribly though :) What filesysyem are you using & is it local or NFS/iSCSI/something else?
    – Philᵀᴹ
    Feb 20, 2017 at 19:01
  • 1
    @EvanCarroll I could try to explain it all, and have a group therapy on why it is like this, yet, it will not help my case at all... So If you don't mind, I will change the world tomorrow. Too busy today.
    – Younes
    Feb 20, 2017 at 20:02
  • 1
    @Younes Have you got the filesystem option noatime set? If not, you probably should. I'm not sure if it'll help with stat(), as i'm unsure of whether that updates the atime of the file, but it's worth a try
    – Philᵀᴹ
    Feb 20, 2017 at 20:27

3 Answers 3

Reset to default
4

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.)

Improve this answer
5
  • Plus to all described, plan D - redesign architecture! 1M tables easy could be similar tables created for each device (in case of IoT) or for each other type of tasks. So proper design - could reduce number of tables dramatically.
    – a_vlad
    Feb 21, 2017 at 1:53
  • I like you plan B! Here is my question: Having 1M tables or 5, why would mysql open all files and get their stat when you start the database? All answers question the design which is shady at best, no one seems to be interested in why mysql open all files when starting ...
    – Younes
    Feb 21, 2017 at 1:53
  • Jacobm001 too the approach that some setting led to opening all the tables. What version are you running? How many databases? Turn on the General log to see if anything is touching information_schema; that is almost guaranteed to open all the files.
    – Rick James
    Feb 21, 2017 at 5:18
  • @RickJames Now that I know exactly why I'm having issues, and that the problem has been mitigated, I'm moving with plan B. I created 6 General tablespaces, and dumping ~250k tables in each one of them. I will keep you posted. Still stuck with the frm files, then again, we're in a much better shape than before. Thanks
    – Younes
    Feb 24, 2017 at 0:27
  • @Younes - I think 8.0 will still have a file per table like the .frm -- sort of a backup of the schema, just in case the DataDictionary gets hosed. Still, 250K+1 is better than 250K*2 (.ibd+.frm).
    – Rick James
    Feb 24, 2017 at 0:37
1

It sounds like your database administrator has set the innodb_stats_persistent= value to OFF. Change this value to ON, and do a restart. The restart will take a long time as normal, but the following reboots should be much faster.

If this device is really a "blackbox" to you, I doubt you can change this yourself. I'd contact your DBA.

See the documentation for more details.

Improve this answer
4
  • innodb_stats_persistent is already at "ON", and always been.
    – Younes
    Feb 20, 2017 at 18:15
  • btw @jacobm001, thanks for being the 1st to really try to help rather than trying to find the funniest way to say this is stupid! :)
    – Younes
    Feb 20, 2017 at 18:26
  • @Younes: What is the value of innodb_stats_auto_update? You may want to set that and innodb_stats_on_metadata to 0.
    – Jacobm001
    Feb 20, 2017 at 19:05
  • innodb_stats_on_metadata is 0 by default, if you meant innodb_stats_auto_recalc, then that one is "on", I will try to turn it off. Thanks.
    – Younes
    Feb 20, 2017 at 19:58
1

This turned out to be 2 issues (other than millions of tables)!

  1. When MySQL start, and a crash recovery is needed, as of 5.7.17, it needs to traverse you datadir to build it's dictionary. This will be lifted in future releases(8.0), as MySQL will have it's own catalog, and will not rely on datadir content anymore. Thanks to 'Rick James' for this hint. Doc states that this isn't done anymore. It's true and false. It doesn't read the 1st page of ibd files, but It does a file stat. Filed Bug
  2. Once it finished (1), it starts a new process, "Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine.". That of course open all the files again. Use disable-partition-engine-check if you think you don't need it. Doc

All this can be observed using sysdig. very powerful handy dtrace like tool.

sysdig proc.name=mysqld | grep "open fd="

Ok, now, it's time to reduce the number of files. Again "Rick James", gave me some good solution....

Comments? Opinions?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.