Перейти к основному содержимому

MySQL by Zabbix agent 2

Macros used​

NameValue
{$MYSQL.ABORTED_CONN.MAX.WARN}3
{$MYSQL.BUFF_UTIL.MIN.WARN}50
{$MYSQL.CREATED_TMP_DISK_TABLES.MAX.WARN}10
{$MYSQL.CREATED_TMP_FILES.MAX.WARN}10
{$MYSQL.CREATED_TMP_TABLES.MAX.WARN}30
{$MYSQL.DSN}<Put your DSN>
{$MYSQL.INNODB_LOG_FILES}2
{$MYSQL.PASSWORD}

-

{$MYSQL.REPL_LAG.MAX.WARN}30m
{$MYSQL.SLOW_QUERIES.MAX.WARN}3
{$MYSQL.USER}

-

Items collected​

NameDescriptionTypeIntervalKey and additional info
MySQL: Aborted clients per secondNumber of connections that were aborted because the client died without closing the connection properly.DEPENDENT

-

mysql.aborted_clients.rate
MySQL: Aborted connections per secondNumber of failed attempts to connect to the MySQL server.DEPENDENT

-

mysql.aborted_connects.rate
MySQL: Binlog cache disk useNumber of transactions that used a temporary disk cache because they could not fit in the regular binary log cache, being larger than binlog_cache_size.DEPENDENT

-

mysql.binlog_cache_disk_use
MySQL: Buffer pool efficiencyThe item shows how effectively the buffer pool is serving reads.CALCULATED

-

mysql.buffer_pool_efficiency
MySQL: Buffer pool utilizationRatio of used to total pages in the buffer pool.CALCULATED

-

mysql.buffer_pool_utilization
MySQL: Bytes receivedNumber of bytes received from all clients.DEPENDENT

-

mysql.bytes_received.rate
MySQL: Bytes sentNumber of bytes sent to all clients.DEPENDENT

-

mysql.bytes_sent.rate
MySQL: Command Delete per secondThe Com_delete counter variable indicates the number of times the delete statement has been executed.DEPENDENT

-

mysql.com_delete.rate
MySQL: Command Insert per secondThe Com_insert counter variable indicates the number of times the insert statement has been executed.DEPENDENT

-

mysql.com_insert.rate
MySQL: Command Select per secondThe Com_select counter variable indicates the number of times the select statement has been executed.DEPENDENT

-

mysql.com_select.rate
MySQL: Command Update per secondThe Com_update counter variable indicates the number of times the update statement has been executed.DEPENDENT

-

mysql.com_update.rate
MySQL: Connections per secondNumber of connection attempts (successful or not) to the MySQL server.DEPENDENT

-

mysql.connections.rate
MySQL: Connection errors accept per secondNumber of errors that occurred during calls to accept() on the listening port.DEPENDENT

-

mysql.connection_errors_accept.rate
MySQL: Connection errors internal per secondNumber of refused connections due to internal server errors, for example, out of memory errors, or failed thread starts.DEPENDENT

-

mysql.connection_errors_internal.rate
MySQL: Connection errors max connections per secondNumber of refused connections due to the max_connections limit being reached.DEPENDENT

-

mysql.connection_errors_max_connections.rate
MySQL: Connection errors peer address per secondNumber of errors while searching for the connecting client IP address.DEPENDENT

-

mysql.connection_errors_peer_address.rate
MySQL: Connection errors select per secondNumber 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
MySQL: Connection errors tcpwrap per secondNumber of connections the libwrap library has refused.DEPENDENT

-

mysql.connection_errors_tcpwrap.rate
MySQL: Created tmp tables on disk per secondNumber of internal on-disk temporary tables created by the server while executing statements.DEPENDENT

-

mysql.created_tmp_disk_tables.rate
MySQL: Created tmp files on disk per secondHow many temporary files mysqld has created.DEPENDENT

-

