Mysql – Recommended innodb_file_per_table value for AWS MySQL RDS database

awsinnodbMySQL

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.

Best Answer

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.

STEP #1

Set innodb_file_per_table to 0 in /etc/my.cnf

[mysqld]
innodb_file_per_table = 0

STEP #2

service mysql restart

STEP #3

Convert every InnoDB table to InnoDB again

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertInnoDBToInnoDB.sql
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -AN -e"${SQL}" >> /root/ConvertInnoDBToInnoDB.sql
less /root/ConvertInnoDBToInnoDB.sql

Once you view he script and are satisfied, login to mysql and run this

mysql> source /root/ConvertInnoDBToInnoDB.sql

EPILOGUE

All .ibd files will vanish and all InnoDB tables and indexes will exist inside ibdata1

Give it a Try !!!

Mysql – Finding reason behind high write IOPS on MySQL (RDS) server

Shown RDS statistics are too global and broad to provide an answer to the question "Who is to blame for the increased load?".

There are serveral ways to profile your queries, but as you are using RDS with MySQL 5.6, I would recommend you to use PERFORMANCE_SCHEMA to do it easily, as you won't need external software (some of which is not fully RDS-compatible). Here you have a video with an introduction to query profiling, with examples like IOPS and temporary table creation monitoring by query pattern, user and table. For a more specific guide (specially for configuration of metrics), you can have a look at the official manual and the sys schema documentation.

For a quick check you can also have a look at your SHOW GLOBAL STATUS like 'com\_%'; and SHOW GLOBAL STATUS like 'Hand%'; at time interval to see if you have an increase on the number of SQL queries per unit of time or on the number of engine row operations per unit of time (the first one does not seem abnormal according to the graphs shown).

Your goal is to detect the offending queries first so that you can modify the SQL, your data structure, the MySQL configuration and/or anything peripheral (like if your RDS instance is still good enough) accordingly. An increase on Write IOPS normally may mean extra SQL load (obviously), but also many other things, such as: too many temporary tables or worse query plans being executed due to a change on the query optimiser plan or on your data cardinality/size. It is critical to identify the underlying cause first before taking any action.