ceph、longhorn、local-path存储压力测试对比
# kbench
使用kbench fio命令对磁盘做性能测试，pvc 30Gi
# 结果对比
# 测试过程
# local-path
kubectl apply -f fio.yaml
kubectl logs -l kbench=fio -f
FIO Benchmark Summary
For: test_device
CPU Idleness Profiling: disabled
Size: 30G
Quick Mode: disabled
IOPS (Read/Write)
Random: 3,006 / 3,162
Sequential: 2,985 / 3,404
Bandwidth in KiB/sec (Read/Write)
Random: 121,870 / 126,883
Sequential: 115,154 / 127,782
Latency in ns (Read/Write)
Random: 345,397 / 387,940
Sequential: 293,500 / 382,933
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
cat fio.yaml
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
name: kbench-pvc
spec:
volumeMode: Filesystem
#volumeMode: Block
#storageClassName: longhorn # replace with your storage class
storageClassName: local-path
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 33Gi
---
apiVersion: batch/v1
kind: Job
metadata:
name: kbench
spec:
template:
metadata:
labels:
kbench: fio
spec:
containers:
- name: kbench
image: yasker/kbench:latest
imagePullPolicy: Always
env:
#- name: QUICK_MODE # for debugging
# value: "1"
- name: FILE_NAME
value: "/volume/test"
- name: SIZE
value: "30G" # must be 10% smaller than the PVC size due to filesystem also took space
- name: CPU_IDLE_PROF
value: "disabled" # must be "enabled" or "disabled"
volumeMounts:
- name: vol
mountPath: /volume/
#volumeDevices:
#- name: vol
# devicePath: /volume/test
restartPolicy: Never
volumes:
- name: vol
persistentVolumeClaim:
claimName: kbench-pvc
backoffLimit: 0
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
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
# longhorn
kubectl apply -f fio-longhorn.yaml
kubectl logs -l kbench=fio -f
FIO Benchmark Summary
For: test_device
CPU Idleness Profiling: disabled
Size: 30G
Quick Mode: disabled
IOPS (Read/Write)
Random: 5,397 / 1,541
Sequential: 8,483 / 3,077
Bandwidth in KiB/sec (Read/Write)
Random: 215,820 / 95,832
Sequential: 245,079 / 110,017
Latency in ns (Read/Write)
Random: 1,107,313 / 1,871,791
Sequential: 802,648 / 1,871,126
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
cat fio-longhorn.yaml
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
name: kbench-pvc
spec:
volumeMode: Filesystem
#volumeMode: Block
storageClassName: longhorn # replace with your storage class
#storageClassName: local-path
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 33Gi
---
apiVersion: batch/v1
kind: Job
metadata:
name: kbench
spec:
template:
metadata:
labels:
kbench: fio
spec:
containers:
- name: kbench
image: yasker/kbench:latest
imagePullPolicy: Always
env:
#- name: QUICK_MODE # for debugging
# value: "1"
- name: FILE_NAME
value: "/volume/test"
- name: SIZE
value: "30G" # must be 10% smaller than the PVC size due to filesystem also took space
- name: CPU_IDLE_PROF
value: "disabled" # must be "enabled" or "disabled"
volumeMounts:
- name: vol
mountPath: /volume/
#volumeDevices:
#- name: vol
# devicePath: /volume/test
restartPolicy: Never
volumes:
- name: vol
persistentVolumeClaim:
claimName: kbench-pvc
backoffLimit: 0
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
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
# ceph
kubectl apply -f fio-longhorn.yaml
kubectl logs -l kbench=fio -f
FIO Benchmark Summary
For: test_device
CPU Idleness Profiling: disabled
Size: 30G
Quick Mode: disabled
IOPS (Read/Write)
Random: 6,518 / 1,926
Sequential: 2,909 / 3,665
Bandwidth in KiB/sec (Read/Write)
Random: 293,968 / 102,718
Sequential: 257,131 / 112,322
Latency in ns (Read/Write)
Random: 1,162,662 / 3,153,147
Sequential: 830,783 / 3,255,677
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
cat fio-ceph.yaml
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
name: kbench-pvc
spec:
volumeMode: Filesystem
#volumeMode: Block
storageClassName: rook-ceph-block # replace with your storage class
#storageClassName: local-path
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 33Gi
---
apiVersion: batch/v1
kind: Job
metadata:
name: kbench
spec:
template:
metadata:
labels:
kbench: fio
spec:
containers:
- name: kbench
image: yasker/kbench:latest
imagePullPolicy: Always
env:
#- name: QUICK_MODE # for debugging
# value: "1"
- name: FILE_NAME
value: "/volume/test"
- name: SIZE
value: "30G" # must be 10% smaller than the PVC size due to filesystem also took space
- name: CPU_IDLE_PROF
value: "disabled" # must be "enabled" or "disabled"
volumeMounts:
- name: vol
mountPath: /volume/
#volumeDevices:
#- name: vol
# devicePath: /volume/test
restartPolicy: Never
volumes:
- name: vol
persistentVolumeClaim:
claimName: kbench-pvc
backoffLimit: 0
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
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
# sysbench
部署postgres+tsdb数据库，使用分布式存储做数据持久化。使用sysbench对数据库进行压测，50并发，120s，1000万条数据，pvc50Gi
# 结果对比
# 测试过程
# local-path
# 读写oltp_read_write:
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=2hcnDXzp7iGEixVX \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_read_write.lua \
prepare
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=2hcnDXzp7iGEixVX \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_read_write.lua \
run
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=2hcnDXzp7iGEixVX \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_read_write.lua \
cleanup
SQL statistics:
queries performed:
read: 1035468
write: 295827
other: 147937
total: 1479232
transactions: 73958 (615.45 per sec.)
queries: 1479232 (12309.61 per sec.)
ignored errors: 4 (0.03 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.1675s
total number of events: 73958
Latency (ms):
min: 5.94
avg: 81.15
max: 511.81
99th percentile: 244.38
sum: 6001527.58
Threads fairness:
events (avg/stddev): 1479.1600/36.27
execution time (avg/stddev): 120.0306/0.03
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
76
77
78
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
76
77
78
# 只写oltp_write_only:
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=2hcnDXzp7iGEixVX \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_write_only.lua \
prepare
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=2hcnDXzp7iGEixVX \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_write_only.lua \
run
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=2hcnDXzp7iGEixVX \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_write_only.lua \
cleanup
SQL statistics:
queries performed:
read: 0
write: 388914
other: 194528
total: 583442
transactions: 97229 (808.71 per sec.)
queries: 583442 (4852.80 per sec.)
ignored errors: 17 (0.14 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.2265s
total number of events: 97229
Latency (ms):
min: 5.10
avg: 61.78
max: 673.28
99th percentile: 350.33
sum: 6006786.32
Threads fairness:
events (avg/stddev): 1944.5800/48.26
execution time (avg/stddev): 120.1357/0.02
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
76
77
78
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
76
77
78
插入oltp_insert：
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=5f0yz1SUqLq2b5v1 \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_insert.lua \
prepare
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=5f0yz1SUqLq2b5v1 \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_insert.lua \
run
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=5f0yz1SUqLq2b5v1 \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_insert.lua \
cleanup
SQL statistics:
queries performed:
read: 0
write: 432267
other: 0
total: 432267
transactions: 432267 (3598.86 per sec.)
queries: 432267 (3598.86 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.1107s
total number of events: 432267
Latency (ms):
min: 1.26
avg: 13.88
max: 379.26
99th percentile: 94.10
sum: 5999963.14
Threads fairness:
events (avg/stddev): 8645.3400/438.67
execution time (avg/stddev): 119.9993/0.04
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
76
77
78
79
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
76
77
78
79
# longhorn
# 读写oltp_read_write:
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=WcCyIsctC8TIGGYe \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_read_write.lua \
prepare
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=WcCyIsctC8TIGGYe \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_read_write.lua \
run
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=WcCyIsctC8TIGGYe \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_read_write.lua \
cleanup
SQL statistics:
queries performed:
read: 821996
write: 234838
other: 117432
total: 1174266
transactions: 58707 (488.38 per sec.)
queries: 1174266 (9768.70 per sec.)
ignored errors: 7 (0.06 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.2057s
total number of events: 58707
Latency (ms):
min: 8.68
avg: 102.26
max: 747.23
99th percentile: 303.33
sum: 6003127.07
Threads fairness:
events (avg/stddev): 1174.1400/276.31
execution time (avg/stddev): 120.0625/0.07
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
76
77
78
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
76
77
78
# 只写oltp_write_only:
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=WcCyIsctC8TIGGYe \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_write_only.lua \
prepare
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=WcCyIsctC8TIGGYe \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_write_only.lua \
run
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=WcCyIsctC8TIGGYe \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_write_only.lua \
cleanup
SQL statistics:
queries performed:
read: 0
write: 265836
other: 133004
total: 398840
transactions: 66458 (552.84 per sec.)
queries: 398840 (3317.83 per sec.)
ignored errors: 23 (0.19 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.2097s
total number of events: 66458
Latency (ms):
min: 4.80
avg: 90.37
max: 2316.29
99th percentile: 344.08
sum: 6005838.01
Threads fairness:
events (avg/stddev): 1329.1600/29.08
execution time (avg/stddev): 120.1168/0.07
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
76
77
78
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
76
77
78
插入oltp_insert：
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=WcCyIsctC8TIGGYe \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_insert.lua \
prepare
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=WcCyIsctC8TIGGYe \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_insert.lua \
run
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=WcCyIsctC8TIGGYe \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_insert.lua \
cleanup
SQL statistics:
queries performed:
read: 0
write: 636194
other: 0
total: 636194
transactions: 636194 (5301.09 per sec.)
queries: 636194 (5301.09 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.0105s
total number of events: 636194
Latency (ms):
min: 2.14
avg: 9.43
max: 191.47
99th percentile: 41.85
sum: 5998550.07
Threads fairness:
events (avg/stddev): 12723.8800/869.33
execution time (avg/stddev): 119.9710/0.00
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
76
77
78
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
76
77
78
# ceph
# 读写oltp_read_write:
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=BQppkbvz53Cznnbh \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_read_write.lua \
prepare
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=BQppkbvz53Cznnbh \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_read_write.lua \
run
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=BQppkbvz53Cznnbh \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_read_write.lua \
cleanup
SQL statistics:
queries performed:
read: 925624
write: 264406
other: 132260
total: 1322290
transactions: 66101 (544.76 per sec.)
queries: 1322290 (10897.49 per sec.)
ignored errors: 15 (0.12 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 121.3376s
total number of events: 66101
Latency (ms):
min: 11.89
avg: 90.96
max: 1932.98
99th percentile: 397.39
sum: 6012228.78
Threads fairness:
events (avg/stddev): 1322.0200/28.42
execution time (avg/stddev): 120.2446/0.44
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
76
77
78
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
76
77
78
# 只写oltp_write_only:
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=BQppkbvz53Cznnbh \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_write_only.lua \
prepare
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=BQppkbvz53Cznnbh \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_write_only.lua \
run
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=BQppkbvz53Cznnbh \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_write_only.lua \
cleanup
SQL statistics:
queries performed:
read: 0
write: 410238
other: 205220
total: 615458
transactions: 102557 (687.62 per sec.)
queries: 615458 (4126.52 per sec.)
ignored errors: 29 (0.19 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 149.1454s
total number of events: 102557
Latency (ms):
min: 5.03
avg: 72.71
max: 32085.71
99th percentile: 442.73
sum: 7456422.89
Threads fairness:
events (avg/stddev): 2051.1400/17.70
execution time (avg/stddev): 149.1285/0.01
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
76
77
78
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
76
77
78
插入oltp_insert：
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=BQppkbvz53Cznnbh \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_insert.lua \
prepare
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=BQppkbvz53Cznnbh \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_insert.lua \
run
sysbench \
--db-driver=pgsql \
--pgsql-host=172.26.198.168 \
--pgsql-port=30542 \
--pgsql-user=postgres \
--pgsql-password=BQppkbvz53Cznnbh \
--pgsql-db=test_db \
--tables=4 \
--table-size=2500000 \
--threads=50 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/oltp_insert.lua \
cleanup
SQL statistics:
queries performed:
read: 0
write: 303777
other: 0
total: 303777
transactions: 303777 (2511.54 per sec.)
queries: 303777 (2511.54 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.9511s
total number of events: 303777
Latency (ms):
min: 3.59
avg: 19.90
max: 2170.77
99th percentile: 99.33
sum: 6045855.78
Threads fairness:
events (avg/stddev): 6075.5400/30.84
execution time (avg/stddev): 120.9171/0.01
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
76
77
78
79
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
76
77
78
79
# 业务脚本测试
使用python脚本写入ptag数据到tsdb库
# 结果对比
| 47795811条数据/单次写入1万条 | local-path | longhorn | ceph | openebs |
|---|---|---|---|---|
| 单次时间95分位/ms | 49 | 58 | 80 | |
| 单次时间99分位/ms | 67 | 81 | 119 | |
| 单次时间99.9分位/ms | 112 | 145 | 312 | |
| 总时间/min | 181.33 | 211.98 | 250.81 |
# 测试过程
cat k-storage.py
# -*- coding: utf-8 -*-
import psycopg2
import datetime
import psycopg2.extras
import time
class PostgresqlHelper(object):
conn = None
def __init__(self, host, username, password, db, port=30542):
self.host = host
self.username = username
self.password = password
self.db = db
self.port = port
def connect(self):
self.conn = psycopg2.connect(
host=self.host,
port=self.port,
user=self.username,
password=self.password,
dbname=self.db)
self.conn.autocommit = True
self.cursor = self.conn.cursor()
def close(self):
self.cursor.close()
self.conn.close()
def get_one(self, sql, params=()):
result = None
try:
self.connect()
self.cursor.execute(sql, params)
result = self.cursor.fetchone()
except Exception as e:
print(e)
finally:
self.close()
return result
def get_all(self, sql, params=None):
list_data = ()
try:
self.connect()
self.cursor.execute(sql, params)
list_data = self.cursor.fetchall()
except Exception as e:
print(e)
finally:
self.close()
return list_data
def insert(self, sql, params=()):
return self.__edit(sql, params)
def insert_all(self, sql, params=()):
return self.__edit_all(sql, params)
def insert_batch(self, sql, params=()):
return self.__edit_batch(sql, params)
def update(self, sql, params=()):
return self.__edit(sql, params)
def delete(self, sql, params=()):
return self.__edit(sql, params)
def create(self, sql):
return self.__edit(sql, '')
def exec(self, sql):
return self.__edit(sql, '')
def __edit(self, sql, params):
count = 0
try:
self.connect()
count = self.cursor.execute(sql, params)
self.conn.commit()
except Exception as e:
print(e)
finally:
self.close()
return count
def __edit_all(self, sql, params):
count = 0
try:
self.connect()
count = self.cursor.executemany(sql, params)
self.conn.commit()
except Exception as e:
print(e)
finally:
self.close()
return count
def __edit_batch(self, sql, params):
count = 0
try:
self.connect()
count = psycopg2.extras.execute_batch(
self.cursor, sql, params, page_size=100)
self.conn.commit()
except Exception as e:
print(e)
finally:
self.close()
return count
if __name__ == '__main__':
conn_info = {'user': 'postgres', 'password': 'Grwa97xAJO3Xl4SH',
'host': '172.26.198.168', 'db': 'test'}
conn = PostgresqlHelper(
username=conn_info['user'], password=conn_info['password'], host=conn_info['host'],
db=conn_info['db'], port=30542)
query = 'INSERT INTO ptagdata_861564(time,tag_id,value,modified_value,quality,update_time) VALUES(%s,%s,%s,%s,%s,%s) on conflict(tag_id,time) do UPDATE SET value=EXCLUDED.value,modified_value=EXCLUDED.modified_value, quality=EXCLUDED.quality, update_time=EXCLUDED.update_time;'
file_name = 'ptagdata_861564.csv'
# BLOCK_SIZE= 2 ** 20
start = time.time()*1000
#print(start)
with open(file_name, 'r') as f:
while True:
# data = f.read(BLOCK_SIZE)
# if not data:
# break
# print(len(data))
# print(data.split('\n'))
# time.sleep(30)
ss = time.time()*1000
data = f.readlines(10000)
if not data:
break
a = [x.split('\n')[0].split(',') for x in data]
# conn.insert_all(query, a)
conn.insert_batch(query, a)
print('cost: ', + int(time.time()*1000)- int(ss))
#time.sleep(30)
#batch = time.time()
end = int(time.time()*1000) - int(start)
print('end_time：', end)
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
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
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
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
上次更新: 2023/12/12, 09:29:58