[kjkoster]

Dear All,

I was playing with Munin and the MySQL query cache last night. My query cache was configured to me 32MiB, which is quite a chunk of memory. That made me wonder if there was no way to bring that down a little.

Munin has a great graph to help with this. It shows the number of queries of each type executed per second. Cache hits (in purple) are shown separate from select queries (in green). Simply put, the sum of the cache hits and the selects are the total sum of selects that the database handles.

I started lowering the amount of memory assigned to the query cache. All the way down from 32MiB to 2MiB nothing seemed to happen. I waited for about an hour each time, to be able to see if the ratio between selects and cache hits would change. This is pictured in the annotated screen shot below.

Note how only when I lowered the query cache size to 1MiB the ratio between selects and cache hits changed. Suddenly the number of cache hist per second dropped and the number of actual selects processed by MySQL rose. It was late, so I left it there. Next morning I logged back in and made the cache 2MiB in size.

This little exercise allowed me to reduce my query cache size by 30MiB without detrimental effect.

Kees Jan

[noncasus]

How do you get your cache to cache most of the querys?

On my server it only seems to cache about 50%

[kjkoster]

Dear noncasus,

The simplest way (if you don't do this already) by normalizing the schema and reducing the number of tables that are written. The query cache is invalidated for a table as soon as there is a write to that table (UPDATE, DELETE, INSERT or REPLACE, maybe others). Thus, if you don't change the data in a table, MySQL can cache it.

You can split many tables into mostly-red fields (e.g. name of a person, date of birth, address) and read/write fields (e.g. forum post count, last logged in timestamp, money spent). By putting such data into two tables (user and user_activity for example) you allow MySQL to cache the base user data aggressively (it is hardly ever written and the cache is hardly ever invalidated) and accept that the activity data is invalidated a lot.

I am sure MySQL experts will give you better and more detailed advise, but this is what I do.

In my experience, the best MySQL tuning advise comes from the good people over on IRC. Try #mysql on irc.freenode.net.

Kees Jan

[noncasus]

Well that is definitely going to be a challenge. We use a CMS for our site and it is constantly being written to by the editor and admin by adding news daily. It is also bombarded with writing to the database by non-registered users when they comment on news articles.

Here is a picture where you can see the query_cache:

[kjkoster]

Dear noncasus,

Looking at your query stats I think you should get about 50% efficiency already.

By the nature of the system you describe I don't think you should expect a very high cache efficiency from the MySQL query cache. One of my other systems has a mostly-write database where I get an abysmal 14% cache efficiency.

I did fiddle with query_cache_limit and query_cache_min_res_unit. That seemed to help a little.

Other than that: this cache is a means to an end. What problem are you trying to solve?

Kees Jan

[noncasus]

We had slow downs lately and I figured it was due to high query usage.

All of that purple you see used to be green with very little orange/yellow. So I figured it was slowing down due to too many accesses to the database using the SELECT statement and saw that you can cache the queries.

We have no longer experienced slow downs. At least not as many as before, now they pop up and I'm thinking it's due to apache forking over...

So I'm researching how to enable MaxClients and allow more than 256. Aiming for about 500.

[guus]

On a side note: 32MiB is "quite a chunk of memory" according to you. Am I missing an important detail here (is this 32MiB per thread, process or something like that)?

Don't get me wrong, I love tinkering, but even Amazon EC2 'micro' images ship with 600MB (MiB?) of memory. The default 'small' image even gives you 1.7GB.

To me, it sounds like you're very tightly bolting down your caches - if you ever have get slightly different usage on those caches, you're likely to hit boundaries immediately. If those additional 30MiBs don't hurt you, I'd keep them just to prevent any future problems.

[kjkoster]

Dear Guus,

Well, as I understand the tuning guides for that cache, making the cache too large actually hurts performance. It has to be invalidated and kept clean. My reason to keep it tight is not the memory itself, but rather the advise to not allocate too much and incur cache maintenance overhead.

I have not measured the overhead, though. So I can only go by what I read on the Internet.

Kees Jan

[guus]

As I read the documentation, it is mainly the type of traffic, not the sheer size of the cache, that introduces the maintenance overhead (more inserts, less selects == more cache maintenance). Sizing of the cache affects only the amount of data on which that maintenance takes place. Then again, I never measured anything ever either.