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;
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-memcached-txn.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.