Available versions




Halley MySQL Python server mi passive

Description

An mysql template create for halley.it

Overview

Here is my template for monitoring MySQL on a Windows server with multiple instances. The logic for this monitoring is:

Script will take 2 parameters: variable ($1) and port ($2). Variable 1 will be the word for what script will looking for in script results file. The first zabbix request will generate a file : mysql_results_$PORT.txt. All next requests in 55 sec will take the values from this file, not from mysql. If the file is over 55 sec, file is rewriten. This assure to have 3 db request for all 117 items

Steps to install:

  1. Create C:\script

  2. Copy in this folder the script mysql_stats.py

  3. in zabbix agent add at UserParameters

UserParameter=mysql-stats[*],c:\script\mysql_stats.py “$1” “$2” # user parameter for show extended status - no grants needed

UserParameter=mysql.ping[*],mysqladmin -u -p –host=127.0.0.1 -P”$2” ping # -P”$2” will tahe the port number, who is the second variable from request

  1. Create a different host for every MySQL instance and specify in host Macros the MySQL port {$PORT}

  2. Add template Halley MySQL Server mi passive. I use passive items beacause not need to specify host name as hostname of the client. Don’t forget to enable on host agent the passive monitoring

  3. Check the data:

a0) from client command line run c:\script\mysql_stats.py Uptime Port

a) from zabbix server with command zabbix_get -s host_ip -k “mysql-stats[Uptime,$port]”

a1) from zabbix server with command zabbix_get -s host_ip -k “mysql-stats[log_bin,$port]”

b) Look in Latest data

Don’t forget:

  1. Specify port in host macros

  2. Install Python on server and when will install select custom install and check for all users

All files are on https://1drv.ms/f/s!Ag6IN4i1M9MuhXQZZpQ5dn2t4MCy

P.S.

This template include description with advices for items and triggers. I spend over a month to get best practices from MySQL Enterprise Monitor, another mysql templates or just mysql documentation. A feedback from you will be nice

Author

Tudor Ticau

Macros used

There are no macros links in this template.

There are no template links in this template.

Discovery rules

There are no discovery rules in this template.

Items collected

