Configuration Tables

Introduction

The ProxySQL Admin stores configuration in tables. If you connect to Admin using admin-admin_credentials credentials, you should be able to see a list of configuration and runtime tables like the following. The exact list of tables may vary depending from the version in use, and if certain modules of ProxySQL are operating. These are tables that are not MySQL or PostgreSQL specific.

Admin> SHOW TABLES FROM main;
+----------------------------------------------------+
| tables                                             |
+----------------------------------------------------+
| clickhouse_users                                   |
| coredump_filters                                   |
| debug_filters                                      |
| debug_levels                                       |
| global_variables                                   |
| scheduler                                          |
+----------------------------------------------------+

Key Configuration Tables

Below is a list of the core configuration tables. Protocol-specific tables are grouped in their respective sections.

See MySQL Tables for all MySQL-specific configuration and runtime tables. See PostgreSQL Tables for all PostgreSQL-specific configuration and runtime tables.

global_variables

The table global_variables defines Global variables. This is a much simpler table, essentially a key-value store. These are global variables used by ProxySQL and are useful in order to tweak its behaviour. Global variables are grouped in classes based on their prefix. Currently there are 2 classes of global variables, although more classes are in the roadmap:

• variables prefixed with admin- are relevant for Admin module and allow tweaking of the admin interface E.G. changing the admin interface (admin-mysql_ifaces) or admin credentials (admin-admin_credentials)
• variables prefixed with mysql- are relevant for MySQL modules.
• variables prefixed with pgsql- are relevant for PostgreSQL modules.

For more information about particular variables, please see the dedicated section on global variables

CREATE TABLE global_variables (
    variable_name VARCHAR NOT NULL PRIMARY KEY,
    variable_value VARCHAR NOT NULL
)

scheduler

Table scheduler defines jobs to be executed at regular intervals.

CREATE TABLE scheduler (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL,
    filename VARCHAR NOT NULL,
    arg1 VARCHAR,
    arg2 VARCHAR,
    arg3 VARCHAR,
    arg4 VARCHAR,
    arg5 VARCHAR,
    comment VARCHAR NOT NULL DEFAULT ''
)

Further details about the scheduler can be found here

restapi_routes

Table restapi_routes defines endpoints that a remote client can call using a REST API endpoint using HTTP in order to trigger the execution of a task by ProxySQL. The following is the table definition:

CREATE TABLE restapi_routes (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    timeout_ms INTEGER CHECK (timeout_ms>=100 AND timeout_ms<=100000000) NOT NULL,
    method VARCHAR NOT NULL CHECK (UPPER(method) IN ('GET','POST')),
    uri VARCHAR NOT NULL,
    script VARCHAR NOT NULL,
    comment VARCHAR NOT NULL DEFAULT ''
)

The table defines:

• id: unique identifier of the endpoint
• active: a REST API endpoint can be either active or not. An endpoint is active by default, and it is possible to disable it by setting active=0.
• timeout_ms: this defines the maximum execution time for the job that will be called by ProxySQL when that endpoint is reached
• method: a REST API endpoint can be called with either a GET or POST method. This defines what method must be used
• uri: this defines the URI (Uniform Resource Identifier) of the endpoint
• script: this is the script (or other executable) that ProxySQL will execute at the specified endpoint
• comment: text field that can be used for any purpose defined by the user, for example to describe the endpoint

Runtime tables

All the configuration tables have a matching runtime_ table (e.g., runtime_global_variables, runtime_mysql_servers, etc.). These tables represent the configuration currently in use by the ProxySQL threads.

A note on main schema

Note that all the content of the in-memory tables (main database) are lost when ProxySQL is restarted if their content wasn’t saved on disk database.

Debug config

debug_filters

The table debug_filters defines filters that can be used to suppress specific information from the error log when ‘debug_levels’ is enabled in a ProxySQL ‘DEBUG’ build.

CREATE TABLE debug_filters (
    filename VARCHAR NOT NULL,
    line INT NOT NULL,
    funct VARCHAR NOT NULL,
    PRIMARY KEY (filename, line, funct)
)

debug_levels

The table debug_levels defines a series of verbosity levels that can be enabled for ProxySQL when compiled in ‘DEBUG’ mode.

CREATE TABLE debug_levels (
    module VARCHAR NOT NULL PRIMARY KEY,
    verbosity INT NOT NULL DEFAULT 0
)

Disk database

The “disk” database has exactly the same tables as the “main” database (minus the runtime_ tables), with the same semantics. The only major difference is that these tables are stored on disk, instead of being stored in-memory. Whenever ProxySQL is restarted, the in-memory “main” database will be populated starting from this database. Note that all the content of the in-memory tables (main database) are lost when ProxySQL is restarted if their content wasn’t saved on disk database.