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