Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Nov 23 '12 at 3:47

This question came from our site for professional and enthusiast programmers.

    
This is only a guess, but I'd say people are not responding to this question because they cannot understand what you are asking. At least, that is what is happening to me... –  Max Vernon Nov 23 '12 at 2:07
2  
Please check the other copy of this same question and turn the language of this one into something human-readable. I bet you got downvotes on that one because of all the contractions. This is not Twitter, you can post questions literally of thousands of characters. –  dezso Nov 23 '12 at 9:10
add comment

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
I am using latest extension such as mysqli and utilizing all features .. problem is day by day db size increase as well as traffic also, so that I asked this. –  user1844933 Nov 22 '12 at 17:11
    
Consider splitting your load between several databases / db servers. Surely that would require to change your code but it'll pay off when you face even more traffic, you'll be able to scale much easier. –  Yuriy Nov 22 '12 at 22:29
    
Also, what is your problem so far? Are some queries much slower than others blocking the necessary tables, or are they all reasonably fast, but there are just too many of them? Quite different scenarios with different solutions. –  Yuriy Nov 22 '12 at 22:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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