MySQL's my.cnf configuration tuning

Optimized for pre_thread_buffers (can be understood as the memory allocated for each user process connected to MySQL):

• Read_buffer_size

  This parameter represents the sequential scan of the table, which represents the size of the buffer allocated by each thread. In the case of full table scan, the data will be sequentially read in the order of storage, each time the database is read read_buffer_size, when the buffer space is full or after the end of the read, then the buffer data back to the upper level of the caller, in order to improve efficiency. The default 128kb, do not set too large, generally in 128~256 can.

• Read_rnd_buffer_size

  This parameter represents a random read of the table, which indicates the buffer size allocated by each thread, such as when an order by sort operation is done by a non-indexed field, the buffer is used to temporarily access the data, the default is 256KB, and the 128~256 is not set too much.

• Sort_buffer_size

  When the table makes an order by and group by sort operation, the using Filesort will appear because the sorted fields are not indexed, and this parameter can be used to increase the buffer size allocated for each thread in order to improve performance. The default is 2M, not too large, generally in 128~256, such as the use of filesort, can be indexed to solve the problem.

• Thread_stack

  This parameter represents the stack size for each thread, the default is 192KB, and if it is a 64-bit system, set 256, without setting too large.

• Join_buffer_size

  When a join connection operation is made to a table, if the associated field does not have an index, a using Jion buffer appears, in order to improve performance, the available secondary parameters increase the swap size allocated for each thread. The default is 128kb, usually in 128~256. When using the join buffer is generally present, it is resolved by increasing the index.

• Binlog_cache_size

  If the database does not have anything big, the write is not particularly frequent, this 1~2m is a suitable choice, if the thing is large, can increase this value appropriately.

• Max_connections

  Set the maximum number of connections, the default is 100, general settings 512~1000.

Pre_thread_buffers The calculation formula of the memory: (read_buffer_size+read_rnd_buffer_size+sort_buffer_size+thread_stack+join_buffer_ Size+binlog_cache_size) *max_connections

MySQL's my.cnf configuration tuning