Skip to main content

MySQL - Classic Collector

Thumbnail icon

The Sumo Logic app for MySQL is a unified logs and metrics app that helps you monitor the availability, performance and resource utilization of MySQL database clusters. Preconfigured dashboards and searches provide insight into the health of your MySQL clusters, replication status, error logs, query performance, slow queries, Innodb operations, failed logins and error logs.

The MySQL app supports following versions:

  • MySQL 5.5.35-1 and later
  • Percona MySQL 5.6.17 and later
  • MySQL 8.0.x

MySQL app Searches​

The predefined searches in the MySQL app are based on the following log types.

Searches based on Error logs​

  • MySQL - Crash Recovery Attempts by Host
  • MySQL - Number of Replication Completion Events by Host
  • MySQL - Replication Failures by Host
  • MySQL - Server Latest Replication State
  • MySQL - Server Latest Running State
  • MySQL - Server Up-Down Events by Host

Searches based on Slow Query logs​

  • Slow Query Server Location. If your servers are not using private IP addresses, you can use the Slow Query Server Location search to visualize their locations on a map of the world.

Log and Metrics Types​

The Sumo Logic app for MySQL assumes the default MySQL Error log file format for error logs, and the MySQL Slow Query file format for slow query logs. For a list of metrics that are collected and used by the app, see MySQL Metrics.

  • The MySQL - Overview dashboard is based on logs from both the Error and Slow Query log formats, so as to correlate information between the two.
  • Dashboards in the Metrics folder are based on MySQL metrics.
  • Dashboards in the Logs folder are based on MySQL logs from both the Error and Slow Query log formats.
    • Dashboards based on the Error format:
      • MySQL - Error Logs
      • MySQL - Failed Logins
      • MySQL - Replication
    • Dashboard based on Slow Query format:
      • MySQL - Slow Queries
  • MySQL General Health is based on logs from the MySQL Error log format.
  • MySQL Replication is based on logs from the MySQL Error log format.
  • MySQL Slow Queries is based on logs from the MySQL Slow Queries log format.

The MySQL app dashboards dependent on error logs are based on the message types ERROR, NOTE, Warning, and Info. For more details on the MySQL log file format, see http://dev.mysql.com/doc/refman/5.5/en/server-logs.html.

Sample log messages​

{
  "timestamp":1617810938497,
  "log":"2021-04-07T15:55:34.261220Z 0 [System] [MY-010931] [Server] /opt/bitnami/mysql/bin/mysqld: ready for connections. Version: '8.0.23'  socket: '/opt/bitnami/mysql/tmp/mysql.sock'  port: 3306  Source distribution.",
  "stream":"stdout",
  "time":"2021-04-07T15:55:34.261397194Z"
}

Sample queries​

This sample query is from the MySQL - Logs dashboard > Logs panel.

