MySQL

The MySQL integration collects performance metrics related to InnoDB, the buffer pool, and various other operations. It also collects general, error, and slow-query logs and parses them into a JSON payload. Error logs are parsed for their error code and subsystem. Slow-query logs are parsed into key-value pairs that describe the performance of a query, including query time and rows examined.

For more information about MySQL, see mysql.com .

Prerequisites

To collect MySQL telemetry, you must install Ops Agent version 2.8.0 or higher.

This receiver supports MySQL versions 5.7 and 8.0.

Configure your MySQL instance

The mysql receiver connects by default to a local MySQL server using a Unix socket and Unix authentication as the root user.

Configure the Ops Agent for MySQL

Following the guide for Configuring the Ops Agent, add the required elements to collect logs and metrics from your MySQL instances, and restart the agent.

Example configuration

The following command creates the configuration file to collect and ingest logs and metrics for MySQL and restarts the Ops Agent on Linux.

sudo tee /etc/google-cloud-ops-agent/config.yaml > /dev/null << EOF
logging:
  receivers:
    mysql_error:
      type: mysql_error
    mysql_general:
      type: mysql_general
    mysql_slow:
      type: mysql_slow
  service:
    pipelines:
      mysql:
        receivers:
          - mysql_error
          - mysql_general
          - mysql_slow
metrics:
  receivers:
    mysql:
      type: mysql
  service:
    pipelines:
      mysql:
        receivers:
          - mysql
EOF
sudo service google-cloud-ops-agent restart

To connect to your MySQL instance using a TCP connection with a username and password, specify the username and password in your metrics receiver. For example:

metrics:
  receivers:
    mysql:
      type: mysql
      endpoint: localhost:3306
      password: pwd
      username: usr
  service:
    pipelines:
      mysql:
        receivers:
          - mysql

Configure logs collection

To ingest logs from MySQL, you must create receivers for the logs MySQL produces and then create a pipeline for the new receivers.

To configure a receiver for your mysql_error logs, specify the following fields:

