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.