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

Configuration tables with an active layer have a matching runtime_ table. For the general tables on this page these are runtime_global_variables, runtime_scheduler, runtime_restapi_routes, and runtime_coredump_filters; protocol-specific projections are inventoried on their MySQL and PostgreSQL table pages. Runtime tables represent the configuration currently in use by ProxySQL threads. They are generated operational projections, not additional configuration sources.

The persistent-database global_settings table is internal metadata used while managing that database; it is not an operator-facing runtime configuration surface and has no runtime_global_settings counterpart.

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

coredump_filters

Table coredump_filters selects source locations for the coredump subsystem. Each row is a filename and line number pair.

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

LOAD COREDUMP TO RUNTIME activates the configured set, which is visible in runtime_coredump_filters with the same schema. Its accepted aliases are LOAD COREDUMP FROM MEMORY, LOAD COREDUMP FROM MEM, and LOAD COREDUMP TO RUN. coredump_filters is main-memory-only: it is absent from the disk and configuration databases, and there is no released SAVE COREDUMP command. Reinsert or provision the desired rows after every restart before running a LOAD form.

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 normally mirrors the persistent tables in the “main” database (minus the runtime_ tables), with the same semantics. coredump_filters is a released exception: it exists only in the main-memory and runtime layers, has no disk/config table, and has no SAVE command. Other disk tables are stored persistently, and ProxySQL populates the corresponding in-memory tables from them at restart. Main-memory content that has no disk copy is lost when ProxySQL restarts.