SHOW PROFILES

Sintassi

SHOW PROFILE [tipo [, tipo] ... ]
    [FOR QUERY n]
    [LIMIT num_righe [OFFSET offset]]

tipo:
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS

Spiegazione

Le istruzioni SHOW PROFILES e SHOW PROFILE restituiscono informazioni sul profiling, che illustrano informazioni sull'utilizzo delle risorse da parte delle istruzioni eseguite nel corso della sessione corrente.

Il profiling è controllato dalla variabile di sessione profiling, il cui valore predefinito è 0 (OFF). Per abilitarlo, occorre impostare profiling a 1 oppure ON:

MariaDB [test]> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

SHOW PROFILES mostra un elenco delle ultime istruzioni che sono state inviate al master. Le dimensioni della lista sono controllate dalla variabile di sessione profiling_history_size, il cui valore predefinito è 15. Il massimo è 100. Impostandolo a 0 si disabilita il profiling.

All statements are profiled except SHOW PROFILES and SHOW PROFILE, so you will find neither of those statements in the profile list. Malformed statements are profiled. For example, SHOW PROFILING is an illegal statement, and a syntax error occurs if you try to execute it, but it will show up in the profiling list.

SHOW PROFILE displays detailed information about a single statement. Without the FOR QUERY n clause, the output pertains to the most recently executed statement. If FOR QUERY n is included, SHOW PROFILE displays information for statement n. The values of n correspond to the Query_ID values displayed by SHOW PROFILES.

The LIMIT row_count clause may be given to limit the output to row_count rows. If LIMIT is given, OFFSET offset may be added to begin the output offset rows into the full set of rows.

By default, SHOW PROFILE displays Status and Duration columns. The Status values are like the State values displayed by SHOW PROCESSLIST, althought there might be some minor differences in interpretion for the two statements for some status values (see http://dev.mysql.com/doc/refman/5.1/en/thread-information.html).

Optional type values may be specified to display specific additional types of information:

• ALL displays all information
• BLOCK IO displays counts for block input and output operations
• CONTEXT SWITCHES displays counts for voluntary and involuntary context switches
• CPU displays user and system CPU usage times
• IPC displays counts for messages sent and received
• MEMORY is not currently implemented
• PAGE FAULTS displays counts for major and minor page faults
• SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs
• SWAPS displays swap counts

Profiling is enabled per session. When a session ends, its profiling information is lost.

Examples:

MariaDB [(none)]> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [test]> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.24 sec)

MariaDB [test]> SHOW PROFILES;
+----------+------------+--------------------------+
| Query_ID | Duration   | Query                    |
+----------+------------+--------------------------+
|        1 | 0.00009200 | SELECT DATABASE()        |
|        2 | 0.00023800 | show databases           |
|        3 | 0.00018900 | show tables              |
|        4 | 0.00014700 | DROP TABLE IF EXISTS t1  |
|        5 | 0.24476900 | CREATE TABLE T1 (id INT) |
+----------+------------+--------------------------+
5 rows in set (0.01 sec)

MariaDB [test]> SHOW PROFILE;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000042 |
| checking permissions | 0.000044 |
| creating table       | 0.244645 |
| After create         | 0.000013 |
| query end            | 0.000003 |
| freeing items        | 0.000016 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000003 |
+----------------------+----------+
8 rows in set (0.00 sec)

MariaDB [test]> SHOW PROFILE FOR QUERY 4;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000126 |
| query end          | 0.000004 |
| freeing items      | 0.000012 |
| logging slow query | 0.000003 |
| cleaning up        | 0.000002 |
+--------------------+----------+
5 rows in set (0.00 sec)

MariaDB [test]> SHOW PROFILE CPU FOR QUERY 5;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000042 | 0.000000 |   0.000000 |
| checking permissions | 0.000044 | 0.000000 |   0.000000 |
| creating table       | 0.244645 | 0.000000 |   0.000000 |
| After create         | 0.000013 | 0.000000 |   0.000000 |
| query end            | 0.000003 | 0.000000 |   0.000000 |
| freeing items        | 0.000016 | 0.000000 |   0.000000 |
| logging slow query   | 0.000003 | 0.000000 |   0.000000 |
| cleaning up          | 0.000003 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+
8 rows in set (0.00 sec)