写入测试结果

本章节介绍写入测试的结果对比。本测试是在发挥数据最佳性能,并在没有任何查询的情况下测得的结果。在不同时间线规模的业务场景,或同时存在查询的场景下,写入性能可能会下降,建议在使用前结合自己的业务场景进行测试评估,本测试结果仅供参考。

写入数据样例

每10秒在一台服务器的9个子系统(CPU、内存、磁盘、磁盘I/O、内核、网络、Redis、PostgreSQL和Nginx)上采样101个测量点, 上报到Lindorm时序引擎。

每个子系统在Lindorm时序引擎中作为一张Table,Table、TAG、Field的定义参考数据模型

  • 建表语句如下,其中hostname作为每个设备的唯一标识。

CREATE table cpu(hostname VARCHAR primary TAG,region VARCHAR TAG,datacenter VARCHAR TAG,rack VARCHAR TAG,os VARCHAR TAG,arch VARCHAR TAG,team VARCHAR TAG,service VARCHAR TAG,service_version VARCHAR TAG,service_environment VARCHAR TAG,time BIGINT,usage_user double,usage_system double,usage_idle double,usage_nice double,usage_iowait double,usage_irq double,usage_softirq double,usage_steal double,usage_guest double,usage_guest_nice double);

CREATE table mem(hostname VARCHAR primary TAG, region VARCHAR TAG, datacenter VARCHAR TAG, rack VARCHAR TAG, os VARCHAR TAG, arch VARCHAR TAG, team VARCHAR TAG, service VARCHAR TAG, service_version VARCHAR TAG, service_environment VARCHAR TAG, time BIGINT,total BIGINT, available BIGINT, used BIGINT, `free` BIGINT, cached BIGINT, buffered BIGINT, used_percent double, available_percent double, buffered_percent double);

CREATE table disk(hostname VARCHAR primary TAG, region VARCHAR TAG, datacenter VARCHAR TAG, rack VARCHAR TAG, os VARCHAR TAG, arch VARCHAR TAG, team VARCHAR TAG, service VARCHAR TAG, service_version VARCHAR TAG, service_environment VARCHAR TAG, path VARCHAR TAG, fstype VARCHAR TAG,time BIGINT,total BIGINT, `free` BIGINT, used BIGINT, used_percent BIGINT, inodes_total BIGINT, inodes_free BIGINT, inodes_used BIGINT);

CREATE table diskio(hostname VARCHAR primary TAG, region VARCHAR TAG, datacenter VARCHAR TAG, rack VARCHAR TAG, os VARCHAR TAG, arch VARCHAR TAG, team VARCHAR TAG, service VARCHAR TAG, service_version VARCHAR TAG, service_environment VARCHAR TAG, serial VARCHAR TAG,time BIGINT,`reads` BIGINT, writes BIGINT, read_bytes BIGINT, write_bytes BIGINT, read_time BIGINT, write_time BIGINT, io_time BIGINT);

CREATE table kernel(hostname VARCHAR primary TAG, region VARCHAR TAG, datacenter VARCHAR TAG, rack VARCHAR TAG, os VARCHAR TAG, arch VARCHAR TAG, team VARCHAR TAG, service VARCHAR TAG, service_version VARCHAR TAG, service_environment VARCHAR TAG,time BIGINT,boot_time BIGINT, interrupts BIGINT, context_switches BIGINT, processes_forked BIGINT, disk_pages_in BIGINT, disk_pages_out BIGINT);

CREATE table net(hostname VARCHAR primary TAG, region VARCHAR TAG, datacenter VARCHAR TAG, rack VARCHAR TAG, os VARCHAR TAG, arch VARCHAR TAG, team VARCHAR TAG, service VARCHAR TAG, service_version VARCHAR TAG, service_environment VARCHAR TAG, interface VARCHAR TAG,time BIGINT, bytes_sent BIGINT, bytes_recv BIGINT, packets_sent BIGINT, packets_recv BIGINT, err_in BIGINT, err_out BIGINT, drop_in BIGINT, drop_out BIGINT );

