Multi layer configuration system

ProxySQL has a complex yet easy-to-use configuration system suited to serve the following needs:

  • allowing easy dynamic updates to the configuration (this is to allow ProxySQL users to use it in larger infrastructures where zero-downtime provisioning is required). A MySQL-compatible admin interface is available for this very purpose.
  • allowing as many configuration items as possible to be modified dynamically, without requiring a restart of the ProxySQL process
  • allowing effortless rollback of invalid configuration

This is achieved through a multi-level 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. They always have to go through the bottom layers.

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_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. This allows us to easily configure at runtime what kind of statements we have in the log in order to debug different problems. This is available only 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 $(DATADIR)/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 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 MySQL admin port of ProxySQL (6032 by default). After connecting to it, you’ll see a MySQL-compatible interface for querying the various ProxySQL-related tables:

Each such table has a well defined role in the admin interface, more information about each table can be found in the 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 Note: 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.

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

Activate / Persist MySQL Users:

Activate / Persist MySQL Servers and MySQL Replication Hostgroup:

Activate / Persist MySQL Query Rules

Activate / Persist MySQL Variables

Activate / Persist ProxySQL Admin Variables

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,