TDEngine应用测试

应用1 壹好车服定位数据

新增专用账号

1
2
3
ALTER USER root PASS 'Lunz2017';
CREATE USER user_service PASS 'Lunz2017';
ALTER USER user_service PRIVILEGE write;

建库

1
CREATE DATABASE IF NOT EXISTS locationcenter UPDATE 2;

参数解析

INFO

  1. KEEP 是该数据库的数据保留多长天数，缺省是 3650 天(10 年)，数据库会自动删除超过时限的数据；

  2. UPDATE 标志数据库支持更新相同时间戳数据；（从 2.1.7.0 版本开始此参数支持设为 2，表示允许部分列更新，也即更新数据行时未被设置的列会保留原值。）（从 2.0.8.0 版本开始支持此参数。注意此参数不能通过ALTER DATABASE指令进行修改。）

    1. UPDATE 设为 0 时，表示不允许更新数据，后发送的相同时间戳的数据会被直接丢弃；
    2. UPDATE 设为 1 时，表示更新全部列数据，即如果更新一个数据行，其中某些列没有提供取值，那么这些列会被设为 NULL；
    3. UPDATE 设为 2 时，表示支持更新部分列数据，即如果更新一个数据行，其中某些列没有提供取值，那么这些列会保持原有数据行中的对应值；
    4. 更多关于 UPDATE 参数的用法，请参考FAQ。

  3. 数据库名最大长度为 33；

  4. 一条 SQL 语句的最大长度为 65480 个字符；

  5. 创建数据库时可用的参数有：

  6. 请注意上面列出的所有参数都可以配置在配置文件 taosd.cfg 中作为创建数据库时使用的默认配置， create database 的参数中明确指定的会覆盖配置文件中的设置。

显示当前参数

1
SHOW VARIABLES;

设置压缩值

1
ALTER DATABASE locationcenter COMP 2;

COMP 参数是指修改数据库文件压缩标志位，缺省值为 2，取值范围为 [0, 2]。0 表示不压缩，1 表示一阶段压缩，2 表示两阶段压缩。

设置副本数

1
ALTER DATABASE locationcenter REPLICA 2;

REPLICA 参数是指修改数据库副本数，取值范围 [1, 3]。在集群中使用，副本数必须小于或等于 DNODE 的数目。

设置保留天数参数

1
ALTER DATABASE locationcenter KEEP 365000;

KEEP 参数是指修改数据文件保存的天数，缺省值为 3650，取值范围 [days, 365000]，必须大于或等于 days 参数值。查看参数时有的话默认就行

设置块大小

1
ALTER DATABASE locationcenter BLOCKS 100;

BLOCKS 参数是每个 VNODE (TSDB) 中有多少 cache 大小的内存块，因此一个 VNODE 的用的内存大小粗略为（cache * blocks）。取值范围 [3, 1000]。

设置确认数

1
ALTER DATABASE locationcenter QUORUM 2;

QUORUM 参数是指数据写入成功所需要的确认数，取值范围 [1, 2]。对于异步复制，quorum 设为 1，具有 master 角色的虚拟节点自己确认即可。对于同步复制，quorum 设为 2。原则上，Quorum >= 1 并且 Quorum <= replica(副本数)，这个参数在启动一个同步模块实例时需要提供。

设置缓存

1
ALTER DATABASE locationcenter CACHELAST 3;

CACHELAST 参数控制是否在内存中缓存子表的最近数据。缺省值为 0，取值范围 [0, 1, 2, 3]。其中 0 表示不缓存，1 表示缓存子表最近一行数据，2 表示缓存子表每一列的最近的非 NULL 值，3 表示同时打开缓存最近行和列功能。（从 2.0.11.0 版本开始支持参数值 [0, 1]，从 2.1.2.0 版本开始支持参数值 [0, 1, 2, 3]。）
说明：缓存最近行，将显著改善 LAST_ROW 函数的性能表现；缓存每列的最近非 NULL 值，将显著改善无特殊影响（WHERE、ORDER BY、GROUP BY、INTERVAL）下的 LAST 函数的性能表现。

显示所有数据库及设置参数

1
2
SHOW DATABASES;
SHOW CREATE DATABASE locationcenter\G;

服务器参数优化参考 https://docs.taosdata.com/reference/config/

taos服务和taosadapter参数配置，dnode添加删除，mnode数量配置，vnode表数量多少，表在vnode分布，vnode在dnode上的分布调整。

参考文章：https://zhuanlan.zhihu.com/p/380778972

taosadapter生产环境参数，需要修改日志记录级别，修改日志记录位置（放数据盘）。

建表

实际场景解释：创建超级表，为模板表，根据实际业务，壹号车服app使用人员3000-4000人，数据写入时根据人员登录Code分不同子表存储数据，这样取数据时会每个人只从一个单子表里取值，且数据库参数CACHELAST设置为3后，每个人最新一次定位直接从缓存获取，大大加快查询速度。

1
2
3
4
5
6
7
8
9
10
11
12
13
USE `locationcenter`;
DROP STABLE IF EXISTS `userlocation`;
CREATE STABLE `userlocation` 
(`locationtime` TIMESTAMP,
 `loginname` NCHAR(50),
 `lat` DOUBLE,
 `lng` DOUBLE,
 `speed` DOUBLE,
 `address` NCHAR(200)) 
 TAGS (`username` NCHAR(50),
       `deviceimei` NCHAR(50),
       `devicemodel` NCHAR(100),
       `devicetype` BOOL);

说明

  1. 表的第一个字段必须是 TIMESTAMP，并且系统自动将其设为主键；
  2. 表名最大长度为 192；
  3. 表的每行长度不能超过 48KB;（注意：每个 BINARY/NCHAR 类型的列还会额外占用 2 个字节的存储位置）；
  4. 子表名只能由字母、数字和下划线组成，且不能以数字开头，不区分大小写；
  5. 使用数据类型 binary 或 nchar，需指定其最长的字节数，如 binary(20)，表示 20 字节；
  6. 为了兼容支持更多形式的表名，TDengine 引入新的转义符 “"，可以让表名与关键词不冲突，同时不受限于上述表名称合法性约束检查。但是同样具有长度限制要求。使用转义字符以后，不再对转义字符中的内容进行大小写统一。 例如：aBc和abc` 是不同的表名，但是 abc 和 aBc 是相同的表名。 需要注意的是转义字符中的内容必须是可打印字符。 上述的操作逻辑和约束要求与 MySQL 数据的操作一致。 从 2.3.0.0 版本开始支持这种方式；
  7. 数据迁移注意保障列长足够，表的第一个字段可以使用UNIX_TIMESTAMP格式写入，根据库的时间经度自动转换；
  8. TAGS 列的数据类型不能是 timestamp 类型；（从 2.1.3.0 版本开始，TAGS 列中支持使用 timestamp 类型，但需注意在 TAGS 中的 timestamp 列写入数据时需要提供给定值，而暂不支持四则运算，例如 NOW + 10s 这类表达式）
  9. TAGS 列名不能与其他列名相同；
  10. TAGS 列名不能为预留关键字（参见：参数限制与保留关键字 章节）；
  11. TAGS 最多允许 128 个，至少 1 个，总长度不超过 16 KB。

支持无模式表数据写入，兼容 InfluxDB 的 行协议（Line Protocol）、OpenTSDB 的 telnet 行协议、OpenTSDB 的 JSON 格式协议。

介绍：https://docs.taosdata.com/reference/schemaless/

如表列不定，随时增加可使用此模式。

支持的数据

# 类型 Bytes 说明
1 TIMESTAMP 8 时间戳。缺省精度毫秒，可支持微秒和纳秒。从格林威治时间 1970-01-01 00:00:00.000 (UTC/GMT) 开始，计时不能早于该时间。（从 2.0.18.0 版本开始，已经去除了这一时间范围限制）（从 2.1.5.0 版本开始支持纳秒精度）
2 INT 4 整型，范围 [-2^31+1, 2^31-1], -2^31 用作 NULL
3 BIGINT 8 长整型，范围 [-2^63+1, 2^63-1], -2^63 用作 NULL
4 FLOAT 4 浮点型，有效位数 6-7，范围 [-3.4E38, 3.4E38]
5 DOUBLE 8 双精度浮点型，有效位数 15-16，范围 [-1.7E308, 1.7E308]
6 BINARY 自定义 记录单字节字符串，建议只用于处理 ASCII 可见字符，中文等多字节字符需使用 nchar。理论上，最长可以有 16374 字节。binary 仅支持字符串输入，字符串两端需使用单引号引用。使用时须指定大小，如 binary(20) 定义了最长为 20 个单字节字符的字符串，每个字符占 1 byte 的存储空间，总共固定占用 20 bytes 的空间，此时如果用户字符串超出 20 字节将会报错。对于字符串内的单引号，可以用转义字符反斜线加单引号来表示，即 \’。
7 SMALLINT 2 短整型， 范围 [-32767, 32767], -32768 用作 NULL
8 TINYINT 1 单字节整型，范围 [-127, 127], -128 用作 NULL
9 BOOL 1 布尔型，{true, false}
10 NCHAR 自定义 记录包含多字节字符在内的字符串，如中文字符。每个 nchar 字符占用 4 bytes 的存储空间。字符串两端使用单引号引用，字符串内的单引号需用转义字符 \’。nchar 使用时须指定字符串大小，类型为 nchar(10) 的列表示此列的字符串最多存储 10 个 nchar 字符，会固定占用 40 bytes 的空间。如果用户字符串长度超出声明长度，将会报错。
11 JSON json 数据类型， 只有 tag 可以是 json 格式

显示建表语句

1
2
SHOW CREATE TABLE userlocation\G;
DESCRIBE userlocation;

注意：查询超级表时对表描述词要改为 STABLE。

例：

1
2
3
SHOW TABLES;
SHOW STABLES;
DROP STABLE XX;

表字段操作同MySQL，表标签相关管理：

1
2
3
4
5
6
7
8
9
10
#修改子表标签
ALTER TABLE userlocation SET TAG loginname=loginname;
#添加超级表标签
ALTER STABLE userlocation ADD TAG testtag NCHAR(50);
#修改超级表标签名
ALTER STABLE userlocation CHANGE TAG testtag testtag1;
#修改超级表列宽
ALTER STABLE stb_name MODIFY TAG testtag1 NCHAR(100);
#删除超级表标签
ALTER STABLE userlocation DROP TAG testtag1;

使用 SELECT 语句可以完成在超级表上的投影及聚合两类查询，在 WHERE 语句中可以对标签及列进行筛选及过滤。

如果在超级表查询语句中不加 ORDER BY, 返回顺序是先返回一个子表的所有数据，然后再返回下个子表的所有数据，所以返回的数据是无序的。如果增加了 ORDER BY 语句，会严格按 ORDER BY 语句指定的顺序返回的。

除了更新标签的值的操作是针对子表进行，其他所有的标签操作（添加标签、删除标签等）均只能作用于 STable，不能对单个子表操作。对 STable 添加标签以后，依托于该 STable 建立的所有表将自动增加了一个标签，所有新增标签的默认值都是 NULL。

开源版限制

1.mnode最多3个；

2.内存分配机制没有企业版控制的好；

内存OOM原因：

剩余内存小于 vm.min_free_kbytes ；

程序请求的内存大于剩余内存；

内存充足但程序占用了特殊的内存地址。

数据迁移

这里使用DATAX

☆ DATAX工具包必需使用官方提供，不然会出各种依赖包问题

亲测可用工具包下载地址：https://cloud.189.cn/t/AVbmaqmUJRf2（访问码：e1jy）

安装 datax后，此文默认DATAX_HOME:/opt/datax

迁移脚本放入 /datax/job文件夹内

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
{
  "job": {
    "content": [{
      "reader": {
        "name": "mysqlreader",
        "parameter": {
          "username": "user_service",
          "password": "Lunz2017",
          "connection": [{
            "querySql": [
                  "SELECT CONCAT(CONCAT(CONCAT('t',LoginName),'_'),DeviceIMEI) AS tbname,LocationTime,Lat,Lng,Speed,Address,LoginName,UserName,DeviceIMEI,DeviceModel,DeviceType FROM tb_userlocation WHERE LocationTime IS NOT NULL AND Lat IS NOT NULL AND Lng IS NOT NULL AND Speed IS NOT NULL AND Address IS NOT NULL AND LoginName IS NOT NULL AND UserName IS NOT NULLAND DeviceIMEI IS NOT NULL AND DeviceModel IS NOT NULL AND DeviceType IS NOT NULL;"
                ],
            "jdbcUrl": ["jdbc:mysql://pc-bp1zip05gl1b1ga3veo.rwlb.rds.aliyuncs.com:3306/locationcenter"]
          }]
        }     
},
       "writer": {
         "name": "tdenginewriter",
         "parameter": {
           "username": "user_service",
           "password": "Lunz2017",
           "column": [
               "tbname",
               "locationtime",
               "lat",
               "lng",
               "speed",
               "address",
               "loginname",
               "username",
               "deviceimei",
               "devicemodel",
               "devicetype"
             ],
           "connection": [
               {
                 "table": [
                   "userlocation"
                 ],
                 "jdbcUrl": "jdbc:TAOS-RS://192.168.200.99:6041/locationcenter",
                 "jdbcUrl": "jdbc:TAOS-RS://192.168.200.148:6041/locationcenter"
               }
             ],
           "batchSize": 10000,
           "ignoreTagsUnmatched": true
         }
      }
    }],
    "setting": {
      "speed": {
        "channel": 3,
        "byte" : 5242880
      }
    }
  }
}

执行迁移

数据使用DATAX迁移，速度较慢

优化方式

速度控制：

迁移 json脚本中直接设置

job.setting.speed.channel 总并发数
job.setting.speed.record 总record限速
job.setting.speed.byte 总byte限速
core.transport.channel.speed.record 单个channel的record限速，默认值为10000（10000条/s）
core.transport.channel.speed.byte 单个channel的byte限速，默认值1024*1024（1M/s）

