MySQL Monitor Variables

List of MySQL Monitor Variables

NOTE: You can click on the variable name to jump to its definition

mysql-monitor_connect_interval

The interval at which the Monitor module of the proxy will try to connect to all the MySQL servers in order to check whether they are available or not.

mysql-monitor_connect_timeout

Connection timeout in milliseconds. The current implementation rounds this value to an integer number of seconds less or equal to the original interval, with 1 second as minimum. This lazy rounding is done because SSL connections are blocking calls.

mysql-monitor_enabled

It enables or disables MySQL Monitor.

mysql-monitor_galera_healthcheck_interval

The interval at which the proxy should connect to the backend servers in order to monitor the Galera status of a node. Nodes can be temporarily shunned if their status is not available which is controlled by the mysql_galera_hostgroups.max_transactions_behind column in the admin interface, on a per-hostgroup level (introduced in ProxySQL v2.0).

mysql-monitor_galera_healthcheck_max_timeout_count

Sets the max number of times ProxySQL has timeout checking on a Galera Node before declaring it OFFLINE.

mysql-monitor_galera_healthcheck_timeout

How long the Monitor module will wait for a Galera status check reply (introduced in ProxySQL v2.0).

mysql-monitor_groupreplication_healthcheck_interval

The interval at which the proxy should connect to the backend servers in order to monitor the Group Replication status of a node. Nodes can be temporarily shunned if their status is not available which is controlled by the mysql_group_replication_hostgroups.max_transactions_behind column in the admin interface, on a per-hostgroup level.

mysql-monitor_groupreplication_healthcheck_max_timeout_count

Sets the max number of times ProxySQL has timeout checking on a Group Replication Node before declaring it OFFLINE.

mysql-monitor_groupreplication_healthcheck_timeout

How long the Monitor module will wait for a Group Replication status check reply.

mysql-monitor_groupreplication_max_transactions_behind_count

Sets the number of times that a server needs to fail a ‘max_transactions_behind’ check before an action is performed. If the number of failed checks exceeds this number:

• Pre v2.3.0 If the server has read_only=1 it’s set to ‘OFFLINE’ until replication catches up.
• After v2.3.0 Servers are placed as ‘SHUNNED’ depending on the value of variable ‘mysql-monitor_groupreplication_max_transaction_behind_for_read_only‘.

NOTE: After v2.3.0 ProxySQL SHUNNED status is executed in two stages in order to allow a grace time before terminating all the connections

mysql-monitor_groupreplication_max_transactions_behind_for_read_only

Determines which action is going to be performed over a server when ‘mysql-monitor_groupreplication_max_transactions_behind_count‘ is exceeded. Possible values are:

• ‘0’: Only servers with read_only=0 are placed as ‘SHUNNED’.
• ‘1’: Only servers with read_only=1 are placed as ‘SHUNNED’ (default).
• ‘2’: Both servers with read_only=1 and read_only=0 are placed as ‘SHUNNED’.

mysql-monitor_history

The duration for which the events for the checks made by the Monitor module are kept. Such events include connecting to backend servers (to check for connectivity issues), querying them with a simple query (in order to check that they are running correctly) or checking their replication lag. These logs are kept in the following admin tables:

• mysql_server_connect_log
• mysql_server_ping_log
• mysql_server_replication_lag_log

mysql-monitor_local_dns_cache_refresh_interval

Specifies the refresh interval at which monitor DNS cache entries, ‘mysql_servers’ and ‘proxysql_servers’ will be re-check for new records. If expired entries are found, i.e entries with higher lifetimes than mysql-monitor_local_dns_cache_ttl, a new resolution attempt will be placed in the DNS resolver queue.

mysql-monitor_local_dns_cache_ttl

Specifies the expiration time for DNS cache entries for monitor. Once a entry outlives this threshold, a new resolution attempt will be placed in the DNS resolver queue when re-checked at the next refresh interval.

mysql-monitor_local_dns_resolver_queue_maxsize

Determines how much the DNS resolver queue can grow before starting new monitoring helper threads. The number of threads is dynamically scaled, starting with 1 helper thread, up to 32 threads. Whenever this scaling is found necessary, a message is placed in the error log.

mysql-monitor_password

Specifies the password that the Monitor module will use to connect to the backends.

mysql-monitor_ping_interval

