Rechercher dans le manuel MySQL
15.8.3.4 Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)
A read-ahead request is
an I/O request to prefetch multiple pages in the
buffer pool
asynchronously, in anticipation that these pages will be needed
soon. The requests bring in all the pages in one
extent.
InnoDB
uses two read-ahead algorithms to
improve I/O performance:
Linear read-ahead is a
technique that predicts what pages might be needed soon based on
pages in the buffer pool being accessed sequentially. You
control when InnoDB
performs a read-ahead
operation by adjusting the number of sequential page accesses
required to trigger an asynchronous read request, using the
configuration parameter
innodb_read_ahead_threshold
.
Before this parameter was added, InnoDB
would
only calculate whether to issue an asynchronous prefetch request
for the entire next extent when it read the last page of the
current extent.
The configuration parameter
innodb_read_ahead_threshold
controls how sensitive InnoDB
is in detecting
patterns of sequential page access. If the number of pages read
sequentially from an extent is greater than or equal to
innodb_read_ahead_threshold
,
InnoDB
initiates an asynchronous read-ahead
operation of the entire following extent.
innodb_read_ahead_threshold
can
be set to any value from 0-64. The default value is 56. The
higher the value, the more strict the access pattern check. For
example, if you set the value to 48, InnoDB
triggers a linear read-ahead request only when 48 pages in the
current extent have been accessed sequentially. If the value is
8, InnoDB
triggers an asynchronous read-ahead
even if as few as 8 pages in the extent are accessed
sequentially. You can set the value of this parameter in the
MySQL configuration
file, or change it dynamically with the
SET
GLOBAL
statement, which requires privileges sufficient
to set global system variables. See
Section 5.1.9.1, “System Variable Privileges”.
Random read-ahead is a
technique that predicts when pages might be needed soon based on
pages already in the buffer pool, regardless of the order in
which those pages were read. If 13 consecutive pages from the
same extent are found in the buffer pool,
InnoDB
asynchronously issues a request to
prefetch the remaining pages of the extent. To enable this
feature, set the configuration variable
innodb_random_read_ahead
to
ON
.
The SHOW ENGINE INNODB STATUS
command
displays statistics to help you evaluate the effectiveness of
the read-ahead algorithm. Statistics include counter information
for the following global status variables:
This information can be useful when fine-tuning the
innodb_random_read_ahead
setting.
For more information about I/O performance, see Section 8.5.8, “Optimizing InnoDB Disk I/O” and Section 8.12.1, “Optimizing Disk I/O”.
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-read-ahead.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.