Global variables

René Cannaò edited this page Jul 14, 2018 · 44 revisions

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 2 variables that cannot be changed at runtime - 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-"

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.

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
Dynamic Yes
Permitted Values Type String
Default admin:admin

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
Dynamic No
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
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
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
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
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
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
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
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
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
Dynamic Yes
Permitted Values Type Integer (seconds)
Default 60
Valid values 5, 10, 30, 60, 120, 300

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
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
Dynamic Yes
Permitted Values Type Integer
Default 6080

MySQL Variables

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
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
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
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
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
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 #28000 and the message "Max connect timeout reached while reaching hostgroup...".

See also mysql-shun_recovery_time_sec

System Variable Name mysql-connect_timeout_server_max
Dynamic Yes
Permitted Values Type Integer (milliseconds)
Default 10000
Minimum 10
Maximum 3600000

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

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

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
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_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
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_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
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
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
Dynamic Yes
Permitted Values Type String
Default information_schema
Valid Values Any existing schema

mysql-default_sql_mode

When a client requires to change 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 establish 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.
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
Dynamic Yes
Permitted Values Type String
Default ''
Valid Values Any valid or invalid sql_mode

mysql-enforce_autocommit_on_reads

ProxySQL tracks the status of autocommit as specified by the client, and ensures that autocommit is set correct on backend connections. This implementation is problematic if a client starts a transaction using autocommit, and read/write split is implemented via query rules. In fact, if a read is sent to a slave and ProxySQL sets autocommit=0 on slave, this will result in 2 transactions (one on master and one on slave). To prevent this to happen, if mysql-enforce_autocommit_on_reads=false (the default), ProxySQL won't change the value of autocommit on backend connections for SELECT stataments.

System Variable Name mysql-enforce_autocommit_on_reads
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 encodided 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
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
Dynamic Yes
Permitted Values Type Integer (bytes)
Default 104857600 (100MB)
Minimum 1048576
Maximum 1073741824

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
Dynamic Yes
Permitted Values Type Integer (percentage)
Default 10
Minimum 0
Maximum 100

mysql-have_compress

Currently unused.

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.

System Variable Name mysql-interfaces
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-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
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
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
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
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
Dynamic Yes
Permitted Values Type Integer (milliseconds)
Default 14400000 (4 hours)
Minimum 1000
Maximum 1728000000

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
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 rouding is done because SSL connections are blocking calls.

System Variable Name mysql-monitor_connect_timeout
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
Dynamic Yes
Permitted Values Type Boolean
Default true

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
Dynamic Yes
Permitted Values Type Integer (milliseconds)
Default 600000 (100 seconds)
Minimum 1000
Maximum 604800000

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
Dynamic Yes
Permitted Values Type String
Default monitor

mysql-monitor_password

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

System Variable Name mysql-monitor_password
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.

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

mysql-monitor_ping_timeout

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

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

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
Dynamic Yes
Permitted Values Type Integer (milliseconds)
Default 3
Minimum 1
Maximum 999999

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
Dynamic Yes
Permitted Values Type Integer (milliseconds)
Default 60000 (1 min)
Minimum 100
Maximum 604800000

mysql-monitor_query_status

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_status
Dynamic Yes
Permitted Values Type String
Default SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS

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
Dynamic Yes
Permitted Values Type Integer (milliseconds)
Default 100

mysql-monitor_query_variables

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_variables
Dynamic Yes
Permitted Values Type String
Default SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES

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
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
Dynamic Yes
Permitted Values Type Integer (milliseconds)
Default 1000
Minimum 100
Maximum 600000

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
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_timer_cached

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
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
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
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
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
Dynamic Yes
Permitted Values Type Integer (milliseconds)
Default 100
Minimum 10
Maximum 20000

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 disabled mysql-query_digests unless you are really sure it won't break your application.

System Variable Name mysql-query_digests
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 other when false query digests are case sensitive.

System Variable Name mysql-query_digests_lowercase
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
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
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_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
Dynamic Yes
Permitted Values Type Integer
Default 0
Minimum 0
Maximum 1000000

mysql-query_processor_regex

This variable defines which regex engine to use:

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 change 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 retries mysql-query_retries_on_failure times.

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

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
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
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
Dynamic Yes
Permitted Values Type Boolean
Default true

mysql-session_debug

Currently unused. Will be removed in a future version.

System Variable Name mysql-session_debug
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
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
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
Dynamic Yes
Permitted Values Type Boolean
Default true

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
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
Dynamic Yes
Permitted Values Type Integer (seconds)
Default 10
Minimum 0
Maximum 31536000

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
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
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
Dynamic Yes
Permitted Values Type Boolean
Default (up to 1.4.3) true
Default (from 1.4.4 onwards) 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.

System Variable Name mysql-threads
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
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
Dynamic Yes
Permitted Values Type Integer (bytes)
Default 4194304 (4MB)
Minimum 1024
Maximum 1073741824

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

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
Dynamic Yes
Permitted Values Type Integer (milliseconds)
Default 28800000 (8 hours)
Minimum 0
Maximum 1728000000