注意事项：

  1. 若配置了总record限速，则必须配置单个channel的record限速
  2. 若配置了总byte限速，则必须配置单个channe的byte限速
  3. 若配置了总record限速和总byte限速，channel并发数参数就会失效。因为配置了总record限速和总byte限速之后，实际channel并发数是通过计算得到的：

计算公式为:

min(总byte限速/单个channle的byte限速，总record限速/单个channel的record限速)

配置示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
{
    "core": {
        "transport": {
            "channel": {
                "speed": {
                    "byte": 1048576 //单个channel byte限速1M/s
                }
            }
        }
    },
    "job": {
        "setting": {
            "speed": {
                "byte" : 5242880 //总byte限速5M/s
            }
        },
        ...
    }
}

内存调整

当提升DataX Job内Channel并发数时，内存的占用会显著增加，因为DataX作为数据交换通道，在内存中会缓存较多的数据。例如Channel中会有一个Buffer，作为临时的数据交换的缓冲区，而在部分Reader和Writer的中，也会存在一些Buffer，为了防止OOM等错误，需调大JVM的堆内存。

调整JVM xms xmx参数的两种方式：一种是直接更改datax.py脚本；另一种是在启动的时候，加上对应的参数，如下：

1
python /newdisk/soft/datax/bin/datax.py --jvm="-Xms6G -Xmx6G" /newdisk/soft/datax/job/mysql2td_location.json

  • -Xms8G：运行的最小分配内存，如果可用内存不足5G，这个命令将不能被执行。

  • -Xmx8G：运行时最大占用内存大小。

最终优化后json任务文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
{
  "job": {
    "content": [{
      "reader": {
        "name": "mysqlreader",
        "parameter": {
          "username": "user_service",
          "password": "Lunz2017",
          "splitPk": "Id",
          "column":[
                "td_tablebane",
                "LocationTime",
                "Lat",
                "Lng",
                "Speed",
                "Address",
                "LoginName",
                "UserName",
                "DeviceIMEI",
                "DeviceModel",
                "DeviceType"
         ],
        "connection": [{
            "table": ["tb_userlocation_td3"],
            "jdbcUrl": ["jdbc:mysql://pc-bp1zip05gl1b1ga3veo.rwlb.rds.aliyuncs.com:3306/locationcenter"]
          }]
        }
      },
      "writer": {
        "name": "tdenginewriter",
        "parameter": {
          "username": "user_service",
          "password": "Lunz2017",
          "column": [
              "tbname",
              "locationtime",
              "lat",
              "lng",
              "speed",
              "address",
              "loginname",
              "username",
              "deviceimei",
              "devicemodel",
              "devicetype"
            ],
          "connection": [
              {
                "table": [
                  "userlocation"
                ],
                "jdbcUrl": "jdbc:TAOS-RS://192.168.200.99:6041/locationcenter"
              }
            ],
          "batchSize": 10000,
          "ignoreTagsUnmatched": true
        }
      }
    }],
    "setting": {
      "speed": {
        "byte" : 15728640
      }
    }
  },
"core": {
        "transport": {
            "channel": {
                "speed": {
                    "byte": 5242880
                }
            }
        }
    }
}

执行脚本

1
2
3
4
5
6
7
8
9
10
11
#新建日志文档，记得定时清理，比较大
touch /opt/datax/log/mysql2td_location.log
#后台执行迁移脚本，因数据量较大时间较久，为防止session断掉，后台运行
nohup python /opt/datax/bin/datax.py --jvm="-Xms5G -Xmx5G" /opt/datax/job/mysql2td_location.json >> /opt/datax/log/mysql2td_location.log 2>&1 &
#查看日志
tail -200f /opt/datax/log/mysql2td_location.log
#日志太大了，不要日志
nohup python /newdisk/soft/datax/bin/datax.py --jvm="-Xms6G -Xmx6G" /newdisk/soft/datax/job/mysql2td_location.json >/dev/null 2>log &
#因为是后台运行 执行完毕需要杀掉进程
ps -ef|grep datax
kill pid

应用数据压测对比

测试数据：1.3亿条

存储格式：超级表加单表

服务器：双节点4C8G

查询单人3天内所有定位点并按定位时间排序：

1
2
3
4
5
6
SELECT *
FROM userlocation 
WHERE locationtime > now-750d 
AND loginname='ZR18080096'
ORDER BY locationtime 
LIMIT 10;

平均用时：Query OK, 10 row(s) in set (0.029506s)

查询每个人最新一次定位

1
2
3
SELECT LAST(*) 
FROM userlocation 
WHERE loginname='ZR18080096';

平均用时：Query OK, 1 row(s) in set (0.004578s)

使用taosBenchmark测试TD性能

压测使用超级表时，经常会在WHERE条件里的需要筛选的字段，放在字段里不要放TAG内

  • 时序数据：存放于 vnode 里，由 data、head 和 last 三个文件组成，数据量大，查询量取决于应用场景。容许乱序写入，但暂时不支持删除操作，并且仅在 update 参数设置为 1 时允许更新操作。通过采用一个采集点一张表的模型，一个时间段的数据是连续存储，对单张表的写入是简单的追加操作，一次读，可以读到多条记录，这样保证对单个采集点的插入和查询操作，性能达到最优。
  • 标签数据：存放于 vnode 里的 meta 文件，支持增删改查四个标准操作。数据量不大，有 N 张表，就有 N 条记录，因此可以全内存存储。如果标签过滤操作很多，查询将十分频繁，因此 TDengine 支持多核多线程并发查询。只要计算资源足够，即使有数千万张表，过滤结果能毫秒级返回。
  • 元数据：存放于 mnode 里，包含系统节点、用户、DB、Table Schema 等信息，支持增删改查四个标准操作。这部分数据的量不大，可以全内存保存，而且由于客户端有缓存，查询量也不大。因此目前的设计虽是集中式存储管理，但不会构成性能瓶颈。

工具介绍: https://www.taosdata.com/docs/cn/v2.0/tools/taosbenchmark#cli

检测脚本

1
touch /newdisk/soft/taosbenchmark/locationsearch.json
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
{
  "filetype": "query",
  "cfgdir": "/etc/taos",
  "host": "192.168.200.99",
  "port": 6030,
  "user": "user_service",
  "password": "Lunz2017",
  "confirm_parameter_prompt": "no",
  "databases": "locationcenter",
  "query_times": 200,
  "query_mode": "taosc",
  "super_table_query": {
     "stblname": "userlocation",
     "query_interval": 0,
     "threads": 5,
     "sqls": [
     {
       "sql": "SELECT * FROM xxxx WHERE locationtime > now-750d AND loginname='ZR18080096' ORDER BY locationtime LIMIT 1000;"
      },
      {
        "sql": "SELECT LAST(*) FROM xxxx WHERE loginname='ZR18080096';"
       } 
     ]
   }
}

执行指令

1
taosdemo -f /newdisk/soft/taosbenchmark/locationsearch.json -o /newdisk/soft/taosbenchmark/locationsearch.txt

第一次测试WHERE 筛选条件里不带loginname筛选

