Slow Cache Table

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 缓存表很慢

| username: TiDBer_AxdzSBqt

There is currently a dimension table with less than 100,000 rows of data, which is now set as a cache table and is updated every hour. It has been observed that in slow query SQLs, the get_snapshot_time is very long when reading the cache table. Does this mean that it is caused by too much historical data? In other words, after the data is updated, when TiDB pulls the cache table from TiKV into memory, there are too many historical versions involved. Currently, only the changes from the last two hours are retained, so theoretically, the maximum would be the current total amount * 2, which is less than 200,000 rows of data. However, in some slow query SQLs, the get_snapshot_time sometimes takes several seconds. What could be causing this?

| username: WinterLiu | Original post link

Is the data volume too large?

| username: TiDBer_AxdzSBqt | Original post link

I mentioned that the data volume is not large, less than 100,000. Even with historical versions, it won’t exceed 200,000 in extreme cases.

| username: lemonade010 | Original post link

Post the execution plan and let’s take a look.

| username: tidb菜鸟一只 | Original post link

Tables larger than 64M are not allowed to be used as cache tables. Although your 100,000 rows are not very large, the hourly updates might not be suitable. You can check the mysql.table_cache_meta table for any locks and information related to the lock lease.

| username: TiDBer_jYQINSnf | Original post link

Check whether the execution plan has hit the cache.

| username: TIDB-Learner | Original post link

The use of cache tables includes one condition: the data does not change frequently, with a change frequency of one hour.
Additionally, the number of rows in the table doesn’t matter much; the main concern is the size.

| username: h5n1 | Original post link

Is it only this cache table that has this problem?

| username: zhanggame1 | Original post link

Cache tables are not suitable for updates, it’s best not to modify them.

| username: Kongdom | Original post link

Please provide the execution plan to check if there are many scan versions inside?

| username: TiDBer_7S8XqKfl | Original post link

You can check if there is an IO bottleneck in the storage of TiKV.

| username: 濱崎悟空 | Original post link

Let’s take a look at the execution plan?

| username: FutureDB | Original post link

From the scenario where you need to update every hour, this is not very suitable for using a cache table. Updating a cache table is relatively slow and cumbersome. Cache tables are more suitable for scenarios with low update frequency and small update data volumes.

| username: TiDBer_7S8XqKfl-1158 | Original post link

Check if the data volume is too large.

| username: TiDBer_3Cusx9uk-0775 | Original post link

If the cache table involves frequent write operations, it may lead to write hotspots, affecting read performance.