Setting up consumers - events_statements_history for Aurora instances with performance schema

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:

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