I noticed many times, linux sysadmins are confused how to optimize my.cnf values.
Here is a script that will help you to determine the values:
===
# wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
# perl mysqltuner.pl
===
run the script and you will see the output as:
====
Variables to adjust:
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)
innodb_buffer_pool_size (>= 32M)
===
This means that query_cache_size should be given a value greater than or equal to 8MB.
Now, many of us may not be satisfied with this, they still will eager to understand the parameters. Those people can continue reading
Parameter 1
=====
table_cache
=====
Each time MySQL accesses a table, it places the table in the cache. If your application accesses many tables, it is always good to have them in cache so that data retrieve is faster.
You can check whether your system needs to have the table_cache value increased by checking the open_tables and opened_tables status variables during peak time.
open_tables is the number of tables opened in cache. Whereas opened_tables is the total number of tables open. Since MySQL supports multi-threading, several queries might be executed on the same table at the same time. So each of these queries will open a table.
The default value to table_cache is 64. Lets consider a sample scenario.
===
table_cache = 64
mysql> SHOW STATUS LIKE "open%tables%";
open_tables = 64 opened_tables = 5426787
====
Here table_cache has maxed out and opened_tables is fairly high. In this case, if you have enough memory, increase table_cache to reduce the number of opened_tables
Parameter2:
====
query_cache_size
====
Query caching has been introduced from MySQL 4 onwards. If your application executes a particular query again and again, MySQL can cache the result set, thereby avoiding the overhead of running through the data over and over and thereby increase the execution time.
You can enable query caching by setting the server variable query_cache_type=1 and setting the cache size in the variable query_cache_size. If either of the above is set to 0, query caching will not be enabled.
=====
There are three status for query caching;
Disabled - query_cache_type = 0
Enabled - query_cache_type = 1
On Demand - query_cache_type = 2
=====
Some sample scenarios:
If you have enabled query caching and do not want the result of a particular query to be cached, you can mention it in your query by specifying SQL_NO_CACHE.
eg: SELECT SQL_NO_CACHE id, name FROM employee_info WHERE employee_rank < 5;
In the above case, the result of the query will not be cached. Similarly, you can enable query caching in ON DEMAND query caching by specifying SQL_CACHE in your query.
eg: SELECT SQL_CACHE id, name FROM employee_info WHERE employee_rank < 5;
After executing the query the result will be added to the cache memory and will be used if the query is executed again.
How to check the query cache status variables
mysql> SHOW STATUS LIKE "%qcache%";
+-------------+-----+
| Variable_name | Value |
+-------------+-----+
| Qcache_free_blocks | 2253 |
| Qcache_free_memory | 9184200 |
| Qcache_hits | 247217 |
| Qcache_inserts | 50012 |
| Qcache_lowmem_prunes | 15666 |
| Qcache_not_cached | 13269 |
| Qcache_queries_in_cache | 5215 |
| Qcache_total_blocks | 13117 |
+-------------+-----+
8 rows in set (0.00 sec)
This is a result from a server with query_cache_type set to 1 ( Enabled). Now lets see what all these status variables stand for.
Qcache_free_blocks: The number of free memory blocks in the cache memory.
Qcache_free_memory: The amount of free memory for the query cache.
Qcache_hits : The number of query cache hits.
Qcache_inserts : The number of queries added to the query cache.
Qcache_lowmem_prunes : The number of queries that were deleted from the query cache because of low memory.
Qcache_not_cached : The number of non-cached queries (not cache-able, or not cached due to the query_cache_type setting).
Qcache_queries_in_cache : The number of queries registered in the query cache. Qcache_total_blocks: The total number of blocks in the query cache.
Qcache_free_blocks is an indication of fragmentation and if this is high in relation to the Qcache_total_blocks, it means that the cache space is wasted. The default block size for query cache is 4KB. If your query result is small and you see fragmentation, you should decrease the block size. You can use the system variable query_cache_min_res_unit to redefine the block size. And if the query result is large, you should increase the block size.
To defragment the query cache, you can use the command
mysql> FLUSH QUERY CACHE;
Query OK, 0 rows affected (0.07 sec)
mysql> show status like "%qcache%";
+-------------+-----+
| Variable_name | Value |
+-------------+-----+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 9090576 |
| Qcache_hits | 248169 |
| Qcache_inserts | 50147 |
| Qcache_lowmem_prunes | 15666 |
| Qcache_not_cached | 13316 |
| Qcache_queries_in_cache | 5273 |
| Qcache_total_blocks | 10979 |
+-------------+-----+
8 rows in set (0.00 sec)
Now the cache memory has been defragmented and you can see that the Qcache_free_blocks has reduced.
Parameter3:
====
sort_buffer_size:
====
Improves large and complex sorts. Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations. The default value for sort_buffer_size is 2MB. Please note this buffer is at a per client level.
When to increase sort_buffer_size:
Queries that sort rows, either by GROUP BY or ORDER BY undergo three processes.
1.Find the rows
2.sort the rows
3.read the sorted rows
MySQL first tries to sort the rows to the memory, the size of which is controlled by sort_buffer_size system variable. If the memory is not sufficient, it creates a temporary file to create the sorted rows, however the temporary file need to be sorted too after all the rows have been found from step 1. The re-sorting of the temporary file counts to the status variable Sort_merge_passes. MySQL actually creates a second temporary file into which it puts the sorted contents of the first file. Therefore, its common to see almost exactly twice as many created temporary files as Sort_merge_passes.
Sort_merge_passes can be reduced by increasing sort_buffer_size.
Parameter4:
====
key_buffer_size:
===
key_buffer_size is the size of buffer used by all the indexes. Ideally, it should be large enough to contain all the indexes ie., total size of all the .MYI files in the server. A rule of thumb is to set to to at least a quarter of the memory available, half the memory to the maximum but not more than that.
When to increase your key_buffer_size:
The status variables you should be checking to find this are Key_read_requests, Key_reads, Key_write_requests and Key_writes.
Key_read_requests : The number of requests to read a key block from the cache.
Key_reads : The number of physical reads of a key block from disk.
Key_write_requests : The number of requests to write a key block to the cache.
Key_writes : The number of physical writes of a key block to disk.
The optimum solution is to keep the ratio Key_reads : Key_read_requests should be 1:100 and Key_writes / Key_write_requests should always be less than 1.
If the Key_reads value is high compared to Key_read_requests, it is high time you increase your key_buffer_size. sort_buffer_size:
Improves large and complex sorts. Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations. The default value for sort_buffer_size is 2MB. Please note this buffer is at a per client level.
When to increase sort_buffer_size:
Queries that sort rows, either by GROUP BY or ORDER BY undergo three processes.
1.Find the rows
2.sort the rows
3.read the sorted rows
MySQL first tries to sort the rows to the memory, the size of which is controlled by sort_buffer_size system variable. If the memory is not sufficient, it creates a temporary file to create the sorted rows, however the temporary file need to be sorted too after all the rows have been found from step 1. The re-sorting of the temporary file counts to the status variable Sort_merge_passes. MySQL actually creates a second temporary file into which it puts the sorted contents of the first file. Therefore, its common to see almost exactly twice as many created temporary files as Sort_merge_passes.
Sort_merge_passes can be reduced by increasing sort_buffer_size.
Parameter5 :
=====
read_rnd_buffer_size:
===
read_rnd_buffer_size is used after a sort for reading the rows in the sorted order. If your application has a lot of queries with ORDER BY, increasing this can improve the performance. This is buffer is also at a per client basis. The default value for read_rnd_buffer_size is 128K. A general rule of thumb is to allocate 1MB for every 1GB memory.
Parameter6:
====
tmp_table_size:
====
Sometimes for executing a statement, a temporary table needs to be created. This variable determines the maximum size for a temporary table in memory.
Always try to avoid temporary table creation by optimizing your query. But if it is unavoidable, make sure that the table is created in the memory. If the memory is not sufficient, a MyISAM table will be created in the disk.
When to increase tmp_table_size:
Check the processlist and see if any query is using temporary tables and is taking too long to resolve. In this case, you should increase the tmp_table_size.
You can also check the status variables Created_tmp_disk_tables and Created_tmp_tables.
Created_tmp_disk_tables : Number of temporary tables created on disk while executing a statement Created_tmp_tables : Number of in-memory tables created.
If a large number of tables are created in the disk, its high time you increase your tmp_table_size. Please note memory is allocated in per client basis (per thread basis).
Paramter7:
====
thread_cache:
====
If your server is busy is making a lot of new connections ie., if you high max_connections, then the server will create a lot of new threads at a very high rate. This may eat up a lot of CPU time.
So the solution is to increase the thread_cache. When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn't give a notable performance improvement if you have a good thread implementation.) By examining the difference between the status variables Connections and Threads_created you can see how efficient the current thread cache is for you.â
If Threads_created is big, you may want to increase the thread_cache_size variable. The cache hit rate can be calculated with Threads_created/Connections.
Cool :)
Here is a script that will help you to determine the values:
===
# wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
# perl mysqltuner.pl
===
run the script and you will see the output as:
====
Variables to adjust:
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)
innodb_buffer_pool_size (>= 32M)
===
This means that query_cache_size should be given a value greater than or equal to 8MB.
Now, many of us may not be satisfied with this, they still will eager to understand the parameters. Those people can continue reading
Parameter 1
=====
table_cache
=====
Each time MySQL accesses a table, it places the table in the cache. If your application accesses many tables, it is always good to have them in cache so that data retrieve is faster.
You can check whether your system needs to have the table_cache value increased by checking the open_tables and opened_tables status variables during peak time.
open_tables is the number of tables opened in cache. Whereas opened_tables is the total number of tables open. Since MySQL supports multi-threading, several queries might be executed on the same table at the same time. So each of these queries will open a table.
The default value to table_cache is 64. Lets consider a sample scenario.
===
table_cache = 64
mysql> SHOW STATUS LIKE "open%tables%";
open_tables = 64 opened_tables = 5426787
====
Here table_cache has maxed out and opened_tables is fairly high. In this case, if you have enough memory, increase table_cache to reduce the number of opened_tables
Parameter2:
====
query_cache_size
====
Query caching has been introduced from MySQL 4 onwards. If your application executes a particular query again and again, MySQL can cache the result set, thereby avoiding the overhead of running through the data over and over and thereby increase the execution time.
You can enable query caching by setting the server variable query_cache_type=1 and setting the cache size in the variable query_cache_size. If either of the above is set to 0, query caching will not be enabled.
=====
There are three status for query caching;
Disabled - query_cache_type = 0
Enabled - query_cache_type = 1
On Demand - query_cache_type = 2
=====
Some sample scenarios:
If you have enabled query caching and do not want the result of a particular query to be cached, you can mention it in your query by specifying SQL_NO_CACHE.
eg: SELECT SQL_NO_CACHE id, name FROM employee_info WHERE employee_rank < 5;
In the above case, the result of the query will not be cached. Similarly, you can enable query caching in ON DEMAND query caching by specifying SQL_CACHE in your query.
eg: SELECT SQL_CACHE id, name FROM employee_info WHERE employee_rank < 5;
After executing the query the result will be added to the cache memory and will be used if the query is executed again.
How to check the query cache status variables
mysql> SHOW STATUS LIKE "%qcache%";
+-------------+-----+
| Variable_name | Value |
+-------------+-----+
| Qcache_free_blocks | 2253 |
| Qcache_free_memory | 9184200 |
| Qcache_hits | 247217 |
| Qcache_inserts | 50012 |
| Qcache_lowmem_prunes | 15666 |
| Qcache_not_cached | 13269 |
| Qcache_queries_in_cache | 5215 |
| Qcache_total_blocks | 13117 |
+-------------+-----+
8 rows in set (0.00 sec)
This is a result from a server with query_cache_type set to 1 ( Enabled). Now lets see what all these status variables stand for.
Qcache_free_blocks: The number of free memory blocks in the cache memory.
Qcache_free_memory: The amount of free memory for the query cache.
Qcache_hits : The number of query cache hits.
Qcache_inserts : The number of queries added to the query cache.
Qcache_lowmem_prunes : The number of queries that were deleted from the query cache because of low memory.
Qcache_not_cached : The number of non-cached queries (not cache-able, or not cached due to the query_cache_type setting).
Qcache_queries_in_cache : The number of queries registered in the query cache. Qcache_total_blocks: The total number of blocks in the query cache.
Qcache_free_blocks is an indication of fragmentation and if this is high in relation to the Qcache_total_blocks, it means that the cache space is wasted. The default block size for query cache is 4KB. If your query result is small and you see fragmentation, you should decrease the block size. You can use the system variable query_cache_min_res_unit to redefine the block size. And if the query result is large, you should increase the block size.
To defragment the query cache, you can use the command
mysql> FLUSH QUERY CACHE;
Query OK, 0 rows affected (0.07 sec)
mysql> show status like "%qcache%";
+-------------+-----+
| Variable_name | Value |
+-------------+-----+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 9090576 |
| Qcache_hits | 248169 |
| Qcache_inserts | 50147 |
| Qcache_lowmem_prunes | 15666 |
| Qcache_not_cached | 13316 |
| Qcache_queries_in_cache | 5273 |
| Qcache_total_blocks | 10979 |
+-------------+-----+
8 rows in set (0.00 sec)
Now the cache memory has been defragmented and you can see that the Qcache_free_blocks has reduced.
Parameter3:
====
sort_buffer_size:
====
Improves large and complex sorts. Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations. The default value for sort_buffer_size is 2MB. Please note this buffer is at a per client level.
When to increase sort_buffer_size:
Queries that sort rows, either by GROUP BY or ORDER BY undergo three processes.
1.Find the rows
2.sort the rows
3.read the sorted rows
MySQL first tries to sort the rows to the memory, the size of which is controlled by sort_buffer_size system variable. If the memory is not sufficient, it creates a temporary file to create the sorted rows, however the temporary file need to be sorted too after all the rows have been found from step 1. The re-sorting of the temporary file counts to the status variable Sort_merge_passes. MySQL actually creates a second temporary file into which it puts the sorted contents of the first file. Therefore, its common to see almost exactly twice as many created temporary files as Sort_merge_passes.
Sort_merge_passes can be reduced by increasing sort_buffer_size.
Parameter4:
====
key_buffer_size:
===
key_buffer_size is the size of buffer used by all the indexes. Ideally, it should be large enough to contain all the indexes ie., total size of all the .MYI files in the server. A rule of thumb is to set to to at least a quarter of the memory available, half the memory to the maximum but not more than that.
When to increase your key_buffer_size:
The status variables you should be checking to find this are Key_read_requests, Key_reads, Key_write_requests and Key_writes.
Key_read_requests : The number of requests to read a key block from the cache.
Key_reads : The number of physical reads of a key block from disk.
Key_write_requests : The number of requests to write a key block to the cache.
Key_writes : The number of physical writes of a key block to disk.
The optimum solution is to keep the ratio Key_reads : Key_read_requests should be 1:100 and Key_writes / Key_write_requests should always be less than 1.
If the Key_reads value is high compared to Key_read_requests, it is high time you increase your key_buffer_size. sort_buffer_size:
Improves large and complex sorts. Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations. The default value for sort_buffer_size is 2MB. Please note this buffer is at a per client level.
When to increase sort_buffer_size:
Queries that sort rows, either by GROUP BY or ORDER BY undergo three processes.
1.Find the rows
2.sort the rows
3.read the sorted rows
MySQL first tries to sort the rows to the memory, the size of which is controlled by sort_buffer_size system variable. If the memory is not sufficient, it creates a temporary file to create the sorted rows, however the temporary file need to be sorted too after all the rows have been found from step 1. The re-sorting of the temporary file counts to the status variable Sort_merge_passes. MySQL actually creates a second temporary file into which it puts the sorted contents of the first file. Therefore, its common to see almost exactly twice as many created temporary files as Sort_merge_passes.
Sort_merge_passes can be reduced by increasing sort_buffer_size.
Parameter5 :
=====
read_rnd_buffer_size:
===
read_rnd_buffer_size is used after a sort for reading the rows in the sorted order. If your application has a lot of queries with ORDER BY, increasing this can improve the performance. This is buffer is also at a per client basis. The default value for read_rnd_buffer_size is 128K. A general rule of thumb is to allocate 1MB for every 1GB memory.
Parameter6:
====
tmp_table_size:
====
Sometimes for executing a statement, a temporary table needs to be created. This variable determines the maximum size for a temporary table in memory.
Always try to avoid temporary table creation by optimizing your query. But if it is unavoidable, make sure that the table is created in the memory. If the memory is not sufficient, a MyISAM table will be created in the disk.
When to increase tmp_table_size:
Check the processlist and see if any query is using temporary tables and is taking too long to resolve. In this case, you should increase the tmp_table_size.
You can also check the status variables Created_tmp_disk_tables and Created_tmp_tables.
Created_tmp_disk_tables : Number of temporary tables created on disk while executing a statement Created_tmp_tables : Number of in-memory tables created.
If a large number of tables are created in the disk, its high time you increase your tmp_table_size. Please note memory is allocated in per client basis (per thread basis).
Paramter7:
====
thread_cache:
====
If your server is busy is making a lot of new connections ie., if you high max_connections, then the server will create a lot of new threads at a very high rate. This may eat up a lot of CPU time.
So the solution is to increase the thread_cache. When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn't give a notable performance improvement if you have a good thread implementation.) By examining the difference between the status variables Connections and Threads_created you can see how efficient the current thread cache is for you.â
If Threads_created is big, you may want to increase the thread_cache_size variable. The cache hit rate can be calculated with Threads_created/Connections.
Cool :)
# wget http://mysqltuner.pl/mysqltuner.pl
# perl mysqltuner.pl
>> MySQLTuner 1.2.0 Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with 'help' for additional options and output filtering - See more at: http://bobcares.com/blog/how-to-increase-your-server-speed/#sthash.UEAYoSA9.dpuf
# perl mysqltuner.pl
>> MySQLTuner 1.2.0 Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with 'help' for additional options and output filtering - See more at: http://bobcares.com/blog/how-to-increase-your-server-speed/#sthash.UEAYoSA9.dpuf
# wget http://mysqltuner.pl/mysqltuner.pl
# perl mysqltuner.pl
>> MySQLTuner 1.2.0 Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with 'help' for additional options and output filtering - See more at: http://bobcares.com/blog/how-to-increase-your-server-speed/#sthash.UEAYoSA9.dpuf
# perl mysqltuner.pl
>> MySQLTuner 1.2.0 Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with 'help' for additional options and output filtering - See more at: http://bobcares.com/blog/how-to-increase-your-server-speed/#sthash.UEAYoSA9.dpuf
# wget http://mysqltuner.pl/mysqltuner.pl
# perl mysqltuner.pl
>> MySQLTuner 1.2.0 Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with 'help' for additional options and output filtering - See more at: http://bobcares.com/blog/how-to-increase-your-server-speed/#sthash.UEAYoSA9.dpuf
# perl mysqltuner.pl
>> MySQLTuner 1.2.0 Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with 'help' for additional options and output filtering - See more at: http://bobcares.com/blog/how-to-increase-your-server-speed/#sthash.UEAYoSA9.dpuf
No comments:
Post a Comment
Note: only a member of this blog may post a comment.