Rechercher dans le manuel MySQL

13.7.6.30 SHOW PROFILE Syntax

Inhoudsopgave
  1. SHOW PROFILE [type [, type] ... ]
  2.     [FOR QUERY n]
  3.     [LIMIT row_count [OFFSET offset]]
  4.  
  5. type: {
  6.     ALL
  7.   | BLOCK IO
  8.   | CONTEXT SWITCHES
  9.   | CPU
  10.   | IPC
  11.   | MEMORY
  12.   | PAGE FAULTS
  13.   | SOURCE
  14.   | SWAPS
  15. }

The SHOW PROFILE and SHOW PROFILES statements display profiling information that indicates resource usage for statements executed during the course of the current session.

Note

The SHOW PROFILE and SHOW PROFILES statements are deprecated and will be removed in a future MySQL release. Use the Performance Schema instead; see Section 26.19.1, “Query Profiling Using Performance Schema”.

To control profiling, use the profiling session variable, which has a default value of 0 (OFF). Enable profiling by setting profiling to 1 or ON:

  1. mysql> SET profiling = 1;

SHOW PROFILES displays a list of the most recent statements sent to the server. The size of the list is controlled by the profiling_history_size session variable, which has a default value of 15. The maximum value is 100. Setting the value to 0 has the practical effect of disabling profiling.

All statements are profiled except SHOW PROFILE and SHOW PROFILES, 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, although there might be some minor differences in interpretion for the two statements for some status values (see Section 8.14, “Examining Thread Information”).

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.

  1. mysql> SELECT @@profiling;
  2. +-------------+
  3. | @@profiling |
  4. +-------------+
  5. |           0 |
  6. +-------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> SET profiling = 1;
  10. Query OK, 0 rows affected (0.00 sec)
  11.  
  12. mysql> DROP TABLE IF EXISTS t1;
  13. Query OK, 0 rows affected, 1 warning (0.00 sec)
  14.  
  15. mysql> CREATE TABLE T1 (id INT);
  16. Query OK, 0 rows affected (0.01 sec)
  17.  
  18. mysql> SHOW PROFILES;
  19. +----------+----------+--------------------------+
  20. | Query_ID | Duration | Query                    |
  21. +----------+----------+--------------------------+
  22. |        0 | 0.000088 | SET PROFILING = 1        |
  23. |        1 | 0.000136 | DROP TABLE IF EXISTS t1  |
  24. |        2 | 0.011947 | CREATE TABLE t1 (id INT) |
  25. +----------+----------+--------------------------+
  26. 3 rows in set (0.00 sec)
  27.  
  28. mysql> SHOW PROFILE;
  29. +----------------------+----------+
  30. | Status               | Duration |
  31. +----------------------+----------+
  32. | checking permissions | 0.000040 |
  33. | creating table       | 0.000056 |
  34. | After create         | 0.011363 |
  35. | query end            | 0.000375 |
  36. | freeing items        | 0.000089 |
  37. | logging slow query   | 0.000019 |
  38. | cleaning up          | 0.000005 |
  39. +----------------------+----------+
  40. 7 rows in set (0.00 sec)
  41.  
  42. mysql> SHOW PROFILE FOR QUERY 1;
  43. +--------------------+----------+
  44. | Status             | Duration |
  45. +--------------------+----------+
  46. | query end          | 0.000107 |
  47. | freeing items      | 0.000008 |
  48. | logging slow query | 0.000015 |
  49. | cleaning up        | 0.000006 |
  50. +--------------------+----------+
  51. 4 rows in set (0.00 sec)
  52.  
  53. mysql> SHOW PROFILE CPU FOR QUERY 2;
  54. +----------------------+----------+----------+------------+
  55. | Status               | Duration | CPU_user | CPU_system |
  56. +----------------------+----------+----------+------------+
  57. | checking permissions | 0.000040 | 0.000038 |   0.000002 |
  58. | creating table       | 0.000056 | 0.000028 |   0.000028 |
  59. | After create         | 0.011363 | 0.000217 |   0.001571 |
  60. | query end            | 0.000375 | 0.000013 |   0.000028 |
  61. | freeing items        | 0.000089 | 0.000010 |   0.000014 |
  62. | logging slow query   | 0.000019 | 0.000009 |   0.000010 |
  63. | cleaning up          | 0.000005 | 0.000003 |   0.000002 |
  64. +----------------------+----------+----------+------------+
  65. 7 rows in set (0.00 sec)
Note

Profiling is only partially functional on some architectures. For values that depend on the getrusage() system call, NULL is returned on systems such as Windows that do not support the call. In addition, profiling is per process and not per thread. This means that activity on threads within the server other than your own may affect the timing information that you see.

Profiling information is also available from the INFORMATION_SCHEMA PROFILING table. See Section 25.20, “The INFORMATION_SCHEMA PROFILING Table”. For example, the following queries are equivalent:

  1. SHOW PROFILE FOR QUERY 2;
  2.  
  3. SELECT STATE, FORMAT(DURATION, 6) AS DURATION
  4. FROM INFORMATION_SCHEMA.PROFILING
  5. WHERE QUERY_ID = 2 ORDER BY SEQ;

Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-show-profile.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

  1. Bekijk - html-document Taal van het document:en Manuel MySQL : https://dev.mysql.com/

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut