Global variables

Global Variables

The behaviour of ProxySQL can be tweaked using global variables. These can be configured in 2 ways:

at runtime, using the admin interface (preferred)
at startup, using the dedicated section in the configuration file

ProxySQL supports maximal uptime by allowing most variables to change at runtime and take effect immediately, without having to restart the daemon. There are only 3x variables that cannot be changed at runtime - mysql-interfaces, mysql-threads and mysql-stacksize.

Also, there are 2 types of global variables, depending on which part of ProxySQL they control:

admin variables, which control the behaviour of the admin interface. Their names begin with the token "admin-"
mysql variables, which control the MySQL functionality of the proxy. Their names begin with the token "mysql-"
mysql-default_ ProxySQL tracks these variables as required by the application, and it set them on the backend connection accordingly. ProxySQL is not aware of the current value of every new connection, instead it assume that mysql-default_variable is the default in MySQL server as well. Given that is important to have these variables correctly configured.

These global variables are stored in a per-thread fashion inside of the proxy in order to speed up access to them, as they are used extremely frequently. They control the behaviour of the proxy in terms of memory footprint or the number of connections accepted, and other essential aspects. Whenever a LOAD MYSQL VARIABLES TO RUNTIME or LOAD ADMIN VARIABLES TO RUNTIME command is issued, all the threads using the mysql or admin variables are notified that they have to update their values.

To change the value of a global variable either use an UPDATE statement:

UPDATE global_variables SET variable_value=1900 WHERE variable_name='admin-refresh_interval';

or the shorter SET statement, similar to MySQL's:

SET admin-refresh_interval = 1700;
SET admin-version = '1.1.1beta8';

Next, we're going to explain each type of variable in detail.

Variable name	Default Value	From Version
admin-admin_credentials	admin:admin
admin-checksum_mysql_query_rules	true
admin-checksum_mysql_servers	true
admin-checksum_mysql_users	true
admin-cluster_check_interval_ms	1000
admin-cluster_check_status_frequency	10
admin-cluster_mysql_query_rules_diffs_before_sync	3
admin-cluster_mysql_query_rules_save_to_disk	true
admin-cluster_mysql_servers_diffs_before_sync	3
admin-cluster_mysql_servers_save_to_disk	true
admin-cluster_mysql_users_diffs_before_sync	3
admin-cluster_mysql_users_save_to_disk	true
admin-cluster_password
admin-cluster_proxysql_servers_diffs_before_sync	3
admin-cluster_proxysql_servers_save_to_disk	true
admin-cluster_username
admin-hash_passwords	true
admin-mysql_ifaces	0.0.0.0:6032
admin-read_only	false
admin-refresh_interval	2000
admin-stats_credentials	stats:stats
admin-stats_mysql_connection_pool	60
admin-stats_mysql_connections	60
admin-stats_mysql_query_cache	60
admin-stats_system_cpu	60
admin-stats_system_memory	60
admin-telnet_admin_ifaces	(null)
admin-telnet_stats_ifaces	(null)
admin-vacuum_stats	true	2.0.6
admin-version	2.0.7-80-g4dd4ef5f
admin-web_enabled	false
admin-web_port	6080
mysql-add_ldap_user_comment
mysql-auditlog_filename
mysql-auditlog_filesize	100MB
mysql-auto_increment_delay_multiplex	5
mysql-autocommit_false_is_transaction	false
mysql-autocommit_false_not_reusable	false
mysql-binlog_reader_connect_retry_msec	3000
mysql-client_found_rows	true
mysql-client_multi_statements	true
mysql-client_session_track_gtid	true
mysql-commands_stats	true
mysql-connect_retries_delay	1
mysql-connect_retries_on_failure	10
mysql-connect_timeout_server	3000
mysql-connect_timeout_server_max	10000
mysql-connection_delay_multiplex_ms	0
mysql-connection_max_age_ms	0
mysql-connpoll_reset_queue_length	50
mysql-default_character_set_results	NULL
mysql-default_charset	utf8
mysql-default_collation_connection
mysql-default_isolation_level	READ COMMITTED
mysql-default_max_join_size	18446744073709551615
mysql-default_max_latency_ms	1000
mysql-default_net_write_timeout	60
mysql-default_query_delay	0
mysql-default_query_timeout	36000000
mysql-default_reconnect	true
mysql-default_schema	information_schema
mysql-default_session_track_gtids	OFF
mysql-default_sql_auto_is_null	OFF
mysql-default_sql_mode
mysql-default_sql_safe_updates	OFF
mysql-default_sql_select_limit	DEFAULT
mysql-default_time_zone	SYSTEM
mysql-default_transaction_read	WRITE
mysql-default_tx_isolation	READ-COMMITTED
mysql-enforce_autocommit_on_reads	false
mysql-eventslog_default_log	0
mysql-eventslog_filename
mysql-eventslog_filesize	104857600
mysql-eventslog_format	1
mysql-forward_autocommit	false
mysql-free_connections_pct	10
mysql-have_compress	true
mysql-have_ssl	false
mysql-hostgroup_manager_verbose	1
mysql-init_connect
mysql-interfaces	0.0.0.0:6033
mysql-keep_multiplexing_variables	tx_isolation,version
mysql-kill_backend_connection_when_disconnect	true
mysql-ldap_user_variable
mysql-long_query_time	1000
mysql-max_allowed_packet	4194304
mysql-max_connections	2048
mysql-max_stmts_cache	10000
mysql-max_stmts_per_connection	20
mysql-max_transaction_time	14400000
mysql-min_num_servers_lantency_awareness	1000
mysql-mirror_max_concurrency	16
mysql-mirror_max_queue_length	32000
mysql-monitor_connect_interval	60000
mysql-monitor_connect_timeout	600
mysql-monitor_enabled	true
mysql-monitor_galera_healthcheck_interval	5000
mysql-monitor_galera_healthcheck_max_timeout_count	3
mysql-monitor_galera_healthcheck_timeout	800
mysql-monitor_groupreplication_healthcheck_interval	5000
mysql-monitor_groupreplication_healthcheck_max_timeout_count	3
mysql-monitor_groupreplication_healthcheck_timeout	800
mysql-monitor_history	600000
mysql-monitor_password	monitor
mysql-monitor_ping_interval	10000
mysql-monitor_ping_max_failures	3
mysql-monitor_ping_timeout	1000
mysql-monitor_query_interval	60000
mysql-monitor_query_timeout	100
mysql-monitor_read_only_interval	1500
mysql-monitor_read_only_max_timeout_count	3
mysql-monitor_read_only_timeout	500
mysql-monitor_replication_lag_interval	10000
mysql-monitor_replication_lag_timeout	1000
mysql-monitor_replication_lag_use_percona_heartbeat
mysql-monitor_slave_lag_when_null	60
mysql-monitor_threads_max	128
mysql-monitor_threads_min	8
mysql-monitor_threads_queue_maxsize	128
mysql-monitor_username	monitor
mysql-monitor_wait_timeout	true
mysql-monitor_writer_is_also_reader	true
mysql-multiplexing	true
mysql-ping_interval_server_msec	120000
mysql-ping_timeout_server	500
mysql-poll_timeout	2000
mysql-poll_timeout_on_failure	100
mysql-query_cache_size_MB	256
mysql-query_cache_stores_empty_result	true
mysql-query_digests	true
mysql-query_digests_lowercase	false
mysql-query_digests_max_digest_length	2048
mysql-query_digests_max_query_length	65000
mysql-query_digests_no_digits	false
mysql-query_digests_normalize_digest_text	false
mysql-query_digests_replace_null	false
mysql-query_digests_track_hostname	false
mysql-query_processor_iterations	0
mysql-query_processor_regex	1
mysql-query_retries_on_failure	1
mysql-reset_connection_algorithm	2
mysql-server_capabilities	569867
mysql-server_version	5.5.30
mysql-servers_stats	true
mysql-session_idle_ms	1000
mysql-session_idle_show_processlist	true
mysql-sessions_sort	true
mysql-set_query_lock_on_hostgroup	1
mysql-show_processlist_extended	0
mysql-shun_on_failures	5
mysql-shun_recovery_time_sec	10
mysql-ssl_p2s_ca
mysql-ssl_p2s_cert
mysql-ssl_p2s_cipher
mysql-ssl_p2s_key
mysql-stacksize	1048576
mysql-stats_time_backend_query	false
mysql-stats_time_query_processor	false
mysql-tcp_keepalive_time	0
mysql-threads	4
mysql-threshold_query_length	524288
mysql-threshold_resultset_size	4194304
mysql-throttle_connections_per_sec_to_hostgroup	1000000
mysql-throttle_max_bytes_per_second_to_client	0
mysql-throttle_ratio_server_to_client	0
mysql-use_tcp_keepalive	0
mysql-verbose_query_error	false
mysql-wait_timeout	28800000

