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”.
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-quick-start.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
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.