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.

TABLE CACHE
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.

TEMP TABLES
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.

JOINS
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.

KEY BUFFER
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.

OPEN FILES LIMIT
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.

SORT OPERATIONS
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