Mysql read_buffer_size: readbuffersize

Mysql read_buffer_size: readbuffersize

Key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = 458624 K

Read_buffer_size: the buffer size read by MySQL. Requests that perform sequential scans on the table will be allocated with a read buffer, and MySQL will allocate it with a memory buffer. The read_buffer_size variable controls the size of the buffer. If you want to scan the table in a very frequent order and think that frequent scanning is too slow, you can increase the performance by increasing the variable value and memory buffer size.

The following is the configuration of 16 GB memory:

Read_buffer_size = 1 M

The personal machine is 32 GB. Generally, databases are used to store data, and mysql is rarely used, so I set it to 2 M or 3 M.

MySQL database server parameter optimization mycnf, 16 GB memory 8-core CPU

Business scenario: the backend supports online mobile phone update systems, with 16 GB db Server Memory, 8 cores, and dell pc servers.

Qps: around 200
Tps: 1 minute or so

Sort_buffer_size = 32 M, 8 M
Read_buffer_size = 32 M, 8 M
Read_rnd_buffer_size = 16 M, 8 M
Table_open_cache = 512
Small. We recommend that you change it to 2048.

Max_allowed_packet = 5 M
Small, it is recommended to change to 16 M

Tmp_table_size = 64 M
Small, it is recommended to change to 2G

Innodb_buffer_pool_size = 3000 M
Small, changed to 60% to 80% of the total memory of the db Server

Innodb_additional_mem_pool_size = 20 M smaller, changed to 128 M

The join_buffer_size parameter is not displayed. The value must be set. join_buffer_size = 8 M must be added. This parameter is associated with the join table and is very important.