Multi layer configuration system

ProxySQL has an advanced yet intuitive configuration system built to facilitate:

  • Easy-to-use dynamic runtime configuration system to ensure zero-downtime changes
  • Effortless rollback of configuration to its previous state
  • A MySQL-compatible administrative interface

This is achieved through a multi-layer configuration system where settings are moved from runtime to memory, and persisted to disk as desired.

The 3 layers of configuration consist of:

RUNTIME

The RUNTIME layer represents the in-memory data structures of ProxySQL used by worker threads handling requests.

The runtime variables contain configuration related to:

  • the actual values defined in the global variables
  • the list of backend servers grouped into hostgroups
  • the list of MySQL users that can connect to the proxy

Note: Operators can never modify the contents of the RUNTIME configuration section directly.

MEMORY

The MEMORY (also referred to as main) layer represents an in-memory database which is exposed via a MySQL-compatible interface. Users can connect with a MySQL client to this interface and view / edit the various ProxySQL configuration tables.

Configuration tables available through this interface include tables such as:

  • mysql_servers — the list of backend servers which ProxySQL connects to
  • mysql_users — the list of users and their credentials which connect to ProxySQL. Please note that ProxySQL will use the same credentials to connect to the backend servers as well!
  • mysql_query_rules — the list of query rules which are evaluated when routing traffic to the various backend servers. These rules can also rewrite queries or even cache the result of an executed query.
  • global_variables — the list of global variables which the proxy is configured to use, and which can be tweaked during runtime.

Examples of global variables:

mysql> select * from global_variables limit 3;
+----------------------------------+----------------+
| variable_name                    | variable_value |
+----------------------------------+----------------+
| mysql-connect_retries_on_failure | 5              |
| mysql-connect_retries_delay      | 1              |
| mysql-connect_timeout_server_max | 10000          |
+----------------------------------+----------------+
  • mysql_collations — the list of MySQL collations available for the proxy to work with. These are extracted directly from the client library.
  • [only available in debug builds] debug_levels — the list of types of debug statements that ProxySQL emits together with their verbosity levels and is only available in debug builds because it can affect performance.

DISK and CONFIG FILE

The DISK layer represents an on-disk SQLite3 database, with the default location at /var/lib/proxysql/proxysql.db. The DISK database is available to persist in-memory configs to disk so that the configuration is available after a ProxySQL restart. The CONFIG file is the classical config file, and we’ll see the relationship between it and the other configuration layers in the next section.

Configuration Life Cycle

During start-up, ProxySQL reads its config file (if present) to determine the defined `datadir` and then tries to locate its internal database file in the specified path.

  • If the database is found in the specified datadir, ProxySQL will initialize its in-memory configuration from the persisted on-disk database i.e. here the disk configuration is loaded into MEMORY and is also propagated to RUNTIME.
  • If the database file is not found (e.g. in case ProxySQL is being run for the first time or the database has been deleted) the config file is parsed and its content is loaded into MEMORY, saved to the DISK database and is also propagated to RUNTIME.

IMPORTANT NOTE: If a database file is found, the `/etc/proxysql.cnf` config file is not parsed i.e. ProxySQL initializes its in-memory configuration from the persisted on-disk database ONLY, unless it is not present in which case it will fallback to the configuration file.

Initial startup (or –initial flag)

As mentioned, during the very first start of ProxySQL, the memory and runtime configuration is populated from the config file, thereafter all persisted configuration is stored in ProxySQL’s internal database.

It is possible to force the initial configuration to re-occur by running proxysql with the `–initial` flag, which will reset the SQLite database file to its original state (i.e. the state defined in the config file) and rename the existing SQLite database file in case a rollback is required (check the defined data directory for the older file if required).

Reload startup (or –reload flag)

If ProxySQL binary is executed with the `–reload` flag, it attempts to merge the configuration in the config file with the content of the database file. Thereafter, it will startup using the newly merged configuration.

There is no guarantee that ProxySQL will successfully manage to merge the two configuration source when there are conflicts, and users should always validate that the merge was executed as expected.

Runtime ProxySQL Configuration

Modifying the config at runtime is done through the ProxySQL Admin port of ProxySQL (6032 by default). After connecting to it with a MySQL compatible client, you’ll see a standard MySQL style interface for querying the various underlying ProxySQL configuration and statistics tables:


ProxySQL Admin> show tables;
+----------------------------------------------------+
| tables                                             |
+----------------------------------------------------+
| global_variables                                   |
| mysql_aws_aurora_hostgroups                        |
| mysql_collations                                   |
| mysql_firewall_whitelist_rules                     |
| mysql_firewall_whitelist_sqli_fingerprints         |
| mysql_firewall_whitelist_users                     |
| mysql_galera_hostgroups                            |
| mysql_group_replication_hostgroups                 |
| mysql_query_rules                                  |
| mysql_query_rules_fast_routing                     |
| mysql_replication_hostgroups                       |
| mysql_servers                                      |
| mysql_users                                        |
| proxysql_servers                                   |
| restapi_routes                                     |
| runtime_checksums_values                           |
| runtime_global_variables                           |
| runtime_mysql_aws_aurora_hostgroups                |
| runtime_mysql_firewall_whitelist_rules             |
| runtime_mysql_firewall_whitelist_sqli_fingerprints |
| runtime_mysql_firewall_whitelist_users             |
| runtime_mysql_galera_hostgroups                    |
| runtime_mysql_group_replication_hostgroups         |
| runtime_mysql_query_rules                          |
| runtime_mysql_query_rules_fast_routing             |
| runtime_mysql_replication_hostgroups               |
| runtime_mysql_servers                              |
| runtime_mysql_users                                |
| runtime_proxysql_servers                           |
| runtime_restapi_routes                             |
| runtime_scheduler                                  |
| scheduler                                          |
+----------------------------------------------------+

