When dealing with huge data on MySQL database, it is quintessential to tune MySQL for optimal performance which comprises of reviewing MySQL configuration and make necessary improvements or optimization.
Before getting your hands on MySQL configuration it is crucial to bear in mind a few points in order to avoid any irrevocable mistake.
• Change only one setting at a time as this is the only way to access the impact of the changed factor on the performance of MySQL. Another point is SET GLOBAL can be used to change settings at runtime. Doing so renders you with the scope to revert any change quickly in case things turn out otherwise.
• It is also important to remember that if the server does not start upon making any change, then check for the correct units used. For example, max_connection has no dimensions, while innodb_buffer_pool_size is set in bytes.
• Please make sure that you are using version control for keeping track of all the changes made.
Let us first of all look at some basic settings for tuning MySQL for optimal performance.
• The number 1 setting to look at while installation using InnoDB is innodb_buffer_pool_size. Setting the buffer pool to the maximum possible value will make sure that you use memory for most of the read operations and not disks. Values that can be used for buffer pool size are 6GB for 8GB RAM, 20 to 25GB for 32GB RAM, and 100 to 120GB for 128GB RAM.
• For achieving good write performance as well as fast crash recovery in a write-intensive application using MySQL 5.6 onwards, the innodb_log_file_size or the redo logs size can be set to 4G.
• In case you face ‘too many connections error’ very often then you must consider increasing the value of max_connections. This error occurs because many a times application does not close database connections properly. Do not go for a very high value as doing so may result in unresponsive server. A thread pool at MySQL level or a connection pool at application level can prove to be of great help.
Now let us look at some InnoDB settings. Since MySQL 5.5 InnoDB is the default storage engine and is used much frequently than other storage engines. Thus, configuring it should be done extremely carefully.
• Setting the value of innodb_file_per_table to ON allows you to reclaim memory space when you carry out any truncate, drop or rebuild action on a table. However, in the scenarios of very large number of tables you don’t want to set file per table ON.
• The default value for innodb_flush_log_at_trx_commit is set to 1, which implies that InnoDB is completely ACID compliant. This setting is the best setting when data safety is on your priority, however, this can have considerable impact on the disks performance. Setting its value to 2 is optimal on performance front. In this case, the log buffer is flushed to OS file cache every time a transaction is committed. Setting its value to 0 further improves the performance but doing so can result in loss of some data in situations of a crash.
• Innodb_flush_method monitors how logs as well as data are flushed to the disk. There are mainly 2 values for this method. One is O_DIRECT and other is fdatasync. O_DIRECT is used when you are using a RAID controller with battery protected write back cache, while fdatasync is its default.
• innodb_log_buffer_size is buffer size for uncommitted transactions. Its default value is set to 1MB, which can fall short when you have large text fields transactions thereby triggering extra I/O load.
Tuning MySQL for optimal performance is a continuous process depending on your workload or hardware. For instance write-intensive workloads, high concurrency, low memory and fast disk, will require specific tuning with due time. You will need to configure, monitor and tune the server till optimal result is obtained. But with the above MySQL configuration you can quickly start working with your MySQL server with optimal performance.