Many Sleep Threads

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

Original topic: sleep线程多

| username: Hacker_ythhI9Qd

【TiDB Usage Environment】Production Environment / Testing / PoC
【TiDB Version】5.7.25-TiDB-v4.0.16
【Reproduction Path】What operations were performed to encounter the issue
【Encountered Issue: Problem Phenomenon and Impact】There are many sleep threads. By using show global variables like '%timeout%'; and show variables like '%timeout%';, the interactive_timeout is found to be 8 hours, and wait_timeout is 0 and 8 hours respectively. How can we reduce these sleep threads?
【Resource Configuration】Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots/Logs/Monitoring】
66e7bca3843c4c288f50d7c8057545b6

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

It is recommended to set it from the application side; setting it in the database is not very good.

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

The time_out can be set lower; we have it set to 1 hour. However, this is only at the database level, and the application level also needs to be configured. You need to set the timeout and connection count in the application framework configuration. Otherwise, if the database kills the sleeping connections, the application will report an error when it tries to reuse those connections.

| username: Kongdom | Original post link

We also have many of these, but we haven’t paid attention to them before. Do these sleep threads have any impact?

| username: xfworld | Original post link

Occupying the maximum number of connections is common when using connection pools, and the impact is generally not significant.

| username: redgame | Original post link

Is there a long idle connection in the application?

| username: TiDBer_5cwU0ltE | Original post link

If it hasn’t caused any issues, you can leave it as is for now. You can try changing some uncertain parameters in the testing environment first; this approach is more cautious.

| username: TiDBer_vfJBUcxl | Original post link

I saw a post about sleep threads in a MySQL database. On the database side, reducing the database wait time is used to decrease sleep threads. I feel that reducing it might cause other issues, so I don’t recommend lowering the corresponding parameter values.

| username: zhanggame1 | Original post link

How much sleep is considered a lot? Has it caused any impact?