CREATE table redis(hostname VARCHAR primary TAG, region VARCHAR TAG, datacenter VARCHAR TAG, rack VARCHAR TAG, os VARCHAR TAG, arch VARCHAR TAG, team VARCHAR TAG, service VARCHAR TAG, service_version VARCHAR TAG, service_environment VARCHAR TAG, port VARCHAR TAG, server VARCHAR TAG,time BIGINT, uptime_in_seconds BIGINT, total_connections_received BIGINT, expired_keys BIGINT, evicted_keys BIGINT, keyspace_hits BIGINT, keyspace_misses BIGINT, instantaneous_ops_per_sec BIGINT, instantaneous_input_kbps BIGINT, instantaneous_output_kbps BIGINT, connected_clients BIGINT, used_memory BIGINT, used_memory_rss BIGINT, used_memory_peak BIGINT, used_memory_lua BIGINT, rdb_changes_since_last_save BIGINT, sync_full BIGINT, sync_partial_ok BIGINT, sync_partial_err BIGINT, pubsub_channels BIGINT, pubsub_patterns BIGINT, latest_fork_usec BIGINT, connected_slaves BIGINT, master_repl_offset BIGINT, repl_backlog_active BIGINT, repl_backlog_size BIGINT, repl_backlog_histlen BIGINT, mem_fragmentation_ratio BIGINT, used_cpu_sys BIGINT, used_cpu_user BIGINT, used_cpu_sys_children BIGINT, used_cpu_user_children BIGINT);

CREATE table postgresl(hostname VARCHAR primary TAG, region VARCHAR TAG, datacenter VARCHAR TAG, rack VARCHAR TAG, os VARCHAR TAG, arch VARCHAR TAG, team VARCHAR TAG, service VARCHAR TAG, service_version VARCHAR TAG, service_environment VARCHAR TAG, time BIGINT,numbackends BIGINT, xact_commit BIGINT, xact_rollback BIGINT, blks_read BIGINT, blks_hit BIGINT, tup_returned BIGINT, tup_fetched BIGINT, tup_inserted BIGINT, tup_updated BIGINT, tup_deleted BIGINT, conflicts BIGINT, temp_files BIGINT, temp_bytes BIGINT, deadlocks BIGINT, blk_read_time BIGINT, blk_write_time BIGINT );

CREATE table nginx(hostname VARCHAR primary TAG, region VARCHAR TAG, datacenter VARCHAR TAG, rack VARCHAR TAG, os VARCHAR TAG, arch VARCHAR TAG, team VARCHAR TAG, service VARCHAR TAG, service_version VARCHAR TAG, service_environment VARCHAR TAG, port VARCHAR TAG, server VARCHAR TAG,time BIGINT,accepts BIGINT, active BIGINT, handled BIGINT, reading BIGINT, requests BIGINT, waiting BIGINT, writing BIGINT );

  • 向每张表插入数据的样例如下

