Rechercher dans le manuel MySQL
8.10.2.2 Multiple Key Caches
As of MySQL 8.0, the compound-part
structured-variable syntax discussed here for referring to
multiple MyISAM
key caches is deprecated.
Shared access to the key cache improves performance but does not eliminate contention among sessions entirely. They still compete for control structures that manage access to the key cache buffers. To reduce key cache access contention further, MySQL also provides multiple key caches. This feature enables you to assign different table indexes to different key caches.
Where there are multiple key caches, the server must know
which cache to use when processing queries for a given
MyISAM
table. By default, all
MyISAM
table indexes are cached in the
default key cache. To assign table indexes to a specific key
cache, use the CACHE INDEX
statement (see Section 13.7.7.2, “CACHE INDEX Syntax”). For example,
the following statement assigns indexes from the tables
t1
, t2
, and
t3
to the key cache named
hot_cache
:
- +---------+--------------------+----------+----------+
- +---------+--------------------+----------+----------+
- +---------+--------------------+----------+----------+
The key cache referred to in a CACHE
INDEX
statement can be created by setting its size
with a SET
GLOBAL
parameter setting statement or by using
server startup options. For example:
To destroy a key cache, set its size to zero:
You cannot destroy the default key cache. Any attempt to do this is ignored:
- +-----------------+---------+
- +-----------------+---------+
- | key_buffer_size | 8384512 |
- +-----------------+---------+
Key cache variables are structured system variables that have
a name and components. For
keycache1.key_buffer_size
,
keycache1
is the cache variable name and
key_buffer_size
is the cache
component. See Section 5.1.9.5, “Structured System Variables”,
for a description of the syntax used for referring to
structured key cache system variables.
By default, table indexes are assigned to the main (default) key cache created at the server startup. When a key cache is destroyed, all indexes assigned to it are reassigned to the default key cache.
For a busy server, you can use a strategy that involves three key caches:
A “hot” key cache that takes up 20% of the space allocated for all key caches. Use this for tables that are heavily used for searches but that are not updated.
A “cold” key cache that takes up 20% of the space allocated for all key caches. Use this cache for medium-sized, intensively modified tables, such as temporary tables.
A “warm” key cache that takes up 60% of the key cache space. Employ this as the default key cache, to be used by default for all other tables.
One reason the use of three key caches is beneficial is that access to one key cache structure does not block access to the others. Statements that access tables assigned to one cache do not compete with statements that access tables assigned to another cache. Performance gains occur for other reasons as well:
The hot cache is used only for retrieval queries, so its contents are never modified. Consequently, whenever an index block needs to be pulled in from disk, the contents of the cache block chosen for replacement need not be flushed first.
For an index assigned to the hot cache, if there are no queries requiring an index scan, there is a high probability that the index blocks corresponding to nonleaf nodes of the index B-tree remain in the cache.
An update operation most frequently executed for temporary tables is performed much faster when the updated node is in the cache and need not be read from disk first. If the size of the indexes of the temporary tables are comparable with the size of cold key cache, the probability is very high that the updated node is in the cache.
The CACHE INDEX
statement sets
up an association between a table and a key cache, but the
association is lost each time the server restarts. If you want
the association to take effect each time the server starts,
one way to accomplish this is to use an option file: Include
variable settings that configure your key caches, and an
init_file
system variable
that names a file containing CACHE
INDEX
statements to be executed. For example:
key_buffer_size = 4G
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 2G
init_file=/path/to/data-directory/mysqld_init.sql
The statements in mysqld_init.sql
are
executed each time the server starts. The file should contain
one SQL statement per line. The following example assigns
several tables each to hot_cache
and
cold_cache
:
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-multiple-key-caches.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.