Rechercher dans le manuel MySQL
15.8.4 Configuring Thread Concurrency for InnoDB
InnoDB
uses operating system
threads to process requests
from user transactions. (Transactions may issue many requests to
InnoDB
before they commit or roll back.) On
modern operating systems and servers with multi-core processors,
where context switching is efficient, most workloads run well
without any limit on the number of concurrent threads.
In situations where it is helpful to minimize context switching
between threads, InnoDB
can use a number of
techniques to limit the number of concurrently executing operating
system threads (and thus the number of requests that are processed
at any one time). When InnoDB
receives a new
request from a user session, if the number of threads concurrently
executing is at a pre-defined limit, the new request sleeps for a
short time before it tries again. A request that cannot be
rescheduled after the sleep is put in a first-in/first-out queue
and eventually is processed. Threads waiting for locks are not
counted in the number of concurrently executing threads.
You can limit the number of concurrent threads by setting the
configuration parameter
innodb_thread_concurrency
. Once
the number of executing threads reaches this limit, additional
threads sleep for a number of microseconds, set by the
configuration parameter
innodb_thread_sleep_delay
, before
being placed into the queue.
You can set the configuration option
innodb_adaptive_max_sleep_delay
to the highest value you would allow for
innodb_thread_sleep_delay
, and
InnoDB
automatically adjusts
innodb_thread_sleep_delay
up or
down depending on the current thread-scheduling activity. This
dynamic adjustment helps the thread scheduling mechanism to work
smoothly during times when the system is lightly loaded and when
it is operating near full capacity.
The default value for
innodb_thread_concurrency
and the
implied default limit on the number of concurrent threads has been
changed in various releases of MySQL and
InnoDB
. The default value of
innodb_thread_concurrency
is
0
, so that by default there is no limit on the
number of concurrently executing threads.
InnoDB
causes threads to sleep only when the
number of concurrent threads is limited. When there is no limit on
the number of threads, all contend equally to be scheduled. That
is, if innodb_thread_concurrency
is 0
, the value of
innodb_thread_sleep_delay
is
ignored.
When there is a limit on the number of threads (when
innodb_thread_concurrency
is >
0), InnoDB
reduces context switching overhead
by permitting multiple requests made during the execution of a
single SQL statement to enter
InnoDB
without observing the limit set by
innodb_thread_concurrency
. Since
an SQL statement (such as a join) may comprise multiple row
operations within InnoDB
,
InnoDB
assigns a specified number of
“tickets” that allow a thread to be scheduled
repeatedly with minimal overhead.
When a new SQL statement starts, a thread has no tickets, and it
must observe
innodb_thread_concurrency
. Once
the thread is entitled to enter InnoDB
, it is
assigned a number of tickets that it can use for subsequently
entering InnoDB
to perform row operations. If
the tickets run out, the thread is evicted, and
innodb_thread_concurrency
is
observed again which may place the thread back into the
first-in/first-out queue of waiting threads. When the thread is
once again entitled to enter InnoDB
, tickets
are assigned again. The number of tickets assigned is specified by
the global option
innodb_concurrency_tickets
, which
is 5000 by default. A thread that is waiting for a lock is given
one ticket once the lock becomes available.
The correct values of these variables depend on your environment
and workload. Try a range of different values to determine what
value works for your applications. Before limiting the number of
concurrently executing threads, review configuration options that
may improve the performance of InnoDB
on
multi-core and multi-processor computers, such as
innodb_adaptive_hash_index
.
For general performance information about MySQL thread handling, see Section 8.12.4.1, “How MySQL Handles Client Connections”.
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-innodb-performance-thread_concurrency.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.