Database Monitoring for AWS RDS MySQL

Atatus Database Monitoring offers comprehensive monitoring for your AWS RDS (Relational Database Service) MySQL databases, providing valuable insights into key metrics like query performance, query samples, execution plans, connection details, system statistics, and InnoDB storage engine telemetry. To enable database monitoring in AWS RDS, the service gathers telemetry by leveraging built-in monitoring tools and metrics. Here’s how you can configure and utilize database monitoring for your MySQL database on AWS RDS:

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+
MariaDB	10.5, 10.6 or 10.11
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 Parameter Group.

performance_schema = 1
max_digest_length = 4096
performance_schema_max_digest_length = 4096
performance_schema_max_sql_text_length = 4096

Restart the server for the parameter changes to take effect.

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

[Optional] To collect and visualize database configuration and schema details in MySQL, the Atatus user requires the following privileges. Use the SQL query below to grant the necessary 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.

cd /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

Restart the atatus infrastructure agent.

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
      db_name: <DB_NAME>
      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  

​Before You Begin

​Configure MySQL Performance Schema Settings

​Setup Database Access for the Agent

​Configure Atatus Infrastructure Agent

​Collecting Schemas (Optional)

​Collecting Custom Metrics (Optional)