InnoDB in combination with
memcached involves writing all data to disk,
whether immediately or sometime later, understand that raw
performance is expected to be somewhat lower than using
memcached by itself. Focus your tuning goals
plugin on achieving higher performance than equivalent SQL
Benchmarks suggest that both queries and DML operations (inserts, updates, and deletes) are faster going through the memcached interface than with traditional SQL. DML operations typically see a larger speedup. Thus, the types of applications you might adapt to use the memcached interface first are those that are write-intensive. You might also use MySQL as a data store for types of write-intensive applications that formerly used some fast, lightweight mechanism where reliability was not a priority.
The types of queries that are most suited to the simple
GET request style are those with a single
clause, or a set of
AND conditions, in the
SQL: select col from tbl where key = 'key_value'; memcached: GET key_value SQL: select col from tbl where col1 = val1 and col2 = val2 and col3 = val3; memcached: # Since you must always know these 3 values to look up the key, # combine them into a unique string and use that as the key # for all ADD, SET, and GET operations. key_value = val1 + ":" + val2 + ":" + val3 GET key_value SQL: select 'key exists!' from tbl where exists (select col1 from tbl where key = 'key_value') limit 1; memcached: # Test for existence of key by asking for its value and checking if the call succeeds, # ignoring the value itself. For existence checking, you typically only store a very # short value such as "1". GET key_value
For best performance, deploy the
memcached plugin on machines that are
configured like typical database servers: in particular, with
the majority of system RAM devoted to the
buffer pool through the
configuration option. For systems with multi-gigabyte buffer
pools, consider raising the value of the
configuration option for maximum throughput when most operations
involve data already cached in memory.
InnoDB has a number of settings that let you
choose the balance between high reliability in case of a crash,
and the amount of I/O overhead during high write workloads. For
example, consider setting the configuration options
Measure the performance with different settings for the
innodb_flush_method option. If
the binary log is not
turned on for the server, use the setting
deprecated and will be removed in a future release. As of
InnoDB support for two-phase
commit in XA transactions is always enabled and disabling
innodb_support_xa is no
For other ways to reduce or tune I/O for table operations, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.
The default value of 1 for the configuration options
is intended for maximum reliability of results and safety of
stored or updated data.
Depending on the type of application, you might increase one or
both of these settings to reduce the overhead of frequent
commit operations. On a busy
system, you might increase
knowing that changes to the data made through SQL might not
become visible to memcached immediately (that
get operations were processed). When
processing data where every write operation must be reliably
stored, you would leave
set to 1. You might increase it when processing large numbers of
updates intended to only be used for statistical analysis, where
it is not critical if the last
updates are lost in case of a crash.
For example, imagine a system that monitors traffic crossing a
busy bridge, recording approximately 100,000 vehicles each day.
If the application simply counts different types of vehicles to
analyze traffic patterns, it might change
the I/O overhead for commit operations by 99%. In case of an
unexpected outage, only a maximum of 100 records could be lost,
which might be an acceptable margin of error. If instead the
application was doing automated toll collection for each car, it
1 to ensure that every toll record was
immediately saved to disk.
Because of the way
InnoDB organizes the
memcached key values on disk, if you have a
large number of keys to create, it can be faster to sort all the
data items by the key value in your application and
add them in sorted order, rather than
creating them in arbitrary order.
The memslap command, which is part of the regular memcached distribution but not included with the MySQL server, can be useful for benchmarking different configurations. It can also be used to generate sample key/value pairs that you can use in your own benchmarking. See Section 220.127.116.11.6, “libmemcached Command-Line Utilities” for details.