Rechercher dans le manuel MySQL
15.15.2 Monitoring InnoDB Mutex Waits Using Performance Schema
A mutex is a synchronization mechanism used in the code to enforce that only one thread at a given time can have access to a common resource. When two or more threads executing in the server need to access the same resource, the threads compete against each other. The first thread to obtain a lock on the mutex causes the other threads to wait until the lock is released.
For InnoDB
mutexes that are instrumented, mutex
waits can be monitored using
Performance Schema. Wait
event data collected in Performance Schema tables can help
identify mutexes with the most waits or the greatest total wait
time, for example.
The following example demonstrates how to enable
InnoDB
mutex wait instruments, how to enable
associated consumers, and how to query wait event data.
To view available
InnoDB
mutex wait instruments, query the Performance Schemasetup_instruments
table. AllInnoDB
mutex wait instruments are disabled by default.- FROM performance_schema.setup_instruments
- +---------------------------------------------------------+---------+-------+
- | NAME | ENABLED | TIMED |
- +---------------------------------------------------------+---------+-------+
- +---------------------------------------------------------+---------+-------+
Some
InnoDB
mutex instances are created at server startup and are only instrumented if the associated instrument is also enabled at server startup. To ensure that allInnoDB
mutex instances are instrumented and enabled, add the followingperformance-schema-instrument
rule to your MySQL configuration file:performance-schema-instrument='wait/synch/mutex/innodb/%=ON'
If you do not require wait event data for all
InnoDB
mutexes, you can disable specific instruments by adding additionalperformance-schema-instrument
rules to your MySQL configuration file. For example, to disableInnoDB
mutex wait event instruments related to full-text search, add the following rule:performance-schema-instrument='wait/synch/mutex/innodb/fts%=OFF'
NoteRules with a longer prefix such as
wait/synch/mutex/innodb/fts%
take precedence over rules with shorter prefixes such aswait/synch/mutex/innodb/%
.After adding the
performance-schema-instrument
rules to your configuration file, restart the server. All theInnoDB
mutexes except for those related to full text search are enabled. To verify, query thesetup_instruments
table. TheENABLED
andTIMED
columns should be set toYES
for the instruments that you enabled.- FROM performance_schema.setup_instruments
- +-------------------------------------------------------+---------+-------+
- | NAME | ENABLED | TIMED |
- +-------------------------------------------------------+---------+-------+
- ...
- +-------------------------------------------------------+---------+-------+
Enable wait event consumers by updating the
setup_consumers
table. Wait event consumers are disabled by default.- mysql> UPDATE performance_schema.setup_consumers
- Query OK, 3 rows affected (0.00 sec)
You can verify that wait event consumers are enabled by querying the
setup_consumers
table. Theevents_waits_current
,events_waits_history
, andevents_waits_history_long
consumers should be enabled.- +----------------------------------+---------+
- | NAME | ENABLED |
- +----------------------------------+---------+
- | events_statements_current | YES |
- | events_statements_history | YES |
- | events_transactions_current | YES |
- | events_transactions_history | YES |
- | events_waits_current | YES |
- | events_waits_history | YES |
- | events_waits_history_long | YES |
- | global_instrumentation | YES |
- | thread_instrumentation | YES |
- | statements_digest | YES |
- +----------------------------------+---------+
Once instruments and consumers are enabled, run the workload that you want to monitor. In this example, the mysqlslap load emulation client is used to simulate a workload.
shell> ./mysqlslap --auto-generate-sql --concurrency=100 --iterations=10 --number-of-queries=1000 --number-char-cols=6 --number-int-cols=6;
Query the wait event data. In this example, wait event data is queried from the
events_waits_summary_global_by_event_name
table which aggregates data found in theevents_waits_current
,events_waits_history
, andevents_waits_history_long
tables. Data is summarized by event name (EVENT_NAME
), which is the name of the instrument that produced the event. Summarized data includes:COUNT_STAR
The number of summarized wait events.
SUM_TIMER_WAIT
The total wait time of the summarized timed wait events.
MIN_TIMER_WAIT
The minimum wait time of the summarized timed wait events.
AVG_TIMER_WAIT
The average wait time of the summarized timed wait events.
MAX_TIMER_WAIT
The maximum wait time of the summarized timed wait events.
The following query returns the instrument name (
EVENT_NAME
), the number of wait events (COUNT_STAR
), and the total wait time for the events for that instrument (SUM_TIMER_WAIT
). Because waits are timed in picoseconds (trillionths of a second) by default, wait times are divided by 1000000000 to show wait times in milliseconds. Data is presented in descending order, by the number of summarized wait events (COUNT_STAR
). You can adjust theORDER BY
clause to order the data by total wait time.- FROM performance_schema.events_waits_summary_global_by_event_name
- +---------------------------------------------------------+------------+-------------------+
- | EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT_MS |
- +---------------------------------------------------------+------------+-------------------+
- +---------------------------------------------------------+------------+-------------------+
NoteThe preceding result set includes wait event data produced during the startup process. To exclude this data, you can truncate the
events_waits_summary_global_by_event_name
table immediately after startup and before running your workload. However, the truncate operation itself may produce a negligible amount wait event data.
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-monitor-innodb-mutex-waits-performance-schema.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
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.