The monitor database

The monitor database contains log tables populated by ProxySQL’s Monitor Module. These tables record the results of periodic health checks against backend servers — connectivity tests, ping checks, read-only status, replication lag measurements, and cluster-specific checks. Entries are automatically purged based on mysql-monitor_history / pgsql-monitor_history settings.

The Monitor Module configuration and behavior is documented separately in the MySQL Monitor Module and PostgreSQL Monitor Module pages. This page documents the schema tables only.

List all tables

Admin> SHOW TABLES FROM monitor;
                         tables
------------------------------------------------
 mysql_server_aws_aurora_check_status
 mysql_server_aws_aurora_failovers
 mysql_server_aws_aurora_log
 mysql_server_connect_log
 mysql_server_galera_log
 mysql_server_group_replication_log
 mysql_server_ping_log
 mysql_server_read_only_log
 mysql_server_replication_lag_log
 pgsql_server_connect_log
 pgsql_server_ping_log
 pgsql_server_read_only_log
 pgsql_server_replication_lag_log
(13 rows)

MySQL Monitor Tables

mysql_server_connect_log

The mysql_server_connect_log table records the results of periodic connection checks performed by the Monitor Module against each MySQL backend server. A new row is inserted after each connection attempt.

CREATE TABLE mysql_server_connect_log (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 3306,
    time_start_us INT NOT NULL DEFAULT 0,
    connect_success_time_us INT DEFAULT 0,
    connect_error VARCHAR,
    PRIMARY KEY (hostname, port, time_start_us))

The fields have the following semantics:

• hostname: the hostname or IP address of the backend MySQL server being checked.
• port: the TCP port of the backend server.
• time_start_us: the time at which the connection check started, expressed in microseconds since the Unix epoch.
• connect_success_time_us: the elapsed time to complete a successful TCP connection and MySQL handshake, in microseconds. A value of 0 indicates a failed attempt.
• connect_error: the error message returned when a connection attempt fails. NULL when the connection succeeds.

Example:

Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 5;
+-----------+------+------------------+------------------------+---------------+
| hostname  | port | time_start_us    | connect_success_time_us | connect_error |
+-----------+------+------------------+------------------------+---------------+
| 10.0.0.1  | 3306 | 1711027200123456 | 843                    | NULL          |
| 10.0.0.2  | 3306 | 1711027200123123 | 912                    | NULL          |
| 10.0.0.3  | 3306 | 1711027199987654 | 0                      | Can't connect to MySQL server on '10.0.0.3' (111) |
| 10.0.0.1  | 3306 | 1711027195100000 | 788                    | NULL          |
| 10.0.0.2  | 3306 | 1711027195099000 | 823                    | NULL          |
+-----------+------+------------------+------------------------+---------------+
5 rows in set (0.00 sec)

mysql_server_ping_log

The mysql_server_ping_log table records the results of periodic ping checks performed by the Monitor Module. Unlike connection checks, ping checks reuse an existing monitoring connection and send a lightweight ping command to verify backend server liveness.

CREATE TABLE mysql_server_ping_log (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 3306,
    time_start_us INT NOT NULL DEFAULT 0,
    ping_success_time_us INT DEFAULT 0,
    ping_error VARCHAR,
    PRIMARY KEY (hostname, port, time_start_us))

The fields have the following semantics:

• hostname: the hostname or IP address of the backend MySQL server being pinged.
• port: the TCP port of the backend server.
• time_start_us: the time at which the ping was issued, expressed in microseconds since the Unix epoch.
• ping_success_time_us: the round-trip time for a successful ping, in microseconds. A value of 0 indicates a failed ping.
• ping_error: the error message when the ping fails. NULL when the ping succeeds.

Example:

Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 5;
+-----------+------+------------------+----------------------+------------+
| hostname  | port | time_start_us    | ping_success_time_us | ping_error |
+-----------+------+------------------+----------------------+------------+
| 10.0.0.1  | 3306 | 1711027260001000 | 312                  | NULL       |
| 10.0.0.2  | 3306 | 1711027260000500 | 298                  | NULL       |
| 10.0.0.3  | 3306 | 1711027259998000 | 0                    | timeout    |
| 10.0.0.1  | 3306 | 1711027255000000 | 301                  | NULL       |
| 10.0.0.2  | 3306 | 1711027254999500 | 315                  | NULL       |
+-----------+------+------------------+----------------------+------------+
5 rows in set (0.00 sec)

mysql_server_read_only_log

The mysql_server_read_only_log table records the results of periodic checks of the read_only system variable on backend MySQL servers. ProxySQL uses these results to detect topology changes — for example, when a replica is promoted to primary — and to route writes and reads to the appropriate hostgroup.

CREATE TABLE mysql_server_read_only_log (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 3306,
    time_start_us INT NOT NULL DEFAULT 0,
    success_time_us INT DEFAULT 0,
    read_only INT DEFAULT 1,
    error VARCHAR,
    PRIMARY KEY (hostname, port, time_start_us))

The fields have the following semantics:

• hostname: the hostname or IP address of the backend MySQL server.
• port: the TCP port of the backend server.
• time_start_us: the time at which the read-only check started, in microseconds since the Unix epoch.
• success_time_us: the elapsed time to retrieve the read_only variable, in microseconds. A value of 0 indicates a failed check.
• read_only: 1 if the server is in read-only mode, 0 if it is read-write. NULL indicates the check could not be completed.
• error: the error message when the check fails. NULL on success.

Example:

Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 5;
+-----------+------+------------------+----------------+-----------+-------+
| hostname  | port | time_start_us    | success_time_us | read_only | error |
+-----------+------+------------------+----------------+-----------+-------+
| 10.0.0.1  | 3306 | 1711027320000000 | 512            | 0         | NULL  |
| 10.0.0.2  | 3306 | 1711027319999000 | 498            | 1         | NULL  |
| 10.0.0.3  | 3306 | 1711027319998000 | 531            | 1         | NULL  |
| 10.0.0.1  | 3306 | 1711027315000000 | 489            | 0         | NULL  |
| 10.0.0.2  | 3306 | 1711027314999500 | 507            | 1         | NULL  |
+-----------+------+------------------+----------------+-----------+-------+
5 rows in set (0.00 sec)

mysql_server_replication_lag_log

The mysql_server_replication_lag_log table records the results of replication lag measurements performed by the Monitor Module. ProxySQL queries Seconds_Behind_Master (or equivalent) on replica servers and uses the recorded lag values to throttle or exclude replicas that are too far behind the primary.

CREATE TABLE mysql_server_replication_lag_log (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 3306,
    time_start_us INT NOT NULL DEFAULT 0,
    success_time_us INT DEFAULT 0,
    repl_lag INT DEFAULT 0,
    error VARCHAR,
    PRIMARY KEY (hostname, port, time_start_us))

The fields have the following semantics:

• hostname: the hostname or IP address of the backend MySQL replica server.
• port: the TCP port of the backend server.
• time_start_us: the time at which the replication lag check started, in microseconds since the Unix epoch.
• success_time_us: the elapsed time to retrieve the lag value, in microseconds. A value of 0 indicates a failed check.
• repl_lag: the measured replication lag in seconds. NULL if the check failed or if the server is not replicating.
• error: the error message when the check fails. NULL on success.

Example:

Admin> SELECT * FROM monitor.mysql_server_replication_lag_log ORDER BY time_start_us DESC LIMIT 5;
+-----------+------+------------------+----------------+----------+-------+
| hostname  | port | time_start_us    | success_time_us | repl_lag | error |
+-----------+------+------------------+----------------+----------+-------+
| 10.0.0.2  | 3306 | 1711027380001000 | 623            | 0        | NULL  |
| 10.0.0.3  | 3306 | 1711027380000500 | 598            | 2        | NULL  |
| 10.0.0.2  | 3306 | 1711027375000000 | 611            | 0        | NULL  |
| 10.0.0.3  | 3306 | 1711027374999000 | 634            | 5        | NULL  |
| 10.0.0.2  | 3306 | 1711027370000000 | 589            | 0        | NULL  |
+-----------+------+------------------+----------------+----------+-------+
5 rows in set (0.00 sec)

mysql_server_group_replication_log

The mysql_server_group_replication_log table records the results of Group Replication status checks. When MySQL Group Replication hostgroups are configured, the Monitor Module periodically queries Group Replication status variables on each member and stores the results here.

CREATE TABLE mysql_server_group_replication_log (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 3306,
    time_start_us INT NOT NULL DEFAULT 0,
    success_time_us INT DEFAULT 0,
    viable_candidate VARCHAR NOT NULL DEFAULT 'NO',
    read_only VARCHAR NOT NULL DEFAULT 'YES',
    transactions_behind INT DEFAULT 0,
    error VARCHAR,
    PRIMARY KEY (hostname, port, time_start_us))

The fields have the following semantics:

• hostname: the hostname or IP address of the Group Replication member.
• port: the TCP port of the backend server.
• time_start_us: the time at which the check started, in microseconds since the Unix epoch.
• success_time_us: the elapsed time to complete the check, in microseconds. A value of 0 indicates a failed check.
• viable_candidate: YES if the member is eligible to be elected as primary (e.g., it is online and has no applied transactions gap). NO otherwise.
• read_only: YES if the member is currently operating in read-only mode (typically secondary members), NO for the primary.
• transactions_behind: the number of transactions the member has yet to apply from the group’s relay log. A value of 0 means the member is fully caught up.
• error: the error message when the check fails. NULL on success.

Example:

Admin> SELECT * FROM monitor.mysql_server_group_replication_log ORDER BY time_start_us DESC LIMIT 4;
+-----------+------+------------------+----------------+------------------+-----------+---------------------+-------+
| hostname  | port | time_start_us    | success_time_us | viable_candidate | read_only | transactions_behind | error |
+-----------+------+------------------+----------------+------------------+-----------+---------------------+-------+
| 10.0.0.1  | 3306 | 1711027440000000 | 721            | YES              | NO        | 0                   | NULL  |
| 10.0.0.2  | 3306 | 1711027439999000 | 698            | YES              | YES       | 0                   | NULL  |
| 10.0.0.3  | 3306 | 1711027439998000 | 710            | YES              | YES       | 3                   | NULL  |
| 10.0.0.1  | 3306 | 1711027435000000 | 689            | YES              | NO        | 0                   | NULL  |
+-----------+------+------------------+----------------+------------------+-----------+---------------------+-------+
4 rows in set (0.00 sec)

mysql_server_galera_log

The mysql_server_galera_log table records the results of Galera/PXC cluster status checks. When Galera hostgroups are configured, the Monitor Module queries a set of Galera-specific status variables on each node to determine its cluster membership, replication queue depth, and operational state.

CREATE TABLE mysql_server_galera_log (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 3306,
    time_start_us INT NOT NULL DEFAULT 0,
    success_time_us INT DEFAULT 0,
    primary_partition VARCHAR NOT NULL DEFAULT 'NO',
    read_only VARCHAR NOT NULL DEFAULT 'YES',
    wsrep_local_recv_queue INT DEFAULT 0,
    wsrep_local_state INT DEFAULT 0,
    wsrep_desync VARCHAR NOT NULL DEFAULT 'NO',
    wsrep_reject_queries VARCHAR NOT NULL DEFAULT 'NO',
    wsrep_sst_donor_rejects_queries VARCHAR NOT NULL DEFAULT 'NO',
    pxc_maint_mode VARCHAR NOT NULL DEFAULT 'NO',
    error VARCHAR,
    PRIMARY KEY (hostname, port, time_start_us))

The fields have the following semantics:

• hostname: the hostname or IP address of the Galera cluster node.
• port: the TCP port of the backend server.
• time_start_us: the time at which the check started, in microseconds since the Unix epoch.
• success_time_us: the elapsed time to complete the check, in microseconds. A value of 0 indicates a failed check.
• primary_partition: YES if the node is part of the primary component (quorum), NO if the node is isolated or in a non-primary partition.
• read_only: YES if the node’s read_only variable is enabled, NO otherwise.
• wsrep_local_recv_queue: the current length of the node’s writeset receive queue. High values indicate the node is falling behind the cluster.
• wsrep_local_state: the integer state code of the Galera node. Common values: 1 = Joining, 2 = Donor/Desynced, 3 = Joined, 4 = Synced.
• wsrep_desync: YES if the node has been explicitly desynced (e.g., for maintenance), NO otherwise.
• wsrep_reject_queries: YES if wsrep_reject_queries is set (node is rejecting client queries), NO otherwise.
• wsrep_sst_donor_rejects_queries: YES if the node is acting as an SST donor and is configured to reject queries during the transfer, NO otherwise.
• pxc_maint_mode: the Percona XtraDB Cluster maintenance mode value (DISABLED, SHUTDOWN, or MAINTENANCE).
• error: the error message when the check fails. NULL on success.