检测结果：

展开查看测试结果 
 
[06/23 11:25:29.826294] INFO: /newdisk/soft/taosbenchmark/locationsearch.json
{
        "filetype":     "query",
        "cfgdir":       "/etc/taos",
        "host": "192.168.200.99",
        "port": 6030,
        "user": "user_service",
        "password":     "Lunz2017",
        "confirm_parameter_prompt":     "no",
        "databases":    "locationcenter",
        "query_times":  20,
        "query_mode":   "taosc",
        "super_table_query":    {
                "stblname":     "userlocation",
                "query_interval":       0,
                "threads":      5,
                "sqls": [{
                                "sql":  "SELECT * FROM xxxx WHERE locationtime > now-750d ORDER BY locationtime LIMIT 1000;"
                        }, {
                                "sql":  "SELECT LAST(*) FROM xxxx;"
                        }]
        }
}
[06/23 11:25:29.942103] INFO: taos client version: 2.6.0.0
[06/23 11:25:29.985904] INFO: userlocation's childTblCount: 2204
[06/23 11:25:47.395394] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:17.3860s
[06/23 11:25:47.661997] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:17.6520s
[06/23 11:25:49.801203] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:19.7930s
[06/23 11:25:51.781808] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:21.7750s
[06/23 11:25:52.382326] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:22.3750s
[06/23 11:26:00.011229] INFO: thread[4] has currently completed queries: 1531, QPS:     51.030
[06/23 11:26:00.014133] INFO: thread[3] has currently completed queries: 1534, QPS:     51.125
[06/23 11:26:00.024295] INFO: thread[2] has currently completed queries: 1369, QPS:     45.609
[06/23 11:26:00.024452] INFO: thread[1] has currently completed queries: 1201, QPS:     40.011
[06/23 11:26:00.059128] INFO: thread[0] has currently completed queries: 1241, QPS:     41.294
[06/23 11:26:04.529191] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:17.1340s
[06/23 11:26:04.737206] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:17.0750s
[06/23 11:26:08.044346] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:18.2430s
[06/23 11:26:13.127916] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:21.3460s
[06/23 11:26:13.680380] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:21.2980s
[06/23 11:26:21.806175] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:17.2770s
[06/23 11:26:22.789503] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:18.0520s
[06/23 11:26:26.866865] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:18.8220s
[06/23 11:26:30.013590] INFO: thread[4] has currently completed queries: 3067, QPS:     51.113
[06/23 11:26:30.017715] INFO: thread[3] has currently completed queries: 3023, QPS:     50.377
[06/23 11:26:30.068283] INFO: thread[0] has currently completed queries: 2470, QPS:     41.124
[06/23 11:26:30.072142] INFO: thread[1] has currently completed queries: 2441, QPS:     40.639
[06/23 11:26:30.081825] INFO: thread[2] has currently completed queries: 2781, QPS:     46.294
[06/23 11:26:33.868119] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:20.7410s
[06/23 11:26:34.794845] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:21.1140s
[06/23 11:26:38.007910] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:16.2010s
[06/23 11:26:39.027625] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:16.2380s
[06/23 11:26:44.349937] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:17.4830s
[06/23 11:26:53.690002] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:19.8220s
[06/23 11:26:54.179681] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:16.1720s
[06/23 11:26:54.751370] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:19.9570s
[06/23 11:26:55.450360] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:16.4230s
[06/23 11:27:00.032487] INFO: thread[4] has currently completed queries: 4689, QPS:     52.087
[06/23 11:27:00.036375] INFO: thread[3] has currently completed queries: 4625, QPS:     51.373
[06/23 11:27:00.081331] INFO: thread[1] has currently completed queries: 3736, QPS:     41.477
[06/23 11:27:00.093663] INFO: thread[0] has currently completed queries: 3794, QPS:     42.115
[06/23 11:27:00.111722] INFO: thread[2] has currently completed queries: 4303, QPS:     47.756
[06/23 11:27:02.482377] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:18.1320s
[06/23 11:27:12.567807] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:18.3880s
[06/23 11:27:15.218884] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:19.7680s
[06/23 11:27:16.970065] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:23.2800s
[06/23 11:27:18.378608] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:23.6270s
[06/23 11:27:23.262542] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:20.7800s
[06/23 11:27:30.036647] INFO: thread[4] has currently completed queries: 6052, QPS:     50.422
[06/23 11:27:30.040622] INFO: thread[3] has currently completed queries: 5937, QPS:     49.462
[06/23 11:27:30.096812] INFO: thread[0] has currently completed queries: 4880, QPS:     40.636
[06/23 11:27:30.111611] INFO: thread[1] has currently completed queries: 4829, QPS:     40.207
[06/23 11:27:30.121808] INFO: thread[2] has currently completed queries: 5556, QPS:     46.256
[06/23 11:27:31.958135] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:19.3910s
[06/23 11:27:34.558165] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:19.3400s
[06/23 11:27:39.456386] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:22.4860s
[06/23 11:27:40.932419] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:22.5540s
[06/23 11:27:41.732982] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:18.4700s
[06/23 11:27:48.320896] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:16.3620s
[06/23 11:27:50.724743] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:16.1660s
[06/23 11:27:58.327522] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:16.5940s
[06/23 11:28:00.021201] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:20.5650s
[06/23 11:28:00.053498] INFO: thread[4] has currently completed queries: 7668, QPS:     51.105
[06/23 11:28:00.069814] INFO: thread[3] has currently completed queries: 7541, QPS:     50.253
[06/23 11:28:00.107871] INFO: thread[0] has currently completed queries: 6178, QPS:     41.159
[06/23 11:28:00.117358] INFO: thread[1] has currently completed queries: 6139, QPS:     40.897
[06/23 11:28:00.129142] INFO: thread[2] has currently completed queries: 7133, QPS:     47.515
[06/23 11:28:00.883925] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:19.9510s
[06/23 11:28:04.528633] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:16.2080s
[06/23 11:28:07.278929] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:16.5540s
[06/23 11:28:16.284883] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:17.9570s
[06/23 11:28:20.968207] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:20.9470s
[06/23 11:28:21.440716] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:16.9120s
[06/23 11:28:21.903253] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:21.0200s
[06/23 11:28:23.760206] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:16.4820s
[06/23 11:28:30.081073] INFO: thread[3] has currently completed queries: 9175, QPS:     50.952
[06/23 11:28:30.085854] INFO: thread[4] has currently completed queries: 9275, QPS:     51.506
[06/23 11:28:30.127840] INFO: thread[1] has currently completed queries: 7423, QPS:     41.211
[06/23 11:28:30.133642] INFO: thread[2] has currently completed queries: 8664, QPS:     48.100
[06/23 11:28:30.171622] INFO: thread[0] has currently completed queries: 7459, QPS:     41.401
[06/23 11:28:33.132875] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:16.8480s
[06/23 11:28:38.366290] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:16.9260s
[06/23 11:28:40.760953] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:17.0000s
[06/23 11:28:42.121350] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:21.1530s
[06/23 11:28:43.137537] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:21.2340s
[06/23 11:28:51.169437] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:18.0370s
[06/23 11:28:55.081240] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:16.7150s
[06/23 11:28:57.246965] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:16.4850s
[06/23 11:29:00.082296] INFO: thread[3] has currently completed queries: 10722, QPS:     51.039
[06/23 11:29:00.120325] INFO: thread[4] has currently completed queries: 10817, QPS:     51.482
[06/23 11:29:00.149787] INFO: thread[1] has currently completed queries: 8681, QPS:     41.310
[06/23 11:29:00.164720] INFO: thread[2] has currently completed queries: 10131, QPS:     48.207
[06/23 11:29:00.183676] INFO: thread[0] has currently completed queries: 8708, QPS:     41.432
[06/23 11:29:03.182366] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:21.0610s
[06/23 11:29:03.668377] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:20.5310s
[06/23 11:29:09.831966] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:18.6620s
[06/23 11:29:13.002151] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:17.9210s
[06/23 11:29:15.903543] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:18.6560s
[06/23 11:29:24.725181] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:21.5430s
[06/23 11:29:25.210472] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:21.5420s
[06/23 11:29:27.849301] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:18.0170s
[06/23 11:29:30.002702] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:17.0000s
[06/23 11:29:30.085149] INFO: thread[3] has currently completed queries: 12182, QPS:     50.742
[06/23 11:29:30.150275] INFO: thread[4] has currently completed queries: 12325, QPS:     51.324
[06/23 11:29:30.157143] INFO: thread[1] has currently completed queries: 9895, QPS:     41.203
[06/23 11:29:30.199164] INFO: thread[2] has currently completed queries: 11555, QPS:     48.108
[06/23 11:29:30.208238] INFO: thread[0] has currently completed queries: 9915, QPS:     41.278
[06/23 11:29:33.398296] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:17.4950s
[06/23 11:29:45.909740] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:18.0600s
[06/23 11:29:46.418577] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:21.6930s
[06/23 11:29:46.684370] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:21.4740s
[06/23 11:29:46.867443] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:16.8650s
[06/23 11:29:49.834367] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:16.4360s
[06/23 11:30:00.094313] INFO: thread[3] has currently completed queries: 13804, QPS:     51.110
[06/23 11:30:00.168550] INFO: thread[4] has currently completed queries: 13965, QPS:     51.692
[06/23 11:30:00.178332] INFO: thread[1] has currently completed queries: 11213, QPS:     41.503
[06/23 11:30:00.204815] INFO: thread[2] has currently completed queries: 13112, QPS:     48.528
[06/23 11:30:00.211187] INFO: thread[0] has currently completed queries: 11214, QPS:     41.502
[06/23 11:30:02.210162] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:15.3430s
[06/23 11:30:02.611121] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:16.7020s
[06/23 11:30:05.967470] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:16.1330s
[06/23 11:30:06.086602] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:19.6680s
[06/23 11:30:06.175547] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:19.4910s
[06/23 11:30:19.380477] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:17.1700s
[06/23 11:30:20.895797] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:18.2840s
[06/23 11:30:23.153220] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:17.1860s
[06/23 11:30:27.048434] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:20.8730s
[06/23 11:30:27.383756] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:21.2970s
[06/23 11:30:30.124032] INFO: thread[3] has currently completed queries: 15317, QPS:     51.037
[06/23 11:30:30.184927] INFO: thread[4] has currently completed queries: 15484, QPS:     51.583
[06/23 11:30:30.190163] INFO: thread[1] has currently completed queries: 12463, QPS:     41.518
[06/23 11:30:30.207185] INFO: thread[2] has currently completed queries: 14500, QPS:     48.301
[06/23 11:30:30.222273] INFO: thread[0] has currently completed queries: 12457, QPS:     41.493
[06/23 11:30:38.852287] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:19.4720s
[06/23 11:30:41.832871] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:20.9370s
[06/23 11:30:43.885670] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:20.7320s
[06/23 11:30:52.244190] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:25.1960s
[06/23 11:30:52.909152] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:25.5260s
[06/23 11:30:59.190679] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:20.3380s
[06/23 11:31:00.148485] INFO: thread[3] has currently completed queries: 16563, QPS:     50.170
[06/23 11:31:00.193099] INFO: thread[4] has currently completed queries: 16768, QPS:     50.784
[06/23 11:31:00.196303] INFO: thread[1] has currently completed queries: 13525, QPS:     40.961
[06/23 11:31:00.208392] INFO: thread[2] has currently completed queries: 15740, QPS:     47.668
[06/23 11:31:00.227029] INFO: thread[0] has currently completed queries: 13500, QPS:     40.882
[06/23 11:31:03.109165] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:21.2770s
[06/23 11:31:04.170431] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:20.2850s
[06/23 11:31:14.628583] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:22.3840s
[06/23 11:31:15.104783] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:22.1950s
[06/23 11:31:16.755738] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:17.5650s
[06/23 11:31:20.150011] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:17.0410s
[06/23 11:31:20.423057] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:16.2530s
[06/23 11:31:30.207574] INFO: thread[1] has currently completed queries: 14947, QPS:     41.496
[06/23 11:31:30.231212] INFO: thread[2] has currently completed queries: 17467, QPS:     48.489
[06/23 11:31:30.239761] INFO: thread[0] has currently completed queries: 14937, QPS:     41.465
[06/23 11:31:30.896999] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:16.2680s
[06/23 11:31:31.153452] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:16.0490s
[06/23 11:31:32.208711] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:12.0580s
[06/23 11:31:44.384336] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:13.4870s
[06/23 11:31:44.598594] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:13.4450s
[06/23 11:31:57.261829] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:12.8770s
[06/23 11:31:57.762974] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:13.1640s
[06/23 11:32:00.217199] INFO: thread[1] has currently completed queries: 16931, QPS:     43.389
[06/23 11:32:00.255773] INFO: thread[0] has currently completed queries: 16907, QPS:     43.324
[06/23 11:32:11.843653] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:14.5820s
[06/23 11:32:12.100391] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:14.3370s
[06/23 11:32:12.101175] INFO: Spend 402.0970 second completed total queries: 88160, the QPS of all threads:    219.251

