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

  • I assume you're using InnoDB? – Jacobm001 Feb 20 '17 at 16:33
  • Yes sir... innodb_file_per_table is on – Younes Feb 20 '17 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 '17 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 '17 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 '17 at 20:27
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.)

  • 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 '17 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 '17 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 '17 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 '17 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 '17 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.

  • innodb_stats_persistent is already at "ON", and always been. – Younes Feb 20 '17 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 '17 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 '17 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 '17 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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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