MCP Stats Endpoint and Tables

ProxySQL v4.0.10 registers 20 tools on /mcp/stats. Most tools are read-only, but flush_query_log and flush_queries mutate statistics state. This page also documents the separate SQL tables that measure MCP tool use.

Common Conventions

• db_type defaults to mysql where present and accepts mysql or pgsql.
• server filters in show_connections, show_free_connections, and show_connection_history use host:port; those handlers require an integer port from 1 through 65535. show_query_log has different behavior documented with that tool.
• Pagination arguments default to limit=100 and offset=0 unless stated otherwise.
• Historical intervals are 30m, 1h, 2h, 4h, 6h, 8h, 12h, 1d, 3d, 7d, 30d, or 90d. Intervals through 6 hours use raw data; intervals from 8 hours use hourly data.
• Read failures include invalid enumerated or numeric input where the handler validates it, unavailable ProxySQL subsystems, and missing or failed Admin/stats database queries. Tool errors are returned as MCP content with isError: true.

Live Data Tools

show_status

• Purpose/classification: Read-only global status variables.
• Protocols: MySQL and PostgreSQL.
• Arguments: db_type; category (connections, queries, commands, pool_ops, monitor, query_cache, prepared_stmts, security, memory, errors, logger, system, or mirror); variable_name SQL-LIKE pattern. variable_name takes precedence over category.
• Result fields: db_type; variables[] with variable_name and value.
• Limits/failures: No row cap. Unknown categories and failed stats-table reads return errors.

show_processlist

• Purpose/classification: Read-only snapshot of active frontend sessions.
• Protocols: MySQL and PostgreSQL; PostgreSQL rows additionally contain backend_pid and backend_state.
• Arguments: db_type, username, database, hostgroup, command, session_id, min_time_ms, match_info, info_case_sensitive (default false), sort_by (time_ms, session_id, username, hostgroup, or command), sort_order (asc or desc), limit, and offset.
• Result fields: db_type, total_sessions, requested_limit, requested_offset, effective_limit, limit_cap, sort_by, sort_order; sessions[] with session/thread IDs, user, database, client and backend addresses, hostgroup, command, time_ms, and info; summary maps by user, hostgroup, and command.
• Limits/failures: limit and offset must be non-negative. The configured cap is mcp-stats_show_processlist_max_rows (default 200, allowed 1–1000) and the handler hard-caps it at 1000. Invalid filters/sorts, an oversized session ID, or an unavailable protocol thread handler fail.

show_queries

• Purpose/classification: Read-only in-memory Top-K query-digest statistics.
• Protocols: MySQL and PostgreSQL.
• Arguments: db_type; sort_by (count, avg_time, sum_time, max_time, or rows_sent); limit, offset, min_count, min_time_us, database, username, hostgroup, digest (unsigned decimal, 0x hex, or unprefixed hex), match_digest_text, and digest_text_case_sensitive.
• Result fields: db_type, total_digests, requested/effective pagination and limit_cap; queries[] with digest/text, hostgroup, database, username, client address, counts/timestamps, min/max/average/sum time in microseconds, and affected/sent row totals; summary totals for queries and time.
• Limits/failures: Numeric filters and pagination must be non-negative. mcp-stats_show_queries_max_rows defaults to 200, accepts 1–1000, and is hard-capped at 1000; requested limit and offset are each clamped to that cap. Invalid sort, digest, protocol, or unavailable Admin state fails.

show_commands

• Purpose/classification: Read-only in-memory command counters and latency histograms.
• Protocols: MySQL and PostgreSQL.
• Arguments: db_type, exact command, limit, and offset.
• Result fields: commands[] with command, count, total/average microseconds, 12 latency buckets from cnt_100us through cnt_INFs, and p50/p90/p95/p99 estimates; summary count and time for the returned page.
• Limits/failures: Pagination must be non-negative; no additional handler cap. Invalid protocol or an unavailable query processor fails.

show_connections

• Purpose/classification: Read-only backend connection-pool snapshot.
• Protocols: MySQL and PostgreSQL; queries_gtid_sync is MySQL-only.
• Arguments: db_type, hostgroup, server, and status (ONLINE, SHUNNED, OFFLINE_SOFT, or OFFLINE_HARD). The removed compatibility argument detail=true returns an error directing clients to show_free_connections.
• Result fields: servers[] with hostgroup/address/status, used/free/success/error/max connections, query and byte counters, latency, utilization, and error rate; summary server, status, connection, query, and utilization totals.
• Limits/failures: No pagination cap. Invalid protocol/server syntax, detail=true, or an unavailable hostgroup manager fails.

show_free_connections

