I am wanting to setup Percona Monitoring Manager (PMM) for a client which uses Aurora. To do so, the documentation says you need to turn on the consumer in the performance schema so that events_statements_history os enabled. There isn't an option in the AWS console to do this and there isn't a parameter group setting to modify this so it must be done directly on the instance.
Here I will show you from the command line:
Here I will show you from the command line:
MySQL [(none)]> show global variables like 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
1 row in set (0.00 sec)
MySQL [(none)]> use performance_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [performance_schema]>
MySQL [performance_schema]> select * from setup_consumers WHERE name = 'events_statements_history';
+---------------------------+---------+
| NAME | ENABLED |
+---------------------------+---------+
| events_statements_history | NO |
+---------------------------+---------+
1 row in set (0.00 sec)
MySQL [performance_schema]> update setup_consumers set enabled='yes' WHERE name = 'events_statements_history';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [performance_schema]> select * from setup_consumers WHERE name = 'events_statements_history';
+---------------------------+---------+
| NAME | ENABLED |
+---------------------------+---------+
| events_statements_history | YES |
+---------------------------+---------+
1 row in set (0.00 sec)
However, after an instance restart, the changes performed to setup_consumers table will be reversed.
MySQL [performance_schema]> show global variables like 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
1 row in set (0.00 sec)
MySQL [performance_schema]> select * from setup_consumers WHERE name = 'events_statements_history';
+---------------------------+---------+
| NAME | ENABLED |
+---------------------------+---------+
| events_statements_history | NO |
+---------------------------+---------+
1 row in set (0.01 sec)
MySQL [performance_schema]> update setup_consumers set enabled='yes' WHERE name = 'events_statements_history';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [performance_schema]> select * from setup_consumers WHERE name = 'events_statements_history';
+---------------------------+---------+
| NAME | ENABLED |
+---------------------------+---------+
| events_statements_history | YES |
+---------------------------+---------+
1 row in set (0.00 sec)
I could setup a Zabbix trigger that turns it back whenever it detects a server restart or some other script. I think the easier route it to create an event that keeps turning it back on. Here is a simple example:
DROP EVENT IF EXISTS enable_statement_history;
CREATE
DEFINER=`root`@`localhost`
EVENT IF NOT EXISTS enable_statement_history
ON SCHEDULE EVERY 60 SECOND
STARTS NOW()
DO
update performance_schema.setup_consumers set enabled='yes' WHERE name = 'events_statements_history';