Rechercher dans le manuel MySQL
15.19.6.4 Controlling Transactional Behavior of the InnoDB memcached Plugin
Unlike traditional memcached, the
daemon_memcached
plugin allows you to control
durability of data values produced through calls to
add
, set
,
incr
, and so on. By default, data written
through the memcached interface is stored to
disk, and calls to get
return the most recent
value from disk. Although the default behavior does not offer
the best possible raw performance, it is still fast compared to
the SQL interface for InnoDB
tables.
As you gain experience using the
daemon_memcached
plugin, you can consider
relaxing durability settings for non-critical classes of data,
at the risk of losing some updated values in the event of an
outage, or returning data that is slightly out-of-date.
Frequency of Commits
One tradeoff between durability and raw performance is how frequently new and changed data is committed. If data is critical, is should be committed immediately so that it is safe in case of a crash or outage. If data is less critical, such as counters that are reset after a crash or logging data that you can afford to lose, you might prefer higher raw throughput that is available with less frequent commits.
When a memcached operation inserts, updates,
or deletes data in the underlying InnoDB
table, the change might be committed to the
InnoDB
table instantly (if
daemon_memcached_w_batch_size=1
)
or some time later (if the
daemon_memcached_w_batch_size
value is greater than 1). In either case, the change cannot be
rolled back. If you increase the value of
daemon_memcached_w_batch_size
to avoid high I/O overhead during busy times, commits could
become infrequent when the workload decreases. As a safety
measure, a background thread automatically commits changes made
through the memcached API at regular
intervals. The interval is controlled by the
innodb_api_bk_commit_interval
configuration option, which has a default setting of
5
seconds.
When a memcached operation inserts or updates
data in the underlying InnoDB
table, the
changed data is immediately visible to other
memcached requests because the new value
remains in the memory cache, even if it is not yet committed on
the MySQL side.
Transaction Isolation
When a memcached operation such as
get
or incr
causes a query
or DML operation on the underlying InnoDB
table, you can control whether the operation sees the very
latest data written to the table, only data that has been
committed, or other variations of transaction
isolation level. Use
the innodb_api_trx_level
configuration option to control this feature. The numeric values
specified for this option correspond to isolation levels such as
REPEATABLE READ
. See the
description of the
innodb_api_trx_level
option for
information about other settings.
A strict isolation level ensures that data you retrieve is not rolled back or changed suddenly causing subsequent queries to return different values. However, strict isolation levels require greater locking overhead, which can cause waits. For a NoSQL-style application that does not use long-running transactions, you can typically use the default isolation level or switch to a less strict isolation level.
Disabling Row Locks for memcached DML Operations
The innodb_api_disable_rowlock
option can be used to disable row locks when
memcached requests through the
daemon_memcached
plugin cause DML operations.
By default, innodb_api_disable_rowlock
is set
to OFF
which means that
memcached requests row locks for
get
and set
operations.
When innodb_api_disable_rowlock
is set to
ON
, memcached requests a
table lock instead of row locks.
The innodb_api_disable_rowlock
option is not
dynamic. It must be specified at startup on the
mysqld command line or entered in a MySQL
configuration file.
Allowing or Disallowing DDL
By default, you can perform DDL
operations such as ALTER TABLE
on
tables used by the daemon_memcached
plugin.
To avoid potential slowdowns when these tables are used for
high-throughput applications, disable DDL operations on these
tables by enabling
innodb_api_enable_mdl
at
startup. This option is less appropriate when accessing the same
tables through both memcached and SQL,
because it blocks CREATE INDEX
statements on the tables, which could be important for running
reporting queries.
Storing Data on Disk, in Memory, or Both
The innodb_memcache.cache_policies
table
specifies whether to store data written through the
memcached interface to disk
(innodb_only
, the default); in memory only,
as with traditional memcached
(cache_only
); or both
(caching
).
With the caching
setting, if
memcached cannot find a key in memory, it
searches for the value in an InnoDB
table.
Values returned from get
calls under the
caching
setting could be out-of-date if the
values were updated on disk in the InnoDB
table but are not yet expired from the memory cache.
The caching policy can be set independently for
get
, set
(including
incr
and decr
),
delete
, and flush
operations.
For example, you might allow get
and
set
operations to query or update a table and
the memcached memory cache at the same time
(using the caching
setting), while making
delete
, flush
, or both
operate only on the in-memory copy (using the
cache_only
setting). That way, deleting or
flushing an item only expires the item from the cache, and the
latest value is returned from the InnoDB
table the next time the item is requested.
- +--------------+-------------+-------------+---------------+--------------+
- | policy_name | get_policy | set_policy | delete_policy | flush_policy |
- +--------------+-------------+-------------+---------------+--------------+
- | cache_policy | innodb_only | innodb_only | innodb_only | innodb_only |
- +--------------+-------------+-------------+---------------+--------------+
innodb_memcache.cache_policies
values are
only read at startup. After changing values in this table,
uninstall and reinstall the daemon_memcached
plugin to ensure that changes take effect.
- mysql> UNINSTALL PLUGIN daemon_memcached;
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-innodb-memcached-txn.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.