Rechercher dans le manuel MySQL

26.12.17.4 Statement Histogram Summary Tables

Table des matières

The Performance Schema maintains statement event summary tables that contain information about minimum, maximum, and average statement latency (see Section 26.12.17.3, “Statement Summary Tables”). Those tables permit high-level assessment of system performance. To permit assessment at a more fine-grained level, the Performance Schema also collects histogram data for statement latencies. These histograms provide additional insight into latency distributions.

Section 26.12.6, “Performance Schema Statement Event Tables” describes the events on which statement summaries are based. See that discussion for information about the content of statement events, the current and historical statement event tables, and how to control statement event collection, which is partially disabled by default.

Example statement histogram information:

  1. mysql> SELECT *
  2.        FROM performance_schema.events_statements_histogram_by_digest
  3.        WHERE SCHEMA_NAME = 'mydb' AND DIGEST = 'bb3f69453119b2d7b3ae40673a9d4c7c'
  4.        AND COUNT_BUCKET > 0 ORDER BY BUCKET_NUMBER\G
  5. *************************** 1. row ***************************
  6.            SCHEMA_NAME: mydb
  7.                 DIGEST: bb3f69453119b2d7b3ae40673a9d4c7c
  8.          BUCKET_NUMBER: 42
  9.       BUCKET_TIMER_LOW: 66069344
  10.      BUCKET_TIMER_HIGH: 69183097
  11.           COUNT_BUCKET: 1
  12. COUNT_BUCKET_AND_LOWER: 1
  13.        BUCKET_QUANTILE: 0.058824
  14. *************************** 2. row ***************************
  15.            SCHEMA_NAME: mydb
  16.                 DIGEST: bb3f69453119b2d7b3ae40673a9d4c7c
  17.          BUCKET_NUMBER: 43
  18.       BUCKET_TIMER_LOW: 69183097
  19.      BUCKET_TIMER_HIGH: 72443596
  20.           COUNT_BUCKET: 1
  21. COUNT_BUCKET_AND_LOWER: 2
  22.        BUCKET_QUANTILE: 0.117647
  23. *************************** 3. row ***************************
  24.            SCHEMA_NAME: mydb
  25.                 DIGEST: bb3f69453119b2d7b3ae40673a9d4c7c
  26.          BUCKET_NUMBER: 44
  27.       BUCKET_TIMER_LOW: 72443596
  28.      BUCKET_TIMER_HIGH: 75857757
  29.           COUNT_BUCKET: 2
  30. COUNT_BUCKET_AND_LOWER: 4
  31.        BUCKET_QUANTILE: 0.235294
  32. *************************** 4. row ***************************
  33.            SCHEMA_NAME: mydb
  34.                 DIGEST: bb3f69453119b2d7b3ae40673a9d4c7c
  35.          BUCKET_NUMBER: 45
  36.       BUCKET_TIMER_LOW: 75857757
  37.      BUCKET_TIMER_HIGH: 79432823
  38.           COUNT_BUCKET: 6
  39. COUNT_BUCKET_AND_LOWER: 10
  40.        BUCKET_QUANTILE: 0.625000
  41. ...

For example, in row 3, these values indicate that 23.52% of queries run in under 75.86 microseconds:

  1. BUCKET_TIMER_HIGH: 75857757
  2.   BUCKET_QUANTILE: 0.235294

In row 4, these values indicate that 62.50% of queries run in under 79.44 microseconds:

  1. BUCKET_TIMER_HIGH: 79432823
  2.   BUCKET_QUANTILE: 0.625000

Each statement histogram summary table has one or more grouping columns to indicate how the table aggregates events:

A histogram consists of N buckets, where each row represents one bucket, with the bucket number indicated by the BUCKET_NUMBER column. Bucket numbers begin with 0.

Each statement histogram summary table has these summary columns containing aggregated values:

  • BUCKET_TIMER_LOW, BUCKET_TIMER_HIGH

    A bucket counts statements that have a latency, in picoseconds, measured between BUCKET_TIMER_LOW and BUCKET_TIMER_HIGH:

    • The value of BUCKET_TIMER_LOW for the first bucket (BUCKET_NUMBER = 0) is 0.

    • The value of BUCKET_TIMER_LOW for a bucket (BUCKET_NUMBER = k) is the same as BUCKET_TIMER_HIGH for the previous bucket (BUCKET_NUMBER = k−1)

    • The last bucket is a catchall for statements that have a latency exceeding previous buckets in the histogram.

  • COUNT_BUCKET

    The number of statements measured with a latency in the interval from BUCKET_TIMER_LOW up to but not including BUCKET_TIMER_HIGH.

  • COUNT_BUCKET_AND_LOWER

    The number of statements measured with a latency in the interval from 0 up to but not including BUCKET_TIMER_HIGH.

  • BUCKET_QUANTILE

    The proportion of statements that fall into this or a lower bucket. This proportion corresponds by definition to COUNT_BUCKET_AND_LOWER / SUM(COUNT_BUCKET) and is displayed as a convenience column.

The statement histogram summary tables have these indexes:

TRUNCATE TABLE is permitted for statement histogram summary tables. Truncation sets the COUNT_BUCKET and COUNT_BUCKET_AND_LOWER columns to 0.

In addition, truncating events_statements_summary_by_digest implicitly truncates events_statements_histogram_by_digest, and truncating events_statements_summary_global_by_event_name implicitly truncates events_statements_histogram_global.


Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-statement-histogram-summary-tables.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut