MySQL is one of the most popular open source databases, which significantly improves performance through caching mechanisms. Common caches include key cache (Key Cache), table cache (Table Cache), Innodb Buffer Pool, etc. These cache mechanisms help reduce disk I/O, but in some scenarios we need to temporarily shut down these caches for debugging, testing, or solving performance issues.
1. Overview of MySQL 8 Caching Mechanism
MySQL 8 provides a variety of caching mechanisms to improve database performance. Understanding how these caches work and use helps us make the right adjustments when needed.
1.1 Key Cache
Key cache is mainly used in MyISAM storage engine, which caches index blocks. Although InnoDB is mainstream, MyISAM is still applicable in certain scenarios. Key cache improves query speed by reducing disk I/O operations. Here are the working principle and configuration methods of key cache:
How it works: The key cache stores the index block in memory. When a query needs to access the index, it can be read directly from memory without accessing the disk.
Configuration method: Can be passedkey_buffer_sizeParameters to set the size of the key cache. For example:
SET GLOBAL key_buffer_size = 256M;
1.2 Table Cache
The table cache stores MySQL-opened table file descriptors. It helps to reduce frequent table opening and closing and improve query speed. Table cache size passestable_open_cacheParameter configuration. Here are the details of the table cache:
How it works: The table cache stores open table file descriptors in memory, avoiding frequent file opening and closing operations, thereby improving performance.
Configuration method: Can be passedtable_open_cacheParameters to set the size of the table cache. For example:
SET GLOBAL table_open_cache = 2000;
1.3 InnoDB Buffer Pool
This is the core caching mechanism of MySQL 8, which is used to cache data pages, index pages, and modified data. Configurationinnodb_buffer_pool_sizeCan significantly improve performance. Suitable for most production environments using InnoDB storage engines. Here are the details of the InnoDB buffer pool:
How it works: The InnoDB buffer pool stores data pages and index pages in memory, reducing disk I/O operations. When a query requires access to data, it can be read directly from memory without accessing disk.
Configuration method: Can be passedinnodb_buffer_pool_sizeParameters to set the size of the buffer pool. For example:
SET GLOBAL innodb_buffer_pool_size = 4G;
2. Why do we need to temporarily close the cache?
In some cases, we may need to temporarily shut down MySQL's caching mechanism. Here are some common scenarios and reasons:
2.1 Benchmarking and performance analysis
Turning off cache helps to understand the true performance of the system when cache is not used, especially when benchmarking, preventing cache from affecting results. By turning off the cache, we can obtain more accurate performance data, helping us optimize database configuration and queries.
Example: When conducting benchmarking, you can simulate the real production environment by turning off the cache to ensure the accuracy of the test results.
2.2 Data consistency and dirty data issues
When the cache fails to refresh in time, it may cause dirty data problems and the query results are inconsistent with the actual data. Turning off cache ensures that the latest data is read from disk and avoids data inconsistencies.
Example: When performing data consistency checks, you can ensure the accuracy of query results by turning off the cache.
2.3 High-frequency data update
In frequent data writing scenarios, the benefits of cache may not be large, because frequent updates will lead to frequent cache refreshes and increase system overhead. At this point, temporarily closing the cache may be a better choice.
Example: In application scenarios for high-frequency data writing, the system overhead can be reduced and write performance can be improved by turning off the cache.
3. How to temporarily close MySQL 8 cache?
In MySQL 8, we can temporarily close different types of caches by adjusting configuration parameters. The following are the specific methods:
3.1 Close the table cache
Table cache can be adjustedtable_open_cacheClosed on time. By setting it to a smaller value, you can reduce the use of cache:
SET GLOBAL table_open_cache = 1;
Doing this will result in MySQL only opening one table file at the same time, reducing the use of table cache.
3.2 Adjusting the InnoDB buffer pool
The InnoDB buffer pool is one of the key components of MySQL performance, closing it is not practical, but it can be adjusted byinnodb_buffer_pool_sizeTo simulate the effect of cache closure:
SET GLOBAL innodb_buffer_pool_size = 16777216; -- Shrink to16MB
By setting the buffer pool size to a smaller value, you can reduce the use of cache and simulate the effect of turning off cache.
3.3 Disable key cache
For MyISAM table scenarios, you can adjustkey_buffer_sizeTo close the key cache:
SET GLOBAL key_buffer_size = 0;
Doing this completely disables key cache and all index accesses are read directly from disk.
4. The potential impact of closing cache
Turning off caching may have an impact on database performance and system resources. Here are some potential implications:
4.1 Performance degradation
Cache improves performance by reducing disk I/O. After the cache is turned off, all queries will read data directly from disk, and the performance will be significantly reduced, especially in large-scale query scenarios.
Example: When conducting large-scale data queries, closing the cache may cause a significant decline in query speed and affect the user experience.
4.2 File system pressure
Turning off cache increases disk I/O load and can cause file system bottlenecks, especially in environments where traditional HDDs are used.
Example: In environments with high disk I/O load, shutting down cache may cause degradation in disk performance and affect overall system performance.
4.3 Data consistency issues
Turning off the cache may cause data inconsistency in some concurrent operations, especially in scenarios where the cache is frequently updated.
Example: In application scenarios with high concurrent writes, closing cache may cause data consistency problems and affect data accuracy.
5. Which scenarios are suitable for temporarily closing cache?
In certain specific scenarios, temporarily closing caches can be an effective solution. Here are some scenarios suitable for temporarily shutting down caches:
5.1 Benchmark Test
When performing performance benchmarks, turning off caches can avoid buffering interference with results, reflecting the actual performance of the database.
Example: Before performing database performance optimization, you can benchmark by turning off the cache to obtain accurate performance data.
5.2 High frequency writing scenarios
In frequent write environments, the effect of cache is limited, and it may even add additional load due to frequent refreshes. In this case, it is possible to consider turning off the cache.
Example: In application scenarios for high-frequency data writing, the system overhead can be reduced and write performance can be improved by turning off the cache.
5.3 Scenarios with low cache hit rate
When the cache hit rate is low, the performance improvement brought by the cache is limited, and turning off the cache may reduce waste of system resources.
Example: In application scenarios with low cache hit rate, you can reduce system resource waste and improve overall performance by turning off cache.
6. How to monitor MySQL performance after closing the cache?
After shutting down the cache, we need to monitor the performance of MySQL to ensure that the system is running properly. Here are some commonly used monitoring methods:
6.1 View cache hit rate
You can view the current cache hit situation through the following SQL statement:
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%'; -- Check InnoDB Buffer pool status
By viewing the cache hit rate, we can understand the cache usage and determine whether we need to adjust the cache configuration.
6.2 Using performance_schema
passperformance_schema, can monitor the database performance after I/O operations and cache shutdown in detail:
SELECT * FROM performance_schema.file_summary_by_event_name WHERE event_name LIKE 'wait/io/file%';
performance_schemaProvide detailed performance data to help us analyze and optimize database performance.
6.3 Using external monitoring tools
For example, MySQL Enterprise Monitor can track cache usage and database performance in real time. External monitoring tools often provide a more intuitive interface and detailed performance reports.
Monitoring MySQL performance after shutting down the cache is a critical step in ensuring the stable operation of the system. In addition to the aforementioned methods, the following tools and techniques can be used to monitor and optimize database performance.
6.4 Using MySQL built-in tools
MySQL provides some built-in tools that can help us monitor and analyze database performance. For example:
SHOW STATUS Order: You can view various state variables of MySQL to understand the operation of the database. For example:
SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Queries';
These commands can help us understand key metrics such as the current number of connections and query numbers.
EXPLAINOrder: It can analyze the execution plan of SQL query and help us optimize query. For example:
EXPLAIN SELECT * FROM my_table WHERE id = 1;
By looking at the execution plan of the query, we can identify potential performance bottlenecks and optimize them.
6.5 Using the operating system monitoring tool
In addition to MySQL built-in tools, we can also use operating system-level monitoring tools to understand the performance of the database. For example:
topandhtop: You can view the system's CPU, memory and I/O usage in real time, helping us understand the resource consumption of the database.
iostat: It can monitor disk I/O performance and help us discover disk bottlenecks. For example:
iostat -x 1
By monitoring disk I/O performance, we can understand the impact on disk after shutting down cache.
6.6 Using the log analysis tool
The log files generated by MySQL are also important performance analysis resources. We can discover performance issues and potential optimization points by analyzing log files. For example:
Slow query log: Records a query whose execution time exceeds the specified threshold. By analyzing slow query logs, we can discover and optimize queries with poor performance. For example:
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; -- Set the slow query threshold to2Second
After turning on the slow query log, we can analyze the log files regularly and optimize slow query.
Error log: Records errors and warnings that occur during MySQL run. By analyzing the error log, we can discover potential issues and fix them.
7. Alternative: Optimization measures when the cache has been removed or invalidated
In some cases, turning off caching may not be the best option. We can consider other optimization measures to improve database performance.
7.1 External Caching Solution
Using external caching solutions such as Redis and Memcached at the application layer can reduce direct queries to the database and reduce database load. For example:
Redis: As an in-memory database, Redis provides a high-performance caching solution. We can store frequently accessed data in Redis to reduce queries to MySQL. For example:
import redis
r = (host='localhost', port=6379, db=0)
('my_key', 'my_value')
value = ('my_key')
print(value)
Memcached: As a distributed cache system, Memcached provides a simple and efficient caching solution. We can store frequently accessed data in Memcached to improve query performance. For example:
import memcache
mc = (['127.0.0.1:11211'], debug=0)
('my_key', 'my_value')
value = ('my_key')
print(value)
7.2 Optimize query and indexing
By optimizing queries and indexes, we can improve the query performance of the database and reduce dependence on caches. For example:
Create the right index: By creating the right index, query performance can be significantly improved. For example:
CREATE INDEX idx_my_column ON my_table(my_column);
Optimize query logic: Reduce unnecessary query operations by optimizing query logic. For example:
SELECT * FROM my_table WHERE my_column = 'value';
7.3 Dynamically adjust cache parameters
MySQL supports dynamic adjustment of cache parameters, and we can flexibly set the cache size according to business load. For example, increase during peak periodsinnodb_buffer_pool_size, reduce during the low peak period to save memory resources. For example:
SET GLOBAL innodb_buffer_pool_size = 8G; -- Increase buffer pool during peak period SET GLOBAL innodb_buffer_pool_size = 2G; -- Reduce buffer pools during low peak periods
By dynamically adjusting cache parameters, we can optimize database performance under different loads.
8. Summary
In MySQL 8, query cache has been removed, but other cache mechanisms (such as table cache, key cache, InnoDB buffer pool) are still key ways to improve performance. Temporary caching shutdown can help benchmark, resolve dirty data issues, or deal with frequent write scenarios. However, shutting down cache can also have side effects of performance degradation and increased disk I/O. By rationally using external caches, optimizing query structures, and dynamically adjusting cache parameters, more efficient database performance management can be achieved.
This is the end of this article about the implementation of MySQL8 temporary cache shutdown method. For more information about MySQL8 temporary cache shutdown content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!