Circular Query Execution buffer is designed as low overhead feature to get queries which are run the most recently, allowing to access/sample these queries avoiding overhead of managing query log file.
The ring buffer of fixed size is allocated and allows quick query storage in the buffer (by copying the query) when it is prepared to be stored to slow query log size. Control of total size rather than number of queries allows to avoid memory allocation as well as potential exploits by storing queries close to max_allowed_packet_size in the buffer. For each query the same information as stored in the Slow Query Log with Percona patches should be stored and additionally:
- Query start timestamp with microsecond resolution
- Error Code (for failed queries)
- Pseudo Queries CONNECT and END to be able to check the sessions
- always increasing query identification number.
Gradually more items can be added to the list.
Important: Extra care has to be maintained to avoid locking issue. In particular we need to ensure data retrieval queries can be run not blocking whole buffer while they do so.
- Variable query_ring_buffer_size=XXX Can be set online allocating the ring buffer for storing the queries. If values is changed query storage is temporary disabled until new buffer is being allocated. Setting value to 0 globally disables logging.
- Variable query_ring_buffer_enabled=0/1 Specifies if tracing is enabled. Can be set globally as well as per session if only given session needs to be traced.
Show statement remains attractive statement for many cases because it can be implemented very flexible while Information Schema tables often does complete materialization to provide the data. The following are examples and syntax and functionality can be adjusted based on implementation complexity and other reasons.
- SHOW QUERIES - Dump Contents of query ring buffer.
- SHOW QUERIES LIMIT 10 - Show last 10 queries form the ring buffer.
- SHOW QUERIES STARTING <query id> Show queries with query_id more than current. (running it with last query id you previously got allows to get all queries)
- SHOW QUERIES FOR <session_id> Trace all queries for given session ID. Could be current session or previous session. This allows finding out all queries which given session has executed (ie in case of some error)
Informaton Schema is to get QUERY_RING_BUFFER table which represent complete contents of query buffer as it is now. This allows SQL language convenience for query analyzes though may have less efficient execution
Application may would like to analyze queries by streaming them from the server. This can be done by separate queries, like:
START QUERY DUMP FROM <query_id>
The command is to start sending the queries from given query ID (advancing in Query Ring Buffer as queries are added) similar as Replication works for binlog events.
If dump is going other slow network or application is slow to process events it is possible it would fall behind in processing. In this case you will see some queries are dropped because there is going to be a gap between query_id values sent by the stream.