My.ini configuration optimization after MySQL server installation

MySQL server after the installation of parameter adjustment and how to optimize MySQL performance, directly using the default MY.CNF parameters, of course, in most cases is not a problem. To gain performance gains and reasonable utilization of resources by adjusting parameters, be aware of the following parameters:

Key_buffer_size
This parameter is very important for the MyISAM engine, and if your server is primarily MyISAM, it is recommended that you set this value to memory 30%-40%. Many optimization tools are a value that calculates the total size of the current index of the system, and it should be noted that the value only caches index data. and MyISAM use the operating system page cache to cache metadata, so you have to set aside enough memory space to the OS level cache, of course, this is not the larger the better, generally if the index will be nearly 1G when the allocation of 4G around the key_buffer_size, and then waste, If your database is only a small number of tables used to MyISAM you can keep the value in 16-32m, see the situation increase the value, once the index data exceeds your set value MySQL will create a temporary table on the disk to meet its needs.

Innodb_buffer_pool_size
This parameter is very important for the InnoDB engine, compared to the MyISAM engine key_buffer_size InnoDB is more sensitive to this parameter, even if the key_buffer_size settings are wrong, MyISAM still can work, InnoDB can be more difficult than it is, mainly because InnoDB data and index cache are placed in the buffer Pool, can be as large as possible, such as the total amount of free memory 70-80%. MySQL automatically merges the dirty pages that meet the criteria into the disk, and the TPS is slightly volatile.

Innodb_additional_mem_pool_size
This thing is not so obvious to the performance improvement, the memory is very comfortable to give a 20MB bar.

Innodb_log_file_size
Very important, redo log file size, recommended set to Innodb_buffer_pool_size 1/4, not the larger the better, too big will lead to MySQL crash restart when the recovery time is super long ... I am generally set to 64-512m.

Innodb_log_buffer_size
The default is 8M, do not recommend that the value of the adjustment is too large, general data here to stay at most is 1 seconds to be brushed to disk, if the transaction generally involves large chunks of data, you can appropriately increase the value of the general 8-16m.

Innodb_flush_log_at_trx_commit
has three values 0, 1, 2, the default is 1,0 refers to the transaction at the time of submission does not brush data to log buffer,1 is to force the brush to log buffer while invoking the OS Fsync () force brush to disk, 2 refers to call only write () does not call Fsync (), 0 is undesirable, Unless the table is unimportant, 1 and 2 are equivalent to fish and paws, 1 can ensure the integrity of the transaction when the database crashes, because log buffer is in memory, 1 is forced Fsync () to disk, 2 is pushed to memory by the system when it is written to disk , so 1 of the pressure on the disk can be imagined, in the wood has SSD or RAID10 system or disk IO and data integrity of the two choose one.

Table_cache
The overhead of opening a table is huge, for example, MyISAM tables need to open myi files frequently and identify the table in their head, and when your system has a large number of tables being accessed at the same time, you don't want to open the table from disk every time? The significance of setting Table_cache is to cache the table as much as possible, reduce disk pressure while improving system performance, this value is generally set to 1024 or higher, if set to 1024 higher value to note the maximum number of open file descriptor system, set Open_files_ Limit and the system of ULIMIT-SHN 65535.

Thread_cache
Not much to say that is generally a logical CPU let him be responsible for two threads, cores * 2

Innodb_doublewrite
Doublewrite buffer, is a innodb table space on the physical disk of a piece of area, InnoDB each synchronization dirty pages are first from memory to Doublewrite buffer, and then sync to the physical disk of the actual location, do not recommend the shutdown, Loss of system performance is not significant.

Other such as Query_cache_size,sync_binlog and so on by default to be good.