MySQL memory usage-Global Sharing

Next, I wrote an article titled "MySQL memory usage-dedicated Threads", and I wrote an introduction to MySQL global shared memory usage. In global sharing, MySQLInstance (mysqld process) and underlying storage engines are used to store various global operations and shared temporary storage information, such as QueryCache for query cache and Thread for cache connection threads.

Next, I wrote an article titled "MySQL memory usage-dedicated Threads", and I wrote an introduction to MySQL global shared memory usage. In global sharing, MySQL Instance (mysqld process) and underlying storage engine are used to store various global operations and shared temporary storage information, such as Query Cache for Query Cache, cache the Thread of the connection Thread

Next, I wrote an article titled "MySQL memory usage-dedicated Threads", and I wrote an introduction to MySQL global shared memory usage.

In global sharing, MySQL Instance (mysqld process) and underlying storage engine are used to store various global operations and shared temporary storage information, such as Query Cache for Query Cache, cache the Thread Cache of the connection Thread, the Table Cache for caching the Table file handle information, and the BinLog Buffer for caching binary logs, cache Key Buffer of the MyISAM storage engine index Key and InnoDB Buffer Pool that stores InnoDB data and indexes. The following is a simple analysis of MySQL's main shared memory.

Query Cache): Query cache is a unique cache region of MySQL. It is used to cache the Result Set information of a specific Query and share it with all clients. After a specific Hash calculation is performed on a Query statement, it is stored in the Query Cache corresponding to the result set to improve the speed of the same Query statement. After MySQL's Query Cache is enabled, MySQL receives a SELECT Query and obtains the Hash value of the Query through a fixed Hash algorithm, then, go to the Query Cache to check whether there is a corresponding Query Cache. If yes, the Cache result set is directly returned to the client. If no, perform subsequent operations. After obtaining the corresponding result set, Cache the result set to the Query Cache and return it to the client. When the data of any table changes, all Query Cache related to the table will be invalid. Therefore, Query Cache is not very suitable for tables with frequent changes, however, it is very suitable for tables with fewer changes and can greatly improve the query efficiency, such as static resource tables and configuration tables. To use Query Cache as efficiently as possible, MySQL designs multiple query_cache_type values and two Query hints: SQL _CACHE and SQL _NO_CACHE for Query Cache. When query_cache_type is set to 0 (or OFF), Query Cache is not used. When it is set to 1 (or ON, mySQL ignores the Query Cache only when SQL _NO_CACHE is used in the Query. When query_cache_type is set to 2 (or DEMAND, mySQL uses the Query Cache only when the SQL _CACHE prompt is used in the Query. You can use query_cache_size to set the maximum memory space that can be used.

Connection Thread Cache): The connection thread is MySQL. To improve the efficiency of creating connection threads, it keeps some idle connection threads in a cache zone for new connection requests, this can greatly improve the efficiency of creating connections, especially for applications that use transient connections. After we set the size of the connection thread that can be cached by the connection thread cache pool through thread_cache_size, we can calculate the hit rate of the connection thread cache through (Connections-Threads_created)/Connections * 100%. Note that the number of connection threads that can be cached is set here, rather than the memory size.

Table Cache): The table cache area is mainly used to cache the file handle information of table files. In versions earlier than MySQL 5.1.3, The table_cache parameter is used to set the file handle information, but the table size is changed from MySQL5.1.3 to table_open_cache. When our client program submits a Query to MySQL, MySQL needs to obtain a Table file handle information for each Table involved in the Query. If there is no Table Cache, therefore, MySQL has to open and close files frequently, which will undoubtedly affect the system performance. Table Cache is generated to solve this problem. When MySQL needs to obtain the handle information of a Table file after Table Cache is available, it first goes to Table Cache to check whether there are idle Table file handles. If yes, it is used directly. If no, you can only open the file to obtain the file handle information. After use, MySQL puts the file handle information back in the Table Cache pool for other threads to use. Note that the number of table file handle information that can be cached is set here, rather than the size of memory space.

