How do I enable and configure GOSH on an Amazon RDS instance that is running MySQL?

Last updated: 2019-06-19

How do I enable and configure Global Status History (GOSH) on an Amazon Relational Database Service (Amazon RDS) instance that's running MySQL?

Short Description

You can use GOSH to maintain the history of different status variables in Amazon RDS for MySQL. First, you must enable an event scheduler before you can enable 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 execute the following commands:

SHOW PROCESSLIST;
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';

3.    Enable GOSH by running the following procedure:

CALL mysql.rds_enable_gsh_collector;

4.    To modify the monitoring interval to one minute, run the following procedure:

CALL rds_set_gsh_collector(1);

5.    Enable rotation for the GOSH tables by running the following procedures:

CALL rds_enable_gsh_rotation;

6.    Modify the rotation by running the following procedure.

CALL 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;

Did this article help you?

Anything we could improve?


Need more help?