MySQL Performance Blog

Have you ever wondered how optimized your Memcached installation is? There is a common misconception that one doesn't have to think too deeply about Memcached performance, but that is not true. If your setup is inefficient, you could:

• Burn Memory
• Waste Network Round-Trips
• Store Keys That Never Get Retrieved
• Have a Low Cache Hit Ratio (i.e. query MySQL too much)
• Suffer a fate too horrible to contemplate.

Percona does a lot of consulting around Memcached, so we try to take a quantitative, scientific approach to measuring memcached performance, just like everything else we do.

memcached is basically a key-value in-memory database, so it works well to analyze its traffic with Maatkit's mk-query-digest tool. You can now use mk-query-digest to get "query event" information and report on memcached effectiveness. As far as we know, this is the first and only tool of its kind.

The output is very similar to that which would be displayed for MySQL queries. Commands are fingerprinted so that similar things can be aggregated together. The output begins with an overall report:

We can see that 77% of commands (cmds) were get, 17% set, 4% delete and 4 incr (increment) cmds. At present, Memc_error only refers to gets (or, retrieval cmds in general) that were interrupted (some things are TODO). Memc_miss is for any cmd that resulted in a NOT_FOUND result. So in this case, 15% of all cmds tried to do something with a nonexistent key.

The next bit aggregates similar queries into classes and prints out the most expensive ones. Here's the top one:

Interestingly, apparently 17% of all "get TEST_KEY" were interrupted (Memc_error), probably by the TCP connection being closed client-side.

You'll notice 100% Memc_get. This is a result of the default --report and --group-by, which is "fingerprint" just like for slow query logs. So the fingerprint for this event is "get TEST_KEY" and since the cmd is "get", 100% Memc_get. There's another way to aggregate -- by the fingerprinted key, omitting the verb (get/set/etc). To do this, you'd specify key_print as the group-by value. More details are below.

This feature is still in beta. Some things are TODO; the report format could be cleaned up a little, etc. But we're releasing early and often, and we're calling on the community to help us out with testing! Here's how:

1. Get the latest mk-query-digest
2. Capture memcached network traffic with tcpdump
3. Parse memcached network traffic with mk-query-digest
4. Ponder the results, or consider a different --report option

In code:

Depending on what you're interested in, you may want to try these other reports:

• --report key_print: Group events by a fingerprinted version of keys. So "get user_123" and "set user_456" are grouped together because the key_print for both is "user_?". Doing this, per-class results will have various percentages for Memc_get, Memc_set, etc. instead of 100% Memc_cmd like above.
• --report key: Like key_print but the keys are not fingerprinted so the two examples above are different classes because "user_123" != "user_456". This would allow you to see per-key stats. Maybe, for example, how often a certain key is get'ed in comparison to other keys. (I.e. "what's the most popular get'ed or set'ed key" etc.)
• --report cmd: Group events by cmd. So "get user_123" and "get whatever" are grouped together because both are "get" cmds. You may want to do this to see, for example, what percentage of get cmds miss, i.e. result in NOT_FOUND.

These instructions could change in the future -- this blog post is not authoritative documentation. The most current documentation is always embedded in the tool itself, and is readable with perldoc! Let us know what you think. Please use the Maatkit mailing list and and post bug reports on Google Code.