MySQLBuffer learning and consideration

This blog post aims to read and understand your own books and use this picture to explain. If there are any mistakes, I hope you can correct them and share them with us for the purpose of mutual discussion. First, let's introduce the source of this figure: I recently read some books about mysql.

This blog post aims to read and understand your own books and use this picture to explain. If there are any mistakes, I hope you can correct them and share them with us for the purpose of mutual discussion. First, let's introduce the source of this figure: I recently read some books about mysql.

This blog post aims to read and understand your own books and use this picture to illustrate the website space. If there are any mistakes, I hope you can correct them and share them with us for the purpose of mutual discussion.

First of all, let's introduce the source of this figure: I recently read some experiences on mysql books and converted the text into images for ease of understanding.

Then, we will give a brief introduction to the above parameters:

Because mysql is called a memory-based database, of course the memory is inseparable, and its relationship with the memory is mainly through several parameters of the buffer size.

Buffering includes global buffering and partial buffering.

The global buffer parameters are roughly as follows:

Key_buffer_size, innodb_buffer_pool_size, innodb_additional_mem_pool_size, Hong Kong Space, innodb_log_buffer_size, query_cache_size

However, partial buffering (I am used to calling it this way, although it is not professional, haha) in general, mysql also allocates connection buffering for each connection.

Global buffer is introduced in the previous blog, which is omitted.

Local cache:

Each thread connected to the MySQL server must have its own buffer. About 256 kb need to be allocated immediately, and even when the thread is idle, they use the default thread stack and network cache. After the transaction starts, more space is required. Running a small query may only add a small amount of memory consumption to the specified thread. However, if you perform complex operations on the data table, such as scanning, sorting, or temporary tables, you need to allocate about read_buffer_size, sort_buffer_size, read_rnd_buffer_size, and tmp_table_size memory space. However, they are allocated only when needed and released after those operations are completed. Some are allocated to separate blocks immediately. Tmp_table_size may be as high as the maximum memory space that MySQL can allocate to this operation. Note: there is more than one thing to consider here-multiple caches of the same type may be allocated, for example, used to process subqueries. Memory usage for some special queries may be larger-If bulk_insert_buffer_size memory needs to be allocated for batch inserts on the MyISAM TABLE; execute alter table, optimize table, the memory size of myisam_sort_buffer_size needs to be allocated during the repair table command.

The above is based on commonly used parameters and the mysql manual summary. Easy to learn and understand. All my personal opinions. If you have any questions, you can share and learn together!

This article is from the "Ro NLP blog". Please keep this source

, Website Space