Mysql

Mysql

MySQL is an open-source relational database management system. Its name is a combination of "My", the name of co-founder Michael Widenius`s daughter, and "SQL", the abbreviation for Structured Query Language.

Available solutions




Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/mysql_agent


Template DB MySQL by Zabbix agent

Overview

For Zabbix version: 4.4
The template is developed for monitoring DBMS MySQL and its forks.

This template was tested on:

  • MySQL, version 5.7, 8.0
  • Percona, version 8.0
  • MariaDB, version 10.4
  • Zabbix, version 4.2.1

Setup

  1. Install Zabbix agent and MySQL client. If necessary, add the path to the mysql and mysqladmin utilities to the global environment variable PATH.
  2. Copy template_db_mysql.conf into folder with Zabbix agent configuration (/etc/zabbix/zabbix_agentd.d/ by default). Don't forget to restart zabbix-agent.
  3. Create MySQL user for monitoring (<password> at your discretion):
CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY '<password>';
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'%';

For more information please read the MYSQL documentation https://dev.mysql.com/doc/refman/8.0/en/grant.html

  1. Create .my.cnf in home directory of Zabbix agent for Linux (/var/lib/zabbix by default ) or my.cnf in c:\ for Windows. The file must have three strings:
[client]
user=zbx_monitor
password=<password>

Zabbix configuration

No specific Zabbix configuration is required.

Macros used

Name Description Default
{$MYSQL.ABORTED_CONN.MAX.WARN}

The number of failed attempts to connect to the MySQL server for trigger expression.

3
{$MYSQL.BUFF_UTIL.MIN.WARN}

The minimum buffer pool utilization in percent for trigger expression.

50
{$MYSQL.HOST}

Hostname or IP of MySQL host or container.

localhost
{$MYSQL.PORT}

MySQL service port.

3306
{$MYSQL.REPL_LAG.MAX.WARN}

The lag of slave from master for trigger expression.

30m
{$MYSQL.SLOW_QUERIES.MAX.WARN}

The number of slow queries for trigger expression.

3

Template links

There are no template links in this template.

Discovery rules

Name Description Type Key and additional info
Databases discovery

Scanning databases in DBMS.

ZABBIX_PASSIVE mysql.db.discovery["{$MYSQL.HOST}","{$MYSQL.PORT}"]

Preprocessing:

- JAVASCRIPT: return JSON.stringify(value.split("\n").map(function (name) { return ({"{#DBNAME}": name}); }));

- DISCARD_UNCHANGED_HEARTBEAT: 1d

Filter:

AND_OR

- A: {#DBNAME} NOT_MATCHES_REGEX information_schema

Replication discovery

If "show slave status" returns Master_Host, "Replication: *" items are created.

ZABBIX_PASSIVE mysql.replication.discovery["{$MYSQL.HOST}","{$MYSQL.PORT}"]

Preprocessing:

- JAVASCRIPT: var matches = value.match(/Master_Host.*>(.*)<.*/); if (matches) { return JSON.stringify([{"{#MASTERHOST}": matches[1]}]); } return '[]';

- DISCARD_UNCHANGED_HEARTBEAT: 1d

Items collected

Group Name Description Type Key and additional info
MySQL MySQL: Status ZABBIX_PASSIVE mysql.ping["{$MYSQL.HOST}","{$MYSQL.PORT}"]

Preprocessing:

- JAVASCRIPT: return value.indexOf('is alive') !== -1 ? 1 : 0;

- DISCARD_UNCHANGED_HEARTBEAT: 10m

MySQL MySQL: Version ZABBIX_PASSIVE mysql.version["{$MYSQL.HOST}","{$MYSQL.PORT}"]

Preprocessing:

- REGEX: (Server version)\s+(.+) \2

- DISCARD_UNCHANGED_HEARTBEAT: 1d

MySQL MySQL: Uptime

The number of seconds that the server has been up.

DEPENDENT mysql.uptime

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Uptime']/field[@name='Value']/text()

MySQL MySQL: Aborted clients per second

The number of connections that were aborted because the client died without closing the connection properly.

DEPENDENT mysql.aborted_clients.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Aborted_clients']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Aborted connections per second

The number of failed attempts to connect to the MySQL server.

DEPENDENT mysql.aborted_connects.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Aborted_connects']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Connection errors accept per second

Number of errors that occurred during calls to accept() on the listening port.

DEPENDENT mysql.connection_errors_accept.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Connection_errors_accept']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Connection errors internal per second

Number of refused connections due to internal server errors, for example out of memory errors, or failed thread starts.

DEPENDENT mysql.connection_errors_internal.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Connection_errors_internal']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Connection errors max connections per second

Number of refused connections due to the max_connections limit being reached.

DEPENDENT mysql.connection_errors_max_connections.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Connection_errors_max_connections']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Connection errors peer address per second

Number of errors while searching for the connecting client IP address.

DEPENDENT mysql.connection_errors_peer_address.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Connection_errors_peer_address']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Connection errors select per second

Number of errors during calls to select() or poll() on the listening port. The client would not necessarily have been rejected in these cases.

DEPENDENT mysql.connection_errors_select.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Connection_errors_select']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Connection errors tcpwrap per second

Number of connections the libwrap library refused.

DEPENDENT mysql.connection_errors_tcpwrap.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Connection_errors_tcpwrap']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Connections per second

The number of connection attempts (successful or not) to the MySQL server.

DEPENDENT mysql.connections.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Connections']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Max used connections

The maximum number of connections that have been in use simultaneously since the server started.

DEPENDENT mysql.max_used_connections

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Max_used_connections']/field[@name='Value']/text()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

MySQL MySQL: Threads cached

The number of threads in the thread cache.

DEPENDENT mysql.threads_cached

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Threads_cached']/field[@name='Value']/text()

MySQL MySQL: Threads connected

The number of currently open connections.

DEPENDENT mysql.threads_connected

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Threads_connected']/field[@name='Value']/text()

MySQL MySQL: Threads created

The number of threads created to handle connections. If Threads_created is big, you may want to increase the thread_cache_size value. The cache miss rate can be calculated as Threads_created/Connections.

DEPENDENT mysql.threads_created

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Threads_created']/field[@name='Value']/text()

MySQL MySQL: Threads running

The number of threads that are not sleeping.

DEPENDENT mysql.threads_running

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Threads_running']/field[@name='Value']/text()

MySQL MySQL: Buffer pool efficiency

The item shows how effectively the buffer pool is serving reads.

CALCULATED mysql.buffer_pool_efficiency

Expression:

last(mysql.innodb_buffer_pool_reads) / ( last(mysql.innodb_buffer_pool_read_requests) + ( last(mysql.innodb_buffer_pool_read_requests) = 0 ) ) * 100 * ( last(mysql.innodb_buffer_pool_read_requests) > 0 )
MySQL MySQL: Buffer pool utilization

Ratio of used to total pages in the buffer pool.

CALCULATED mysql.buffer_pool_utilization

Expression:

( last(mysql.innodb_buffer_pool_pages_total) - last(mysql.innodb_buffer_pool_pages_free) ) / ( last(mysql.innodb_buffer_pool_pages_total) + ( last(mysql.innodb_buffer_pool_pages_total) = 0 ) ) * 100 * ( last(mysql.innodb_buffer_pool_pages_total) > 0 )
MySQL MySQL: Created tmp files on disk

How many temporary files mysqld has created.

DEPENDENT mysql.created_tmp_files

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Created_tmp_files']/field[@name='Value']/text()

MySQL MySQL: Created tmp tables on disk

The number of internal on-disk temporary tables created by the server while executing statements.

DEPENDENT mysql.created_tmp_disk_tables

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Created_tmp_disk_tables']/field[@name='Value']/text()

MySQL MySQL: Created tmp tables on memory

The number of internal temporary tables created by the server while executing statements.

DEPENDENT mysql.created_tmp_tables

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Created_tmp_tables']/field[@name='Value']/text()

MySQL MySQL: InnoDB buffer pool pages free

The total size of the InnoDB buffer pool, in pages.

DEPENDENT mysql.innodb_buffer_pool_pages_free

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Innodb_buffer_pool_pages_free']/field[@name='Value']/text()

MySQL MySQL: InnoDB buffer pool pages total

The total size of the InnoDB buffer pool, in pages.

DEPENDENT mysql.innodb_buffer_pool_pages_total

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Innodb_buffer_pool_pages_total']/field[@name='Value']/text()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

MySQL MySQL: InnoDB buffer pool read requests per second

The number of logical read requests per second.

DEPENDENT mysql.innodb_buffer_pool_read_requests.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Innodb_buffer_pool_read_requests']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: InnoDB buffer pool reads per second

The number of logical reads per second that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.

DEPENDENT mysql.innodb_buffer_pool_reads.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Innodb_buffer_pool_reads']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: InnoDB row lock time

The total time spent in acquiring row locks for InnoDB tables, in milliseconds.

DEPENDENT mysql.innodb_row_lock_time

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Innodb_row_lock_time']/field[@name='Value']/text()

- MULTIPLIER: 0.001

- DISCARD_UNCHANGED_HEARTBEAT: 1h

MySQL MySQL: InnoDB row lock time max

The maximum time to acquire a row lock for InnoDB tables, in milliseconds.

DEPENDENT mysql.innodb_row_lock_time_max

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Innodb_row_lock_time_max']/field[@name='Value']/text()

- MULTIPLIER: 0.001

- DISCARD_UNCHANGED_HEARTBEAT: 1h

MySQL MySQL: InnoDB row lock waits

The number of times operations on InnoDB tables had to wait for a row lock.

DEPENDENT mysql.innodb_row_lock_waits

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Innodb_row_lock_waits']/field[@name='Value']/text()

MySQL MySQL: Slow queries per second

The number of queries that have taken more than long_query_time seconds.

DEPENDENT mysql.slow_queries.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Slow_queries']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Bytes received

The number of bytes received from all clients.

DEPENDENT mysql.bytes_received.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Bytes_received']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Bytes sent

The number of bytes sent to all clients.

DEPENDENT mysql.bytes_sent.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Bytes_sent']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Command Delete per second

The Com_delete counter variable indicates the number of times the delete statement has been executed.

DEPENDENT mysql.com_delete.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Com_delete']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Command Insert per second

The Com_insert counter variable indicates the number of times the insert statement has been executed.

DEPENDENT mysql.com_insert.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Com_insert']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Command Select per second

The Com_select counter variable indicates the number of times the select statement has been executed.

DEPENDENT mysql.com_select.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Com_select']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Command Update per second

The Com_update counter variable indicates the number of times the update statement has been executed.

DEPENDENT mysql.com_update.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Com_update']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Queries per second

The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable.

DEPENDENT mysql.queries.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Queries']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Questions per second

The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable.

DEPENDENT mysql.questions.rate

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Questions']/field[@name='Value']/text()

- CHANGE_PER_SECOND

MySQL MySQL: Size of database {#DBNAME}

-

ZABBIX_PASSIVE mysql.dbsize["{$MYSQL.HOST}","{$MYSQL.PORT}","{#DBNAME}"]

Preprocessing:

- DISCARD_UNCHANGED_HEARTBEAT: 1h

MySQL MySQL: Replication Seconds Behind Master {#MASTERHOST}

The number of seconds that the slave SQL thread is behind processing the master binary log.

A high number (or an increasing one) can indicate that the slave is unable to handle events

from the master in a timely fashion.

DEPENDENT mysql.seconds_behind_master["{#MASTERHOST}"]

Preprocessing:

- XMLPATH: /resultset/row/field[@name='Seconds_Behind_Master']/text()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

- NOT_MATCHES_REGEX: null

⛔️ON_FAIL: CUSTOM_ERROR -> Replication is not performed.

MySQL MySQL: Replication Slave IO Running {#MASTERHOST}

Whether the I/O thread for reading the master's binary log is running.

Normally, you want this to be Yes unless you have not yet started replication or have

explicitly stopped it with STOP SLAVE.

DEPENDENT mysql.slave_io_running["{#MASTERHOST}"]

Preprocessing:

- XMLPATH: /resultset/row/field[@name='Slave_IO_Running']/text()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

MySQL MySQL: Replication Slave SQL Running {#MASTERHOST}

Whether the SQL thread for executing events in the relay log is running.

As with the I/O thread, this should normally be Yes.

DEPENDENT mysql.slave_sql_running["{#MASTERHOST}"]

Preprocessing:

- XMLPATH: /resultset/row/field[@name='Slave_SQL_Running']/text()

- DISCARD_UNCHANGED_HEARTBEAT: 1h

Zabbix_raw_items MySQL: Get status variables

The item gets server global status information.

ZABBIX_PASSIVE mysql.get_status_variables["{$MYSQL.HOST}","{$MYSQL.PORT}"]
Zabbix_raw_items MySQL: InnoDB buffer pool read requests

The number of logical read requests.

DEPENDENT mysql.innodb_buffer_pool_read_requests

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Innodb_buffer_pool_read_requests']/field[@name='Value']/text()

Zabbix_raw_items MySQL: InnoDB buffer pool reads

The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.

DEPENDENT mysql.innodb_buffer_pool_reads

Preprocessing:

- XMLPATH: /resultset/row[field/text()='Innodb_buffer_pool_reads']/field[@name='Value']/text()

Zabbix_raw_items MySQL: Replication Slave status {#MASTERHOST}

The item gets status information on essential parameters of the slave threads.

ZABBIX_PASSIVE mysql.slave_status["{$MYSQL.HOST}","{$MYSQL.PORT}","{#MASTERHOST}"]

Triggers

Name Description Expression Severity Dependencies and additional info
MySQL: Service is down {TEMPLATE_NAME:mysql.ping["{$MYSQL.HOST}","{$MYSQL.PORT}"].last()}=0 HIGH
MySQL: Version has changed (new version value received: {ITEM.VALUE})

MySQL version has changed. Ack to close.

{TEMPLATE_NAME:mysql.version["{$MYSQL.HOST}","{$MYSQL.PORT}"].diff()}=1 and {TEMPLATE_NAME:mysql.version["{$MYSQL.HOST}","{$MYSQL.PORT}"].strlen()}>0 INFO

Manual close: YES

MySQL: Service has been restarted (uptime < 10m)

MySQL uptime is less than 10 minutes.

{TEMPLATE_NAME:mysql.uptime.last()}<10m INFO
MySQL: Server has aborted connections (over {$MYSQL.ABORTED_CONN.MAX.WARN} for 5m)

The number of failed attempts to connect to the MySQL server is more than {$MYSQL.ABORTED_CONN.MAX.WARN} in the last 5 minutes.

{TEMPLATE_NAME:mysql.aborted_connects.rate.min(5m)}>{$MYSQL.ABORTED_CONN.MAX.WARN} AVERAGE

Depends on:

- MySQL: Refused connections (max_connections limit reached)

MySQL: Refused connections (max_connections limit reached)

Number of refused connections due to the max_connections limit being reached.

{TEMPLATE_NAME:mysql.connection_errors_max_connections.rate.last()}>0 AVERAGE
MySQL: Buffer pool utilization is too low (less {$MYSQL.BUFF_UTIL.MIN.WARN}% for 5m)

The buffer pool utilization is less than {$MYSQL.BUFF_UTIL.MIN.WARN}% in the last 5 minutes. This means that there is a lot of unused RAM allocated for the buffer pool, which you can easily reallocate at the moment.

{TEMPLATE_NAME:mysql.buffer_pool_utilization.max(5m)}<{$MYSQL.BUFF_UTIL.MIN.WARN} WARNING
MySQL: Server has slow queries (over {$MYSQL.SLOW_QUERIES.MAX.WARN} for 5m)

The number of slow queries is more than {$MYSQL.SLOW_QUERIES.MAX.WARN} in the last 5 minutes.

{TEMPLATE_NAME:mysql.slow_queries.rate.min(5m)}>{$MYSQL.SLOW_QUERIES.MAX.WARN} WARNING
MySQL: Replication lag is too high (over {$MYSQL.REPL_LAG.MAX.WARN} for 5m)

-

{TEMPLATE_NAME:mysql.seconds_behind_master["{#MASTERHOST}"].min(5m)}>{$MYSQL.REPL_LAG.MAX.WARN} WARNING
MySQL: The slave I/O thread is not running

Whether the I/O thread for reading the master's binary log is running.

{TEMPLATE_NAME:mysql.slave_io_running["{#MASTERHOST}"].count(#1,"No",eq)}=1 AVERAGE
MySQL: The slave I/O thread is not connected to a replication master

-

{TEMPLATE_NAME:mysql.slave_io_running["{#MASTERHOST}"].count(#1,"Yes",ne)}=1 WARNING

Depends on:

- MySQL: The slave I/O thread is not running

MySQL: The SQL thread is not running

Whether the SQL thread for executing events in the relay log is running.

{TEMPLATE_NAME:mysql.slave_sql_running["{#MASTERHOST}"].count(#1,"No",eq)}=1 WARNING

Depends on:

- MySQL: The slave I/O thread is not running

MySQL: Failed to get items (no data for 30m)

Zabbix has not received data for items for the last 30 minutes.

{TEMPLATE_NAME:mysql.get_status_variables["{$MYSQL.HOST}","{$MYSQL.PORT}"].nodata(30m)}=1 WARNING

Depends on:

- MySQL: Service is down

Feedback

Please report any issues with the template at https://support.zabbix.com

You can also provide feedback, discuss the template or ask for help with it at ZABBIX forums.

Add your solution