Consider these aspects of MySQL and
tables when adapting an existing memcached
application to use the MySQL integration:
If you have key values longer than a few bytes, you might find it more efficient to use a numeric auto-increment column for the primary key in the
InnoDBtable, and create a unique secondary index on the column holding the memcached key values. This is because
InnoDBperforms best for large-scale insertions if the primary key values are added in sorted order (as they are with auto-increment values), and the primary key values are duplicated in each secondary index, which can take up unnecessary space when the primary key is a long string value.
If you store several different classes of information in memcached, you might set up a separate
InnoDBtable for each kind of data. Define additional table identifiers in the
innodb_memcache.containerstable, and use the notation
@@to store or retrieve items from different tables. Physically dividing the items lets you tune the characteristics of each table for best space utilization, performance, and reliability. For example, you might enable compression for a table that holds blog posts, but not for one that holds thumbnail images. You might back up one table more frequently than another because it holds critical data. You might create additional secondary indexes on tables that are frequently used to generate reports through SQL.
Preferably, set up a stable set of table definitions for use with the memcached interface and leave them in place permanently. Changes to the
containerstable take effect the next time that table is queried. The entries in that table are processed at startup, and are consulted whenever an unrecognized table ID is requested by the
@@notation. Thus, new entries are visible as soon as you try to use the associated table ID, but changes to existing entries require a server restart before they take effect.
When you use the default caching policy
innodb_only, your calls to
incr(), and so on can succeed but still trigger debugging messages such as
while expecting 'STORED', got unexpected response 'NOT_STORED. This is because in the
innodb_onlyconfiguration, new and updated values are sent directly to the
InnoDBtable without being saved in the memory cache.