How to gradually introduce and test memcached on an existing site?

Question

I don't mean actually installing it. I currently use LAMP for development and would like to start using memcached for a high traffic site. Which types of db calls should be cached? (all?) and what is a good way to do load testing on a development server? Are there benchmarks that I should be looking at on my production site first? The site is primarily selects with very few updates/inserts. Are there any general guidelines that should be followed as far as ram usage vs traffic or is it mostly simply by trial and error?

Thanks.

Answer 1

i would start with a query that supports a secondary feature feature first. observe how that responds to being cached first, tinker with it a bit. and then as you get the feel for how your site reacts to it, then start trying to cache other queries. i have never used memcached but i am playing with query caching in codeigniter.

i am starting with caching queries that i use to populate certain dropdown boxes and form elements on my site. they very rarely change, and they will not change with out administrative action such as loading in a new file to the database that adds records to my tables. if you have any queries like that i would suggest trying those first.

Answer 2

Always measure first and then optimize. Write down your major bottlenecks and do some good performance tests on them.

Currently there aren't many benchmarks programs out there that can emulate your own load, and I don't think I could recommend one to you since every site is unique.

But there are a few tools out there that try to emulate high traffic.

• List of Web Traffic Test Tools
• Stress tools to test your Web server

Answer 3

Firstly, it depends on how much ram you have to play with, how often you need the data, and how often the data gets updated.

Which types of db calls should be cached? (all?)

If you are updating the data at about the same rate you are selecting it, then it probably isn't worth caching.

As Eric said, drop downs, and any other static content you use for your site would make good use of memcache.

Remember to delete/refresh the cached item once you update it.

and what is a good way to do load testing on a development server?

Not sure what kind of load testing you have in mind for it.

Are there benchmarks that I should be looking at on my production site first? The site primarily selects with very few updates/inserts.

Check out the type of queries you are making, possibly log them and the amount of times the are called. The most frequent can probably be cached.

Also make sure there isn't any other sql optimizations you could make first.

Are there any general guidelines that should be followed as far as ram usage vs traffic or is it mostly simply by trial and error?

I don't think there is much of a ram vs traffic relation. I would figure out what kind of ram your box needs to function under peak traffic times and set your ram limits for memcache accordingly.

Answer 4

If you have many SELECTs and few writes to the database then MySQL's query cache should do a pretty good job at caching the data for you.

To see how effectively the query cache is working try the following:

> SHOW GLOBAL STATUS LIKE 'Qcache_hits';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Qcache_hits   | 735740 |
+---------------+--------+
1 row in set (0.00 sec)


> SHOW GLOBAL STATUS LIKE 'Com_select';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_select    | 5644038 |
+---------------+---------+
1 row in set (0.00 sec)

To find the hit ratio calculate Qcache_hits / (Qcache_hits + Com_select)

In this case it's 735740 / (735740 + 5644038) = 0.1153

That's a 11% hit rate and is pretty poor.

If you see a much higher % on your machine then there might be less of a case for using memcached.

MySQL's query cache works well in many cases (I suspect this includes yours). The cache entries are invalidated every time a table is updated (even if your cached query relates to a row that's not been changed) and this is one big shortcoming of it. There are cases where you can use memcached to cache items at closer to a row level when you and your application can be smarter about cache expiry than MySQL query cache.

Let me know the result of the query cache hit rate. You might even find that you've got the query cache disabled at the moment(!)

You can verify whether it's enabled by running the following query:

> SHOW GLOBAL VARIABLES LIKE '%cache';

Check for have_query_cache = YES and query_cache_size > 0 in there.