MariaDB

The MariaDB 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 MariaDB, see mariadb.org .

Prerequisites

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

This receiver supports MariaDB versions 10.1.X through 10.7.X.

Configure your MariaDB instance

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system (RDBMS). To collect logs and metrics for MariaDB, use the mysql receivers.

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

Configure the Ops Agent for MariaDB

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

Example configuration

The following command creates the configuration file to collect and ingest logs and metrics for MariaDB 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 MariaDB 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 MariaDB, you must create receivers for the logs MariaDB 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`.
`record_log_file_path`	`false`	If set to `true`, then the path to the specific file from which the log record was obtained appears in the output log entry as the value of the `agent.googleapis.com/log_file_path` label. When using a wildcard, only the path of the file from which the record was obtained is recorded.
`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`.
`record_log_file_path`	`false`	If set to `true`, then the path to the specific file from which the log record was obtained appears in the output log entry as the value of the `agent.googleapis.com/log_file_path` label. When using a wildcard, only the path of the file from which the record was obtained is recorded.
`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`.
`record_log_file_path`	`false`	If set to `true`, then the path to the specific file from which the log record was obtained appears in the output log entry as the value of the `agent.googleapis.com/log_file_path` label. When using a wildcard, only the path of the file from which the record was obtained is recorded.
`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 MariaDB 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	MariaDB error code associated with the log
`jsonPayload.subsystem`	string	MariaDB 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 MariaDB, you must create a receiver for MariaDB metrics and then create a pipeline for the new receiver.

This receiver does not support the use of multiple instances in the configuration, for example, to monitor multiple endpoints. All such instances write to the same time series, and Cloud Monitoring has no way to distinguish among them.

To configure a receiver for your MariaDB metrics, specify the following fields:

Field	Default	Description
`type`		The value must be `mysql`.
`endpoint`	`localhost:3306`	The url exposed by MariaDB.
`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 MariaDB instance.

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

Verify the configuration

You can use the Logs Explorer and Metrics Explorer to verify that you correctly configured the MariaDB 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 MariaDB 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.