We usually run MySQL with default setting in production environment without analyzing the hardware configuration, database size, load on database and nature of application. That is big mistake we made!!! And any point of time MySQL will face performance issue.
Lets make it easier. The goal of this post is to give a list of critical setting which should be configured after first MySQL installation.
Basic Setting
key_buffer_size : this parameter cache the MYISAM table data and enhance the read performance. Examine how much data is on MyISAM tables and configure it accordingly.
InnoDB Setting
innodb_flush_method: this setting controls how data and logs are flushed to disk. Popular values are O_DIRECT when you have a hardware RAID controller with a battery-protected write-back cache and fdatasync (default value) for most other scenarios. sysbench is a good tool to help you choose between the 2 values.
innodb_log_buffer_size: this is the size of the buffer for transactions that have not been committed yet. The default value (1MB) is usually fine but as soon as you have transactions with large blob/text fields, the buffer can fill up very quickly and trigger extra I/O load. Look at the Innodb_log_waits status variable and if it is not 0, increase
Lets make it easier. The goal of this post is to give a list of critical setting which should be configured after first MySQL installation.
Basic Setting
innodb_buffer_pool_size : The buffer pool cached all data and indexes so keeping it as large as possible will ensure mysql use memory rather than disk for read operation. Recommended value is 80% of your system memory. key_buffer_size : this parameter cache the MYISAM table data and enhance the read performance. Examine how much data is on MyISAM tables and configure it accordingly.
innodb_log_file_size: this is size of redo log. the redo logs ensure writes are fast and recovery at the time of crash. In latest MySQL 5.6 redo logs can be configured up to 4 GB. Configuring it 1 GB is ideal value for write performance and fast recovery. If your database is write intensive then increase it to 4 GB but recovery would be slow in case of crash.max_connections: define how many connection can be made to database at any point of time, what could be the feasible value? high values might consume all resource and hang your database ( a single connection use some memory ) and lower value might unstable your application. Better is to monitor your application for some time and come to a final value. In case application doesnt kill connection after it complete it operation then handle it at application level.InnoDB Setting
innodb_file_per_table : this setting will tell InnoDB if it stores data and indexes in shared tables space ( ibdata1) or in individual .ibd files. Setting this value ON will benefit you in reclaim space when dropping, truncating or rebuilding a table.innodb_flush_log_at_trx_commit : setting of 1 means that InnoDB is fully ACID compliant. innodb_flush_method: this setting controls how data and logs are flushed to disk. Popular values are O_DIRECT when you have a hardware RAID controller with a battery-protected write-back cache and fdatasync (default value) for most other scenarios. sysbench is a good tool to help you choose between the 2 values.
innodb_log_buffer_size: this is the size of the buffer for transactions that have not been committed yet. The default value (1MB) is usually fine but as soon as you have transactions with large blob/text fields, the buffer can fill up very quickly and trigger extra I/O load. Look at the Innodb_log_waits status variable and if it is not 0, increase
innodb_log_buffer_size.
