Under what circumstances does a TiDB node use disk?

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

Original topic: TiDB节点在什么情况下会使用磁盘呢?

| username: kkpeter

I noticed that the disk IO of TiDB nodes occasionally increases in the cluster. I would like to know in what situations the TiDB nodes use the disk, besides writing logs and slow logs.

| username: 啦啦啦啦啦 | Original post link

After version 6.5, adding an index will also temporarily occupy disk space on TiDB nodes. When memory usage exceeds the mem-quota-query limit, some operators will also use temporary disk space.

| username: TiDBer_5cwU0ltE | Original post link

Has the IO increased to the point where it affects performance? If it doesn’t seem to have an impact, you might not need to worry about it for now. Alternatively, you can use some monitoring tools to see which processes are writing to the disk.

| username: kkpeter | Original post link

What else is there?

| username: TiDBer_QYr0vohO | Original post link

PD has a dashboard, which also reads the logs on the TiDB server disk when viewing component logs, as well as monitoring, etc.

| username: zhanggame1 | Original post link

You can refer to this

TiDB will use temporary disk space
TiDB Environment and System Configuration Check | PingCAP Documentation Center

| username: 啦啦啦啦啦 | Original post link

Generally speaking, it’s just these two types. Checking logs and similar activities will also take up a bit, but very little.

| username: kkpeter | Original post link

I have configured both directories and set them on the data disk, but the system disk IO has surprisingly risen to 78% usage, which is a bit strange.

| username: zhanggame1 | Original post link

Install the iotop command to see which specific process it is.

| username: Fly-bird | Original post link

Temporary space.

| username: 昵称想不起来了 | Original post link

How about verifying it with Linux command results?
Use the iotop command to check processes with high IO usage.
Use the lsof command to check files being used by processes.
Use the strace command to trace and debug system calls of processes.

| username: 人如其名 | Original post link

  1. Sort Sorting: If the current session’s memory is insufficient, external sorting will be performed. External sorting results in lower disk read/write efficiency, leading to poor performance. There is already a PR for optimization: executor: pre-alloc chunks to optimize `SortExec` in by YangKeao · Pull Request #46483 · pingcap/tidb · GitHub.
  2. HashAGG Aggregation: By default, it does not spill to disk. However, if you enable tidb_hashagg_final_concurrency=1 and tidb_hashagg_partial_concurrency=1 to use non-parallel hashAGG mode, the result set may spill to disk if it is too large. For more details, refer to: 对于hashAgg算子非并行模式下还是发生OOM - TiDB 的问答社区. There is also an issue for the disk-spilling capability of parallel hashAGG (default parameters): Support spill to disk for parallel hash aggregation · Issue #46631 · pingcap/tidb · GitHub.
  3. HashJoin: When the build side of hashJoin is too large, data will spill to disk, and only row pointers are kept in memory. For more details, refer to: 当HashJoin的BuildSide过大时容易OOM - TiDB 的问答社区.
  4. Merge Join: If the inner table has too many duplicate values, it may also spill to disk.
  5. CTE: Common Table Expressions (with xxx as structure) will spill intermediate results to disk if session memory is insufficient.
  6. CursorFetch Data Retrieval: When using MySQL’s Cursor Fetch protocol, if the result set exceeds the tidb_mem_quota_query limit, it can cause TiDB to OOM. After the fix, TiDB will automatically write the result set to disk to free up memory resources. The specific fix PR: server: implement spill disk for cursorFetch result by YangKeao · Pull Request #45163 · pingcap/tidb · GitHub, which has been fixed in version 6.5.4.
  7. DDL Adding Index: Using ingest to speed up index creation will also consume a large amount of temporary space.

I believe the following also need disk-spilling mechanisms:

  1. Temporary Tables: Currently, temporary tables are in-memory tables, which limits their usage. They should have a disk-spilling mechanism. Refer to the post: 临时表能力的增强与tidb-server层缓存机制的建立 - TiDB 的问答社区.
  2. Large DML Transactions: Currently, large transactions read all records into the tidb-server memory. There should be a disk-spilling mechanism to reduce memory usage (Note: delete and update operations read entire rows from TiKV into tidb-server, which should be optimized, e.g., delete operations should only fetch keys to tidb-server to reduce memory usage).
| username: redgame | Original post link

Temporary tables and sorting, we have a lot of these here.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.