mysql.created_tmp_files.rate
MySQL: Created tmp tables on memory per secondNumber of internal temporary tables created by the server while executing statements.DEPENDENT

-

mysql.created_tmp_tables.rate
MySQL: Get status variablesThe item gets server global status information.

-

-

mysql.get_status_variables["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWORD}"]
MySQL: InnoDB buffer pool pages freeThe total size of the InnoDB buffer pool, in pages.DEPENDENT

-

mysql.innodb_buffer_pool_pages_free
MySQL: InnoDB buffer pool pages totalThe total size of the InnoDB buffer pool, in pages.DEPENDENT

-

mysql.innodb_buffer_pool_pages_total
MySQL: InnoDB buffer pool readsNumber of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from the disk.DEPENDENT

-

mysql.innodb_buffer_pool_reads
MySQL: InnoDB buffer pool reads per secondNumber of logical reads per second that InnoDB could not satisfy from the buffer pool, and had to read directly from the disk.DEPENDENT

-

mysql.innodb_buffer_pool_reads.rate
MySQL: InnoDB buffer pool read requestsNumber of logical read requests.DEPENDENT

-

mysql.innodb_buffer_pool_read_requests
MySQL: InnoDB buffer pool read requests per secondNumber of logical read requests per second.DEPENDENT

-

mysql.innodb_buffer_pool_read_requests.rate
MySQL: Innodb buffer pool wait freeNumber of times InnoDB waited for a free page before reading or creating a page. Normally, writes to the InnoDB buffer pool happen in the background. When no clean pages are available, dirty pages are flushed first in order to free some up. This counts the numbers of wait for this operation to finish. If this value is not small, look at the increasing innodb_buffer_pool_size.DEPENDENT

-

mysql.innodb_buffer_pool_wait_free
MySQL: Calculated value of innodb_log_file_sizeCalculated by (innodb_os_log_written-innodb_os_log_written(time shift -1h))/{$MYSQL.INNODB_LOG_FILES} value of the innodb_log_file_size. Innodb_log_file_size is the size in bytes of the each InnoDB redo log file in the log group. The combined size can be no more than 512GB. Larger values mean less disk I/O due to less flushing checkpoint activity, but also slower recovery from a crash.CALCULATED

-

mysql.innodb_log_file_size
MySQL: Innodb number open filesNumber of open files held by InnoDB. InnoDB only.DEPENDENT

-

mysql.innodb_num_open_files
MySQL: Innodb log writtenNumber of bytes written to the InnoDB log.DEPENDENT

-

mysql.innodb_os_log_written
MySQL: InnoDB row lock timeThe total time spent in acquiring row locks for InnoDB tables, in milliseconds.DEPENDENT

-

mysql.innodb_row_lock_time
MySQL: InnoDB row lock time maxThe maximum time to acquire a row lock for InnoDB tables, in milliseconds.DEPENDENT

-

mysql.innodb_row_lock_time_max
MySQL: InnoDB row lock waitsNumber of times operations on InnoDB tables had to wait for a row lock.DEPENDENT

-

mysql.innodb_row_lock_waits
MySQL: Max used connectionsThe maximum number of connections that have been in use simultaneously since the server start.DEPENDENT

-

mysql.max_used_connections
MySQL: Open tablesNumber of tables that are open.DEPENDENT

-

mysql.open_tables
MySQL: Open table definitionsNumber of cached table definitions.DEPENDENT

-

mysql.open_table_definitions
MySQL: Status

-

-

-

mysql.ping["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWORD}"]
MySQL: Queries per secondNumber of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable.DEPENDENT

-

mysql.queries.rate
MySQL: Questions per secondNumber 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
MySQL: Slow queries per secondNumber of queries that have taken more than long_query_time seconds.DEPENDENT

-

mysql.slow_queries.rate
MySQL: Threads cachedNumber of threads in the thread cache.DEPENDENT

-

