MySQL InnoDB engine memory allocation entries

MySQL is the most popular open source database, InnoDB in 5.6 has become the default engine, in 5.7 system bar engine also has myisam to InnoDB, you can see that InnoDB engine now can meet most of the business needs, memory allocation directly affect the overall efficiency of MySQL, there are innodb_ Buffer, there are various cache_size, these options need to be reasonable allocation, reasonable use of server limited memory to let MySQL run more hi-skin, the direct impact of the options are the following:

Innodb_buffer_pool_size: Buffer, storage table data, index data, size is critical, directly affect the SQL execution IO overhead, if the buffer is large enough to cache the following table all the data, in memory using the hash list to find the way is quite fast

Innodb_additional_mem_pool_size: Data dictionary store, open a table corresponding table dictionary information is stored in this area, when the more data table, the greater the space for the demand, a table occupies about 4K space, this option has been removed in 5.7

Innodb_log_buffer_size:innodb thing log Cache area size

Binlog_cache_size:binlog Cache Area Size

Session level Parameters:

Join_buffer_size: The size of the area where the link key is stored when using block nesting loops

Sort_buffer_size: Sort key storage Area size

Net_buffer_length: The client connects the buffer that holds the session information and dynamically grows to the size of the Max_allowed_packet setting when needed

Max_allowed_packet: Limit the maximum size of a data table for a session

Query cache query_cache_size can be based on business scenarios to determine whether to open, query caching mechanism is the same data need the same SQL to hit, when the database update, delete, insert comparison is not appropriate to use the query cache, as long as the data changes will be invalidated , the failure operation will hold a global lock on the query buffer, which will block other SQL matching, and a SQL after the Connection manager, whether or not the query cache matches will be matched to the query cache once, which will also reduce the efficiency of SQL execution.

Memory allocation According to the real business situation simulation test allocation, session level parameters each link is allocated a memory area, must be tested to not waste too much memory.

The written parameters are InnoDB very important memory configuration parameters, MyISAM because it has been phased out, do not read too much.

This article is from the "Small ops" blog, please make sure to keep this source http://2364914.blog.51cto.com/2354914/1754860

MySQL InnoDB engine memory allocation entries