Understanding the Parameters Involved in Optimizing MySQL

Reading Time: 5 mins

MySQL, free and open-source relational database is the most opted server among the developers’ community. For a stable application, configuration and performance are the key components. These factors form crucial for driving revenue and productive results for your business. Therefore, it is necessary to tune the MySQL server so as to enhance the efficiency of any application. 
Before start tuning the MySQL server, you have to know the location of the configuration file. The location of the configuration file will vary from one operating system to another. The location of the configuration file is /etc/mysql/mysql.conf.d/mysqld.cnf since, Linux system is considered here.
If needed, one can back up the /etc/mysql/my.cnf before proceeding with the MySQL configuration.

sudo cp /etc/mysql/my.cnf ~/my.cnf.backup

Fine Tuning the MySQL Server

For optimizing MySQL, you need to increase the value of some parameters which includes;


The lower the key_buffer_size, the higher MySQL speed. According to the buffer size, the speed of the server will eventually vary by assuming that there is  free memory. In the case of InnoDB, the key buffer size may go up to 70% whereas, in MyISAM, it takes not more than 25%. 


Note: It is recommended to set 64M for servers with more than 256 MB of RAM and 16M for servers with 128 MB of RAM. 


The small default value ensures that you don’t run out of memory by using large packets during an unnecessary period and assist in detecting the incorrect packets during client and server communication.  
The default max_allowed_packet in the client program is 16 MB, you can set the larger value by:


The default max_allowed_packet in the server program is 4MB, you can set the larger value by:


Note: The maximum allowed packet which can be transmitted between the client and server communication is 1 GB. 


The size of each thread is controlled by this thread_stack system variable. The MySQL default thread size is ample to carry out the usual tasks whereas, in case of any errors, you can eventually increase the size of it.

thread_stack= 256K


Only when the thread_cache is empty (i.e. the thread_cache_size is set to 0) the new thread will be created for the new connections. Otherwise, requests will be satisfied by reusing the threads which are stored in a cache. This comes as a great help at the time of receiving hundreds of connections per second. During such periods, you can set the thread_cache_size high so that the majority of the connections will use the cached threads. 



Raising the max_connection forms crucial when a client encounters too_many_connections errors that happens when all available connections are used by other clients. Generally, the allowed number of connections is controlled by the max_connections system. The default value is 151 connections and you can also set the max connections to a larger value of the variable to support more connections. 



The query_cache_size is the memory needed for query caching. If a larger value is set, then the smaller queries cannot be cached and if a lower value is set then the larger queries cannot be cached. Hence, it must be configured as per your needs and requirements.



Similar to the query_cache_size, the query_cache_limit indicates the number of kilobytes in which queries are cached. The default query cache limit is 1MB and can configure it up to 4GB.



This is to set the type of query cache. There are three ways in which one can set the directive; value zero is meant for not to cache or retrieve results, value 1 is meant to cache every query except the one which starts with SELECT S_NO_CHANGE and the value 2 is meant to cache queries that only starts with SELECT SQL_CACHE.


Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top