MySQL cache mechanism (1) and mysql Cache Mechanism

MySQL cache mechanism (1) and mysql Cache Mechanism

 

This article used to learn | reference: http://www.2cto.com/database/201308/236361.html

Understanding and summary of MySql Query Cache and SQL Server process Cache I. MySql Query Cache 1. Query Cache MySQL Query Cache is used to Cache the SELECT statement we executed and the result of this statement set. The specific technical details of MySql in implementing Query Cache are similar to the typical KV storage, it is to map the SELECT statement and the query statement result set to a HASH and save it in a certain memory area. When the client initiates an SQL Query, the Query Cache search logic is to first verify the SQL permission and then Query the results through the Query Cache. It does not need to go through the Optimizer module to analyze and optimize the execution plan, and does not need to interact with any storage engine. It reduces a large amount of disk I/O and CPU operations, so sometimes the efficiency is very high. 2. Query Cache setting parameters can be enabled and set by adjusting MySQL parameters. The Query Cache function mainly includes the following five parameters: (1) and query_cache_limit: maximum capacity of a single query result set that can be cached. The default value is 1 MB. The query result set that exceeds this parameter is not cached. (2), query_cache_min_res_unit: set the minimum size of memory allocated by Query Cache for each Query, that is, the Minimum Memory occupied by the Cache for each Query. (3) query_cache_size: Set the memory used by Query Cache, the default value is 0, and the size must be an integer multiple of 1024. If it is not an integer multiple, MySQL will automatically adjust and reduce the minimum quantity to a multiple of 1024. (4), query_cache_type: you can set the Query Cache function to 0, 1, and 2, with the following meanings: a and 0 (OFF): Disable Query Cach. E function. Query Cache is not used in any case. B, 1 (ON): the Query Cache function is enabled. However, when SQL _NO_CACHE is used in SELECT statements, Query Cache is not used; c, 2 (DEMAND): Enable the Query Cache function, but the Query Cache is used only when the SQL _CACHE prompt is used in the SELECT statement. (5) query_cache_wlock_invalidate: determines whether to invalidate the Query Cache related to the table when a write lock occurs on the table. If it is set to 1 (TRUE ), when the write lock is performed, all Query Cache related to the table will be invalidated. If it is set to 0 (FALSE), the Query Cache related to the table can still be read at the lock time. 3. the Query Cache and performance are too slow. Especially for systems with frequent writes, enabling the Query Cache function may not improve the system performance, but may sometimes decrease the performance. The reason is that to ensure that the content cached by the Query Cache is absolutely consistent with the actual data, when a data table is updated, deleted, and inserted, mySql forcibly invalidates all Query Cache statements referenced in the table. For intensive write operations, enabling the query cache may cause frequent cache failures, which indirectly leads to memory spikes and CPU spikes. This is a great burden on busy database systems. 4. The implementation of other Query caches varies slightly with the MySql storage engine. For example, MyISAM stores Cache result sets in OS Cache, while the most popular InnoDB stores in Buffer Pool. Ii. SQL Server's Procedure Cache SQL Server does not have a Query Cache mechanism similar to MySql, but it has its own Cache mechanism. SQL Server does not directly cache the SQL query result set, but caches the queried Data pages (Data Buffer) It has read ), it also caches execution plans (process Cache Procedure Cache). Next we will talk about the well-known process Cache. 1. During SQL Execution, the SQL statement needs to be compiled before execution, and then needs to be optimized through the SQL Server query engine to obtain the optimized execution plan, finally, the SQL statement is executed according to the execution plan. 2. Process Cache execution plan creation takes up CPU resources. When an execution plan is created, the SQL Server query engine automatically caches the execution plan by default. For SQL statements with different parameters, SQL Server can reuse the cache execution plan. However, for different SQL statements, SQL Server cannot reuse the previous execution plan, but needs to re-compile a new execution plan because the SQL Server query engine automatically caches the execution plan, each new execution plan occupies the memory of SQL Server. When SQL Server has enough memory available, the query engine does not actively clear previously saved query plans. Therefore, in some cases, a similar SQL statement only has many execution plans out of thin air because of different writing methods. For similar SQL statements, these redundant execution plans occupy the memory, the number of query plans cached in SQL Server is greatly affected. In this case, if the maximum available memory of SQL Server is limited, it will reduce the available memory of SQL Server, therefore, more memory pages are exchanged with the disk during query execution, especially for large data queries. If the maximum available memory is not set, the SQL Server caches too many execution plans, so that the memory usage is too large. 3. How to reduce the usage of process cache is mainly through parameterized query. The key to parameterized query is that the query optimizer will create a reusable cache plan (the SQL Server Query Optimizer will rewrite the query into a parameterized SQL statement ), this reusable cache plan eliminates the need to create a cache plan for each execution of these SQL statements. By creating a reusable plan, SQL Server reduces the memory usage required to store similar execution plans. For developers, we can use the following two methods to achieve parameterized query: (1) Execute SQL statements using stored procedures; (2) Execute SQL statements using sp_executesql. Concerning the use of stored procedures to execute SQL statements, another question: there has been a lot of controversy over stored procedures. For example, The ORM team believes that stored procedures are completely process-oriented and difficult to expand and maintain. Based on my personal development experience, I suggest using more simple and almost no logic stored procedures, but complex stored procedures have always been BUG camps, in addition, the maintenance cost is extremely high in the future (as our architect said, the database of an important business system has a storage process of more than eight thousand rows and more than two hundred variables, no one dares to change ), it is best to write the logic through the business logic layer to cope with drastic changes. Now we have a mature ORM and layers. In development, we must avoid writing too long and logically complex stored procedures. Otherwise, we will face changes, it is not impossible to see several eight thousand rows of stored procedures over time.

 

