SHOW VARIABLES

SHOW [{GLOBAL | SESSION}] VARIABLES LIKE 'pattern'

It returns the current values of a few server-wide variables. Also, support for GLOBAL and SESSION clauses was added.

mysql> SHOW GLOBAL VARIABLES;
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| autocommit               | 1         |
| collation_connection     | libc_ci   |
| query_log_format         | sphinxql  |
| log_level                | info      |
| max_allowed_packet       | 134217728 |
| character_set_client     | utf8      |
| character_set_connection | utf8      |
| grouping_in_utc          | 0         |
| last_insert_id           | 123, 200  |
+--------------------------+-----------+
9 rows in set (0.00 sec)
mysql> show variables like '%log%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_log_format | sphinxql |
| log_level        | info     |
+------------------+----------+
2 rows in set (0.00 sec)
mysql> show session variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | 0     |
+---------------+-------+
1 row in set (0.00 sec)

⪢ Profiling

Query profile

The SQL SHOW PROFILE statement and the "profile": true JSON interface option both provide a detailed execution profile of the executed query. In the case of SQL, profiling must be enabled in the current session before running the statement to be instrumented. This can be accomplished with the SET profiling=1 statement. By default, profiling is disabled to prevent potential performance implications, resulting in an empty profile if not enabled.

Each profiling result includes the following fields:

  • Status column briefly describes the specific state where the time was spent. See below.
  • Duration column shows the wall clock time, in seconds.
  • Switches column displays the number of times the query engine changed to the given state. These are merely logical engine state switches and not any OS level context switches or function calls (although some sections might actually map to function calls), and they do not have any direct effect on performance. In a sense, the number of switches is just the number of times the respective instrumentation point was hit.
  • Percent column shows the percentage of time spent in this state.

States in the profile are returned in a prerecorded order that roughly maps (but is not identical) to the actual query order.

The list of states may (and will) change over time as we refine the states. Here's a brief description of the currently profiled states.

  • unknown: generic catch-all state. Accounts for not-yet-instrumented code or small miscellaneous tasks that don't really belong in any other state but are too small to warrant their own state.
  • net_read: reading the query from the network (i.e., the application).
  • io: generic file IO time.
  • dist_connect: connecting to remote agents in the distributed table case.
  • sql_parse: parsing the SQL syntax.
  • dict_setup: dictionary and tokenizer setup.
  • parse: parsing the full-text query syntax.
  • transforms: full-text query transformations (wildcard and other expansions, simplification, etc.).
  • init: initializing the query evaluation.
  • open: opening the table files.
  • read_docs: IO time spent reading document lists.
  • read_hits: IO time spent reading keyword positions.
  • get_docs: computing the matching documents.
  • get_hits: computing the matching positions.
  • filter: filtering the full-text matches.
  • rank: computing the relevance rank.
  • sort: sorting the matches.
  • finalize: finalizing the per-table search result set (last stage expressions, etc.).
  • dist_wait: waiting for remote results from agents in the distributed table case.
  • aggregate: aggregating multiple result sets.
  • net_write: writing the result set to the network.
‹›
  • SQL
  • JSON
📋
SET profiling=1;

SELECT id FROM forum WHERE MATCH('the best') LIMIT 1;

SHOW PROFILE;
‹›
Response
Query OK, 0 rows affected (0.00 sec)

+--------+
| id     |
+--------+
| 241629 |
+--------+
1 row in set (0.35 sec)

+--------------+----------+----------+---------+
| Status       | Duration | Switches | Percent |
+--------------+----------+----------+---------+
| unknown      | 0.000557 | 5        | 0.16    |
| net_read     | 0.000016 | 1        | 0.00    |
| local_search | 0.000076 | 1        | 0.02    |
| sql_parse    | 0.000121 | 1        | 0.03    |
| dict_setup   | 0.000003 | 1        | 0.00    |
| parse        | 0.000072 | 1        | 0.02    |
| transforms   | 0.000331 | 2        | 0.10    |
| init         | 0.001945 | 3        | 0.56    |
| read_docs    | 0.001257 | 76       | 0.36    |
| read_hits    | 0.002598 | 186      | 0.75    |
| get_docs     | 0.089328 | 2691     | 25.80   |
| get_hits     | 0.189626 | 2799     | 54.78   |
| filter       | 0.009369 | 2613     | 2.71    |
| rank         | 0.029669 | 7760     | 8.57    |
| sort         | 0.019070 | 2531     | 5.51    |
| finalize     | 0.000001 | 1        | 0.00    |
| clone_attrs  | 0.002009 | 1        | 0.58    |
| aggregate    | 0.000054 | 2        | 0.02    |
| net_write    | 0.000076 | 2        | 0.02    |
| eval_post    | 0.000001 | 1        | 0.00    |
| total        | 0.346179 | 18678    | 0       |
+--------------+----------+----------+---------+
21 rows in set (0.00 sec)