INSERT INTO cpu(hostname,region,datacenter,rack,os,arch,team,service,service_version,service_environment, time ,usage_user,usage_system,usage_idle,usage_nice,usage_iowait,usage_irq,usage_softirq,usage_steal,usage_guest,usage_guest_nice) VALUES ('host_0','ap-northeast-1','ap-northeast-1a','72','Ubuntu16.10','x86','CHI','10','0','test',1514764800000,60.4660287979619540,94.0509088045012476,66.4560053218490481,43.7714187186980155,42.4637497071265670,68.6823072867109374,6.5637019217476222,15.6519254732791246,9.6969518914484567,30.0911860585287059);
INSERT INTO diskio(hostname,region,datacenter,rack,os,arch,team,service,service_version,service_environment,serial, time ,`reads`,writes,read_bytes,write_bytes,read_time,write_time,io_time) VALUES ('host_0','ap-northeast-1','ap-northeast-1a','72','Ubuntu16.10','x86','CHI','10','0','test','694-511-162',1514764800000,0,0,3,0,0,7,0);
INSERT INTO disk(hostname,region,datacenter,rack,os,arch,team,service,service_version,service_environment,path,fstype, time ,total,`free`,used,used_percent,inodes_total,inodes_free,inodes_used) VALUES ('host_0','ap-northeast-1','ap-northeast-1a','72','Ubuntu16.10','x86','CHI','10','0','test','/dev/sda9','ext4',1514764800000,1099511627776,549755813888,549755813888,50,268435456,134217728,134217728);
INSERT INTO kernel(hostname,region,datacenter,rack,os,arch,team,service,service_version,service_environment, time ,boot_time,interrupts,context_switches,processes_forked,disk_pages_in,disk_pages_out) VALUES ('host_0','ap-northeast-1','ap-northeast-1a','72','Ubuntu16.10','x86','CHI','10','0','test',1514764800000,233,0,1,0,0,0);
INSERT INTO mem(hostname,region,datacenter,rack,os,arch,team,service,service_version,service_environment, time ,total,available,used,`free`,cached,buffered,used_percent,available_percent,buffered_percent) VALUES ('host_0','ap-northeast-1','ap-northeast-1a','72','Ubuntu16.10','x86','CHI','10','0','test',1514764800000,8589934592,6072208808,2517725783,5833292948,1877356426,2517725783,29.3101857336815748,70.6898142663184217,78.1446947407235797);
INSERT INTO net(hostname,region,datacenter,rack,os,arch,team,service,service_version,service_environment,interface, time ,bytes_sent,bytes_recv,packets_sent,packets_recv,err_in,err_out,drop_in,drop_out) VALUES ('host_0','ap-northeast-1','ap-northeast-1a','72','Ubuntu16.10','x86','CHI','10','0','test','eth3',1514764800000,0,0,0,2,0,0,0,0);
INSERT INTO nginx(hostname,region,datacenter,rack,os,arch,team,service,service_version,service_environment,port,server, time ,accepts,active,handled,reading,requests,waiting,writing) VALUES ('host_0','ap-northeast-1','ap-northeast-1a','72','Ubuntu16.10','x86','CHI','10','0','test','12552','nginx_65466',1514764800000,0,0,11,0,0,0,0);
INSERT INTO postgresl(hostname,region,datacenter,rack,os,arch,team,service,service_version,service_environment, time ,numbackends,xact_commit,xact_rollback,blks_read,blks_hit,tup_returned,tup_fetched,tup_inserted,tup_updated,tup_deleted,conflicts,temp_files,temp_bytes,deadlocks,blk_read_time,blk_write_time) VALUES ('host_0','ap-northeast-1','ap-northeast-1a','72','Ubuntu16.10','x86','CHI','10','0','test',1514764800000,0,0,0,0,3,0,0,0,0,0,0,0,12,0,0,0);
INSERT INTO redis(hostname,region,datacenter,rack,os,arch,team,service,service_version,service_environment,port,server, time ,uptime_in_seconds,total_connections_received,expired_keys,evicted_keys,keyspace_hits,keyspace_misses,instantaneous_ops_per_sec,instantaneous_input_kbps,instantaneous_output_kbps,connected_clients,used_memory,used_memory_rss,used_memory_peak,used_memory_lua,rdb_changes_since_last_save,sync_full,sync_partial_ok,sync_partial_err,pubsub_channels,pubsub_patterns,latest_fork_usec,connected_slaves,master_repl_offset,repl_backlog_active,repl_backlog_size,repl_backlog_histlen,mem_fragmentation_ratio,used_cpu_sys,used_cpu_user,used_cpu_sys_children,used_cpu_user_children) VALUES ('host_0','ap-northeast-1','ap-northeast-1a','72','Ubuntu16.10','x86','CHI','10','0','test','19071','redis_86258',1514764800000,0,0,0,5,0,0,0,0,0,0,8589934592,8589934592,8589934592,8589934592,0,0,0,0,36,0,0,0,0,0,0,0,0,0,16,0,0);
  • 本测试中,使用SQL语句进行写入,为了使SQL写入具有更高的性能,会对同一张表进行batch,先prepare然后再绑定参数,进行批量写入。写入方式参考 如何高效地写入数据

写入数据量

  • 设备数量10000

  • 每个设备每10秒上报101个测量值

  • 共上报15天数据

测试性能指标说明

介绍该测试中写入的性能指标。

  • tps: 向数据库中执行写入时,平均每秒钟写入的测量点数。在Lindorm时序引擎中,也就是每秒写入的Field数量,参考数据模型

  • worker:写入的并发数量

  • batch:每次写入的batch的行数

  • max_cpu: CPU的峰值百分比

测试结果

注意
  • 写入方式均是攒批写入(即一次写入请求传入一批数据点的方式)。

  • 测试写入的同时,时序引擎没有执行任何查询。

