Selection of TDSQL Instance and Shard Configuration
Last updated: 2024-01-06 17:34:55
TDSQL Selection Overview
A TDSQL instance is composed of shards. The specification and number of shards determine the processing capability of the instance. In theory:
TDSQL instance read and write concurrence performance = ∑ (performance of a shard with a certain specification * number of shards)
TDSQL instance transaction performance = ∑ (transaction performance of a shard with a certain specification * 70% * number of shards)
Therefore, the higher the shard specification and quantity, the stronger the processing capability of the instance. The performance of a shard is mainly subject to CPU/memory and measured by QPS. General performance metrics are detailed in the shard performance description section.
TDSQL Shard Specification Selection
Specification of a TDSQL shard is determined by three factors: performance, capacity, and other requirements.
Performance: by predicting the performance demand and possible growth for at least six months, you can define the total CPU/memory size required of your TDSQL instance.
Capacity: by predicting the disk capacity and possible growth for at least one year, you can define the total disk size required of your TDSQL instance.
Other requirements: you are recommended to store at least 50 million rows of data in one shard and take into account business needs such as broadcast and non-sharded tables and in-node joins.
Note:
You are recommended to ensure high specification for a single shard while keeping the shard quantity relatively small.
Based on the above, it is estimated that you may have the following business requirements. Recommended strategies are as follows:
For functional testing in TDSQL with no special performance requirements: 2 shards with 2 GB of memory and 25 GB of disk capacity each.
In initial stage of business when the total size of data is small but grows fast: 2 shards with 16 GB of memory and 200 GB of disk capacity each.
In stable development stage when sharding is based on actual business conditions: 4 shards, and the specification for each one should be current business peak * growth rate / 4.
TDSQL Shard Performance Test
The database benchmark performance test is to modify the descriptions of sysbench 0.5: the OTLP script that comes with sysbench was modified. Specifically, the read/write ratio was changed to 1:1 and controlled by the testing command parameters
oltp_point_selects and oltp_index_updates. In this document, all test cases involve four SELECT operations and one UPDATE operation with the read/write ratio at 4:1.Memory (GB) | Storage Capacity (GB) | Dataset (GB) | Number of Clients | Single-Client Concurrence | QPS | TPS |
2 GB | 100 GB | 46 GB | 1 | 128 | 1880 | 351 |
4 GB | 200 GB | 76 GB | 1 | 128 | 3983 | 797 |
8 GB | 200 GB | 142 GB | 1 | 128 | 6151 | 1210 |
16 GB | 400 GB | 238 GB | 1 | 128 | 10098 | 2119 |
32 GB | 700 GB | 238 GB | 2 | 128 | 20125 | 3549 |
64 GB | 1 TB | 378 GB | 2 | 128 | 37956 | 7002 |
96 GB | 1.5 TB | 378 GB | 3 | 128 | 51026 | 10591 |
120 GB | 2 TB | 378 GB | 3 | 128 | 81050 | 15013 |
240 GB | 3 TB | 567 GB | 4 | 128 | 96891 | 17698 |
480 GB | 6 TB | 567 GB | 6 | 128 | 140256 | 26599 |
TPS here is for single transaction other than distributed transaction.
As required by operational strategies, current TDSQL instances (or part of them) adopt the policy of overuse of idle resources, so CPU utilization may exceed 100% on your monitoring panel.
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No
Feedback