| « PreviousNext » | |
   | Did this page help you? Yes | No | Tell us about it... |
Appendix: Common DBA Tasks for MySQL
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. This section describes the Amazon RDS-specific implementations of some common DBA tasks for DB instances running the MySQL database engine.
Killing a Session or Query
To terminate user sessions or queries on DB instances, Amazon RDS provides the following commands:
PROMPT> CALL mysql.rds_kill(thread-ID)
PROMPT> CALL mysql.rds_kill_query(thread-ID)
For example, to kill the session that is running on thread 99, you would type the following:
PROMPT> CALL mysql.rds_kill(99);
To kill the query that is running on thread 99, you would type the following:
PROMPT> CALL mysql.rds_kill_query(99);
Skipping the Current Replication Error
With MySQL versions 5.1.62 and 5.5.23, 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.
Note
To verify that the error can be safely skipped, at the command prompt, you should first type the following command on the Read Replica:
PROMPT> show slave status\G
To skip the error, you can issue the following command:
PROMPT> CALL mysql.rds_skip_repl_error;
This command has no effect if you run it on the master DB instance or on a Read Replica that has not encountered a replication error.
If you are using a MySQL version older than 5.1.62 or 5.5.23, you can upgrade to the latest minor version within your major version of MySQL. For more information, see Modifying a DB Instance Running the MySQL Database Engine.
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. For each procedure, at the command prompt, type the following:
PROMPT> CALL procedure-name;
Where procedure-name is one of the procedures in the table.
Procedure | Description |
|---|---|
|
Enables GoSH to take default snapshots at intervals specified by
|
|
Specifies the interval, in minutes, between snapshots. Default value is 5. |
|
Disables snapshots. |
|
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'