图 1. batch为500的情况下,各个规格不同并发数量下的写入TPS

图片 1

表 1. 集群1(4核16GB * 3节点)

batch

worker

tps

max_cpu

1

1

4,846.48

36.68

1

16

36,862.15

78.50

1

50

31,653.44

73.46

1

100

31,521.71

74.32

1

200

31,651.03

73.03

50

1

126,462.85

65.43

50

16

460,032.75

79.89

50

50

457,791.78

81.50

50

100

457,956.53

82.69

50

200

434,573.47

81.18

100

1

168,643.80

74.14

100

16

468,008.25

84.09

100

50

470,608.31

84.34

100

100

451,384.44

83.32

100

200

457,740.22

84.61

200

1

205,046.31

74.77

200

16

480,309.56

84.74

200

50

489,903.34

86.73

200

100

484,745.44

86.77

200

200

475,824.97

86.55

500

1

239,847.34

74.76

500

16

511,989.50

87.86

500

50

544,544.75

88.23

500

100

543,131.56

88.12

500

200

528,027.12

88.57

表 2. 集群2(8核32GB * 3节点)

batch

worker

tps

max_cpu

1

1

3,601.72

19.88

1

16

46,701.05

46.97

1

50

69,892.66

59.77

1

100

70,219.33

60.32

1

200

70,187.81

60.54

50

1

114,062.01

22.88

50

16

1,123,739.88

64.66

50

50

1,416,314.00

70.99

50

100

1,421,701.75

70.94

50

200

1,422,040.12

71.56

100

1

183,456.98

22.38

100

16

1,651,046.25

65.09

100

50

2,029,514.75

74.16

100

100

2,040,670.38

73.39

100

200

2,025,066.12

73.98

200

1

254,914.23

24.27

200

16

2,172,662.25

71.47

200

50

2,670,999.25

76.47

200

100

2,674,582.25

76.95

200

200

2,693,531.50

76.41

500

1

332,250.78

23.86

500

16

2,820,651.50

72.56

500

50

3,429,375.00

80.98

500

100

3,442,593.75

80.62

500

200

3,440,201.50

81.12

表 3. 集群3(16核64GB * 3节点)

batch

worker

tps

max_cpu

1

1

3,897.79

8.97

1

16

58,217.44

27.30

1

50

127,110.89

50.49

1

100

165,754.09

62.31

1

200

202,844.20

70.72

50

1

136,378.39

11.77

50

16

1,634,203.88

41.92

50

50

2,773,785.75

58.96

50

100

3,363,458.25

67.87

50

200

3,703,033.00

74.14

100

1

198,375.67

10.35

100

16

2,494,268.00

45.86

100

50

4,007,320.25

60.87

100

100

4,753,680.50

69.29

100

200

5,095,771.00

75.17

200

1

278,253.53

10.57

200

16

3,368,596.50

45.48

200

50

5,214,060.50

64.57

200

100

6,040,166.50

72.35

200

200

6,283,312.00

77.07

500

1

352,744.78

10.80

500

16

4,281,761.50

47.17

500

50

6,544,214.00

71.73

500

100

7,267,295.50

77.15

500

200

7,290,116.00

83.91

表 4. 集群4(32核128GB * 3节点)

batch

worker

tps

max_cpu

1

1

3,405.32

4.52

1

16

51,460.87

11.04

1

50

134,289.62

27.32

1

100

201,014.75

40.45

1

200

255,692.84

51.99

50

1

113,644.64

5.33

50

16

1,596,669.88

19.13

50

50

3,676,491.50

38.75

50

100

5,217,282.50

50.84

50

200

6,345,112.00

62.49

100

1

188,352.08

5.05

100

16

2,624,622.50

21.15

100

50

5,740,561.50

40.49

100

100

7,521,672.00

55.85

100

200

8,507,855.00

61.68

200

1

249,571.77

5.05

200

16

3,637,803.50

21.23

200

50

8,141,380.50

45.39

200

100

10,289,145.00

57.85

200

200

10,462,525.00

60.48

500

1

334,678.31

5.47

500

16

4,657,772.50

24.23

500

50

10,098,200.00

46.90

500

100

12,405,648.00

64.57

500

200

12,136,903.00

66.01

阿里云首页 云原生多模数据库 Lindorm 相关技术圈