TCP/IP Protocol Sequence Diagrams
Posted using ShareThis
Sunday, February 21, 2010
Monday, July 27, 2009
Tuesday, May 12, 2009
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.
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
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
Wednesday, May 6, 2009
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.
SLOW QUERIES
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.
http://www.experts-exchange.com/Programming/Languages/Scripting/CGI/Q_22064844.html
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.
http://www.databasejournal.com/features/mysql/print.php/1382791
I will continue on this in my next post. I would like to see feedback and comments on my posts.
-Babu Satasiya
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.
SLOW QUERIES
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.
http://www.experts-exchange.com/Programming/Languages/Scripting/CGI/Q_22064844.html
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.
http://www.databasejournal.com/features/mysql/print.php/1382791
I will continue on this in my next post. I would like to see feedback and comments on my posts.
-Babu Satasiya
Subscribe to:
Posts (Atom)
