Skip to content

Configuring ProxySQL

Jump to bottom
Kenan Fallon edited this page Feb 15, 2018 · 12 revisions

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 restartof 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 levels of configuration consist of the following layers:

+-------------------------+
|         RUNTIME         |
+-------------------------+
       /|\          |
        |           |
    [1] |       [2] |
        |          \|/
+-------------------------+
|         MEMORY          |
+-------------------------+ _
       /|\          |      |\
        |           |        \
    [3] |       [4] |         \ [5]
        |          \|/         \
+-------------------------+  +-------------------------+
|          DISK           |  |       CONFIG FILE       |
+-------------------------+  +-------------------------+

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

The runtime variables contain:

  • 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 (sometime also referred as main) represent an in-memory database which is exposed via a MySQL-compatible interface. Users can connect with a MySQL client to this interface and query the various ProxySQL configuration tables / databases.

The configuration tables available through this interface are:

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

DISK represents an on-disk SQLite3 database, with the default location at $(DATADIR)/proxysql.db. Across restarts, the in-memory configs that were not persisted will be lost. Therefore it is important to persist the configuration into DISK . 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.

In the following sections, we'll describe the life-cycle of each of these layers for the basic operations that the daemon goes through: starting up for the first time, starting up, restarting, shutting down, etc.

Startup

At a normal start-up, ProxySQL reads its config file (if present) to determine its datadir. What happen next depends from the presence or not of its database file (disk) in its datadir.

If the database file is found, ProxySQL initializes its in-memory configuration from the persisted on-disk database. So, disk gets loaded into memory and then propagated towards the runtime configuration. If the database file is not found and a config file exists, the config file is parsed and its content is loaded into the in-memory database, to then be both saved on-disk database and loaded at runtime. It is important to note that if a database file is found, the config file is not parsed . That is, during a normal start-up, ProxySQL initializes its in-memory configuration from the persisted on-disk database ONLY.

Note: ProxySQL 1.4.4 introduced 2 new general variables that are always parsed from the config file.

Initial startup (or --initial flag)

At the initial start-up, the memory and runtime configurations are populated from the config file. Thereafter the configuration is persisted in ProxySQL's embedded SQLite 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 perform its regular startup procedures.

There is no guarantee that ProxySQL will successfully manage to merge the two configuration source if they conflict, and users should always validate that the merge was executed as expected.

Modifying config at runtime

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:

mysql> show tables;
+-------------------+
| tables            |
+-------------------+
| mysql_servers     |
| mysql_users       |
| mysql_query_rules |
| global_variables  |
| mysql_collations  |
| debug_levels      |
+-------------------+
6 rows in set (0.01 sec)

Each such table has a well defined role in the admin interface:

  • mysql_servers contains the list of backend servers for ProxySQL to connect to
  • mysql_users contains the list of users which ProxySQL will use to authenticate to the backend servers
  • mysql_query_rules contains the rules for caching, routing or rewriting the SQL queries that are sent to ProxySQL
  • global_variables contains the MySQL variables and admin variables which were defined during the server's initial configuration
  • debug_levels is only useful for debugging a manual build of ProxySQL

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.

For more details about these tables and their fields, see their dedicated description in the documentation.

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. Once you understand how each of the three layers are used, the semantics should be clear. Together with the explanation of each command, there is a numbered option written next to each. The number corresponds to the arrow listed in the diagram above.

For reconfiguring MySQL users, execute either one of the following commands:

  • [1] LOAD MYSQL USERS FROM MEMORY / LOAD MYSQL USERS TO RUNTIME
    • loads MySQL users from the in-memory database to the runtime data structures or vice versa
  • [2] SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME
    • persists the MySQL users from runtime to the in-memory database
  • [3] LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK
    • loads the persisted MySQL users from the on-disk database to the in-memory database
  • [4] SAVE MYSQL USERS FROM MEMORY / SAVE MYSQL USERS TO DISK
    • persists the MySQL users from the in-memory database to the on-disk database
  • [5] LOAD MYSQL USERS FROM CONFIG
    • loads from the configuration file the users into the in-memory database