Query String - Top 10 Slow Queries by Average Execution Time
db_system=mysql db_cluster={{db_cluster}} "User@Host" "Query_time"  
| parse regex "(?<query_block># User@Host:[\S\s]+?SET timestamp=\d+;[\S\s]+?;)" multi
| parse regex field=query_block "# User@Host: \S+?\[(?<user>\S*?)\] @ (?<host_name>\S+)\s\[(?<ip_addr>\S*?)\]" nodrop // Pttrn1-vrtn1
| parse regex field=query_block "# User@Host: \S+?\[(?<user>\S*?)\] @\s+\[(?<ip_addr>\S*?)\]\s+Id:\s+(?<Id>\d{1,10})" nodrop // Pttrn1-vrtn2
| parse regex field=query_block "# User@Host: \S+?\[(?<user>\S*?)\] @ (?<host_name>\S+)\s\[(?<ip_addr>\S*?)\]\s+Id:\s+(?<Id>\d{1,10})" // Pttrn1-vrtn3
| parse regex field=query_block "Schema: (?<schema>(?:\S*|\s)?)\s*Last_errno[\s\S]+?Query_time:\s+(?<query_time>[\d.]*)\s+Lock_time:\s+(?<lock_time>[\d.]*)\s+Rows_sent:\s+(?<rows_sent>[\d.]*)\s+Rows_examined:\s+(?<rows_examined>[\d.]*)\s+Rows_affected:\s+(?<rows_affected>[\d.]*)\s+Rows_read:\s+(?<rows_read>[\d.]*)\n" nodrop // Pttrn2-vrtn1
| parse regex field=query_block "Schema: (?<schema>(?:\S*|\s)?)\s*Last_errno[\s\S]+?\s+Killed:\s+\d+\n" nodrop // Pttrn2-vrtn2
| parse regex field=query_block "Query_time:\s+(?<query_time>[\d.]*)\s+Lock_time:\s+(?<lock_time>[\d.]*)\s+Rows_sent:\s+(?<rows_sent>[\d]*)\s+Rows_examined:\s+(?<rows_examined>[\d]*)\s+Rows_affected:\s+(?<rows_affected>[\d]*)\s+" nodrop // Pttrn2-vrtn3
| parse regex field=query_block "Query_time:\s+(?<query_time>[\d.]*)\s+Lock_time:\s+(?<lock_time>[\d.]*)\s+Rows_sent:\s+(?<rows_sent>[\d]*)\s+Rows_examined:\s+(?<rows_examined>[\d]*)" // Pttrn2-vrtn4
| parse regex field=query_block "# Bytes_sent:\s+(?<bytes_sent>\d*)\s+Tmp_tables:\s+(?<tmp_tables>\d*)\s+Tmp_disk_tables:\s+(?<temp_disk_tables>\d*)\s+Tmp_table_sizes:\s+(?<tmp_table_sizes>\d*)\n" nodrop // Pttrn3-vrtn1
| parse regex field=query_block "# Bytes_sent:\s+(?<bytes_sent>\d*)\n" nodrop // Pttrn3-vrtn2
| parse regex field=query_block "SET timestamp=(?<set_timestamp>\d*);(?:\\n|\n)(?<sql_cmd>[\s\S]*);" nodrop
| fields -query_block
| avg(query_time) as avg_time, sum(query_time) as total_time, min(query_time) as min_time, max(query_time) as max_time, avg(rows_examined) as avg_rows_examined, avg(rows_sent) as avg_rows_sent, avg(Lock_Time) as avg_lock_time, count as frequency group by sql_cmd, db_cluster
| sort by avg_time | limit 10

Collecting logs and metrics for MySQL​

Configuring log and metric collection for the MySQL app includes the following tasks.

Step 1: Configure Fields in Sumo Logic​

Create the following Fields in Sumo Logic prior to configuring the collection. This ensures that your logs and metrics are tagged with relevant metadata, which is required by the app dashboards. For information on setting up fields, see Sumo Logic Fields.

If you're using MySQL in a Kubernetes environment, create the fields:

  • pod_labels_component
  • pod_labels_environment
  • pod_labels_db_system
  • pod_labels_db_cluster
  • pod_labels_db_cluster_address
  • pod_labels_db_cluster_port

Step 2: Configure MySQL Logs and Metrics Collection​

In Kubernetes environments, we use the Telegraf Operator, which is packaged with our Kubernetes collection. For more information, see Telegraf Collection Architecture.

The diagram below illustrates how data is collected from MySQL in Kubernetes environments. In the architecture shown below, there are four services that make up the metric collection pipeline: Telegraf, Telegraf Operator, Prometheus, and Sumo Logic Distribution for OpenTelemetry Collector.


K8s flow

The first service in the metrics pipeline is Telegraf. Telegraf collects metrics from MySQL. Note that we’re running Telegraf in each pod we want to collect metrics from as a sidecar deployment: that is, Telegraf runs in the same pod as the containers it monitors. Telegraf uses the MySQL input plugin to obtain metrics. (For simplicity, the diagram doesn’t show the input plugins.) The injection of the Telegraf sidecar container is done by the Telegraf Operator. Prometheus pulls metrics from Telegraf and sends them to Sumo Logic Distribution for OpenTelemetry Collector which enriches metadata and sends metrics to Sumo Logic.