Field Default Description
type This value must be mysql_error.
include_paths [/var/log/mysqld.log, /var/log/mysql/mysqld.log, /var/log/mysql/error.log] A list of filesystem paths to read by tailing each file. A wild card (*) can be used in the paths; for example, /var/log/mysql/*.log.
exclude_paths [] A list of filesystem path patterns to exclude from the set matched by include_paths.
wildcard_refresh_interval 60s The interval at which wildcard file paths in include_paths are refreshed. Given as a time duration, for example 30s or 2m. This property might be useful under high logging throughputs where log files are rotated faster than the default interval.


To configure a receiver for your mysql_general logs, specify the following fields:

Field Default Description
type This value must be mysql_general.
include_paths [/var/lib/mysql/${HOSTNAME}.log] The log files to read.
exclude_paths [] A list of filesystem path patterns to exclude from the set matched by include_paths.
wildcard_refresh_interval 60s The interval at which wildcard file paths in include_paths are refreshed. Given as a time duration, for example 30s or 2m. This property might be useful under high logging throughputs where log files are rotated faster than the default interval.


To configure a receiver for your mysql_slow logs, specify the following fields:

Field Default Description
type This value must be mysql_slow.
include_paths [/var/lib/mysql/${HOSTNAME}-slow.log] The log files to read.
exclude_paths [] A list of filesystem path patterns to exclude from the set matched by include_paths.
wildcard_refresh_interval 60s The interval at which wildcard file paths in include_paths are refreshed. Given as a time duration, for example 30s or 2m. This property might be useful under high logging throughputs where log files are rotated faster than the default interval.

What is logged

The logName of the mysql_error, mysql_general, and mysql_slow logs are derived from the receiver IDs specified in the configuration.

Depending on your MySQL version, mysql_error logs might contain the following fields inside the LogEntry:

Field Type Description
jsonPayload.level string Log entry level
jsonPayload.tid number Thread ID where the log originated
jsonPayload.errorCode string MySQL error code associated with the log
jsonPayload.subsystem string MySQL subsystem where the log originated
jsonPayload.message string Log message
severity string (LogSeverity) Log entry level (translated)
timestamp string (Timestamp) Time the request was received

Any fields that are blank or missing will not be present in the log entry.


mysql_general logs contain the following fields in the LogEntry:

Field Type Description
jsonPayload.tid number Thread ID where the log originated
jsonPayload.command string Type of database action being logged
jsonPayload.message string Log of the database action
timestamp string (Timestamp) Time the entry was logged


mysql_slow logs contain the following fields in the LogEntry:

Field Type Description
jsonPayload.user string User that executed the query
jsonPayload.database string Database where the query was executed
jsonPayload.host string Host of the database instance
jsonPayload.ipAddress string Address of the database instance
jsonPayload.tid number Thread ID where the query was logged
jsonPayload.queryTime number The statement execution time in seconds
jsonPayload.lockTime number The time to acquire locks in seconds
jsonPayload.rowsSent number The number of rows sent to the client
jsonPayload.rowsExamined number The number of rows examined by the server layer
jsonPayload.errorNumber number The statement error number, or 0 if no error occurred
jsonPayload.killed number If the statement was terminated, the error number indicating why, or 0 if the statement terminated normally
jsonPayload.bytesReceived number The number of bytes received from all clients
jsonPayload.bytesSent number The number of bytes sent to all clients
jsonPayload.readFirst number The number of times the first entry in an index was read
jsonPayload.readLast number The number of requests to read the last key in an index
jsonPayload.readKey number The number of requests to read a row based on a key
jsonPayload.readNext number The number of requests to read the next row in key order
jsonPayload.readPrev number The number of requests to read the previous row in key order
jsonPayload.readRnd number The number of requests to read a row based on a fixed position
jsonPayload.readRndNext number The number of requests to read the next row in the data file
jsonPayload.sortMergePasses number The number of merge passes that the sort algorithm has had to do
jsonPayload.sortRangeCount number The number of sorts that were done using ranges
jsonPayload.sortRows number The number of sorted rows
jsonPayload.sortScanCount number The number of sorts that were done by scanning the table
jsonPayload.createdTmpDiskTables number The number of internal on-disk temporary tables created by the server
jsonPayload.createdTmpTables number The number of internal temporary tables created by the server
jsonPayload.startTime string The statement execution start time
jsonPayload.endTime string The statement execution end time
jsonPayload.message string Full text of the query
timestamp string (Timestamp) Time the entry was logged

  These fields are only provided if the log_slow_extra system variable is set to 'ON'. This variable is available as of MySQL 8.0.14.

For information about these fields, refer to the MySQL documentation.

Configure metrics collection

To collect metrics from MySQL, you must create a receiver for MySQL metrics and then create a pipeline for the new receiver. To configure a receiver for your MySQL metrics, specify the following fields:

Field Default Description
type The value must be mysql.
endpoint localhost:3306 The url exposed by MySQL.
collection_interval 60s A time.Duration value, such as 30s or 5m.
username root The username used to connect to the server.
password The password used to connect to the server.

What is monitored

The following table provides the list of metrics that the Ops Agent collects from the MySQL instance.

Metric type 
Kind, Type
Monitored resources
Labels
workload.googleapis.com/mysql.buffer_pool_operations
CUMULATIVEINT64
gce_instance
operation
workload.googleapis.com/mysql.buffer_pool_pages
GAUGEDOUBLE
gce_instance
kind
workload.googleapis.com/mysql.buffer_pool_size
GAUGEDOUBLE
gce_instance
kind
workload.googleapis.com/mysql.commands
CUMULATIVEINT64
gce_instance
command
workload.googleapis.com/mysql.double_writes
CUMULATIVEINT64
gce_instance
kind
workload.googleapis.com/mysql.handlers
CUMULATIVEINT64
gce_instance
kind
workload.googleapis.com/mysql.locks
CUMULATIVEINT64
gce_instance
kind
workload.googleapis.com/mysql.log_operations
CUMULATIVEINT64
gce_instance
operation
workload.googleapis.com/mysql.operations
CUMULATIVEINT64
gce_instance
operation
workload.googleapis.com/mysql.page_operations
CUMULATIVEINT64
gce_instance
operation
workload.googleapis.com/mysql.row_locks
CUMULATIVEINT64
gce_instance
kind
workload.googleapis.com/mysql.row_operations
CUMULATIVEINT64
gce_instance
operation
workload.googleapis.com/mysql.sorts
CUMULATIVEINT64
gce_instance
kind
workload.googleapis.com/mysql.threads
GAUGEDOUBLE
gce_instance
kind

Sample dashboard

You can view the metrics from this integration using a sample Cloud Monitoring dashboard. Complete the Installing sample dashboards procedures to import the MySQL GCE Overview dashboard from the Sample library and view charts that display your MySQL metrics.

Verify the configuration

You can use the Logs Explorer and Metrics Explorer to verify that you correctly configured the MySQL receiver. It might take one or two minutes for the Ops Agent to begin collecting logs and metrics.

To verify the logs are ingested, go to the Logs Explorer and run the following query to view the MySQL logs:

resource.type="gce_instance"
logName=("projects/PROJECT_ID/logs/mysql_error" OR "projects/PROJECT_ID/logs/mysql_general" OR "projects/PROJECT_ID/logs/mysql_slow")


To verify the metrics are ingested, go to Metrics Explorer and run the following query in the MQL tab.

fetch gce_instance
| metric 'workload.googleapis.com/mysql.commands'
| align rate(1m)
| every 1m

What's next

For a walkthrough on how to use Ansible to install the Ops Agent, configure a third-party application, and install a sample dashboard, see the Install the Ops Agent to troubleshoot third-party applications video.