Rechercher dans le manuel MySQL

26.4.5 Pre-Filtering by Object

The setup_objects table controls whether the Performance Schema monitors particular table and stored program objects. The initial setup_objects contents look like this:

  1. mysql> SELECT * FROM performance_schema.setup_objects;
  2. +-------------+--------------------+-------------+---------+-------+
  3. | OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
  4. +-------------+--------------------+-------------+---------+-------+
  5. | EVENT       | mysql              | %           | NO      | NO    |
  6. | EVENT       | performance_schema | %           | NO      | NO    |
  7. | EVENT       | information_schema | %           | NO      | NO    |
  8. | EVENT       | %                  | %           | YES     | YES   |
  9. | FUNCTION    | mysql              | %           | NO      | NO    |
  10. | FUNCTION    | performance_schema | %           | NO      | NO    |
  11. | FUNCTION    | information_schema | %           | NO      | NO    |
  12. | FUNCTION    | %                  | %           | YES     | YES   |
  13. | PROCEDURE   | mysql              | %           | NO      | NO    |
  14. | PROCEDURE   | performance_schema | %           | NO      | NO    |
  15. | PROCEDURE   | information_schema | %           | NO      | NO    |
  16. | PROCEDURE   | %                  | %           | YES     | YES   |
  17. | TABLE       | mysql              | %           | NO      | NO    |
  18. | TABLE       | performance_schema | %           | NO      | NO    |
  19. | TABLE       | information_schema | %           | NO      | NO    |
  20. | TABLE       | %                  | %           | YES     | YES   |
  21. | TRIGGER     | mysql              | %           | NO      | NO    |
  22. | TRIGGER     | performance_schema | %           | NO      | NO    |
  23. | TRIGGER     | information_schema | %           | NO      | NO    |
  24. | TRIGGER     | %                  | %           | YES     | YES   |
  25. +-------------+--------------------+-------------+---------+-------+

Modifications to the setup_objects table affect object monitoring immediately.

The OBJECT_TYPE column indicates the type of object to which a row applies. TABLE filtering affects table I/O events (wait/io/table/sql/handler instrument) and table lock events (wait/lock/table/sql/handler instrument).

The OBJECT_SCHEMA and OBJECT_NAME columns should contain a literal schema or object name, or '%' to match any name.

The ENABLED column indicates whether matching objects are monitored, and TIMED indicates whether to collect timing information. Setting the TIMED column affects Performance Schema table contents as described in Section 26.4.1, “Performance Schema Event Timing”.

The effect of the default object configuration is to instrument all objects except those in the mysql, INFORMATION_SCHEMA, and performance_schema databases. (Tables in the INFORMATION_SCHEMA database are not instrumented regardless of the contents of setup_objects; the row for information_schema.% simply makes this default explicit.)

When the Performance Schema checks for a match in setup_objects, it tries to find more specific matches first. For rows that match a given OBJECT_TYPE, the Performance Schema checks rows in this order:

  • Rows with OBJECT_SCHEMA='literal' and OBJECT_NAME='literal'.

  • Rows with OBJECT_SCHEMA='literal' and OBJECT_NAME='%'.

  • Rows with OBJECT_SCHEMA='%' and OBJECT_NAME='%'.

For example, with a table db1.t1, the Performance Schema looks in TABLE rows for a match for 'db1' and 't1', then for 'db1' and '%', then for '%' and '%'. The order in which matching occurs matters because different matching setup_objects rows can have different ENABLED and TIMED values.

For table-related events, the Performance Schema combines the contents of setup_objects with setup_instruments to determine whether to enable instruments and whether to time enabled instruments:

  • For tables that match a row in setup_objects, table instruments produce events only if ENABLED is YES in both setup_instruments and setup_objects.

  • The TIMED values in the two tables are combined, so that timing information is collected only when both values are YES.

For stored program objects, the Performance Schema takes the ENABLED and TIMED columns directly from the setup_objects row. There is no combining of values with setup_instruments.

Suppose that setup_objects contains the following TABLE rows that apply to db1, db2, and db3:

  1. +-------------+---------------+-------------+---------+-------+
  2. | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED |
  3. +-------------+---------------+-------------+---------+-------+
  4. | TABLE       | db1           | t1          | YES     | YES   |
  5. | TABLE       | db1           | t2          | NO      | NO    |
  6. | TABLE       | db2           | %           | YES     | YES   |
  7. | TABLE       | db3           | %           | NO      | NO    |
  8. | TABLE       | %             | %           | YES     | YES   |
  9. +-------------+---------------+-------------+---------+-------+

If an object-related instrument in setup_instruments has an ENABLED value of NO, events for the object are not monitored. If the ENABLED value is YES, event monitoring occurs according to the ENABLED value in the relevant setup_objects row:

  • db1.t1 events are monitored

  • db1.t2 events are not monitored

  • db2.t3 events are monitored

  • db3.t4 events are not monitored

  • db4.t5 events are monitored

Similar logic applies for combining the TIMED columns from the setup_instruments and setup_objects tables to determine whether to collect event timing information.

If a persistent table and a temporary table have the same name, matching against setup_objects rows occurs the same way for both. It is not possible to enable monitoring for one table but not the other. However, each table is instrumented separately.


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-performance-schema-object-filtering.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