Example:

Admin> SELECT * FROM monitor.mysql_server_galera_log ORDER BY time_start_us DESC LIMIT 3;
+-----------+------+------------------+----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+-------------------------------+----------------+-------+
| hostname  | port | time_start_us    | success_time_us | primary_partition | read_only | wsrep_local_recv_queue | wsrep_local_state | wsrep_desync | wsrep_reject_queries | wsrep_sst_donor_rejects_queries | pxc_maint_mode | error |
+-----------+------+------------------+----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+-------------------------------+----------------+-------+
| 10.0.0.1  | 3306 | 1711027500001000 | 812            | YES               | NO        | 0                      | 4                 | NO           | NO                   | NO                              | DISABLED       | NULL  |
| 10.0.0.2  | 3306 | 1711027500000500 | 798            | YES               | NO        | 0                      | 4                 | NO           | NO                   | NO                              | DISABLED       | NULL  |
| 10.0.0.3  | 3306 | 1711027499999000 | 823            | YES               | NO        | 2                      | 4                 | NO           | NO                   | NO                              | DISABLED       | NULL  |
+-----------+------+------------------+----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+-------------------------------+----------------+-------+
3 rows in set (0.00 sec)

mysql_server_aws_aurora_log

The mysql_server_aws_aurora_log table records Aurora-specific monitoring data collected by the Monitor Module. In addition to standard checks, ProxySQL queries the information_schema.replica_host_status table on Aurora clusters to gather replication lag, estimated lag, and CPU load for each Aurora instance.

CREATE TABLE mysql_server_aws_aurora_log (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 3306,
    time_start_us INT NOT NULL DEFAULT 0,
    success_time_us INT DEFAULT 0,
    error VARCHAR,
    SERVER_ID VARCHAR NOT NULL DEFAULT '',
    SESSION_ID VARCHAR,
    LAST_UPDATE_TIMESTAMP VARCHAR,
    replica_lag_in_milliseconds INT NOT NULL DEFAULT 0,
    estimated_lag_ms INT NOT NULL DEFAULT 0,
    CPU INT NOT NULL DEFAULT 0,
    PRIMARY KEY (hostname, port, time_start_us, SERVER_ID))

The fields have the following semantics:

• hostname: the hostname or cluster endpoint used to reach the Aurora instance.
• port: the TCP port of the Aurora instance.
• time_start_us: the time at which the Aurora check started, in microseconds since the Unix epoch.
• success_time_us: the elapsed time to complete the Aurora status query, in microseconds.
• error: the error message when the check fails. NULL on success.
• SERVER_ID: the unique identifier for the Aurora DB instance as returned by information_schema.replica_host_status.
• SESSION_ID: the session ID from Aurora’s status table, used to distinguish writer vs. reader instances.
• LAST_UPDATE_TIMESTAMP: the timestamp at which Aurora last updated the status row for this instance.
• replica_lag_in_milliseconds: the replication lag in milliseconds as reported by Aurora’s internal monitoring.
• estimated_lag_ms: ProxySQL’s own estimate of the replica lag in milliseconds, derived from monitoring intervals.
• CPU: the CPU utilization percentage of the Aurora instance as reported by Aurora.

Example:

Admin> SELECT * FROM monitor.mysql_server_aws_aurora_log ORDER BY time_start_us DESC LIMIT 4;
+-------------------------------------+------+------------------+----------------+-------+-------------------+--------------------------------------+-------------------------+------------------------------+------------------+-----+
| hostname                            | port | time_start_us    | success_time_us | error | SERVER_ID         | SESSION_ID                           | LAST_UPDATE_TIMESTAMP   | replica_lag_in_milliseconds  | estimated_lag_ms | CPU |
+-------------------------------------+------+------------------+----------------+-------+-------------------+--------------------------------------+-------------------------+------------------------------+------------------+-----+
| mydb.cluster.us-east-1.rds.amazonaws.com | 3306 | 1711027560001000 | 934 | NULL  | mydb-instance-1   | MASTER_SESSION_ID                    | 2024-03-21 12:00:01     | 0                            | 0                | 12  |
| mydb.cluster.us-east-1.rds.amazonaws.com | 3306 | 1711027560001000 | 934 | NULL  | mydb-instance-2   | a1b2c3d4-e5f6-7890-abcd-ef1234567890 | 2024-03-21 12:00:00     | 18                           | 20               | 8   |
| mydb.cluster.us-east-1.rds.amazonaws.com | 3306 | 1711027555000000 | 912 | NULL  | mydb-instance-1   | MASTER_SESSION_ID                    | 2024-03-21 11:59:56     | 0                            | 0                | 11  |
| mydb.cluster.us-east-1.rds.amazonaws.com | 3306 | 1711027555000000 | 912 | NULL  | mydb-instance-2   | a1b2c3d4-e5f6-7890-abcd-ef1234567890 | 2024-03-21 11:59:55     | 22                           | 25               | 9   |
+-------------------------------------+------+------------------+----------------+-------+-------------------+--------------------------------------+-------------------------+------------------------------+------------------+-----+
4 rows in set (0.00 sec)

