How do I configure GoSH on an Amazon RDS instance that is running MySQL?

2 minute read
0

I have an Amazon Relational Database Service (Amazon RDS) instance that is running MySQL. I want to turn on and configure Global Status History (GoSH) on my RDS DB instance. How can I do this?

Short description

You can use GoSH to maintain the history of different status variables in Amazon RDS for MySQL. First, you must turn on an event scheduler before you can use GoSH. Then, you can modify GoSH to run at specific intervals and to rotate tables regularly. By default, the GoSH information is collected every five minutes, stored in the mysql.rds_global_status_history table, and the table is rotated every seven days.

Resolution

1.    Modify the custom DB parameter group attached to the instance so that event_scheduler is set to ON.

2.    Log in to your DB instance, and then run this command:

SHOW PROCESSLIST;
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';

3.    Turn on GoSH by running this command:

CALL mysql.rds_enable_gsh_collector;

4.    To modify the monitoring interval to one minute, run this command:

CALL mysql.rds_set_gsh_collector(1);

5.    Turn on rotation for the GoSH tables by running this command:

CALL mysql.rds_enable_gsh_rotation;

6.    Modify the rotation by running this command:

CALL mysql.rds_set_gsh_rotation(5);

Query the GoSH tables to fetch information about specific operations. For example, the following query provides details about the number of Data Manipulation Language (DML) operations performed on the instance every minute.

SELECT collection_start, collection_end, sum(value) AS 'DML Queries Count' from (select collection_start, collection_end, "INSERTS" as "Operation", variable_Delta as "value" from mysql.rds_global_status_history  where variable_name = 'com_insert' union select collection_start, collection_end, "UPDATES" as "Operation", variable_Delta as "value" from mysql.rds_global_status_history  where variable_name = 'com_update' union select collection_start, collection_end, "DELETES" as "Operation", variable_Delta as "value" from mysql.rds_global_status_history  where variable_name = 'com_delete') a group by 1,2;

Note: This query is not applicable for MySQL 8.0.


Related information

Common DBA tasks for MySQL DB instances

Managing the global status history

AWS OFFICIAL
AWS OFFICIALUpdated 2 years ago