Thousands of tables in a MySQL database
Just read about the potential performance implications of having thousands of tables in a single MySQL database over at Ask Bjørn Hansen’s blog.
We have that type of setup at Alert Logic, but I don’t remember any problems while shutting down the database server. I wonder if the FLUSH TABLES problem only happens that badly under MyISAM instead of InnoDB?
Mark Leith said,
February 17, 2008 @ 5:33 am
João!
Yes MyISAM would be affected worse than InnoDB in a default set up - as MyISAM as ‘multiple files per table’ (MYD/MYI/frm), whilst InnoDB stores all of it’s tables within a single (or a number of) data file(s). This leads to much less open file handles etc.
InnoDB could be affected just the same when using the innodb_file_per_table option however (which makes InnoDB act like MyISAM with the .ibd files).
Cheers!
Mark
Ask Bjørn Hansen said,
February 18, 2008 @ 3:03 am
Hi Mark,
There’s more than that going on though. Doing the flush’es individually is reasonably fast (less than a minute) where doing it as “flush tables” can take many many times as long.
- ask