Is there a "global table" setting in TiDB?

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

Original topic: TIDB 是否存在 “”全局表“”设置

| username: DBAER

Does TiDB support setting certain tables and their associated tables to be distributed on the same TiKV, so that queries can be completed on a single KV and avoid cross-node queries or aggregation?

| username: changpeng75 | Original post link

Doesn’t this setting conflict with TiDB’s internal mechanism?
Since each three-replica region independently selects its own Leader, even if the initial setup is on one TiKV server, the Leaders will gradually be distributed across different TiKV servers.

| username: changpeng75 | Original post link

The described application environment seems more like an OLAP application, so using TiFlash and running MPP should be sufficient.

| username: vincentLi | Original post link

Currently, there should be no such setting. However, I think your idea is very good. In fact, tables that are frequently queried together should adopt the same storage strategy, which could bring better performance for associated queries. Additionally, the OOM problem that might arise from the inability to push down operators in large table associations could also be avoided. Of course, this storage planning issue might be a dilemma. Introducing an AI module in PD to solve such planning issues might be a good approach.

But at present, it seems unfeasible. Many data centers now use 10G networks and SSDs, so the data transmission problem that this same storage strategy aims to solve might not be as time-consuming as imagined. Moreover, we also need to consider that operator pushdown itself can optimize associated table queries to some extent. If all tables are on the same TiKV node and all operators are pushed down to the same machine for computation, the effect might actually be worse.

| username: zhanggame1 | Original post link

The table associations in TiDB are handled by the TiDB server nodes. Even if TiKV stores the data together, it doesn’t have any practical significance and may instead cause read-write hotspots.

| username: YuchongXU | Original post link

Not this.

| username: xiaoqiao | Original post link

It’s not useful. For example, TiFlash can be used, but it’s not exactly what you have in mind. TiDB is similar to a sharded cluster, where you can specify shard keys.

| username: TIDB-Learner | Original post link

I thought of labels. In your situation, it is feasible, but it does not align with the advantages of distributed systems.

| username: TiDBer_vJGTQABF | Original post link

Currently not supported.

| username: ti-tiger | Original post link

What if other business operations do not require these tables to be associated, but rather some of these tables to be associated with other tables? Wouldn’t the final result still be scattered?

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

You can specify the location of a particular table using Placement Rules in SQL. Placement Rules in SQL | PingCAP 文档中心

| username: Soysauce520 | Original post link

If you want it to be faster, it’s better to query separately. If you want to do resource management, it’s better to use a higher version for isolation.

| username: zhang_2023 | Original post link

There is no such thing.

| username: zhaokede | Original post link

Placement policy can be set.

| username: TiDBer_HErMeXDz | Original post link

There are no global tables… This concept exists in database middleware such as Mycat and DBLE.

| username: h5n1 | Original post link

Configuring placement rules in SQL, but doing so doesn’t make much of a difference. Even if it’s not across nodes, it’s still a distributed transaction.

| username: WalterWj | Original post link

TiDB does not have local transactions; it uses distributed transactions. :relieved:

| username: 有猫万事足 | Original post link

If it’s a dictionary table that everyone needs to use, you can use a cached table.

Directly cache the small table in TiDB’s memory to improve read access performance.

| username: TiDBer_嘎嘣脆 | Original post link

Transactions, there are no global tables, right?

| username: TiDBer_lQ7RQmJ5 | Original post link

I wonder if there is a way to set up a replicated table. By creating a small table as a replicated table, it can also be used for analysis and processing without needing to join across machines. This should not conflict with the TiDB architecture.