Introduction to MySQL Buffer learning and thinking

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 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.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/1930004c8-0.png "border =" 0 "alt =" "/>

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

 
 

  
  
1. max_connections. This parameter indicates the maximum number of connections of MySql. If the number of concurrent connection requests on the server is large, we recommend that you increase this value to increase the number of parallel connections, the server increases according to the actual situation. If the number of connections increases, MySql will provide a connection buffer for each connection, and the memory overhead will increase. Therefore, you need to adjust this value appropriately, you cannot blindly increase the value. You can use mysql-e "show variables like 'max _ connections ';" to view the number of connections in the current status to set this value. This value can be added when you often see the Too worker connections error. The default value is 100.
  
  
 
  
  
2. The back_log parameter is an additional connection based on max_connections. That is to say, when the mysql connection is greater than the value set by max_connections, it is between max_connections + back_log, mysql puts the new connection into the stack and waits for the process of the previous connection to be released. If the current maximum request exceeds max_connections + back_log), the connection is not authorized, of course, this value is also determined by the tcp_max_syn_backlog value of the listening queue system connected by the system's TCP/IP), which can be viewed through cat/proc/sys/net/ipv4/tcp_max_syn_backlog, of course, you can modify this value.
  
  
Sysctl-w net. ipv4.tcp _ max_syn_backlog = N or add tcp_max_syn_backlog = N to/etc/sysctl. conf.
  
  
 
  
  


 
 

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, bytes, innodb_log_buffer_size, query_cache_size, And I am used to calling this method as a local buffer. Although not professional, mysql usually allocates connection buffer 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.

 
 

  
  
Read_buffer_size indicates 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.
  
  
 
  
  
Sort_buffer_size is the buffer size used by MySql to perform sorting. To increase the order by speed, first check whether MySQL can use indexes instead of additional sorting stages. If not, increase the variable sort_buffer_size.
  
  
 
  
  
Read_rnd_buffer_size is the random read buffer size of MySql. When a row is read in any order (for example, in the sorting order), a random read cache is allocated. During sorting query, MySql first scans the buffer to avoid disk search and increase the query speed. If you need to sort a large amount of data, you can increase the value accordingly. However, MySql will issue this buffer space for each client connection. Therefore, set this value as much as possible to avoid excessive memory overhead.
  
  
 
  
  
Tmp_table_size is the heap accumulation of MySql.) the buffer size of the table. All joins are completed in a DML command, and most joins can be completed even without a temporary table. Most temporary tables are memory-based (HEAP) tables. Temporary tables with a large record length (the sum of the lengths of all columns) or tables containing BLOB columns are stored on the hard disk. If the size of a heap table exceeds tmp_table_size, MySQL can automatically change the heap table in memory to hard disk-based MyISAM table as needed. You can also increase the size of the temporary table by setting the tmp_table_size option. That is to say, if you increase this value, MySql will also increase the size of the heap table, which can improve the join query speed.
 
 

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 blog" blog, please be sure to keep this source http://luoweiro.blog.51cto.com/2186161/985887