In the logs pipeline, Sumo Logic Distribution for OpenTelemetry Collector collects logs written to standard out and forwards them to another instance of Sumo Logic Distribution for OpenTelemetry Collector, which enriches metadata and sends logs to Sumo Logic.

prerequisites

Ensure that you are monitoring your Kubernetes clusters with the Telegraf operator. If you're not, see Install Telegraf.

Step 1: Configure Metrics collection​

This configures metrics collection from Kubernetes.

  1. Add the following annotations to your MySQL pods, and make the edits described below:
primary:
  podAnnotations:
    telegraf.influxdata.com/class: sumologic-prometheus
    prometheus.io/scrape: "true"
    prometheus.io/port: "9273"
    telegraf.influxdata.com/inputs: |+
      [[inputs.mysql]]
       servers = ["user:passwd@tcp(localhost:3306)/?tls=false"]
        table_schema_databases = []
        gather_slave_status = true
        gather_global_variables = true
        gather_table_io_waits = true
        gather_table_lock_waits = true
        gather_index_io_waits = true
        gather_event_waits = true
        gather_file_events_stats = true
        gather_perf_events_statements = true
        [inputs.mysql.tags]
          environment = "ENV_TO_BE_CHANGED"
          component = "database"
          db_system = "mysql"
          db_cluster = "ENV_TO_BE_CHANGED"
          db_cluster_address = "ENV_TO_BE_CHANGED"
          db_cluster_port = "ENV_TO_BE_CHANGED"
    tailing-sidecar: sidecarconfig;slowlog:data:/bitnami/mysql/data/mysql-release-0-slow.log
  1. Enter values for the following parameters in your annotations:
    • telegraf.influxdata.com/inputs. This contains the required configuration for the Telegraf MySQL Input plugin. For information on configuring the MySQL input plugin for Telegraf, see the MySQL Input Plugin Readme. Because Telegraf will be run as a sidecar the host should always be localhost.
    • In [[inputs.mysql]]:
      • servers. The URL to the MySQL server
      • Configure metrics to collect by uncommenting or setting the following parameters. For more information, see the MySQL Input Plugin README.
        • table_schema_databases = []
        • gather_slave_status = true
        • gather_global_variables = true
        • gather_table_io_waits = true
        • gather_table_lock_waits = true
        • gather_index_io_waits = true
        • gather_event_waits = true
        • gather_file_events_stats = true
        • gather_perf_events_statements = true
    • In [inputs.mysql.tags]:
      • environment. This is the deployment environment where the MySQL cluster identified by the value of servers resides. For example: dev, prod or qa. While this value is optional we highly recommend setting it.
      • db_cluster. Enter a name to uniquely identify this MySQL cluster. This cluster name will be shown in the Sumo Logic dashboards.
      • db_cluster_address - Enter the cluster hostname or ip address that is used by the application to connect to the database. It could also be the load balancer or proxy endpoint.
      • db_cluster_port - Enter the database port. If not provided, a default port will be used.
note

db_cluster_address and db_cluster_port should reflect the exact configuration of DB client configuration in your application, especially if you instrument it with OT tracing. The values of these fields should match exactly the connection string used by the database client (reported as values for net.peer.name and net.peer.port metadata fields).

For example, if your application uses “mysql-prod.sumologic.com:3306” as the connection string, the field values should be set as follows: db_cluster_address=mysql-prod.sumologic.com db_cluster_port=3306

If your application connects directly to a given MySQL node, rather than the whole cluster, use the application connection string to override the value of the “host” field in the Telegraf configuration: host=mysql-prod.sumologic.com

Pivoting to Tracing data from Entity Inspector is possible only for “MySQL address” Entities.

  • Do not modify the following values as it will cause the Sumo Logic app to not function correctly.
    • telegraf.influxdata.com/class: sumologic-prometheus instructs the Telegraf operator what output to use.
    • prometheus.io/scrape: "true" ensures Prometheus will scrape the metrics.
    • prometheus.io/port: "9273" tells Prometheus what ports to scrape on.
    • telegraf.influxdata.com/inputs
      • In the [inputs.mysql.tags] section:
      • component: "database" is used by the Sumo Logic app to identify application components.
      • db_system: "mysql" identifies the database system.
  • For information about properties that can be configured globally in the Telegraf agent, see the Configuration documentation for Telegraf.
  1. Sumo Logic Kubernetes collection will automatically start collecting metrics from the pods with the labels and annotations you added in the previous step.
  2. To verify the metrics have been ingested, run this metrics query:
db_cluster=<your_mysql_cluster_name> component="database" and db_system="mysql"

Configure Logs collection​

This section explains the steps to collect MySQL logs from a Kubernetes environment.

  1. Follow the steps in Method A or Method B, depending on whether your logs are being written to standard output or to log files.
Method A: Collect MySQL logs written to standard output

If your MySQL Helm chart/pod is writing the logs to standard output, follow these steps:

Apply the following labels to your MySQL pods:

labels:
    environment: "prod"
    component: "database"
    db_system: "mysql"
    db_cluster: "your_mysql_cluster_name"

Enter in values for the following parameters (marked CHANGEME in the snippet above):

  • environment. This is the deployment environment where the MySQL cluster identified by the value of servers resides. For example: dev, prod or qa. While this value is optional we highly recommend setting it.
  • db_cluster. Enter a name to identify this MySQL cluster. This cluster name will be shown in the Sumo Logic dashboards.

There are additional configuration options that you should not modify, as changing them will prevent the MySQL app from functioning correctly. The settings you should not modify are:

  • component: "database" is used by Sumo Logic apps to identify application components.
  • db_system: "mysql" identifies the database system.

For information about properties that can be configured globally in the Telegraf agent, see the Configuration documentation for Telegraf.

The Sumo Logic Kubernetes Collection process will automatically capture the logs from stdout and send the logs to Sumo Logic. For more information on deploying the sumologic-kubernetes-collection, see Collect Logs and Metrics for the Kubernetes app.

Method B: Collect MySQL logs written to log files

This method is recommend for Slow Query Logs. If your MySQL helm chart/pod is writing its logs to log files, you can use a sidecar to send log files to standard out. To do so:

  1. Determine the location of the MySQL log file on Kubernetes. You can determine this from the my.cnf file for your MySQL cluster along with the volume mounts on the MySQL pods.
  2. Install the Sumo Logic tailing sidecar operator.
  3. Add the following annotation in addition to the existing annotations.
   primary:
     podAnnotations:
      tailing-sidecar: sidecarconfig;container_name:<mount_volume>:<path_of_mysql_log_file>/<mysql_log_file_name>

For example:

 primary:
   podAnnotations:
    tailing-sidecar: sidecarconfig;slowlog:data:/bitnami/mysql/data/mysql-release-0-slow.log

To verify that the MySQL pods are running and annotations are applied, run this command:

kubectl describe pod <mysql_pod_name>

Sumo Logic Kubernetes collection will automatically start collecting logs from the pods having the annotations defined above.

  1. Add an FER to normalize the fields in Kubernetes environments. This step is not needed if using application components solution terraform script. Labels created in Kubernetes environments are automatically prefixed with pod_labels. To normalize these for our app to work, we'll create a Field Extraction Rule, Database Application Components, assuming it does not already exist:
    1. Classic UI. In the main Sumo Logic menu, select Manage Data > Logs > Field Extraction Rules.
      New UI. In the top menu select Configuration, and then under Logs select Field Extraction Rules. You can also click the Go To... menu at the top of the screen and select Field Extraction Rules.
    2. Click the + Add.
    3. The Add Field Extraction pane appears.
    4. Rule Name. Enter "App Observability - Database".
    5. Applied At. Choose "Ingest Time".
    6. Scope. Select "Specific Data".
    • Scope. Enter the following keyword search expression:
    pod_labels_environment=* pod_labels_component=database pod_labels_db_system=* pod_labels_db_cluster=*
    • Parse Expression. Enter the following parse expression:
    | if (!isEmpty(pod_labels_environment), pod_labels_environment, "") as environment
    | pod_labels_component as component
    | pod_labels_db_system as db_system
    | if (!isEmpty(pod_labels_db_cluster), pod_labels_db_cluster, null) as db_cluster
    1. Click Save to create the rule.
    2. To verify that logs are flowing into Sumo Logic, run this query:
    component=database db_system=mysql db_cluster=<your_mysql_cluster_name>

