Global shared memory is mainly used by the MySQL Instance (mysqld process) and the underlying storage engine to temporarily store various global computation data and sharable temporary information. Examples include the Query Cache for storing query results, the Thread Cache for caching connection threads, the Table Cache for caching table file handle information, the BinLog Buffer for caching binary logs, the Key Buffer for caching MyISAM storage engine index keys, and the InnoDB Buffer Pool for storing InnoDB data and indexes, among others. Below, we provide a simple analysis of the main shared memory areas in MySQL.
**Query Cache**: The Query Cache is a unique cache area in MySQL that stores result set information for specific queries and shares it with all clients. By performing a specific hash calculation on the query statement, it is matched and stored with its corresponding result set in the Query Cache to improve the response speed of identical queries. When the Query Cache is enabled in MySQL, each SELECT-type query received by MySQL will first undergo a fixed hash algorithm to obtain the query's hash value, then check if there is a corresponding entry in the Query Cache. If so, the cached result set is directly returned to the client. If not, subsequent operations are performed, and once the corresponding result set is obtained, it is cached in the Query Cache before being returned to the client. Whenever any data in a table changes, all Query Caches related to that table become invalid. Therefore, the Query Cache is not very suitable for frequently updated tables but is highly effective for static or rarely updated tables, such as configuration tables. To use the Query Cache as efficiently as possible, MySQL provides multiple `query_cache_type` values and two Query Hints: `SQL_CACHE` and `SQL_NO_CACHE`. When `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 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` hint is used in the query. The maximum available memory space can be set through `query_cache_size`.
**Thread Cache**: The Thread Cache is used by MySQL to improve the efficiency of creating connection threads by keeping some idle connection threads in a cache pool for new incoming connection requests, which is especially beneficial for applications using short-lived connections. After setting the size of the connection thread cache pool via `thread_cache_size`, the hit rate of the connection thread cache can be calculated using `(Connections - Threads_created) / Connections * 100%`. Note that this setting refers to the number of connection threads that can be cached, not the size of memory space.
**Table Cache**: The Table Cache primarily caches table file handle information. Before MySQL 5.1.3, this was configured via the `table_cache` parameter, but from MySQL 5.1.3 onwards, it is set via `table_open_cache`. When a client program submits a query to MySQL, MySQL needs to retrieve a file handle for every table involved in the query. Without a Table Cache, MySQL would have to repeatedly open and close files, which would negatively impact system performance. The Table Cache addresses this issue. With the Table Cache in place, whenever MySQL needs to retrieve a file handle for a specific table, it first checks if there is an idle file handle in the Table Cache. If one exists, it retrieves and uses it; otherwise, it opens the file to get the file handle. After use, MySQL returns the file handle to the Table Cache pool for other threads to use. Note that this setting refers to the number of table file handles that can be cached, not the size of memory space.
**Table Definition Cache**: Introduced in MySQL 5.1.3, the Table Definition Cache stores table definition information. When MySQL involves many tables, this cache improves access efficiency to table definition information. The number of tables that can be cached is set via the `table_definition_cache` parameter. In versions prior to MySQL 5.1.25, the default value was 128, but from MySQL 5.1.25 onwards, the default value was increased to 256, with a maximum setting of 524288. Note that this setting refers to the number of table definitions that can be cached, not the size of memory space.
**Binlog Buffer**: The Binlog Buffer primarily caches Binary Log information generated by various data change operations. To enhance system performance, MySQL does not directly write binary logs to log files but first writes them to the Binlog Buffer, and then writes them to the log file when certain conditions (such as those set by the `sync_binlog` parameter) are met. The usable memory size can be set via `binlog_cache_size`, and the maximum size can be limited via `max_binlog_cache_size` (MySQL will request more memory if a single transaction is too large). If the required memory exceeds the value set by `max_binlog_cache_size`, MySQL will throw an error: "Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage."
**MyISAM Index Cache (Key Buffer)**: The MyISAM Index Cache stores index information for MyISAM tables in memory to improve access performance. This cache is one of the most important factors affecting the performance of the MyISAM storage engine, and its maximum usable memory space can be set via `key_buffer_size`.
**InnoDB Log Buffer**: This buffer is used by the transaction log of the InnoDB storage engine. Similar to the Binlog Buffer, InnoDB writes transaction logs to the Innodb Log Buffer to enhance performance, and only writes logs to the file (or synchronizes to disk) when the conditions set by the `innodb_flush_log_trx_commit` parameter are met (or when the log buffer is full). Its maximum usable memory space can be set via the `innodb_log_buffer_size` parameter.
Note: The `innodb_flush_log_trx_commit` parameter significantly affects the write performance of InnoDB Logs. It can be set to 0, 1, or 2:
- **0**: Data in the log buffer is written to the log file once per second, and the file system is synchronized to the disk at the same time. However, each transaction commit does not trigger any refresh of the log buffer to the log file or synchronization of the file system to the disk.
- **1**: Each transaction commit writes data from the log buffer to the log file and triggers synchronization of the file system to the disk.
- **2**: Each transaction commit refreshes the log buffer to the log file but does not trigger synchronization of the file system to the disk. Additionally, there is a file system to disk synchronization once per second.
The MySQL documentation also mentions that the mechanism of syncing once per second may not always ensure that synchronization occurs exactly every second due to process scheduling issues. In reality, whether InnoDB truly satisfies the meaning of the set parameter value for normal recovery still depends on the file system under different operating systems and the limitations of the disk itself. Sometimes, mysqld might be told that the disk sync is complete even though it hasn't actually been completed.
**InnoDB Buffer Pool**: The InnoDB Buffer Pool plays a role similar to the Key Buffer Cache for the MyISAM storage engine but differs in that it not only caches index data but also table data, and it caches data according to the structure of the data blocks in the data file, much like the database buffer cache in Oracle SGA. Thus, the impact of the InnoDB Buffer Pool on the performance of the InnoDB storage engine is significant. The hit rate of the InnoDB Buffer Pool can be calculated using `(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%`.
**InnoDB Dictionary Information Cache (InnoDB Additional Memory Pool)**: The InnoDB Dictionary Information Cache primarily stores dictionary information and internal shared data structures for the InnoDB storage engine. Its size is closely related to the number of InnoDB tables used in the system. However, if the memory size set via the `innodb_additional_mem_pool_size` parameter is insufficient, InnoDB will automatically request more memory and record warning information in MySQL's Error Log.
The various shared memories listed here are what I personally consider to be the main shared memories that significantly impact MySQL performance. In fact, apart from these shared memories, MySQL has many other shared memory information, such as the back_log queue used to store connection request information when too many simultaneous connection requests occur.
Article Link: 【Eden Network】http://www.edenw.com/tech/devdeloper/database/2010-07-20/4824.html