• Purpose/classification: Read-only but sensitive debug snapshot of individual free backend connections.
• Protocols: MySQL and PostgreSQL, with protocol-specific connection metadata.
• Arguments: db_type, hostgroup, and server.
• Result fields: free_connections[] with file descriptor, hostgroup/server, user, database/schema, session settings, idle time, statistics, and protocol-specific info; summary total and counts by hostgroup.
• Limits/failures: Disabled by default. It fails unless mcp-stats_enable_debug_tools=true has been loaded to runtime; invalid protocol/server syntax or an unavailable hostgroup manager also fails. No row cap is applied after enabling it.

show_errors

• Purpose/classification: Read-only aggregated backend error statistics.
• Protocols: MySQL uses errno; PostgreSQL uses sqlstate.
• Arguments: db_type, protocol-specific errno or sqlstate, username, database, hostgroup, min_count, sort_by (count, first_seen, or last_seen), limit, and offset.
• Result fields: total error types/count; errors[] with hostgroup, backend, user/client/database, errno or SQLSTATE, count, first/last seen, last error, and hourly frequency; summaries by error identifier and hostgroup.
• Limits/failures: Default page is 100 with no hard cap. Stats query or count failures return errors.

show_users

• Purpose/classification: Read-only frontend-user connection utilization.
• Protocols: MySQL (including LDAP users when loaded) and PostgreSQL.
• Arguments: db_type, exact username, limit, and offset.
• Result fields: users[] with username, current/max frontend connections, utilization percentage, and normal, near_limit, or at_limit status; page summary totals and utilization.
• Limits/failures: Non-positive limits become 100, limits above 1000 become 1000, and negative offsets become zero. Invalid protocol or an unavailable authentication module fails.

show_client_cache

• Purpose/classification: Read-only client-host error cache.
• Protocols: MySQL and PostgreSQL.
• Arguments: db_type, client_address, min_error_count, limit, and offset.
• Result fields: hosts[] with client address, error count, and last update; summary host/error totals.
• Limits/failures: Default page is 100 with no hard cap. Stats-table read failures return an error.

show_query_rules

• Purpose/classification: Read-only MySQL/PostgreSQL query-rule hit counters (not MCP query-rule hits).
• Protocols: MySQL and PostgreSQL.
• Arguments: db_type, rule_id, min_hits, include_zero_hits (default false), limit, and offset.
• Result fields: db_type, unfiltered total_rules, rules[] with rule_id and hits, and page summary hit counts.
• Limits/failures: Default page is 100 with no hard cap. Rule query or count failures return errors.

show_prepared_statements

• Purpose/classification: Read-only prepared-statement registry.
• Protocols: MySQL and PostgreSQL.
• Arguments: db_type, username, and database.
• Result fields: total and statements[] with ID, database, username, digest, client/server reference counts, query text, MySQL num_columns/num_params, or PostgreSQL num_param_types.
• Limits/failures: No pagination or row cap. Stats-table read failures return an error.

show_gtid

• Purpose/classification: Read-only GTID execution state.
• Protocols: MySQL only. There is no db_type argument.
• Arguments: hostname and port filters.
• Result fields: servers[] with hostname, port, gtid_executed, and events; summary server/event totals.
• Limits/failures: No row cap. Failure to refresh/read stats_mysql_gtid_executed returns an error.

show_cluster

• Purpose/classification: Read-only ProxySQL cluster health, metrics, and checksums.
• Protocols: Protocol-neutral ProxySQL cluster data.
• Arguments: hostname and include_checksums (default true).
• Result fields: health, node counts, master, nodes[] with check and workload metrics; optional checksum rows; sync/query/connection/ping summary.
• Limits/failures: No row cap. Status, metrics, or requested checksum query failures return errors.

Historical Data Tools

show_system_history

• Purpose/classification: Read-only CPU and memory history from statsdb_disk.
• Protocols: Protocol-neutral.
• Arguments: metric (cpu, memory, or all, default all) and interval.
• Result fields: interval, resolution; CPU rows with timestamp/user/system time and memory rows with allocated, resident, active, mapped, metadata, and retained bytes.
• Limits/failures: The fixed interval set bounds the time range to 90 days. Invalid intervals fail; unavailable history data can produce empty arrays.

show_query_cache_history

• Purpose/classification: Read-only query-cache history.
• Protocols: MySQL only; PostgreSQL is explicitly rejected.
• Arguments: db_type and interval.
• Result fields: db_type, interval/resolution, and data[] with timestamp, GET/GET_OK/SET counts, bytes, purges, entries, memory, and hit rate.
• Limits/failures: Time range is at most 90 days. PostgreSQL, invalid interval, and failed disk-history queries return errors.

show_connection_history