Admin Variables

`admin-admin_credentials`

This is a list of semi-colon separated user:password pairs, that can be used to authenticate to the admin interface with read-write rights. For read-only credentials that can be used to connect to the admin, see the variable admin-stats_credentials. Note that the admin interface listens on a separate port from the main ProxySQL thread. This port is controlled through the variable admin-mysql_ifaces.

It is important to note that:

the default admin user can only connect locally, in order to connect remotely a secondary user needs to be created by defining this in the admin-admin_credentials variable E.G. admin-admin_credentials="admin:admin;radminuser:radminpass".
users in admin-admin_credentials cannot be used also in mysql_users table.

System Variable	Name	admin-admin_credentials
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	admin:admin

`admin-hash_passwords`

ProxySQL v1.2.3 introduced a new global boolean variable, admin-hash_passwords, enabled by default. When admin-hash_passwords=true, password are automatically hashed at RUNTIME when running LOAD MYSQL USERS TO RUNTIME. Passwords in mysql_users tables are not automatically hashed and require you to run SAVE MYSQL USERS FROM RUNTIME.

See Password Management for further details.

System Variable	Name	admin-hash_passwords
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`admin-mysql_ifaces`

Semicolon-separated list of hostname:port entries for interfaces on which the admin interface should listen on. Note that this also supports UNIX domain sockets for the cases where the connection is done from an application on the same machine E.G.: SET admin-mysql_ifaces='127.0.0.1:6032;/tmp/proxysql_admin.sock'. Please note that the default admin user can only connect locally, in order to connect remotely a secondary user needs to be created by defining this in the admin-admin_credentials variable E.G. admin-admin_credentials="admin:admin;radminuser:radminpass".

System Variable	Name	admin-mysql_ifaces
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default (up to 1.4.0)	127.0.0.1:6032
	Default (from 1.4.1 onwards)	0.0.0.0:6032

`admin-read_only`

When this variable is set to true and loaded at runtime, the Admin module does not accept write anymore. This is useful to ensure that ProxySQL is not reconfigured. When admin-read_only=true, the only way to revert it to false at runtime (and make the Admin module writable again) is to run the command PROXYSQL READWRITE.

System Variable	Name	admin-read_only
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`admin-refresh_interval`

The refresh interval (in microseconds) for updates to the query rules statistics and commands counters statistics. Be careful about tweaking this to a value that is:

too low, because it might affect the overall performance of the proxy
too high, because it might affect the correctness of the results

System Variable	Name	admin-refresh_interval
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (microseconds)
	Default	2000
	Minimum	100
	Maximum	100000

`admin-stats_credentials`

This is a list of semi-colon separated user:password pairs that defines the read-only credentials for connecting to the admin interface. These are not allowed updates to internal data structures such as the list of MySQL backend servers (or hostgroups), query rules, etc. They only allow readings from the statistics and monitoring tables (the other tables are not only even visible). Note: users in admin-stats_credentials cannot be used also in mysql_users table.

System Variable	Name	admin-stats_credentials
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	stats:stats

`admin-telnet_admin_ifaces`

Not currently used (planned usage in a future version).

`admin-telnet_stats_ifaces`

Not currently used (planned usage in a future version).

`admin-version`

This variable displays ProxySQL version. This variable is read only.

System Variable	Name	admin-version
System Variable	Dynamic	No
Permitted Values	Type	String
	Read Only	true

Admin historical statistics

Since ProxySQL 1.4.4 Admin stores historical metrics in new database named proxysql_stats.db in the datadir.
Tables structures is subject to future changes.

`admin-stats_mysql_connection_pool`

The refresh interval (in seconds) to update the historical statistics of the connection pool.

System Variable	Name	admin-stats_mysql_connection_pool
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (seconds)
	Default	60
	Valid values	5, 10, 30, 60, 120, 300

`admin-stats_mysql_connections`

The refresh interval (in seconds) to update the historical statistics of MySQL connections, both frontends and backends.

System Variable	Name	admin-stats_mysql_connections
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (seconds)
	Default	60
	Valid values	5, 10, 30, 60, 120, 300

`admin-stats_mysql_query_cache`

The refresh interval (in seconds) to update the historical statistics of MySQL Query Cache.

System Variable	Name	admin-stats_mysql_query_cache
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (seconds)
	Default	60
	Valid values	5, 10, 30, 60, 120, 300

`admin-stats_system_cpu`

The refresh interval (in seconds) to update the historical statistics of CPU usage.

System Variable	Name	admin-stats_system_cpu
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (seconds)
	Default	60
	Valid values	5, 10, 30, 60, 120, 300

`admin-stats_system_memory`

The refresh interval (in seconds) to update the historical statistics of memory usage.
Note: These statistics are not available if ProxySQL is not compiled with jemalloc. Note that all official packages are compiled with jemalloc.

System Variable	Name	admin-stats_mysql_system_memory
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (seconds)
	Default	60
	Valid values	5, 10, 30, 60, 120, 300

admin-vacuum_stats

This parameter enable|disable the vacuum operation on the SQLite database storing the statistics. VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure.

System Variable	Name	admin-vacuum_stats
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean (seconds)
	Default	true
	Valid values	true, false

Admin web interface

ProxySQL 1.4.4 embeds an HTTP web server from where is possible to gather certain metrics.
Credentials to access the web interfaces are the same defined in admin-stats_credentials.

`admin-web_enabled`

If admin-web_enabled is set to true, the web server is automatically enabled.

System Variable	Name	admin-web_enabled
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`admin-web_port`

This variable defines on which port the web server is listening.

System Variable	Name	admin-web_port
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	6080

MySQL Variables

`mysql-add_ldap_user_comment`

If mysql-add_ldap_user_comment is set, a comment like the following will be added on the query::

valueof_mysql-add_ldap_user_comment=frontend_username

MySQL Variable	Name	mysql-add_ldap_user_comment
MySQL Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	NULL
	Valid values

`mysql-ldap_user_variable`

When set, sessions will have a variable set with the user_name value, ie: SET @mysql-ldap_user_variable:='username' The use of this variable can be for auditing purposed backend side. For example, if a trigger on a table will use that session variable.

MySQL Variable	Name	mysql-ldap_user_variable
MySQL Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	NULL
	Valid values

