MySQL is the most widely used multi-user, multi-threading SQL database management system. To optimize MySQL performance, a DBA should have reasonable knowledge of MySQL system variables. This article covers the basics of MySQL server optimization. We will first discuss MySQL optimization during installation. Then we will discuss the seven most important and common system variables. Plus, a brief note on how to optimize the server using those seven variables.
We will also discuss MySQL status variables. This article would be very useful for a newbie MySQL DBA when setting up a MySQL server.
1. How to optimize the MySQL Installation.
Most server owners prefer to have a control panel software (like cPanel, Plesk etc) installed in their server so that they can easily manage their servers. In that case, MySQL installation will be customized according to the control panel software installed.
If you are planning to compile MySQL by your own, following are a few points which you should consider for faster performance.
- While compiling MySQL, compile it statically ( -static option). This will require more disk space but runs faster.
- Enable debug option if you actually need it. Enabling debug mode installs a safe Memory Allocation (SAFEMALLOC) checker. Running SAFEMALLOC is slow. So if you have enabled debug mode and is having performance problems, you should start mysqld with -skip-safemallaoc option.
- Compile without frame pointers (-fomit-frame-pointer). Frame Pointer is a pointer to the current stack frame. Frame pointer is mainly used for debugging purpose. Compiling without frame pointers make mysqld run 1-4% faster.
- Standard binary distributions from MySQL are compiled with support for all character sets. When you are compiling by yourself, use the character sets required for your application only. This can be specified by the -with-charset option in the configure option. Or you can use the minimum character set using the -with-extra-charsets=none option.
2. Compilation over.. Now what??
Now according to your requirement, you have compiled your MySQL server and now we can go forward with optimizing it.
Optimizing MySQL is done based on the server specifications and the applications which are running on the server. The MySQL server configuration file should be named my.cnf and is usually placed in DATADIR/my.cnf. You can specify the location to the my.cnf file using the âdefault-file argument during installation. This helps associate configuration files with particular server instances.
Fortunately, MySQL itself is providing some sample my.cnf files which fit to most of the systems. The different config files that MySQL provide are my-huge.cnf, my-large.cnf, my-medium.cnf and my-small.cnf.
Each config file is designed for different systems and it is always recommended to select the sample config according to your system specs.
- my-huge.cnf : recommended for systems that have at least 1GB memory, and run mainly MySQL
- my-large.cnf : for systems with slightly less memory (512MB), and also mainly running MySQL.
- my-medium.cnf : tweaked for a system where MySQL and a Web server are running together with around 128MB, or around 64MB
- my-small.cnf : for system with less than 64MB
So choose the right config file for your system and your server should work good. But you can get better results if you tweak the variables properly. For this you need to know the different system and status variables and how they are linked together. Remember, changing the system variables improperly may lead to worse situations.There are several MySQL system variables which can be tweaked for improving performance. But here, we will be discussing about 7 variables, tuning which can change the performance drastically.
3. Is the sample config enough for me?
I would say NO. Each database server will be running different applications in it and so you MUST fine tune your server according to the applications and resourse utilization. Now.. how can I identify the variables that need to be tweaked? And how should I go for it?
Below are the 7 most important system variables that need to be tweaked in normal case.
3.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.
3.2. 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 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
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.There are situations when a query is not cached, such as returning current time, random number etc. Any queries making use of the following commands / types / functions will not be cached:
User-Defined Functions
BENCHMARK CONNECTION_ID CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATABASE
ENCRYPT (with one parameter) FOUND_ROWS GET_LOCK LAST_INSERT_ID LOAD_FILE MASTER_POS_WAIT NOW RAND
RELEASE_LOCK SYSDATE UNIX_TIMESTAMP (without parameters) USER
query contains user variables
query references the mysql system database
Queries like these
SELECT ... IN SHARE MODE
SELECT ... INTO OUTFILE ...
SELECT ... INTO DUMPFILE ...
SELECT * FROM AUTOINCREMENT_FIELD IS NULL
queries inside transactions (in MySQL 4.0.x)
Some interesting facts about Query Caching:
Query caching is case sensitive:
Eg: mysql> show status like "%qcache%"; +-------------+-----+ | Variable_name | Value | +-------------+-----+ | Qcache_free_blocks | 1| | Qcache_free_memory | 12574168 | | Qcache_hits | 0 | |Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached |0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------+-----+ 8 rows in set (0.00 sec)
mysql> select * from wp_post2cat where category_id=14; +----+----+---+ +-----+-------+ 15 rows in set (0.03 sec)
mysql> select * from wp_post2cat where category_id=14; +----+-----+-------+ +----+-----+-------+ 15 rows in set (0.00 sec)
mysql> show status like "%qcache%"; +-------------+-----+ | Variable_name | Value | +-------------+-----+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 12571408 | | Qcache_hits | 1 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------+-----+ 8 rows in set (0.00 sec)
If you check the status variables,Before executing the query, the Qcache_inserts and Qcache_hits was 0. And the query took 0.03secs to execute for the first time.
When the query was executed for the second time, the Qcache_inserts and Qcache_hits was increased by 1 and took less time to execute.
Now I am executing the same query with a small difference:
mysql> SELECT * FROM wp_post2cat where category_id=14; +----+-----+-------+ +----+-----+-------+ 15 rows in set (0.02 sec) mysql> show status like "%qcache%"; +-------------+-----+ | Variable_name | Value | +-------------+-----+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 12569160 | | Qcache_hits | 1 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 2 | | Qcache_total_blocks | 6 | +-------------+-----+ 8 rows in set (0.00 sec)
Now you can see that the Qcache_inserts has increased and the query took 0.02 secs to execute. MySQL has inserted another query in the cache since it is considering this as a separate query. So if you are a programmer, make sure that you write all the query in a standard format so that your program utilizes query caching properly.
Query cache Clearing:
MySQL is clever enough to clear the query results whenever a change is made to the table. Any of INSERT, UPDATE, DELETE, TRUNCATE, ALTER, DROP TABLE or DROP DATABASE will remove queries from the cache. You can manually clear the query cache with RESET QUERY CACHE.
3.3. 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.
3.4. 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.
3.5. 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.
3.6. 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).
3.7. 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.
4. Can I keep on increasing the size….?
The answer is NO!!. You should be aware of the resources available while tweaking the system variables. Here are some points you should take care of while optimizing your server.
4.1. DO NOT allocate too much memory.
Using less memory than available can reduce the performance, but using more memory than available can lead to worse performance or even crashes. A general resource allocation formula is
memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections
4.2. Never let your box use the swap space actively.
Using swap space will affect the server performance badly.
4.3. Do not just scale the sample config file blindly.
If the my-huge.cnf (for 1GB memory) has some variable=1MB. And suppose you are having 16GB so you should set it to 16MB.. NO!! Set the variables wisely.
4.4. Consider per session variable.
Suppose you have set the sort_buffer_size for your server as 1MB and you have one query which requires more sort_buffer_size (say 16MB). Do NOT set sort_buffer_size=16M globally.
Use SET command to increase the sort_buffer_size
execute the command and
change it to 1MB using SET command.
5. All set….??.. NO!!
Mentioned above are only 7 of the 100+ system variables. There are several other variables which can be tweaked.
Tuning server parameters can increase the performance. But this doesn’t mean that MySQL optimization is only Server parameters’ optimization. No.. The database design and the SQL queries used also plays an equally important role. In fact, I would say Schema and Queries hold an upper hand. If the database is properly designed and the queries are properly created, tuning server parameters will make your server / site lightning speed..
Enjoy using MySQL…. 
