An mysql template create for halley.it
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:
Create C:\script
Copy in this folder the script mysql_stats.py
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
Create a different host for every MySQL instance and specify in host Macros the MySQL port {$PORT}
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
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:
Specify port in host macros
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
Tudor Ticau
There are no macros links in this template.
There are no template links in this template.
There are no discovery rules in this template.
| 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> |
There are no triggers in this template.
An mysql template create for halley.it
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:
Create C:\script
Copy in this folder the script mysql_stats.py
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
Create a different host for every MySQL instance and specify in host Macros the MySQL port {$PORT}
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
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:
Specify port in host macros
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
Tudor Ticau
There are no macros links in this template.
There are no template links in this template.
There are no discovery rules in this template.
| 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> |
There are no triggers in this template.
An mysql template create for halley.it
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:
Create C:\script
Copy in this folder the script mysql_stats.py
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
Create a different host for every MySQL instance and specify in host Macros the MySQL port {$PORT}
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
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:
Specify port in host macros
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
Tudor Ticau
There are no macros links in this template.
There are no template links in this template.
There are no discovery rules in this template.
| 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> |
There are no triggers in this template.