Name Description Type Key and additional info
MySQL: old_passwords <p>This variable controls the password hashing method used by the PASSWORD() function. It also influences password hashing performed by CREATE USER and GRANT statements that specify a password using an IDENTIFIED BY clause. The following table shows, for each password hashing method, the permitted value of old_passwords and which authentication plugins use the hashing method. Password Hashing Method old_passwords Value Associated Authentication Plugin MySQL 4.1 native hashing 0 mysql_native_password SHA-256 hashing 2 sha256_password If you set old_passwords=2, follow the instructions for using the sha256_password plugin at Section 6.5.1.4, “SHA-256 Pluggable Authentication”. The server sets the global old_passwords value during startup to be consistent with the password hashing method required by the authentication plugin indicated by the default_authentication_plugin system variable. When a client successfully connects to the server, the server sets the session old_passwords value appropriately for the account authentication method. For example, if the account uses the sha256_password authentication plugin, the server sets old_passwords=2. Default Value 0</p> Zabbix agent mysql-stats[old_passwords,{$PORT}]<p>Update: 6h</p>
MySQL: secure_auth <p>If this variable is enabled, the server blocks connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format. Enable this variable to prevent all use of passwords employing the old format (and hence insecure communication over the network). This variable is deprecated and will be removed in a future MySQL release. It is always enabled and attempting to disable it produces an error. Server startup fails with an error if this variable is enabled and the privilege tables are in pre-4.1 format. See Section 6.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”. Default value: ON Links: “MySQL Manual: Password Hashing as of MySQL 4.1 MySQL Manual: Security-Related mysqld Options MySQL Manual: mysql Options MySQL Manual: Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”</p> Zabbix agent mysql-stats[secure_auth,{$PORT}]<p>Update: 6h</p>
MySQL: Com_prepare_sql <p>“Prepared statements may increase performance in applications that execute similar statements more than once, primarily because the query is parsed only once. Prepared statements can also reduce network traffic because it is only necessary to send the data for the parameters for each execution rather than the whole statement. However, prepared statements take time to prepare and consume memory in the MySQL server until they are closed, so it is important to use them properly. If you are only executing a statement a few times, the overhead of creating a prepared statement may not be worthwhile.”</p> Zabbix agent mysql-stats[Com_prepare_sql,{$PORT}]<p>Update: 5m</p>
MySQL: log_bin <p>Display system status of variable log_bin The binary log captures DML, DDL, and security changes that occur and stores these changes in a binary format. The binary log enables point-in-time recovery, preventing data loss during a disaster recovery situation. It also enables you to review all alterations made to your database. Links: MySQL Manual: The Binary Log MySQL Knowledge Base: What do I have to set up for point-in-time recovery? MySQL Knowledge Base: How can I recover all of my data up to now?</p> Zabbix agent mysql-stats[log_bin,{$PORT}]<p>Update: 6h</p>
Commands Replace <p>The Com_replace statement counter variables indicate the number of times each replace statement has been executed.</p> Zabbix agent mysql-stats[Com_replace,{$PORT}]<p>Update: 60</p>
Sort_scan <p>The number of sorts that were done by scanning the table.</p> Zabbix agent mysql-stats[Sort_scan,{$PORT}]<p>Update: 60</p>
Handler_rollback <p>The number of requests for a storage engine to perform a rollback operation.</p> Zabbix agent mysql-stats[Handler_rollback,{$PORT}]<p>Update: 60</p>
Handler_read_rnd <p>The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly.</p> Zabbix agent mysql-stats[Handler_read_rnd,{$PORT}]<p>Update: 60</p>
MySQL: Com_grant <p>Indicate a grant statement “MySQL Knowledge Base: What are some tips on administering users? MySQL Manual: Privileges Provided by MySQL”</p> Zabbix agent mysql-stats[Com_grant,{$PORT}]<p>Update: 5m</p>
MySQL: Com_stmt_prepare <p>Com_stmt_reprepare indicates the number of times statements were automatically reprepared by the server after metadata changes to tables or views referred to by the statement. A reprepare operation increments Com_stmt_reprepare, and also Com_stmt_prepare. Prepared statements may increase performance in applications that execute similar statements more than once, primarily because the query is parsed only once. Prepared statements can also reduce network traffic because it is only necessary to send the data for the parameters for each execution rather than the whole statement.</p> Zabbix agent mysql-stats[Com_stmt_prepare,{$PORT}]<p>Update: 5m</p>
Handler_read_first <p>The number of times the first entry in an index was read. If this value is high, it suggests that the server is doing a lot of full index scans; If MySQL is frequently accessing the first row of a table index, it suggests that it is performing a sequential scan of the entire index. This indicates that the corresponding table is not properly indexed.</p> Zabbix agent mysql-stats[Handler_read_first,{$PORT}]<p>Update: 60</p>
Key reads requests <p>The number of requests to read a key block from the MyISAM key cache.</p> Zabbix agent mysql-stats[Key_read_requests,{$PORT}]<p>Update: 60</p>
Commands Create Table <p>The Com_create_table statement counter variables indicate the number of times each create_table statement has been executed.</p> Zabbix agent mysql-stats[Com_create_table,{$PORT}]<p>Update: 60</p>
MySQL: log_warnings <p>“Error conditions encountered by a MySQL server are always logged in the error log, but warning conditions are only logged if log_warnings is set to a value greater than 0. If warnings are not logged you will not get valuable information about aborted connections and various other communication errors. This is especially important if you use replication so you get more information about what is happening, such as messages about network failures and re-connections. Note that as of MySQL 5.7.2, the log_error_verbosity system variable is preferred over, and should be used instead of, log_warnings. WARNING: One of the system variables used in this advisor (log_warnings) is deprecated and has been removed in MySQL Server 8.0. Please plan ahead accordingly.” Default Value (64-bit platforms) 2 Links: “MySQL Manual: The Error Log MySQL Manual: Server Command Options - log_warnings MySQL Manual: Server Command Options - log_error_verbosity MySQL Manual: Binary Logging Options and Variables - log_statements_unsafe_for_binlog MySQL Manual: Communication Errors and Aborted Connections Bug #24761: dropped TCP connections not logged as errors but result in blocked host MySQL Manual: Usage of Row-based Logging and Row-Based Replication”</p> Zabbix agent mysql-stats[log_warnings,{$PORT}]<p>Update: 6h</p>
Qcache_free_blocks <p>The number of free memory blocks in the query cache.</p> Zabbix agent mysql-stats[Qcache_free_blocks,{$PORT}]<p>Update: 60</p>
Created_tmp_files in memory <p>How many temporary files mysqld has created.</p> Zabbix agent mysql-stats[Created_tmp_files,{$PORT}]<p>Update: 60</p>
Commands Commit <p>The Com_commi statement counter variables indicate the number of times each commit statement has been executed.</p> Zabbix agent mysql-stats[Com_commit,{$PORT}]<p>Update: 60</p>
Select_range <p>The number of joins that used ranges on the first table. This is normally not a critical issue even if the value is quite large.</p> Zabbix agent mysql-stats[Select_range,{$PORT}]<p>Update: 60</p>
MySQL: myisam_repair_threads <p>If this value is greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process. The default value is 1. Using multiple threads when repairing MyISAM tables can improve performance, but it can also lead to table and index corruption as reported by several bugs (#11527, #11684, #18874). Even though these bugs have been fixed, this feature is still considered beta-quality, as noted in the manual. “MySQL Manual: System Variables MySQL Bug DB: Bug #11527 MySQL Bug DB: Bug #11684 MySQL Bug DB: Bug #18874”</p> Zabbix agent mysql-stats[myisam_repair_threads,{$PORT}]<p>Update: 6h</p>
Key reads <p>The number of physical reads of a key block from disk into the MyISAM key cache. This variable indicates the number of filesystem accesses MySQL performed to fetch database indexes. Performing filesystem reads for database indexes slows query performance. If this variable is high, it indicates that MySQL’s key cache is overloaded and should be reconfigured. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.</p> Zabbix agent mysql-stats[Key_reads,{$PORT}]<p>Update: 60</p>
MySQL: flush_time <p>If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and synchronize unflushed data to disk. This option is best used only on systems with minimal resources. If flush_time is set to a non-zero value, all tables are closed every flush_time seconds to free up resources and synchronize unflushed data to disk. If your system is unreliable and tends to lock up or restart often, forcing out table changes this way degrades performance but can reduce the chance of table corruption or data loss. We recommend that this option be used only on Windows, or on systems with minimal resources.</p> Zabbix agent mysql-stats[flush_time,{$PORT}]<p>Update: 6h</p>
Commands Begin <p>The Com_begin statement counter variables indicate the number of times each begin statement has been executed.</p> Zabbix agent mysql-stats[Com_begin,{$PORT}]<p>Update: 60</p>
Connection_errors_accept <p>The number of errors that occurred during calls to accept() on the listening port. These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_accept,{$PORT}]<p>Update: 60</p>
MySQL: myisam_recover_options <p>The myisam-recover-options option (named myisam-recover before MySQL 5.5.3) enables automatic MyISAM crash recovery should a MyISAM table become corrupt for some reason. If this option is not set, then a table will be “Marked as crashed” if it becomes corrupt, and no sessions will be able to SELECT from it, or perform any sort of DML against it. MySQL Manual: MyISAM Startup Options MySQL Manual: mysqld Command Options</p> Zabbix agent mysql-stats[myisam_recover_options,{$PORT}]<p>Update: 6h</p>
Open_table_definitions <p>The number of cached .frm files.</p> Zabbix agent mysql-stats[Open_table_definitions,{$PORT}]<p>Update: 60</p>
Table_open_cache_overflows <p>The number of overflows for the open tables cache. This is the number of times, after a table is opened or closed, a cache instance has an unused entry and the size of the instance is larger than table_open_cache / table_open_cache_instances.</p> Zabbix agent mysql-stats[Table_open_cache_overflows,{$PORT}]<p>Update: 60</p>
Qcache hits <p>The number of query cache hits.</p> Zabbix agent mysql-stats[Qcache_hits,{$PORT}]<p>Update: 60</p>
Commands Delete <p>Count delete commands per second. Used to see the writes on database</p> Zabbix agent mysql-stats[Com_delete,{$PORT}]<p>Update: 60</p>
MySQL: sql_mode <p>SQL Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. If no SQL modes are enabled this means there is no form of server-enforced data integrity, which means incoming data that is invalid will not be rejected by the server, but instead will be changed to conform to the target column’s default datatype. Note that any client can change its own session SQL mode value at any time. Default Value (>= 5.7.8) ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION Default Value (5.7.7) ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION Default Value (>= 5.7.5, <= 5.7.6) ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ENGINE_SUBSTITUTION Default Value (<= 5.7.4) NO_ENGINE_SUBSTITUTION Valid Values ALLOW_INVALID_DATES ANSI_QUOTES ERROR_FOR_DIVISION_BY_ZERO HIGH_NOT_PRECEDENCE IGNORE_SPACE NO_AUTO_CREATE_USER NO_AUTO_VALUE_ON_ZERO NO_BACKSLASH_ESCAPES NO_DIR_IN_CREATE NO_ENGINE_SUBSTITUTION NO_FIELD_OPTIONS NO_KEY_OPTIONS NO_TABLE_OPTIONS NO_UNSIGNED_SUBTRACTION NO_ZERO_DATE NO_ZERO_IN_DATE ONLY_FULL_GROUP_BY PAD_CHAR_TO_FULL_LENGTH PIPES_AS_CONCAT REAL_AS_FLOAT STRICT_ALL_TABLES STRICT_TRANS_TABLES Links: “MySQL Manual: Server SQL Mode MySQL Manual: MySQL 5.0 FAQ - Server SQL Mode MySQL Manual: How MySQL Deals with Constraints MySQL Manual: Running MySQL in ANSI Mode”</p> Zabbix agent mysql-stats[sql_mode,{$PORT}]<p>Update: 6h</p>
Open_files <p>The number of files that are open. This count includes regular files opened by the server.</p> Zabbix agent mysql-stats[Open_files,{$PORT}]<p>Update: 60</p>
MySQL: concurrent_insert <p>Default: AUTO If AUTO (the default), MySQL permits INSERT and SELECT statements to run concurrently for MyISAM tables that have no free blocks in the middle of the data file. If you start mysqld with –skip-new, this variable is set to NEVER. This variable can take the values shown in the following table. The variable can be assigned using either the name values or corresponding integer values. Value Description NEVER (or 0) Disables concurrent inserts AUTO (or 1) (Default) Enables concurrent insert for MyISAM tables that do not have holes ALWAYS (or 2) Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.</p> Zabbix agent mysql-stats[concurrent_insert,{$PORT}]<p>Update: 6h</p>
Commands Update <p>Count update commands per second. Used to see the writes on database</p> Zabbix agent mysql-stats[Com_update,{$PORT}]<p>Update: 60</p>
Opened_files <p>The number of files that have been opened with my_open() (a mysys library function). Parts of the server that open files without using this function do not increment the count.</p> Zabbix agent mysql-stats[Opened_files,{$PORT}]<p>Update: 60</p>
Qcache not cached <p>The number of noncached queries (not cacheable, or not cached due to the query_cache_type setting).</p> Zabbix agent mysql-stats[Qcache_not_cached,{$PORT}]<p>Update: 60</p>
Available status <p>-</p> Zabbix agent mysql.ping[,{$PORT}]<p>Update: 60</p>
Key writes to disk <p>The number of physical writes of a key block from the MyISAM key cache to disk.</p> Zabbix agent mysql-stats[Key_writes,{$PORT}]<p>Update: 60</p>
Key_blocks_used <p>The number of used blocks in the MyISAM key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time.</p> Zabbix agent mysql-stats[Key_blocks_used,{$PORT}]<p>Update: 60</p>
Binlog_cache_use <p>The number of transactions that used the binary log cache</p> Zabbix agent mysql-stats[Binlog_cache_use,{$PORT}]<p>Update: 60</p>
Aborted_clients <p>The number of connections that were aborted because the client died without closing the connection properly. If this value increments, it usually means there’s been an application error, such as the programmer forgetting to close MySQL connections properly before terminating the program. This is not usually indicative of a big problem.</p> Zabbix agent mysql-stats[Aborted_clients,{$PORT}]<p>Update: 60</p>
Connection_errors_peer_address <p>The number of errors that occurred while searching for connecting client IP addresses. Note These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_peer_address,{$PORT}]<p>Update: 60</p>
MySQL: Com_stmt_close <p>“Prepared statements may increase performance in applications that execute similar statements more than once, primarily because the query is parsed only once. Prepared statements can also reduce network traffic because it is only necessary to send the data for the parameters for each execution rather than the whole statement. However, prepared statements take time to prepare and consume memory in the MySQL server until they are closed, so it is important to use them properly. If you are not closing prepared statements when you are done with them, you are needlessly tying up memory that could be put to use in other ways.”</p> Zabbix agent mysql-stats[Com_stmt_close,{$PORT}]<p>Update: 5m</p>
Table_open_cache_hits <p>The number of hits for open tables cache lookups.</p> Zabbix agent mysql-stats[Table_open_cache_hits,{$PORT}]<p>Update: 60</p>
Open_tables <p>The number of tables that are open. This value is best analyzed in combination with the size of the table cache. If this value is low and the table_cache value is high, it’s probably safe to reduce the cache size without affecting performance. On the other hand, if this value is high and close to the table_cache value, there is benefit in increasing the size of the table cache.</p> Zabbix agent mysql-stats[Open_tables,{$PORT}]<p>Update: 60</p>
Aborted connections <p>The number of failed attempts to connect to the MySQL server. If this counter is increasing, your clients are trying and failing to connect to the database. Investigate the source of the problem with fine-grained connection metrics such as Connection_errors_max_connections and Connection_errors_internal.</p> Zabbix agent mysql-stats[Aborted_connects,{$PORT}]<p>Update: 60</p>
Sort_merge_passes <p>The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.</p> Zabbix agent mysql-stats[Sort_merge_passes,{$PORT}]<p>Update: 60</p>
Key_blocks_not_flushed <p>The number of key blocks in the MyISAM key cache that have changed but have not yet been flushed to disk.</p> Zabbix agent mysql-stats[Key_blocks_not_flushed,{$PORT}]<p>Update: 60</p>
Select_range_check <p>The number of joins without keys that check for key usage after each row. If this is not 0, you should carefully check the indexes of your tables.</p> Zabbix agent mysql-stats[Select_range_check,{$PORT}]<p>Update: 60</p>
Qcache inserts <p>The number of queries added to the query cache.</p> Zabbix agent mysql-stats[Qcache_inserts,{$PORT}]<p>Update: 60</p>
Qcache queries in cache <p>The number of queries who are in the query cache.</p> Zabbix agent mysql-stats[Qcache_queries_in_cache,{$PORT}]<p>Update: 60</p>
Created tmp tables on disk <p>The number of internal on-disk temporary tables created by the server while executing statements. Accessing tables on disk is typically slower than accessing the same tables in memory. So queries that use the CREATE TEMPORARY TABLE syntax are likely to be slow when this value is high. Must be ass low ass possible. Best 0 If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values. If Created_tmp_disk_tables is large, you may want to increase the tmp_table_size or max_heap_table_size value to lessen the likelihood that internal temporary tables in memory will be converted to on-disk tables. You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.</p> Zabbix agent mysql-stats[Created_tmp_disk_tables,{$PORT}]<p>Update: 60</p>
Handler_write <p>The number of requests to insert a row in a table per second.</p> Zabbix agent mysql-stats[Handler_write,{$PORT}]<p>Update: 60</p>
Opened_table_definitions <p>The number of .frm files that have been cached.</p> Zabbix agent mysql-stats[Opened_table_definitions,{$PORT}]<p>Update: 60</p>
Connection_errors_tcpwrap <p>The number of connections refused by the libwrap library. Note These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_tcpwrap,{$PORT}]<p>Update: 60</p>
Max used connections <p>The maximum number of connections that have been in use simultaneously since the server started. This value provides a benchmark to help you decide the maximum number of connections your server should support. It can also help in traffic analysis. (max_used_connections / max_connections) indicates if you could run out soon of connection slots. Alarm if connections usage is > 85%.</p> Zabbix agent mysql-stats[Max_used_connections,{$PORT}]<p>Update: 60</p>
Incoming traffic <p>Incoming bytes per second</p> Zabbix agent mysql-stats[Bytes_received,{$PORT}]<p>Update: 60</p>
Table_open_cache_misses <p>The number of misses for open tables cache lookups.</p> Zabbix agent mysql-stats[Table_open_cache_misses,{$PORT}]<p>Update: 60</p>
Select_scan <p>The number of joins that did a full scan of the first table.</p> Zabbix agent mysql-stats[Select_scan,{$PORT}]<p>Update: 60</p>
MySQL: default_storage_engine <p>The default storage engine. This variable sets the storage engine for permanent tables only. To set the storage engine for TEMPORARY tables, set the default_tmp_storage_engine system variable. To see which storage engines are available and enabled, use the SHOW ENGINES statement or query the INFORMATION_SCHEMA ENGINES table. “MySQL Manual: MyISAM Startup Options MySQL Manual: mysqld Command Options”</p> Zabbix agent mysql-stats[default_storage_engine,{$PORT}]<p>Update: 6h</p>
Handler_read_prev <p>The number of requests to read the previous row in key order.</p> Zabbix agent mysql-stats[Handler_read_prev,{$PORT}]<p>Update: 60</p>
MySQL: query_cache_size <p>The amount of memory allocated for caching query results. By default, the query cache is disabled. This is achieved using a default value of 1M, with a default for query_cache_type of 0. (To reduce overhead significantly if you set the size to 0, you should also start the server with query_cache_type=0. The permissible values are multiples of 1024; other values are rounded down to the nearest multiple. For nonzero values of query_cache_size, that many bytes of memory are allocated even if query_cache_type=0. See Section 8.10.3.3, “Query Cache Configuration”, for more information. The query cache needs a minimum size of about 40KB to allocate its structures. (The exact size depends on system architecture.) If you set the value of query_cache_size too small, a warning will occur Default value: 1 Mb Links: “MySQL Manual: The Query Cache MySQL Manual: Query Cache Configuration MySQL Manual: Query Cache Status and Maintenance”</p> Zabbix agent mysql-stats[query_cache_size,{$PORT}]<p>Update: 5m</p>
Sort_rows <p>The number of sorted rows.</p> Zabbix agent mysql-stats[Sort_rows,{$PORT}]<p>Update: 60</p>
Queries <p>The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.</p> Zabbix agent mysql-stats[Queries,{$PORT}]<p>Update: 60</p>
Version <p>-</p> Zabbix agent mysql-stats[version,{$PORT}]<p>Update: 3600</p>
Handler_read_last <p>The number of requests to read the last key in an index. </p> Zabbix agent mysql-stats[Handler_read_last,{$PORT}]<p>Update: 60</p>
Connections <p>The number of connection attempts (successful or not) to the MySQL server per second.</p> Zabbix agent mysql-stats[Connections,{$PORT}]<p>Update: 60</p>
Handler_update <p>The number of times that rows have been updated from tables per second.</p> Zabbix agent mysql-stats[Handler_update,{$PORT}]<p>Update: 60</p>
Commands Insert <p>Count insert commands per second. Used to see the writes on database</p> Zabbix agent mysql-stats[Com_insert,{$PORT}]<p>Update: 60</p>
Select_full_join <p>This variable indicates the number of full joins MySQL has performed to satisfy client queries or the number of joins that perform table scans because they do not use indexes. A high value indicates that MySQL is being forced to perform full table joins (which are performance-intensive) instead of using indexes. This suggests a need for greater indexing of the corresponding tables. If this value is not 0, you should carefully check the indexes of your tables.</p> Zabbix agent mysql-stats[Select_full_join,{$PORT}]<p>Update: 60</p>
Sort_range <p>The number of sorts that were done using ranges.</p> Zabbix agent mysql-stats[Sort_range,{$PORT}]<p>Update: 60</p>
MySQL: version_compile_os <p>The type of operating system on which MySQL was built.</p> Zabbix agent mysql-stats[version_compile_os,{$PORT}]<p>Update: 6h</p>
MySQL: expire_logs_days <p>Display system status of variable expire_logs_days The binary log captures DML, DDL, and security changes that occur and stores these changes in a binary format. The binary log enables point-in-time recovery, preventing data loss during a disaster recovery situation. It is used on master replication servers as a record of the statements to be sent to slave servers. It also enables you to review all alterations made to your database. However, the number of log files and the space they use can grow rapidly, especially on a busy server, so it is important to remove these files on a regular basis when they are no longer needed, as long as appropriate backups have been made. The expire_logs_days parameter enables automatic binary log removal. Links: “MySQL Manual: The Binary Log MySQL Manual: Server System Variables Bug #28238: expire_logs_days and PURGE MASTER LOGS fail when index not up to date MySQL Knowledge Base: What do I have to set up for point-in-time recovery? MySQL Knowledge Base: How can I recover all of my data up to now?”</p> Zabbix agent mysql-stats[expire_logs_days,{$PORT}]<p>Update: 6h</p>
Threads created <p>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.</p> Zabbix agent mysql-stats[Threads_created,{$PORT}]<p>Update: 60</p>
MySQL: query_cache_type <p>Set the query cache type. Setting the GLOBAL value sets the type for all clients that connect thereafter. Individual clients can set the SESSION value to affect their own use of the query cache. Possible values are shown in the following table. Option Description 0 or OFF Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0. 1 or ON Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE. 2 or DEMAND Cache results only for cacheable queries that begin with SELECT SQL_CACHE. This variable defaults to OFF. If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution. Links: “MySQL Manual: The Query Cache MySQL Manual: Query Cache Configuration MySQL Manual: Query Cache Status and Maintenance”</p> Zabbix agent mysql-stats[query_cache_type,{$PORT}]<p>Update: 5m</p>
Handler_commit <p>The number of internal COMMIT statements.</p> Zabbix agent mysql-stats[Handler_commit,{$PORT}]<p>Update: 60</p>
MySQL: thread_cache_size <p>How many threads the server should cache for reuse. When a client disconnects, the client’s threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads. By examining the difference between the Connections and Threads_created status variables, you can see how efficient the thread cache is. For details, see Section 5.1.9, “Server Status Variables”. The default value is based on the following formula, capped to a limit of 100: 8 + (max_connections / 100)</p> Zabbix agent mysql-stats[thread_cache_size,{$PORT}]<p>Update: 5m</p>
Qcache free memory <p>The amount of free memory for the query cache.</p> Zabbix agent mysql-stats[Qcache_free_memory,{$PORT}]<p>Update: 60</p>
Handler_read_next <p>The number of requests to read the next row in key order. </p> Zabbix agent mysql-stats[Handler_read_next,{$PORT}]<p>Update: 60</p>
MySQL: event_scheduler <p>“The Event Scheduler is a very useful feature when enabled. It is a framework for executing SQL commands at specific times or at regular intervals. Conceptually, it is similar to the idea of the Unix crontab (also known as a ““cron job””) or the Windows Task Scheduler. The basics of its architecture are simple. An event is a stored routine with a starting date and time, and a recurring tag. Once defined and activated, it will run when requested. Unlike triggers, events are not linked to specific table operations, but to dates and times. Using the event scheduler, the database administrator can perform recurring events with minimal hassle. Common uses are the cleanup of obsolete data, the creation of summary tables for statistics, and monitoring of server performance and usage.” Default Value OFF Valid Values ON OFF DISABLED Links: “MySQL Manual: Using the Event Scheduler MySQL Manual: Event Scheduler Overview”</p> Zabbix agent mysql-stats[event_scheduler,{$PORT}]<p>Update: 5m</p>
Questions <p>The number of statements executed by the server. As of MySQL 5.0.72, this includes only statements sent to the server by clients and no longer includes statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands. MySQL increments the questions and queries counters before executing the query</p> Zabbix agent mysql-stats[Questions,{$PORT}]<p>Update: 60</p>
Threads connected <p>This variable indicates the total number of clients that have currently open connections to the server. It provides real-time information on how many clients are currently connected to the server. This can help in traffic analysis or in deciding the best time for a server re-start.</p> Zabbix agent mysql-stats[Threads_connected,{$PORT}]<p>Update: 60</p>
MySQL: Com_execute_sql <p>“Prepared statements may increase performance in applications that execute similar statements more than once, primarily because the query is parsed only once. Prepared statements can also reduce network traffic because it is only necessary to send the data for the parameters for each execution rather than the whole statement. However, prepared statements take time to prepare and consume memory in the MySQL server until they are closed, so it is important to use them properly. If you are only executing a statement a few times, the overhead of creating a prepared statement may not be worthwhile.”</p> Zabbix agent mysql-stats[Com_execute_sql,{$PORT}]<p>Update: 5m</p>
Created_tmp_tables on memory <p>The number of internal temporary tables created by the server while executing statements. You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.</p> Zabbix agent mysql-stats[Created_tmp_tables,{$PORT}]<p>Update: 60</p>
Handler_read_rnd_next <p>The number of requests to read the next row in the data file. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.</p> Zabbix agent mysql-stats[Handler_read_rnd_next,{$PORT}]<p>Update: 60</p>
MySQL: Com_revoke <p>Indicate a revoke statement “MySQL Knowledge Base: What are some tips on administering users? MySQL Manual: Privileges Provided by MySQL”</p> Zabbix agent mysql-stats[Com_revoke,{$PORT}]<p>Update: 5m</p>
Opened_tables <p>The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.</p> Zabbix agent mysql-stats[Opened_tables,{$PORT}]<p>Update: 60</p>
Handler_read_key <p>The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.</p> Zabbix agent mysql-stats[Handler_read_key,{$PORT}]<p>Update: 60</p>
Threads running <p>The number of threads that are not sleeping. Is the number of queries running concurrently and fight between to complete in time</p> Zabbix agent mysql-stats[Threads_running,{$PORT}]<p>Update: 60</p>
Handler_savepoint_rollback <p>The number of requests for a storage engine to roll back to a savepoint.</p> Zabbix agent mysql-stats[Handler_savepoint_rollback,{$PORT}]<p>Update: 60</p>
MySQL: have_symlink <p>YES if symbolic link support is enabled, NO if not. This is required on Unix for support of the DATA DIRECTORY and INDEX DIRECTORY table options. If the server is started with the –skip-symbolic-links option, the value is DISABLED. This variable has no meaning on Windows. Links: “MySQL Manual: Making MySQL Secure Against Attackers MySQL Manual: Using Symbolic Links MySQL Manual: Disk Issues MySQL Manual: CREATE TABLE Syntax”</p> Zabbix agent mysql-stats[have_symlink,{$PORT}]<p>Update: 6h</p>
Threads cached <p>The number of threads in the thread cache.</p> Zabbix agent mysql-stats[Threads_cached,{$PORT}]<p>Update: 60</p>
Qcache lowmem prunes <p>The number of queries that were deleted from the query cache because of low memory. Could indicate a misconfigured query_cache</p> Zabbix agent mysql-stats[Qcache_lowmem_prunes,{$PORT}]<p>Update: 60</p>
MySQL: lower_case_table_names <p>If set to 0, table names are stored as specified and comparisons are case-sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional details, see Section 9.2.2, “Identifier Case Sensitivity”. On Windows the default value is 1. On macOS, the default value is 2. You should not set lower_case_table_names to 0 if you are running MySQL on a system where the data directory resides on a case-insensitive file system (such as on Windows or macOS). It is an unsupported combination that could result in a hang condition when running an INSERT INTO … SELECT … FROM tbl_name operation with the wrong tbl_name letter case. With MyISAM, accessing table names using different letter cases could cause index corruption. An error message is printed and the server exits if you attempt to start the server with –lower_case_table_names=0 on a case-insensitive file system. If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase. The setting of this variable in MySQL 8.0 affects the behavior of replication filtering options with regard to case sensitivity. (Bug #51639) See Section 17.2.5, “How Servers Evaluate Replication Filtering Rules”, for more information. It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are based on the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared. Default Value 0 Links: MySQL Manual: Identifier Case Sensitivity https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names</p> Zabbix agent mysql-stats[lower_case_table_names,{$PORT}]<p>Update: 6h</p>
MySQL: sync_binlog <p>Controls the number of binary log commit groups to collect before synchronizing the binary log to disk. When sync_binlog=0, the binary log is never synchronized to disk, and the server relies on the operating system to flush the binary log’s contents from time to time as for any other file. When sync_binlog is set to a value greater than 0, this number of binary log commit groups is periodically synchronized to disk. When sync_binlog=1, all transactions are synchronized to the binary log before they are committed. Therefore, even in the event of an unexpected restart, any transactions that are missing from the binary log are only in prepared state. This causes the server’s automatic recovery routine to roll back those transactions. This guarantees that no transaction is lost from the binary log, and is the safest option. However this can have a negative impact on performance because of an increased number of disk writes. Using a higher value improves performance, but with the increased risk of data loss. When sync_binlog=0 or sync_binlog is greater than 1, transactions are committed without having been synchronized to disk. Therefore, in the event of a power failure or operating system crash, it is possible that the server has committed some transactions that have not been synchronized to the binary log. Therefore it is impossible for the recovery routine to recover these transactions and they will be lost from the binary log. The default value of sync_binlog is 1, which is the safest choice, but as noted above can impact performance. Default Value: 1 Links: “MySQL Manual: The Binary Log MySQL Manual: Server System Variables”</p> Zabbix agent mysql-stats[sync_binlog,{$PORT}]<p>Update: 6h</p>
Connection_errors_internal <p>The number of connections refused due to internal errors in the server, such as failure to start a new thread or an out-of-memory condition. Connection_errors_internal is a good one to watch, because it is incremented only when the error comes from the server itself. Internal errors can reflect an out-of-memory condition or the server’s inability to start a new thread.</p> Zabbix agent mysql-stats[Connection_errors_internal,{$PORT}]<p>Update: 60</p>
Key writes requests <p>The number of requests to write a key block to the MyISAM key cache.</p> Zabbix agent mysql-stats[Key_write_requests,{$PORT}]<p>Update: 60</p>
Binlog_cache_disk_use <p>The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction.</p> Zabbix agent mysql-stats[Binlog_cache_disk_use,{$PORT}]<p>Update: 60</p>
Commands Select <p>Count select commands per second. Used to see the read throughput on database</p> Zabbix agent mysql-stats[Com_select,{$PORT}]<p>Update: 60</p>
Select_full_range_join <p>The number of joins that used a range search on a reference table.</p> Zabbix agent mysql-stats[Select_full_range_join,{$PORT}]<p>Update: 60</p>
MySQL: max_heap_table_size <p>This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value. Default: 16Mb This variable is also used in conjunction with tmp_table_size to limit the size of internal in-memory tables. Links: MySQL Manual: How MySQL Uses Internal Temporary Tables MySQL Manual: System Variables MySQL Performance Blog: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE</p> Zabbix agent mysql-stats[max_heap_table_size,{$PORT}]<p>Update: 6h</p>
Outcoming traffic <p>Outcoming bytes per second</p> Zabbix agent mysql-stats[Bytes_sent,{$PORT}]<p>Update: 60</p>
MySQL: thread_handling <p>The thread-handling model used by the server for connection threads. The permissible values are no-threads (the server uses a single thread to handle one connection) and one-thread-per-connection (the server uses one thread to handle each client connection). no-threads is useful for debugging under Linux; Default Value: one-thread-per-connection Valid Values (<= 5.7.8) no-threads one-thread-per-connection Links: MySQL Manual: MySQL Enterprise Thread Pool MySQL Manual: Thread Pool Installation</p> Zabbix agent mysql-stats[thread_handling,{$PORT}]<p>Update: 5m</p>
Handler_delete <p>The number of times that rows have been deleted from tables per second.</p> Zabbix agent mysql-stats[Handler_delete,{$PORT}]<p>Update: 60</p>
Slow queries <p>The number of queries that have taken more than long_query_time seconds. This counter increments regardless of whether the slow query log is enabled. A high value indicates that many queries are not being optimally executed. A necessary next step would be to examine the slow query log and identify these slow queries for optimization.</p> Zabbix agent mysql-stats[Slow_queries,{$PORT}]<p>Update: 60</p>
Key_blocks_unused <p>The number of unused blocks in the MyISAM key cache. You can use this value to determine how much of the key cache is in use; see the discussion of key_buffer_size in Section 5.1.5, “Server System Variables”.</p> Zabbix agent mysql-stats[Key_blocks_unused,{$PORT}]<p>Update: 60</p>
MySQL: tmp_table_size <p>The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables. Default: 16 Mb The actual limit is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk temporary table. As of MySQL 5.7.5, the internal_tmp_disk_storage_engine option defines the storage engine used for on-disk temporary tables. Prior to MySQL 5.7.5, the MyISAM storage engine is used. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory. You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables. Links: MySQL Manual: How MySQL Uses Internal Temporary Tables MySQL Manual: System Variables MySQL Performance Blog: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE</p> Zabbix agent mysql-stats[tmp_table_size,{$PORT}]<p>Update: 6h</p>
Qcache_total_blocks <p>The total number of blocks in the query cache.</p> Zabbix agent mysql-stats[Qcache_total_blocks,{$PORT}]<p>Update: 60</p>
Binlog_stmt_cache_disk_use <p>The number of nontransaction statements that used the binary log statement cache but that exceeded the value of binlog_stmt_cache_size and used a temporary file to store those statements.</p> Zabbix agent mysql-stats[Binlog_stmt_cache_disk_use,{$PORT}]<p>Update: 60</p>
Slow_launch_threads <p>The number of threads that have taken more than slow_launch_time seconds to create.</p> Zabbix agent mysql-stats[Slow_launch_threads,{$PORT}]<p>Update: 60</p>
Binlog_stmt_cache_use <p>The number of nontransactional statements that used the binary log statement cache.</p> Zabbix agent mysql-stats[Binlog_stmt_cache_use,{$PORT}]<p>Update: 60</p>
Table_locks_waited <p>The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.</p> Zabbix agent mysql-stats[Table_locks_waited,{$PORT}]<p>Update: 60</p>
Table_locks_immediate <p>The number of times that a request for a table lock could be granted immediately.</p> Zabbix agent mysql-stats[Table_locks_immediate,{$PORT}]<p>Update: 60</p>
Connection_errors_select <p>The number of errors that occurred during calls to select() or poll() on the listening port. (Failure of this operation does not necessarily means a client connection was rejected.) Note These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_select,{$PORT}]<p>Update: 60</p>
Connection_errors_max_connections <p>The number of connections refused because the server max_connections limit was reached. Note These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_max_connections,{$PORT}]<p>Update: 60</p>
Uptime <p>This variable indicates the number of seconds since the server was last restarted. This value is useful to analyze server uptime, as well as to generate reports on overall system performance. A consistent low value indicates that the server is being frequently restarted, thereby causing frequent interruptions to client service.</p> Zabbix agent mysql-stats[Uptime,{$PORT}]<p>Update: 60</p>
MySQL: local_infile <p>This variable controls server-side LOCAL capability for LOAD DATA statements. Depending on the local_infile setting, the server refuses or permits local data loading by clients that have LOCAL enabled on the client side. To explicitly cause the server to refuse or permit LOAD DATA LOCAL statements (regardless of how client programs and libraries are configured at build time or runtime), start mysqld with local_infile disabled or enabled, respectively. local_infile can also be set at runtime. For more information, see Section 6.1.6, “Security Issues with LOAD DATA LOCAL”. Default Value ON “MySQL Manual: Security Issues with LOAD DATA LOCAL MySQL Manual: Security-Related mysqld Options”</p> Zabbix agent mysql-stats[local_infile,{$PORT}]<p>Update: 6h</p>
Handler_savepoint <p>The number of requests for a storage engine to place a savepoint.</p> Zabbix agent mysql-stats[Handler_savepoint,{$PORT}]<p>Update: 60</p>

