Query Digest — Understanding Normalized Queries
ProxySQL normalizes all queries into digests — query templates with literal values replaced by placeholders. This lets ProxySQL group identical query patterns together and track their collective performance, even when they have different WHERE clause values.
SQL — Understanding Digests
-- Example: These three queries produce the same digest:
SELECT * FROM orders WHERE id = 1;
SELECT * FROM orders WHERE id = 42;
SELECT * FROM orders WHERE id = 999;
-- Digest (normalized form):
-- SELECT * FROM orders WHERE id = ?
-- Query the digest table to see this
SELECT digest, digest_text, count_star, avg_time
FROM stats.stats_mysql_query_digest
WHERE digest_text LIKE 'SELECT * FROM orders%'; Finding Slow Queries
SQL — Find Slow Queries
-- Queries with highest average execution time
SELECT hostgroup,
schemaname,
digest_text,
count_star AS executions,
ROUND(avg_time/1000, 2) AS avg_ms,
ROUND(max_time/1000, 2) AS max_ms,
ROUND(sum_time/1000000, 2) AS total_sec
FROM stats.stats_mysql_query_digest
ORDER BY avg_time DESC
LIMIT 20;
-- Queries consuming most total time (optimization priority)
SELECT digest_text,
count_star,
ROUND(sum_time/1000000, 2) AS total_sec,
ROUND(avg_time/1000, 2) AS avg_ms
FROM stats.stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 20;
-- Queries with high execution count (potential for caching)
SELECT digest_text, count_star, avg_time
FROM stats.stats_mysql_query_digest
ORDER BY count_star DESC
LIMIT 20; Slow Query Threshold
SQL — Slow Query Logging
-- Set slow query threshold (queries slower than this are logged)
UPDATE global_variables SET variable_value='1000'
WHERE variable_name='mysql-long_query_time';
-- Enable slow query log
UPDATE global_variables SET variable_value='1'
WHERE variable_name='mysql-query_digests';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
-- Count slow queries
SELECT variable_name, variable_value
FROM stats.stats_mysql_global
WHERE variable_name = 'Slow_queries'; Query Digest Variables
| Variable | Default | Description |
|---|---|---|
| mysql-query_digests | true | Enable query digest tracking |
| mysql-query_digests_max_digest_length | 2048 | Max characters in digest text |
| mysql-query_digests_max_query_length | 65000 | Max query length to track |
| mysql-long_query_time | 1000 | Slow query threshold (ms) |
| mysql-query_digests_grouping_limit | -1 | Max groups in digest (-1=unlimited) |
ProxySQL Query Log
SQL + Bash — Query Logging
-- Enable query logging for specific rule
UPDATE mysql_query_rules SET log=1
WHERE rule_id=2;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
-- The log goes to: /var/lib/proxysql/proxysql_queries.log
-- Format: timestamp | thread_id | username | schema | query
-- View log file from shell
tail -f /var/lib/proxysql/proxysql_queries.log Query Pattern Analysis
SQL — Query Pattern Analysis
-- Distribution of queries by type
SELECT
CASE
WHEN digest_text LIKE 'SELECT%' THEN 'SELECT'
WHEN digest_text LIKE 'INSERT%' THEN 'INSERT'
WHEN digest_text LIKE 'UPDATE%' THEN 'UPDATE'
WHEN digest_text LIKE 'DELETE%' THEN 'DELETE'
WHEN digest_text LIKE 'BEGIN%' OR digest_text LIKE 'START%' THEN 'TRANSACTION'
WHEN digest_text LIKE 'COMMIT%' THEN 'COMMIT'
WHEN digest_text LIKE 'ROLLBACK%' THEN 'ROLLBACK'
ELSE 'OTHER'
END AS query_type,
COUNT(*) AS distinct_patterns,
SUM(count_star) AS total_executions,
ROUND(SUM(sum_time)/1000000, 2) AS total_sec
FROM stats.stats_mysql_query_digest
GROUP BY query_type
ORDER BY total_executions DESC;
-- Top tables accessed (extract from digest)
SELECT digest_text, count_star
FROM stats.stats_mysql_query_digest
WHERE digest_text LIKE '%FROM orders%'
ORDER BY count_star DESC;💡 Note: Use query digest data to identify candidates for query caching (high count_star, low data volatility) and query optimization (high avg_time or sum_time).
