Atatus Database Monitoring provides comprehensive visibility for your AWS Aurora MySQL-compatible databases, offering valuable insights into key metrics such as query performance, query samples, execution plans, connection details, system statistics, and Aurora-specific storage engine telemetry.
- Enable Performance Schema and it's configuration
- Setup Database Access for the Agent
- Install Atatus Infrastructure Agent
Before You Begin
Component | Supported Versions |
---|---|
MySQL | 5.6, 5.7, or 8.0+ |
Atatus Infra Agent | 3.4.0 or higher |
Configure MySQL Performance Schema Settings
To collect query metrics, samples, and execution plans, enable the MySQL Performance Schema. Update the following parameters in your DB Cluster Parameter Group, then restart the server for the settings to take effect.
performance_schema = 1
performance_schema_max_digest_length = 4096
performance_schema_max_sql_text_length = 4096
performance-schema-consumer-events-statements-current = 1
performance-schema-consumer-events-waits-current = 1
performance-schema-consumer-events-statements-history-long = 1
performance-schema-consumer-events-statements-history = 1
Setup Database Access for the Agent
Create the atatus
user and grant basic permissions:
CREATE USER atatus@'%' IDENTIFIED BY '<UNIQUEPASSWORD>';
ALTER USER atatus@'%' WITH MAX_USER_CONNECTIONS 8;
GRANT REPLICATION CLIENT ON *.* TO atatus@'%';
GRANT PROCESS ON *.* TO atatus@'%';
GRANT SELECT ON performance_schema.* TO atatus@'%';
(OR)
# Only For MySQL 5.6, follow below
CREATE USER atatus@'%' IDENTIFIED BY '<UNIQUEPASSWORD>';
GRANT REPLICATION CLIENT ON *.* TO atatus@'%' WITH MAX_USER_CONNECTIONS 8;
GRANT PROCESS ON *.* TO atatus@'%';
GRANT SELECT ON performance_schema.* TO atatus@'%';
Create the following schema:
CREATE SCHEMA IF NOT EXISTS atatus;
GRANT EXECUTE ON atatus.* TO atatus@'%';
Procedure to Enable Events Statements Consumers:
DELIMITER $$
CREATE PROCEDURE atatus.enable_events_statements_consumers()
SQL SECURITY DEFINER
BEGIN
UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name LIKE 'events_statements_%';
UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name = 'events_waits_current';
END $$
DELIMITER ;
GRANT EXECUTE ON PROCEDURE atatus.enable_events_statements_consumers TO atatus@'%';
Create the EXPLAIN_STATEMENT
procedure to enable the agent to collect explain plans.
DELIMITER $$
CREATE PROCEDURE atatus.explain_statement(IN query TEXT)
SQL SECURITY DEFINER
BEGIN
SET @explain := CONCAT('EXPLAIN FORMAT=json ', query);
PREPARE stmt FROM @explain;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
Additionally, create this procedure in every schema from which you want to collect explain plans. Replace YOUR_SCHEMA
with the appropriate schema name.
DELIMITER $$
CREATE PROCEDURE <YOUR_SCHEMA>.explain_statement(IN query TEXT)
SQL SECURITY DEFINER
BEGIN
SET @explain := CONCAT('EXPLAIN FORMAT=json ', query);
PREPARE stmt FROM @explain;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
GRANT EXECUTE ON PROCEDURE <YOUR_SCHEMA>.explain_statement TO atatus@'%';
To collect database information about MySQL, atatus user needs the below necessary privileges. The following SQL query grants the required permissions:
GRANT SELECT, SHOW DATABASES, SHOW VIEW, INDEX, REFERENCES ON *.* TO 'atatus'@'%';
FLUSH PRIVILEGES;
Configure Atatus Infrastructure Agent
To enable Database Monitoring for your databases, You should install the Atatus Infrastructure agent in your host machine.
Copy the mysql example configuration file.
copycd /etc/atatus-infra-agent/conf.d/mysql.d/ sudo cp mysql.yml.template mysql.yml
Update mysql Configuration File:
Add the following configuration to /etc/atatus-infra-agent/conf.d/mysql.d/mysql.yml
:
metrics:
- hosts: ["tcp(example.rds.amazonaws.com)/"]
username: atatus
password: <UNIQUEPASSWORD>
dbm: true
logs:
- type: error
- type: slowlog
- Restart the atatus infrastructure agent and MySQL service.
sudo service mysql restart
sudo service atatus-infra-agent restart
Collecting Schemas (Optional)
Starting from Agent v3.4.0, the Atatus Infra Agent supports collecting schema information from MySQL databases.
To enable this, configure the schemas_collection
option in your agent settings.
metrics:
- hosts:
- "tcp(example.rds.amazonaws.com)/"
username: <DB_USERNAME>
password: <DB_PASSWORD>
dbm: true
dbm_mysql_options:
collect_database_info:
enabled: true
collect_settings:
enabled: true
Collecting Custom Metrics (Optional)
To collect custom metrics, use the custom_queries
option in your configuration. Refer to the sample atatus-mysql.conf
file for guidance.
metrics:
- hosts:
- "tcp(example.rds.amazonaws.com)/"
username: <DB_USERNAME>
password: <DB_PASSWORD>
dbm: true
dbm_mysql_options:
additional_metrics_options:
# The following is an example of a custom query configuration.
custom_queries:
enabled: true
custom_query:
- query: SELECT age, salary, hours_worked, name FROM hr.employees;
columns:
- name: custom.employee_age
type: gauge
- name: custom.employee_salary
type: gauge
- name: custom.employee_hours
type: count
- name: name
type: tag
tags:
- 'table:employees'
max_custom_queries: 20