Triggers

There are no triggers in this template.

Halley MySQL Python server mi passive

Description

An mysql template create for halley.it

Overview

Here is my template for monitoring MySQL on a Windows server with multiple instances. The logic for this monitoring is:

Script will take 2 parameters: variable ($1) and port ($2). Variable 1 will be the word for what script will looking for in script results file. The first zabbix request will generate a file : mysql_results_$PORT.txt. All next requests in 55 sec will take the values from this file, not from mysql. If the file is over 55 sec, file is rewriten. This assure to have 3 db request for all 117 items

Steps to install:

  1. Create C:\script

  2. Copy in this folder the script mysql_stats.py

  3. in zabbix agent add at UserParameters

UserParameter=mysql-stats[*],c:\script\mysql_stats.py “$1” “$2” # user parameter for show extended status - no grants needed

UserParameter=mysql.ping[*],mysqladmin -u -p –host=127.0.0.1 -P”$2” ping # -P”$2” will tahe the port number, who is the second variable from request

  1. Create a different host for every MySQL instance and specify in host Macros the MySQL port {$PORT}

  2. Add template Halley MySQL Server mi passive. I use passive items beacause not need to specify host name as hostname of the client. Don’t forget to enable on host agent the passive monitoring

  3. Check the data:

a0) from client command line run c:\script\mysql_stats.py Uptime Port

a) from zabbix server with command zabbix_get -s host_ip -k “mysql-stats[Uptime,$port]”

a1) from zabbix server with command zabbix_get -s host_ip -k “mysql-stats[log_bin,$port]”

b) Look in Latest data

Don’t forget:

  1. Specify port in host macros

  2. Install Python on server and when will install select custom install and check for all users

All files are on https://1drv.ms/f/s!Ag6IN4i1M9MuhXQZZpQ5dn2t4MCy

P.S.

This template include description with advices for items and triggers. I spend over a month to get best practices from MySQL Enterprise Monitor, another mysql templates or just mysql documentation. A feedback from you will be nice

Author

Tudor Ticau

Macros used

There are no macros links in this template.

There are no template links in this template.

Discovery rules

There are no discovery rules in this template.

Items collected

