In this article, we will learn and discuss some important performance tuning settings for MySQL which we need to implement after the MySQL Installation better and speedy performance.
Assuming that we have already installed the MySQL, I am providing some tips before we start to know about Performance tuning settings for MySQL.
Even experienced IT persons may make some mistakes which will lead to many problems, so before we apply any recommendations which are shown in this article, we will keep the below items in mind to avoid issues or problems.
We will apply only one setting at a time so that we can estimate the changes in a beneficial manner.
Almost all the changes can be changed at the runtime level with “SET GLOBAL” which is very easiest and allow us to make revert the changes which we apply and creates any problems. Finally, we can apply the permanent changes by setting in the configuration file.
If a change in the configuration is not applied even after the service restart (MySQL) it might be due to the incorrect configuration file? Or do the setting applies to the right section?.
MySQL will not allow the duplicate settings. If we want to keep track of the changes it’s suggested to use version control.
Here are some Performance Tunning settings in the [mysqld] section where we can learn about some performance tuning settings. It is always recommended to change these settings at the time of installation only.
innodb_buffer_pool_size − This is a very important setting to look immediate after the installation using InnoDB. The InnoDB is the buffer pool where the data is indexed the cached, which has a very large possible size that will make sure and use the memory no the disk space for most of the read-write operations, generally the size of InnoDB values are 5-6GB for 8GB RAM.
innodb_log_file_size − This settings is for redo logs. The redo logs makes sure that read and write are fast and durable even at the time of crash recovery, in previous versions of MySQL 5.1, it is very hard to make adjustment as we need a very large redo logs for a better performance and a small redo log is a ver for fast crash recovery of MySQL. After the MySQL version 5.5 the crash recovery performance is improved. The limit of the MySQL redo logs size was limited to 4GB in MySQL 5.5 now the size of redo logs size has been removed in the MySQL 5.6.
max_connections − To avoid the “Too many connections” error we have to increase the max_connections the default connections is 151, the main drawback of the high values for the max_connections make the server unresponsive if the no of connections is more and active transactions.
Innodb_log_buffer_size − This setting is the size of the buffer for the transactions which are not been committed yet. The default value is 1MB, as we have big transactions with text/blob fields the buffer size will fill up very quicker and triggers extra Input and output load, we can increase the innodb_log_buffer_size.
Innodb_flush_method − This setting for to controls how logs and data are flushed to the disk. The the best values are O_DIRECT when we have a RAID controller hardware with write-back cache and data sync for the most of the scenarios. Sysbench is a good tool which will hep you to choose the values.
query_cache_size − This setting is well known for the bottleneck which can we saw even we have concurrency moderate level. The best option is to disable if from the beginning, to disable we needed to send query_cache_size=0 if we are using the MySQL 5.6 version by default it has been disabled. We have others ways to speed up the indexing using the Memcache or Redis for the instance. If we have already configured the application with query cache enabled and we don’t have any issues then the query cache is useful, we needed to be very cautious to disable the query cache.
log_bin − This setting is very useful if we want to make the server as a replication master server. It is also very useful for a single server when we want to be able to do (point-in-time-recovery) restore the latest backup and apply the binary logs. Once the log_bin logs are enabled are kept forever and we needed to clean up the older logs to save the disk space on the server we can do it by settings PURGE BINARY LOGS or by setting the expire_logs_days setting by specifying that after how many days the log_bin logs are automatically deleted.
skip_name_resolve − This setting is used fully when a client connects to the MySQL server. The server will perform hostname resolutions and when DNS is slow, the connection will also become very slow, so it is recommended to start the server with skip_name_resolve to disable the DNS lookups, so when we use the GRANT statements, we must use the IP address instead of hostname, Hence, be careful when we adding this setting to an existing application configuration.
In the above article we have learned how to tune the MySQL to give a better performance, and speed up the read and write levels of the MySQL by changing the log_bins, skip_name_resolve, query_cache_size, innodb_log_buffer_size, max_connections, innodb_log_file_size, innodb_buffer_pool_size.