How to Optimize Kernel Parameters to Improve TiDB Write Performance

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

Original topic: TiDB提升写入性能内核参数如何优化

| username: 波士顿小铁匠

[TiDB Usage Environment] Test Environment
[TiDB Version] v6.5.2
[Reproduction Path] None
[Encountered Problem: Problem Phenomenon and Impact] During a mixed read-write stress test on a 5 million-row data table, the TPS was found to be too low. Seeking clarification on how to configure kernel parameters for write performance in a production environment.
[Resource Configuration]
[Attachment: Screenshot/Log/Monitoring]
Sysbench compression results are as follows:

| username: 像风一样的男子 | Original post link

What is the cluster architecture, how many nodes, and what configuration? Are the disks mechanical? The performance is a bit poor.

| username: 波士顿小铁匠 | Original post link

This is my topology distribution:


All are mechanical disks, the performance is extremely poor :rofl:
I want to ask if there are any basic kernel parameters that need to be adjusted.

| username: 像风一样的男子 | Original post link

Try increasing the sysbench parameter threads.

| username: 波士顿小铁匠 | Original post link

I set the thread to 10, but the improvement is not significant, as shown in the picture below:

| username: 像风一样的男子 | Original post link

If you change it to 100 or 200, the difference will be significant. Also, does TiDB have load balancing settings?

| username: TiDBer_jYQINSnf | Original post link

Increase the number of threads until the QPS drops. Then check the disk I/O to see if it’s saturated. If it is, determine whether it’s the IOPS or the bandwidth that’s maxed out.

If the IOPS is maxed out, adjust the parameters to increase batching:

Increase the write buffer size to accumulate more in memory:

Additionally, consider tuning the TiKV thread pool:

However, for mechanical hard drives, the thread pool adjustments might not be as effective since the disk bandwidth will likely be the limiting factor.

These adjustments might increase write latency, but overall throughput should improve. For better read performance, increase the block cache size.

| username: Billmay表妹 | Original post link

Resource contention is likely to occur with mixed deployment.

According to the official documentation, apart from TiDB and PD which can be deployed on the same server, it is recommended to deploy other components separately.

| username: zhanggame1 | Original post link

Mechanical drives definitely won’t work, switch to a standalone database.

| username: 波士顿小铁匠 | Original post link

Okay, thank you. I’ll give it a try.

| username: 波士顿小铁匠 | Original post link

Currently, I have not configured load balancing for TiDB. Does this factor have a significant impact?

| username: 像风一样的男子 | Original post link

The difference between one TiDB node and three TiDB nodes is definitely significant, but the bottleneck of your cluster is likely on the disk.

| username: 波士顿小铁匠 | Original post link

Thank you, teacher. I will explore according to your idea.

| username: cy6301567 | Original post link

According to the official recommendation, deploy independently. PD and KV have high IO resource requirements, so they are not suitable for mixed deployment.

| username: system | Original post link

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