Name Description Type Key and additional info
Connections <p>The number of connection attempts (successful or not) to the MySQL server per second.</p> Zabbix agent mysql-stats[Connections,{$PORT}]<p>Update: 60</p>
MySQL: thread_cache_size <p>How many threads the server should cache for reuse. When a client disconnects, the client’s threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads. By examining the difference between the Connections and Threads_created status variables, you can see how efficient the thread cache is. For details, see Section 5.1.9, “Server Status Variables”. The default value is based on the following formula, capped to a limit of 100: 8 + (max_connections / 100)</p> Zabbix agent mysql-stats[thread_cache_size,{$PORT}]<p>Update: 5m</p>
Opened_tables <p>The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.</p> Zabbix agent mysql-stats[Opened_tables,{$PORT}]<p>Update: 60</p>
Threads cached <p>The number of threads in the thread cache.</p> Zabbix agent mysql-stats[Threads_cached,{$PORT}]<p>Update: 60</p>
Table_open_cache_misses <p>The number of misses for open tables cache lookups.</p> Zabbix agent mysql-stats[Table_open_cache_misses,{$PORT}]<p>Update: 60</p>
Aborted_clients <p>The number of connections that were aborted because the client died without closing the connection properly. If this value increments, it usually means there’s been an application error, such as the programmer forgetting to close MySQL connections properly before terminating the program. This is not usually indicative of a big problem.</p> Zabbix agent mysql-stats[Aborted_clients,{$PORT}]<p>Update: 60</p>
Handler_savepoint <p>The number of requests for a storage engine to place a savepoint.</p> Zabbix agent mysql-stats[Handler_savepoint,{$PORT}]<p>Update: 60</p>
Binlog_cache_use <p>The number of transactions that used the binary log cache</p> Zabbix agent mysql-stats[Binlog_cache_use,{$PORT}]<p>Update: 60</p>
MySQL: secure_auth <p>If this variable is enabled, the server blocks connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format. Enable this variable to prevent all use of passwords employing the old format (and hence insecure communication over the network). This variable is deprecated and will be removed in a future MySQL release. It is always enabled and attempting to disable it produces an error. Server startup fails with an error if this variable is enabled and the privilege tables are in pre-4.1 format. See Section 6.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”. Default value: ON Links: “MySQL Manual: Password Hashing as of MySQL 4.1 MySQL Manual: Security-Related mysqld Options MySQL Manual: mysql Options MySQL Manual: Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”</p> Zabbix agent mysql-stats[secure_auth,{$PORT}]<p>Update: 6h</p>
Created_tmp_tables on memory <p>The number of internal temporary tables created by the server while executing statements. You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.</p> Zabbix agent mysql-stats[Created_tmp_tables,{$PORT}]<p>Update: 60</p>
MySQL: max_heap_table_size <p>This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value. Default: 16Mb This variable is also used in conjunction with tmp_table_size to limit the size of internal in-memory tables. Links: MySQL Manual: How MySQL Uses Internal Temporary Tables MySQL Manual: System Variables MySQL Performance Blog: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE</p> Zabbix agent mysql-stats[max_heap_table_size,{$PORT}]<p>Update: 6h</p>
Table_open_cache_overflows <p>The number of overflows for the open tables cache. This is the number of times, after a table is opened or closed, a cache instance has an unused entry and the size of the instance is larger than table_open_cache / table_open_cache_instances.</p> Zabbix agent mysql-stats[Table_open_cache_overflows,{$PORT}]<p>Update: 60</p>
Connection_errors_internal <p>The number of connections refused due to internal errors in the server, such as failure to start a new thread or an out-of-memory condition. Connection_errors_internal is a good one to watch, because it is incremented only when the error comes from the server itself. Internal errors can reflect an out-of-memory condition or the server’s inability to start a new thread.</p> Zabbix agent mysql-stats[Connection_errors_internal,{$PORT}]<p>Update: 60</p>
Available status <p>-</p> Zabbix agent mysql.ping[,{$PORT}]<p>Update: 60</p>
Qcache lowmem prunes <p>The number of queries that were deleted from the query cache because of low memory. Could indicate a misconfigured query_cache</p> Zabbix agent mysql-stats[Qcache_lowmem_prunes,{$PORT}]<p>Update: 60</p>
MySQL: local_infile <p>This variable controls server-side LOCAL capability for LOAD DATA statements. Depending on the local_infile setting, the server refuses or permits local data loading by clients that have LOCAL enabled on the client side. To explicitly cause the server to refuse or permit LOAD DATA LOCAL statements (regardless of how client programs and libraries are configured at build time or runtime), start mysqld with local_infile disabled or enabled, respectively. local_infile can also be set at runtime. For more information, see Section 6.1.6, “Security Issues with LOAD DATA LOCAL”. Default Value ON “MySQL Manual: Security Issues with LOAD DATA LOCAL MySQL Manual: Security-Related mysqld Options”</p> Zabbix agent mysql-stats[local_infile,{$PORT}]<p>Update: 6h</p>
Handler_rollback <p>The number of requests for a storage engine to perform a rollback operation.</p> Zabbix agent mysql-stats[Handler_rollback,{$PORT}]<p>Update: 60</p>
Select_scan <p>The number of joins that did a full scan of the first table.</p> Zabbix agent mysql-stats[Select_scan,{$PORT}]<p>Update: 60</p>
MySQL: expire_logs_days <p>Display system status of variable expire_logs_days The binary log captures DML, DDL, and security changes that occur and stores these changes in a binary format. The binary log enables point-in-time recovery, preventing data loss during a disaster recovery situation. It is used on master replication servers as a record of the statements to be sent to slave servers. It also enables you to review all alterations made to your database. However, the number of log files and the space they use can grow rapidly, especially on a busy server, so it is important to remove these files on a regular basis when they are no longer needed, as long as appropriate backups have been made. The expire_logs_days parameter enables automatic binary log removal. Links: “MySQL Manual: The Binary Log MySQL Manual: Server System Variables Bug #28238: expire_logs_days and PURGE MASTER LOGS fail when index not up to date MySQL Knowledge Base: What do I have to set up for point-in-time recovery? MySQL Knowledge Base: How can I recover all of my data up to now?”</p> Zabbix agent mysql-stats[expire_logs_days,{$PORT}]<p>Update: 6h</p>
MySQL: myisam_repair_threads <p>If this value is greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process. The default value is 1. Using multiple threads when repairing MyISAM tables can improve performance, but it can also lead to table and index corruption as reported by several bugs (#11527, #11684, #18874). Even though these bugs have been fixed, this feature is still considered beta-quality, as noted in the manual. “MySQL Manual: System Variables MySQL Bug DB: Bug #11527 MySQL Bug DB: Bug #11684 MySQL Bug DB: Bug #18874”</p> Zabbix agent mysql-stats[myisam_repair_threads,{$PORT}]<p>Update: 6h</p>
Handler_commit <p>The number of internal COMMIT statements.</p> Zabbix agent mysql-stats[Handler_commit,{$PORT}]<p>Update: 60</p>
Connection_errors_peer_address <p>The number of errors that occurred while searching for connecting client IP addresses. Note These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_peer_address,{$PORT}]<p>Update: 60</p>
MySQL: version_compile_os <p>The type of operating system on which MySQL was built.</p> Zabbix agent mysql-stats[version_compile_os,{$PORT}]<p>Update: 6h</p>
MySQL: lower_case_table_names <p>If set to 0, table names are stored as specified and comparisons are case-sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional details, see Section 9.2.2, “Identifier Case Sensitivity”. On Windows the default value is 1. On macOS, the default value is 2. You should not set lower_case_table_names to 0 if you are running MySQL on a system where the data directory resides on a case-insensitive file system (such as on Windows or macOS). It is an unsupported combination that could result in a hang condition when running an INSERT INTO … SELECT … FROM tbl_name operation with the wrong tbl_name letter case. With MyISAM, accessing table names using different letter cases could cause index corruption. An error message is printed and the server exits if you attempt to start the server with –lower_case_table_names=0 on a case-insensitive file system. If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase. The setting of this variable in MySQL 8.0 affects the behavior of replication filtering options with regard to case sensitivity. (Bug #51639) See Section 17.2.5, “How Servers Evaluate Replication Filtering Rules”, for more information. It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are based on the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared. Default Value 0 Links: MySQL Manual: Identifier Case Sensitivity https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names</p> Zabbix agent mysql-stats[lower_case_table_names,{$PORT}]<p>Update: 6h</p>
Sort_scan <p>The number of sorts that were done by scanning the table.</p> Zabbix agent mysql-stats[Sort_scan,{$PORT}]<p>Update: 60</p>
Key_blocks_not_flushed <p>The number of key blocks in the MyISAM key cache that have changed but have not yet been flushed to disk.</p> Zabbix agent mysql-stats[Key_blocks_not_flushed,{$PORT}]<p>Update: 60</p>
MySQL: Com_revoke <p>Indicate a revoke statement “MySQL Knowledge Base: What are some tips on administering users? MySQL Manual: Privileges Provided by MySQL”</p> Zabbix agent mysql-stats[Com_revoke,{$PORT}]<p>Update: 5m</p>
Version <p>-</p> Zabbix agent mysql-stats[version,{$PORT}]<p>Update: 3600</p>
MySQL: log_bin <p>Display system status of variable log_bin The binary log captures DML, DDL, and security changes that occur and stores these changes in a binary format. The binary log enables point-in-time recovery, preventing data loss during a disaster recovery situation. It also enables you to review all alterations made to your database. Links: MySQL Manual: The Binary Log MySQL Knowledge Base: What do I have to set up for point-in-time recovery? MySQL Knowledge Base: How can I recover all of my data up to now?</p> Zabbix agent mysql-stats[log_bin,{$PORT}]<p>Update: 6h</p>
MySQL: query_cache_size <p>The amount of memory allocated for caching query results. By default, the query cache is disabled. This is achieved using a default value of 1M, with a default for query_cache_type of 0. (To reduce overhead significantly if you set the size to 0, you should also start the server with query_cache_type=0. The permissible values are multiples of 1024; other values are rounded down to the nearest multiple. For nonzero values of query_cache_size, that many bytes of memory are allocated even if query_cache_type=0. See Section 8.10.3.3, “Query Cache Configuration”, for more information. The query cache needs a minimum size of about 40KB to allocate its structures. (The exact size depends on system architecture.) If you set the value of query_cache_size too small, a warning will occur Default value: 1 Mb Links: “MySQL Manual: The Query Cache MySQL Manual: Query Cache Configuration MySQL Manual: Query Cache Status and Maintenance”</p> Zabbix agent mysql-stats[query_cache_size,{$PORT}]<p>Update: 5m</p>
MySQL: sql_mode <p>SQL Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. If no SQL modes are enabled this means there is no form of server-enforced data integrity, which means incoming data that is invalid will not be rejected by the server, but instead will be changed to conform to the target column’s default datatype. Note that any client can change its own session SQL mode value at any time. Default Value (>= 5.7.8) ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION Default Value (5.7.7) ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION Default Value (>= 5.7.5, <= 5.7.6) ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ENGINE_SUBSTITUTION Default Value (<= 5.7.4) NO_ENGINE_SUBSTITUTION Valid Values ALLOW_INVALID_DATES ANSI_QUOTES ERROR_FOR_DIVISION_BY_ZERO HIGH_NOT_PRECEDENCE IGNORE_SPACE NO_AUTO_CREATE_USER NO_AUTO_VALUE_ON_ZERO NO_BACKSLASH_ESCAPES NO_DIR_IN_CREATE NO_ENGINE_SUBSTITUTION NO_FIELD_OPTIONS NO_KEY_OPTIONS NO_TABLE_OPTIONS NO_UNSIGNED_SUBTRACTION NO_ZERO_DATE NO_ZERO_IN_DATE ONLY_FULL_GROUP_BY PAD_CHAR_TO_FULL_LENGTH PIPES_AS_CONCAT REAL_AS_FLOAT STRICT_ALL_TABLES STRICT_TRANS_TABLES Links: “MySQL Manual: Server SQL Mode MySQL Manual: MySQL 5.0 FAQ - Server SQL Mode MySQL Manual: How MySQL Deals with Constraints MySQL Manual: Running MySQL in ANSI Mode”</p> Zabbix agent mysql-stats[sql_mode,{$PORT}]<p>Update: 6h</p>
Uptime <p>This variable indicates the number of seconds since the server was last restarted. This value is useful to analyze server uptime, as well as to generate reports on overall system performance. A consistent low value indicates that the server is being frequently restarted, thereby causing frequent interruptions to client service.</p> Zabbix agent mysql-stats[Uptime,{$PORT}]<p>Update: 60</p>
Sort_range <p>The number of sorts that were done using ranges.</p> Zabbix agent mysql-stats[Sort_range,{$PORT}]<p>Update: 60</p>
Commands Insert <p>Count insert commands per second. Used to see the writes on database</p> Zabbix agent mysql-stats[Com_insert,{$PORT}]<p>Update: 60</p>
MySQL: sync_binlog <p>Controls the number of binary log commit groups to collect before synchronizing the binary log to disk. When sync_binlog=0, the binary log is never synchronized to disk, and the server relies on the operating system to flush the binary log’s contents from time to time as for any other file. When sync_binlog is set to a value greater than 0, this number of binary log commit groups is periodically synchronized to disk. When sync_binlog=1, all transactions are synchronized to the binary log before they are committed. Therefore, even in the event of an unexpected restart, any transactions that are missing from the binary log are only in prepared state. This causes the server’s automatic recovery routine to roll back those transactions. This guarantees that no transaction is lost from the binary log, and is the safest option. However this can have a negative impact on performance because of an increased number of disk writes. Using a higher value improves performance, but with the increased risk of data loss. When sync_binlog=0 or sync_binlog is greater than 1, transactions are committed without having been synchronized to disk. Therefore, in the event of a power failure or operating system crash, it is possible that the server has committed some transactions that have not been synchronized to the binary log. Therefore it is impossible for the recovery routine to recover these transactions and they will be lost from the binary log. The default value of sync_binlog is 1, which is the safest choice, but as noted above can impact performance. Default Value: 1 Links: “MySQL Manual: The Binary Log MySQL Manual: Server System Variables”</p> Zabbix agent mysql-stats[sync_binlog,{$PORT}]<p>Update: 6h</p>
Select_full_join <p>This variable indicates the number of full joins MySQL has performed to satisfy client queries or the number of joins that perform table scans because they do not use indexes. A high value indicates that MySQL is being forced to perform full table joins (which are performance-intensive) instead of using indexes. This suggests a need for greater indexing of the corresponding tables. If this value is not 0, you should carefully check the indexes of your tables.</p> Zabbix agent mysql-stats[Select_full_join,{$PORT}]<p>Update: 60</p>
MySQL: flush_time <p>If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and synchronize unflushed data to disk. This option is best used only on systems with minimal resources. If flush_time is set to a non-zero value, all tables are closed every flush_time seconds to free up resources and synchronize unflushed data to disk. If your system is unreliable and tends to lock up or restart often, forcing out table changes this way degrades performance but can reduce the chance of table corruption or data loss. We recommend that this option be used only on Windows, or on systems with minimal resources.</p> Zabbix agent mysql-stats[flush_time,{$PORT}]<p>Update: 6h</p>
Key_blocks_used <p>The number of used blocks in the MyISAM key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time.</p> Zabbix agent mysql-stats[Key_blocks_used,{$PORT}]<p>Update: 60</p>
Binlog_cache_disk_use <p>The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction.</p> Zabbix agent mysql-stats[Binlog_cache_disk_use,{$PORT}]<p>Update: 60</p>
Open_table_definitions <p>The number of cached .frm files.</p> Zabbix agent mysql-stats[Open_table_definitions,{$PORT}]<p>Update: 60</p>
Handler_read_rnd <p>The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly.</p> Zabbix agent mysql-stats[Handler_read_rnd,{$PORT}]<p>Update: 60</p>
Created_tmp_files in memory <p>How many temporary files mysqld has created.</p> Zabbix agent mysql-stats[Created_tmp_files,{$PORT}]<p>Update: 60</p>
Opened_files <p>The number of files that have been opened with my_open() (a mysys library function). Parts of the server that open files without using this function do not increment the count.</p> Zabbix agent mysql-stats[Opened_files,{$PORT}]<p>Update: 60</p>
Sort_merge_passes <p>The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.</p> Zabbix agent mysql-stats[Sort_merge_passes,{$PORT}]<p>Update: 60</p>
Key reads <p>The number of physical reads of a key block from disk into the MyISAM key cache. This variable indicates the number of filesystem accesses MySQL performed to fetch database indexes. Performing filesystem reads for database indexes slows query performance. If this variable is high, it indicates that MySQL’s key cache is overloaded and should be reconfigured. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.</p> Zabbix agent mysql-stats[Key_reads,{$PORT}]<p>Update: 60</p>
Commands Delete <p>Count delete commands per second. Used to see the writes on database</p> Zabbix agent mysql-stats[Com_delete,{$PORT}]<p>Update: 60</p>
Connection_errors_tcpwrap <p>The number of connections refused by the libwrap library. Note These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_tcpwrap,{$PORT}]<p>Update: 60</p>
Select_full_range_join <p>The number of joins that used a range search on a reference table.</p> Zabbix agent mysql-stats[Select_full_range_join,{$PORT}]<p>Update: 60</p>
MySQL: default_storage_engine <p>The default storage engine. This variable sets the storage engine for permanent tables only. To set the storage engine for TEMPORARY tables, set the default_tmp_storage_engine system variable. To see which storage engines are available and enabled, use the SHOW ENGINES statement or query the INFORMATION_SCHEMA ENGINES table. “MySQL Manual: MyISAM Startup Options MySQL Manual: mysqld Command Options”</p> Zabbix agent mysql-stats[default_storage_engine,{$PORT}]<p>Update: 6h</p>
Commands Begin <p>The Com_begin statement counter variables indicate the number of times each begin statement has been executed.</p> Zabbix agent mysql-stats[Com_begin,{$PORT}]<p>Update: 60</p>
Select_range_check <p>The number of joins without keys that check for key usage after each row. If this is not 0, you should carefully check the indexes of your tables.</p> Zabbix agent mysql-stats[Select_range_check,{$PORT}]<p>Update: 60</p>
Commands Replace <p>The Com_replace statement counter variables indicate the number of times each replace statement has been executed.</p> Zabbix agent mysql-stats[Com_replace,{$PORT}]<p>Update: 60</p>
Threads created <p>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.</p> Zabbix agent mysql-stats[Threads_created,{$PORT}]<p>Update: 60</p>
Binlog_stmt_cache_use <p>The number of nontransactional statements that used the binary log statement cache.</p> Zabbix agent mysql-stats[Binlog_stmt_cache_use,{$PORT}]<p>Update: 60</p>
Open_files <p>The number of files that are open. This count includes regular files opened by the server.</p> Zabbix agent mysql-stats[Open_files,{$PORT}]<p>Update: 60</p>
Table_locks_immediate <p>The number of times that a request for a table lock could be granted immediately.</p> Zabbix agent mysql-stats[Table_locks_immediate,{$PORT}]<p>Update: 60</p>
Handler_update <p>The number of times that rows have been updated from tables per second.</p> Zabbix agent mysql-stats[Handler_update,{$PORT}]<p>Update: 60</p>
MySQL: event_scheduler <p>“The Event Scheduler is a very useful feature when enabled. It is a framework for executing SQL commands at specific times or at regular intervals. Conceptually, it is similar to the idea of the Unix crontab (also known as a ““cron job””) or the Windows Task Scheduler. The basics of its architecture are simple. An event is a stored routine with a starting date and time, and a recurring tag. Once defined and activated, it will run when requested. Unlike triggers, events are not linked to specific table operations, but to dates and times. Using the event scheduler, the database administrator can perform recurring events with minimal hassle. Common uses are the cleanup of obsolete data, the creation of summary tables for statistics, and monitoring of server performance and usage.” Default Value OFF Valid Values ON OFF DISABLED Links: “MySQL Manual: Using the Event Scheduler MySQL Manual: Event Scheduler Overview”</p> Zabbix agent mysql-stats[event_scheduler,{$PORT}]<p>Update: 5m</p>
Qcache inserts <p>The number of queries added to the query cache.</p> Zabbix agent mysql-stats[Qcache_inserts,{$PORT}]<p>Update: 60</p>
Handler_read_next <p>The number of requests to read the next row in key order. </p> Zabbix agent mysql-stats[Handler_read_next,{$PORT}]<p>Update: 60</p>
Connection_errors_select <p>The number of errors that occurred during calls to select() or poll() on the listening port. (Failure of this operation does not necessarily means a client connection was rejected.) Note These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_select,{$PORT}]<p>Update: 60</p>
Threads connected <p>This variable indicates the total number of clients that have currently open connections to the server. It provides real-time information on how many clients are currently connected to the server. This can help in traffic analysis or in deciding the best time for a server re-start.</p> Zabbix agent mysql-stats[Threads_connected,{$PORT}]<p>Update: 60</p>
Queries <p>The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.</p> Zabbix agent mysql-stats[Queries,{$PORT}]<p>Update: 60</p>
Key reads requests <p>The number of requests to read a key block from the MyISAM key cache.</p> Zabbix agent mysql-stats[Key_read_requests,{$PORT}]<p>Update: 60</p>
Commands Commit <p>The Com_commi statement counter variables indicate the number of times each commit statement has been executed.</p> Zabbix agent mysql-stats[Com_commit,{$PORT}]<p>Update: 60</p>
Commands Create Table <p>The Com_create_table statement counter variables indicate the number of times each create_table statement has been executed.</p> Zabbix agent mysql-stats[Com_create_table,{$PORT}]<p>Update: 60</p>
MySQL: Com_grant <p>Indicate a grant statement “MySQL Knowledge Base: What are some tips on administering users? MySQL Manual: Privileges Provided by MySQL”</p> Zabbix agent mysql-stats[Com_grant,{$PORT}]<p>Update: 5m</p>
Connection_errors_max_connections <p>The number of connections refused because the server max_connections limit was reached. Note These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_max_connections,{$PORT}]<p>Update: 60</p>
MySQL: query_cache_type <p>Set the query cache type. Setting the GLOBAL value sets the type for all clients that connect thereafter. Individual clients can set the SESSION value to affect their own use of the query cache. Possible values are shown in the following table. Option Description 0 or OFF Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0. 1 or ON Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE. 2 or DEMAND Cache results only for cacheable queries that begin with SELECT SQL_CACHE. This variable defaults to OFF. If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution. Links: “MySQL Manual: The Query Cache MySQL Manual: Query Cache Configuration MySQL Manual: Query Cache Status and Maintenance”</p> Zabbix agent mysql-stats[query_cache_type,{$PORT}]<p>Update: 5m</p>
Handler_read_first <p>The number of times the first entry in an index was read. If this value is high, it suggests that the server is doing a lot of full index scans; If MySQL is frequently accessing the first row of a table index, it suggests that it is performing a sequential scan of the entire index. This indicates that the corresponding table is not properly indexed.</p> Zabbix agent mysql-stats[Handler_read_first,{$PORT}]<p>Update: 60</p>
Aborted connections <p>The number of failed attempts to connect to the MySQL server. If this counter is increasing, your clients are trying and failing to connect to the database. Investigate the source of the problem with fine-grained connection metrics such as Connection_errors_max_connections and Connection_errors_internal.</p> Zabbix agent mysql-stats[Aborted_connects,{$PORT}]<p>Update: 60</p>
Incoming traffic <p>Incoming bytes per second</p> Zabbix agent mysql-stats[Bytes_received,{$PORT}]<p>Update: 60</p>
Outcoming traffic <p>Outcoming bytes per second</p> Zabbix agent mysql-stats[Bytes_sent,{$PORT}]<p>Update: 60</p>
Table_open_cache_hits <p>The number of hits for open tables cache lookups.</p> Zabbix agent mysql-stats[Table_open_cache_hits,{$PORT}]<p>Update: 60</p>
MySQL: Com_prepare_sql <p>“Prepared statements may increase performance in applications that execute similar statements more than once, primarily because the query is parsed only once. Prepared statements can also reduce network traffic because it is only necessary to send the data for the parameters for each execution rather than the whole statement. However, prepared statements take time to prepare and consume memory in the MySQL server until they are closed, so it is important to use them properly. If you are only executing a statement a few times, the overhead of creating a prepared statement may not be worthwhile.”</p> Zabbix agent mysql-stats[Com_prepare_sql,{$PORT}]<p>Update: 5m</p>
Key writes requests <p>The number of requests to write a key block to the MyISAM key cache.</p> Zabbix agent mysql-stats[Key_write_requests,{$PORT}]<p>Update: 60</p>
Created tmp tables on disk <p>The number of internal on-disk temporary tables created by the server while executing statements. Accessing tables on disk is typically slower than accessing the same tables in memory. So queries that use the CREATE TEMPORARY TABLE syntax are likely to be slow when this value is high. Must be ass low ass possible. Best 0 If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values. If Created_tmp_disk_tables is large, you may want to increase the tmp_table_size or max_heap_table_size value to lessen the likelihood that internal temporary tables in memory will be converted to on-disk tables. You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.</p> Zabbix agent mysql-stats[Created_tmp_disk_tables,{$PORT}]<p>Update: 60</p>
Connection_errors_accept <p>The number of errors that occurred during calls to accept() on the listening port. These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_accept,{$PORT}]<p>Update: 60</p>
MySQL: tmp_table_size <p>The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables. Default: 16 Mb The actual limit is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk temporary table. As of MySQL 5.7.5, the internal_tmp_disk_storage_engine option defines the storage engine used for on-disk temporary tables. Prior to MySQL 5.7.5, the MyISAM storage engine is used. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory. You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables. Links: MySQL Manual: How MySQL Uses Internal Temporary Tables MySQL Manual: System Variables MySQL Performance Blog: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE</p> Zabbix agent mysql-stats[tmp_table_size,{$PORT}]<p>Update: 6h</p>
Key_blocks_unused <p>The number of unused blocks in the MyISAM key cache. You can use this value to determine how much of the key cache is in use; see the discussion of key_buffer_size in Section 5.1.5, “Server System Variables”.</p> Zabbix agent mysql-stats[Key_blocks_unused,{$PORT}]<p>Update: 60</p>
Handler_write <p>The number of requests to insert a row in a table per second.</p> Zabbix agent mysql-stats[Handler_write,{$PORT}]<p>Update: 60</p>
Slow_launch_threads <p>The number of threads that have taken more than slow_launch_time seconds to create.</p> Zabbix agent mysql-stats[Slow_launch_threads,{$PORT}]<p>Update: 60</p>
Select_range <p>The number of joins that used ranges on the first table. This is normally not a critical issue even if the value is quite large.</p> Zabbix agent mysql-stats[Select_range,{$PORT}]<p>Update: 60</p>
Commands Select <p>Count select commands per second. Used to see the read throughput on database</p> Zabbix agent mysql-stats[Com_select,{$PORT}]<p>Update: 60</p>
Threads running <p>The number of threads that are not sleeping. Is the number of queries running concurrently and fight between to complete in time</p> Zabbix agent mysql-stats[Threads_running,{$PORT}]<p>Update: 60</p>
Handler_read_key <p>The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.</p> Zabbix agent mysql-stats[Handler_read_key,{$PORT}]<p>Update: 60</p>
Opened_table_definitions <p>The number of .frm files that have been cached.</p> Zabbix agent mysql-stats[Opened_table_definitions,{$PORT}]<p>Update: 60</p>
MySQL: concurrent_insert <p>Default: AUTO If AUTO (the default), MySQL permits INSERT and SELECT statements to run concurrently for MyISAM tables that have no free blocks in the middle of the data file. If you start mysqld with –skip-new, this variable is set to NEVER. This variable can take the values shown in the following table. The variable can be assigned using either the name values or corresponding integer values. Value Description NEVER (or 0) Disables concurrent inserts AUTO (or 1) (Default) Enables concurrent insert for MyISAM tables that do not have holes ALWAYS (or 2) Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.</p> Zabbix agent mysql-stats[concurrent_insert,{$PORT}]<p>Update: 6h</p>
MySQL: old_passwords <p>This variable controls the password hashing method used by the PASSWORD() function. It also influences password hashing performed by CREATE USER and GRANT statements that specify a password using an IDENTIFIED BY clause. The following table shows, for each password hashing method, the permitted value of old_passwords and which authentication plugins use the hashing method. Password Hashing Method old_passwords Value Associated Authentication Plugin MySQL 4.1 native hashing 0 mysql_native_password SHA-256 hashing 2 sha256_password If you set old_passwords=2, follow the instructions for using the sha256_password plugin at Section 6.5.1.4, “SHA-256 Pluggable Authentication”. The server sets the global old_passwords value during startup to be consistent with the password hashing method required by the authentication plugin indicated by the default_authentication_plugin system variable. When a client successfully connects to the server, the server sets the session old_passwords value appropriately for the account authentication method. For example, if the account uses the sha256_password authentication plugin, the server sets old_passwords=2. Default Value 0</p> Zabbix agent mysql-stats[old_passwords,{$PORT}]<p>Update: 6h</p>
MySQL: log_warnings <p>“Error conditions encountered by a MySQL server are always logged in the error log, but warning conditions are only logged if log_warnings is set to a value greater than 0. If warnings are not logged you will not get valuable information about aborted connections and various other communication errors. This is especially important if you use replication so you get more information about what is happening, such as messages about network failures and re-connections. Note that as of MySQL 5.7.2, the log_error_verbosity system variable is preferred over, and should be used instead of, log_warnings. WARNING: One of the system variables used in this advisor (log_warnings) is deprecated and has been removed in MySQL Server 8.0. Please plan ahead accordingly.” Default Value (64-bit platforms) 2 Links: “MySQL Manual: The Error Log MySQL Manual: Server Command Options - log_warnings MySQL Manual: Server Command Options - log_error_verbosity MySQL Manual: Binary Logging Options and Variables - log_statements_unsafe_for_binlog MySQL Manual: Communication Errors and Aborted Connections Bug #24761: dropped TCP connections not logged as errors but result in blocked host MySQL Manual: Usage of Row-based Logging and Row-Based Replication”</p> Zabbix agent mysql-stats[log_warnings,{$PORT}]<p>Update: 6h</p>
Qcache not cached <p>The number of noncached queries (not cacheable, or not cached due to the query_cache_type setting).</p> Zabbix agent mysql-stats[Qcache_not_cached,{$PORT}]<p>Update: 60</p>
Qcache free memory <p>The amount of free memory for the query cache.</p> Zabbix agent mysql-stats[Qcache_free_memory,{$PORT}]<p>Update: 60</p>
Handler_savepoint_rollback <p>The number of requests for a storage engine to roll back to a savepoint.</p> Zabbix agent mysql-stats[Handler_savepoint_rollback,{$PORT}]<p>Update: 60</p>
Handler_read_rnd_next <p>The number of requests to read the next row in the data file. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.</p> Zabbix agent mysql-stats[Handler_read_rnd_next,{$PORT}]<p>Update: 60</p>
Handler_read_last <p>The number of requests to read the last key in an index. </p> Zabbix agent mysql-stats[Handler_read_last,{$PORT}]<p>Update: 60</p>
Open_tables <p>The number of tables that are open. This value is best analyzed in combination with the size of the table cache. If this value is low and the table_cache value is high, it’s probably safe to reduce the cache size without affecting performance. On the other hand, if this value is high and close to the table_cache value, there is benefit in increasing the size of the table cache.</p> Zabbix agent mysql-stats[Open_tables,{$PORT}]<p>Update: 60</p>
Sort_rows <p>The number of sorted rows.</p> Zabbix agent mysql-stats[Sort_rows,{$PORT}]<p>Update: 60</p>
Qcache hits <p>The number of query cache hits.</p> Zabbix agent mysql-stats[Qcache_hits,{$PORT}]<p>Update: 60</p>
MySQL: Com_stmt_prepare <p>Com_stmt_reprepare indicates the number of times statements were automatically reprepared by the server after metadata changes to tables or views referred to by the statement. A reprepare operation increments Com_stmt_reprepare, and also Com_stmt_prepare. Prepared statements may increase performance in applications that execute similar statements more than once, primarily because the query is parsed only once. Prepared statements can also reduce network traffic because it is only necessary to send the data for the parameters for each execution rather than the whole statement.</p> Zabbix agent mysql-stats[Com_stmt_prepare,{$PORT}]<p>Update: 5m</p>
Qcache_total_blocks <p>The total number of blocks in the query cache.</p> Zabbix agent mysql-stats[Qcache_total_blocks,{$PORT}]<p>Update: 60</p>
Key writes to disk <p>The number of physical writes of a key block from the MyISAM key cache to disk.</p> Zabbix agent mysql-stats[Key_writes,{$PORT}]<p>Update: 60</p>
Handler_delete <p>The number of times that rows have been deleted from tables per second.</p> Zabbix agent mysql-stats[Handler_delete,{$PORT}]<p>Update: 60</p>
Qcache queries in cache <p>The number of queries who are in the query cache.</p> Zabbix agent mysql-stats[Qcache_queries_in_cache,{$PORT}]<p>Update: 60</p>
MySQL: myisam_recover_options <p>The myisam-recover-options option (named myisam-recover before MySQL 5.5.3) enables automatic MyISAM crash recovery should a MyISAM table become corrupt for some reason. If this option is not set, then a table will be “Marked as crashed” if it becomes corrupt, and no sessions will be able to SELECT from it, or perform any sort of DML against it. MySQL Manual: MyISAM Startup Options MySQL Manual: mysqld Command Options</p> Zabbix agent mysql-stats[myisam_recover_options,{$PORT}]<p>Update: 6h</p>
Binlog_stmt_cache_disk_use <p>The number of nontransaction statements that used the binary log statement cache but that exceeded the value of binlog_stmt_cache_size and used a temporary file to store those statements.</p> Zabbix agent mysql-stats[Binlog_stmt_cache_disk_use,{$PORT}]<p>Update: 60</p>
Max used connections <p>The maximum number of connections that have been in use simultaneously since the server started. This value provides a benchmark to help you decide the maximum number of connections your server should support. It can also help in traffic analysis. (max_used_connections / max_connections) indicates if you could run out soon of connection slots. Alarm if connections usage is > 85%.</p> Zabbix agent mysql-stats[Max_used_connections,{$PORT}]<p>Update: 60</p>
Table_locks_waited <p>The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.</p> Zabbix agent mysql-stats[Table_locks_waited,{$PORT}]<p>Update: 60</p>
Questions <p>The number of statements executed by the server. As of MySQL 5.0.72, this includes only statements sent to the server by clients and no longer includes statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands. MySQL increments the questions and queries counters before executing the query</p> Zabbix agent mysql-stats[Questions,{$PORT}]<p>Update: 60</p>
MySQL: thread_handling <p>The thread-handling model used by the server for connection threads. The permissible values are no-threads (the server uses a single thread to handle one connection) and one-thread-per-connection (the server uses one thread to handle each client connection). no-threads is useful for debugging under Linux; Default Value: one-thread-per-connection Valid Values (<= 5.7.8) no-threads one-thread-per-connection Links: MySQL Manual: MySQL Enterprise Thread Pool MySQL Manual: Thread Pool Installation</p> Zabbix agent mysql-stats[thread_handling,{$PORT}]<p>Update: 5m</p>
Slow queries <p>The number of queries that have taken more than long_query_time seconds. This counter increments regardless of whether the slow query log is enabled. A high value indicates that many queries are not being optimally executed. A necessary next step would be to examine the slow query log and identify these slow queries for optimization.</p> Zabbix agent mysql-stats[Slow_queries,{$PORT}]<p>Update: 60</p>
Handler_read_prev <p>The number of requests to read the previous row in key order.</p> Zabbix agent mysql-stats[Handler_read_prev,{$PORT}]<p>Update: 60</p>
Qcache_free_blocks <p>The number of free memory blocks in the query cache.</p> Zabbix agent mysql-stats[Qcache_free_blocks,{$PORT}]<p>Update: 60</p>
MySQL: Com_stmt_close <p>“Prepared statements may increase performance in applications that execute similar statements more than once, primarily because the query is parsed only once. Prepared statements can also reduce network traffic because it is only necessary to send the data for the parameters for each execution rather than the whole statement. However, prepared statements take time to prepare and consume memory in the MySQL server until they are closed, so it is important to use them properly. If you are not closing prepared statements when you are done with them, you are needlessly tying up memory that could be put to use in other ways.”</p> Zabbix agent mysql-stats[Com_stmt_close,{$PORT}]<p>Update: 5m</p>
MySQL: Com_execute_sql <p>“Prepared statements may increase performance in applications that execute similar statements more than once, primarily because the query is parsed only once. Prepared statements can also reduce network traffic because it is only necessary to send the data for the parameters for each execution rather than the whole statement. However, prepared statements take time to prepare and consume memory in the MySQL server until they are closed, so it is important to use them properly. If you are only executing a statement a few times, the overhead of creating a prepared statement may not be worthwhile.”</p> Zabbix agent mysql-stats[Com_execute_sql,{$PORT}]<p>Update: 5m</p>
MySQL: have_symlink <p>YES if symbolic link support is enabled, NO if not. This is required on Unix for support of the DATA DIRECTORY and INDEX DIRECTORY table options. If the server is started with the –skip-symbolic-links option, the value is DISABLED. This variable has no meaning on Windows. Links: “MySQL Manual: Making MySQL Secure Against Attackers MySQL Manual: Using Symbolic Links MySQL Manual: Disk Issues MySQL Manual: CREATE TABLE Syntax”</p> Zabbix agent mysql-stats[have_symlink,{$PORT}]<p>Update: 6h</p>
Commands Update <p>Count update commands per second. Used to see the writes on database</p> Zabbix agent mysql-stats[Com_update,{$PORT}]<p>Update: 60</p>