The interval at which the Monitor module should ping the backend servers by using the mysql_ping API. Before version 1.4.14, the default was 60000 (1 minute).

mysql-monitor_ping_max_failures

The maximum number of ping failures the Monitor module should tolerate before sending a signal to MySQL_Hostgroups_Manager to kill all connections to the backend server.

mysql-monitor_ping_timeout

How long the Monitor module will wait for a ping reply.

mysql-monitor_query_interval

Currently unused. Will be used by the Monitor module in order to collect data about the global status of the backend servers.

mysql-monitor_query_timeout

Currently unused. Will be used by the Monitor module in order to collect data about the global status of the backend servers.

mysql-monitor_read_only_interval

Defines the frequency to check the Read Only status of a backend server (in milliseconds).

mysql-monitor_read_only_max_timeout_count

When the monitor thread performs a read_only check, AND the check exceeds mysql-monitor_read_only_timeout, repeat the read_only check up to mysql-monitor_read_only_max_timeout_count times before setting the replica to OFFLINE HARD.

mysql-monitor_read_only_timeout

The timeout for a single attempt at checking the Read Only status on a backend server from the proxy.

mysql-monitor_replication_lag_count

Defines the count at which a server is going to be SHUNNED due to replication lag if it’s currently found replication lag exceeds the specified max_replication_lag for the server.

mysql-monitor_replication_lag_group_by_host

Introduced in v2.4.0. Allows to control how monitor performs replication lag checks over servers in hostgroups. Possible values are:

• true: Monitor will perform 1 replication lag check per server per hostgroup.
• false: Monitor will perform 1 replication lag check per server.

NOTE: This variable need to be set only in setups in which the same server is configured in many hostgroups, thus reducing the number of checks.

mysql-monitor_replication_lag_interval

The interval at which the proxy should connect to the backend servers in order to monitor the replication lag between those that are slaves and their masters. Slaves can be temporarily shunned if the replication lag is too large. This setting is controlled by the mysql_servers.max_replication_lag column in the admin interface, at a per-hostgroup level.

mysql-monitor_replication_lag_timeout

How long the Monitor module will wait for the output of SHOW SLAVE STATUS to be returned from the database.

mysql-monitor_replication_lag_use_percona_heartbeat

This variable defines the . where pt-heartbeat information is written, when this variable is defined replication lag checks are determined based on the values in this table rather than SHOW SLAVE STATUS. This is empty by default, when using pt-heartbeat the value is typically defined as percona.heartbeat.

mysql-monitor_slave_lag_when_null

When replication check returns that Seconds_Behind_Master=NULL, the value of mysql-monitor_slave_lag_when_null (in seconds) is assumed to be the current replication lag. This allows to either shun or keep online a server where replication is broken/stopped.

mysql-monitor_threads_max

Controls the maximum number of threads within the Monitor Module thread pool. Introduced in ProxySQL v2.0. From 1.3.2 and before 2.0 the minimum value was hardcoded.

mysql-monitor_threads_min

Controls the minimum number of threads within the Monitor Module thread pool. Introduced in ProxySQL v2.0. From 1.3.2 and before 2.0 the minimum value was hardcoded.

mysql-monitor_threads_queue_maxsize

The variable controls how many checks are queued before starting new monitor threads.

mysql-monitor_timer_cached

DEPRECATED This variable controls whether ProxySQL should use a cached (and less accurate) value of wall clock time, or not.

mysql-monitor_username

Specifies the username that the Monitor module will use to connect to the backends. The user needs only USAGE privileges to connect, ping and check read_only. The user needs also REPLICATION CLIENT if it needs to monitor replication lag. The user specified in mysql-monitor_username CANNOT be used in mysql_users.

mysql-monitor_wait_timeout

In order to avoid being disconnected the Monitor Module tunes wait_timeout on its connections to backends. This is generally a good thing, however it could become a problem if ProxySQL is acting as a “forwarder”. When mysql-monitor_wait_timeout is set to false the feature is disabled.

mysql-monitor_writer_is_also_reader

When a node changes its read_only value from 1 to 0, this variable determines if the node should be present in both hostgroups or not:

• false : the node will be moved in writer_hostgroup and removed from reader_hostgroup
• true : the node will be copied in writer_hostgroup and it will also stay in reader_hostgroup