You might think about switching the tablespaces from individual to shared if you receive a notification in your AWS RDS stating that “DB Instance instance-name has a large number of tables which can increase database recovery time.“
What is a tablespace?
Each table in MySQL has a table definition, data, and indexes. InnoDB, the MySQL storage engine, stores table data and indexes in a tablespace. InnoDB creates a global shared tablespace that can hold table data and indexes in addition to a data dictionary and other pertinent metadata.
For every table and partition, InnoDB may also create a separate tablespace. The header of each of these distinct tablespaces carries a number that uniquely identifies it, and they are all stored in files with the .ibd extension.
How to configure it?
In a MySQL parameter group, Amazon RDS offers a setting named innodb_file_per_table. By setting the parameter value to 0 or 1, this option determines whether InnoDB adds new table data and indexes to the common tablespace or to individual tablespaces.
When using Amazon RDS, the innodb_file_per_table parameter’s default value is set to 1, allowing you to delete certain InnoDB tables and free up space for the DB instance. Setting the innodb_file_per_table parameter to 1 is advised in the majority of usage scenarios.
When to configure what?
When you have a lot of tables, such as over 1000 tables when using standard (magnetic) or general-purpose SSD storage or over 10,000 tables while using Provisioned IOPS storage, you should set the innodb_file_per_table parameter to 0. Individual tablespaces are not created when this parameter is set to 0, which might speed up database crash recovery.
The parameter value is set to 1 if the scenario doesn’t fit within the categories mentioned above.
What is the impact?
During the crash recovery cycle, MySQL processes each metadata file, which also includes tablespaces. When there are several tablespaces, the time it takes MySQL to process the metadata data in the shared tablespace is short compared to the time it takes to process thousands of tablespace files. As each file’s header contains the tablespace number, reading all of the tablespace files at once can take up to several hours. For instance, processing a million InnoDB tablespaces on standard storage during a crash recovery cycle can take five to eight hours.
Sometimes, following a crash recovery cycle, InnoDB may decide that it needs further cleanup, at which point it will start a new crash recovery cycle, extending the recovery period. Remember that in addition to processing tablespace data, a crash recovery cycle also comprises rolling back transactions, fixing broken pages, and other tasks.
Hope you find this article helpful.
Big Data and SQL 