Not the answer you need?
Register and ask your own question!

innodb_file_per_table for single tenant SaaS

mysqlsaasmysqlsaas EntrantCurrent User Role Beginner
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
Welcome any thoughts on this.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.