Installing MySQL Monitors​

The next few sections provide instructions for installing Sumo Logic Monitors for MySQL, the app and descriptions of each of the app dashboards. These instructions assume you have already set up collection as described in Collecting MySQL Logs and Metrics.

Sumo Logic has provided pre-packaged alerts available through Sumo Logic monitors to help you proactively determine if a MySQL cluster is available and performing as expected. These monitors are based on metric and log data and include pre-set thresholds that reflect industry best practices and recommendations. For more information about individual alerts, see MySQL Alerts.

To install these monitors, you must have the Manage Monitors role capability.

You can install monitors by importing a JSON file or using a Terraform script.

There are limits to how many alerts can be enabled. For more information, see Monitors for details.

Method A: Install Monitors by importing a JSON file​

  1. Download the JSON file that describes the monitors.
  2. Replace $$mysql_data_source with a custom source filter. To configure alerts for a specific database cluster, use a filter like db_system=mysql or db_cluster=dev-mysql. To configure the alerts for all of your clusters, set $$mysql_data_source to blank ("").
  3. Classic UI. In the main Sumo Logic menu, select Manage Data > Monitoring > Monitors.
    New UI. In the main Sumo Logic menu, select Alerts > Monitors. You can also click the Go To... menu at the top of the screen and select Monitors.
  4. Click Add.
  5. Click Import.
  6. On the Import Content popup, enter "MySQL" in the Name field, paste in the JSON into the the popup, and click Import.
  7. The monitors are created in a "MySQL" folder. The monitors are disabled by default. See the Monitors topic for information about enabling monitors and configuring notifications or connections.

Method B: Using a Terraform script​

  1. Generate an access key and access ID for a user that has the Manage Monitors role capability. For instructions, see Access Keys.
  2. Download Terraform 0.13 or later, and install it.
  3. Download the Sumo Logic Terraform package for MySQL monitors. The alerts package is available in the Sumo Logic GitHub repository. You can either download it using the git clone command or as a zip file.
  4. Alert Configuration: After extracting the package, navigate to the terraform-sumologic-sumo-logic-monitor/monitor_packages/mysql/ directory.
    1. Edit the mysql.auto.tfvars file and add the Sumo Logic Access Key and Access ID from Step 1 and your Sumo Logic deployment. If you're not sure of your deployment, see Sumo Logic Endpoints and Firewall Security.
    access_id   = "<SUMOLOGIC ACCESS ID>"
    access_key  = "<SUMOLOGIC ACCESS KEY>"
    environment = "<SUMOLOGIC DEPLOYMENT>"
    1. The Terraform script installs the alerts without any scope filters. If you would like to restrict the alerts to specific clusters or environments, update the mysql_data_source variable. For example:
    • To configure alerts for a specific cluster, set mysql_data_source to something like db_cluster=mysql.prod.01
    • To configure alerts for all clusters in an environment Set mysql_data_source to something like environment=prod
    • To configure alerts for...Multiple clusters using a wildcard, set mysql_data_source to something like db_cluster=mysql-prod*
    • To configure alerts for a specific cluster within a specific environment, set mysql_data_source to something like db_cluster=mysql-1 and environment=prod. This assumes you have configured and applied Fields as described in Step 1: Configure Fields of the Sumo Logic of the Collect Logs and Metrics for MySQL topic.

All monitors are disabled by default on installation. To enable all of the monitors, set the monitors_disabled parameter to false. By default, the monitors will be located in a "MySQL" folder on the Monitors page. To change the name of the folder, update the monitor folder name in the folder variable in the mysql.auto.tfvars file.

  1. If you want the alerts to send email or connection notifications, edit the mysql_notifications.auto.tfvars file to populate the connection_notifications and email_notifications sections. Examples are provided below.