• Purpose/classification: Read-only global or per-server connection history.
• Protocols: MySQL only; PostgreSQL is explicitly rejected.
• Arguments: db_type, interval, scope (global, per_server, or all), hostgroup, and server.
• Result fields: protocol/interval/resolution/scope; optional global.connections, global.myhgm, and per_server rows with connection-pool counters.
• Limits/failures: Time range is at most 90 days and no row pagination is applied. PostgreSQL, invalid interval/server syntax, and absent history can fail or yield empty arrays.

show_query_history

• Purpose/classification: Read-only query-digest snapshots created by flush_queries.
• Protocols: MySQL and PostgreSQL.
• Arguments: db_type, dump_time, start_time, end_time, digest, username, database, limit, and offset.
• Result fields: snapshots[] grouped by dump_time; query rows contain hostgroup, database/user/client, digest/text, counts/timestamps/timings, and affected/sent rows; summary snapshot range.
• Limits/failures: Default page is 100 with no hard cap. Disk-history query failures return an error.

Query-log and Flush Tools

flush_query_log

• Purpose/classification: Write/mutating. Drains buffered MySQL query events to a queryable destination.
• Protocols: MySQL only.
• Arguments: destination (memory, disk, or both; default memory).
• Result fields: events_flushed and destination.
• Limits/failures: Invalid destination, unavailable MySQL logger/Admin, or unavailable requested stats database fails. This changes the buffer and destination tables.

show_query_log

• Purpose/classification: Read-only individual query events.
• Protocols: MySQL only.
• Arguments: source (memory or disk), username, database, query_digest, server, errno, errors_only, start_time, end_time, limit, and offset. Here server is an escaped exact string comparison against the event row’s server column; the handler does not parse host:port or validate a port range.
• Result fields: source and total; events[] with thread/user/database, times, digest/query, server/client, event type, hostgroup, row counts, errno, and error; error/time-range summary.
• Limits/failures: Default page is 100 with no hard cap. The source must exist and its query must succeed. Only MySQL query-event tables are read. Any string is accepted for server; malformed or noncanonical values normally return no matching rows rather than a validation error, while SQL metacharacters are escaped before the exact predicate is built.

flush_queries

• Purpose/classification: Write/mutating. Persists current digest statistics to history and resets the in-memory digest counters.
• Protocols: MySQL and PostgreSQL.
• Arguments: db_type.
• Result fields: db_type, digests_saved, and snapshot dump_time.
• Limits/failures: Invalid protocol, unavailable Admin, or unavailable disk stats database fails.

MCP Usage Statistics Tables

These SQL tables measure MCP calls themselves. They are distinct from the operational data returned by /mcp/stats.

The two digest tables use this schema:

CREATE TABLE stats_mcp_query_digest (
    tool_name VARCHAR NOT NULL,
    run_id INT,
    digest VARCHAR NOT NULL,
    digest_text VARCHAR NOT NULL,
    count_star INTEGER NOT NULL,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    sum_time INTEGER NOT NULL,
    min_time INTEGER NOT NULL,
    max_time INTEGER NOT NULL,
    PRIMARY KEY(tool_name, run_id, digest)
);

stats_mcp_query_digest_reset has the same columns and key.

The two tool-counter tables use this schema:

CREATE TABLE stats_mcp_query_tools_counters (
    endpoint VARCHAR NOT NULL,
    tool VARCHAR NOT NULL,
    schema VARCHAR NOT NULL,
    count INT NOT NULL,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    sum_time INTEGER NOT NULL,
    min_time INTEGER NOT NULL,
    max_time INTEGER NOT NULL,
    PRIMARY KEY(endpoint, tool, schema)
);

stats_mcp_query_tools_counters_reset has the same columns and key. Times are recorded in microseconds. MCP rule-hit statistics are documented with the MCP configuration tables.

stats_genai_global

This plugin-owned live projection is rebuilt before each Admin SELECT:

CREATE TABLE stats_genai_global (
    Variable_name VARCHAR NOT NULL,
    Value VARCHAR NOT NULL,
    PRIMARY KEY (Variable_name)
);

It reports four GenAI worker rows (genai_threads_initialized, genai_active_requests, genai_completed_requests, genai_failed_requests), three MCP rows (mcp_total_requests, mcp_failed_requests, mcp_active_connections), and 14 AI-manager rows: llm_total_requests, llm_cache_hits, llm_cache_misses, llm_local_model_calls, llm_cloud_model_calls, llm_total_response_time_ms, llm_cache_total_lookup_time_ms, llm_cache_total_store_time_ms, llm_cache_lookups, llm_cache_stores, anomaly_total_checks, anomaly_blocked_queries, anomaly_flagged_queries, and daily_cloud_spend_usd.

Rows from an unavailable component are omitted. Selecting the table does not reset any counter.