Each such table has a well defined role in the admin interface, more information about each table can be found in the [Main (runtime)](https://proxysql.com/documentation/main-runtime/) schema documentation.

These tables represent the in-memory database described in the diagram above and can be configured using standard SQL queries. In order to move the configuration from this layer upwards (i.e. in order to be used during runtime) or downwards (in order to be persisted to disk), please review the next section.

Moving config between layers

In order to persist configuration to disk or load configuration to runtime there is a set of different admin commands available which can be executed via the admin interface for each configuration item e.g. `mysql servers`, `mysql users`, `mysql query rules`.

The following command syntax can be used to move ProxySQL configuration between the various layers, <item> is a placeholder for the configuration item you wish to configure:

  • [1] `LOAD <item> FROM MEMORY` / `LOAD <item> TO RUNTIME`
    • Loads configuration item from the in-memory database to the runtime data structures
  • [2] `SAVE <item> TO MEMORY` / `SAVE <item> FROM RUNTIME`
    • Saves the configuration item from runtime to the in-memory database
  • [3] `LOAD <item> TO MEMORY` / `LOAD <item> FROM DISK`
    • Loads the persisted configuration item from the on-disk database to the in-memory database
  • [4] `SAVE <item> FROM MEMORY` / `SAVE <item> TO DISK`
    • Saves the configuration item from the in-memory database to the on-disk database
  • [5] `LOAD <item> FROM CONFIG`
    • Loads the configuration item from the configuration file into the in-memory database

Important Notes:

  • Changes will NOT be activated until they are loaded to RUNTIME and any changes which are NOT saved to DISK will NOT be available after a ProxySQL restart.
  • When ProxySQL receives the LOAD <item> FROM CONFIG command, the following is its expected behavior:
  1. if a loaded entry is present both in the configuration file and the in-memory table(s), LOAD <item> FROM CONFIG is going to overwrite the one configured in the in-memory table(s)
  2. if a loaded entry is only present in the configuration file, but not in the in-memory table(s), LOAD <item> FROM CONFIG is going to add the entry to the in-memory table(s)
  3. if an entry is only present in the in-memory table(s), but not in the configuration file, LOAD <item> FROM CONFIG is not going to delete the entry from the in-memory table(s). To sum it up:
  • LOAD <item> FROM CONFIG is equivalent to an INSERT OR REPLACE command regarding the in-memory table(s) – it cannot be used to delete an already existing entry in them (that needs to be done in an explicit way).

Sample commands for common configuration items can be found below (this is not a complete list):

Activate / Persist MySQL Users:

# Active current in-memory MySQL User configuration
LOAD MYSQL USERS TO RUNTIME;

# Save the current in-memory MySQL User configuration to disk
SAVE MYSQL USERS TO DISK;
Activate / Persist MySQL Servers and MySQL Replication Hostgroup:

# Active current in-memory MySQL Server and Replication Hostgroup configuration
LOAD MYSQL SERVERS TO RUNTIME;

# Save the current in-memory MySQL Server and Replication Hostgroup configuration to disk
SAVE MYSQL SERVERS TO DISK;
Activate / Persist MySQL Query Rules

# Active current in-memory MySQL Query Rule configuration
LOAD MYSQL QUERY RULES TO RUNTIME;

# Save the current in-memory MySQL Query Rule configuration to disk
SAVE MYSQL QUERY RULES TO DISK;
Activate / Persist MySQL Variables

# Active current in-memory MySQL Variable configuration
LOAD MYSQL VARIABLES TO RUNTIME;

# Save the current in-memory MySQL Variable configuration to disk
SAVE MYSQL VARIABLES TO DISK;
Activate / Persist ProxySQL Admin Variables

# Active current in-memory ProxySQL Admin Variable configuration
LOAD ADMIN VARIABLES TO RUNTIME;

# Save the current in-memory ProxySQL Admin Variable configuration to disk
SAVE ADMIN VARIABLES TO DISK;

Note: the above commands allow the following shortcuts:

  • MEM for MEMORY
  • RUN for RUNTIME

For example, these two commands are equivalent:

  • `SAVE ADMIN VARIABLES TO MEMORY`
  • `SAVE ADMIN VARIABLES TO MEM`

Troubleshooting

Note that final validation is only done when values are loaded to runtime. You can set a value that won’t raise any kind of warning or error when saved to memory, or even saved to disk. Changes are however reverted to their previously saved state when a load to runtime is executed. If this occurs you should check the defined error log.

For example,

[WARNING] Impossible to set variable monitor_read_only_interval with value "0". Resetting to current "1500".