1

I would like to optimize my MySQL DB. I noticed some important points as follows:

  1. Enabling Query cache.
  2. Using memory storage. (I know pluses and minuses of this. Being I am having static tables, I need not worry about this facility's drawback. Before executing a query, I'll check if memory tables exist or not.)
  3. Increasing key_buffer_size.

My question is, can I use both Query cache and memory storage? As well as what is the role of key_buffer_size?

Why am I asking? Because everything depends on system memory. How these 3 options will work together? Can I do these things together?

0

1 Answer 1

0

Query cache can be combined with memory storage but it'll only make sense if don't have many updates (as your cached results will be no longer actual after your change your data).

Basically query cache stores results of your SELECT queries (if run again, data is retrieved faster), key buffer size stores indices (helps to use them more efficiently, i.e. to retrieve data quicker) and you said you know what memory storage is.

All three options you mentioned can be used together, but the real efficiency depends on your usage pattern. But hey won't do any harm when combined (by that I mean assigning bigger values to cache and buffer while using memory storage) as long as you have enough memory.

P.S. As you probably reckon yourself, your setup screams you're using a wrong tool. I can imagine situation where it is still necessary, e.g. using legacy / unchangeable code that depends on MySQL but otherwise consider redesigning and using other data storages besides MySQL, permanent or temporary like memcache.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.