Understanding and summary of MySql Query Cache and SQL Server process Cache I. MySql Query Cache 1. Query Cache MySQL Query Cache is used to Cache the SELECT statement we executed and the result of this statement set. The specific technical details of MySql in implementing Query Cache are similar to the typical KV storage, it is to map the SELECT statement and the query statement result set to a HASH and save it in a certain memory area. When the client initiates an SQL Query, the Query Cache search logic is to first verify the SQL permission and then Query the results through the Query Cache. It does not need to go through the Optimizer module to analyze and optimize the execution plan, and does not need to interact with any storage engine. It reduces a large amount of disk I/O and CPU operations, so sometimes the efficiency is very high. 2. Query Cache setting parameters can be enabled and set by adjusting MySQL parameters. The Query Cache function mainly includes the following five parameters: (1) and query_cache_limit: maximum capacity of a single query result set that can be cached. The default value is 1 MB. The query result set that exceeds this parameter is not cached. (2), query_cache_min_res_unit: set the minimum size of memory allocated by Query Cache for each Query, that is, the Minimum Memory occupied by the Cache for each Query. (3) query_cache_size: Set the memory used by Query Cache, the default value is 0, and the size must be an integer multiple of 1024. If it is not an integer multiple, MySQL will automatically adjust and reduce the minimum quantity to a multiple of 1024. (4), query_cache_type: you can set the Query Cache function to 0, 1, and 2, with the following meanings: a and 0 (OFF): Disable Query Cach. E function. Query Cache is not used in any case. B, 1 (ON): the Query Cache function is enabled. However, when SQL _NO_CACHE is used in SELECT statements, Query Cache is not used; c, 2 (DEMAND): Enable the Query Cache function, but the Query Cache is used only when the SQL _CACHE prompt is used in the SELECT statement. (5) query_cache_wlock_invalidate: determines whether to invalidate the Query Cache related to the table when a write lock occurs on the table. If it is set to 1 (TRUE ), when the write lock is performed, all Query Cache related to the table will be invalidated. If it is set to 0 (FALSE), the Query Cache related to the table can still be read at the lock time. 3. the Query Cache and performance are too slow. Especially for systems with frequent writes, enabling the Query Cache function may not improve the system performance, but may sometimes decrease the performance. The reason is that to ensure that the content cached by the Query Cache is absolutely consistent with the actual data, when a data table is updated, deleted, and inserted, mySql forcibly invalidates all Query Cache statements referenced in the table. For intensive write operations, enabling the query cache may cause frequent cache failures, which indirectly leads to memory spikes and CPU spikes. This is a great burden on busy database systems. 4. The implementation of other Query caches varies slightly with the MySql storage engine. For example, MyISAM stores Cache result sets in OS Cache, while the most popular InnoDB stores in Buffer Pool. Ii. SQL Server's Procedure Cache SQL Server does not have a Query Cache mechanism similar to MySql, but it has its own Cache mechanism. SQL Server does not directly cache the SQL query result set, but caches the queried Data pages (Data Buffer) It has read ), it also caches execution plans (process Cache Procedure Cache). Next we will talk about the well-known process Cache. 1. During SQL Execution, the SQL statement needs to be compiled before execution, and then needs to be optimized through the SQL Server query engine to obtain the optimized execution plan, finally, the SQL statement is executed according to the execution plan. 2. Process Cache execution plan creation takes up CPU resources. When an execution plan is created, the SQL Server query engine automatically caches the execution plan by default. For SQL statements with different parameters, SQL Server can reuse the cache execution plan. However, for different SQL statements, SQL Server cannot reuse the previous execution plan, but needs to re-compile a new execution plan because the SQL Server query engine automatically caches the execution plan, each new execution plan occupies the memory of SQL Server. When SQL Server has enough memory available, the query engine does not actively clear previously saved query plans. Therefore, in some cases, a similar SQL statement only has many execution plans out of thin air because of different writing methods. For similar SQL statements, these redundant execution plans occupy the memory, the number of query plans cached in SQL Server is greatly affected. In this case, if the maximum available memory of SQL Server is limited, it will reduce the available memory of SQL Server, therefore, more memory pages are exchanged with the disk during query execution, especially for large data queries. If the maximum available memory is not set, the SQL Server caches too many execution plans, so that the memory usage is too large. 3. How to reduce the usage of process cache is mainly through parameterized query. The key to parameterized query is that the query optimizer will create a reusable cache plan (the SQL Server Query Optimizer will rewrite the query into a parameterized SQL statement ), this reusable cache plan eliminates the need to create a cache plan for each execution of these SQL statements. By creating a reusable plan, SQL Server reduces the memory usage required to store similar execution plans. For developers, we can use the following two methods to achieve parameterized query: (1) Execute SQL statements using stored procedures; (2) Execute SQL statements using sp_executesql. Concerning the use of stored procedures to execute SQL statements, another question: there has been a lot of controversy over stored procedures. For example, The ORM team believes that stored procedures are completely process-oriented and difficult to expand and maintain. Based on my personal development experience, I suggest using more simple and almost no logic stored procedures, but complex stored procedures have always been BUG camps, in addition, the maintenance cost is extremely high in the future (as our architect said, the database of an important business system has a storage process of more than eight thousand rows and more than two hundred variables, no one dares to change ), it is best to write the logic through the business logic layer to cope with drastic changes. Now we have a mature ORM and layers. In development, we must avoid writing too long and logically complex stored procedures. Otherwise, we will face changes, it is not impossible to see several eight thousand rows of stored procedures over time.