Tip
To analyze database performance, you can also use Performance Insights on Amazon RDS. For more information, see Monitoring DB load with Performance Insights on Amazon RDS.
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.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 MySQL DB
instances running MySQL 5.7, also set the parameter show_compatibility_56
to 1. For information about creating and modifying a DB parameter group,
see Parameter groups for Amazon RDS. For information about the side
effects of enabling this parameter, see show_compatibility_56
You can then use the procedures in the following table to enable and configure GoSH.
First connect to your MySQL DB instance, then issue the appropriate commands as shown
following. For more information, see Connecting to your MySQL DB instance. For each procedure, run the following
command and replace procedure-name:
CALL procedure-name; The following table lists all of the procedures that you can use for procedure-name in the previous
command.
| 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 mysql.rds_global_status_history as a join mysql.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' 