Tuesday, May 5, 2009

MySQL Performance tunning - Part 2

Once you run these scripts you will find out that you have used performance tunning paramters with MySQL or not? it will gives suggestions based on "Show Variables" information stored in MySQL for all queries and other information.

For example, do you use query cache or not, if not then it will give information about the paramters to use for enabling them and also other paramters related to improving the query cache.

One of the server which I used to tune query cache looks like this after several iterations of changes. This server is running for 49 days since I restarted myaql. You will notice that I have query cache size is 512M, Good thing about MySQL is it does not keep reservation of all memory specified in query cache size, But that does not meen we can configure the all the memory for query cache, I have seen query cache being used upto 300-400M on this server.

Query cache is enabled
Current query_cache_size = 512 M
Current query_cache_used = 126 M
Current query_cache_limit = 8 M
Current Query cache Memory fill ratio = 24.66 %
Current query_cache_min_res_unit = 1 K

If you have small queries, then it is suggested to use smaller size for the query_cache_min_res_unit, it is default 4K.

To maximize the cache, it also have limit on how big query you would like cahce with query_cache_limit.

I will continue on the mysql performance tunning in my next blog.

Thanks for your time.

-Babu Satasiya

No comments:

Post a Comment