We have the following scenario: Our databases are hosted on AWS MySQL 5.6, and we are planning the following migration: we currently have 10 distinct instances with about 50 databases split between them, and we desire to migrate to a single instance containing all 50 databases. Each database has about 600 tables. All bases have the same structure, although the volume of data and traffic is different for each one of them. The main purpose of the change is to stack up the resource consumption curves of our different clients, setting up a server properly with fewer resources than the sum of the 10 current instances.

A point that is not standardized between instances, and what I need to define at this point, is the innodb_file_per_table parameter (which will define whether to use a shared tablespace, or individual tablespace for each table). I could not find enough information to decide on it.

The materials I've read talk about advantages for disaster recovery, however our AWS environment already has adequate redundancy, so I want to decide according to the performance (latency, IOPS, amount of open connections, ...). Our application does not perform so much DROP TABLE operations, and we are aware of the specifications of memory, CPU, network, etc., necessary for our centralized instance. Our focus is mainly quick response for our clients.

Based on the "Potential Disadvantages of File-Per-Table Tablespaces" section of the documentation at https://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html, using a shared tablespace can bring more speed in writing operations.

Further information is that after we complete the unification of the databases and make some adjustments, we have plans to upgrade from MySQL 5.6 to MySQL 5.7. Thus, the issue of the limit of 10000 tables for instances above 200 GiB is not at all relevant.

We thank you in advance for any suggestions on the best option to adopt, or the indication of materials that help in this decision.


Complex answer

All tables are "small"? Use =0.
All tables are "large"? Use =1.
A mixture? You could do some one way, some the other. Such as using file_per_table=1 for tables over 10MB.

Simple answer

OK, that is more complexity than you would like. In that case, let's go with the "short answer": Use whatever AWS has it set to. It won't matter much.

In particular, none of the metrics you quoted raise any alarms saying that you must go one way or the other.

  • Thanks for your answer! Here in our company we have talked about this, and we've decided to try file_per_table=0 for all. We'll be applying it until the end of this month. After that, I plan to post here the observed results, just for reference for someone looking for help with this, like I did. – Ulisses Oct 22 '18 at 19:38
  • That's the old way to do things. The main disadvantage comes when (if) you delete lots of rows in a table (or otherwise shrink it); =0 does provide a convenient way to give the freed space back to the user. InnoDB lived for a decade that way, so it is not "terrible". – Rick James Oct 23 '18 at 4:44

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.