Separation of HTAP's Analytical and Transactional Processing

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

Original topic: HTAP的A\T分离

| username: karasu

[TiDB Usage Environment] Production Environment
[TiDB Version] v5.1.0
Traditional transaction and analysis systems usually separate OLTP and OLAP databases, so that analysis on the OLAP system does not affect the performance of the business system.
TiDB’s HTAP automatically optimizes based on SQL, choosing either TiKV replicas or TiFlash replicas.
How can I configure it to ensure that business applications and analysis applications hit different replicas?

| username: Kongdom | Original post link

TiDB supports Optimizer Hints syntax, which is based on the comment-like syntax introduced in MySQL 5.7, such as /*+ HINT_NAME(t1, t2) */. When the TiDB optimizer does not choose the optimal query plan, it is recommended to use Optimizer Hints.

READ_FROM_STORAGE(TIFLASH[t1_name [, tl_name ...]], TIKV[t2_name [, tl_name ...]]) hints the optimizer to read the specified tables from the specified storage engines. Currently supported storage engine parameters are TIKV and TIFLASH. If an alias is specified for a table, only the alias can be used as a parameter for READ_FROM_STORAGE(); if no alias is specified, the table’s original name is used as its parameter. For example:

SELECT /*+ READ_FROM_STORAGE(TIFLASH[t1], TIKV[t2]) */ t1.a FROM t t1, t t2 WHERE t1.a = t2.a;
| username: karasu | Original post link

We mainly want to separate A/T at the application level. It looks like we can achieve this by setting session parameters to only access TiFlash and TiKV respectively. Furthermore, if we want both business applications and analytical applications to use TiKV and TiFlash but access different replicas, is there a way to achieve this?

| username: Kongdom | Original post link

This doesn’t seem to work. A cluster only provides one copy externally, which is the leader replica.

However, it is possible to read data from the follower replica.

| username: dba-kit | Original post link

+1, this is feasible. Let OLTP only read and write to the leader, and OLAP only read from the follower. However, in the end, it will still map to the same set of TiKV. One good aspect of this solution is that OLAP needs to traverse a lot of data, which can easily lead to read hotspots. Reading from the follower can avoid hotspots to a certain extent.

| username: Kongdom | Original post link

:handshake: :handshake: :handshake:

| username: system | Original post link

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