zabbix_export: version: '6.0' date: '2021-11-21T21:37:18Z' groups: - uuid: 2ad896345beb4c1a8ba349313af7af90 name: 'Halley MySQL Template' - uuid: 34e1e88844bd4a6786b5c9dd2e319f72 name: 'Halley passive agent' - uuid: cdce88f4196c45bfa225b57ddba7445e name: 'Halley template' templates: - uuid: ad37f470e4e94fa4a1791777b0dfef9f template: 'Halley MySQL Python server mi passive' name: 'Halley MySQL Python server mi passive' description: | ## Description An mysql template create for halley.it ## Overview Here is my template for monitoring **MySQL on a Windows server with multiple instances**. The logic for this monitoring is: Script will take 2 parameters: variable ($1) and port ($2). Variable 1 will be the word for what script will looking for in script results file. The first zabbix request will generate a file : mysql\_results\_$PORT.txt. All next requests in 55 sec will take the values from this file, not from mysql. If the file is over 55 sec, file is rewriten. This assure to have 3 db request for all 117 items **Steps to install:** 1. Create C:\script 2. Copy in this folder the script mysql\_stats.py 3. in zabbix agent add at UserParameters UserParameter=mysql-stats[*],c:\script\mysql\_stats.py "$1" "$2" # user parameter for show extended status - no grants needed UserParameter=mysql.ping[*],mysqladmin -u -p --host=127.0.0.1 -P"$2" ping # -P"$2" will tahe the port number, who is the second variable from request 4. Create a different host for every MySQL instance and specify in host Macros the MySQL port {$PORT} 5. 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 6. Check the data: a0) from client command line run c:\script\mysql\_stats.py Uptime Port a) from zabbix server with command zabbix\_get -s host\_ip -k "mysql-stats[Uptime,$port]" a1) from zabbix server with command zabbix\_get -s host\_ip -k "mysql-stats[log\_bin,$port]" b) Look in Latest data Don't forget: 1. Specify port in host macros 2. Install Python on server and when will install select custom install and check for all users All files are on https://1drv.ms/f/s!Ag6IN4i1M9MuhXQZZpQ5dn2t4MCy **P.S.** This template include description with advices for items and triggers. I spend over a month to get best practices from MySQL Enterprise Monitor, another mysql templates or just mysql documentation. A feedback from you will be nice ## Author Tudor Ticau groups: - name: 'Halley MySQL Template' - name: 'Halley passive agent' - name: 'Halley template' items: - uuid: 424b8b94af1f4382804860233659088f name: Aborted_clients key: 'mysql-stats[Aborted_clients,{$PORT}]' delay: '60' history: 7d units: con/s description: | 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. preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 2bad714aae9940fb824af043556e821c name: 'Aborted connections' key: 'mysql-stats[Aborted_connects,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: con/s description: | 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. preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL triggers: - uuid: 1605ba03bf934663b5926e990851368e expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Aborted_connects,{$PORT}])>1' name: 'MySQL: Many attempts to connect' priority: INFO manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: 6d99e78036594c3094e710209833270e name: Binlog_cache_disk_use key: 'mysql-stats[Binlog_cache_disk_use,{$PORT}]' delay: '60' history: 7d units: tran/sec description: '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.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: dfe8c35343d84bc09e5aa0c61512d5fb name: Binlog_cache_use key: 'mysql-stats[Binlog_cache_use,{$PORT}]' delay: '60' history: 7d description: 'The number of transactions that used the binary log cache' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 94887bafa69b41fb815daa9c6a7651f2 name: Binlog_stmt_cache_disk_use key: 'mysql-stats[Binlog_stmt_cache_disk_use,{$PORT}]' delay: '60' history: 7d description: '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.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 6f4f83d093e54ba7af7dedcb35969f62 name: Binlog_stmt_cache_use key: 'mysql-stats[Binlog_stmt_cache_use,{$PORT}]' delay: '60' history: 7d description: 'The number of nontransactional statements that used the binary log statement cache.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: f6b3de6733334755850192e0daf49629 name: 'Incoming traffic' key: 'mysql-stats[Bytes_received,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: b/s description: 'Incoming bytes per second' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: bff5f771cc4a4ccaad046035ce90c349 name: 'Outcoming traffic' key: 'mysql-stats[Bytes_sent,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: b/s description: 'Outcoming bytes per second' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 787cb06aa7374616bc7ee2acbe6d8c02 name: 'Commands Begin' key: 'mysql-stats[Com_begin,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: 'The Com_begin statement counter variables indicate the number of times each begin statement has been executed.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: aedb352a5d134568bbd8bc1f61426310 name: 'Commands Commit' key: 'mysql-stats[Com_commit,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: 'The Com_commi statement counter variables indicate the number of times each commit statement has been executed.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 7abc01ec9c8c49f58aa6f645329485f5 name: 'Commands Create Table' key: 'mysql-stats[Com_create_table,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: 'The Com_create_table statement counter variables indicate the number of times each create_table statement has been executed.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 74f5f564cd2e4fc98da630169f2e357a name: 'Commands Delete' key: 'mysql-stats[Com_delete,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: com/s description: 'Count delete commands per second. Used to see the writes on database' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: a3e7d44926274d4da58acc254b9d5666 name: 'MySQL: Com_execute_sql' key: 'mysql-stats[Com_execute_sql,{$PORT}]' delay: 5m history: 7d description: | "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." preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: cc34cfceae534e34afe9f89f72b902ee name: 'MySQL: Com_grant' key: 'mysql-stats[Com_grant,{$PORT}]' delay: 5m history: 7d description: | Indicate a grant statement "MySQL Knowledge Base: What are some tips on administering users? MySQL Manual: Privileges Provided by MySQL" tags: - tag: Application value: MySQL triggers: - uuid: f5dcc5acdcba4662a883e5aa8fb54dce expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Com_grant,{$PORT}])>1' name: 'MySQL: Privilege Alterations Detected: Privileges Granted' status: DISABLED priority: AVERAGE description: 'For development environments, changes to database security privileges may be a normal occurrence, but for production environments it is wise to know when any security changes occur with respect to database privileges, and to ensure that those changes are authorized and required.' manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: 638d887eba9c47f593fccef6d57707aa name: 'Commands Insert' key: 'mysql-stats[Com_insert,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: com/s description: 'Count insert commands per second. Used to see the writes on database' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 36438a038e67435082de2b28b6688e24 name: 'MySQL: Com_prepare_sql' key: 'mysql-stats[Com_prepare_sql,{$PORT}]' delay: 5m history: 7d description: | "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." preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 0ebc30699cf04512891120459e4456e4 name: 'Commands Replace' key: 'mysql-stats[Com_replace,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: 'The Com_replace statement counter variables indicate the number of times each replace statement has been executed.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 11a65522a12e45b0a3421e13a0c9e172 name: 'MySQL: Com_revoke' key: 'mysql-stats[Com_revoke,{$PORT}]' delay: 5m history: 7d description: | Indicate a revoke statement "MySQL Knowledge Base: What are some tips on administering users? MySQL Manual: Privileges Provided by MySQL" tags: - tag: Application value: MySQL triggers: - uuid: ca0f11b4814a45ce8379587f7af97f3b expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Com_revoke,{$PORT}])>0' name: 'MySQL: Privilege Alterations Detected: Privileges Revoked' status: DISABLED priority: AVERAGE description: 'For development environments, changes to database security privileges may be a normal occurrence, but for production environments it is wise to know when any security changes occur with respect to database privileges, and to ensure that those changes are authorized and required.' manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: c037232ac4cb4fa29a471e7a2f2d9594 name: 'Commands Select' key: 'mysql-stats[Com_select,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: com/s description: | Count select commands per second. Used to see the read throughput on database preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 58ef18a3449743d88d30eb7040230ce7 name: 'MySQL: Com_stmt_close' key: 'mysql-stats[Com_stmt_close,{$PORT}]' delay: 5m history: 7d description: | "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." preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 3692ca14da734cf3acc258433b99928d name: 'MySQL: Com_stmt_prepare' key: 'mysql-stats[Com_stmt_prepare,{$PORT}]' delay: 5m history: 7d description: | 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. preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 97e1951293744d7a8984608d392f60aa name: 'Commands Update' key: 'mysql-stats[Com_update,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: com/s description: | Count update commands per second. Used to see the writes on database preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: b7b311bf7cfd46509fbcd23f82bd987f name: 'MySQL: concurrent_insert' key: 'mysql-stats[concurrent_insert,{$PORT}]' delay: 6h history: 7d trends: '0' value_type: CHAR description: | 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. tags: - tag: Application value: MySQL - uuid: 3ebeb53b347e4dd5a112fc74e4621dc9 name: Connections key: 'mysql-stats[Connections,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: con/s description: 'The number of connection attempts (successful or not) to the MySQL server per second.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: e8eb3bb0a6c6436fbed3f38655c0b89b name: Connection_errors_accept key: 'mysql-stats[Connection_errors_accept,{$PORT}]' delay: '60' history: 7d units: err description: | 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). preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 9a8deb4335e14d5abe0b2b7da5f34792 name: Connection_errors_internal key: 'mysql-stats[Connection_errors_internal,{$PORT}]' delay: '60' history: 7d units: con/s description: | 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. preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: ef40cbfe453349299649e9b0b9802a08 name: Connection_errors_max_connections key: 'mysql-stats[Connection_errors_max_connections,{$PORT}]' delay: '60' history: 7d units: con description: | 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). preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: f8faa345c7704403bfd23c571acbceaf name: Connection_errors_peer_address key: 'mysql-stats[Connection_errors_peer_address,{$PORT}]' delay: '60' history: 7d units: err description: | 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). preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 05667bbb2bd248ecaaf9ddd5c38b3edc name: Connection_errors_select key: 'mysql-stats[Connection_errors_select,{$PORT}]' delay: '60' history: 7d units: err description: | 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). preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 470faeef03824fad90150993ed079858 name: Connection_errors_tcpwrap key: 'mysql-stats[Connection_errors_tcpwrap,{$PORT}]' delay: '60' history: 7d units: con description: | 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). preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: cf9a01bfc5da452897f078d01873094d name: 'Created tmp tables on disk' key: 'mysql-stats[Created_tmp_disk_tables,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: | 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. preprocessing: - type: SIMPLE_CHANGE parameters: - '' tags: - tag: Application value: MySQL triggers: - uuid: 095068f478ed4b8fab4f1b8b214fcf50 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Created_tmp_disk_tables,{$PORT}])>700' name: 'MySQL: Over 700 temporary tables on disk' priority: WARNING tags: - tag: Value value: '{ITEM.VALUE}' - uuid: dd106e2a178149068ea9511b73edd44c name: 'Created_tmp_files in memory' key: 'mysql-stats[Created_tmp_files,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: "How many temporary files\_mysqld\_has created." preprocessing: - type: SIMPLE_CHANGE parameters: - '' tags: - tag: Application value: MySQL - uuid: 7d8d1fb9290649c796cce2546582f603 name: 'Created_tmp_tables on memory' key: 'mysql-stats[Created_tmp_tables,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: '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.' preprocessing: - type: SIMPLE_CHANGE parameters: - '' tags: - tag: Application value: MySQL - uuid: f71ca45848084a65911b680420a54c44 name: 'MySQL: default_storage_engine' key: 'mysql-stats[default_storage_engine,{$PORT}]' delay: 6h history: 7d trends: '0' value_type: CHAR description: | 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" tags: - tag: Application value: MySQL - uuid: e051cc0977a0491f9c120bf61bb45b04 name: 'MySQL: event_scheduler' key: 'mysql-stats[event_scheduler,{$PORT}]' delay: 5m history: 7d trends: '0' value_type: CHAR description: | "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" tags: - tag: Application value: MySQL triggers: - uuid: 87ba257394454ff1925b466da9f8a4b1 expression: 'find(/Halley MySQL Python server mi passive/mysql-stats[event_scheduler,{$PORT}],,"regexp","ON")<>1' name: 'MySQL: Event Scheduler Disabled' status: DISABLED priority: WARNING description: | "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." Advice: "Enable the Event Scheduler and use it to automate recurring events. Add the line event_scheduler=1 to the [mysqld] section of your my.cnf/my.ini file so the variable is set properly when the server is restarted. Recommended Action SET GLOBAL event_scheduler = ON;" manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: e77de84d6c3e4460a21ad17bab084eba name: 'MySQL: expire_logs_days' key: 'mysql-stats[expire_logs_days,{$PORT}]' delay: 6h history: 7d description: | 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?" tags: - tag: Application value: MySQL triggers: - uuid: 66cac8bbe3ee484f9413746c5f26af16 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[expire_logs_days,{$PORT}])>0 and last(/Halley MySQL Python server mi passive/mysql-stats[expire_logs_days,{$PORT}])<7' name: 'MySQL: Binary Logs Automatically Removed Too Quickly' priority: INFO description: | "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." manual_close: 'YES' tags: - tag: Value value: '<7' - uuid: 305b7cc6c8e34c1c850aa97e58705f46 name: 'MySQL: flush_time' key: 'mysql-stats[flush_time,{$PORT}]' delay: 6h history: 7d units: s description: | 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. tags: - tag: Application value: MySQL - uuid: 5ed08fed6b6f4b18b8bcfef8b36c37f1 name: Handler_commit key: 'mysql-stats[Handler_commit,{$PORT}]' delay: '60' history: 7d description: 'The number of internal COMMIT statements.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 1a92422e326c44ae848c2df4d85b5062 name: Handler_delete key: 'mysql-stats[Handler_delete,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: 'The number of times that rows have been deleted from tables per second.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: e853e96ba8e04f31b89ad57436031641 name: Handler_read_first key: 'mysql-stats[Handler_read_first,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: | 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. preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL triggers: - uuid: b81fb4aa1df040c7b71d9280a97d7fd0 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_first,{$PORT}])>100' name: 'MySQL: Over 100 first row read requests' priority: WARNING description: '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.' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: a527ee7d4e49408984cfc6587f2f93cb name: Handler_read_key key: 'mysql-stats[Handler_read_key,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: '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.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: b676bf2cdc694e9cb1c7ae90da184227 name: Handler_read_last key: 'mysql-stats[Handler_read_last,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: "The number of requests to read the last key in an index.\_" preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: a818a7f7fd124c119424bbeb1d800597 name: Handler_read_next key: 'mysql-stats[Handler_read_next,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: "The number of requests to read the next row in key order.\_" preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 07f5e62ef3f9486da9947c9d2f5e1f3c name: Handler_read_prev key: 'mysql-stats[Handler_read_prev,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: 'The number of requests to read the previous row in key order.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 62029688279a491883c48bf3170df0d8 name: Handler_read_rnd key: 'mysql-stats[Handler_read_rnd,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: '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.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 0a32c37c62be4a2a8bfd2ae99357a95b name: Handler_read_rnd_next key: 'mysql-stats[Handler_read_rnd_next,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: '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.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 6a76fa3ed3994b1caea8336bd5e04cdd name: Handler_rollback key: 'mysql-stats[Handler_rollback,{$PORT}]' delay: '60' history: 7d description: 'The number of requests for a storage engine to perform a rollback operation.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 849eddb2575e4854a5568fd36a7bd4e7 name: Handler_savepoint key: 'mysql-stats[Handler_savepoint,{$PORT}]' delay: '60' history: 7d description: 'The number of requests for a storage engine to place a savepoint.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 9d497bb8709046d8be6c5f3dab5f7138 name: Handler_savepoint_rollback key: 'mysql-stats[Handler_savepoint_rollback,{$PORT}]' delay: '60' history: 7d description: 'The number of requests for a storage engine to roll back to a savepoint.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 245c0e71453b46be89db0a063fa04726 name: Handler_update key: 'mysql-stats[Handler_update,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: 'The number of times that rows have been updated from tables per second.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 6f0740eab244495793af1e040d01c6d9 name: Handler_write key: 'mysql-stats[Handler_write,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: 'The number of requests to insert a row in a table per second.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: a8e6df3e931e4b7daa3cf94240447def name: 'MySQL: have_symlink' key: 'mysql-stats[have_symlink,{$PORT}]' delay: 6h history: 7d trends: '0' value_type: CHAR description: | 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" tags: - tag: Application value: MySQL triggers: - uuid: 26256d9962e04bc799aeb188f25040b1 expression: 'find(/Halley MySQL Python server mi passive/mysql-stats[have_symlink,{$PORT}],,"regexp","YES")=1' name: 'MySQL: Symlinks Are Enabled' status: DISABLED priority: WARNING description: | "You can move tables and databases from the database directory to other locations and replace them with symbolic links to the new locations. You might want to do this, for example, to move a database to a file system with more free space or to increase the speed of your system by spreading your tables to different disks. However, symlinks can compromise security. This is especially important if you run mysqld as root, because anyone who has write access to the server's data directory could then delete any file in the system!" manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: 79319cb31a39429e90d022099d67ab4a name: Key_blocks_not_flushed key: 'mysql-stats[Key_blocks_not_flushed,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: "The number of key blocks in the\_MyISAM\_key cache that have changed but have not yet been flushed to disk." preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 041a3cb588c941058d28d0e39ecbbcee name: Key_blocks_unused key: 'mysql-stats[Key_blocks_unused,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: "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”." preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 4d3d90daf0dd457eadc8b64ba1ba92c2 name: Key_blocks_used key: 'mysql-stats[Key_blocks_used,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: "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." preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: a45a55caaf6f4254a100a45588cd9098 name: 'Key reads' key: 'mysql-stats[Key_reads,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: | 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. preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 21efda55e98d4ea6a900208b1fa9ef83 name: 'Key reads requests' key: 'mysql-stats[Key_read_requests,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: "The number of requests to read a key block from the\_MyISAM\_key cache." preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: d9693d9de1c94551ab2fc550720adf62 name: 'Key writes to disk' key: 'mysql-stats[Key_writes,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: "The number of physical writes of a key block from the\_MyISAM\_key cache to disk." preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: c9d2d46cbdfb4bb897e675edaff35939 name: 'Key writes requests' key: 'mysql-stats[Key_write_requests,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: "The number of requests to write a key block to the\_MyISAM\_key cache." preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 1ec3c3810775446aa5ae62944e997b4c name: 'MySQL: local_infile' key: 'mysql-stats[local_infile,{$PORT}]' delay: 6h history: 7d trends: '0' value_type: CHAR description: | 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" tags: - tag: Application value: MySQL triggers: - uuid: e793884c388f4321bdee76505891931a expression: 'find(/Halley MySQL Python server mi passive/mysql-stats[local_infile,{$PORT}],,"regexp","ON")=1' name: 'MySQL: LOCAL Option Of LOAD DATA Statement Is Enabled' status: DISABLED priority: AVERAGE description: | "The LOAD DATA statement can load a file that is located on the server host, or it can load a file that is located on the client host when the LOCAL keyword is specified. There are two potential security issues with supporting the LOCAL version of LOAD DATA statements: The transfer of the file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server's choosing rather than the file named by the client in the LOAD DATA statement. Such a server could access any file on the client host to which the client user has read access. In a Web environment where the clients are connecting from a separate web server, a user could use LOAD DATA LOCAL to read any files that the web server process has read access to (assuming that a user could run any statement against the SQL server). In this environment, the client with respect to the MySQL server actually is the web server, not the remote program being run by the user who connects to the web server." Advice: "Start the MySQL Server with the --local-infile option disabled (--local-infile=0), or add ""local-infile = 0"" to your my.cnf/my.ini file. Recommended Action SET GLOBAL local_infile = 0;" manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: 69a637c24ffe4facb87f2536dd1f349a name: 'MySQL: log_bin' key: 'mysql-stats[log_bin,{$PORT}]' delay: 6h history: 7d trends: '0' value_type: CHAR description: | 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? tags: - tag: Application value: MySQL - uuid: 40d52ec63811406796357dd1c7c27396 name: 'MySQL: log_warnings' key: 'mysql-stats[log_warnings,{$PORT}]' delay: 6h history: 7d description: | "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" tags: - tag: Application value: MySQL triggers: - uuid: 9b02f80999b844fc9d1a3eb2bdc47c9c expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[log_warnings,{$PORT}])=0' name: 'MySQL: Warnings Not Being Logged' priority: WARNING description: | "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." manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: aad43819713e4d188b6eb2b32a30fafb name: 'MySQL: lower_case_table_names' key: 'mysql-stats[lower_case_table_names,{$PORT}]' delay: 6h history: 7d description: | 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 tags: - tag: Application value: MySQL triggers: - uuid: 5b3d51f25c7e4585afd2a9fa764fed52 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[lower_case_table_names,{$PORT}])<>1' name: 'MySQL: Database May Not Be Portable Due To Identifier Case Sensitivity' priority: INFO description: | The case sensitivity of the underlying operating system determines the case sensitivity of database and table names. If you are using MySQL on only one platform, you don't normally have to worry about this. However, depending on how you have configured your server you may encounter difficulties if you want to transfer tables between platforms that differ in filesystem case sensitivity. Advice: Set lower_case_table_names=1 in your my.cnf/my.ini file and restart your MySQL server. Note that if you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert your old database and table names to lowercase before restarting mysqld with the new variable setting. manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: 5e2a0ebfa2c8402cbab6dfe6346e46c4 name: 'MySQL: max_heap_table_size' key: 'mysql-stats[max_heap_table_size,{$PORT}]' delay: 6h history: 7d units: B description: | 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 tags: - tag: Application value: MySQL - uuid: f9e8702f809147a19dee57836e0f353c name: 'Max used connections' key: 'mysql-stats[Max_used_connections,{$PORT}]' delay: '60' history: 7d description: | 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%. tags: - tag: Application value: MySQL - uuid: 11a80902dd244b6b8d8a91d6869b7149 name: 'MySQL: myisam_recover_options' key: 'mysql-stats[myisam_recover_options,{$PORT}]' delay: 6h history: 7d trends: '0' value_type: CHAR description: | 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 tags: - tag: Application value: MySQL - uuid: a90e87b9adb64331a450037bde7dfae6 name: 'MySQL: myisam_repair_threads' key: 'mysql-stats[myisam_repair_threads,{$PORT}]' delay: 6h history: 7d description: | 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" tags: - tag: Application value: MySQL triggers: - uuid: bbe3b5c08d61415dbabbe4f61b060b36 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[myisam_repair_threads,{$PORT}])<>1' name: 'MySQL: Multiple Threads Used When Repairing MyISAM Tables' priority: WARNING description: '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.' manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: 095d7f8db94d462597e110c4638321a1 name: 'MySQL: old_passwords' key: 'mysql-stats[old_passwords,{$PORT}]' delay: 6h history: 7d trends: '0' value_type: CHAR description: | 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 tags: - tag: Application value: MySQL triggers: - uuid: 9bc2f84f96af4db4bb38a268808328f6 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[old_passwords,{$PORT}])=1 or find(/Halley MySQL Python server mi passive/mysql-stats[old_passwords,{$PORT}],,"regexp","ON")=1' name: 'MySQL: Insecure Password Generation Option Is Enabled' status: DISABLED priority: AVERAGE description: 'Prior to MySQL 4.1, password hashes computed by the PASSWORD() function were 16 bytes long. As of MySQL 4.1 (and later), PASSWORD() was modified to produce a longer 41-byte hash value to provide enhanced security. In order to allow backward-compatibility with older client programs, you can configure MySQL to generate short (pre-4.1) password hashes for new passwords, however, this is not recommended.' - uuid: bf2ff7ef64d24721b46701ac592057a4 name: Opened_files key: 'mysql-stats[Opened_files,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: '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.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: e7e82a05b3ed4e5ab50e9b3de9a2325d name: Opened_tables key: 'mysql-stats[Opened_tables,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: "The number of tables that have been opened. If\_Opened_tables\_is big, your\_table_open_cache\_value is probably too small." preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 65dd9dde76174a6bb0752cc1b96dbc80 name: Opened_table_definitions key: 'mysql-stats[Opened_table_definitions,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: "The number of\_.frm\_files that have been cached." preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: d95314232c76424388b294a7f5b66acc name: Open_files key: 'mysql-stats[Open_files,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: 'The number of files that are open. This count includes regular files opened by the server.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 201476d2b71246daac6de3f1e19147d5 name: Open_tables key: 'mysql-stats[Open_tables,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: | 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. preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL triggers: - uuid: eb5b630818894c099b83b81d58d57861 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Open_tables,{$PORT}])>2000' name: 'MySQL: Over 2000 Concurrent open tables' priority: AVERAGE description: '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.' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: 87725b0d27ef4ede9c5d86c38d8877a0 name: Open_table_definitions key: 'mysql-stats[Open_table_definitions,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: 'The number of cached .frm files.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: cebf115649c949da882cc4e8f8388df0 name: Qcache_free_blocks key: 'mysql-stats[Qcache_free_blocks,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: b description: 'The number of free memory blocks in the query cache.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: d7efa3583d7e455d98a6d268ade91ae1 name: 'Qcache free memory' key: 'mysql-stats[Qcache_free_memory,{$PORT}]' delay: '60' history: 7d units: b description: 'The amount of free memory for the query cache.' tags: - tag: Application value: MySQL triggers: - uuid: 3ed58049ffce4ae1bb91afa97f913bdf expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_free_memory,{$PORT}])<8M' name: 'MySQL: Lack of available qcache memory' priority: INFO manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: 6e185b2f07e24b3f9e5c7f0a75e01497 name: 'Qcache hits' key: 'mysql-stats[Qcache_hits,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: 'The number of query cache hits.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 8631932cd4504a03b1aac918ed9ad8ba name: 'Qcache inserts' key: 'mysql-stats[Qcache_inserts,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: 'The number of queries added to the query cache.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 376dcaf3a4e34be19cd2be1af7608cfc name: 'Qcache lowmem prunes' key: 'mysql-stats[Qcache_lowmem_prunes,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: 'The number of queries that were deleted from the query cache because of low memory. Could indicate a misconfigured query_cache' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 41349ab9957441b6b7b3daa882e9d7fc name: 'Qcache not cached' key: 'mysql-stats[Qcache_not_cached,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: 'The number of noncached queries (not cacheable, or not cached due to the query_cache_type setting).' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: e64900eaec47417cb062bc9789f04cb6 name: 'Qcache queries in cache' key: 'mysql-stats[Qcache_queries_in_cache,{$PORT}]' delay: '60' history: 7d units: q description: 'The number of queries who are in the query cache.' tags: - tag: Application value: MySQL - uuid: 9d5700c3a8274415870630e72e27d8f5 name: Qcache_total_blocks key: 'mysql-stats[Qcache_total_blocks,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: b description: 'The total number of blocks in the query cache.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 76afbde5f6744541962278867623dc19 name: Queries key: 'mysql-stats[Queries,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: '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.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 6c419da7a05143358c2808ad50d2d1c4 name: 'MySQL: query_cache_size' key: 'mysql-stats[query_cache_size,{$PORT}]' delay: 5m history: 7d units: B description: | 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" preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 85adf06953754410b4f0667a4aafb4a7 name: 'MySQL: query_cache_type' key: 'mysql-stats[query_cache_type,{$PORT}]' delay: 5m history: 7d trends: '0' value_type: CHAR description: | 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" tags: - tag: Application value: MySQL - uuid: 4379227db33c4d2c8fc3a316bca30c06 name: Questions key: 'mysql-stats[Questions,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: | 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 preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 7f621f2d1adb4239a51807a0e484f64e name: 'MySQL: secure_auth' key: 'mysql-stats[secure_auth,{$PORT}]' delay: 6h history: 7d trends: '0' value_type: CHAR description: | 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" tags: - tag: Application value: MySQL triggers: - uuid: a3914575cd104173a2862435960dda07 expression: 'find(/Halley MySQL Python server mi passive/mysql-stats[secure_auth,{$PORT}],,"regexp","ON")<>1' name: 'MySQL: Insecure Password Authentication Option Is Enabled' status: DISABLED priority: AVERAGE description: | "Prior to MySQL 4.1, password hashes computed by the PASSWORD() function were 16 bytes long. As of MySQL 4.1 (and later), PASSWORD() was modified to produce a longer 41-byte hash value to provide enhanced security. However, in order to allow backward-compatibility with user tables that have been migrated from pre-4.1 systems, you can configure MySQL to accept logins for accounts that have password hashes created using the old, less-secure PASSWORD() function, but this is not recommended. WARNING: One of the system variables used in this advisor (secure_auth) is deprecated and has been removed in MySQL Server 8.0. Please plan ahead accordingly." Advice: "Disable authentication for accounts that have old (pre-4.1) password hashes by setting secure_auth=1. You may do this from the command line, but be sure to also set it in your my.cnf/my.ini file so it is set properly when the server is restarted. Note that you should be sure any valid old accounts have new passwords generated before you disable this option. Also note that server-side support for pre-4.1 password hashes was deprecated in MySQL Server 5.6 and was removed in MySQL Server 5.7.5 (and later). For more information, see Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin in the MySQL Manual. Recommended Action SET GLOBAL secure_auth = 1;" manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: 4dd789949f9340e4b8f71a620f883f03 name: Select_full_join key: 'mysql-stats[Select_full_join,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: | 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. preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL triggers: - uuid: 897c84f8c1dc4fc8a24ade3d363625f3 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Select_full_join,{$PORT}])>100' name: 'MySQL: Over 100 full join queries' priority: AVERAGE description: 'A high value indicates that MySQL is being forced to perform full table joins (which are performance-intensive) instead of using indexes. You should carefully check the indexes of your tables.' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: 680d740650734038aafbe5c294ddea9a name: Select_full_range_join key: 'mysql-stats[Select_full_range_join,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: 'The number of joins that used a range search on a reference table.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 1aab90ab3609489fb206f7f2b1e1722c name: Select_range key: 'mysql-stats[Select_range,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: '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.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 56d910077fe24cd18a2c027c212f4c04 name: Select_range_check key: 'mysql-stats[Select_range_check,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: '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.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: db0cf105605544efb5855a9c8fa694ae name: Select_scan key: 'mysql-stats[Select_scan,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: 'The number of joins that did a full scan of the first table.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 20a0e156091e4bb888ee3be3f430eb69 name: Slow_launch_threads key: 'mysql-stats[Slow_launch_threads,{$PORT}]' delay: '60' history: 7d description: 'The number of threads that have taken more than slow_launch_time seconds to create.' tags: - tag: Application value: MySQL - uuid: bbfd21723e4b4e34a503db7923b23009 name: 'Slow queries' key: 'mysql-stats[Slow_queries,{$PORT}]' delay: '60' history: 7d value_type: FLOAT units: q/s description: | 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. preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL triggers: - uuid: be74f41eb78d45feba63e4131683cf7e expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Slow_queries,{$PORT}])>10' name: 'MySQL: You have over 10 slow queries' priority: AVERAGE tags: - tag: Value value: '{ITEM.VALUE}' - uuid: 1ab68c25704346d2b4eafcdb0cb06fe1 name: Sort_merge_passes key: 'mysql-stats[Sort_merge_passes,{$PORT}]' delay: '60' history: 7d description: '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.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 6569b6f914a04169896894826c660db3 name: Sort_range key: 'mysql-stats[Sort_range,{$PORT}]' delay: '60' history: 7d description: 'The number of sorts that were done using ranges.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: fe5b90306f164d04886fdf5b59228272 name: Sort_rows key: 'mysql-stats[Sort_rows,{$PORT}]' delay: '60' history: 7d description: 'The number of sorted rows.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 4394471695d9494e97890ab53c6f8136 name: Sort_scan key: 'mysql-stats[Sort_scan,{$PORT}]' delay: '60' history: 7d description: 'The number of sorts that were done by scanning the table.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: c696939d1bdb412cb9fc1d93ce9ac4e8 name: 'MySQL: sql_mode' key: 'mysql-stats[sql_mode,{$PORT}]' delay: 6h history: 7d trends: '0' value_type: CHAR description: | 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" tags: - tag: Application value: MySQL triggers: - uuid: 41d347b8f91649218a6f784e69f23b8c expression: 'find(/Halley MySQL Python server mi passive/mysql-stats[sql_mode,{$PORT}],,"regexp","")=1' name: 'MySQL: Server-Enforced Data Integrity Checking Disabled' status: DISABLED priority: AVERAGE description: | 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. Advice: "Ensure that the sql_mode variable contains one of the following in order to obtain the highest level of data integrity: TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES. After setting sql_mode to the desired value in your my.cnf/my.ini file, restart your MySQL server." manual_close: 'YES' - uuid: b589eb03b04441879033f3e5147eccb4 name: 'MySQL: sync_binlog' key: 'mysql-stats[sync_binlog,{$PORT}]' delay: 6h history: 7d description: | 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" tags: - tag: Application value: MySQL - uuid: b5af92a545044a3994e0c0f7dd831a04 name: Table_locks_immediate key: 'mysql-stats[Table_locks_immediate,{$PORT}]' delay: '60' history: 7d description: 'The number of times that a request for a table lock could be granted immediately.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 7a6b4bda3b4c40cda521b0d68d407b6e name: Table_locks_waited key: 'mysql-stats[Table_locks_waited,{$PORT}]' delay: '60' history: 7d description: '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.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 2ae0720616d442ceba450e423b11a708 name: Table_open_cache_hits key: 'mysql-stats[Table_open_cache_hits,{$PORT}]' delay: '60' history: 7d description: 'The number of hits for open tables cache lookups.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: d06743221473422880fa5662b38e7d4f name: Table_open_cache_misses key: 'mysql-stats[Table_open_cache_misses,{$PORT}]' delay: '60' history: 7d description: 'The number of misses for open tables cache lookups.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 2a03a61baa7a4e9bae99858ccfe43678 name: Table_open_cache_overflows key: 'mysql-stats[Table_open_cache_overflows,{$PORT}]' delay: '60' history: 7d description: '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.' preprocessing: - type: CHANGE_PER_SECOND parameters: - '' tags: - tag: Application value: MySQL - uuid: 3920fda12401424099fb248365f8ae00 name: 'Threads cached' key: 'mysql-stats[Threads_cached,{$PORT}]' delay: '60' history: 7d description: 'The number of threads in the thread cache.' tags: - tag: Application value: MySQL - uuid: b0f1a17b753d46df9edfba0b95ccff49 name: 'Threads connected' key: 'mysql-stats[Threads_connected,{$PORT}]' delay: '60' history: 7d description: | 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. tags: - tag: Application value: MySQL triggers: - uuid: 45ec57e4ed7e434ca2771f6c85dcc1ca expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Threads_connected,{$PORT}])>1000' name: 'MySQL: Client open connections are over 1000' priority: AVERAGE tags: - tag: Value value: '{ITEM.VALUE}' - uuid: f52e574890bf4390937fd9d4a14f94c1 name: 'Threads created' key: 'mysql-stats[Threads_created,{$PORT}]' delay: '60' history: 7d value_type: FLOAT description: '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.' preprocessing: - type: SIMPLE_CHANGE parameters: - '' tags: - tag: Application value: MySQL - uuid: bd28dd2c74c941898c7d9c56de5e8404 name: 'Threads running' key: 'mysql-stats[Threads_running,{$PORT}]' delay: '60' history: 7d description: | The number of threads that are not sleeping. Is the number of queries running concurrently and fight between to complete in time tags: - tag: Application value: MySQL triggers: - uuid: 2ef4852435004f75979b6d712a2671cc expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Threads_running,{$PORT}])>32' name: 'MySQL: Too Many Concurrent Queries Running' priority: WARNING description: 'Too many active queries indicates there is a severe load on the server, and may be a sign of lock contention or unoptimized SQL queries.' manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: b8b5dce5b9b84f679ad7ed4893e70db3 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Threads_running,{$PORT}])>1000' name: 'MySQL active threads more than 1000 on {HOST.NAME}' priority: HIGH tags: - tag: Value value: '{ITEM.VALUE}' - uuid: c81a9aea842d4979a8fd51a9f1244379 name: 'MySQL: thread_cache_size' key: 'mysql-stats[thread_cache_size,{$PORT}]' delay: 5m history: 7d description: | 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) tags: - tag: Application value: MySQL triggers: - uuid: a9f6f1facec246ebafe0897ba7e6097d expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[thread_cache_size,{$PORT}])<1' name: 'MySQL: Thread Cache Not Enabled' priority: AVERAGE description: 'Each connection to the MySQL database server runs in its own thread. Thread creation takes time, so rather than killing the thread when a connection is closed, the server can keep the thread in its thread cache and use it for a new connection later.' manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: e9868e15d3ac4c9fbdfe231c11198bf2 name: 'MySQL: thread_handling' key: 'mysql-stats[thread_handling,{$PORT}]' delay: 5m history: 7d trends: '0' value_type: CHAR description: | 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 tags: - tag: Application value: MySQL - uuid: f929d220b9be41f3aefe41df8b7a3b73 name: 'MySQL: tmp_table_size' key: 'mysql-stats[tmp_table_size,{$PORT}]' delay: 6h history: 7d units: B description: | 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 tags: - tag: Application value: MySQL - uuid: d2b4276324464779a89dab5ccf0d9737 name: Uptime key: 'mysql-stats[Uptime,{$PORT}]' delay: '60' history: 7d units: uptime description: | 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. tags: - tag: Application value: MySQL triggers: - uuid: 46faaadc05f54ef38f7ba85897523d4d expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])<300' name: 'MySQL: Has just been restarted on {HOST.NAME}' priority: INFO manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' - uuid: 389f38d15b44489f8f4cce74a56b289e name: Version key: 'mysql-stats[version,{$PORT}]' delay: '3600' history: 365d trends: '0' value_type: CHAR tags: - tag: Application value: MySQL triggers: - uuid: 1663cc0e8dbb4df98b029412d124e876 expression: '(last(/Halley MySQL Python server mi passive/mysql-stats[version,{$PORT}],#1)<>last(/Halley MySQL Python server mi passive/mysql-stats[version,{$PORT}],#2))>0' name: 'MySQL: Version of mysql was changed on {HOST.NAME}' priority: WARNING - uuid: f443746acab2429cb3e6e88fc972ce1c name: 'MySQL: version_compile_os' key: 'mysql-stats[version_compile_os,{$PORT}]' delay: 6h history: 7d trends: '0' value_type: CHAR description: 'The type of operating system on which MySQL was built.' tags: - tag: Application value: MySQL triggers: - uuid: 11db658a0a4f4cf6b1eda878dff923a3 expression: 'find(/Halley MySQL Python server mi passive/mysql-stats[version_compile_os,{$PORT}],,"regexp","Win32")<>1 and find(/Halley MySQL Python server mi passive/mysql-stats[version_compile_os,{$PORT}],,"regexp","Win64")<>1 and last(/Halley MySQL Python server mi passive/mysql-stats[version_compile_os,{$PORT}])>0' name: 'MySQL: Flush Time Set To Non-Zero Value' priority: WARNING description: '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.' manual_close: 'YES' tags: - tag: Value value: '>0' - uuid: 87c55eac4e8a46e894905999b16fe9d2 name: 'Available status' key: 'mysql.ping[,{$PORT}]' delay: '60' history: 7d trends: '0' value_type: CHAR tags: - tag: Application value: MySQL triggers: - uuid: a2180c1f8cc14d7aa64da429adc542c2 expression: 'find(/Halley MySQL Python server mi passive/mysql.ping[,{$PORT}],,"iregexp","mysqld is alive")<>1' name: 'MySQL is not available' priority: HIGH manual_close: 'YES' tags: - tag: Value value: '{ITEM.VALUE}' triggers: - uuid: 9b6f77197a054eafb914bbe549614f18 expression: 'find(/Halley MySQL Python server mi passive/mysql-stats[log_bin,{$PORT}],,"regexp","ON")=1 and last(/Halley MySQL Python server mi passive/mysql-stats[sync_binlog,{$PORT}])=1' name: 'MySQL: Binary Logging Not Synchronized To Disk At Each Write' priority: WARNING description: | By default, the binary log contents are not synchronized to disk. If the server host machine or operating system crash, there is a chance that the latest events in the binary log are not persisted on disk. You can alter this behavior using the sync_binlog server variable. If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after sync_binlog commit groups are written to the binary log. The default value of sync_binlog is 0, which does no synchronizing to disk - in this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast). Advice: "Set sync_binlog = 1 within the [mysqld] section of your my.cnf/my.ini file to ensure the greatest safety for recovering from hardware, OS, and MySQL server crashes. Recommended Action SET GLOBAL sync_binlog = 1;" manual_close: 'YES' - uuid: 9055755b60bc455291545a45345b02a7 expression: '(100-(last(/Halley MySQL Python server mi passive/mysql-stats[Binlog_cache_disk_use,{$PORT}])/(last(/Halley MySQL Python server mi passive/mysql-stats[Binlog_cache_use,{$PORT}])+1)*100))<50' name: 'MySQL: Binary Log Usage Exceeding Disk Cache Memory Limits - High' priority: HIGH description: 'When binary log usage exceeds the binary log cache memory limits, it is performing excessive disk operations. For optimal performance, transactions that move through the binary log should be contained within the binary log cache.' manual_close: 'YES' tags: - tag: Value value: '<50' - uuid: cbda4049bb804b1db66d8e9e24de3b2a expression: '(100-(last(/Halley MySQL Python server mi passive/mysql-stats[Binlog_cache_disk_use,{$PORT}])/(last(/Halley MySQL Python server mi passive/mysql-stats[Binlog_cache_use,{$PORT}])+1)*100))<90' name: 'MySQL: Binary Log Usage Exceeding Disk Cache Memory Limits - Info' priority: INFO description: 'When binary log usage exceeds the binary log cache memory limits, it is performing excessive disk operations. For optimal performance, transactions that move through the binary log should be contained within the binary log cache.' manual_close: 'YES' tags: - tag: Value value: '<90' - uuid: 860252124caf4c21bc21c2980dc66a22 expression: '(100-(last(/Halley MySQL Python server mi passive/mysql-stats[Binlog_cache_disk_use,{$PORT}])/(last(/Halley MySQL Python server mi passive/mysql-stats[Binlog_cache_use,{$PORT}])+1)*100))<70' name: 'MySQL: Binary Log Usage Exceeding Disk Cache Memory Limits - Warn' priority: INFO description: 'When binary log usage exceeds the binary log cache memory limits, it is performing excessive disk operations. For optimal performance, transactions that move through the binary log should be contained within the binary log cache.' manual_close: 'YES' tags: - tag: Value value: '<70' - uuid: 9ba0ff01f7de43d08bd88a2334ef0364 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and (100-((last(/Halley MySQL Python server mi passive/mysql-stats[Created_tmp_disk_tables,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Created_tmp_tables,{$PORT}]))*100)<50)' name: 'MySQL: Excessive Disk Temporary Table Usage Detected - High' priority: HIGH description: | "If the space required to build a temporary table exceeds either tmp_table_size or max_heap_table_size, MySQL creates a disk-based table in the server's tmpdir directory. Also, tables that have TEXT or BLOB columns are automatically placed on disk. For performance reasons it is ideal to have most temporary tables created in memory, leaving exceedingly large temporary tables to be created on disk." manual_close: 'YES' tags: - tag: Value value: '<50' - uuid: 15038a1753cb43d9a6413c8e73b42222 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and (100-((last(/Halley MySQL Python server mi passive/mysql-stats[Created_tmp_disk_tables,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Created_tmp_tables,{$PORT}]))*100)<90)' name: 'MySQL: Excessive Disk Temporary Table Usage Detected - Info' priority: INFO description: | "If the space required to build a temporary table exceeds either tmp_table_size or max_heap_table_size, MySQL creates a disk-based table in the server's tmpdir directory. Also, tables that have TEXT or BLOB columns are automatically placed on disk. For performance reasons it is ideal to have most temporary tables created in memory, leaving exceedingly large temporary tables to be created on disk." manual_close: 'YES' tags: - tag: Value value: '<90' - uuid: cf2348e368ae45c780f4b47ec4669f99 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and (100-((last(/Halley MySQL Python server mi passive/mysql-stats[Created_tmp_disk_tables,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Created_tmp_tables,{$PORT}]))*100)<75)' name: 'MySQL: Excessive Disk Temporary Table Usage Detected - Warn' priority: HIGH description: | "If the space required to build a temporary table exceeds either tmp_table_size or max_heap_table_size, MySQL creates a disk-based table in the server's tmpdir directory. Also, tables that have TEXT or BLOB columns are automatically placed on disk. For performance reasons it is ideal to have most temporary tables created in memory, leaving exceedingly large temporary tables to be created on disk." manual_close: 'YES' tags: - tag: Value value: '<70' - uuid: aedb5a188e57470d9b50cef953c35b33 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Connections,{$PORT}])>100 and (100*(last(/Halley MySQL Python server mi passive/mysql-stats[Aborted_connects,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Connections,{$PORT}])))>30' name: 'MySQL: Excessive Percentage Of Attempted Connections To The Server Have Failed' priority: WARNING description: | "Excess aborted connection attempts to MySQL may indicate an issue with respect to the server or network, or could be indicative of DoS or password-cracking attempts against the MySQL Server. The aborted-connects count is incremented when: A client does not have privileges to access a database A client uses the wrong password A malformed packet is received The connect_timeout variable is exceeded" Advice: "Examine the maximum number of connections configured and ensure that the limit has not been reached. If the connection limit appears to not be an issue, investigate the possibility of network problems, and/or attempts to hack in to the database. Enabling the general query log and setting log_warnings=2 within your my.cnf/my.ini file will also force the MySQL server to log further information about these errors. Note that authentication failures are currently logged only to the general query log, but that log can get very large very fast on a heavily-loaded system so you may want to limit its use. Recommended Action SET GLOBAL log_warnings = 2;" manual_close: 'YES' tags: - tag: Value value: '>30' - uuid: 9174a818d24141d2826502d123ae89db expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Connections,{$PORT}])>100 and (100*(last(/Halley MySQL Python server mi passive/mysql-stats[Aborted_connects,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Connections,{$PORT}])))>50' name: 'MySQL: Excessive Percentage Of Attempted Connections To The Server Have Failed - High' priority: WARNING description: | "Excess aborted connection attempts to MySQL may indicate an issue with respect to the server or network, or could be indicative of DoS or password-cracking attempts against the MySQL Server. The aborted-connects count is incremented when: A client does not have privileges to access a database A client uses the wrong password A malformed packet is received The connect_timeout variable is exceeded" Advice: "Examine the maximum number of connections configured and ensure that the limit has not been reached. If the connection limit appears to not be an issue, investigate the possibility of network problems, and/or attempts to hack in to the database. Enabling the general query log and setting log_warnings=2 within your my.cnf/my.ini file will also force the MySQL server to log further information about these errors. Note that authentication failures are currently logged only to the general query log, but that log can get very large very fast on a heavily-loaded system so you may want to limit its use. Recommended Action SET GLOBAL log_warnings = 2;" manual_close: 'YES' tags: - tag: Value value: '>50' - uuid: 296750677290489cbc0c263f9af2d854 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[max_heap_table_size,{$PORT}])10800 and last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd_next,{$PORT}])>100000 and ((100-(((last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd_next,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd,{$PORT}]))/(last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd_next,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_first,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_next,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_key,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_prev,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_last,{$PORT}])))*100))<60)' name: 'MySQL: Indexes Not Being Used Efficiently - High' priority: HIGH description: 'The target server does not appear to be using indexes efficiently. The values of Handler_read_rnd_next and Handler_read_rnd together - which reflect the number of rows read via full table scans - are high compared to the Handler variables which denote index accesses - such as Handler_read_key, Handler_read_next etc. You should examine your tables and queries for proper use of indexes.' tags: - tag: Value value: '<60' - uuid: 4ac3d3b8ce044723aeebc48d623d061d expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd_next,{$PORT}])>100000 and ((100-(((last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd_next,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd,{$PORT}]))/(last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd_next,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_first,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_next,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_key,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_prev,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_last,{$PORT}])))*100))<90)' name: 'MySQL: Indexes Not Being Used Efficiently - Info' priority: HIGH description: 'The target server does not appear to be using indexes efficiently. The values of Handler_read_rnd_next and Handler_read_rnd together - which reflect the number of rows read via full table scans - are high compared to the Handler variables which denote index accesses - such as Handler_read_key, Handler_read_next etc. You should examine your tables and queries for proper use of indexes.' tags: - tag: Value value: '<90' - uuid: 04724588283a4262865537c5f2a08339 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd_next,{$PORT}])>100000 and ((100-(((last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd_next,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd,{$PORT}]))/(last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd_next,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_rnd,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_first,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_next,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_key,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_prev,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Handler_read_last,{$PORT}])))*100))<70)' name: 'MySQL: Indexes Not Being Used Efficiently - Warn' priority: HIGH description: 'The target server does not appear to be using indexes efficiently. The values of Handler_read_rnd_next and Handler_read_rnd together - which reflect the number of rows read via full table scans - are high compared to the Handler variables which denote index accesses - such as Handler_read_key, Handler_read_next etc. You should examine your tables and queries for proper use of indexes.' tags: - tag: Value value: '<70' - uuid: 45b680fcd97b4d72aa6f934efe2b2b12 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and last(/Halley MySQL Python server mi passive/mysql-stats[Key_read_requests,{$PORT}])>10000 and (100-(last(/Halley MySQL Python server mi passive/mysql-stats[Key_reads,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Key_read_requests,{$PORT}]))*100)<75' name: 'MySQL: Key Buffer Size May Not Be Optimal For Key Cache - High' priority: HIGH description: 'The key cache hit ratio represents the proportion of keys that are being read from the key cache in memory instead of from disk. This should normally be greater than 99% for optimum efficiency.' manual_close: 'YES' tags: - tag: Value value: '<75' - uuid: c1a45ea297cc4791965f52c805e6a084 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and last(/Halley MySQL Python server mi passive/mysql-stats[Key_read_requests,{$PORT}])>10000 and (100-(last(/Halley MySQL Python server mi passive/mysql-stats[Key_reads,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Key_read_requests,{$PORT}]))*100)<95' name: 'MySQL: Key Buffer Size May Not Be Optimal For Key Cache - Info' priority: HIGH description: 'The key cache hit ratio represents the proportion of keys that are being read from the key cache in memory instead of from disk. This should normally be greater than 99% for optimum efficiency.' manual_close: 'YES' tags: - tag: Value value: '<95' - uuid: 07e882b9b57a46d19c6346a62c1fce68 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and last(/Halley MySQL Python server mi passive/mysql-stats[Key_read_requests,{$PORT}])>10000 and (100-(last(/Halley MySQL Python server mi passive/mysql-stats[Key_reads,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Key_read_requests,{$PORT}]))*100)<85' name: 'MySQL: Key Buffer Size May Not Be Optimal For Key Cache - Warn' priority: HIGH description: 'The key cache hit ratio represents the proportion of keys that are being read from the key cache in memory instead of from disk. This should normally be greater than 99% for optimum efficiency.' manual_close: 'YES' tags: - tag: Value value: '<85' - uuid: 0d70a78088ac488ca43ff3edba225a33 expression: '((last(/Halley MySQL Python server mi passive/mysql-stats[Threads_connected,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Max_used_connections,{$PORT}]))*100)>95 and last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800' name: 'MySQL: Maximum Connection Limit reach 95%' priority: AVERAGE description: | Once the maximum connection limit for the MySQL server has been reached, no other user connections can be established and errors occur on the client side of the application. Docs in: http://dev.mysql.com/doc/mysql/en/too-many-connections.html manual_close: 'YES' - uuid: 4a01927c7dbc43a98e3143a4552f1e85 expression: '((last(/Halley MySQL Python server mi passive/mysql-stats[Threads_connected,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Max_used_connections,{$PORT}]))*100)>100 and last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800' name: 'MySQL: Maximum Connection Limit reach 100%' priority: HIGH description: | Once the maximum connection limit for the MySQL server has been reached, no other user connections can be established and errors occur on the client side of the application. Docs in: http://dev.mysql.com/doc/mysql/en/too-many-connections.html manual_close: 'YES' - uuid: 20357120932f4841a1ece13a964d0007 expression: 'find(/Halley MySQL Python server mi passive/mysql-stats[default_storage_engine,{$PORT}],,"regexp","MyISAM")=1 and (find(/Halley MySQL Python server mi passive/mysql-stats[myisam_recover_options,{$PORT}],,"regexp","OFF")=1 or find(/Halley MySQL Python server mi passive/mysql-stats[myisam_recover_options,{$PORT}],,"regexp","DEFAULT")=1 or find(/Halley MySQL Python server mi passive/mysql-stats[myisam_recover_options,{$PORT}],,"regexp","")=1)' name: 'MySQL: No Value Set For MyISAM Recover Options' status: DISABLED priority: WARNING description: | 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. Advice: Enable binary logging for point-in-time recovery by setting the log-bin configuration variable in the [mysqld] section of your my.cnf/my.ini file. manual_close: 'YES' - uuid: b5cb3b99c0444a79b48fcd4bf0bb11f9 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Com_stmt_prepare,{$PORT}])>1 and ((100*(last(/Halley MySQL Python server mi passive/mysql-stats[Com_stmt_close,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Com_stmt_prepare,{$PORT}])))<40)' name: 'MySQL: Prepared Statements Not Being Closed - High' priority: HIGH description: | "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." manual_close: 'YES' tags: - tag: Value value: '<40' - uuid: d604690b718e4dec8c5cc786562401bb expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Com_stmt_prepare,{$PORT}])>1 and ((100*(last(/Halley MySQL Python server mi passive/mysql-stats[Com_stmt_close,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Com_stmt_prepare,{$PORT}])))<80)' name: 'MySQL: Prepared Statements Not Being Closed - Info' priority: HIGH description: | "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." manual_close: 'YES' tags: - tag: Value value: '<80' - uuid: c3f54a98e0f74c1bb39908d898c0060f expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Com_stmt_prepare,{$PORT}])>1 and ((100*(last(/Halley MySQL Python server mi passive/mysql-stats[Com_stmt_close,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Com_stmt_prepare,{$PORT}])))<60)' name: 'MySQL: Prepared Statements Not Being Closed - Warn' priority: HIGH description: | "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." manual_close: 'YES' tags: - tag: Value value: '<60' - uuid: a04609498eb3420b9516cfae16e2583e expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Com_prepare_sql,{$PORT}])>1 and (last(/Halley MySQL Python server mi passive/mysql-stats[Com_execute_sql,{$PORT}])>last(/Halley MySQL Python server mi passive/mysql-stats[Com_prepare_sql,{$PORT}])) and ((100-(last(/Halley MySQL Python server mi passive/mysql-stats[Com_prepare_sql,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Com_execute_sql,{$PORT}]))*100)<50)' name: 'MySQL: Prepared Statements Not Being Used Effectively - High' priority: HIGH description: | "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." manual_close: 'YES' tags: - tag: Value value: '<50' - uuid: db05a1a40b8e4d43bfe8d49b1a0537de expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Com_prepare_sql,{$PORT}])>1 and (last(/Halley MySQL Python server mi passive/mysql-stats[Com_execute_sql,{$PORT}])>last(/Halley MySQL Python server mi passive/mysql-stats[Com_prepare_sql,{$PORT}])) and ((100-(last(/Halley MySQL Python server mi passive/mysql-stats[Com_prepare_sql,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Com_execute_sql,{$PORT}]))*100)<90)' name: 'MySQL: Prepared Statements Not Being Used Effectively - Info' priority: HIGH description: | "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." manual_close: 'YES' tags: - tag: Value value: '<90' - uuid: 888806078ca94dc7b673e7834577b901 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Com_prepare_sql,{$PORT}])>1 and (last(/Halley MySQL Python server mi passive/mysql-stats[Com_execute_sql,{$PORT}])>last(/Halley MySQL Python server mi passive/mysql-stats[Com_prepare_sql,{$PORT}])) and ((100-(last(/Halley MySQL Python server mi passive/mysql-stats[Com_prepare_sql,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Com_execute_sql,{$PORT}]))*100)<75)' name: 'MySQL: Prepared Statements Not Being Used Effectively - Warn' priority: HIGH description: | "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." manual_close: 'YES' tags: - tag: Value value: '<70' - uuid: 89620995d0d942009d400a953d6a3999 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Com_prepare_sql,{$PORT}])>1 and (last(/Halley MySQL Python server mi passive/mysql-stats[Com_execute_sql,{$PORT}])>last(/Halley MySQL Python server mi passive/mysql-stats[Com_prepare_sql,{$PORT}])) and ((100-(last(/Halley MySQL Python server mi passive/mysql-stats[Com_prepare_sql,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Com_execute_sql,{$PORT}]))*100)<75)' name: 'MySQL: Prepared Statements Not Being Used Effectively < 75' priority: WARNING description: | "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." - uuid: 26c2ee402944410f9873940695c041f8 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Com_grant,{$PORT}])>0 or last(/Halley MySQL Python server mi passive/mysql-stats[Com_revoke,{$PORT}])>0' name: 'MySQL: Privilege Alterations Have Been Detected' status: DISABLED priority: HIGH description: 'For development environments, changes to database security privileges may be a normal occurrence, but for production environments it is wise to know when any security changes occur with respect to database privileges, and to ensure that those changes are authorized and required.' manual_close: 'YES' - uuid: 0c16d346d5fe4af6bd2bab5d9fb20907 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and (last(/Halley MySQL Python server mi passive/mysql-stats[query_cache_size,{$PORT}])>0 and find(/Halley MySQL Python server mi passive/mysql-stats[query_cache_type,{$PORT}],,"regexp","OFF")=0) and (((1-(last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_free_memory,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[query_cache_size,{$PORT}])))*100)>25) and (((last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_hits,{$PORT}])/(last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_inserts,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_hits,{$PORT}])))*100)<40)' name: 'MySQL: Query Cache Has Sub-Optimal Hit Rate - High' priority: HIGH description: | "When enabled, the query cache should experience a high degree of ""hits"", meaning that queries in the cache are being reused by other user connections. A low hit rate may mean that not enough memory is allocated to the cache, identical queries are not being issued repeatedly to the server, or that the statements in the query cache are invalidated too frequently by INSERT, UPDATE or DELETE statements. This advisor triggers when more than 25% of the Query Cache is being used, and the ratio of Query Cache hits to Query Cache inserts is low." manual_close: 'YES' tags: - tag: Value value: '<40' - uuid: 5fe12a8b3be14ef9860a3ee667d0f150 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and (last(/Halley MySQL Python server mi passive/mysql-stats[query_cache_size,{$PORT}])>0 and find(/Halley MySQL Python server mi passive/mysql-stats[query_cache_type,{$PORT}],,"regexp","OFF")=0) and (((1-(last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_free_memory,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[query_cache_size,{$PORT}])))*100)>25) and (((last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_hits,{$PORT}])/(last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_inserts,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_hits,{$PORT}])))*100)<60)' name: 'MySQL: Query Cache Has Sub-Optimal Hit Rate - Info' priority: INFO description: | "When enabled, the query cache should experience a high degree of ""hits"", meaning that queries in the cache are being reused by other user connections. A low hit rate may mean that not enough memory is allocated to the cache, identical queries are not being issued repeatedly to the server, or that the statements in the query cache are invalidated too frequently by INSERT, UPDATE or DELETE statements. This advisor triggers when more than 25% of the Query Cache is being used, and the ratio of Query Cache hits to Query Cache inserts is low." manual_close: 'YES' tags: - tag: Value value: '<60' - uuid: ae1a37c6f9764a60a31a0c00869f92c6 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and (last(/Halley MySQL Python server mi passive/mysql-stats[query_cache_size,{$PORT}])>0 and find(/Halley MySQL Python server mi passive/mysql-stats[query_cache_type,{$PORT}],,"regexp","OFF")=0) and (((1-(last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_free_memory,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[query_cache_size,{$PORT}])))*100)>25) and (((last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_hits,{$PORT}])/(last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_inserts,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_hits,{$PORT}])))*100)<50)' name: 'MySQL: Query Cache Has Sub-Optimal Hit Rate - Warn' priority: WARNING description: | "When enabled, the query cache should experience a high degree of ""hits"", meaning that queries in the cache are being reused by other user connections. A low hit rate may mean that not enough memory is allocated to the cache, identical queries are not being issued repeatedly to the server, or that the statements in the query cache are invalidated too frequently by INSERT, UPDATE or DELETE statements. This advisor triggers when more than 25% of the Query Cache is being used, and the ratio of Query Cache hits to Query Cache inserts is low." manual_close: 'YES' tags: - tag: Value value: '<50' - uuid: 332aee4a1fc842b3a067b985cd85d4fe expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[query_cache_size,{$PORT}])>0 and find(/Halley MySQL Python server mi passive/mysql-stats[query_cache_type,{$PORT}],,"regexp","OFF")<>1 and last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_free_blocks,{$PORT}])>10000' name: 'MySQL: Query Cache Is Excessively Fragmented - High' priority: HIGH description: | "Enabling the query cache can significantly increase performance for SELECT queries that are identically executed across many connections, returning the same result set. However, performance can be adversely affected if the memory used for the query cache is excessively fragmented, causing the server to pause while it is removing entries from the cache or searching the free block list for a good block to use to insert a new query into the cache. WARNING: The query cache is deprecated and has been removed in MySQL Server 8.0. Please plan ahead accordingly and consider disabling the query cache now." manual_close: 'YES' tags: - tag: Value value: '>10000' - uuid: 2fee25fa88514b22a13d414bb8a361c2 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[query_cache_size,{$PORT}])>0 and find(/Halley MySQL Python server mi passive/mysql-stats[query_cache_type,{$PORT}],,"regexp","OFF")<>1 and last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_free_blocks,{$PORT}])>1000' name: 'MySQL: Query Cache Is Excessively Fragmented - Info' priority: INFO description: | "Enabling the query cache can significantly increase performance for SELECT queries that are identically executed across many connections, returning the same result set. However, performance can be adversely affected if the memory used for the query cache is excessively fragmented, causing the server to pause while it is removing entries from the cache or searching the free block list for a good block to use to insert a new query into the cache. WARNING: The query cache is deprecated and has been removed in MySQL Server 8.0. Please plan ahead accordingly and consider disabling the query cache now." manual_close: 'YES' tags: - tag: Value value: '>1000' - uuid: e0714c9b2f0e41a5bc2c3802f8c9a47d expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[query_cache_size,{$PORT}])>0 and find(/Halley MySQL Python server mi passive/mysql-stats[query_cache_type,{$PORT}],,"regexp","OFF")<>1 and last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_free_blocks,{$PORT}])>5000' name: 'MySQL: Query Cache Is Excessively Fragmented - Warn' priority: INFO description: | "Enabling the query cache can significantly increase performance for SELECT queries that are identically executed across many connections, returning the same result set. However, performance can be adversely affected if the memory used for the query cache is excessively fragmented, causing the server to pause while it is removing entries from the cache or searching the free block list for a good block to use to insert a new query into the cache. WARNING: The query cache is deprecated and has been removed in MySQL Server 8.0. Please plan ahead accordingly and consider disabling the query cache now." manual_close: 'YES' tags: - tag: Value value: '>5000' - uuid: 53c742499f3049119560847f6bc046c3 expression: '(last(/Halley MySQL Python server mi passive/mysql-stats[query_cache_size,{$PORT}])>0 and find(/Halley MySQL Python server mi passive/mysql-stats[query_cache_type,{$PORT}],,"regexp","OFF")<>1) and last(/Halley MySQL Python server mi passive/mysql-stats[Qcache_lowmem_prunes,{$PORT}])>0' name: 'MySQL: Query Cache Potentially Undersized' priority: AVERAGE description: | "When the Query Cache is full, and needs to add more queries to the cache, it will make more room in the cache by freeing the least recently used queries from the cache, and then inserting the new queries. If this is happening often then you should increase the size of the cache to avoid this constant ""swapping"". WARNING: The query cache is deprecated and has been removed in MySQL Server 8.0. Please plan ahead accordingly and consider disabling the query cache now." manual_close: 'YES' tags: - tag: Value value: '>0' - uuid: d1b679aa4d6d466998526a23d3b3757e expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and (((last(/Halley MySQL Python server mi passive/mysql-stats[Table_locks_waited,{$PORT}])/(last(/Halley MySQL Python server mi passive/mysql-stats[Table_locks_immediate,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Table_locks_waited,{$PORT}])))*100)>95)' name: 'MySQL: Table Lock Contention Excessive - High' priority: HIGH description: | "If the space required to build a temporary table exceeds either tmp_table_size or max_heap_table_size, MySQL creates a disk-based table in the server's tmpdir directory. Also, tables that have TEXT or BLOB columns are automatically placed on disk. For performance reasons it is ideal to have most temporary tables created in memory, leaving exceedingly large temporary tables to be created on disk." manual_close: 'YES' tags: - tag: Value value: '>95' - uuid: 6b48d0c1584849cbbf42084143b153e8 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and (((last(/Halley MySQL Python server mi passive/mysql-stats[Table_locks_waited,{$PORT}])/(last(/Halley MySQL Python server mi passive/mysql-stats[Table_locks_immediate,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Table_locks_waited,{$PORT}])))*100)>30)' name: 'MySQL: Table Lock Contention Excessive - Info' priority: INFO description: | "If the space required to build a temporary table exceeds either tmp_table_size or max_heap_table_size, MySQL creates a disk-based table in the server's tmpdir directory. Also, tables that have TEXT or BLOB columns are automatically placed on disk. For performance reasons it is ideal to have most temporary tables created in memory, leaving exceedingly large temporary tables to be created on disk." manual_close: 'YES' tags: - tag: Value value: '>30' - uuid: e9c8e944c33c44cb8f8822da9b0eae65 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and (((last(/Halley MySQL Python server mi passive/mysql-stats[Table_locks_waited,{$PORT}])/(last(/Halley MySQL Python server mi passive/mysql-stats[Table_locks_immediate,{$PORT}])+last(/Halley MySQL Python server mi passive/mysql-stats[Table_locks_waited,{$PORT}])))*100)>60)' name: 'MySQL: Table Lock Contention Excessive - Warn' priority: HIGH description: | "If the space required to build a temporary table exceeds either tmp_table_size or max_heap_table_size, MySQL creates a disk-based table in the server's tmpdir directory. Also, tables that have TEXT or BLOB columns are automatically placed on disk. For performance reasons it is ideal to have most temporary tables created in memory, leaving exceedingly large temporary tables to be created on disk." manual_close: 'YES' tags: - tag: Value value: '>60' - uuid: 8c31a6a50be9430b8336ab372b21a387 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and (100-((last(/Halley MySQL Python server mi passive/mysql-stats[Threads_created,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Connections,{$PORT}]))*100)<85)' name: 'MySQL: Thread Cache Size May Not Be Optimal - Info' priority: INFO description: | Each connection to the MySQL database server runs in its own thread. Thread creation takes time, so rather than killing the thread when a connection is closed, the server can keep the thread in its thread cache and use it for a new connection later. Advice: "Increase the thread_cache_size variable dynamically and monitor the thread cache hit ratio (SET GLOBAL thread_cache_size = ;). When it reaches an acceptable level, put the corresponding value of thread_cache_size in your my.cnf/my.ini file so the variable is set properly when the server is restarted. The ideal situation is to get Threads_created as close as possible to thread_cache_size - no new connections having to wait for new thread allocation - staying as close to around a 99% thread cache hit ratio as you can. The thread cache hit ratio is calculated as follows: 100-((Threads_created / Connections)*100)" tags: - tag: Value value: '<85' - uuid: f6d25bcfd28d4be7a8bdc71d1dee0f27 expression: 'last(/Halley MySQL Python server mi passive/mysql-stats[Uptime,{$PORT}])>10800 and (100-((last(/Halley MySQL Python server mi passive/mysql-stats[Threads_created,{$PORT}])/last(/Halley MySQL Python server mi passive/mysql-stats[Connections,{$PORT}]))*100)<75)' name: 'MySQL: Thread Cache Size May Not Be Optimal - Warn' priority: INFO description: | Each connection to the MySQL database server runs in its own thread. Thread creation takes time, so rather than killing the thread when a connection is closed, the server can keep the thread in its thread cache and use it for a new connection later. Advice: "Increase the thread_cache_size variable dynamically and monitor the thread cache hit ratio (SET GLOBAL thread_cache_size = ;). When it reaches an acceptable level, put the corresponding value of thread_cache_size in your my.cnf/my.ini file so the variable is set properly when the server is restarted. The ideal situation is to get Threads_created as close as possible to thread_cache_size - no new connections having to wait for new thread allocation - staying as close to around a 99% thread cache hit ratio as you can. The thread cache hit ratio is calculated as follows: 100-((Threads_created / Connections)*100)" tags: - tag: Value value: '<87' graphs: - uuid: 37f9a9d829ef434ea1d2829c6fb8e954 name: 'MySQL: Binlog Cache Writes Efficiency' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Binlog_cache_disk_use,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Binlog_cache_use,{$PORT}]' - uuid: 3d2661fbef4c4c0eb522811b08b3f835 name: 'MySQL: Binlog Statement Cache Efficiency' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Binlog_stmt_cache_disk_use,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Binlog_stmt_cache_use,{$PORT}]' - uuid: 911bb0e562c84f63a4324e6b39d71cdb name: 'MySQL: Connections' graph_items: - color: 2774A4 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Connections,{$PORT}]' - sortorder: '1' color: A54F10 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Max_used_connections,{$PORT}]' - sortorder: '2' color: FC6EA3 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Threads_connected,{$PORT}]' - sortorder: '3' color: 6C59DC item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Threads_running,{$PORT}]' - uuid: f39fabe7348b42e798ae37875b822983 name: 'MySQL: Connections - Aborted' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Aborted_connects,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Aborted_clients,{$PORT}]' - sortorder: '2' color: 2774A4 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Connections,{$PORT}]' - uuid: fa9a2e235b6a424fb3814c33abf608ec name: 'MySQL: Connections - Cache (Thread cache)' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Connections,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Threads_cached,{$PORT}]' - uuid: 80a721025ab44ed89879689ed6dd1c4c name: 'MySQL: Connections/Threads' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Threads_cached,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Threads_connected,{$PORT}]' - sortorder: '2' color: 2774A4 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Threads_running,{$PORT}]' - uuid: e78b9fa9c0ad4094a153a4e5d78e2e84 name: 'MySQL: MyISAM Indexes' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Key_reads,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Key_read_requests,{$PORT}]' - sortorder: '2' color: 2774A4 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Key_write_requests,{$PORT}]' - sortorder: '3' color: A54F10 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Key_writes,{$PORT}]' - sortorder: '4' color: FC6EA3 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Key_blocks_not_flushed,{$PORT}]' - sortorder: '5' color: 6C59DC item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Key_blocks_unused,{$PORT}]' - sortorder: '6' color: AC8C14 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Key_blocks_used,{$PORT}]' - uuid: c98b37d8f5ab4cdeaf9cc0906fe04e79 name: 'MySQL: Network traffic' width: '600' show_work_period: 'NO' show_triggers: 'NO' ymin_type_1: FIXED graph_items: - color: FF6666 yaxisside: RIGHT item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Bytes_received,{$PORT}]' - sortorder: '1' color: 6666FF yaxisside: RIGHT item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Bytes_sent,{$PORT}]' - uuid: a5993a76edde4db0b1d5e5138e1e9601 name: 'MySQL: Queries executed' width: '600' show_work_period: 'NO' show_triggers: 'NO' ymin_type_1: FIXED graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Com_begin,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Com_commit,{$PORT}]' - sortorder: '2' color: 2774A4 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Com_create_table,{$PORT}]' - sortorder: '3' color: A54F10 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Com_delete,{$PORT}]' - sortorder: '4' color: FC6EA3 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Com_insert,{$PORT}]' - sortorder: '5' color: 6C59DC item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Com_replace,{$PORT}]' - sortorder: '6' color: AC8C14 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Com_select,{$PORT}]' - sortorder: '7' color: 611F27 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Com_update,{$PORT}]' - uuid: 36d5a90c743d4dc3ab7abcf59ebb69a7 name: 'MySQL: Query cache blocks' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Qcache_free_blocks,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Qcache_total_blocks,{$PORT}]' - uuid: 59fcd74fdfcc4632acc55dd4544cf971 name: 'MySQL: Query cache efficiency' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Qcache_hits,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Qcache_inserts,{$PORT}]' - sortorder: '2' color: 2774A4 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Qcache_not_cached,{$PORT}]' - uuid: e322bd638e224ea7b63f3ae53d0e6e2e name: 'MySQL: Query cache low memory prunes' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Qcache_lowmem_prunes,{$PORT}]' - uuid: 2fffad630f024e97aa401e39d1958a2b name: 'MySQL: Query Cache queries' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Qcache_queries_in_cache,{$PORT}]' - uuid: ccddadc4e759450ba9c00285cd1b5bf4 name: 'MySQL: Row access (Handlers reads)' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Handler_read_first,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Handler_read_key,{$PORT}]' - sortorder: '2' color: 2774A4 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Handler_read_last,{$PORT}]' - sortorder: '3' color: A54F10 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Handler_read_next,{$PORT}]' - sortorder: '4' color: FC6EA3 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Handler_read_prev,{$PORT}]' - sortorder: '5' color: 6C59DC item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Handler_read_rnd,{$PORT}]' - sortorder: '6' color: AC8C14 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Handler_read_rnd_next,{$PORT}]' - sortorder: '7' color: 611F27 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Sort_rows,{$PORT}]' - uuid: c24bbff011b34e4497cd7ce9c3958fa6 name: 'MySQL: Row writes (Handler writes)' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Handler_write,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Handler_delete,{$PORT}]' - sortorder: '2' color: 2774A4 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Handler_update,{$PORT}]' - uuid: 2aa28967f433403892e39d551c00e789 name: 'MySQL: Slow Queries' width: '600' show_work_period: 'NO' show_triggers: 'NO' ymin_type_1: FIXED graph_items: - color: EE0000 yaxisside: RIGHT item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Slow_queries,{$PORT}]' - uuid: dbcc6f87733b43ca8673e7da79b8f615 name: 'MySQL: Sort activity' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Sort_merge_passes,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Sort_range,{$PORT}]' - sortorder: '2' color: 2774A4 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Sort_scan,{$PORT}]' - uuid: 7cb47147861843a7a2e963e8ad9db17a name: 'MySQL: Table definition' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Opened_table_definitions,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Open_table_definitions,{$PORT}]' - uuid: 1366edbef62f49f49e2567fbf404103f name: 'MySQL: Table locks' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Table_locks_immediate,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Table_locks_waited,{$PORT}]' - uuid: 83afaa2561c0426baf6f231250f77790 name: 'MySQL: Temporary files' graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Created_tmp_files,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Opened_files,{$PORT}]' - uuid: d142efa49a8c49a1893e104458241fe9 name: 'MySQL: Temporary tables' ymin_type_1: FIXED graph_items: - color: 1A7C11 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Created_tmp_disk_tables,{$PORT}]' - sortorder: '1' color: F63100 item: host: 'Halley MySQL Python server mi passive' key: 'mysql-stats[Created_tmp_tables,{$PORT}]'