QPS大概是295

第二次测试为上面全部查询条件，带WHERE筛选

展开查看测试结果 
 
[06/23 15:56:16.741911] INFO: /newdisk/soft/taosbenchmark/locationsearch.json
{
        "filetype":     "query",
        "cfgdir":       "/etc/taos",
        "host": "192.168.200.99",
        "port": 6030,
        "user": "user_service",
        "password":     "Lunz2017",
        "confirm_parameter_prompt":     "no",
        "databases":    "locationcenter",
        "query_times":  20,
        "query_mode":   "taosc",
        "super_table_query":    {
                "stblname":     "userlocation",
                "query_interval":       0,
                "threads":      5,
                "sqls": [{
                                "sql":  "SELECT * FROM xxxx WHERE locationtime > now-750d AND loginname='ZR18080096' ORDER BY locationtime LIMIT 1000;"
                        }, {
                                "sql":  "SELECT LAST(*) FROM xxxx WHERE loginname='ZR18080096';"
                        }]
        }
}
[06/23 15:56:16.882414] INFO: taos client version: 2.6.0.0
[06/23 15:56:16.910498] INFO: userlocation's childTblCount: 2204
[06/23 15:56:29.610277] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:12.6770s
[06/23 15:56:29.851190] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.9170s
[06/23 15:56:30.948195] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:14.0150s
[06/23 15:56:33.864315] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:16.9320s
[06/23 15:56:34.081614] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:17.1500s
[06/23 15:56:42.131075] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.2800s
[06/23 15:56:42.348381] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:12.7380s
[06/23 15:56:45.165683] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:14.2170s
[06/23 15:56:46.935699] INFO: thread[0] has currently completed queries: 1582, QPS:     52.726
[06/23 15:56:46.941998] INFO: thread[3] has currently completed queries: 2098, QPS:     69.915
[06/23 15:56:46.949207] INFO: thread[1] has currently completed queries: 1561, QPS:     52.004
[06/23 15:56:46.964167] INFO: thread[4] has currently completed queries: 2083, QPS:     69.364
[06/23 15:56:46.965832] INFO: thread[2] has currently completed queries: 1889, QPS:     62.900
[06/23 15:56:50.149224] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:16.2850s
[06/23 15:56:50.215609] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:16.1340s
[06/23 15:56:54.128171] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:11.7800s
[06/23 15:56:54.502407] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.3710s
[06/23 15:56:58.316296] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:13.1510s
[06/23 15:57:06.099179] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:15.9500s
[06/23 15:57:06.300253] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:16.0850s
[06/23 15:57:06.536024] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:12.4080s
[06/23 15:57:06.668328] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.1660s
[06/23 15:57:11.682409] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:13.3660s
[06/23 15:57:16.957269] INFO: thread[3] has currently completed queries: 4237, QPS:     70.588
[06/23 15:57:16.969086] INFO: thread[4] has currently completed queries: 4220, QPS:     70.292
[06/23 15:57:16.970547] INFO: thread[0] has currently completed queries: 3227, QPS:     53.748
[06/23 15:57:16.973480] INFO: thread[1] has currently completed queries: 3215, QPS:     53.547
[06/23 15:57:16.985694] INFO: thread[2] has currently completed queries: 3767, QPS:     62.729
[06/23 15:57:19.170242] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:12.6340s
[06/23 15:57:19.243252] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.5750s
[06/23 15:57:22.138964] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:16.0390s
[06/23 15:57:22.268433] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:15.9680s
[06/23 15:57:25.518519] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:13.8360s
[06/23 15:57:31.449882] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:12.2790s
[06/23 15:57:31.857031] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.6140s
[06/23 15:57:37.294125] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:15.1550s
[06/23 15:57:37.499734] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:15.2310s
[06/23 15:57:38.831634] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:13.3130s
[06/23 15:57:44.038288] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:12.5890s
[06/23 15:57:44.764270] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.9070s
[06/23 15:57:46.961418] INFO: thread[3] has currently completed queries: 6376, QPS:     70.822
[06/23 15:57:46.974507] INFO: thread[0] has currently completed queries: 4943, QPS:     54.896
[06/23 15:57:46.976399] INFO: thread[4] has currently completed queries: 6343, QPS:     70.445
[06/23 15:57:46.980930] INFO: thread[1] has currently completed queries: 4943, QPS:     54.893
[06/23 15:57:46.992588] INFO: thread[2] has currently completed queries: 5799, QPS:     64.391
[06/23 15:57:52.125702] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:13.2940s
[06/23 15:57:53.080700] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:15.7860s
[06/23 15:57:53.433868] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:15.9340s
[06/23 15:57:56.327224] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:11.5630s
[06/23 15:57:56.615254] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:12.5770s
[06/23 15:58:05.933754] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:13.8080s
[06/23 15:58:09.475644] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:13.1480s
[06/23 15:58:09.672404] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:16.2390s
[06/23 15:58:09.914506] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:16.8340s
[06/23 15:58:09.956917] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:13.3410s
[06/23 15:58:16.966921] INFO: thread[3] has currently completed queries: 8454, QPS:     70.431
[06/23 15:58:16.977414] INFO: thread[0] has currently completed queries: 6587, QPS:     54.871
[06/23 15:58:16.990589] INFO: thread[1] has currently completed queries: 6565, QPS:     54.682
[06/23 15:58:17.000403] INFO: thread[2] has currently completed queries: 7772, QPS:     64.731
[06/23 15:58:17.001556] INFO: thread[4] has currently completed queries: 8453, QPS:     70.402
[06/23 15:58:19.212969] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:13.2790s
[06/23 15:58:21.579566] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.1040s
[06/23 15:58:21.933175] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:11.9760s
[06/23 15:58:25.113474] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:15.4410s
[06/23 15:58:25.959218] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:16.0450s
[06/23 15:58:33.001226] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:13.7880s
[06/23 15:58:34.034189] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.4550s
[06/23 15:58:34.435996] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:12.5020s
[06/23 15:58:41.647532] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:16.5340s
[06/23 15:58:42.139212] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:16.1800s
[06/23 15:58:46.983853] INFO: thread[0] has currently completed queries: 8220, QPS:     54.781
[06/23 15:58:46.987185] INFO: thread[3] has currently completed queries: 10563, QPS:     70.395
[06/23 15:58:47.005994] INFO: thread[2] has currently completed queries: 9694, QPS:     64.596
[06/23 15:58:47.017921] INFO: thread[1] has currently completed queries: 8197, QPS:     54.616
[06/23 15:58:47.019911] INFO: thread[4] has currently completed queries: 10543, QPS:     70.247
[06/23 15:58:47.106212] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:14.1050s
[06/23 15:58:47.251632] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:12.8150s
[06/23 15:58:47.281396] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:13.2470s
[06/23 15:58:57.020348] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:15.3730s
[06/23 15:58:57.287225] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:15.1480s
[06/23 15:58:59.345230] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.0640s
[06/23 15:58:59.431883] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:12.1800s
[06/23 15:59:00.536989] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:13.4300s
[06/23 15:59:11.005123] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:11.6600s
[06/23 15:59:11.162868] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:11.7310s
[06/23 15:59:12.037817] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:15.0170s
[06/23 15:59:12.621252] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:15.3340s
[06/23 15:59:13.415012] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:12.8780s
[06/23 15:59:16.996454] INFO: thread[0] has currently completed queries: 9981, QPS:     55.430
[06/23 15:59:17.022996] INFO: thread[4] has currently completed queries: 12739, QPS:     70.738
[06/23 15:59:17.024869] INFO: thread[3] has currently completed queries: 12735, QPS:     70.714
[06/23 15:59:17.042181] INFO: thread[1] has currently completed queries: 9931, QPS:     55.139
[06/23 15:59:17.079183] INFO: thread[2] has currently completed queries: 11653, QPS:     64.686
[06/23 15:59:23.204856] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.1990s
[06/23 15:59:23.588289] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:12.4260s
[06/23 15:59:26.897799] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:13.4820s
[06/23 15:59:27.305009] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:15.2680s
[06/23 15:59:27.927708] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:15.3060s
[06/23 15:59:35.304509] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.1000s
[06/23 15:59:36.224949] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:12.6360s
[06/23 15:59:40.658812] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:13.7610s
[06/23 15:59:43.117408] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:15.8120s
[06/23 15:59:43.584076] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:15.6570s
[06/23 15:59:46.998960] INFO: thread[0] has currently completed queries: 11670, QPS:     55.554
[06/23 15:59:47.026291] INFO: thread[4] has currently completed queries: 14896, QPS:     70.902
[06/23 15:59:47.035069] INFO: thread[3] has currently completed queries: 14879, QPS:     70.818
[06/23 15:59:47.055110] INFO: thread[1] has currently completed queries: 11631, QPS:     55.353
[06/23 15:59:47.117361] INFO: thread[2] has currently completed queries: 13581, QPS:     64.615
[06/23 15:59:47.813407] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.5090s
[06/23 15:59:48.530546] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:12.3050s
[06/23 15:59:54.360848] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:13.7020s
[06/23 15:59:59.613625] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:16.4960s
[06/23 16:00:00.209528] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:16.6250s
[06/23 16:00:00.379573] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.5660s
[06/23 16:00:01.540119] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:13.0100s
[06/23 16:00:07.864559] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:13.5040s
[06/23 16:00:12.572445] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:12.1930s
[06/23 16:00:13.713394] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:12.1730s
[06/23 16:00:15.147517] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:15.5340s
[06/23 16:00:15.204324] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:14.9950s
[06/23 16:00:17.001404] INFO: thread[0] has currently completed queries: 13332, QPS:     55.534
[06/23 16:00:17.031175] INFO: thread[4] has currently completed queries: 17047, QPS:     71.000
[06/23 16:00:17.041008] INFO: thread[3] has currently completed queries: 17005, QPS:     70.822
[06/23 16:00:17.057005] INFO: thread[1] has currently completed queries: 13331, QPS:     55.517
[06/23 16:00:17.129278] INFO: thread[2] has currently completed queries: 15597, QPS:     64.934
[06/23 16:00:20.918288] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:13.0540s
[06/23 16:00:24.169922] INFO: thread[4] complete all sqls to allocate all sub-tables[1764 - 2203] once queries duration:11.5970s
[06/23 16:00:24.723675] INFO: thread[3] complete all sqls to allocate all sub-tables[1323 - 1763] once queries duration:11.0100s
[06/23 16:00:29.377737] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:14.1730s
[06/23 16:00:29.435308] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:14.2880s
[06/23 16:00:31.775970] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:10.8570s
[06/23 16:00:41.958418] INFO: thread[2] complete all sqls to allocate all sub-tables[882 - 1322] once queries duration:10.1820s
[06/23 16:00:42.163680] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:12.7280s
[06/23 16:00:42.360845] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:12.9830s
[06/23 16:00:47.006939] INFO: thread[0] has currently completed queries: 15361, QPS:     56.877
[06/23 16:00:47.073863] INFO: thread[1] has currently completed queries: 15353, QPS:     56.833
[06/23 16:00:53.858526] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:11.6950s
[06/23 16:00:54.201840] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:11.8410s
[06/23 16:01:06.349354] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:12.1480s
[06/23 16:01:06.422182] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:12.5640s
[06/23 16:01:17.034805] INFO: thread[0] has currently completed queries: 17509, QPS:     58.343
[06/23 16:01:17.090599] INFO: thread[1] has currently completed queries: 17481, QPS:     58.239
[06/23 16:01:18.832644] INFO: thread[0] complete all sqls to allocate all sub-tables[0 - 440] once queries duration:12.4100s
[06/23 16:01:19.128519] INFO: thread[1] complete all sqls to allocate all sub-tables[441 - 881] once queries duration:12.7790s
[06/23 16:01:19.128796] INFO: Spend 302.1980 second completed total queries: 88160, the QPS of all threads:    291.729