mysql_server_aws_aurora_check_status

The mysql_server_aws_aurora_check_status table tracks the cumulative health check status for each Aurora instance per hostgroup. Unlike the log tables that record individual check events, this table is updated in place and provides a summary of check history and the most recent error for each server.

CREATE TABLE mysql_server_aws_aurora_check_status (
    writer_hostgroup INT NOT NULL,
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 3306,
    last_checked_at VARCHAR,
    checks_tot INT NOT NULL DEFAULT 0,
    checks_ok INT NOT NULL DEFAULT 0,
    last_error VARCHAR,
    PRIMARY KEY (writer_hostgroup, hostname, port))

The fields have the following semantics:

• writer_hostgroup: the hostgroup ID designated as the writer hostgroup for the Aurora cluster. Used to group Aurora instances belonging to the same cluster.
• hostname: the hostname or IP address of the Aurora DB instance.
• port: the TCP port of the Aurora instance.
• last_checked_at: a human-readable timestamp indicating when the most recent check was performed.
• checks_tot: the cumulative total number of health checks that have been attempted for this instance since ProxySQL started or the entry was created.
• checks_ok: the cumulative number of health checks that completed successfully.
• last_error: the error string from the most recent failed check. NULL if the last check was successful.

Example:

Admin> SELECT * FROM monitor.mysql_server_aws_aurora_check_status;
+------------------+-------------------------------------+------+-------------------------+------------+------------+------------+
| writer_hostgroup | hostname                            | port | last_checked_at         | checks_tot | checks_ok  | last_error |
+------------------+-------------------------------------+------+-------------------------+------------+------------+------------+
| 1                | mydb.cluster.us-east-1.rds.amazonaws.com | 3306 | 2024-03-21 12:01:00 | 1440       | 1440       | NULL       |
+------------------+-------------------------------------+------+-------------------------+------------+------------+------------+
1 row in set (0.00 sec)

mysql_server_aws_aurora_failovers

The mysql_server_aws_aurora_failovers table records Aurora failover events detected by ProxySQL’s Monitor Module. Each row represents a detected failover for a specific Aurora cluster (identified by its writer hostgroup), capturing the new writer hostname and when the failover was recorded.

CREATE TABLE mysql_server_aws_aurora_failovers (
    writer_hostgroup INT NOT NULL,
    hostname VARCHAR NOT NULL,
    inserted_at VARCHAR NOT NULL)

The fields have the following semantics:

• writer_hostgroup: the hostgroup ID of the writer hostgroup for the affected Aurora cluster.
• hostname: the hostname of the instance that became the new writer after the failover was detected.
• inserted_at: the timestamp at which ProxySQL recorded the failover event.

Example:

Admin> SELECT * FROM monitor.mysql_server_aws_aurora_failovers;
+------------------+---------------------------------------------+-------------------------+
| writer_hostgroup | hostname                                    | inserted_at             |
+------------------+---------------------------------------------+-------------------------+
| 1                | mydb-instance-2.us-east-1.rds.amazonaws.com | 2024-03-21 03:14:22     |
+------------------+---------------------------------------------+-------------------------+
1 row in set (0.00 sec)

PostgreSQL Monitor Tables

pgsql_server_connect_log

The pgsql_server_connect_log table records the results of periodic connection checks performed against each PostgreSQL backend server. Its structure mirrors mysql_server_connect_log, with the default port adjusted for PostgreSQL.

CREATE TABLE pgsql_server_connect_log (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 5432,
    time_start_us INT NOT NULL DEFAULT 0,
    connect_success_time_us INT DEFAULT 0,
    connect_error VARCHAR,
    PRIMARY KEY (hostname, port, time_start_us))