mysql.threads_cached
MySQL: Threads connectedNumber of currently open connections.DEPENDENT

-

mysql.threads_connected
MySQL: Threads created per secondNumber 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.rate
MySQL: Threads runningNumber of threads which are not sleeping.DEPENDENT

-

mysql.threads_running
MySQL: UptimeThe amount of seconds that the server has been up.DEPENDENT

-

mysql.uptime
MySQL: Version

-

-

15mmysql.version["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWORD}"]

Triggers​

NameDescriptionExpressionPriorityDependencies
MySQL: Server has aborted connectionsThe number of failed attempts to connect to the MySQL server is more than {$MYSQL.ABORTED_CONN.MAX.WARN} in the last 5 minutes.min(/MySQL by Zabbix agent 2/mysql.aborted_connects.rate,5m)>{$MYSQL.ABORTED_CONN.MAX.WARN}AVERAGE ⚠MySQL: Aborted connections per second
MySQL: Buffer pool utilization is too lowThe 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.max(/MySQL by Zabbix agent 2/mysql.buffer_pool_utilization,5m)<{$MYSQL.BUFF_UTIL.MIN.WARN}WARNING 📢MySQL: Buffer pool utilization
MySQL: Refused connectionsNumber of refused connections due to the max_connections limit being reached.last(/MySQL by Zabbix agent 2/mysql.connection_errors_max_connections.rate)>0AVERAGE ⚠MySQL: Connection errors max connections per second
MySQL: Number of on-disk temporary tables created per second is highPossibly the application using the database is in need of query optimization.min(/MySQL by Zabbix agent 2/mysql.created_tmp_disk_tables.rate,5m)>{$MYSQL.CREATED_TMP_DISK_TABLES.MAX.WARN}WARNING 📢MySQL: Created tmp tables on disk per second
MySQL: Number of temporary files created per second is highPossibly the application using the database is in need of query optimization.min(/MySQL by Zabbix agent 2/mysql.created_tmp_files.rate,5m)>{$MYSQL.CREATED_TMP_FILES.MAX.WARN}WARNING 📢MySQL: Created tmp files on disk per second
MySQL: Number of internal temporary tables created per second is highPossibly the application using the database is in need of query optimization.min(/MySQL by Zabbix agent 2/mysql.created_tmp_tables.rate,5m)>{$MYSQL.CREATED_TMP_TABLES.MAX.WARN}WARNING 📢MySQL: Created tmp tables on memory per second
MySQL: Service is down

-

