We have a mySQL backed SaaS application where each customer has their own mySQL database. So think of it like a wordpress/magento etc. database for each client.
Each database has ~200 table, and we expect to have about 1,000 databases per server, so 200,000+ tables. We currently have innodb_file_per_table enabled.
Our question is regarding whether we should use innodb_file_per_table or not. We’ve seen some conflicting information on this:
Performance: With the large number of tables, is it better or worse to use innodb_file_per_table. We understand there can be issues with the number of file pointers required to have hundreds of thousands of tables open.
Stability: There is some discussion that using innodb_file_per_table is safer or improves crash recorvery
Concurrency: Some reports say that using innodb_file_per_table improves concurrency because there are individual tablespaces.
I presume nothing has come from innodb_file_per_database. Facebook talked about this back in 2009 (!) but I’ve not seen anything since. https://www.facebook.com/notes/mysql-at-facebook/hack-of-the-day/165403635932?comment_id=6285127
[/LIST] Welcome any thoughts on this.