QPS影响不大，219

RDS测试使用mysql自带mysqlslap

并发、SQL语句保持与TD一致

1
2
SELECT * FROM tb_userlocation_td3 WHERE locationtime > DATE_ADD(NOW(),INTERVAL -750 DAY) AND ORDER BY locationtime LIMIT 1000;
SELECT * FROM tb_userlocation_td3 ORDER BY locationtime DESC LIMIT 1;
1
mysqlslap -hpc-bp1zip05gl1b1ga3v.rwlb.rds.aliyuncs.com --create-schema=locationcenter -uuser_service -pLunz2017 --number-of-queries=200 --iterations=5 --concurrency=200 --engine=innodb --query=/opt/userlocationslap.sql --verbose

第一次压测结果，WHERE 条件中不加loginname筛选条件

展开查看测试结果 

Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.163 seconds
        Minimum number of seconds to run all queries: 0.141 seconds
        Maximum number of seconds to run all queries: 0.216 seconds
        Number of clients running queries: 5
        Average number of queries per client: 40

image-20220623171426893

换算为QPS后，大概 235左右

第二次压测，SQL脚本为：

1
2
SELECT * FROM tb_userlocation_td3 WHERE locationtime > DATE_ADD(NOW(),INTERVAL -750 DAY) AND loginname='ZR18080096' ORDER BY locationtime LIMIT 1000;
SELECT * FROM tb_userlocation_td3 WHERE loginname='ZR18080096' ORDER BY locationtime DESC LIMIT 1;