`mysql-auditlog_filename`

This variable defines the base name of the audit log where audit events are logged. The filename of the log file will be the base name followed by an 8 digits progressive number.

MySQL Variable	Name	mysql-auditlog_filename
MySQL Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	NULL
	Valid values

`mysql-auditlog_filesize`

This variable defines the maximum file size of the audit log when the current file will be closed and a new file will be created. The default value is 104857600 (100MB)

System Variable	Name	mysql-auditlog_filesize
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (count)
	Default	104857600
	Minimum	1MB
	Maximum	1GB

`mysql-auto_increment_delay_multiplex`

Several applications rely, explicitly or implicitly, to the value returned by LAST_INSERT_ID(). If multiplexing is not configured correctly, or if the queries pattern is really unpredictable (for example if new queries are often deployed), it is possible that the query using LAST_INSERT_ID() uses a connection different than the connection where an auto-increment was used.
If mysql-auto_increment_delay_multiplex is set, after an OK packet with last_insert_id is received, multiplexing is temporary disabled for the same number of queries as specified in mysql-auto_increment_delay_multiplex.
Note that disabling multiplexing doesn't disable routing, so it is important to configure read/write split correctly.

System Variable	Name	mysql-auto_increment_delay_multiplex
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (count)
	Default	5
	Minimum	0
	Maximum	1000000

`mysql-autocommit_false_is_transaction`

If mysql-autocommit_false_is_transaction=true (false by default), a connection with autocommit=0 is treated as a transaction. If forward_autocommit=true (false by default), the same behavior applies.

System Variable	Name	mysql-autocommit_false_is_transaction
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`mysql-autocommit_false_not_reusable`

When set to true, a connection with autocommit=0 is not re-used and is destroyed when the connection is returned to the connection pool.

System Variable	Name	mysql-autocommit_false_not_reusable
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`mysql-binlog_reader_connect_retry_msec`

Controls the connect retry timeout for the binlog reader (introduced in ProxySQL 2.0).

System Variable	Name	mysql-binlog_reader_connect_retry_msec
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	3000
	Minimum	200
	Maximum	120000

`mysql-client_found_rows`

When set to true, client flag CLIENT_FOUND_ROWS is set when connecting to MySQL backends.

System Variable	Name	mysql-client_found_rows
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`mysql-client_multi_statements`

When set to true, client flag CLIENT_MULTI_STATEMENTS is set when connecting to MySQL backends.

System Variable	Name	mysql-client_multi_statements
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

### `mysql-client_session_track_gtid` When activate ProxySQL will keep track of the GTID status on the backend serves in the stats_mysql_gtid_executed table

System Variable	Name	mysql-client_session_track_gtid
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`mysql-commands_stats`

Enable per-command MySQL query statistics. A command is a type of SQL query that is being executed. Some examples are: SELECT, INSERT or ALTER TABLE.

System Variable	Name	mysql-commands_stats
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`mysql-connect_retries_delay`

The delay (in milliseconds) before trying to reconnect after a failed attempt to a backend MySQL server. Failed attempts can take place due to numerous reasons: too busy, timed out for the current attempt, etc. This will be retried for mysql-connect_retries_on_failure times.

System Variable	Name	mysql-connect_retries_delay
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	1
	Minimum	0
	Maximum	10000

`mysql-connect_retries_on_failure`

The number of times for which a reconnect should be attempted in case of an error, timeout, or any other event that led to an unsuccessful connection to a backend MySQL server. After the number of attempts is depleted, if a connection still couldn't be established, an error is returned. The error returned is either the last connection attempt error or a generic error ("Max connect failure while reaching hostgroup" with error code 28000).

Be careful about tweaking this parameter - a value that is too high can significantly increase the latency which with an unresponsive hostgroup is reported to the MySQL client.

System Variable	Name	mysql-connect_retries_on_failure
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	10
	Minimum	0
	Maximum	1000

`mysql-connect_timeout_server`

The timeout for a single attempt at connecting to a backend server from the proxy. If this fails, according to the other parameters, the attempt will be retried until too many errors per second are generated (and the server is automatically shunned) or until the final cut-off is reached and an error is returned to the client (see mysql-connect_timeout_server_max).

System Variable	Name	mysql-connect_timeout_server
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	1000
	Minimum	10
	Maximum	120000

`mysql-connect_timeout_server_max`

The timeout for connecting to a backend server from the proxy. When this timeout is reached, an error is returned to the client with code 9001 and the message "Max connect timeout reached while reaching hostgroup...".

`mysql-connection_delay_multiplex_ms`

Disable multiplexing for a short period of time on a connection, this will allow a frontend connection to re-use the same backend connection for successive queries (e.g. when batching queries). The delay is measured for the time there is no activity on the connection.

System Variable	Name	mysql-connection_delay_multiplex_ms
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	0
	Minimum	0
	Maximum	300000

`mysql-connection_max_age_ms`

