Appendix: Common DBA Tasks for MySQL
This section describes the Amazon RDS-specific implementations of some common DBA tasks for DB instances running the MySQL database engine. In order to deliver a managed service experience, Amazon RDS does not provide shell access to DB instances, and it restricts access to certain system procedures and tables that require advanced privileges.
For information about working with MySQL log files on Amazon RDS, see MySQL Database Log Files
Killing a Session or Query
You can terminate user sessions or queries on DB instances by using the
rds_kill_query commands. First connect to
your MySQL database instance, then issue the appropriate command as shown following.
For more information, see Connecting to a DB Instance Running the MySQL Database
CALL mysql.rds_kill(thread-ID) CALL mysql.rds_kill_query(thread-ID)
For example, to kill the session that is running on thread 99, you would type the following:
To kill the query that is running on thread 99, you would type the following:
Skipping the Current Replication Error
Amazon RDS provides a mechanism for you to skip an error on your Read Replicas if the error is causing your Read Replica to hang and the error doesn’t affect the integrity of your data. First connect to your MySQL database instance, then issue the appropriate commands as shown following. For more information, see Connecting to a DB Instance Running the MySQL Database Engine.
You should first verify that the error can be safely skipped. In a MySQL utility, connect to the Read Replica and run the following MySQL command:
SHOW SLAVE STATUS\G
For information about the values returned, go to SHOW SLAVE STATUS Syntax in the MySQL documentation.
To skip the error, you can issue the following command:
This command has no effect if you run it on the source DB instance, or on a Read Replica that has not encountered a replication error.
For more information, such as the versions of MySQL that support
mysql.rds_skip_repl_error, see mysql.rds_skip_repl_error.
If you attempt to call mysql.rds_skip_repl_error and
encounter the following error:
ERROR 1305 (42000): PROCEDURE mysql.rds_skip_repl_error does not exist,
then upgrade your MySQL DB instance to the latest minor version or one of the minimum
minor versions listed in mysql.rds_skip_repl_error.
Every table in MySQL consists of a table definition, data, and indexes. The MySQL storage engine InnoDB stores table data and indexes in a tablespace. InnoDB creates a global shared tablespace that contains a data dictionary and other relevant metadata, and it can contain table data and indexes. InnoDB can also create separate tablespaces for each table and partition. These separate tablespaces are stored in files with a .ibd extension and the header of each tablespace contains a number that uniquely identifies it.
Amazon RDS provides a parameter in a MySQL parameter group called
innodb_file_per_table. This parameters controls whether InnoDB adds new
table data and indexes to the shared tablespace (by setting the parameter value to 0) or
to individual tablespaces (by setting the parameter value to 1). Amazon RDS sets the
default value for
innodb_file_per_table parameter to 1, which allows you to
drop individual InnoDB tables and reclaim storage used by those tables for the DB
instance. In most use cases, setting the
innodb_file_per_table parameter to
1 is the recommended setting.
You should set the
innodb_file_per_table parameter to 0 when you have a
large number of tables, such as over 1000 tables when you use standard (magnetic) or general purpose
SSD storage or over
10,000 tables when you use Provisioned IOPS storage. When you set this parameter to 0,
individual tablespaces are not created and this can improve the time it takes for
database crash recovery.
MySQL processes each metadata file, which includes tablespaces, during the crash recovery cycle. The time it takes MySQL to process the metadata information in the shared tablespace is negligible compared to the time it takes to process thousands of tablespace files when there are multiple tablespaces. Because the tablespace number is stored within the header of each file, the aggregate time to read all the tablespace files can take up to several hours. For example, a million InnoDB tablespaces on standard storage can take from five to eight hours to process during a crash recovery cycle. In some cases, InnoDB can determine that it needs additional cleanup after a crash recovery cycle so it will begin another crash recovery cycle, which will extend the recovery time. Keep in mind that a crash recovery cycle also entails rolling-back transactions, fixing broken pages, and other operations in addition to the processing of tablespace information.
innodb_file_per_table parameter resides in a parameter group,
you can change the parameter value by editing the parameter group used by your DB
instance without having to reboot the DB instance. After the setting is changed, for
example, from 1 (create individual tables) to 0 (use shared tablespace), new InnoDB
tables will be added to the shared tablespace while existing tables continue to have
individual tablespaces. To move an InnoDB table to the shared tablespace, you must use
ALTER TABLE command.
Migrating Multiple Tablespaces to the Shared Tablespace
You can move an InnoDB table's metadata from its own tablespace to the shared
tablespace, which will rebuild the table metadata
according to the
innodb_file_per_table parameter setting.
First connect to your MySQL database instance, then issue the appropriate commands as shown following.
For more information, see Connecting to a DB Instance Running the MySQL Database
table_nameENGINE = InnoDB, ALGORITHM=COPY;
For example, the following query returns an
ALTER TABLE statement for
every InnoDB table.
SELECT CONCAT('ALTER TABLE `', REPLACE(TABLE_SCHEMA, '`', '``'), '`.`', REPLACE(TABLE_NAME, '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND ENGINE = 'InnoDB' AND TABLE_SCHEMA <> 'mysql';
Rebuilding a MySQL table to move the table's metadata to the shared tablespace requires additional storage space temporarily to rebuild the table, so the DB instance must have storage space available. During rebuilding, the table is locked and inaccessible to queries. For small tables or tables not frequently accessed, this may not be an issue; for large tables or tables frequently accessed in a heavily concurrent environment, you can rebuild tables on a Read Replica.
You can create a Read Replica and migrate table metadata to the shared tablespace on the Read Replica. While the ALTER TABLE statement blocks access on the Read Replica, the source DB instance is not affected. The source DB instance will continue to generate its binary logs while the Read Replica lags during the table rebuilding process. Because the rebuilding requires additional storage space and the replay log file can become large, you should create a Read Replica with storage allocated that is larger than the source DB instance.
The following steps should be followed to create a Read Replica and rebuild InnoDB tables to use the shared tablespace:
Ensure that backup retention is enabled on the source DB instance so that binary logging is enabled
Use the AWS Console or AWS CLI to create a Read Replica for the source DB instance. Since the creation of a Read Replica involves many of the same processes as crash recovery, the creation process may take some time if there are a large number of InnoDB tablespaces. Allocate more storage space on the Read Replica than is currently used on the source DB instance.
When the Read Replica has been created, create a parameter group with the parameter settings
read_only = 0and
innodb_file_per_table = 0, and then associate the parameter group with the Read Replica.
Issue ALTER TABLE <name> ENGINE = InnoDB against all tables you want migrated on the replica.
When all of your ALTER TABLE statements have completed on the Read Replica, verify that the Read Replica is connected to the source DB instance and that the two instances are in-sync.
When ready, use the AWS Console or AWS CLI to promote the Read Replica to be the master instance. Make sure that the parameter group used for the new master has the innodb_file_per_table parameter set to 0. Change the name of the new master, and point any applications to the new master instance.
Managing the Global Status History
MySQL maintains many status variables that provide information about its operation.
Their value can help you detect locking or memory issues on a DB instance . The values
of these status variables are cumulative since last time the DB instance was started.
You can reset most status variables to 0 by using the
FLUSH STATUS command.
To allow for monitoring of these values over time, Amazon RDS provides a set of procedures that will snapshot the values of these status variables over time and write them to a table, along with any changes since the last snapshot. This infrastructure, called Global Status History (GoSH), is installed on all MySQL DB instances starting with versions 5.1.62 and 5.5.23. GoSH is disabled by default.
To enable GoSH, you first enable the event scheduler from a DB parameter group by setting the parameter event_scheduler to ON. For information about creating and modifying a DB parameter group, see Working with DB Parameter Groups.
You can then use the procedures in the following table to enable and configure GoSH. First connect to your MySQL database instance, then issue the appropriate commands as shown following. For more information, see Connecting to a DB Instance Running the MySQL Database Engine. For each procedure, type the following:
Where procedure-name is one of the procedures in the table.
Enables GoSH to take default snapshots at intervals specified by
Specifies the interval, in minutes, between snapshots. Default value is 5.
Takes a snapshot on demand.
Enables rotation of the contents of the
Specifies the interval, in days, between table rotations. Default value is 7.
Disables table rotation.
Rotates the contents of the
When GoSH is running, you can query the tables that it writes to. For example, to query the hit ratio of the Innodb buffer pool, you would issue the following query:
select a.collection_end, a.collection_start, (( a.variable_Delta-b.variable_delta)/a.variable_delta)*100 as "HitRatio" from rds_global_status_history as a join rds_global_status_history as b on a.collection_end = b.collection_end where a. variable_name = 'Innodb_buffer_pool_read_requests' and b.variable_name = 'Innodb_buffer_pool_reads'