Mysql Global Shared Memory Introduction _mysql

Objective

Global shared memory is mainly the MySQL Instance (mysqld process) and the underlying storage engine used to hold all kinds of global operations and shareable temporary information, such as query cache to store queries, caching thread cache of connected threads, caching table file handle information Table cache, Cache binary log binlog buffer, cache MyISAM storage engine key buffer and storage InnoDB data and index InnoDB buffer Pool and so on. The following is a simple analysis of MySQL's main shared memory.

Query caching (queries 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. You can increase the corresponding speed of the exact same query statement by storing it in query Cache after a specific Hash calculation of the query statement. When we open the MySQL query cache, MySQL receives each SELECT type of query after the first through the fixed hash algorithm to get the hash value of the query, and then to query cache to find whether there is a corresponding Query 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 followed. After any change in the data of any one table, all query cache related to this table will be invalidated, so query cache is not very useful for tables that change more frequently, but it is appropriate for those with fewer changes, which can greatly improve query efficiency, such as those of static resource tables , configuration tables, and so on. To use query Cache,mysql as efficiently as possible, you designed multiple Query_cache_type values and two query Hint:sql_cache and sql_no_cache for query Cache. When Query_cache_type is set to 0 (or off) without using query cache, when set to 1 (or on), MySQL ignores query cache when and only when Sql_no_cache is used in query. When Query_cache_type is set to 2 (or demand), MySQL uses query cache for this query only after the Sql_cache hint is used in query. You can set the maximum memory space that can be used by query_cache_size.

Connection thread caching (threads cache)

Connection threads are MySQL to increase the efficiency of creating connection threads, keep some of the idle connection threads in a buffer for new incoming connection requests, especially for applications that use short connections, which can greatly improve the efficiency of creating connections. When we set the size of the connection thread that the connection thread cache pool can cache through thread_cache_size, the hit ratio of the connection thread cache can be computed through (connections-threads_created)/connections * 100%. Note that this setting is the number of connection threads that can be cached, not the size of the memory space.

Table Cache

A table buffer is primarily used to cache file handle information for a table file, and the version before MySQL5.1.3 is set by the Table_cache parameter, but from MySQL5.1.3 to Table_open_cache to set its size. When our client program submits query to MySQL, MySQL needs to query for each of the table to obtain a table file handle information, if there is no table Cache, then MySQL has to frequent open close file operations, will undoubtedly be on the system can produce Have a certain impact, the Table Cache is to solve this problem. After the table cache, MySQL each time need to get a table file handle information, first will go to the table cache to find out whether there is an idle state of the table file handle. If there is, then take out the direct use, no words can only be open file operation to obtain file handle information. After use, MySQL puts the file handle information back into the Table cache pool for use by other threads. Note that this setting is the number of table file handle information that can be cached, not the size of the memory space.

Table definition information cache (table definitions cache)

The table definition information cache is a new buffer that is introduced from the MySQL5.1.3 version, and is used to store table definition information. When more tables are used in our MySQL, this caching will undoubtedly increase the efficiency of accessing table-defined information. MySQL provides the Table_definition_cache parameter to set the number of tables that can be cached. In the previous version of MySQL5.1.25, the default value is 128, starting with the MySQL5.1.25 version, the default value is adjusted to 256, and the maximum setting value is 524288. Note that the number of table-defined information that can be cached is set here, not the size of the memory space.

Binary log buffer (Binlog buffer)

Binary log buffers are primarily used to cache Binary log information generated by various data modification exercises. To improve the performance of the system, MySQL does not always write binary logs directly to log file, but instead writes the information to Binlog Buffer and writes to log file again after certain conditions (such as the Sync_binlog parameter setting) are met. We can set the amount of memory it can use 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 settings, MySQL will complain: "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, setting the maximum memory space that can be used by 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 the Binlog Buffer,innodb in writing transaction log, in order to improve performance, it is also first write information into the INNOFB log Buffer, when meeting Innodb_flush_log_trx_commit The log is not written to a file (or synchronized to a disk) until the corresponding condition set by the 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 effect on the write performance of the InnoDB log. This parameter can be set to 0,1,2 and interpreted as follows:
* Data in 0:log buffer will be written to log file at once per second, while simultaneous file system to disk synchronization, but the commit of each transaction does not trigger any log buffer to log file refresh or file system to disk refresh operation ；
* 1: The data in log buffer is written to log file every time a transaction is committed, and the synchronization of the file system to disk is also triggered;
* 2: Transaction commit triggers the refresh of log buffer to log file, but does not trigger synchronization of disk file system to disk. In addition, there will be one file system to disk synchronization operation per second.

In addition, the MySQL documentation mentions that the mechanism for synchronizing each second of these settings may not fully ensure that very accurate synchronization occurs every second, and also depends on the process scheduling problem. In fact, InnoDB can really satisfy the value that this parameter sets represents the normal Recovery is still limited by the file system under different OS and the disk itself, and may sometimes tell Mysqld that the disk synchronization has been completed without actually completing the disk synchronization.

InnoDB Data and index caching (InnoDB buffer Pool)

The effect of the InnoDB buffer pool on the InnoDB storage engine is similar to the impact of the key buffer cache on the MyISAM storage engine, the main difference being that the InnoDB buffer pool not only caches the index data, but also caches the table's data and ends Caching is based on the fast-structured information in the data file, which is very similar to the database buffer cache in the Oracle SGA. Therefore, the InnoDB Buffer Pool on the performance of the InnoDB storage engine can be imagined. can be computed by (innodb_buffer_pool_read_requests-innodb_buffer_pool_reads)/innodb_buffer_pool_read_requests * 100% InnoDB Buffer Pool's hit rate.

InnoDB dictionary information cache (InnoDB Additional Memory Pool)

InnoDB dictionary information caching is mainly used to store the dictionary information of InnoDB storage engine and some internal shared data structure information. So its size is also related to the number of InnoDB storage engine tables used in the system. However, if the amount of memory we set through the Innodb_additional_mem_pool_size parameter is not enough, InnoDB automatically requests more memory and logs the warning message in the MySQL Error Log.

The various shared memory listed here is the main shared memory that I personally think has a greater impact on MySQL performance. In fact, in addition to these shared memory, there are many other shared memory information in MySQL, such as the Back_log queue used to hold the connection request information when too many connections are requested.

The above content may exist the analysis is improper, welcome all friends to pat the brick, exchanges together.