When mysql-connection_max_age_ms is set to a value greater than 0, inactive connections in the connection pool (therefore not currently used by any session) are closed if they were created more than mysql-connection_max_age_ms milliseconds ago. By default, connections aren't closed based on their age.
When mysql-connection_max_age_ms is reached, connections are simply disconnected, without sending COM_QUIT command to the server, so this might result in 'Aborted connection' warnings showing up in your MySQL server logs (this behaviour is intended, see #1861).

System Variable	Name	mysql-connection_max_age_ms
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	0
	Minimum	0
	Maximum	86400000

`mysql-connpoll_reset_queue_length`

PoxySQL 1.4.0 introduced a background thread (HGCU_thread_run()) responsible for resetting connections instead of dropping them when MySQL_HostGroups_Manager::destroy_MyConn_from_pool() is called. There could be cases in which this behavior is not beneficial. In ProxySQL 1.4.4 mysql-connpoll_reset_queue_length allows this behavior to be configurable by destroying the connection when the defined threshold is reached.

System Variable	Name	mysql-connpoll_reset_queue_length
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	50
	Minimum	0
	Maximum	1000

`mysql-default_charset`

The default server charset to be used in the communication with the MySQL clients. Note that this is the defult for client connections, not for backend connections.

System Variable	Name	mysql-default_charset
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	utf8
	Valid Values	Run "select * from mysql_collations;" in the Admin interface to view the full list

`mysql-default_character_set_results`

The character set used for returning query results to the client. This includes result data such as column values, result metadata such as column names, and error messages.

System Variable	Name	mysql-default_character_set_results
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	NULL
	Valid Values

mysql-default_collation_connection

The collation of the connection character set.

System Variable	Name	mysql-default_collation_connection
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	NULL
	Valid Values

`mysql-default_isolation_level`

The default transaction isolation level. Very important: SESSION is mandatory, SET TRANSACTION ISOLATION LEVEL value is not supported and will disable multiplexing

mysql-default_isolation_level	Name	mysql-default_collation_connection
mysql-default_isolation_level	Dynamic	Yes
Permitted Values	Type	String
	Default	READ COMMITTED
	Valid Values	ONLY at SESSION level: REPEATBALE READ, READ COMMITTED, SERIALIZED

`mysql-default_max_join_size`

Do not permit statements that probably need to examine more than max_join_size rows (for single-table statements) or row combinations (for multiple-table statements) or that are likely to do more than max_join_size disk seeks. By setting this value, you can catch statements where keys are not used properly and that would probably take a long time. Set it if your users tend to perform joins that lack a WHERE clause, that take a long time, or that return millions of rows. Default: 18446744073709551615

System Variable	Name	mysql-default_max_join_size
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	18446744073709551615
	Minimum	0
	Maximum	18446744073709551615

`mysql-default_max_latency_ms`

ProxySQL uses a mechanism to automatically ignore hosts if their latency is excessive. Note that hosts are not disabled, but only ignored: in other words, ProxySQL will prefer hosts with a smaller latency. It is possible to configure the maximum latency for each backend from mysql_servers table, column max_latency_ms. If mysql_servers.max_latency_ms is 0, the default value mysql-default_max_latency_ms applies.

System Variable	Name	mysql-default_max_latency_ms
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	1000
	Minimum	0
	Maximum	1728000000

Note: due to a limitation in SSL implementation, it is recommended to increase mysql-default_max_latency_ms if using SSL.

`mysql-default_net_write_timeout`

The number of seconds to wait for a block to be written to a connection before aborting the write

System Variable	Name	mysql-default_net_write_timeout
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (seconds)
	Default	60
	Minimum	0
	Maximum

`mysql-default_query_delay`

Simple throttling mechanism for queries to the backends. Setting this variable to a non-zero value (in miliseconds) will delay the execution of all queries, globally. There is a more fine-grained throttling mechanism in the admin table mysql_query_rules, where for each rule there can be one delay that is applied to all queries matching the rule. That extra delay is added on top of the default, global one.

System Variable	Name	mysql-default_query_delay
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	0
	Minimum	0
	Maximum	3600000

`mysql-default_query_timeout`

Mechanism for specifying the maximal duration of queries to the backend MySQL servers until ProxySQL should return an error to the MySQL client. Whenever ProxySQL detects that a query has timed out, it will spawn a separate thread that runs a KILL query against the specific MySQL backend in order to stop the query from running in the backend. Because the query is killed, an error will be returned to the MySQL client.

System Variable	Name	mysql-default_query_timeout
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	86400000
	Minimum	1000
	Maximum	1728000000

`mysql-default_reconnect`

Not used for now.

`mysql-default_schema`

The default schema to be used for incoming MySQL client connections which do not specify a schema name. This is required because ProxySQL doesn't allow connection without a schema.

System Variable	Name	mysql-default_schema
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	information_schema
	Valid Values	Any existing schema

mysql-default_session_track_gtids

Controls whether the server tracks GTIDs within the current session and returns them to the client. Depending on the variable value, at the end of executing each transaction, the server GTIDs are captured by the tracker and returned to the client.

System Variable	Name	mysql-default_session_track_gtids
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	OFF
	Valid Values	valid only at SESSION scope OFF: The tracker collects no GTIDs. This is the default. OWN_GTID: The tracker collects GTIDs generated by successfully committed read/write transactions. ALL_GTIDS: Not supported in ProxySQL

`mysql-default_sql_auto_is_null`

If this variable is enabled, then after a statement that successfully inserts an automatically generated AUTO_INCREMENT value, you can find that value by issuing a statement of the following form: SELECT * FROM tbl_name WHERE auto_col IS NULL If the statement returns a row, the value returned is the same as if you invoked the LAST_INSERT_ID() function. For details, including the return value after a multiple-row insert, see Section 12.15, “Information Functions”. If no AUTO_INCREMENT value was successfully inserted, the SELECT statement returns no row.

System Variable	Name	mysql-default_sql_auto_is_null
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	OFF
	Valid Values	ON\|OFF

`mysql-default_sql_mode`

When a client requires a different sql_mode, ProxySQL needs to track the change to ensure that the needed sql_mode is the same on every backend connection used by that specific client. When ProxySQL establishes a new connection to a backend it doesn't know the current sql_mode. Although it is possible to query the backend to retrieve sql_mode and other variables, querying the backend has a latency cost. For this reason ProxySQL doesn't query the backend to know the value of sql_mode, and instead it assumes that all the backend connections have by default the sql_mode defined in mysql-default_sql_mode.

If a client changes sql_mode to a value different than mysql-default_sql_mode, ProxySQL will ensure to change sql_mode on every connection used by that client.
On the other hand, if a client set sql_mode to the same value specified in mysql-default_sql_mode, ProxySQL won't change the sql_mode on the backend connection because it assumes that the sql_mode is already correct.

A misconfigured mysql-default_sql_mode can lead to unexpected results. For example, if mysql-default_sql_mode='' (the default in ProxySQL, and also the default for MySQL <= 5.6.5) while the backend has sql_mode different than '', if a client executes set session sql_mode='' ProxySQL won't change the sql_mode on backend.

This variable needs to configured as the default sql_mode across all backends. If backends have different sql_mode or if you want ProxySQL to always enforce the sql_mode specified by the client, mysql-default_sql_mode can be configured using an invalid sql_mode. This will force ProxySQL to always change the sql_mode on backend to whatever value specific by the client.

System Variable	Name	mysql-default_sql_mode
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	''
	Valid Values	Any valid or invalid sql_mode

`mysql-default_sql_safe_updates`

If this variable is enabled, UPDATE and DELETE statements that do not use a key in the WHERE clause or a LIMIT clause produce an error. This makes it possible to catch UPDATE and DELETE statements where keys are not used properly and that would probably change or delete a large number of rows.

System Variable	Name	mysql-default_sql_safe_updates
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	OFF
	Valid Values	ON\|OFF

`mysql-default_sql_select_limit`

The maximum number of rows to return from SELECT statements. The default value for a new connection is the maximum number of rows that the server permits per table. Typical default values are (232)−1 or (264)−1. If you have changed the limit, the default value can be restored by assigning a value of DEFAULT. If a SELECT has a LIMIT clause, the LIMIT takes precedence over the value of sql_select_limit.

System Variable	Name	mysql-default_sql_select_limit
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	DEFAULT
	Valid Values	The default value for a new connection is the maximum number of rows that the server permits per table. Typical default values are (232)−1 or (264)−1. If you have changed the limit, the default value can be restored by assigning a value of DEFAULT.

`mysql-default_time_zone`

If a client doesn't specify any time_zone, the time zone assigned to the client is whatever time zone currently assigned to mysql-default_time_zone. When a client requires a different time_zone, ProxySQL needs to track the change to ensure that the needed time_zone is the same on every backend connection used by that specific client. When ProxySQL establishes a new connection to a backend it doesn't know the current time_zone. Although it is possible to query the backend to retrieve time_zone and other variables, querying the backend has a latency cost. For this reason ProxySQL doesn't query the backend to know the value of time_zone, and instead it assumes that all the backend connections have by default the time_zone defined in mysql-default_time_zone.

If a client changes time_zone to a value different than mysql-default_time_zone, ProxySQL will ensure to change time_zone on every connection used by that client.
On the other hand, if a client set time_zone to the same value specified in mysql-default_time_zone, ProxySQL won't change the time_zone on the backend connection because it assumes that the time_zone is already correct.

A misconfigured mysql-default_time_zone can lead to unexpected results so this variable needs to configured as the default time_zone across all backends. If backends have different time_zone or if you want ProxySQL to always enforce the time_zone specified by the client, mysql-default_time_zone can be configured using an invalid time_zone. This will force ProxySQL to always change the time_zone on backend to whatever value specific by the client.

System Variable	Name	mysql-default_time_zone
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	SYSTEM
	Valid Values	Any valid or invalid MySQL time_zone

`mysql-default_transaction_read`

ProxySQL tracks the transaction access modes, READ WRITE or READ ONLY clause. If manually set what is mandatory for ProxySQL is the SESSION scope definition: - SET SESSION TRANSACTION READ WRITE - SET SESSION TRANSACTION READ ONLY SET TRANSACTION READ (WRITE|ONLY) is not supported, and it will automatically disable multiplexing.

In MySQL by default, a transaction takes place in read/write mode, with both reads and writes permitted to tables used in the transaction. This mode may be specified explicitly using SET **SESSION** TRANSACTION with an access mode of READ WRITE.

System Variable	Name	mysql-default_transaction_read
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	WRITE
	Valid Values	READ\|WRITE

`mysql-default_tx_isolation`

ProxySQL support the change of the Transaction Isolation level ONLY at SESSION level. Any attempts to run command like SET TRANSACTION ISOLATION LEVEL value are not supported, and it will automatically disable multiplexing. Correct syntax is: SET SESSION TRANSACTION ISOLATION LEVEL value

System Variable	Name	mysql-default_tx_isolation
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	READ-COMMITTED
	Valid Values	READ COMMITTED, REPEATABLE READ, and SERIALIZABLE

`mysql-multiplexing`

Enable multiplexing by default. We recommend to leave multiplexing on (the default). Please note that multiplexing can still be disabled for other reasons, enabling this parameters makes sure it never enabled.

System Variable	Name	mysql-multiplexing
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`mysql-enforce_autocommit_on_reads`

System Variable	Name	mysql-enforce_autocommit_on_reads
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`mysql-eventslog_filename`

If this variable is set, ProxySQL will log all traffic to the specified filename. Note that the log file is not a text file, but a binary log with encoded traffic. The value of this variable can be set to an absolute pathname (e.g. "/data/events_log/events_log" or else a filename (e.g. "events_log") will be written to the defined data directory. A sequential number will always be suffixed in the file's extension (e.g. "events_log.00000001").

System Variable	Name	mysql-eventslog_filename
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	empty string, not set
	Valid Values	A filename or absolute path

`mysql-eventslog_filesize`

This variable specifies the maximum size of files created by ProxySQL logger as specified in mysql-eventslog_filename. When the maximum size is reached, the file is rotated.

System Variable	Name	mysql-eventslog_filesize
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (bytes)
	Default	104857600 (100MB)
	Minimum	1048576
	Maximum	1073741824

`mysql-eventslog_default_log`

ProxySQL is able to log queries that pass through. If there is no definition for Log in a matching rule in mysql_query_rules, mysql-eventslog_default_log applies. See also https://github.com/sysown/proxysql/wiki/Query-Logging

System Variable	Name	mysql-eventslog_default_log
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

mysql-eventslog_format

From version 2.0.6 ProxySQL can handle two different log formats: 1 : this is the default: queries are logged in binary format (like before 2.0.6) 2 : the queries are logged in JSON format. Default: 1 See also https://github.com/sysown/proxysql/wiki/Query-Logging

System Variable	Name	mysql-eventslog_format
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	1
	Valid Values	1 : this is the default: queries are logged in binary format (like before 2.0.6) 2 : the queries are logged in JSON format.

`mysql-forward_autocommit`

When mysql-forward_autocommit=false (the default), ProxySQL will track (and remember) the autocommit value that the client wants and change autocommit on a backend connection as needed. For example, if a client sends set autcommit=0, ProxySQL will just reply OK. When the client sends a DDL, proxysql will get a connection to target hostgroup, and change autocommit before running the DDL.

If mysql-forward_autocommit=true, SET autocommit=0 is forwarded to the backend. SET autocommit=0 doesn't start any transaction, the connection is set in the connection pool, and queries may execute on a different connection. If you set mysql-forward_autocommit=true, you should also set mysql-autocommit_false_not_reusable=true to prevent the connection to be returned to the connection pool. In other words, setting mysql-forward_autocommit=false will prevent this behaviour since the autocommit state is tracked.

System Variable	Name	mysql-forward_autocommit
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`mysql-free_connections_pct`

ProxySQL uses a connection pool to connect to backend servers.
Connections to backend are never pre-allocated if there is no need, so at start up there will 0 connections to the backend.
When application starts sending traffic to proxysql, this identifies to which backend if needs to send traffic. If there is a connection in the connection pool for that backend, that connection is used, otherwise a new connection is created. When the connection completes serving the client's request, it is sent back to the the Hostgroup Manager. If the Hostgroup Manager determines that the connection is safe to share and the connection pool isn't full, it will place it in the connection pool. Although, not all the unused connections are kept in the connection pool.
This variable controls the percentage of open idle connections from the total maximum number of connections for a specific server in a hostgroup.
For each hostgroup/backend pair, the Hostgroup Manager will keep in the connection pool up to mysql-free_connections_pct * mysql_servers.max_connections / 100 connections . Connections are kept open with periodic pings.

A connection is idle if it hasn't used since the last round of pings. The time interval between two such rounds of pings for idle connections is controlled by the variable mysql-ping_interval_server_msec.

System Variable	Name	mysql-free_connections_pct
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (percentage)
	Default	10
	Minimum	0
	Maximum	100

<a name=""mysql-have_compress>`mysql-have_compress`

Currently unused.

`mysql-have_ssl`

Introduced in ProxySQL v2.0, enables frontend SSL support (see SSL Support for more information).

System Variable	Name	mysql-have_ssl
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`mysql-hostgroup_manager_verbose`

Enable verbose logging of hostgroup manager details in ProxySQL logs (e.g. when running LOAD MYSQL SERVERS TO RUNTIME).

System Variable	Name	mysql-hostgroup_manager_verbose
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	1
	Minimum	0
	Maximum	2

`mysql-init_connect`

String containing one or more SQL statements, separated by semicolons, that will be executed by the ProxySQL for each backend connection when created or initialised e.g. SET WAIT_TIMEOUT=28800 (works similarly to MySQL's init_connect variable).

System Variable	Name	mysql-init_connect
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	empty string, not set
	Valid Values	String containing one or more SQL statements, separated by semicolons

`mysql-interfaces`

Semicolon-separated list of hostname:port entries for interfaces for incoming MySQL traffic. Note that this also supports UNIX domain sockets for the cases where the connection is done from an application on the same machine.
Note that changing this value has no effect at runtime, if you need to change it you have to restart the proxy.
After changing mysql-interfaces, you should not run LOAD MYSQL VARIABLES TO RUNTIME because this variable cannot be loaded at runtime. Attempt to load them at runtime will cause their reset.
In other words, after changing mysql-interfaces, you need to run SAVE MYSQL VARIABLES TO DISK and then restart proxysql (for example using PROXYSQL RESTART).

System Variable	Name	mysql-interfaces
System Variable	Dynamic	No
Permitted Values	Type	String
	Default	0.0.0.0:6033;/tmp/proxysql.sock
	Valid Values	IP / hostname with ':' seperated port and ';' separated socket filename

`mysql-keep_multiplexing_variables`

Defines a list of variables that do not causes multiplexing to be disabled if queries. For example "SELECT @@version", by default proxysql would disable multiplexing. But because "version" is in mysql-keep_multiplexing_variables, multiplexing is not disabled. The list is define in ProxySQL and is not dynamic. Default: trx_isolation,version

`mysql-max_stmts_cache`

Set the total maximum number of statemnts that can be cached when using Prepare Statement

System Variable	Name	mysql-max_stmts_cache/td>
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	10000

`mysql-kill_backend_connection_when_disconnect`

When enabled the backend connection for a client connection is killed when the client disconnects (introduced in ProxySQL v2.0).

System Variable	Name	mysql-kill_backend_connection_when_disconnect
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`mysql-long_query_time`

Threshold for counting queries passing through the proxy as 'slow'. The total number of slow queries can be found in the stats_mysql_global table, in the variable named Slow_queries (each row in that table represents one variable).

System Variable	Name	mysql-long_query_time
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	1000
	Minimum	0
	Maximum	1728000000

`mysql-max_allowed_packet`

mysql-max_allowed_packet defines the maximum size of a single packet/command received by the client. It mimics the behavior of mysqld's max_allowed_packet

System Variable	Name	mysql-max_allowed_packet
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (bytes)
	Default	4194304 (4MB)
	Minimum	8192 (8KB)
	Maximum	1073741824 (1GB)

`mysql-max_connections`

The maximum number of client connections that the proxy can handle. After this number is reached, new connections will be rejected with the #HY000 error, and the error message Too many connections.

System Variable	Name	mysql-max_connections
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	2048
	Minimum	1
	Maximum	1000000

`mysql-max_stmts_per_connection`

The threshold for the number of statements that can be prepared on a backend connection before that connection is closed (prior to version 1.4.3) or reset (starting version 1.4.4). This is evaluated when a connection is returned to the connection pool.

System Variable	Name	mysql-max_stmts_per_connection
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	20
	Minimum	1
	Maximum	1024

`mysql-max_transaction_time`

Sessions with active transactions running more than this timeout are killed.

System Variable	Name	mysql-max_transaction_time
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	14400000 (4 hours)
	Minimum	1000
	Maximum	1728000000

`mysql-min_num_servers_lantency_awareness`

Latency awareness is an algorithm uses to send traffic only to closest backends. IE: In case of slaves in multiple AZs, ProxySQL will sends traffic only to the slaves on the same AZ. But to trigger this algorithm, a minimum number of servers is required. In case of 3 slaves in 3 AZs, and application/ProxySQL is in one AZ, you MAY not want to send almost all the traffic to only one slave.

System Variable	Name	mysql-min_num_servers_lantency_awarenes
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	1000

`mysql-mirror_max_concurrency`

ToDo

System Variable	Name	mysql-mirror_max_concurrency
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	16
	Minimum	1
	Maximum	8192

`mysql-mirror_max_queue_length`

ToDo

System Variable	Name	mysql-mirror_max_queue_length
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	32000
	Minimum	0
	Maximum	1048576

`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.

System Variable	Name	mysql-monitor_connect_interval
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	120000 (2 mins)
	Minimum	100
	Maximum	604800000

`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.

System Variable	Name	mysql-monitor_connect_timeout
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	200
	Minimum	100
	Maximum	600000

`mysql-monitor_enabled`

It enables or disables MySQL Monitor.

System Variable	Name	mysql-monitor_enabled
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`mysql-monitor_galera_healthcheck_interval`

The interval at which the proxy should connect to the backend servers in order to monitor the Galera staus 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, at a per-hostgroup level (introduced in ProxySQL v2.0).

System Variable	Name	mysql-monitor_galera_healthcheck_timeout
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	5000
	Minimum	50
	Maximum	604800000

`mysql-monitor_galera_healthcheck_max_timeout_count`

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

System Variable	Name	mysql-monitor_galera_healthcheck_max_timeout_count
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	3

`mysql-monitor_galera_healthcheck_timeout`

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

System Variable	Name	mysql-monitor_galera_healthcheck_timeout
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	800
	Minimum	50
	Maximum	600000

`mysql-monitor_groupreplication_healthcheck_interval`

The interval at which the proxy should connect to the backend servers in order to monitor the Group Replication staus 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, at a per-hostgroup level.

System Variable	Name	mysql-monitor_groupreplication_healthcheck_interval
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	5000
	Minimum	50
	Maximum	604800000

`mysql-monitor_groupreplication_healthcheck_timeout`

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

System Variable	Name	mysql-monitor_groupreplication_healthcheck_timeout
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	800
	Minimum	50
	Maximum	600000

`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

System Variable	Name	mysql-monitor_history
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	600000 (60 seconds)
	Minimum	1000
	Maximum	604800000

`mysql-monitor_password`

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

System Variable	Name	mysql-monitor_password
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	monitor

`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).

System Variable	Name	mysql-monitor_ping_interval
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	8000
	Minimum	100
	Maximum	604800000

`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.

System Variable	Name	mysql-monitor_ping_max_failures
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	3
	Minimum	1
	Maximum	1000000

`mysql-monitor_ping_timeout`

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

System Variable	Name	mysql-monitor_ping_timeout
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	1000
	Minimum	100
	Maximum	600000

`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.

System Variable	Name	mysql-monitor_query_interval
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	60000 (1 min)
	Minimum	100
	Maximum	604800000

`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.

System Variable	Name	mysql-monitor_query_timeout
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	100

`mysql-monitor_read_only_interval`

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

System Variable	Name	mysql-monitor_read_only_interval
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	1000 (1 sec)
	Minimum	100
	Maximum	604800000

`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 slave to OFFLINE HARD.

System Variable	Name	mysql-monitor_read_only_max_timeout_count
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	3
	Minimum	1
	Maximum	999999

`mysql-monitor_read_only_timeout`

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

System Variable	Name	mysql-monitor_read_only_timeout
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	800
	Minimum	100
	Maximum	600000

`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.

System Variable	Name	mysql-monitor_replication_lag_interval
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	10000
	Minimum	100
	Maximum	604800000

`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.

System Variable	Name	mysql-monitor_replication_lag_timeout
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	1000
	Minimum	100
	Maximum	600000

`mysql-monitor_replication_lag_use_percona_heartbeat`

This variable defines the <schema>.<table> 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.

System Variable	Name	mysql-monitor_replication_lag_use_percona_heartbeat
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default

`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 allow to either shun or keep online a server where replication is broken/stopped.

System Variable	Name	mysql-monitor_slave_lag_when_null
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (seconds)
	Default	60
	Minimum (up to 1.3.1)	100
	Minimum (from 1.3.2 onwards)	0
	Maximum	604800 (1 week)

`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.

System Variable	Name	mysql-monitor_threads_max
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	128
	Minimum (from 1.3.2 onwards)	4
	Maximum	256

`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.

System Variable	Name	mysql-monitor_threads_min
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	8
	Minimum (from 1.3.2 onwards)	2
	Maximum	16

`mysql-monitor_threads_queue_maxsize`

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

System Variable	Name	mysql-monitor_threads_queue_maxsize
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	128
	Minimum	16
	Maximum	1024

(introduced in ProxySQL v2.0)

`mysql-monitor_timer_cached`

DEPRECATED

This variable controls whether ProxySQL should use a cached (and less accurate) value of wall clock time, or not. The actual API used for this is described here.

System Variable	Name	mysql-monitor_timer_cached
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`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.

System Variable	Name	mysql-monitor_username
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	monitor

`mysql-monitor_wait_timeout`

In order to avoid being disconnected the Monitor Module tunes wait_timeout on its connections to backend. 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.

System Variable	Name	mysql-monitor_wait_timeout
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`mysql-monitor_writer_is_also_reader`

When a node change 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 stay also in reader_hostgroup

System Variable	Name	mysql-monitor_writer_is_also_reader
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`mysql-ping_interval_server_msec`

The interval at which the proxy should ping backend connections in order to maintain them alive, even though there is no outgoing traffic. The purpose here is to keep some connections alive in order to reduce the latency of new queries towards a less frequently used destination backend server.

System Variable	Name	mysql-ping_interval_server_msec
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	10000
	Minimum	1000
	Maximum	604800000

`mysql-ping_timeout_server`

The proxy internally pings the connections it has opened in order to keep them alive. This eliminates the cost of opening a new connection towards a hostgroup when a query needs to be routed, at the cost of additional memory footprint inside the proxy and some extra traffic. This is the timeout allowed for those pings to succeed.

System Variable	Name	mysql-ping_timeout_server
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	200
	Minimum	10
	Maximum	600000

`mysql-poll_timeout`

The minimal timeout used by the proxy in order to detect incoming/outgoing traffic via the poll() system call. If the proxy determines that it should stick to a higher timeout because of its internal computations, it will use that one, but it will never use a value less than this one.

System Variable	Name	mysql-poll_timeout
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	2000
	Minimum	10
	Maximum	20000

`mysql-poll_timeout_on_failure`

The timeout used in order to detect incoming/outgoing traffic after a connection error has occured. The proxy automatically tweaks its timeout to a lower value in such an event in order to be able to quickly respond with a valid connection.

System Variable	Name	mysql-poll_timeout_on_failure
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	100
	Minimum	10
	Maximum	20000

`mysql-query_cache_size_MB`

The total amount of memory used by the Query Cache, note: the current implementation of mysql-query_cache_size_MB doesn't impose a hard limit . Instead, it is used as an argument by the purging thread.

System Variable	Name	mysql-query_cache_size_MB
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (MB)
	Default	256
	Minimum	0
	Maximum	10485760

`mysql-query_cache_stores_empty_result`

The variable controls if resultset without rows will be cached or not (introduced in ProxySQL v2.0).

System Variable	Name	mysql-query_cache_stores_empty_result
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`mysql-query_digests`

When this variable is set to true, the proxy analyzes the queries passing through it and divides them into classes of queries having different values for the same parameters. It computes a couple of metrics for these classes of queries, all found in the stats_mysql_query_digest table. For more details, please refer to the admin tables documentation.
It is also very important to note that query digest is required to determine when multiplexing needs to be disabled, for example in case of TEMPORARY tables, SQL_CALC_FOUND_ROWS , GET_LOCK, etc.
Do not disable mysql-query_digests unless you are really sure it won't break your application.

System Variable	Name	mysql-query_digests
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`mysql-query_digests_lowercase`

When this variable is set to true, query digest is automatically converted to lowercase otherwise when false, query digests are case sensitive.

System Variable	Name	mysql-query_digests_lowercase
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`mysql-query_digests_max_digest_length`

Defines the maximum length of digest_text as then reported in stats_mysql_query_digest

System Variable	Name	mysql-query_digests_max_digest_length
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	2048
	Minimum	16
	Maximum (up to 1.3.1)	65000
	Maximum (from 1.3.2 onwards)	1048576

`mysql-query_digests_max_query_length`

Defines the maximum query length processed when computing query's digest and digest_text

System Variable	Name	mysql-query_digests_max_query_length
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	65000
	Minimum	16
	Maximum (up to 1.3.1)	65000
	Maximum (from 1.3.2 onwards)	16777216

`mysql-query_digests_no_digits`

When active ProxySQL will replace all numbers in the query to '?' signs for generating digest. This functionality can be controlled by Testing was done with queries like this: CALL 76_char_14.gen_v67_chr_camp_item_upsert_5();

System Variable	Name	mysql-query_digests_no_digits
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`mysql-query_digests_normalize_digest_text`

When set to FALSE (default), ProxySQL will cache the SQL digest and related information in the table stats.stats_mysql_query_digest by schema. When this variable is TRUE, queries statistics store digest_text on a different internal hash table. In this way ProxySQL will be able to normalize data, digest_text is internally stored elsewhere, and it deduplicate data. When you query stats_mysql_query_digest, the data is merged together. This drastically reduces memory usage on setups with many schemas but similar queries patterns

System Variable	Name	mysql-query_digests_normalize_digest_text
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`mysql-query_digests_replace_null`

When TRUE, ProxySQL will replace NULLs when creating the Query digest with '?'. This approach will normalize statements like the following:

    SQL                                       Digest
    INSERT INTO tablename(id) VALUES (1);     INSERT INTO tablename(id) VALUES (?);
    INSERT INTO tablename(id) VALUES (NULL);  INSERT INTO tablename(id) VALUES (?);
    CALL spa(NULL, null, NULL, null);         CALL spa(?, ?, ?, ?);
    CALL spa(1, null, NULL, 4);               CALL spa(?, ?, ?, ?);
    CALL spa(1, 2, 3, 4);                     CALL spa(?, ?, ?, ?);

System Variable	Name	mysql-query_digests_replace_null
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`mysql-query_digests_track_hostname`

If active it reports the original client address in the table stats_mysql_query_digest See also https://github.com/sysown/proxysql/wiki/STATS-(statistics)

System Variable	Name	mysql-query_digests_track_hostname
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`mysql-query_processor_iterations`

If mysql_query_rules.flagOUT is set and mysql-query_processor_iterations is greater than 0, a matching rule will set flagIN and starts processing rules from the beginning up to mysql-query_processor_iterations iterations.
Therefore, mysql-query_processor_iterations allows to jump back to previous mysql_query_rules.

System Variable	Name	mysql-query_processor_iterations
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	0
	Minimum	0
	Maximum	1000000

`mysql-query_processor_regex`

This variable defines which regex engine to use:

1 : PCRE
2 : RE2

Before version v1.4.0, only RE2 was available, CASELESS was always enabled, and GLOBAL was always disabled.
Starting from v1.4.0, both PCRE and RE2 are available. Now both PCRE and RE2 support CASELESS and GLOBAL using re_modifiers.
Although, RE2 doesn't support both CASELESS and GLOBAL at the same time if they are both configured in re_modifiers. For this reason, the default regex engine was changed to PCRE.

System Variable	Name	mysql-query_processor_regex
	Dynamic	Yes
Permitted Values	Type	Integer
	Default	PCRE	1
	Valid Values	PCRE	1
		RE2	2

`mysql-query_retries_on_failure`

In case of failures while running a query, the same can be retried mysql-query_retries_on_failure times.

System Variable	Name	mysql-query_retries_on_failure
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	1
	Minimum	0
	Maximum	1000

`mysql-reset_connection_algorithm`

When reset_connection_algorithm = 2, MySQL_Thread itself tries to reset connections instead of relying on connections purger HGCU_thread_run() (introduced in ProxySQL v2.0), reset_connection_algorithm can be set to:

1 = legacy algorithm used in ProxySQL v1.x
2 = algorithm new since ProxySQL v2.0 (new default)

System Variable	Name	mysql-reset_connection_algorithm
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	2
	Minimum	1
	Maximum	2

`mysql-server_capabilities`

The bitmask of MySQL capabilities (encoded as bits) with which the proxy will respond to clients connecting to it.
This is useful in order to prevent certain features from being used, although it is planned to be deprecated in the future.
The default capabilities are:

server_capabilities = CLIENT_FOUND_ROWS | CLIENT_PROTOCOL_41 | CLIENT_IGNORE_SIGPIPE | CLIENT_TRANSACTIONS | CLIENT_SECURE_CONNECTION | CLIENT_CONNECT_WITH_DB | CLIENT_SSL;

More details about server capabilities in the official documentation.

System Variable	Name	mysql-server_capabilities
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	47626
	Minimum	10
	Maximum	65535

`mysql-server_version`

The server version with which the proxy will respond to the clients. Note that regardless of the versions of the backend servers, the proxy will respond with this.

System Variable	Name	mysql-server_version
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default	5.5.30

`mysql-servers_stats`

Currently unused. Will be removed in a future version.

System Variable	Name	mysql-servers_stats
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`mysql-session_debug`

DEPRECATED

System Variable	Name	mysql-session_debug
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`mysql-session_idle_ms`

Starting from v1.3.0 , each MySQL_Thread has an auxiliary thread that is responsible to handle idle sessions (client connections). mysql-session_idle_ms defines when a session is idle and passed from the main thread to the auxiliary thread.

System Variable	Name	mysql-session_idle_ms
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	1000
	Minimum	100
	Maximum	3600000

`mysql-session_idle_show_processlist`

mysql-session_idle_show_processlist defines if in idle session (as defined by mysql-session_idle_ms) should be listed in SHOW PROCESSLIST (or in general, in stats_mysql_processlist table). For performance reason, idle sessions are not listed by default.

System Variable	Name	mysql-session_idle_show_processlist
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`mysql-sessions_sort`

Sessions are conversations between a MySQL client and a backend server in the proxy. Sessions are generally processed in a stable order but in certain scenarios (like using a transaction workload, which makes sessions bind to certain MySQL connections from the pool), processing them in the same order leads to starvation.

This variable controls whether sessions should be processed in the order of waiting time, in order to have a more balanced distribution of traffic among sessions.

System Variable	Name	mysql-sessions_sort
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`mysql-set_query_lock_on_hostgroup`

When active (default from 2.0.6), if SET statement is used in multi-statements commands or if parsing of SET statement it is not successful, both multiplexing and query routing is disabled. The client will remain bind to a single backend connections. Any SET statement that ProxySQL doesn't understands will disables multiplexing and routing.

System Variable	Name	mysql-set_query_lock_on_hostgroup
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	true

`mysql-show_processlist_extended`

When active ProxySQL will show extended information in JSON format about the processes running. Information will be available in stats_mysql_processlist.extended_info

System Variable	Name	mysql-show_processlist_extended/td>
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

### `mysql-shun_on_failures`

The number of connection errors tolerated to the same server within an interval of 1 second until it is automatically shunned temporarily. For now, this can not be disabled by setting it to a special value, so if you want to do that, you can increase it to a very large value.

System Variable	Name	mysql-shun_on_failures
System Variable	Dynamic	Yes
Permitted Values	Type	Integer
	Default	5
	Minimum	0
	Maximum	10000000

`mysql-shun_recovery_time_sec`

A backend server that has been automatically shunned will be recovered after at least this amount of time.
Note that if ProxySQL isn't handling client traffic, there is no actual hard guarantee of the exact timing, but in practice it shouldn't exceed this value by more than a couple of seconds.

Self tuning:

mysql-shun_recovery_time_sec should always be less than mysql-connect_timeout_server_max/1000 , in order to prevent that a server is taken out for so long that an error is returned to the client. If mysql-shun_recovery_time_sec > mysql-connect_timeout_server_max/1000 , the smaller of the two is used. (see #530)
if only one server is present in a hostgroup and mysql-shun_recovery_time_sec > 1 , the server is automatically brought back online after 1 second

System Variable	Name	mysql-shun_recovery_time_sec
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (seconds)
	Default	10
	Minimum	0
	Maximum	31536000

`mysql-ssl_p2s_ca`

SSL CA to be used for backend connections.

System Variable	Name	mysql-ssl_p2s_ca
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default

`mysql-ssl_p2s_cert`

SSL Certificate to be used for backend connections.

System Variable	Name	mysql-ssl_p2s_cert
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default

`mysql-ssl_p2s_cipher`

SSL Cipher to be used for backend connections (MySQL CIPHER list can be found here).

System Variable	Name	mysql-ssl_p2s_cipher
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default

`mysql-ssl_p2s_key`

SSL Key to be used for backend connections.

System Variable	Name	mysql-ssl_p2s_key
System Variable	Dynamic	Yes
Permitted Values	Type	String
	Default

`mysql-stacksize`

The stack size to be used with the background threads that the proxy uses to handle MySQL traffic and connect to the backends. Note that changing this value has no effect at runtime, if you need to change it you have to restart the proxy.

System Variable	Name	mysql-stacksize
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (bytes)
	Default	1048576
	Minimum	262144
	Maximum	4194304

`mysql-stats_time_backend_query`

Enables / disables collection of backend query CPU time statistics.

System Variable	Name	mysql-stats_time_backend_query
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default (up to 1.4.3)	true
	Default (from 1.4.4 onwards)	false

`mysql-stats_time_query_processor`

Enables / disables collection of query processor CPU time statistics.

System Variable	Name	mysql-stats_time_query_processor
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default (up to 1.4.3)	true
	Default (from 1.4.4 onwards)	false

`mysql-tcp_keepalive_time`

When mysql-use_tcp_keepalive is active, ProxySQL will send a KeepAlive to the destination every tcp_keepalive_time in seconds

System Variable	Name	mysql-tcp_keepalive_time
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (seconds)
	Default	0

`mysql-use_tcp_keepalive`

When active ProxySQL will send KeepAlive signal during the client open session.

System Variable	Name	mysql-tcp_keepalive_time
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`mysql-threads`

The number of background threads that ProxySQL uses in order to process MySQL traffic. Note that there are other "administrative" threads on top of these, such as:

the admin interface thread
the monitoring module threads that interact with the backend servers (one for monitoring connectivity, one for pinging the servers and one for monitoring the replication lag)
occasional temporary threads created in order to kill long running queries that have become unresponsive
background threads used by the libmariadbclient library in order to make certain interactions with MySQL servers async

Note that changing this value has no effect at runtime, if you need to change it you have to restart the proxy.
After changing mysql-threads, you should not run LOAD MYSQL VARIABLES TO RUNTIME because this variable cannot be loaded at runtime. Attempt to load them at runtime will cause their reset.
In other words, after changing mysql-threads, you need to run SAVE MYSQL VARIABLES TO DISK and then restart proxysql (for example using PROXYSQL RESTART).

System Variable	Name	mysql-threads
System Variable	Dynamic	No
Permitted Values	Type	Integer
	Default	4
	Minimum	1
	Maximum	255

`mysql-threshold_query_length`

The maximal size of an incoming SQL query to the proxy that will mark the background MySQL connection as non-reusable. This will force the proxy to open a new connection to the backend server, in order to make sure that the memory footprint of the server stays within reasonable limits.

More details about it here: https://dev.mysql.com/doc/refman/5.6/en/memory-use.html

Relevant quote from the mysqld documentation: "The connection buffer and result buffer each begin with a size equal to net_buffer_length bytes, but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length bytes after each SQL statement."

System Variable	Name	mysql-threshold_query_length
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (bytes)
	Default	524288
	Minimum	1024
	Maximum	1073741824

`mysql-threshold_resultset_size`

If a resultset returned by a backend server is bigger than this, proxysql will start sending the result to the MySQL client that was requesting the result in order to limit its memory footprint.

System Variable	Name	mysql-threshold_resultset_size
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (bytes)
	Default	4194304 (4MB)
	Minimum	1024
	Maximum	1073741824

Default value: 4194304 (bytes, the equivalent of 4 MB)

System Variable	Name	mysql-verbose_query_error
System Variable	Dynamic	Yes
Permitted Values	Type	Boolean
	Default	false

`mysql-wait_timeout`

If a proxy session (which is a conversation between a MySQL client and a ProxySQL) has been idle for more than this threshold, the proxy will kill the session.

System Variable	Name	mysql-wait_timeout
System Variable	Dynamic	Yes
Permitted Values	Type	Integer (milliseconds)
	Default	28800000 (8 hours)
	Minimum	0
	Maximum	1728000000