Can a TiDB cluster perform read-write separation?

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

Original topic: tidb集群可以做读写分离吗

| username: TiDBer_y9IRzLWc

[TiDB Usage Environment] Production / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots / Logs / Monitoring]

Currently, when our TiDB database performs batch insert operations, if another service concurrently reads the database content, the QPS of TiDB increases, which in turn affects the performance of the inserts. Can TiDB perform read-write separation? Can insert operations and data reads be handled by different servers without affecting each other’s performance?

| username: longzhuquan | Original post link

From the TiDB layer (computing layer), it is possible, but TiKV cannot achieve this for now. Read-write separation in MySQL essentially refers to master-slave read-write separation. The TiDB architecture itself is distributed, with read and write operations evenly distributed across three machines at the KV layer. Theoretically, this model is superior to the master-slave model. If you insist on having read-write separation similar to MySQL’s master-slave setup, where read and write transactions do not affect each other, you could use two clusters with CDC synchronization, one for writing and one for reading.

| username: ffeenn | Original post link

First of all, TiDB is a distributed database. There is no need for read-write separation; you should consider whether the performance bottleneck is due to PD or TiDB. Configuring read-write separation should not be considered. In scenarios where the database is read-heavy, it is generally optimized into a cache cluster.

| username: zhanggame1 | Original post link

Deploy TiCDC to synchronize data to other databases.

| username: TiDBer_y9IRzLWc | Original post link

Okay, thank you, understood.

| username: WalterWj | Original post link

You can use the follow read feature, and limit the leader to a few servers. If you want to know how to enable the feature and set the limits, you can search on the official website.

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

Refer to this, using follower read will do. Setting up another cluster is a bit costly.

| username: cassblanca | Original post link

TiDB itself is a distributed database with a separation of storage and computation, which has requirements for hardware resources. Adding additional sharding for read-write separation would result in significant hardware resource investment. It is recommended to design the data table structure well to avoid write hotspots, as the mutual impact should not be too significant. Alternatively, you can also use CDC to synchronize to other databases.

| username: redgame | Original post link

Are you going to conduct an experiment, meaning using TiDB as the write node and TiKV as the read node? Then try it out in the application…

| username: ShawnYan | Original post link

Consider whether you are encountering a write hotspot issue.
Consider upgrading to a newer version.

| username: zhanggame1 | Original post link

When performing batch insert operations in the TiDB database, if another service is concurrently reading the database content, the QPS of TiDB will increase.

To solve the problem, you can start from the problem itself, such as the insertion speed, how the insertion is done (different insertion methods have different performance impacts), and which resources have bottlenecks during insertion, etc. See if it is possible to optimize first.

| username: YuchongXU | Original post link

You can try
follow read

| username: system | Original post link

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