MySQL database shared memory and cache introduction

Reprint to Http://www.server110.com/mysql/201311/2991.html

The global shared memory is mostly MySQL Instance (MySQLD process) and the underlying storage engine is used to stage various global operations and shareable staging information, such as query cache to store queries cached, thread cache for the connection thread, table cache that caches the file handle information for tables, cache binary log BinLog Buffer, cache the key buffer for the MyISAM Storage Engine index key, and the InnoDB buffer Pool that stores InnoDB data and indexes, and so on. The following is a simple analysis of MySQL's main shared memory.

Query cache:
The query cache is a unique cache area for MySQL that caches the result set information for a particular query and shares it with all clients. By making a specific Hash calculation of the query statement, it is stored in the query Cache with the result set to increase the corresponding speed of the exact same query statement. When we open the MySQL query cache, MySQL receives each SELECT type of query after the query will be a fixed hash algorithm to get the hash value, and then to query Cache to find if there is a corresponding q Uery Cache. If so, the result set of the cache is returned directly to the client. If not, the result set is cached to the Query cache and returned to the client after the corresponding result set is obtained. When any table data changes, all the query caches associated with that table are invalidated, so querycache is not very useful for tables that change more frequently, but is well suited for tables with fewer changes, which can greatly improve query efficiency, such as those of static resource tables , configuration tables, and so on. To make the most efficient use of query Cache,mysql, a number of query_cache_type values and two Queryhint:sql_cache and Sql_no_cache are designed for query Cache. When the Query_cache_type is set to 0 (or OFF), the query cache is not used, when set to 1 (or on), MySQL ignores the query cache when the Sql_no_cache is used in query, and when Q When the Uery_cache_type is set to 2 (or demand), MySQL uses query cache for that query only when the Sql_cache hint is used in query. You can use Query_cache_size to set the maximum memory space that can be used.

Connection thread caching (thread cache):
Connection thread is MySQL in order to improve the efficiency of creating connection threads, keep some idle connection threads in one buffer for new incoming connection requests, which can greatly improve the efficiency of creating a connection for applications that use short connections. After we set the size of the connection thread that the connection thread cache pool can cache through Thread_cache_size, you can calculate the hit ratio of the connection thread cache by (connections-threads_created)/Connections * 100%. Note that this is set to the number of connection threads that can be cached, not the size of the memory space.

Tables cache (table caches):
Table buffers are primarily used to cache file handle information for table files, and versions prior to MySQL5.1.3 are set by the Table_cache parameter, but are changed from MySQL5.1.3 to Table_open_cache to set their size. When our client program submits a query to MySQL, MySQL needs to get a table file handle information for each table involved in query, and if there is no table Cache, then MySQL has to open the close file operation frequently, Will undoubtedly have a certain impact on system performance, Table Cache is to solve this problem. After the table cache is available, MySQL will first look in the table cache to find out if there are any tables file handles for an idle state when it needs to fetch the handle information of the file. If there is, then remove the direct use, if not, you can only open the file operation to obtain file handle information. After use, MySQL will then put the file handle information back into the Table cache pool for other threads to use. Note that this is set to the number of table file handle information that can be cached, not the size of the memory space.

Table definition information cache (table definition Cache):
The table definition information cache is a new buffer that is introduced from the MySQL5.1.3 version to hold table definition information. When more tables are used in MySQL, this cache will undoubtedly increase the efficiency of access to table-defined information. MySQL provides the Table_definition_cache parameter to let us set the number of tables that can be cached. In versions prior to MySQL5.1.25, the default value was 128, starting with the MySQL5.1.25 version, the default value was adjusted to 256, and the maximum setting was 524288. Note that this is set to the number of table definition information that can be cached, not the size of the memory space.

Binary log buffers (Binlogbuffer):
Binary log buffers are primarily used to cache binary logs generated by various data change operations. To improve the performance of the system, MySQL does not always write the binary log directly to the log file, but instead writes the information to the Binlogbuffer first, and then writes to the log file once the specific conditions (such as the Sync_binlog parameter settings) are met. We can set the amount of memory that can be used by binlog_cache_size and limit its maximum size by max_binlog_cache_size (MySQL will request more memory when a single transaction is too Large). When the required memory is greater than the Max_binlog_cache_size parameter setting, MySQL will error: "Multi-statement transaction required more than ' max_binlog_cache_ Size ' bytes of storage '.

MyISAM Index Cache (Key buffer):The MyISAM index cache caches the index information of the MyISAM table in memory to improve its access performance. This cache can be said to be one of the most important factors affecting the performance of the MyISAM storage engine by setting the maximum memory space that can be used with key_buffere_size.

InnoDB log buffers (InnoDB log buffer):
This is the buffer used by the transaction log of the InnoDB storage engine. Similar to Binlog Buffer,innodb when writing transaction logs, in order to improve performance, the information is first written to the INNOFB log Buffer, when it satisfies the innodb_flush_log_trx_ The log is written to a file (or to a disk) after the corresponding condition set by the commit parameter (or the log buffer is full). You can set the maximum memory space that can be used by the Innodb_log_buffer_size parameter.
Note: The Innodb_flush_log_trx_commit parameter has a very critical impact on the write performance of InnoDB log. This parameter can be set to 0,1,2, as explained below:
The data in the 0:log buffer is written to logfile at the frequency of every second, and the file system-to-disk synchronization is performed, but the commit of each transaction does not trigger any log buffer-to-logfile refreshes or file system-to-disk refresh operations.
1: The data in log buffer will be written to log file each time the transaction commits, and the file system to disk synchronization will also be triggered;
2: Transaction commit triggers a flush of log buffer to logfile, but does not trigger a disk file system-to-disk synchronization. In addition, there is a file system-to-disk synchronization operation every second.
In addition, the MySQL documentation mentions that the mechanism of synchronizing each second in these settings may not completely ensure that a very accurate synchronization occurs every second and depends on the process scheduling problem. In fact, whether InnoDB can really meet the value set by this parameter means normal Recovery is still under the limitations of the file system and the disk itself in the different OS, there may be times when the disk synchronization is not actually complete and will tell Mysqld that the disk synchronization has been completed.

InnoDB Data and Index cache (InnoDB buffer Pool):
Innodbbuffer Pool's role with the InnoDB storage engine is similar to the impact of the key Buffer cache on the MyISAM storage engine, except that the InnoDB buffer pool caches not only the index data, but also the table's data, and is exactly as Data files, which are very similar to the database buffer cache in the Oracle SGA. So the InnoDB Buffer Pool has a great impact on the performance of the InnoDB storage engine. can be calculated by (innodb_buffer_pool_read_requests-innodb_buffer_pool_reads)/innodb_buffer_pool_read_requests * 100% InnoDB the hit rate of the Buffer Pool.

InnoDB dictionary information cache (InnoDB Additional memorypool):
InnoDB dictionary information cache is mainly used to store the dictionary information of InnoDB storage engine and some internal shared data structure information. So its size is also more related to the number of InnoDB storage engine tables used in the system. However, if the amount of memory that we set through the Innodb_additional_mem_pool_size parameter is not enough, InnoDB will automatically request more memory and log the warning message in MySQL's Error log.
The various shared memory listed here is the main shared memory that I personally think has a significant impact on MySQL performance. In fact, in addition to these shared memory, MySQL also has a lot of other shared memory information, such as when the simultaneous request for too many connections to hold the connection request information Back_log queue.

MySQL database shared memory and cache introduction