Rechercher dans le manuel MySQL
26.1 Performance Schema Quick Start
This section briefly introduces the Performance Schema with examples that show how to use it. For additional examples, see Section 26.19, “Using the Performance Schema to Diagnose Problems”.
The Performance Schema is enabled by default. To enable or disable
it explicitly, start the server with the
performance_schema
variable set
to an appropriate value. For example, use these lines in the
server my.cnf
file:
[mysqld]
performance_schema=ON
When the server starts, it sees
performance_schema
and attempts
to initialize the Performance Schema. To verify successful
initialization, use this statement:
- +--------------------+-------+
- +--------------------+-------+
- +--------------------+-------+
A value of ON
means that the Performance Schema
initialized successfully and is ready for use. A value of
OFF
means that some error occurred. Check the
server error log for information about what went wrong.
The Performance Schema is implemented as a storage engine, so you
will see it listed in the output from the
INFORMATION_SCHEMA.ENGINES
table or
the SHOW ENGINES
statement:
The PERFORMANCE_SCHEMA
storage engine
operates on tables in the performance_schema
database. You can make performance_schema
the
default database so that references to its tables need not be
qualified with the database name:
Performance Schema tables are stored in the
performance_schema
database. Information about
the structure of this database and its tables can be obtained, as
for any other database, by selecting from the
INFORMATION_SCHEMA
database or by using
SHOW
statements. For example, use
either of these statements to see what Performance Schema tables
exist:
- +------------------------------------------------------+
- | TABLE_NAME |
- +------------------------------------------------------+
- | accounts |
- | cond_instances |
- ...
- | events_stages_current |
- | events_stages_history |
- | events_stages_history_long |
- | events_stages_summary_by_account_by_event_name |
- | events_stages_summary_by_host_by_event_name |
- | events_stages_summary_by_thread_by_event_name |
- | events_stages_summary_by_user_by_event_name |
- | events_stages_summary_global_by_event_name |
- | events_statements_current |
- | events_statements_history |
- | events_statements_history_long |
- ...
- | file_instances |
- | file_summary_by_event_name |
- | file_summary_by_instance |
- | host_cache |
- | hosts |
- | memory_summary_by_account_by_event_name |
- | memory_summary_by_host_by_event_name |
- | memory_summary_by_thread_by_event_name |
- | memory_summary_by_user_by_event_name |
- | memory_summary_global_by_event_name |
- | metadata_locks |
- | mutex_instances |
- | objects_summary_global_by_type |
- | performance_timers |
- | replication_connection_configuration |
- | replication_connection_status |
- | replication_applier_configuration |
- | replication_applier_status |
- | replication_applier_status_by_coordinator |
- | replication_applier_status_by_worker |
- | rwlock_instances |
- | session_account_connect_attrs |
- | session_connect_attrs |
- | setup_actors |
- | setup_consumers |
- | setup_instruments |
- | setup_objects |
- | socket_instances |
- | socket_summary_by_event_name |
- | socket_summary_by_instance |
- | table_handles |
- | table_io_waits_summary_by_index_usage |
- | table_io_waits_summary_by_table |
- | table_lock_waits_summary_by_table |
- | threads |
- | users |
- +------------------------------------------------------+
- +------------------------------------------------------+
- | Tables_in_performance_schema |
- +------------------------------------------------------+
- | accounts |
- | cond_instances |
- | events_stages_current |
- | events_stages_history |
- | events_stages_history_long |
- ...
The number of Performance Schema tables increases over time as implementation of additional instrumentation proceeds.
The name of the performance_schema
database is
lowercase, as are the names of tables within it. Queries should
specify the names in lowercase.
To see the structure of individual tables, use
SHOW CREATE TABLE
:
- *************************** 1. row ***************************
- Table: setup_consumers
Table structure is also available by selecting from tables such as
INFORMATION_SCHEMA.COLUMNS
or by
using statements such as SHOW
COLUMNS
.
Tables in the performance_schema
database can
be grouped according to the type of information in them: Current
events, event histories and summaries, object instances, and setup
(configuration) information. The following examples illustrate a
few uses for these tables. For detailed information about the
tables in each group, see
Section 26.12, “Performance Schema Table Descriptions”.
Initially, not all instruments and consumers are enabled, so the performance schema does not collect all events. To turn all of these on and enable event timing, execute two statements (the row counts may differ depending on MySQL version):
To see what the server is doing at the moment, examine the
events_waits_current
table. It
contains one row per thread showing each thread's most recent
monitored event:
- FROM performance_schema.events_waits_current\G
- *************************** 1. row ***************************
- THREAD_ID: 0
- EVENT_ID: 5523
- END_EVENT_ID: 5523
- EVENT_NAME: wait/synch/mutex/mysys/THR_LOCK::mutex
- SOURCE: thr_lock.c:525
- TIMER_START: 201660494489586
- TIMER_END: 201660494576112
- TIMER_WAIT: 86526
- SPINS: NULL
- OBJECT_SCHEMA: NULL
- OBJECT_NAME: NULL
- INDEX_NAME: NULL
- OBJECT_TYPE: NULL
- OBJECT_INSTANCE_BEGIN: 142270668
- NESTING_EVENT_ID: NULL
- NESTING_EVENT_TYPE: NULL
- OPERATION: lock
- NUMBER_OF_BYTES: NULL
- FLAGS: 0
- ...
This event indicates that thread 0 was waiting for 86,526
picoseconds to acquire a lock on
THR_LOCK::mutex
, a mutex in the
mysys
subsystem. The first few columns provide
the following information:
The ID columns indicate which thread the event comes from and the event number.
EVENT_NAME
indicates what was instrumented andSOURCE
indicates which source file contains the instrumented code.The timer columns show when the event started and stopped and how long it took. If an event is still in progress, the
TIMER_END
andTIMER_WAIT
values areNULL
. Timer values are approximate and expressed in picoseconds. For information about timers and event time collection, see Section 26.4.1, “Performance Schema Event Timing”.
The history tables contain the same kind of rows as the
current-events table but have more rows and show what the server
has been doing “recently” rather than
“currently.” The
events_waits_history
and
events_waits_history_long
tables
contain the most recent 10 events per thread and most recent
10,000 events, respectively. For example, to see information for
recent events produced by thread 13, do this:
- FROM performance_schema.events_waits_history
- ORDER BY EVENT_ID;
- +----------+-----------------------------------------+------------+
- | EVENT_ID | EVENT_NAME | TIMER_WAIT |
- +----------+-----------------------------------------+------------+
- | 86 | wait/synch/mutex/mysys/THR_LOCK::mutex | 686322 |
- | 87 | wait/synch/mutex/mysys/THR_LOCK_malloc | 320535 |
- | 88 | wait/synch/mutex/mysys/THR_LOCK_malloc | 339390 |
- | 89 | wait/synch/mutex/mysys/THR_LOCK_malloc | 377100 |
- | 90 | wait/synch/mutex/sql/LOCK_plugin | 614673 |
- | 91 | wait/synch/mutex/sql/LOCK_open | 659925 |
- | 92 | wait/synch/mutex/sql/THD::LOCK_thd_data | 494001 |
- | 93 | wait/synch/mutex/mysys/THR_LOCK_malloc | 222489 |
- | 94 | wait/synch/mutex/mysys/THR_LOCK_malloc | 214947 |
- | 95 | wait/synch/mutex/mysys/LOCK_alarm | 312993 |
- +----------+-----------------------------------------+------------+
As new events are added to a history table, older events are discarded if the table is full.
Summary tables provide aggregated information for all events over
time. The tables in this group summarize event data in different
ways. To see which instruments have been executed the most times
or have taken the most wait time, sort the
events_waits_summary_global_by_event_name
table on the COUNT_STAR
or
SUM_TIMER_WAIT
column, which correspond to a
COUNT(*)
or SUM(TIMER_WAIT)
value, respectively, calculated over all events:
- FROM performance_schema.events_waits_summary_global_by_event_name
- +---------------------------------------------------+------------+
- | EVENT_NAME | COUNT_STAR |
- +---------------------------------------------------+------------+
- | wait/synch/mutex/mysys/THR_LOCK_malloc | 6419 |
- | wait/io/file/sql/FRM | 452 |
- | wait/synch/mutex/sql/LOCK_plugin | 337 |
- | wait/synch/mutex/mysys/THR_LOCK_open | 187 |
- | wait/synch/mutex/mysys/LOCK_alarm | 147 |
- | wait/synch/mutex/sql/THD::LOCK_thd_data | 115 |
- | wait/io/file/myisam/kfile | 102 |
- | wait/synch/mutex/sql/LOCK_global_system_variables | 89 |
- | wait/synch/mutex/mysys/THR_LOCK::mutex | 89 |
- | wait/synch/mutex/sql/LOCK_open | 88 |
- +---------------------------------------------------+------------+
- FROM performance_schema.events_waits_summary_global_by_event_name
- +----------------------------------------+----------------+
- | EVENT_NAME | SUM_TIMER_WAIT |
- +----------------------------------------+----------------+
- | wait/io/file/sql/MYSQL_LOG | 1599816582 |
- | wait/synch/mutex/mysys/THR_LOCK_malloc | 1530083250 |
- | wait/io/file/sql/binlog_index | 1385291934 |
- | wait/io/file/sql/FRM | 1292823243 |
- | wait/io/file/myisam/kfile | 411193611 |
- | wait/io/file/myisam/dfile | 322401645 |
- | wait/synch/mutex/mysys/LOCK_alarm | 145126935 |
- | wait/io/file/sql/casetest | 104324715 |
- | wait/synch/mutex/sql/LOCK_plugin | 86027823 |
- | wait/io/file/sql/pid | 72591750 |
- +----------------------------------------+----------------+
These results show that the THR_LOCK_malloc
mutex is “hot,” both in terms of how often it is used
and amount of time that threads wait attempting to acquire it.
The THR_LOCK_malloc
mutex is used only in
debug builds. In production builds it is not hot because it is
nonexistent.
Instance tables document what types of objects are instrumented.
An instrumented object, when used by the server, produces an
event. These tables provide event names and explanatory notes or
status information. For example, the
file_instances
table lists instances
of instruments for file I/O operations and their associated files:
- FROM performance_schema.file_instances\G
- *************************** 1. row ***************************
- EVENT_NAME: wait/io/file/sql/binlog
- OPEN_COUNT: 0
- *************************** 2. row ***************************
- EVENT_NAME: wait/io/file/myisam/kfile
- OPEN_COUNT: 1
- *************************** 3. row ***************************
- EVENT_NAME: wait/io/file/myisam/kfile
- OPEN_COUNT: 1
- ...
Setup tables are used to configure and display monitoring
characteristics. For example,
setup_instruments
lists the set of
instruments for which events can be collected and shows which of
them are enabled:
- FROM performance_schema.setup_instruments;
- +---------------------------------------------------+---------+-------+
- | NAME | ENABLED | TIMED |
- +---------------------------------------------------+---------+-------+
- ...
- ...
- | statement/sql/flush | YES | YES |
- ...
- | wait/synch/mutex/sql/LOCK_global_read_lock | YES | YES |
- | wait/synch/mutex/sql/LOCK_global_system_variables | YES | YES |
- | wait/synch/mutex/sql/LOCK_lock_db | YES | YES |
- | wait/synch/mutex/sql/LOCK_manager | YES | YES |
- ...
- | wait/synch/rwlock/sql/LOCK_grant | YES | YES |
- | wait/synch/rwlock/sql/LOGGER::LOCK_logger | YES | YES |
- | wait/synch/rwlock/sql/LOCK_sys_init_connect | YES | YES |
- | wait/synch/rwlock/sql/LOCK_sys_init_slave | YES | YES |
- ...
- | wait/io/file/sql/binlog | YES | YES |
- | wait/io/file/sql/binlog_index | YES | YES |
- | wait/io/file/sql/casetest | YES | YES |
- | wait/io/file/sql/dbopt | YES | YES |
- ...
To understand how to interpret instrument names, see Section 26.6, “Performance Schema Instrument Naming Conventions”.
To control whether events are collected for an instrument, set its
ENABLED
value to YES
or
NO
. For example:
- mysql> UPDATE performance_schema.setup_instruments
The Performance Schema uses collected events to update tables in
the performance_schema
database, which act as
“consumers” of event information. The
setup_consumers
table lists the
available consumers and which are enabled:
- +----------------------------------+---------+
- | NAME | ENABLED |
- +----------------------------------+---------+
- | events_statements_current | YES |
- | events_statements_history | YES |
- | events_transactions_current | YES |
- | events_transactions_history | YES |
- | global_instrumentation | YES |
- | thread_instrumentation | YES |
- | statements_digest | YES |
- +----------------------------------+---------+
To control whether the Performance Schema maintains a consumer as
a destination for event information, set its
ENABLED
value.
For more information about the setup tables and how to use them to control event collection, see Section 26.4.2, “Performance Schema Event Filtering”.
There are some miscellaneous tables that do not fall into any of
the previous groups. For example,
performance_timers
lists the
available event timers and their characteristics. For information
about timers, see Section 26.4.1, “Performance Schema Event Timing”.
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-quick-start.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.