The fields have the following semantics:

• hostname: the hostname or IP address of the backend PostgreSQL server being checked.
• port: the TCP port of the backend server.
• time_start_us: the time at which the connection check started, expressed in microseconds since the Unix epoch.
• connect_success_time_us: the elapsed time to complete a successful TCP connection and PostgreSQL authentication handshake, in microseconds. A value of 0 indicates a failed attempt.
• connect_error: the error message returned when a connection attempt fails. NULL when the connection succeeds.

Example:

Admin> SELECT * FROM monitor.pgsql_server_connect_log ORDER BY time_start_us DESC LIMIT 5;
+-----------+------+------------------+------------------------+---------------+
| hostname  | port | time_start_us    | connect_success_time_us | connect_error |
+-----------+------+------------------+------------------------+---------------+
| 10.0.1.1  | 5432 | 1711027620001000 | 1102                   | NULL          |
| 10.0.1.2  | 5432 | 1711027620000500 | 987                    | NULL          |
| 10.0.1.3  | 5432 | 1711027619998000 | 0                      | connection refused |
| 10.0.1.1  | 5432 | 1711027615000000 | 1089                   | NULL          |
| 10.0.1.2  | 5432 | 1711027614999500 | 1001                   | NULL          |
+-----------+------+------------------+------------------------+---------------+
5 rows in set (0.00 sec)

pgsql_server_ping_log

The pgsql_server_ping_log table records the results of periodic ping checks against PostgreSQL backend servers. Its structure mirrors mysql_server_ping_log, adapted for PostgreSQL connections.

CREATE TABLE pgsql_server_ping_log (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 5432,
    time_start_us INT NOT NULL DEFAULT 0,
    ping_success_time_us INT DEFAULT 0,
    ping_error VARCHAR,
    PRIMARY KEY (hostname, port, time_start_us))

The fields have the following semantics:

• hostname: the hostname or IP address of the backend PostgreSQL server being pinged.
• port: the TCP port of the backend server.
• time_start_us: the time at which the ping was issued, expressed in microseconds since the Unix epoch.
• ping_success_time_us: the round-trip time for a successful ping, in microseconds. A value of 0 indicates a failed ping.
• ping_error: the error message when the ping fails. NULL when the ping succeeds.

Example:

Admin> SELECT * FROM monitor.pgsql_server_ping_log ORDER BY time_start_us DESC LIMIT 5;
+-----------+------+------------------+----------------------+------------+
| hostname  | port | time_start_us    | ping_success_time_us | ping_error |
+-----------+------+------------------+----------------------+------------+
| 10.0.1.1  | 5432 | 1711027680001000 | 421                  | NULL       |
| 10.0.1.2  | 5432 | 1711027680000500 | 398                  | NULL       |
| 10.0.1.3  | 5432 | 1711027679998000 | 0                    | timeout    |
| 10.0.1.1  | 5432 | 1711027675000000 | 412                  | NULL       |
| 10.0.1.2  | 5432 | 1711027674999500 | 403                  | NULL       |
+-----------+------+------------------+----------------------+------------+
5 rows in set (0.00 sec)

pgsql_server_read_only_log

The pgsql_server_read_only_log table records the results of periodic read-only status checks against PostgreSQL backend servers. ProxySQL checks whether each PostgreSQL server is operating as a primary or standby to route writes and reads to the appropriate hostgroup. Its structure mirrors mysql_server_read_only_log.

CREATE TABLE pgsql_server_read_only_log (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 5432,
    time_start_us INT NOT NULL DEFAULT 0,
    success_time_us INT DEFAULT 0,
    read_only INT DEFAULT 1,
    error VARCHAR,
    PRIMARY KEY (hostname, port, time_start_us))

The fields have the following semantics:

• hostname: the hostname or IP address of the backend PostgreSQL server.
• port: the TCP port of the backend server.
• time_start_us: the time at which the read-only check started, in microseconds since the Unix epoch.
• success_time_us: the elapsed time to retrieve the server’s standby status, in microseconds. A value of 0 indicates a failed check.
• read_only: 1 if the server is a standby (read-only), 0 if it is the primary (read-write). NULL indicates the check could not be completed.
• error: the error message when the check fails. NULL on success.

Example:

Admin> SELECT * FROM monitor.pgsql_server_read_only_log ORDER BY time_start_us DESC LIMIT 5;
+-----------+------+------------------+----------------+-----------+-------+
| hostname  | port | time_start_us    | success_time_us | read_only | error |
+-----------+------+------------------+----------------+-----------+-------+
| 10.0.1.1  | 5432 | 1711027740001000 | 634            | 0         | NULL  |
| 10.0.1.2  | 5432 | 1711027740000500 | 618            | 1         | NULL  |
| 10.0.1.3  | 5432 | 1711027739999000 | 641            | 1         | NULL  |
| 10.0.1.1  | 5432 | 1711027735000000 | 609            | 0         | NULL  |
| 10.0.1.2  | 5432 | 1711027734999500 | 627            | 1         | NULL  |
+-----------+------+------------------+----------------+-----------+-------+
5 rows in set (0.00 sec)

pgsql_server_replication_lag_log

The pgsql_server_replication_lag_log table records the results of replication lag measurements against PostgreSQL standby servers. ProxySQL queries streaming replication lag from pg_stat_replication or equivalent views and uses the results to throttle or exclude standbys that are too far behind the primary. Its structure mirrors mysql_server_replication_lag_log.

CREATE TABLE pgsql_server_replication_lag_log (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 5432,
    time_start_us INT NOT NULL DEFAULT 0,
    success_time_us INT DEFAULT 0,
    repl_lag INT DEFAULT 0,
    error VARCHAR,
    PRIMARY KEY (hostname, port, time_start_us))

The fields have the following semantics:

• hostname: the hostname or IP address of the backend PostgreSQL standby server.
• port: the TCP port of the backend server.
• time_start_us: the time at which the replication lag check started, in microseconds since the Unix epoch.
• success_time_us: the elapsed time to retrieve the lag value, in microseconds. A value of 0 indicates a failed check.
• repl_lag: the measured replication lag in seconds. NULL if the check failed or if the server is not a streaming replica.
• error: the error message when the check fails. NULL on success.

Example:

Admin> SELECT * FROM monitor.pgsql_server_replication_lag_log ORDER BY time_start_us DESC LIMIT 5;
+-----------+------+------------------+----------------+----------+-------+
| hostname  | port | time_start_us    | success_time_us | repl_lag | error |
+-----------+------+------------------+----------------+----------+-------+
| 10.0.1.2  | 5432 | 1711027800001000 | 742            | 0        | NULL  |
| 10.0.1.3  | 5432 | 1711027800000500 | 718            | 1        | NULL  |
| 10.0.1.2  | 5432 | 1711027795000000 | 729            | 0        | NULL  |
| 10.0.1.3  | 5432 | 1711027794999500 | 751            | 3        | NULL  |
| 10.0.1.2  | 5432 | 1711027790000000 | 711            | 0        | NULL  |
+-----------+------+------------------+----------------+----------+-------+
5 rows in set (0.00 sec)

History Management

All tables in the monitor database are append-only log tables. To prevent unbounded growth, ProxySQL automatically purges old entries based on the configured history retention setting:

• MySQL: the mysql-monitor_history variable controls how long monitoring records are retained (in microseconds). The default value is 600000 microseconds (600 milliseconds), which means ProxySQL keeps roughly the most recent entries within that time window per server. Older rows are deleted automatically.
• PostgreSQL: the pgsql-monitor_history variable serves the same purpose for PostgreSQL monitor tables, with the same default of 600000 microseconds.

These variables can be adjusted via the Admin interface:

-- Retain 60 seconds of MySQL monitor history
SET mysql-monitor_history=60000000;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- Retain 60 seconds of PostgreSQL monitor history
SET pgsql-monitor_history=60000000;
LOAD PGSQL VARIABLES TO RUNTIME;
SAVE PGSQL VARIABLES TO DISK;

Setting a higher value retains more historical data for troubleshooting, while a lower value reduces memory and storage usage. The current values can be inspected with:

Admin> SELECT * FROM global_variables WHERE variable_name IN ('mysql-monitor_history', 'pgsql-monitor_history');
+-----------------------+----------------+
| variable_name         | variable_value |
+-----------------------+----------------+
| mysql-monitor_history | 600000         |
| pgsql-monitor_history | 600000         |
+-----------------------+----------------+
2 rows in set (0.00 sec)