When using memcached to cache MySQL data, your application must retrieve data from the database and load the appropriate key-value pairs into the cache. Then, subsequent lookups can be done directly from the cache.
Because MySQL has its own in-memory caching mechanisms for
queried data, such as the
buffer pool and the
MySQL query cache, look for opportunities beyond loading
individual column values or rows into the cache. Prefer to cache
composite values, such as those retrieved from multiple tables
through a join query, or result sets assembled from multiple
Limit the information in the cache to non-sensitive data, because there is no security required to access or update the information within a memcached instance. Anybody with access to the machine has the ability to read, view and potentially update the information. To keep the data secure, encrypt the information before caching it. To restrict the users capable of connecting to the server, either disable network access, or use IPTables or similar techniques to restrict access to the memcached ports to a select set of hosts.
You can introduce
memcached to an existing
application, even if caching was not part of the original
design. In many languages and environments the changes to the
application will be just a few lines, first to attempt to read
from the cache when loading data, fall back to the old method if
the information is not cached, and to update the cache with
information once the data has been read.
The general sequence for using memcached in any language as a caching solution for MySQL is as follows:
Request the item from the cache.
If the item exists, use the item data.
If the item does not exist, load the data from MySQL, and store the value into the cache. This means the value is available to the next client that requests it from the cache.
For a flow diagram of this sequence, see Figure 3.5, “Typical memcached Application Flowchart”.
The most direct way to cache MySQL data is to use a 2-column table, where the first column is a primary key. Because of the uniqueness requirements for memcached keys, make sure your database schema makes appropriate use of primary keys and unique constraints.
If you combine multiple column values into a single memcached item value, choose data types to make it easy to parse the value back into its components, for example by using a separator character between numeric values.
The queries that map most easily to memcached
lookups are those with a single
WHERE clauses, or those using
operators such as
LIKE, memcached does not
provide a simple or efficient way to scan through or filter the
keys or associated values, so typically you perform those
operations as SQL queries on the underlying database.