In the variable definition below, replace <CONNECTION_ID> with the connection ID of the Webhook connection. You can obtain the Webhook connection ID by calling the Monitors API.

Pagerduty connection example
connection_notifications = [
    {
      connection_type       = "PagerDuty",
      connection_id         = "<CONNECTION_ID>",
      payload_override      = "{\"service_key\": \"your_pagerduty_api_integration_key\",\"event_type\": \"trigger\",\"description\": \"Alert: Triggered {{TriggerType}} for Monitor {{Name}}\",\"client\": \"Sumo Logic\",\"client_url\": \"{{QueryUrl}}\"}",
      run_for_trigger_types = ["Critical", "ResolvedCritical"]
    },
    {
      connection_type       = "Webhook",
      connection_id         = "<CONNECTION_ID>",
      payload_override      = "",
      run_for_trigger_types = ["Critical", "ResolvedCritical"]
    }
  ]

For information about overriding the payload for different connection types, see Set Up Webhook Connections.

Email notifications example
email_notifications = [
    {
      connection_type       = "Email",
      recipients            = ["abc@example.com"],
      subject               = "Monitor Alert: {{TriggerType}} on {{Name}}",
      time_zone             = "PST",
      message_body          = "Triggered {{TriggerType}} Alert on {{Name}}: {{QueryURL}}",
      run_for_trigger_types = ["Critical", "ResolvedCritical"]
    }
  ]
  1. Install Monitors.
    1. Navigate to the terraform-sumologic-sumo-logic-monitor/monitor_packages/mysql/ directory and run terraform init. This will initialize Terraform and download the required components.
    2. Run terraform plan to view the monitors that Terraform will create or modify.
    3. Run terraform apply.

Installing the MySQL app​

Now that you have set up collection for MySQL, install the Sumo Logic app for MySQL to use the preconfigured searches and dashboards that provide insight into your data.

To install the app:

  1. Select App Catalog.
  2. In the 🔎 Search Apps field, run a search for your desired app, then select it.
  3. Click Install App.
    note

    Sometimes this button says Add Integration.

  4. On the next configuration page, under Select Data Source for your App, complete the following fields:
    • Data Source. Select one of the following options:
      • Choose Source Category and select a source category from the list; or
      • Choose Enter a Custom Data Filter, and enter a custom source category beginning with an underscore. For example, _sourceCategory=MyCategory.
    • Folder Name. You can retain the existing name or enter a custom name of your choice for the app.
    • All Folders (optional). The default location is the Personal folder in your Library. If desired, you can choose a different location and/or click New Folder to add it to a new folder.
  5. Click Next.
  6. Look for the dialog confirming that your app was installed successfully.
    app-success.png

Post-installation

Once your app is installed, it will appear in your Personal folder or the folder that you specified. From here, you can share it with other users in your organization. Dashboard panels will automatically start to fill with data matching the time range query received since you created the panel. Results won't be available immediately, but within about 20 minutes, you'll see completed graphs and maps.

Viewing MySQL dashboards​

Filter with template variables

Template variables provide dynamic dashboards that can rescope data on the fly. As you apply variables to troubleshoot through your dashboard, you view dynamic changes to the data for a quicker resolution to the root cause. You can use template variables to drill down and examine the data on a granular level. For more information, see Filter with template variables.

Overview​

The MySQL - Overview dashboard gives you an at-a-glance view of the state of your database clusters by monitoring key cluster information such as errors, failed logins, errors, queries executed, slow queries, lock waits, uptime and more.

Use this dashboard to:

  • Quickly identify the state of a given database cluster.
MySQL dashboard

Error Logs​

The MySQL - Error Logs dashboard provides insight into database error logs by specifically monitoring database shutdown/start events, errors over time, errors, warnings, and crash recovery attempts.

Use this dashboard to:

  • Quickly identify errors and patterns in logs for troubleshooting.
  • Monitor trends in error logs and identify outliers.
  • Ensure that server start, server stop and crash recovery events are in line with expectations.
  • Dashboard filters allow you to narrow a search for database cluster.
MySQL dashboard

Failed Logins​

