mysql read_buffer_size Set what is appropriate

  • 2021-01-06 00:45:39
  • OfStack

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

read_buffer_size: Is the size of the MySQL read buffer. A request to perform a sequential scan of a table will be allocated a read buffer, and MySQL will allocate a memory buffer. The read_buffer_size variable controls the size of this 1 buffer. If sequential scan requests for the table are very frequent and you think frequent scans are too slow, you can improve performance by increasing the value of this variable and the size of the memory buffer.

Here is the setup for 16g memory

read_buffer_size = 1M

The personal machine is 32G, and a general database is used to store data. mysql is rarely used, so I set it to 2M or 3M

Mobile phone online update system MySQL database server parameter optimization mycnf,16G memory 8 core CPU

Business scenario: Backstage support mobile phone online update system, db server memory 16G, 8 cores, dell pc server.

qps: About 200
tps: About 50 per minute

sort_buffer_size = 32M 8M
read_buffer_size = 32M 8M
read_rnd_buffer_size = 16M (8M
table_open_cache = 512
It's too small. I suggest you change it to 2048

max_allowed_packet = 5M
It is too small. Suggest change to 16M

It is too small. Suggest to change it to 2G

innodb_buffer_pool_size = 3000M
Smaller, to 60 to 80 percent of the total memory of the db server

innodb_additional_mem_pool_size = 20M (128M

join_buffer_size = 8M; join_buffer_size = 8M;

_size = 8M; join_buffer_size = 8M; join_buffer_size = 8M;

Related articles: