Outdated Version
You are viewing an older version of this section. View current production version.
SHOW PLANCACHE
Shows all query statements that MemSQL has compiled and executed, as well as cumulative query execution statistics associated with each plan.
SHOW PLANCACHE
Notes
SHOW PLANCACHEis a MemSQL extension (it doesn’t exist in MySQL).- All counters shown by
SHOW PLANCACHEare cleared when MemSQL is restarted. - This command can be run on any MemSQL node (see Node Requirements for MemSQL Commands).
Output
| Column | Description |
|---|---|
Database |
Context database selected with USE <db_name> when query was compiled. |
QueryText |
Query text with all numeric and string parameters replaced by tags (depending on parameter and/or query type). @ is used for integers and ^ for string parameters. ? is always used for INSERT queries. |
Commits |
Number of successful executions of the query. |
Rollbacks |
Number of unsuccessful executions of the query (i.e. if the query was aborted or it encountered runtime errors). |
RowCount |
Cumulative number of rows returned by a SELECT query or the cumulative number of rows inserted, updated, or deleted for an INSERT, UPDATE, or DELETE query. |
ExecutionTime |
Cumulative time (in milliseconds) spent executing the query. |
LogBufferTime |
NULL for SELECT queries, otherwise the cumulative time (in milliseconds) spent waiting to reserve space in the transaction buffer for this query. A larger transaction buffer and faster disk can help reduce it. |
LogFlushTime |
NULL for SELECT queries, otherwise the cumulative time (in milliseconds) spent waiting until changes made by this query are flushed to disk. A faster disk can help reduce it. |
RowLockTime |
NULL for SELECT queries, otherwise the cumulative time (in milliseconds) spent waiting to acquire exclusive row locks. |
StreamedRows |
(Cluster Only) Cumulative number of rows streamed from leaves and processed by the SELECT query. |
LeafNetworkTime |
(Cluster Only) Cumulative time (in milliseconds) spent waiting for results from leaves. This includes the time spent executing queries on the leaves. |
AverageMemoryUse |
The average amount of memory used to execute this query. Any temporary memory allocation needed to execute a query including those for hash tables, sorts, result tables, etc. is tracked here. |
Example
memsql> SHOW PLANCACHE;
+----------+---------------------------------------+---------+-----------+----------+---------------+---------------+--------------+-------------+
| Database | QueryText | Commits | Rollbacks | RowCount | ExecutionTime | LogBufferTime | LogFlushTime | RowLockTime |
+----------+---------------------------------------+---------+-----------+----------+---------------+---------------+--------------+-------------+
| perf | SELECT * FORM scans WHERE id = @ | 101 | 0 | 98 | 5 | NULL | NULL | NULL |
| perf | INSERT INTO scans VALUES (?, ?, ?, ?) | 120 | 0 | 120 | 9 | 0 | 0 | 0 |
+----------+---------------------------------------+---------+-----------+----------+---------------+---------------+--------------+-------------+