Table definition Cache): Table definition information cache is a new cache zone introduced from MySQL 5.1.3 to store table definition information. When many tables are used in MySQL, this cache will undoubtedly improve the access efficiency of table definition information. MySQL provides the table_definition_cache parameter to set the number of tables that can be cached. In versions earlier than MySQL5.1.25, the default value is 128. From MySQL5.1.25, the default value is adjusted to 256. The maximum value is 524288. Note that the number of table definitions that can be cached is set here, rather than the size of memory space.

Binary log Buffer): The Binary Log buffer is mainly used to cache Binary Log information generated by various data changes. To improve system performance, MySQL does not directly write binary logs to Log files every time, but first writes information to Binlog Buffer. when certain conditions are met (such as sync_binlog parameter settings) and then write it into the Log File again. We can use binlog_cache_size to set the memory size that can be used, and use max_binlog_cache_size to limit its maximum size (MySQL will apply for more memory when a single transaction is too large ). When the required memory is greater than the max_binlog_cache_size parameter, MySQL reports the error "Multi-statement transaction required more than 'max _ binlog_cache_size 'bytes of storage ".

MyISAM index cache (Key Buffer): MyISAM index cache caches the index information of the MyISAM table in the memory to improve its access performance. This cache is one of the most important factors affecting the performance of the MyISAM storage engine. You can use key_buffere_size to set the maximum memory space that can be used.

InnoDB Log Buffer): This is the buffer used by the transaction logs of the InnoDB Storage engine. Similar to Binlog Buffer, InnoDB writes information to Innofb Log Buffer to improve performance when writing transaction logs, when the corresponding conditions set by the innodb_flush_log_trx_commit parameter are met (or the log buffer is full), the log will be written to a file (or synchronized to the disk. You can use the innodb_log_buffer_size parameter to set the maximum memory space that can be used.
Note: The innodb_flush_log_trx_commit parameter has a critical impact on the InnoDB Log write performance. This parameter can be set to 0, 1, and 2. The explanation is as follows:

• 0: Data in the log buffer is written to the log file at a frequency every second, and the file system is synchronized to the disk at the same time, however, the commit of each transaction does not trigger any refresh from log buffer to log file or from the file system to disk;

• 1: Data in the log buffer will be written to the log file during each transaction commit, and synchronization from the file system to the disk will also be triggered;

• 2: transaction commit triggers refresh from log buffer to log file, but does not trigger Disk file System to disk synchronization. In addition, a file system is synchronized to the disk every second.

In addition, the MySQL documentation also mentions that the mechanism for synchronizing these settings once per second may not completely ensure that synchronization will occur every second very accurately, but also depends on the process scheduling problem. In fact, whether InnoDB can really meet the meaning of the value set by this parameter indicates that normal Recovery is still restricted by file systems and disks in different operating systems, sometimes, if the Disk Synchronization is not completed, mysqld is also notified that the disk synchronization has been completed.

InnoDB data and index cache (InnoDB Buffer Pool): The Role of InnoDB Buffer Pool on the InnoDB Storage engine is similar to that of the Key Buffer Cache on the MyISAM storage engine. The main difference is that the InnoDB Buffer Pool not only caches index data, but also caches table data, the cache is based entirely on the fast data structure information in the data file, which is very similar to the database buffer cache in Oracle SGA. Therefore, the InnoDB Buffer Pool has a big impact on the performance of the InnoDB Storage engine. You can use (Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests * 100% to calculate the InnoDB Buffer Pool hit rate.

InnoDB dictionary information cache (InnoDB Additional Memory Pool): InnoDB dictionary information cache is mainly used to store dictionary information of the InnoDB Storage engine and some internal shared data structure information. Therefore, the size of the table depends on the number of InnoDB Storage engine tables used in the system. However, if the memory size we set through the innodb_additional_mem_pool_size parameter is not enough, InnoDB will automatically apply for more memory and record warning information in the MySQL Error Log.

The various shared memories listed here are the main shared memory that I personally think has a great impact on MySQL performance. In fact, in addition to the shared memory, MySQL also has many other shared memory information, such as the back_log queue used to store connection request information when too many connections are requested at the same time.

The above content may have some improper analysis. You are welcome to make a discussion.

Original article address: MySQL memory usage-Global Sharing, thanks to the original author for sharing.