Rechercher dans le manuel MySQL
26.3 Performance Schema Startup Configuration
To use the MySQL Performance Schema, it must be enabled at server startup to enable event collection to occur.
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
If the server is unable to allocate any internal buffer during
Performance Schema initialization, the Performance Schema disables
itself and sets
performance_schema
to
OFF
, and the server runs without
instrumentation.
The Performance Schema also permits instrument and consumer configuration at server startup.
To control an instrument at server startup, use an option of this form:
--performance-schema-instrument='instrument_name=value'
Here, instrument_name
is an instrument
name such as wait/synch/mutex/sql/LOCK_open
,
and value
is one of these values:
OFF
,FALSE
, or0
: Disable the instrumentON
,TRUE
, or1
: Enable and time the instrumentCOUNTED
: Enable and count (rather than time) the instrument
Each
--performance-schema-instrument
option can specify only one instrument name, but multiple
instances of the option can be given to configure multiple
instruments. In addition, patterns are permitted in instrument
names to configure instruments that match the pattern. To
configure all condition synchronization instruments as enabled and
counted, use this option:
--performance-schema-instrument='wait/synch/cond/%=COUNTED'
To disable all instruments, use this option:
--performance-schema-instrument='%=OFF'
Exception: The memory/performance_schema/%
instruments are built in and cannot be disabled at startup.
Longer instrument name strings take precedence over shorter pattern names, regardless of order. For information about specifying patterns to select instruments, see Section 26.4.9, “Naming Instruments or Consumers for Filtering Operations”.
An unrecognized instrument name is ignored. It is possible that a plugin installed later may create the instrument, at which time the name is recognized and configured.
To control a consumer at server startup, use an option of this form:
--performance-schema-consumer-consumer_name=value
Here, consumer_name
is a consumer name
such as events_waits_history
, and
value
is one of these values:
OFF
,FALSE
, or0
: Do not collect events for the consumerON
,TRUE
, or1
: Collect events for the consumer
For example, to enable the events_waits_history
consumer, use this option:
--performance-schema-consumer-events-waits-history=ON
The permitted consumer names can be found by examining the
setup_consumers
table. Patterns are
not permitted. Consumer names in the
setup_consumers
table use
underscores, but for consumers set at startup, dashes and
underscores within the name are equivalent.
The Performance Schema includes several system variables that provide configuration information:
- +--------------------------------------------------------+---------+
- +--------------------------------------------------------+---------+
- | performance_schema_accounts_size | 100 |
- | performance_schema_digests_size | 200 |
- | performance_schema_events_stages_history_long_size | 10000 |
- | performance_schema_events_stages_history_size | 10 |
- | performance_schema_events_statements_history_long_size | 10000 |
- | performance_schema_events_statements_history_size | 10 |
- | performance_schema_events_waits_history_long_size | 10000 |
- | performance_schema_events_waits_history_size | 10 |
- | performance_schema_hosts_size | 100 |
- | performance_schema_max_cond_classes | 80 |
- | performance_schema_max_cond_instances | 1000 |
- ...
The performance_schema
variable
is ON
or OFF
to indicate
whether the Performance Schema is enabled or disabled. The other
variables indicate table sizes (number of rows) or memory
allocation values.
With the Performance Schema enabled, the number of Performance Schema instances affects the server memory footprint, perhaps to a large extent. The Performance Schema autoscales many parameters to use memory only as required; see Section 26.17, “The Performance Schema Memory-Allocation Model”.
To change the value of Performance Schema system variables, set
them at server startup. For example, put the following lines in a
my.cnf
file to change the sizes of the
history tables for wait events:
[mysqld]
performance_schema
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000
The Performance Schema automatically sizes the values of several of its parameters at server startup if they are not set explicitly. For example, it sizes the parameters that control the sizes of the events waits tables this way. The Performance Schema allocates memory incrementally, scaling its memory use to actual server load, instead of allocating all the memory it needs during server startup. Consequently, many sizing parameters need not be set at all. To see which parameters are autosized or autoscaled, use mysqld --verbose --help and examine the option descriptions, or see Section 26.15, “Performance Schema System Variables”.
For each autosized parameter that is not set at server startup, the Performance Schema determines how to set its value based on the value of the following system values, which are considered as “hints” about how you have configured your MySQL server:
max_connections
open_files_limit
table_definition_cache
table_open_cache
To override autosizing or autoscaling for a given parameter, set it to a value other than −1 at startup. In this case, the Performance Schema assigns it the specified value.
At runtime, SHOW VARIABLES
displays
the actual values that autosized parameters were set to.
Autoscaled parameters display with a value of −1.
If the Performance Schema is disabled, its autosized and
autoscaled parameters remain set to −1 and
SHOW VARIABLES
displays −1.
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-startup-configuration.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.