The MySQL - Failed Logins dashboard provides insights into all failed login attempts by location, users, and hosts.

Use this dashboard to:

  • Monitor all failed login attempts and identify any unusual or suspicious activity.
MySQL dashboard

Replication​

The MySQL - Replication dashboard provides insights into the state of database replication.

Use this dashboard to:

  • Quickly determine reasons for replication failures.
  • Monitor replication status trends.
MySQL dashboard

Slow Queries​

The MySQL - Slow Queries dashboard provides insights into all slow queries executed on the database.

Note: Slow queries are queries that take 10 seconds or more to execute (default value is 10 seconds as per mysql configuration which can be altered) and excessive slow queries are those that take 15 seconds or more to execute.

Use this dashboard to:

  • Identify all slow queries.
  • Quickly determine which queries have been identified as slow or excessive slow queries.
  • Monitor users and hosts running slow queries.
  • Determine which SQL commands are slower than others.
  • Examine slow query trends to determine if there are periodic performance bottlenecks in your database clusters.
MySQL dashboard

Performance and Resource Metrics​

The MySQL - Performance and Resource Metrics dashboard allows you to monitor the performance and resource usage of your database clusters.

Use this dashboard to:

  • Understand the behavior and performance of your database clusters.
  • Monitor key operational metrics around connections, network traffic, threads running, innodb waits, and locks.
  • Monitor query execution trends to ensure they match up with expectations.
  • Dashboard filters allow you to narrow a search for a specific database cluster.
MySQL dashboard

Performance Schema Metrics​

The MySQL - Performance Schema Metrics dashboard provides insights into the metrics provided by the MySQL Performance Schema, which is a feature for monitoring MySQL Server execution at a low level.

Use this dashboard to:

  • Monitor errors and warning for SQL statements.
  • Monitor statements running without use of index columns.
  • Monitor statistics such as Table and Index waits and read and write lock waits to optimize the performance of your database.
MySQL dashboard

MySQL Alerts​

This section describes the monitors provided with the MySQL app. These monitors are built based on logs and metrics datasets and have preset thresholds based on industry best practices and recommendations.

Monitor name Monitor description Alert Condition Recovery Condition
MySQL - Connection refused This alert fires when connections are refused when the limit of maximum connections is reached within 5 minute time interval. <= 1 > 1
MySQL - High average query run time This alert fires when the average run time of SQL queries for a given schema is greater than or equal to one second within a time interval of 5 minutes. >= 1 < 1
MySQL - High Innodb buffer pool utilizationThis alert fires when we detect that the InnoDB buffer pool utilization is high (>=90%) within a 5 minute time interval.>= 90 < 90
MySQL - Large number of aborted connections This alert fires when we detect that there are 5 or more aborted connections identified within a time interval of 5 minutes. >= 5 < 5
MySQL - Large number of internal connection errors This alert fires when we detect that there are 5 or more internal connection errors within a time interval of 5 minutes. >= 5 < 5
MySQL - Large number of slow queries This alert fires when we detect that there are 5 or more slow queries within a 5 minute time interval. >= 5 < 5
MySQL - Large number of statement errors This alert fires when we detect that there are 5 or more statement errors within a 5 minute time interval. >= 5 < 5
MySQL - Large number of statement warnings This alert fires when we detect that there are 20 or more statement warnings within a 5 minute time interval. >= 20 < 20
MySQL - No index used in the SQL statements This alert fires when we detect that there are 5 or more statements not using an index in the sql query within a 5 minute time interval. >= 5 < 5
MySQL - Excessive Slow Query Detected This alert fires when we detect the average time to execute a query is more than 5 seconds over a 24 hour time-period >=1 < 1
MySQL - Follower replication lag detected This alert fires when we detect that the average replication lag is greater than or equal to 900 seconds within a 5 minute time interval. >= 900 < 900
MySQL - Instance down This alert fires when we detect that a MySQL instance is down within last 5 minutes interval. >=1 < 1

MySQL Metrics​

Here are the Telegraf metrics for MySQL collected by the MySQL app.