last(/MySQL by Zabbix agent 2/mysql.ping["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWORD}"])=0HIGH ⛔MySQL: Status
MySQL: Server has slow queriesThe number of slow queries is more than {$MYSQL.SLOW_QUERIES.MAX.WARN} in the last 5 minutes.min(/MySQL by Zabbix agent 2/mysql.slow_queries.rate,5m)>{$MYSQL.SLOW_QUERIES.MAX.WARN}WARNING 📢MySQL: Slow queries per second
MySQL: Failed to fetch info dataZabbix has not received data for items for the last 30 minutes.nodata(/MySQL by Zabbix agent 2/mysql.uptime,30m)=1INFO 🔔MySQL: Uptime
MySQL: Service has been restartedMySQL uptime is less than 10 minutes.last(/MySQL by Zabbix agent 2/mysql.uptime)<10mINFO 🔔MySQL: Uptime
MySQL: Version has changedMySQL version has changed. Ack to close.last(/MySQL by Zabbix agent 2/mysql.version["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWORD}"],#1)<>last(/MySQL by Zabbix agent 2/mysql.version["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWORD}"],#2) and length(last(/MySQL by Zabbix agent 2/mysql.version["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWORD}"]))>0INFO 🔔MySQL: Version

Discovery rule №1​

NameDescriptionTypeIntervalKey and additional info
Database discoveryScanning databases in DBMS.

-

1hmysql.db.discovery["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWORD}"]

Item prototypes​

NameDescriptionTypeIntervalKey and additional info
MySQL: Size of database {#DATABASE}

-

-

5mmysql.db.size["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWORD}","{#DATABASE}"]

Discovery rule №2​

NameDescriptionTypeIntervalKey and additional info
MariaDB discoveryAdditional metrics if MariaDB is used.DEPENDENT0mysql.extra_metric.discovery

Item prototypes​

NameDescriptionTypeIntervalKey and additional info
MySQL: Binlog commitsTotal number of transactions committed to the binary log.DEPENDENT

-

mysql.binlog_commits[{#SINGLETON}]
MySQL: Binlog group commitsTotal number of group commits done to the binary log.DEPENDENT

-

mysql.binlog_group_commits[{#SINGLETON}]
MySQL: Master GTID wait countThe number of times MASTER_GTID_WAIT called.DEPENDENT

-

mysql.master_gtid_wait_count[{#SINGLETON}]
MySQL: Master GTID wait timeoutsNumber of timeouts occurring in MASTER_GTID_WAIT.DEPENDENT

-

mysql.master_gtid_wait_timeouts[{#SINGLETON}]
MySQL: Master GTID wait timeTotal number of time spent in MASTER_GTID_WAIT.DEPENDENT

-

mysql.master_gtid_wait_time[{#SINGLETON}]

Discovery rule №3​

NameDescriptionTypeIntervalKey and additional info
Replication discoveryIf "show slave status" returns Master_Host, "Replication: *" items are created.

-

1hmysql.replication.discovery["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWORD}"]

Item prototypes​

NameDescriptionTypeIntervalKey and additional info
MySQL: Replication Slave status {#MASTER_HOST}The item gets status information on the essential parameters of the slave threads.

-

-

mysql.replication.get_slave_status["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWORD}","{#MASTER_HOST}"]
MySQL: Replication Seconds Behind Master {#MASTER_HOST}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.replication.seconds_behind_master["{#MASTER_HOST}"]
MySQL: Replication Slave IO Running {#MASTER_HOST}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 a replication or have explicitly stopped it with STOP SLAVE.DEPENDENT

-

mysql.replication.slave_io_running["{#MASTER_HOST}"]
MySQL: Replication Slave SQL Running {#MASTER_HOST}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.replication.slave_sql_running["{#MASTER_HOST}"]
MySQL: Replication Slave SQL Running State {#MASTER_HOST}This shows the state of the SQL driver threads.DEPENDENT

-

mysql.replication.slave_sql_running_state["{#MASTER_HOST}"]

Trigger prototypes​

NameDescriptionExpressionPriorityDependencies
MySQL: Replication lag is too high

-

min(/MySQL by Zabbix agent 2/mysql.replication.seconds_behind_master["{#MASTER_HOST}"],5m)>{$MYSQL.REPL_LAG.MAX.WARN}WARNING 📢MySQL: Replication Seconds Behind Master {#MASTER_HOST}
MySQL: The slave I/O thread is not connected to a replication master

-

count(/MySQL by Zabbix agent 2/mysql.replication.slave_io_running["{#MASTER_HOST}"],#1,"ne","Yes")=1WARNING 📢MySQL: Replication Slave IO Running {#MASTER_HOST}
MySQL: The slave I/O thread is not runningWhether the I/O thread for reading the master's binary log is running.count(/MySQL by Zabbix agent 2/mysql.replication.slave_io_running["{#MASTER_HOST}"],#1,"eq","No")=1AVERAGE ⚠MySQL: Replication Slave IO Running {#MASTER_HOST}
MySQL: The SQL thread is not runningWhether the SQL thread for executing events in the relay log is running.count(/MySQL by Zabbix agent 2/mysql.replication.slave_sql_running["{#MASTER_HOST}"],#1,"eq","No")=1WARNING 📢MySQL: Replication Slave SQL Running {#MASTER_HOST}