Database Monitoring for Google Cloud SQL for MySQL

Atatus Database Monitoring offers comprehensive monitoring for your Google Cloud SQL for MySQL instances, providing valuable insights into query performance, query samples, execution plans, connection details, system statistics, and InnoDB storage engine telemetry.

To enable Database Monitoring, the Agent gathers telemetry by accessing the database as a read-only user. The Atatus Infra Agent runs on a separate VM that connects to your Cloud SQL instance over a private IP, public IP, or the Cloud SQL Auth Proxy.

Configure MySQL Performance Schema Settings
Setup Database Access for the Agent
Configure 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

Database Monitoring requires at least 16 GB of RAM on the Cloud SQL instance.

Configure MySQL Performance Schema Settings

In the Google Cloud Console, open your Cloud SQL instance and edit its Database flags. Add the following flags, then restart the instance for changes to take effect.

For MySQL 5.7 and 8.0+

Parameter	Value	Purpose
`performance_schema`	`on`	Enables Performance Schema (required).
`max_digest_length`	`4096`	Allows collection of queries longer than 1024 chars.
`performance_schema_max_digest_length`	`4096`	Must match `max_digest_length`.
`performance_schema_max_sql_text_length`	`4096`	Must match `max_digest_length`.

For MySQL 5.6

Parameter	Value	Purpose
`performance_schema`	`on`	Enables Performance Schema (required).
`max_digest_length`	`4096`	Allows collection of larger queries.
`performance_schema_max_digest_length`	`4096`	Must match `max_digest_length`.

Setup Database Access for the Agent

Create the atatus user and grant basic permissions:

copy

  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:

copy

  CREATE SCHEMA IF NOT EXISTS atatus;
  GRANT EXECUTE ON atatus.* TO atatus@'%';

Procedure to Enable Events Statements Consumers:

copy

  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.

copy

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.

copy

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

Grant access for InnoDB index metrics:

copy

GRANT SELECT ON mysql.innodb_index_stats TO atatus@'%';

[Optional] To collect and visualize database configuration and schema details in MySQL, grant the following privileges:

copy

GRANT SELECT, SHOW DATABASES, SHOW VIEW, INDEX, REFERENCES ON *.* TO 'atatus'@'%';
FLUSH PRIVILEGES;

Configure Atatus Infrastructure Agent

Install the Atatus Infrastructure agent on a host that can reach your Cloud SQL instance (via private IP, public IP, or the Cloud SQL Auth Proxy).
Copy the MySQL example configuration file:

copy
```
cd /etc/atatus-infra-agent/conf.d/mysql.d/
sudo cp mysql.yml.template mysql.yml
```
Update the MySQL configuration file:

Add the following configuration to /etc/atatus-infra-agent/conf.d/mysql.d/mysql.yml:

copy
```
metrics:
  - hosts: ["tcp(<INSTANCE_IP>:3306)/"]
    username: atatus
    password: <UNIQUEPASSWORD>
    dbm: true
```
Replace <INSTANCE_IP> with the Cloud SQL instance's private/public IP, or 127.0.0.1:3306 if connecting through the Cloud SQL Auth Proxy.
Restart the Atatus Infra Agent:

copy
```
sudo service atatus-infra-agent restart
```

Connecting via Cloud SQL Auth Proxy (Optional)

If you connect through the Cloud SQL Auth Proxy, point the agent at the proxy's local address and override the reported hostname so metrics are tagged to the underlying instance, not localhost:

copy

metrics:
  - hosts: ["tcp(127.0.0.1:3306)/"]
    username: atatus
    password: <UNIQUEPASSWORD>
    dbm: true
    reported_hostname: <CLOUD_SQL_INSTANCE_NAME>

Collecting Schemas (Optional)

To collect schema information from your Cloud SQL instance, configure the schemas_collection options:

copy

metrics:
    - hosts:
        - "tcp(<INSTANCE_IP>:3306)/"
      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:

copy

metrics:
    - hosts:
        - "tcp(<INSTANCE_IP>:3306)/"
      username: <DB_USERNAME>
      password: <DB_PASSWORD>
      dbm: true
      db_name: <DB_NAME>
      dbm_mysql_options:
        additional_metrics_options:
          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