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;
key_buffer_size
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%.
key_buffer_size=64M
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.
max_allowed_packet
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:
max_allowed_packet=32M
The default max_allowed_packet in the server program is 4MB, you can set the larger value by:
max_allowed_packet=16M
Note: The maximum allowed packet which can be transmitted between the client and server communication is 1 GB.
thread_stack
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
thread_cache_size
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.
thread_cache_size=54
max_connections
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.
max_connections=170
query_cache_size
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.
query_cache_size=128MB
query_cache_limit
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.
query_cache_limit=4MB
query_cache_type
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.
query_cache_type=1