For handling MySQL servers:

  • [1] LOAD MYSQL SERVERS FROM MEMORY / LOAD MYSQL SERVERS TO RUNTIME
    • loads MySQL servers from the in-memory database to runtime
  • [2] SAVE MYSQL SERVERS TO MEMORY / SAVE MYSQL SERVERS FROM RUNTIME
    • persists the MySQL servers from runtime to the in-memory database
  • [3] LOAD MYSQL SERVERS TO MEMORY / LOAD MYSQL SERVERS FROM DISK
    • loads MySQL servers from the on-disk database to the in-memory database
  • [4] SAVE MYSQL SERVERS FROM MEMORY / SAVE MYSQL SERVERS TO DISK
    • persists the MySQL servers from the in-memory database to the on-disk database
  • [5] LOAD MYSQL SERVERS FROM CONFIG
    • loads from the configuration file the servers into the in-memory database

For handling MySQL query rules:

  • [1] LOAD MYSQL QUERY RULES FROM MEMORY / LOAD MYSQL QUERY RULES TO RUNTIME
    • loads the MySQL query rules from the in-memory database to the runtime data structures
  • [2] SAVE MYSQL QUERY RULES TO MEMORY / SAVE MYSQL QUERY RULES FROM RUNTIME
    • persists the MySQL query rules from the runtime data structures to the in-memory database
  • [3] LOAD MYSQL QUERY RULES TO MEMORY / LOAD MYSQL QUERY RULES FROM DISK
    • loads MySQL query rules from the on-disk database to the in-memory database
  • [4] SAVE MYSQL QUERY RULES FROM MEMORY / SAVE MYSQL QUERY RULES TO DISK
    • persists the MySQL query rules from the in-memory database to the on-disk database
  • [5] LOAD MYSQL QUERY RULES FROM CONFIG
    • loads from the configuration file query rules into the in-memory database

For handling MySQL variables:

  • [1] LOAD MYSQL VARIABLES FROM MEMORY / LOAD MYSQL VARIABLES TO RUNTIME
    • loads the MySQL variables from the in-memory database to the runtime data structures
  • [2] SAVE MYSQL VARIABLES FROM MEMORY / SAVE MYSQL VARIABLES TO DISK
    • persists the MySQL variables from the in-memory database to the on-disk database
  • [3] LOAD MYSQL VARIABLES TO MEMORY / LOAD MYSQL VARIABLES FROM DISK
    • loads MySQL variables from the on-disk database to the in-memory database
  • [4] SAVE MYSQL VARIABLES TO MEMORY / SAVE MYSQL VARIABLES FROM RUNTIME
    • persists the MySQL variables from the runtime data structures to the in-memory database
  • [5] LOAD MYSQL VARIABLES FROM CONFIG
    • loads the configuration file variables into the in-memory database

For handling admin variables:

  • [1] LOAD ADMIN VARIABLES FROM MEMORY / LOAD ADMIN VARIABLES TO RUNTIME
    • loads admin variables from the in-memory database to runtime
  • [2] SAVE ADMIN VARIABLES TO MEMORY / SAVE ADMIN VARIABLES FROM RUNTIME
    • persists the admin variables from runtime to the in-memory database
  • [3] LOAD ADMIN VARIABLES TO MEMORY / LOAD ADMIN VARIABLES FROM DISK
    • loads admin variables from the on-disk database to the in-memory database
  • [4] SAVE ADMIN VARIABLES FROM MEMORY / SAVE ADMIN VARIABLES TO DISK
    • persists the admin variables from the in-memory database to the on-disk database
  • [5] LOAD ADMIN VARIABLES FROM CONFIG
    • loads the configuration file admin variables into the in-memory database

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

Summary

Having now understood how this system works, here are some sample commands from above that you may find yourself needing the most. These are specifically related to how you make config changes active (RUNTIME) and how they are saved to disk permanently (DISK). Remember that no changes will be activated until they are loaded to RUNTIME.

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

Troubleshooting

Note that final validation is only done when values are loaded to runtime. You cam set a value that won't rais any kind of warning or error when saved to memory, or even saved to disk. Change are however reverted to their previously saved state when a load to runtime is executed. In 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".

 

Clone this wiki locally