Thursday, May 7, 2009

MySQL Performance Tunning Part - 4

So far we looked at query caching and slow query log and its analysis, I am sure once we do log analysis on regular basis we wont have performance problem.

In addtion to this MySQL has couple other tunning paramters depending on RAM available in your system.

If you have smaller table cache value, then it might impact on your performance, in that case increase the table_cache to higher. If you look at script output, you will see somthing like this, means server has proper setting, if it say, we need to increase then increase it and then check after few days.

Current table_cache value = 2048 tables
You have a total of 238 tables
You have 721 open tables.
The table_cache value seems to be fine

As per my explanation of slow queries, if mysql has to create temp table too many time on disk, then it will impact on performance, you can increase the tmp_table_size and max_heap_table_size to similar size.

Current max_heap_table_size = 255 M
Current tmp_table_size = 256 M

If you are using joins then you should increase join_buffer_size upto 4M and also set "log-queries-not-using-indexes", so it will log the queries which does not have proper index for join query.

Current join_buffer_size = 4.00 M
You have had 270 queries where a join could not use an index properly

You should also increase the key_buffer_size for your MyISM table indexes. as per recomondation of script, it can be upto 1/4 of total system RAM.

Current MyISAM index space = 589 M
Current key_buffer_size = 256 M
Key cache miss rate is 1 : 512
Key buffer fill ratio = 99.00 %

It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.

If you have small no of open files, that can also impact on performance, if script output request to increase the no of open files(open_files_limit). you should increase it.

Current open_files_limit = 4306 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

There are two paramters for sort operation sort_buffer_size and read_rnd_buffer_size, you can optimize as per script output.

Current sort_buffer_size = 8 M
Current read_rnd_buffer_size = 3 M
Sort buffer seems to be fine

Script also reports about using InnoDB table if MySQL use too many time table lock which impact on write operations. Select use of InnoDB might help to improve performance.

I think I have covered most paramters which helps to improve performance here.

Again the MySQL performance tunning should be considered as ongoing task and one should look into the slow query log on regular basis and the script out inpection will help to keep server upto speed most time.

Now we have taken most steps and still we think MySQL performance needs improvement then there are couple other options, one has to explore is:

1. MySQL replication.
2. MySQL Clustering.

Again thanks for your time.

-Babu Satasiya

Wednesday, May 6, 2009

Music on Searchme.

SearchmeView in searchme: full | lite

MySQL Performance Tunning Part - 3

During last two post, I have covered the query cache and scripts, which will start improving perofrmance of repeat queries. So you will notice performance improvement.

If you look at the script output, it will give us details about the slow queries, if you have not enabled logging of slow queries, please enable it, so it will allow us to see what is causing problem (I should have describe this in my first blog).

You might see somthing like this, slow query logging is enabled and it logs the query if it takes more then 2 seconds.

The slow query log is enabled.
Current long_query_time = 2 sec.
You have 35148 out of 63753279 that take longer than 2 sec. to complete
Your long_query_time seems to be fine

This is the parameters you would have to add:

log-slow-queries = /var/lib/mysql/slow-queries.log
long_query_time = 2

Once you have slow query log, you can start looking into the queries and findout how many queries are slow and its frequency. I would improve queries first which is being run almost every visit or page view of the site, even it is taking 3-4 seconds while there is a query which take 4 minutes but run only two-three times a day, most likely these long queries are for backend processing.

You can find out tools which helps to process these slow queries and give combined count for each queries which is slower.

There are several cause of slow queries and most likely it is due to not having proper indexes created for given query.

To findout index is not properly, use mysql - EXPLAIN and it will give more details how many raw, it has to scan for given queries, it will also give details about posible fields to use for index to improve performance of the query.

If we do not have propery indexes in mysql table, mysql has to use internal sort which it write to disk and then gets the results which cause the performance issue.

Please read this artical more about indexing.

I will continue on this in my next post. I would like to see feedback and comments on my posts.

-Babu Satasiya

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

Friday, May 1, 2009

MySQL performance tunning - Part 1

There are couple scripts available which helps to improve the performance of mysql, I found two usefull scripts, that helped me to improve performance of server a lot, earlier mysql was running most CPU intesive, after tunning the parameters it went down a lot.

1. MySQL Performance Tuning Primer Script (
2. High Performance MySQL Tuning Script (

I will write more details in next post.

-Babu Satasiya