Triggers

There are no triggers in this template.

Halley MySQL Python server mi passive

Description

An mysql template create for halley.it

Overview

Here is my template for monitoring MySQL on a Windows server with multiple instances. The logic for this monitoring is:

Script will take 2 parameters: variable ($1) and port ($2). Variable 1 will be the word for what script will looking for in script results file. The first zabbix request will generate a file : mysql_results_$PORT.txt. All next requests in 55 sec will take the values from this file, not from mysql. If the file is over 55 sec, file is rewriten. This assure to have 3 db request for all 117 items

Steps to install:

  1. Create C:\script

  2. Copy in this folder the script mysql_stats.py

  3. in zabbix agent add at UserParameters

UserParameter=mysql-stats[*],c:\script\mysql_stats.py “$1” “$2” # user parameter for show extended status - no grants needed

UserParameter=mysql.ping[*],mysqladmin -u -p –host=127.0.0.1 -P”$2” ping # -P”$2” will tahe the port number, who is the second variable from request

  1. Create a different host for every MySQL instance and specify in host Macros the MySQL port {$PORT}

  2. Add template Halley MySQL Server mi passive. I use passive items beacause not need to specify host name as hostname of the client. Don’t forget to enable on host agent the passive monitoring

  3. Check the data:

a0) from client command line run c:\script\mysql_stats.py Uptime Port

