Rechercher dans le manuel MySQL
26.7 Performance Schema Status Monitoring
There are several status variables associated with the Performance Schema:
- +-----------------------------------------------+-------+
- +-----------------------------------------------+-------+
- | Performance_schema_accounts_lost | 0 |
- | Performance_schema_cond_classes_lost | 0 |
- | Performance_schema_cond_instances_lost | 0 |
- | Performance_schema_digest_lost | 0 |
- | Performance_schema_file_classes_lost | 0 |
- | Performance_schema_file_handles_lost | 0 |
- | Performance_schema_file_instances_lost | 0 |
- | Performance_schema_hosts_lost | 0 |
- | Performance_schema_locker_lost | 0 |
- | Performance_schema_memory_classes_lost | 0 |
- | Performance_schema_metadata_lock_lost | 0 |
- | Performance_schema_mutex_classes_lost | 0 |
- | Performance_schema_mutex_instances_lost | 0 |
- | Performance_schema_nested_statement_lost | 0 |
- | Performance_schema_program_lost | 0 |
- | Performance_schema_rwlock_classes_lost | 0 |
- | Performance_schema_rwlock_instances_lost | 0 |
- | Performance_schema_session_connect_attrs_lost | 0 |
- | Performance_schema_socket_classes_lost | 0 |
- | Performance_schema_socket_instances_lost | 0 |
- | Performance_schema_stage_classes_lost | 0 |
- | Performance_schema_statement_classes_lost | 0 |
- | Performance_schema_table_handles_lost | 0 |
- | Performance_schema_table_instances_lost | 0 |
- | Performance_schema_thread_classes_lost | 0 |
- | Performance_schema_thread_instances_lost | 0 |
- | Performance_schema_users_lost | 0 |
- +-----------------------------------------------+-------+
The Performance Schema status variables provide information about instrumentation that could not be loaded or created due to memory constraints. Names for these variables have several forms:
Performance_schema_
indicates how many instruments of typexxx
_classes_lostxxx
could not be loaded.Performance_schema_
indicates how many instances of object typexxx
_instances_lostxxx
could not be created.Performance_schema_
indicates how many instances of object typexxx
_handles_lostxxx
could not be opened.Performance_schema_locker_lost
indicates how many events are “lost” or not recorded.
For example, if a mutex is instrumented in the server source but
the server cannot allocate memory for the instrumentation at
runtime, it increments
Performance_schema_mutex_classes_lost
.
The mutex still functions as a synchronization object (that is,
the server continues to function normally), but performance data
for it will not be collected. If the instrument can be allocated,
it can be used for initializing instrumented mutex instances. For
a singleton mutex such as a global mutex, there will be only one
instance. Other mutexes have an instance per connection, or per
page in various caches and data buffers, so the number of
instances varies over time. Increasing the maximum number of
connections or the maximum size of some buffers will increase the
maximum number of instances that might be allocated at once. If
the server cannot create a given instrumented mutex instance, it
increments
Performance_schema_mutex_instances_lost
.
Suppose that the following conditions hold:
The server was started with the
--performance_schema_max_mutex_classes=200
option and thus has room for 200 mutex instruments.150 mutex instruments have been loaded already.
The plugin named
plugin_a
contains 40 mutex instruments.The plugin named
plugin_b
contains 20 mutex instruments.
The server allocates mutex instruments for the plugins depending on how many they need and how many are available, as illustrated by the following sequence of statements:
- INSTALL PLUGIN plugin_a
The server now has 150+40 = 190 mutex instruments.
- UNINSTALL PLUGIN plugin_a;
The server still has 190 instruments. All the historical data generated by the plugin code is still available, but new events for the instruments are not collected.
- INSTALL PLUGIN plugin_a;
The server detects that the 40 instruments are already defined, so no new instruments are created, and previously assigned internal memory buffers are reused. The server still has 190 instruments.
- INSTALL PLUGIN plugin_b;
The server has room for 200-190 = 10 instruments (in this case,
mutex classes), and sees that the plugin contains 20 new
instruments. 10 instruments are loaded, and 10 are discarded or
“lost.” The
Performance_schema_mutex_classes_lost
indicates the number of instruments (mutex classes) lost:
- +---------------------------------------+-------+
- +---------------------------------------+-------+
- | Performance_schema_mutex_classes_lost | 10 |
- +---------------------------------------+-------+
The instrumentation still works and collects (partial) data for
plugin_b
.
When the server cannot create a mutex instrument, these results occur:
No row for the instrument is inserted into the
setup_instruments
table.Performance_schema_mutex_classes_lost
increases by 1.Performance_schema_mutex_instances_lost
does not change. (When the mutex instrument is not created, it cannot be used to create instrumented mutex instances later.)
The pattern just described applies to all types of instruments, not just mutexes.
A value of
Performance_schema_mutex_classes_lost
greater than 0 can happen in two cases:
To save a few bytes of memory, you start the server with
--performance_schema_max_mutex_classes=
, whereN
N
is less than the default value. The default value is chosen to be sufficient to load all the plugins provided in the MySQL distribution, but this can be reduced if some plugins are never loaded. For example, you might choose not to load some of the storage engines in the distribution.You load a third-party plugin that is instrumented for the Performance Schema but do not allow for the plugin's instrumentation memory requirements when you start the server. Because it comes from a third party, the instrument memory consumption of this engine is not accounted for in the default value chosen for
performance_schema_max_mutex_classes
.If the server has insufficient resources for the plugin's instruments and you do not explicitly allocate more using
--performance_schema_max_mutex_classes=
, loading the plugin leads to starvation of instruments.N
If the value chosen for
performance_schema_max_mutex_classes
is too small, no error is reported in the error log and there is
no failure at runtime. However, the content of the tables in the
performance_schema
database will miss events.
The
Performance_schema_mutex_classes_lost
status variable is the only visible sign to indicate that some
events were dropped internally due to failure to create
instruments.
If an instrument is not lost, it is known to the Performance
Schema, and is used when instrumenting instances. For example,
wait/synch/mutex/sql/LOCK_delete
is the name of
a mutex instrument in the
setup_instruments
table. This single
instrument is used when creating a mutex in the code (in
THD::LOCK_delete
) however many instances of the
mutex are needed as the server runs. In this case,
LOCK_delete
is a mutex that is per connection
(THD
), so if a server has 1000 connections,
there are 1000 threads, and 1000 instrumented
LOCK_delete
mutex instances
(THD::LOCK_delete
).
If the server does not have room for all these 1000 instrumented
mutexes (instances), some mutexes are created with
instrumentation, and some are created without instrumentation. If
the server can create only 800 instances, 200 instances are lost.
The server continues to run, but increments
Performance_schema_mutex_instances_lost
by 200 to indicate that instances could not be created.
A value of
Performance_schema_mutex_instances_lost
greater than 0 can happen when the code initializes more mutexes
at runtime than were allocated for
--performance_schema_max_mutex_instances=
.
N
The bottom line is that if
SHOW STATUS LIKE
'perf%'
says that nothing was lost (all values are
zero), the Performance Schema data is accurate and can be relied
upon. If something was lost, the data is incomplete, and the
Performance Schema could not record everything given the
insufficient amount of memory it was given to use. In this case,
the specific
Performance_schema_
variable indicates the problem area.
xxx
_lost
It might be appropriate in some cases to cause deliberate instrument starvation. For example, if you do not care about performance data for file I/O, you can start the server with all Performance Schema parameters related to file I/O set to 0. No memory will be allocated for file-related classes, instances, or handles, and all file events will be lost.
Use SHOW ENGINE
PERFORMANCE_SCHEMA STATUS
to inspect the internal
operation of the Performance Schema code:
- ...
- *************************** 3. row ***************************
- Type: performance_schema
- Name: events_waits_history.size
- Status: 76
- *************************** 4. row ***************************
- Type: performance_schema
- Name: events_waits_history.count
- Status: 10000
- *************************** 5. row ***************************
- Type: performance_schema
- Name: events_waits_history.memory
- Status: 760000
- ...
- *************************** 57. row ***************************
- Type: performance_schema
- Name: performance_schema.memory
- Status: 26459600
- ...
This statement is intended to help the DBA understand the effects that different Performance Schema options have on memory requirements. For a description of the field meanings, see Section 13.7.6.15, “SHOW ENGINE Syntax”.
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-status-monitoring.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.