Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: tidb生产环境中是否建议使用查询缓存功能？
|
username: TIDB-Learner
Starting from v7.5.0, it is compatible with MySQL 8.0. The query cache feature was deprecated after MySQL 8.0. What about TiDB’s use of this feature (query cache)? Is it better to disable or enable it?
The command show VARIABLES like ‘%query_cache%’; shows that this parameter is enabled.
The query cache in MySQL 8.0 has been deprecated and completely removed in version 8.0.20. This feature has more disadvantages than advantages. It seems that TiDB does not have a query cache feature. The only similar feature is small table cache, which is supported. By default, tables up to 64M can be cached as small tables.
tidb-server conditionally supports execution plan caching, but it does not currently support caching for complete query results in tidb-server. The use of caching not only has a relatively low hit rate but also brings many memory management issues.
However, tidb-server has a Coprocessor Cache, which mainly caches query results at the region level and is a cache for pushed-down computation results. The cache is only stored in TiDB’s memory, and it becomes invalid after TiDB restarts. Different TiDB instances do not share the cache. Even if the cache is hit, TiDB still performs subsequent calculations.
Since writes to a Region will invalidate the cache involving that Region, this feature is mainly effective for data that changes infrequently.
Distributed database query caching is very difficult to implement and consumes much more resources than a single machine. It is currently not supported.
TiDB uses region cache for query caching. From the design of different databases, caching functionality is often present, but specific considerations may vary.
Currently, query caching is not supported, but small table caching is supported.
Only small tables are cached, just finished the exam 
TiDB does not have query caching, and small table caching is not much faster either.
In scenarios involving reading relatively small configuration tables and static global tables (tens or hundreds of rows), the performance improvement is still quite noticeable. We have a scenario where Flink joins some configuration tables, with a QPS of tens of thousands. After switching to small table caching, the join SQL latency dropped from 10ms to around 2ms, significantly increasing the speed.
It is possible that your table is indeed small. I tested three tables with 100,000 rows in the database, all placed in the small table cache, and the join wasn’t much faster, returning only one or two rows of data.
Is the small table cache something that needs to be configured? Or does it cache based on query heat automatically?
Set it yourself. After setting, it is not suitable to update and modify this table. It needs to be converted to uncached to make changes.
In TiDB, this configuration is enabled. The purpose of posting this topic is to gain a deeper understanding of the usage of this cache. If TiDB does not support it, does this configuration have any other uses? Or is some code just borrowed from MySQL, so it exists but is not functional?
TiDB has small table caching, and it is best for these small tables to generally have no DDL and DML operations.
Cached Tables | PingCAP Documentation Center
The hot small table caches the entire small table in memory and ensures data consistency through read-write locks. It feels more like the usage of Redis.
Query caching improves the response speed of identical query statements. MySQL Server calculates a hash value for the query statement. After obtaining the hash value, it matches the query result in the query cache using this value.
If there is a match, the result set is directly returned to the client without parsing and executing the query again, which improves performance to some extent. If there is no match, the hash value and result set are stored in the query cache for future use. In other words, when a query statement (select) reaches the MySQL Server, it first checks the query cache. If it has been executed before, it directly returns the result set to the client.
Initially, I was just confused about what this feature actually is. Gradually, I started to understand it better. If you are familiar with the distributed caching technology used in distributed systems, it might become clearer.
In summary, there are many things I don’t understand and need to research deeply. If you want to progress faster, you should post more topics and discuss them with like-minded people to promote progress.
The official documentation states: “Since applications and connectors usually need to read MySQL variables, to be compatible with this requirement, some MySQL variables in TiDB can be both read and set. For example, although the JDBC connector does not rely on the behavior of the query cache, it can still read and set the query cache.”
Cluster databases seem to have a low query cache hit rate. Cache for small tables can be useful.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: