TDengine
TDengine copied to clipboard
taosdata
TD count on subtables have low QPS
Performance Issue
当子表较多的时候，count查询子表响应速度很慢. 当concurrent = cpu核数 vnode leader节点cpu 100%；
Problem Description
库表信息如下
taos> show create database history_t005\G;
*************************** 1.row ***************************
Database: history_t005
Create Database: CREATE DATABASE `history_t005` BUFFER 128 CACHESIZE 64 CACHEMODEL 'last_row' COMP 2 DURATION 43200m WAL_FSYNC_PERIOD 3000 MAXROWS 4096 MINROWS 100 STT_TRIGGER 1 KEEP 1056960m,1056960m,1056960m PAGES 256 PAGESIZE 4 PRECISION 'ms' REPLICA 3 WAL_LEVEL 1 VGROUPS 2 SINGLE_STABLE 0 TABLE_PREFIX 0 TABLE_SUFFIX 0 TSDB_PAGESIZE 4 WAL_RETENTION_PERIOD 0 WAL_RETENTION_SIZE 0 KEEP_TIME_OFFSET 0 ENCRYPT_ALGORITHM 'none' S3_CHUNKSIZE 262144 S3_KEEPLOCAL 5256000m S3_COMPACT 0
Query OK, 1 row(s) in set (0.000889s)
taos> show create stable st_m001\G;
*************************** 1.row ***************************
Table: st_m001
Create Table: CREATE STABLE `st_m001` (`ts` TIMESTAMP ENCODE 'delta-i' COMPRESS 'lz4' LEVEL 'medium', `consume` INT ENCODE 'simple8b' COMPRESS 'lz4' LEVEL 'medium') TAGS (`devicetype` VARCHAR(16), `type` VARCHAR(16), `version` INT UNSIGNED, `batchnumber` VARCHAR(32))
Query OK, 1 row(s) in set (0.001933s)
vnode信息
taos> show vgroups;
vgroup_id | db_name | tables | v1_dnode | v1_status | v2_dnode | v2_status | v3_dnode | v3_status | v4_dnode | v4_status | cacheload | cacheelements | tsma |
======================================================================================================================================================================================================
4 | history_t005 | 597370 | 1 | leader | 2 | follower | 3 | follower | NULL | NULL | 1048528 | 10082 | 0 |
5 | history_t005 | 596294 | 1 | follower | 2 | leader | 3 | follower | NULL | NULL | 1048528 | 10082 | 0 |
Query OK, 2 row(s) in set (0.002256s)
To Reproduce
root@ip-10-1-5-151:~# taosBenchmark -f query.json_1
[05/28 06:32:31.052135] INFO: query.json_1
{
"filetype": "query",
"cfgdir": "/etc/taos",
"host": "127.0.0.1",
"port": 6030,
"user": "root",
"password": "xxx",
"confirm_parameter_prompt": "no",
"continue_if_fail": "yes",
"databases": "history_t005",
"query_times": 500,
"query_mode": "taosc",
"specified_table_query": {
"query_interval": 0,
"concurrent": 2,
"sqls": [{
"sql": "SELECT count(*) as days,sum(consume) as total from history_t005.m001_20051880605573251h4148e1e91c5ee8_0_389503",
"result": "./query_res0.txt"
}]
}
}
[05/28 06:32:31.084202] INFO: client version: 3.3.0.0
[05/28 06:33:24.259621] INFO: thread[0] has currently completed queries: 1, QPS: 0.018808
[05/28 06:33:24.260668] INFO: thread[1] has currently completed queries: 1, QPS: 0.018808
[05/28 06:33:54.545487] INFO: thread[0] has currently completed queries: 58, QPS: 0.694985
[05/28 06:33:54.630304] INFO: thread[1] has currently completed queries: 57, QPS: 0.682316
[05/28 06:34:24.604224] INFO: thread[0] has currently completed queries: 115, QPS: 1.013091
[05/28 06:34:24.690136] INFO: thread[1] has currently completed queries: 115, QPS: 1.012333
[05/28 06:34:54.767764] INFO: thread[1] has currently completed queries: 171, QPS: 1.190178
[05/28 06:34:55.267754] INFO: thread[0] has currently completed queries: 175, QPS: 1.213786
[05/28 06:35:25.199651] INFO: thread[1] has currently completed queries: 228, QPS: 1.309532
[05/28 06:35:25.336386] INFO: thread[0] has currently completed queries: 233, QPS: 1.337190
[05/28 06:35:55.574121] INFO: thread[1] has currently completed queries: 284, QPS: 1.388869
[05/28 06:35:55.814631] INFO: thread[0] has currently completed queries: 293, QPS: 1.431195
[05/28 06:36:25.951828] INFO: thread[1] has currently completed queries: 340, QPS: 1.447671
[05/28 06:36:26.400070] INFO: thread[0] has currently completed queries: 354, QPS: 1.504398
[05/28 06:36:56.163242] INFO: thread[1] has currently completed queries: 398, QPS: 1.501479
[05/28 06:36:56.455769] INFO: thread[0] has currently completed queries: 412, QPS: 1.552579
[05/28 06:37:26.647769] INFO: thread[1] has currently completed queries: 456, QPS: 1.542855
[05/28 06:37:26.808194] INFO: thread[0] has currently completed queries: 470, QPS: 1.589352
complete query with 2 threads and 500 query delay avg: 0.630899s min: 0.398531s max: 53.169216s p90: 0.700389s p95: 0.724244s p99: 0.749432s SQL command: SELECT count(history_t005.m001_20051880605573251h4148e1e91c5ee8_0_389503
[05/28 06:37:50.655246] INFO: Total specified queries: 1000
[05/28 06:37:50.655259] INFO: Spend 319.5710 second completed total queries: 1000, the QPS of all threads: 3.129
[05/28 06:37:50.655274] INFO: free resource and exit ...
Screenshots If applicable, add screenshots to help explain your problem.
Environment (please complete the following information):
- OS: [e.g. CentOS 7.0]
Linux ip-10-1-5-151 6.5.0-1017-aws #17~22.04.2-Ubuntu SMP Mon Mar 25 20:47:22 UTC 2024 aarch64 aarch64 aarch64 GNU/Linux
2c 4g * 3
Additional Context Add any other context about the problem here.