a) from zabbix server with command zabbix_get -s host_ip -k “mysql-stats[Uptime,$port]”

a1) from zabbix server with command zabbix_get -s host_ip -k “mysql-stats[log_bin,$port]”

b) Look in Latest data

Don’t forget:

  1. Specify port in host macros

  2. Install Python on server and when will install select custom install and check for all users

All files are on https://1drv.ms/f/s!Ag6IN4i1M9MuhXQZZpQ5dn2t4MCy

P.S.

This template include description with advices for items and triggers. I spend over a month to get best practices from MySQL Enterprise Monitor, another mysql templates or just mysql documentation. A feedback from you will be nice

Author

Tudor Ticau

Macros used

There are no macros links in this template.

There are no template links in this template.

Discovery rules

There are no discovery rules in this template.

Items collected

Name Description Type Key and additional info
Connections <p>The number of connection attempts (successful or not) to the MySQL server per second.</p> Zabbix agent mysql-stats[Connections,{$PORT}]<p>Update: 60</p>
MySQL: thread_cache_size <p>How many threads the server should cache for reuse. When a client disconnects, the client’s threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads. By examining the difference between the Connections and Threads_created status variables, you can see how efficient the thread cache is. For details, see Section 5.1.9, “Server Status Variables”. The default value is based on the following formula, capped to a limit of 100: 8 + (max_connections / 100)</p> Zabbix agent mysql-stats[thread_cache_size,{$PORT}]<p>Update: 5m</p>
Opened_tables <p>The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.</p> Zabbix agent mysql-stats[Opened_tables,{$PORT}]<p>Update: 60</p>
Threads cached <p>The number of threads in the thread cache.</p> Zabbix agent mysql-stats[Threads_cached,{$PORT}]<p>Update: 60</p>
Table_open_cache_misses <p>The number of misses for open tables cache lookups.</p> Zabbix agent mysql-stats[Table_open_cache_misses,{$PORT}]<p>Update: 60</p>
Aborted_clients <p>The number of connections that were aborted because the client died without closing the connection properly. If this value increments, it usually means there’s been an application error, such as the programmer forgetting to close MySQL connections properly before terminating the program. This is not usually indicative of a big problem.</p> Zabbix agent mysql-stats[Aborted_clients,{$PORT}]<p>Update: 60</p>
Handler_savepoint <p>The number of requests for a storage engine to place a savepoint.</p> Zabbix agent mysql-stats[Handler_savepoint,{$PORT}]<p>Update: 60</p>
Binlog_cache_use <p>The number of transactions that used the binary log cache</p> Zabbix agent mysql-stats[Binlog_cache_use,{$PORT}]<p>Update: 60</p>
MySQL: secure_auth <p>If this variable is enabled, the server blocks connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format. Enable this variable to prevent all use of passwords employing the old format (and hence insecure communication over the network). This variable is deprecated and will be removed in a future MySQL release. It is always enabled and attempting to disable it produces an error. Server startup fails with an error if this variable is enabled and the privilege tables are in pre-4.1 format. See Section 6.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”. Default value: ON Links: “MySQL Manual: Password Hashing as of MySQL 4.1 MySQL Manual: Security-Related mysqld Options MySQL Manual: mysql Options MySQL Manual: Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”</p> Zabbix agent mysql-stats[secure_auth,{$PORT}]<p>Update: 6h</p>
Created_tmp_tables on memory <p>The number of internal temporary tables created by the server while executing statements. You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.</p> Zabbix agent mysql-stats[Created_tmp_tables,{$PORT}]<p>Update: 60</p>
MySQL: max_heap_table_size <p>This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value. Default: 16Mb This variable is also used in conjunction with tmp_table_size to limit the size of internal in-memory tables. Links: MySQL Manual: How MySQL Uses Internal Temporary Tables MySQL Manual: System Variables MySQL Performance Blog: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE</p> Zabbix agent mysql-stats[max_heap_table_size,{$PORT}]<p>Update: 6h</p>
Table_open_cache_overflows <p>The number of overflows for the open tables cache. This is the number of times, after a table is opened or closed, a cache instance has an unused entry and the size of the instance is larger than table_open_cache / table_open_cache_instances.</p> Zabbix agent mysql-stats[Table_open_cache_overflows,{$PORT}]<p>Update: 60</p>
Connection_errors_internal <p>The number of connections refused due to internal errors in the server, such as failure to start a new thread or an out-of-memory condition. Connection_errors_internal is a good one to watch, because it is incremented only when the error comes from the server itself. Internal errors can reflect an out-of-memory condition or the server’s inability to start a new thread.</p> Zabbix agent mysql-stats[Connection_errors_internal,{$PORT}]<p>Update: 60</p>
Available status <p>-</p> Zabbix agent mysql.ping[,{$PORT}]<p>Update: 60</p>
Qcache lowmem prunes <p>The number of queries that were deleted from the query cache because of low memory. Could indicate a misconfigured query_cache</p> Zabbix agent mysql-stats[Qcache_lowmem_prunes,{$PORT}]<p>Update: 60</p>
MySQL: local_infile <p>This variable controls server-side LOCAL capability for LOAD DATA statements. Depending on the local_infile setting, the server refuses or permits local data loading by clients that have LOCAL enabled on the client side. To explicitly cause the server to refuse or permit LOAD DATA LOCAL statements (regardless of how client programs and libraries are configured at build time or runtime), start mysqld with local_infile disabled or enabled, respectively. local_infile can also be set at runtime. For more information, see Section 6.1.6, “Security Issues with LOAD DATA LOCAL”. Default Value ON “MySQL Manual: Security Issues with LOAD DATA LOCAL MySQL Manual: Security-Related mysqld Options”</p> Zabbix agent mysql-stats[local_infile,{$PORT}]<p>Update: 6h</p>
Handler_rollback <p>The number of requests for a storage engine to perform a rollback operation.</p> Zabbix agent mysql-stats[Handler_rollback,{$PORT}]<p>Update: 60</p>
Select_scan <p>The number of joins that did a full scan of the first table.</p> Zabbix agent mysql-stats[Select_scan,{$PORT}]<p>Update: 60</p>
MySQL: expire_logs_days <p>Display system status of variable expire_logs_days The binary log captures DML, DDL, and security changes that occur and stores these changes in a binary format. The binary log enables point-in-time recovery, preventing data loss during a disaster recovery situation. It is used on master replication servers as a record of the statements to be sent to slave servers. It also enables you to review all alterations made to your database. However, the number of log files and the space they use can grow rapidly, especially on a busy server, so it is important to remove these files on a regular basis when they are no longer needed, as long as appropriate backups have been made. The expire_logs_days parameter enables automatic binary log removal. Links: “MySQL Manual: The Binary Log MySQL Manual: Server System Variables Bug #28238: expire_logs_days and PURGE MASTER LOGS fail when index not up to date MySQL Knowledge Base: What do I have to set up for point-in-time recovery? MySQL Knowledge Base: How can I recover all of my data up to now?”</p> Zabbix agent mysql-stats[expire_logs_days,{$PORT}]<p>Update: 6h</p>
MySQL: myisam_repair_threads <p>If this value is greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process. The default value is 1. Using multiple threads when repairing MyISAM tables can improve performance, but it can also lead to table and index corruption as reported by several bugs (#11527, #11684, #18874). Even though these bugs have been fixed, this feature is still considered beta-quality, as noted in the manual. “MySQL Manual: System Variables MySQL Bug DB: Bug #11527 MySQL Bug DB: Bug #11684 MySQL Bug DB: Bug #18874”</p> Zabbix agent mysql-stats[myisam_repair_threads,{$PORT}]<p>Update: 6h</p>
Handler_commit <p>The number of internal COMMIT statements.</p> Zabbix agent mysql-stats[Handler_commit,{$PORT}]<p>Update: 60</p>
Connection_errors_peer_address <p>The number of errors that occurred while searching for connecting client IP addresses. Note These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_peer_address,{$PORT}]<p>Update: 60</p>
MySQL: version_compile_os <p>The type of operating system on which MySQL was built.</p> Zabbix agent mysql-stats[version_compile_os,{$PORT}]<p>Update: 6h</p>
MySQL: lower_case_table_names <p>If set to 0, table names are stored as specified and comparisons are case-sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional details, see Section 9.2.2, “Identifier Case Sensitivity”. On Windows the default value is 1. On macOS, the default value is 2. You should not set lower_case_table_names to 0 if you are running MySQL on a system where the data directory resides on a case-insensitive file system (such as on Windows or macOS). It is an unsupported combination that could result in a hang condition when running an INSERT INTO … SELECT … FROM tbl_name operation with the wrong tbl_name letter case. With MyISAM, accessing table names using different letter cases could cause index corruption. An error message is printed and the server exits if you attempt to start the server with –lower_case_table_names=0 on a case-insensitive file system. If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase. The setting of this variable in MySQL 8.0 affects the behavior of replication filtering options with regard to case sensitivity. (Bug #51639) See Section 17.2.5, “How Servers Evaluate Replication Filtering Rules”, for more information. It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are based on the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared. Default Value 0 Links: MySQL Manual: Identifier Case Sensitivity https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names</p> Zabbix agent mysql-stats[lower_case_table_names,{$PORT}]<p>Update: 6h</p>
Sort_scan <p>The number of sorts that were done by scanning the table.</p> Zabbix agent mysql-stats[Sort_scan,{$PORT}]<p>Update: 60</p>
Key_blocks_not_flushed <p>The number of key blocks in the MyISAM key cache that have changed but have not yet been flushed to disk.</p> Zabbix agent mysql-stats[Key_blocks_not_flushed,{$PORT}]<p>Update: 60</p>
MySQL: Com_revoke <p>Indicate a revoke statement “MySQL Knowledge Base: What are some tips on administering users? MySQL Manual: Privileges Provided by MySQL”</p> Zabbix agent mysql-stats[Com_revoke,{$PORT}]<p>Update: 5m</p>
Version <p>-</p> Zabbix agent mysql-stats[version,{$PORT}]<p>Update: 3600</p>
MySQL: log_bin <p>Display system status of variable log_bin The binary log captures DML, DDL, and security changes that occur and stores these changes in a binary format. The binary log enables point-in-time recovery, preventing data loss during a disaster recovery situation. It also enables you to review all alterations made to your database. Links: MySQL Manual: The Binary Log MySQL Knowledge Base: What do I have to set up for point-in-time recovery? MySQL Knowledge Base: How can I recover all of my data up to now?</p> Zabbix agent mysql-stats[log_bin,{$PORT}]<p>Update: 6h</p>
MySQL: query_cache_size <p>The amount of memory allocated for caching query results. By default, the query cache is disabled. This is achieved using a default value of 1M, with a default for query_cache_type of 0. (To reduce overhead significantly if you set the size to 0, you should also start the server with query_cache_type=0. The permissible values are multiples of 1024; other values are rounded down to the nearest multiple. For nonzero values of query_cache_size, that many bytes of memory are allocated even if query_cache_type=0. See Section 8.10.3.3, “Query Cache Configuration”, for more information. The query cache needs a minimum size of about 40KB to allocate its structures. (The exact size depends on system architecture.) If you set the value of query_cache_size too small, a warning will occur Default value: 1 Mb Links: “MySQL Manual: The Query Cache MySQL Manual: Query Cache Configuration MySQL Manual: Query Cache Status and Maintenance”</p> Zabbix agent mysql-stats[query_cache_size,{$PORT}]<p>Update: 5m</p>
MySQL: sql_mode <p>SQL Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. If no SQL modes are enabled this means there is no form of server-enforced data integrity, which means incoming data that is invalid will not be rejected by the server, but instead will be changed to conform to the target column’s default datatype. Note that any client can change its own session SQL mode value at any time. Default Value (>= 5.7.8) ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION Default Value (5.7.7) ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION Default Value (>= 5.7.5, <= 5.7.6) ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ENGINE_SUBSTITUTION Default Value (<= 5.7.4) NO_ENGINE_SUBSTITUTION Valid Values ALLOW_INVALID_DATES ANSI_QUOTES ERROR_FOR_DIVISION_BY_ZERO HIGH_NOT_PRECEDENCE IGNORE_SPACE NO_AUTO_CREATE_USER NO_AUTO_VALUE_ON_ZERO NO_BACKSLASH_ESCAPES NO_DIR_IN_CREATE NO_ENGINE_SUBSTITUTION NO_FIELD_OPTIONS NO_KEY_OPTIONS NO_TABLE_OPTIONS NO_UNSIGNED_SUBTRACTION NO_ZERO_DATE NO_ZERO_IN_DATE ONLY_FULL_GROUP_BY PAD_CHAR_TO_FULL_LENGTH PIPES_AS_CONCAT REAL_AS_FLOAT STRICT_ALL_TABLES STRICT_TRANS_TABLES Links: “MySQL Manual: Server SQL Mode MySQL Manual: MySQL 5.0 FAQ - Server SQL Mode MySQL Manual: How MySQL Deals with Constraints MySQL Manual: Running MySQL in ANSI Mode”</p> Zabbix agent mysql-stats[sql_mode,{$PORT}]<p>Update: 6h</p>
Uptime <p>This variable indicates the number of seconds since the server was last restarted. This value is useful to analyze server uptime, as well as to generate reports on overall system performance. A consistent low value indicates that the server is being frequently restarted, thereby causing frequent interruptions to client service.</p> Zabbix agent mysql-stats[Uptime,{$PORT}]<p>Update: 60</p>
Sort_range <p>The number of sorts that were done using ranges.</p> Zabbix agent mysql-stats[Sort_range,{$PORT}]<p>Update: 60</p>
Commands Insert <p>Count insert commands per second. Used to see the writes on database</p> Zabbix agent mysql-stats[Com_insert,{$PORT}]<p>Update: 60</p>
MySQL: sync_binlog <p>Controls the number of binary log commit groups to collect before synchronizing the binary log to disk. When sync_binlog=0, the binary log is never synchronized to disk, and the server relies on the operating system to flush the binary log’s contents from time to time as for any other file. When sync_binlog is set to a value greater than 0, this number of binary log commit groups is periodically synchronized to disk. When sync_binlog=1, all transactions are synchronized to the binary log before they are committed. Therefore, even in the event of an unexpected restart, any transactions that are missing from the binary log are only in prepared state. This causes the server’s automatic recovery routine to roll back those transactions. This guarantees that no transaction is lost from the binary log, and is the safest option. However this can have a negative impact on performance because of an increased number of disk writes. Using a higher value improves performance, but with the increased risk of data loss. When sync_binlog=0 or sync_binlog is greater than 1, transactions are committed without having been synchronized to disk. Therefore, in the event of a power failure or operating system crash, it is possible that the server has committed some transactions that have not been synchronized to the binary log. Therefore it is impossible for the recovery routine to recover these transactions and they will be lost from the binary log. The default value of sync_binlog is 1, which is the safest choice, but as noted above can impact performance. Default Value: 1 Links: “MySQL Manual: The Binary Log MySQL Manual: Server System Variables”</p> Zabbix agent mysql-stats[sync_binlog,{$PORT}]<p>Update: 6h</p>
Select_full_join <p>This variable indicates the number of full joins MySQL has performed to satisfy client queries or the number of joins that perform table scans because they do not use indexes. A high value indicates that MySQL is being forced to perform full table joins (which are performance-intensive) instead of using indexes. This suggests a need for greater indexing of the corresponding tables. If this value is not 0, you should carefully check the indexes of your tables.</p> Zabbix agent mysql-stats[Select_full_join,{$PORT}]<p>Update: 60</p>
MySQL: flush_time <p>If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and synchronize unflushed data to disk. This option is best used only on systems with minimal resources. If flush_time is set to a non-zero value, all tables are closed every flush_time seconds to free up resources and synchronize unflushed data to disk. If your system is unreliable and tends to lock up or restart often, forcing out table changes this way degrades performance but can reduce the chance of table corruption or data loss. We recommend that this option be used only on Windows, or on systems with minimal resources.</p> Zabbix agent mysql-stats[flush_time,{$PORT}]<p>Update: 6h</p>
Key_blocks_used <p>The number of used blocks in the MyISAM key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time.</p> Zabbix agent mysql-stats[Key_blocks_used,{$PORT}]<p>Update: 60</p>
Binlog_cache_disk_use <p>The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction.</p> Zabbix agent mysql-stats[Binlog_cache_disk_use,{$PORT}]<p>Update: 60</p>
Open_table_definitions <p>The number of cached .frm files.</p> Zabbix agent mysql-stats[Open_table_definitions,{$PORT}]<p>Update: 60</p>
Handler_read_rnd <p>The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly.</p> Zabbix agent mysql-stats[Handler_read_rnd,{$PORT}]<p>Update: 60</p>
Created_tmp_files in memory <p>How many temporary files mysqld has created.</p> Zabbix agent mysql-stats[Created_tmp_files,{$PORT}]<p>Update: 60</p>
Opened_files <p>The number of files that have been opened with my_open() (a mysys library function). Parts of the server that open files without using this function do not increment the count.</p> Zabbix agent mysql-stats[Opened_files,{$PORT}]<p>Update: 60</p>
Sort_merge_passes <p>The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.</p> Zabbix agent mysql-stats[Sort_merge_passes,{$PORT}]<p>Update: 60</p>
Key reads <p>The number of physical reads of a key block from disk into the MyISAM key cache. This variable indicates the number of filesystem accesses MySQL performed to fetch database indexes. Performing filesystem reads for database indexes slows query performance. If this variable is high, it indicates that MySQL’s key cache is overloaded and should be reconfigured. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.</p> Zabbix agent mysql-stats[Key_reads,{$PORT}]<p>Update: 60</p>
Commands Delete <p>Count delete commands per second. Used to see the writes on database</p> Zabbix agent mysql-stats[Com_delete,{$PORT}]<p>Update: 60</p>
Connection_errors_tcpwrap <p>The number of connections refused by the libwrap library. Note These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_tcpwrap,{$PORT}]<p>Update: 60</p>
Select_full_range_join <p>The number of joins that used a range search on a reference table.</p> Zabbix agent mysql-stats[Select_full_range_join,{$PORT}]<p>Update: 60</p>
MySQL: default_storage_engine <p>The default storage engine. This variable sets the storage engine for permanent tables only. To set the storage engine for TEMPORARY tables, set the default_tmp_storage_engine system variable. To see which storage engines are available and enabled, use the SHOW ENGINES statement or query the INFORMATION_SCHEMA ENGINES table. “MySQL Manual: MyISAM Startup Options MySQL Manual: mysqld Command Options”</p> Zabbix agent mysql-stats[default_storage_engine,{$PORT}]<p>Update: 6h</p>
Commands Begin <p>The Com_begin statement counter variables indicate the number of times each begin statement has been executed.</p> Zabbix agent mysql-stats[Com_begin,{$PORT}]<p>Update: 60</p>
Select_range_check <p>The number of joins without keys that check for key usage after each row. If this is not 0, you should carefully check the indexes of your tables.</p> Zabbix agent mysql-stats[Select_range_check,{$PORT}]<p>Update: 60</p>
Commands Replace <p>The Com_replace statement counter variables indicate the number of times each replace statement has been executed.</p> Zabbix agent mysql-stats[Com_replace,{$PORT}]<p>Update: 60</p>
Threads created <p>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.</p> Zabbix agent mysql-stats[Threads_created,{$PORT}]<p>Update: 60</p>
Binlog_stmt_cache_use <p>The number of nontransactional statements that used the binary log statement cache.</p> Zabbix agent mysql-stats[Binlog_stmt_cache_use,{$PORT}]<p>Update: 60</p>
Open_files <p>The number of files that are open. This count includes regular files opened by the server.</p> Zabbix agent mysql-stats[Open_files,{$PORT}]<p>Update: 60</p>
Table_locks_immediate <p>The number of times that a request for a table lock could be granted immediately.</p> Zabbix agent mysql-stats[Table_locks_immediate,{$PORT}]<p>Update: 60</p>
Handler_update <p>The number of times that rows have been updated from tables per second.</p> Zabbix agent mysql-stats[Handler_update,{$PORT}]<p>Update: 60</p>
MySQL: event_scheduler <p>“The Event Scheduler is a very useful feature when enabled. It is a framework for executing SQL commands at specific times or at regular intervals. Conceptually, it is similar to the idea of the Unix crontab (also known as a ““cron job””) or the Windows Task Scheduler. The basics of its architecture are simple. An event is a stored routine with a starting date and time, and a recurring tag. Once defined and activated, it will run when requested. Unlike triggers, events are not linked to specific table operations, but to dates and times. Using the event scheduler, the database administrator can perform recurring events with minimal hassle. Common uses are the cleanup of obsolete data, the creation of summary tables for statistics, and monitoring of server performance and usage.” Default Value OFF Valid Values ON OFF DISABLED Links: “MySQL Manual: Using the Event Scheduler MySQL Manual: Event Scheduler Overview”</p> Zabbix agent mysql-stats[event_scheduler,{$PORT}]<p>Update: 5m</p>
Qcache inserts <p>The number of queries added to the query cache.</p> Zabbix agent mysql-stats[Qcache_inserts,{$PORT}]<p>Update: 60</p>
Handler_read_next <p>The number of requests to read the next row in key order. </p> Zabbix agent mysql-stats[Handler_read_next,{$PORT}]<p>Update: 60</p>
Connection_errors_select <p>The number of errors that occurred during calls to select() or poll() on the listening port. (Failure of this operation does not necessarily means a client connection was rejected.) Note These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_select,{$PORT}]<p>Update: 60</p>
Threads connected <p>This variable indicates the total number of clients that have currently open connections to the server. It provides real-time information on how many clients are currently connected to the server. This can help in traffic analysis or in deciding the best time for a server re-start.</p> Zabbix agent mysql-stats[Threads_connected,{$PORT}]<p>Update: 60</p>
Queries <p>The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.</p> Zabbix agent mysql-stats[Queries,{$PORT}]<p>Update: 60</p>
Key reads requests <p>The number of requests to read a key block from the MyISAM key cache.</p> Zabbix agent mysql-stats[Key_read_requests,{$PORT}]<p>Update: 60</p>
Commands Commit <p>The Com_commi statement counter variables indicate the number of times each commit statement has been executed.</p> Zabbix agent mysql-stats[Com_commit,{$PORT}]<p>Update: 60</p>
Commands Create Table <p>The Com_create_table statement counter variables indicate the number of times each create_table statement has been executed.</p> Zabbix agent mysql-stats[Com_create_table,{$PORT}]<p>Update: 60</p>
MySQL: Com_grant <p>Indicate a grant statement “MySQL Knowledge Base: What are some tips on administering users? MySQL Manual: Privileges Provided by MySQL”</p> Zabbix agent mysql-stats[Com_grant,{$PORT}]<p>Update: 5m</p>
Connection_errors_max_connections <p>The number of connections refused because the server max_connections limit was reached. Note These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_max_connections,{$PORT}]<p>Update: 60</p>
MySQL: query_cache_type <p>Set the query cache type. Setting the GLOBAL value sets the type for all clients that connect thereafter. Individual clients can set the SESSION value to affect their own use of the query cache. Possible values are shown in the following table. Option Description 0 or OFF Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0. 1 or ON Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE. 2 or DEMAND Cache results only for cacheable queries that begin with SELECT SQL_CACHE. This variable defaults to OFF. If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution. Links: “MySQL Manual: The Query Cache MySQL Manual: Query Cache Configuration MySQL Manual: Query Cache Status and Maintenance”</p> Zabbix agent mysql-stats[query_cache_type,{$PORT}]<p>Update: 5m</p>
Handler_read_first <p>The number of times the first entry in an index was read. If this value is high, it suggests that the server is doing a lot of full index scans; If MySQL is frequently accessing the first row of a table index, it suggests that it is performing a sequential scan of the entire index. This indicates that the corresponding table is not properly indexed.</p> Zabbix agent mysql-stats[Handler_read_first,{$PORT}]<p>Update: 60</p>
Aborted connections <p>The number of failed attempts to connect to the MySQL server. If this counter is increasing, your clients are trying and failing to connect to the database. Investigate the source of the problem with fine-grained connection metrics such as Connection_errors_max_connections and Connection_errors_internal.</p> Zabbix agent mysql-stats[Aborted_connects,{$PORT}]<p>Update: 60</p>
Incoming traffic <p>Incoming bytes per second</p> Zabbix agent mysql-stats[Bytes_received,{$PORT}]<p>Update: 60</p>
Outcoming traffic <p>Outcoming bytes per second</p> Zabbix agent mysql-stats[Bytes_sent,{$PORT}]<p>Update: 60</p>
Table_open_cache_hits <p>The number of hits for open tables cache lookups.</p> Zabbix agent mysql-stats[Table_open_cache_hits,{$PORT}]<p>Update: 60</p>
MySQL: Com_prepare_sql <p>“Prepared statements may increase performance in applications that execute similar statements more than once, primarily because the query is parsed only once. Prepared statements can also reduce network traffic because it is only necessary to send the data for the parameters for each execution rather than the whole statement. However, prepared statements take time to prepare and consume memory in the MySQL server until they are closed, so it is important to use them properly. If you are only executing a statement a few times, the overhead of creating a prepared statement may not be worthwhile.”</p> Zabbix agent mysql-stats[Com_prepare_sql,{$PORT}]<p>Update: 5m</p>
Key writes requests <p>The number of requests to write a key block to the MyISAM key cache.</p> Zabbix agent mysql-stats[Key_write_requests,{$PORT}]<p>Update: 60</p>
Created tmp tables on disk <p>The number of internal on-disk temporary tables created by the server while executing statements. Accessing tables on disk is typically slower than accessing the same tables in memory. So queries that use the CREATE TEMPORARY TABLE syntax are likely to be slow when this value is high. Must be ass low ass possible. Best 0 If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values. If Created_tmp_disk_tables is large, you may want to increase the tmp_table_size or max_heap_table_size value to lessen the likelihood that internal temporary tables in memory will be converted to on-disk tables. You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.</p> Zabbix agent mysql-stats[Created_tmp_disk_tables,{$PORT}]<p>Update: 60</p>
Connection_errors_accept <p>The number of errors that occurred during calls to accept() on the listening port. These variables provide information about errors that occur during the client connection process. They are global only and represent error counts aggregated across connections from all hosts. These variables track errors not accounted for by the host cache, such as errors that are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).</p> Zabbix agent mysql-stats[Connection_errors_accept,{$PORT}]<p>Update: 60</p>
MySQL: tmp_table_size <p>The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables. Default: 16 Mb The actual limit is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk temporary table. As of MySQL 5.7.5, the internal_tmp_disk_storage_engine option defines the storage engine used for on-disk temporary tables. Prior to MySQL 5.7.5, the MyISAM storage engine is used. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory. You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables. Links: MySQL Manual: How MySQL Uses Internal Temporary Tables MySQL Manual: System Variables MySQL Performance Blog: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE</p> Zabbix agent mysql-stats[tmp_table_size,{$PORT}]<p>Update: 6h</p>
Key_blocks_unused <p>The number of unused blocks in the MyISAM key cache. You can use this value to determine how much of the key cache is in use; see the discussion of key_buffer_size in Section 5.1.5, “Server System Variables”.</p> Zabbix agent mysql-stats[Key_blocks_unused,{$PORT}]<p>Update: 60</p>
Handler_write <p>The number of requests to insert a row in a table per second.</p> Zabbix agent mysql-stats[Handler_write,{$PORT}]<p>Update: 60</p>
Slow_launch_threads <p>The number of threads that have taken more than slow_launch_time seconds to create.</p> Zabbix agent mysql-stats[Slow_launch_threads,{$PORT}]<p>Update: 60</p>
Select_range <p>The number of joins that used ranges on the first table. This is normally not a critical issue even if the value is quite large.</p> Zabbix agent mysql-stats[Select_range,{$PORT}]<p>Update: 60</p>
Commands Select <p>Count select commands per second. Used to see the read throughput on database</p> Zabbix agent mysql-stats[Com_select,{$PORT}]<p>Update: 60</p>
Threads running <p>The number of threads that are not sleeping. Is the number of queries running concurrently and fight between to complete in time</p> Zabbix agent mysql-stats[Threads_running,{$PORT}]<p>Update: 60</p>
Handler_read_key <p>The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.</p> Zabbix agent mysql-stats[Handler_read_key,{$PORT}]<p>Update: 60</p>
Opened_table_definitions <p>The number of .frm files that have been cached.</p> Zabbix agent mysql-stats[Opened_table_definitions,{$PORT}]<p>Update: 60</p>
MySQL: concurrent_insert <p>Default: AUTO If AUTO (the default), MySQL permits INSERT and SELECT statements to run concurrently for MyISAM tables that have no free blocks in the middle of the data file. If you start mysqld with –skip-new, this variable is set to NEVER. This variable can take the values shown in the following table. The variable can be assigned using either the name values or corresponding integer values. Value Description NEVER (or 0) Disables concurrent inserts AUTO (or 1) (Default) Enables concurrent insert for MyISAM tables that do not have holes ALWAYS (or 2) Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.</p> Zabbix agent mysql-stats[concurrent_insert,{$PORT}]<p>Update: 6h</p>
MySQL: old_passwords <p>This variable controls the password hashing method used by the PASSWORD() function. It also influences password hashing performed by CREATE USER and GRANT statements that specify a password using an IDENTIFIED BY clause. The following table shows, for each password hashing method, the permitted value of old_passwords and which authentication plugins use the hashing method. Password Hashing Method old_passwords Value Associated Authentication Plugin MySQL 4.1 native hashing 0 mysql_native_password SHA-256 hashing 2 sha256_password If you set old_passwords=2, follow the instructions for using the sha256_password plugin at Section 6.5.1.4, “SHA-256 Pluggable Authentication”. The server sets the global old_passwords value during startup to be consistent with the password hashing method required by the authentication plugin indicated by the default_authentication_plugin system variable. When a client successfully connects to the server, the server sets the session old_passwords value appropriately for the account authentication method. For example, if the account uses the sha256_password authentication plugin, the server sets old_passwords=2. Default Value 0</p> Zabbix agent mysql-stats[old_passwords,{$PORT}]<p>Update: 6h</p>
MySQL: log_warnings <p>“Error conditions encountered by a MySQL server are always logged in the error log, but warning conditions are only logged if log_warnings is set to a value greater than 0. If warnings are not logged you will not get valuable information about aborted connections and various other communication errors. This is especially important if you use replication so you get more information about what is happening, such as messages about network failures and re-connections. Note that as of MySQL 5.7.2, the log_error_verbosity system variable is preferred over, and should be used instead of, log_warnings. WARNING: One of the system variables used in this advisor (log_warnings) is deprecated and has been removed in MySQL Server 8.0. Please plan ahead accordingly.” Default Value (64-bit platforms) 2 Links: “MySQL Manual: The Error Log MySQL Manual: Server Command Options - log_warnings MySQL Manual: Server Command Options - log_error_verbosity MySQL Manual: Binary Logging Options and Variables - log_statements_unsafe_for_binlog MySQL Manual: Communication Errors and Aborted Connections Bug #24761: dropped TCP connections not logged as errors but result in blocked host MySQL Manual: Usage of Row-based Logging and Row-Based Replication”</p> Zabbix agent mysql-stats[log_warnings,{$PORT}]<p>Update: 6h</p>
Qcache not cached <p>The number of noncached queries (not cacheable, or not cached due to the query_cache_type setting).</p> Zabbix agent mysql-stats[Qcache_not_cached,{$PORT}]<p>Update: 60</p>
Qcache free memory <p>The amount of free memory for the query cache.</p> Zabbix agent mysql-stats[Qcache_free_memory,{$PORT}]<p>Update: 60</p>
Handler_savepoint_rollback <p>The number of requests for a storage engine to roll back to a savepoint.</p> Zabbix agent mysql-stats[Handler_savepoint_rollback,{$PORT}]<p>Update: 60</p>
Handler_read_rnd_next <p>The number of requests to read the next row in the data file. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.</p> Zabbix agent mysql-stats[Handler_read_rnd_next,{$PORT}]<p>Update: 60</p>
Handler_read_last <p>The number of requests to read the last key in an index. </p> Zabbix agent mysql-stats[Handler_read_last,{$PORT}]<p>Update: 60</p>
Open_tables <p>The number of tables that are open. This value is best analyzed in combination with the size of the table cache. If this value is low and the table_cache value is high, it’s probably safe to reduce the cache size without affecting performance. On the other hand, if this value is high and close to the table_cache value, there is benefit in increasing the size of the table cache.</p> Zabbix agent mysql-stats[Open_tables,{$PORT}]<p>Update: 60</p>
Sort_rows <p>The number of sorted rows.</p> Zabbix agent mysql-stats[Sort_rows,{$PORT}]<p>Update: 60</p>
Qcache hits <p>The number of query cache hits.</p> Zabbix agent mysql-stats[Qcache_hits,{$PORT}]<p>Update: 60</p>
MySQL: Com_stmt_prepare <p>Com_stmt_reprepare indicates the number of times statements were automatically reprepared by the server after metadata changes to tables or views referred to by the statement. A reprepare operation increments Com_stmt_reprepare, and also Com_stmt_prepare. Prepared statements may increase performance in applications that execute similar statements more than once, primarily because the query is parsed only once. Prepared statements can also reduce network traffic because it is only necessary to send the data for the parameters for each execution rather than the whole statement.</p> Zabbix agent mysql-stats[Com_stmt_prepare,{$PORT}]<p>Update: 5m</p>
Qcache_total_blocks <p>The total number of blocks in the query cache.</p> Zabbix agent mysql-stats[Qcache_total_blocks,{$PORT}]<p>Update: 60</p>
Key writes to disk <p>The number of physical writes of a key block from the MyISAM key cache to disk.</p> Zabbix agent mysql-stats[Key_writes,{$PORT}]<p>Update: 60</p>
Handler_delete <p>The number of times that rows have been deleted from tables per second.</p> Zabbix agent mysql-stats[Handler_delete,{$PORT}]<p>Update: 60</p>
Qcache queries in cache <p>The number of queries who are in the query cache.</p> Zabbix agent mysql-stats[Qcache_queries_in_cache,{$PORT}]<p>Update: 60</p>
MySQL: myisam_recover_options <p>The myisam-recover-options option (named myisam-recover before MySQL 5.5.3) enables automatic MyISAM crash recovery should a MyISAM table become corrupt for some reason. If this option is not set, then a table will be “Marked as crashed” if it becomes corrupt, and no sessions will be able to SELECT from it, or perform any sort of DML against it. MySQL Manual: MyISAM Startup Options MySQL Manual: mysqld Command Options</p> Zabbix agent mysql-stats[myisam_recover_options,{$PORT}]<p>Update: 6h</p>
Binlog_stmt_cache_disk_use <p>The number of nontransaction statements that used the binary log statement cache but that exceeded the value of binlog_stmt_cache_size and used a temporary file to store those statements.</p> Zabbix agent mysql-stats[Binlog_stmt_cache_disk_use,{$PORT}]<p>Update: 60</p>
Max used connections <p>The maximum number of connections that have been in use simultaneously since the server started. This value provides a benchmark to help you decide the maximum number of connections your server should support. It can also help in traffic analysis. (max_used_connections / max_connections) indicates if you could run out soon of connection slots. Alarm if connections usage is > 85%.</p> Zabbix agent mysql-stats[Max_used_connections,{$PORT}]<p>Update: 60</p>
Table_locks_waited <p>The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.</p> Zabbix agent mysql-stats[Table_locks_waited,{$PORT}]<p>Update: 60</p>
Questions <p>The number of statements executed by the server. As of MySQL 5.0.72, this includes only statements sent to the server by clients and no longer includes statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands. MySQL increments the questions and queries counters before executing the query</p> Zabbix agent mysql-stats[Questions,{$PORT}]<p>Update: 60</p>
MySQL: thread_handling <p>The thread-handling model used by the server for connection threads. The permissible values are no-threads (the server uses a single thread to handle one connection) and one-thread-per-connection (the server uses one thread to handle each client connection). no-threads is useful for debugging under Linux; Default Value: one-thread-per-connection Valid Values (<= 5.7.8) no-threads one-thread-per-connection Links: MySQL Manual: MySQL Enterprise Thread Pool MySQL Manual: Thread Pool Installation</p> Zabbix agent mysql-stats[thread_handling,{$PORT}]<p>Update: 5m</p>
Slow queries <p>The number of queries that have taken more than long_query_time seconds. This counter increments regardless of whether the slow query log is enabled. A high value indicates that many queries are not being optimally executed. A necessary next step would be to examine the slow query log and identify these slow queries for optimization.</p> Zabbix agent mysql-stats[Slow_queries,{$PORT}]<p>Update: 60</p>
Handler_read_prev <p>The number of requests to read the previous row in key order.</p> Zabbix agent mysql-stats[Handler_read_prev,{$PORT}]<p>Update: 60</p>
Qcache_free_blocks <p>The number of free memory blocks in the query cache.</p> Zabbix agent mysql-stats[Qcache_free_blocks,{$PORT}]<p>Update: 60</p>
MySQL: Com_stmt_close <p>“Prepared statements may increase performance in applications that execute similar statements more than once, primarily because the query is parsed only once. Prepared statements can also reduce network traffic because it is only necessary to send the data for the parameters for each execution rather than the whole statement. However, prepared statements take time to prepare and consume memory in the MySQL server until they are closed, so it is important to use them properly. If you are not closing prepared statements when you are done with them, you are needlessly tying up memory that could be put to use in other ways.”</p> Zabbix agent mysql-stats[Com_stmt_close,{$PORT}]<p>Update: 5m</p>
MySQL: Com_execute_sql <p>“Prepared statements may increase performance in applications that execute similar statements more than once, primarily because the query is parsed only once. Prepared statements can also reduce network traffic because it is only necessary to send the data for the parameters for each execution rather than the whole statement. However, prepared statements take time to prepare and consume memory in the MySQL server until they are closed, so it is important to use them properly. If you are only executing a statement a few times, the overhead of creating a prepared statement may not be worthwhile.”</p> Zabbix agent mysql-stats[Com_execute_sql,{$PORT}]<p>Update: 5m</p>
MySQL: have_symlink <p>YES if symbolic link support is enabled, NO if not. This is required on Unix for support of the DATA DIRECTORY and INDEX DIRECTORY table options. If the server is started with the –skip-symbolic-links option, the value is DISABLED. This variable has no meaning on Windows. Links: “MySQL Manual: Making MySQL Secure Against Attackers MySQL Manual: Using Symbolic Links MySQL Manual: Disk Issues MySQL Manual: CREATE TABLE Syntax”</p> Zabbix agent mysql-stats[have_symlink,{$PORT}]<p>Update: 6h</p>
Commands Update <p>Count update commands per second. Used to see the writes on database</p> Zabbix agent mysql-stats[Com_update,{$PORT}]<p>Update: 60</p>

Triggers

There are no triggers in this template.