mysql_aborted_clients
mysql_aborted_connects
mysql_bytes_received
mysql_bytes_sent
mysql_commands_delete
mysql_commands_insert
mysql_commands_select
mysql_commands_update
mysql_connection_errors_internal
mysql_connection_errors_max_connections
mysql_connections
mysql_created_tmp_disk_tables
mysql_created_tmp_files
mysql_created_tmp_tables
mysql_innodb_buffer_pool_pages_free
mysql_innodb_buffer_pool_pages_total
mysql_innodb_buffer_pool_read_requests
mysql_innodb_buffer_pool_reads
mysql_innodb_buffer_pool_wait_free
mysql_innodb_data_fsyncs
mysql_innodb_data_read
mysql_innodb_data_writes
mysql_innodb_log_waits
mysql_innodb_row_lock_current_waits
mysql_innodb_row_lock_waits
mysql_innodb_rows_deleted
mysql_innodb_rows_inserted
mysql_innodb_rows_read
mysql_innodb_rows_updated
mysql_locked_connects
mysql_mysqlx_connections_accepted
mysql_mysqlx_connections_closed
mysql_mysqlx_connections_rejected
mysql_mysqlx_worker_threads
mysql_mysqlx_worker_threads_active
mysql_opened_files
mysql_opened_tables
mysql_perf_schema_events_statements_errors_total
mysql_perf_schema_events_statements_no_index_used_total
mysql_perf_schema_events_statements_rows_affected_total
mysql_perf_schema_events_statements_rows_examined_total
mysql_perf_schema_events_statements_rows_sent_total
mysql_perf_schema_events_statements_seconds_total
mysql_perf_schema_events_statements_sort_merge_passes_total
mysql_perf_schema_events_statements_sort_rows_total
mysql_perf_schema_events_statements_tmp_disk_tables_total
mysql_perf_schema_events_statements_tmp_tables_total
mysql_perf_schema_events_statements_total
mysql_perf_schema_events_statements_warnings_total
mysql_perf_schema_index_io_waits_seconds_total_delete		mysql_perf_schema_index_io_waits_seconds_total_fetch
mysql_perf_schema_index_io_waits_seconds_total_insert
mysql_perf_schema_index_io_waits_seconds_total_update
mysql_perf_schema_index_io_waits_total_delete
mysql_perf_schema_index_io_waits_total_fetch
mysql_perf_schema_index_io_waits_total_insert
mysql_perf_schema_index_io_waits_total_update
mysql_perf_schema_read
mysql_perf_schema_read_high_priority
mysql_perf_schema_read_no_insert
mysql_perf_schema_read_normal
mysql_perf_schema_read_with_shared_locks
mysql_perf_schema_table_io_waits_seconds_total_delete
mysql_perf_schema_table_io_waits_seconds_total_fetch
mysql_perf_schema_table_io_waits_seconds_total_insert
mysql_perf_schema_table_io_waits_seconds_total_update
mysql_perf_schema_table_io_waits_total_delete
mysql_perf_schema_table_io_waits_total_fetch
mysql_perf_schema_table_io_waits_total_insert
mysql_perf_schema_table_io_waits_total_update
mysql_perf_schema_write
mysql_perf_schema_write_allow_write
mysql_perf_schema_write_concurrent_insert
mysql_perf_schema_write_low_priority
mysql_perf_schema_write_normal
mysql_qcache_hits
mysql_qcache_inserts
mysql_queries
mysql_questions
mysql_select_full_join
mysql_select_full_range_join
mysql_select_range
mysql_select_range_check
mysql_select_scan
mysql_slow_queries
mysql_sort_merge_passes
mysql_sort_range
mysql_sort_rows
mysql_sort_scan
mysql_table_locks_immediate
mysql_table_locks_waited
mysql_table_open_cache_hits
mysql_table_open_cache_misses
mysql_table_open_cache_overflows
mysql_threads_cached
mysql_threads_connected
mysql_threads_created
mysql_threads_running
mysql_uptime
Edit this page
Last updated on by John Pipkin (Sumo Logic)
Status
Legal
Privacy Statement
Terms of Use

Copyright © 2024 by Sumo Logic, Inc.