Rechercher dans le manuel MySQL

26.4.6 Pre-Filtering by Thread

The threads table contains a row for each server thread. Each row contains information about a thread and indicates whether monitoring is enabled for it. For the Performance Schema to monitor a thread, these things must be true:

  • The thread_instrumentation consumer in the setup_consumers table must be YES.

  • The threads.INSTRUMENTED column must be YES.

  • Monitoring occurs only for those thread events produced from instruments that are enabled in the setup_instruments table.

The threads table also indicates for each server thread whether to perform historical event logging. This includes wait, stage, statement, and transaction events and affects logging to these tables:

events_waits_history
events_waits_history_long
events_stages_history
events_stages_history_long
events_statements_history
events_statements_history_long
events_transactions_history
events_transactions_history_long

For historical event logging to occur, these things must be true:

  • The appropriate history-related consumers in the setup_consumers table must be enabled. For example, wait event logging in the events_waits_history and events_waits_history_long tables requires the corresponding events_waits_history and events_waits_history_long consumers to be YES.

  • The threads.HISTORY column must be YES.

  • Logging occurs only for those thread events produced from instruments that are enabled in the setup_instruments table.

For foreground threads (resulting from client connections), the initial values of the INSTRUMENTED and HISTORY columns in threads table rows are determined by whether the user account associated with a thread matches any row in the setup_actors table. The values come from the ENABLED and HISTORY columns of the matching setup_actors table row.

For background threads, there is no associated user. INSTRUMENTED and HISTORY are YES by default and setup_actors is not consulted.

The initial setup_actors contents look like this:

  1. mysql> SELECT * FROM performance_schema.setup_actors;
  2. +------+------+------+---------+---------+
  3. | HOST | USER | ROLE | ENABLED | HISTORY |
  4. +------+------+------+---------+---------+
  5. | %    | %    | %    | YES     | YES     |
  6. +------+------+------+---------+---------+

The HOST and USER columns should contain a literal host or user name, or '%' to match any name.

The ENABLED and HISTORY columns indicate whether to enable instrumentation and historical event logging for matching threads, subject to the other conditions described previously.

When the Performance Schema checks for a match for each new foreground thread in setup_actors, it tries to find more specific matches first, using the USER and HOST columns (ROLE is unused):

  • Rows with USER='literal' and HOST='literal'.

  • Rows with USER='literal' and HOST='%'.

  • Rows with USER='%' and HOST='literal'.

  • Rows with USER='%' and HOST='%'.

The order in which matching occurs matters because different matching setup_actors rows can have different USER and HOST values. This enables instrumenting and historical event logging to be applied selectively per host, user, or account (user and host combination), based on the ENABLED and HISTORY column values:

  • When the best match is a row with ENABLED=YES, the INSTRUMENTED value for the thread becomes YES. When the best match is a row with HISTORY=YES, the HISTORY value for the thread becomes YES.

  • When the best match is a row with ENABLED=NO, the INSTRUMENTED value for the thread becomes NO. When the best match is a row with HISTORY=NO, the HISTORY value for the thread becomes NO.

  • When no match is found, the INSTRUMENTED and HISTORY values for the thread become NO.

The ENABLED and HISTORY columns in setup_actors rows can be set to YES or NO independent of one another. This means you can enable instrumentation separately from whether you collect historical events.

By default, monitoring and historical event collection are enabled for all new foreground threads because the setup_actors table initially contains a row with '%' for both HOST and USER. To perform more limited matching such as to enable monitoring only for some foreground threads, you must change this row because it matches any connection, and add rows for more specific HOST/USER combinations.

Suppose that you modify setup_actors as follows:

  1. UPDATE performance_schema.setup_actors
  2. SET ENABLED = 'NO', HISTORY = 'NO'
  3. WHERE HOST = '%' AND USER = '%';
  4. INSERT INTO performance_schema.setup_actors
  5. (HOST,USER,ROLE,ENABLED,HISTORY)
  6. VALUES('localhost','joe','%','YES','YES');
  7. INSERT INTO performance_schema.setup_actors
  8. (HOST,USER,ROLE,ENABLED,HISTORY)
  9. VALUES('hosta.example.com','joe','%','YES','NO');
  10. INSERT INTO performance_schema.setup_actors
  11. (HOST,USER,ROLE,ENABLED,HISTORY)
  12. VALUES('%','sam','%','NO','YES');

The UPDATE statement changes the default match to disable instrumentation and historical event collection. The INSERT statements add rows for more specific matches.

Now the Performance Schema determines how to set the INSTRUMENTED and HISTORY values for new connection threads as follows:

  • If joe connects from the local host, the connection matches the first inserted row. The INSTRUMENTED and HISTORY values for the thread become YES.

  • If joe connects from hosta.example.com, the connection matches the second inserted row. The INSTRUMENTED value for the thread becomes YES and the HISTORY value becomes NO.

  • If joe connects from any other host, there is no match. The INSTRUMENTED and HISTORY values for the thread become NO.

  • If sam connects from any host, the connection matches the third inserted row. The INSTRUMENTED value for the thread becomes NO and the HISTORY value becomes YES.

  • For any other connection, the row with HOST and USER set to '%' matches. This row now has ENABLED and HISTORY set to NO, so the INSTRUMENTED and HISTORY values for the thread become NO.

Modifications to the setup_actors table affect only foreground threads created subsequent to the modification, not existing threads. To affect existing threads, modify the INSTRUMENTED and HISTORY columns of threads table rows.


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