Shulou(Shulou.com)05/31 Report--

How to understand MySQL per_thread_buffers optimization, for this problem, this article details the corresponding analysis and solution, hoping to help more small partners who want to solve this problem find a simpler and easier way.

For per_thread_buffers, which can be understood as PGA of Oracle, the memory allocated for each user process connected to MySQL includes the following parameters:

1、read_buffer_size

This parameter is used to describe the order of the table and indicates the size of the buffer allocated by each thread. For example, when performing a full table scan, MySQL will read the data blocks in turn according to the storage order of the data. The data blocks read each time will first be temporarily stored in per_thread_buffers. When the buffer space is full or all data reads are completed, the data in the buffer will be returned to the upper caller to improve efficiency. The default is 128KB, generally between 128KB and 256KB.

2、read_rnd_buffer_size

This parameter is read randomly from the user table and indicates the buffer size allocated per thread. For example, when ordering by a non-index field, this buffer is used to temporarily store the read data. The default is 256KB, typically 128KB-256KB.

3、sort_buffer_size

When sorting tables by order and group, since the sorted fields have no index, using filesort will appear. To improve performance, this parameter can be used to increase the buffer size allocated by each thread. The default is 2MB, not too large, generally 128KB-256KB.

4、thread_stack

This parameter represents the stack size for each thread. The default is 182KB, and the 64 system can be set to 256KB.

5、join_buffer_size

Table join operation, if the associated field has no index, will appear using join buffer, in order to improve performance, you can use this parameter to increase the buffer size allocated per thread. The default is 128KB. Generally 128KB-256KB, when using join buffer, add index to solve.

6、binlog_cache_size

There are no large transactions in the database, and writes are not particularly frequent. Set it to 1-2MB. If there are large transactions, you can increase this cache value appropriately to obtain better performance.

7、max_connections

Maximum number of connections, default is 100, generally set to 512-1000.

About how to understand MySQL per_thread_buffers optimization questions to share here, I hope the above content can be of some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.