这样压测，RDS直接挂了，没结果。

RDS测试使用sysbench

工具优点是测试项全面，

安装工具

1
2
3
4
5
yum -y install sysbench
#如果没有此包就安装三方源后再执行
yum -y install epel-release
#查看版本
sysbench --version

因工具不能指定查询语句，只能添加测试表测试了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
#准备测试脚本
mysqladmin -hpc-bp1zip05gl1b1ga3v.rwlb.rds.aliyuncs.com -uuser_service -pLunz2017 -P3306 create sbtest;

sysbench --mysql-host=pc-bp1zip05gl1b1ga3v.rwlb.rds.aliyuncs.com \
         --mysql-port=3306 \
         --mysql-user=user_service \
         --mysql-password=Lunz2017 \
         --mysql-db=locationcenter \
         /usr/share/sysbench/oltp_common.lua \
         --tables=10 \
         --table_size=100000 \
         prepare
#压测脚本
sysbench --db-driver=mysql --time=30 --threads=5 --report-interval=1 \
         --mysql-host=pc-bp1zip05gl1b1ga3v.rwlb.rds.aliyuncs.com \
         --mysql-port=3306 \
         --mysql-user=user_service \
         --mysql-password=Lunz2017 \
         --mysql-db=locationcenter --tables=10 --table_size=1000000 \
         /usr/share/sysbench/oltp_point_select.lua \
         --db-ps-mode=disable run
#清理测试数据
sysbench --db-driver=mysql --time=30 --threads=5 --report-interval=1 \
         --mysql-host=pc-bp1zip05gl1b1ga3v.rwlb.rds.aliyuncs.com \
         --mysql-port=3306 \
         --mysql-user=user_service \
         --mysql-password=Lunz2017 \
         --mysql-db=locationcenter --tables=10 --table_size=1000000 \
         oltp_read_only --db-ps-mode=disable cleanup

压测结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL statistics:
    queries performed:
        read:                            429542
        write:                           0
        other:                           0
        total:                           429542
    transactions:                        429542 (14316.25 per sec.)
    queries:                             429542 (14316.25 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
    total time:                          30.0025s
    total number of events:              429542
Latency (ms):
         min:                                    0.23
         avg:                                    0.35
         max:                                   42.47
         95th percentile:                        0.43
         sum:                               149842.12
Threads fairness:
    events (avg/stddev):           85908.4000/2960.38
    execution time (avg/stddev):   29.9684/0.00

应用2 三方监控

StatsD监控网络，collectd监控服务器硬件，配合garfna前台展示，均使用TDengine存储数据

安装grafana监控

安装包下载地址：https://cloud.189.cn/t/7bUb2eBRfM7f（访问码：4bdl）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
sudo yum install grafana-8.5.3-1.x86_64.rpm
#查看cli版本  
grafana-cli -V
#安装td插件
sudo -u grafana grafana-cli \
  --pluginUrl https://github.com/taosdata/grafanaplugin/releases/download/v3.1.3/tdengine-datasource-3.1.3.zip \
  plugins install tdengine-datasource
#编辑配置文件
/etc/grafana/grafana.ini
[plugins]
allow_loading_unsigned_plugins = tdengine-datasource
#重启服务
sudo systemctl restart grafana-server
#如果启动失败，就执行下这个
yum install polkit -y

配合TDinsight监控数据库
配置及监控项解释： https://docs.taosdata.com/reference/tdinsight/
监控脚本下载地址（内网机器可能加载不了脚本，json格式上传）：https://cloud.189.cn/t/nQrEJzAvmiYj（访问码：b3ea）

StatsD安装

下载安装包

https://github.com/statsd/statsd

云：https://cloud.189.cn/t/AFnEzuAfENVb（访问码：9els）

安装nodejs

https://nodejs.org/en/download

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#解压
xz -d node-xxxx.tar.xz
tar -xvf node-xxxx.tar
#移动到文件夹
mv node-xxx /usr/local/node
#建立软连接
ln -s /usr/local/node/bin/node /usr/bin/node
ln -s /usr/local/node/bin/npm /usr/bin/npm
#验证环境变量
node -v
npm -v
#配置安装路径
cd /usr/local/node/
mkdir -p node_global
mkdir -p node_cache
npm config set prefix "node_global"
npm config set cache "node_cache"
#npm慢的话装个cnmp
npm install cnpm -g --registry=https://registry.npm.taobao.org
#安装依赖
npm install

建账号、库

1
2
3
4
5
CREATE USER user_mo PASS 'Lunz2017';
ALTER USER user_mo PRIVILEGE write;
CREATE DATABASE IF NOT EXISTS statsd UPDATE 2;
CREATE DATABASE IF NOT EXISTS collectd UPDATE 2;
CREATE DATABASE IF NOT EXISTS telegraf UPDATE 2;

编辑TD配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
vim /etc/taos/taosadapter.toml

[statsd]
enable = true
port = 6044
db = "statsd"
user = "user_mo"
password = "Lunz2017"
worker = 10
gatherInterval = "5s"
protocol = "udp"
maxTCPConnections = 250
tcpKeepAlive = false
allowPendingMessages = 50000
deleteCounters = true
deleteGauges = true
deleteSets = true
deleteTimings = true

编辑statd配置文件，假设文件夹位置 /opt/statsd

1
2
3
cd  /opt/statsd
cp exampleConfig.js tdconfig.js
vim /opt/statsd/tdconfig.js
1
2
3
4
5
{
port: 8125
, backends: ["./backends/repeater"]
, repeater: [{ host: '192.168.200.99', port: 6044}]
}

启动

1
node stats.js tdconfig.js &

测试

1
2
yum install nc
echo "foo:1|c" | nc -u -w1 192.168.200.99 8125

collectd 安装

下载安装包

https://cloud.189.cn/t/bM3eAzAjQv2y（访问码：4mew）

编译安装 略

配置接收直接采集插件数据

1
2
vim /opt/collectd/etc/collectd.conf
#td官方文档默认编译安装位置 /etc/collectd/collectd.conf  验证非此文件
1
2
3
4
LoadPlugin network
<Plugin network>
         Server "192.168.200.99" "6045"
</Plugin>

配置write_tsdb 插件数据

1
vim /opt/collectd/etc/collectd.conf
1
2
3
4
5
6
7
8
9
10
LoadPlugin write_tsdb
<Plugin write_tsdb>
        <Node>
                Host "192.168.200.99"
                Port "6047"
                HostTags "status=production"
                StoreRates false
                AlwaysAppendDS false
        </Node>
</Plugin>

编辑配置文件

1
vim /etc/taos/taosadapter.toml
1
2
3
4
5
6
7
8
[opentsdb_telnet]
enable = true
maxTCPConnections = 250
tcpKeepAlive = false
dbs = ["opentsdb_telnet", "collectd", "icinga2", "tcollector"]
ports = [6046, 6047, 6048, 6049]
user = "user_mo"
password = "Lunz2017"

建立服务软连接

1
2
3
4
#环境变量
ln -s /opt/collectd/sbin/collectd /usr/sbin/collectd
#注册服务 这玩意不好使直接去库里验证数据吧
ln -s /opt/collectd/sbin/collectd /etc/init.d/collectd

启动服务

1
2
collectd
ps -ef|grep collectd

连接数据库查看collectd库内数据

验证存入数据

1
2
use collectd;
select * from cpu_value limit 10;

应用3 另一种监控

安装 Telegraf 监控系统

添加镜像源

1
2
3
4
5
6
7
8
cat <<EOF | sudo tee /etc/yum.repos.d/influxdata.repo
[influxdata]
name = InfluxData Repository - Stable
baseurl = https://repos.influxdata.com/stable/\$basearch/main
enabled = 1
gpgcheck = 1
gpgkey = https://repos.influxdata.com/influxdb.key
EOF

安装

1
sudo yum install telegraf

rpm安装：

https://cloud.189.cn/t/NZVVjaiYrYve（访问码：it2m）

修改配置文件

1
/etc/telegraf/telegraf.conf
1
2
3
4
5
6
7
8
[[outputs.http]]
  url = "http://192.168.200.99:6041/influxdb/v1/write?db=telegraf"
  method = "POST"
  timeout = "5s"
  username = "user_mo"
  password = "Lunz2017"
  data_format = "influx"
  influx_max_line_bytes = 250

启动

1
sudo systemctl restart telegraf

验证存入数据

1
2
use telegraf;
select * from cpu limit 10;
数据库 > TDEngine
#TDEngine #时序数据库
TDEngine应用测试
https://blog.wongcw.cn/2022/06/02/TDEngine应用测试/
作者
wangcw
发布于
2022年6月2日
许可协议