Klustron 1.3 性能对比测试报告
Klustron 1.3 性能对比测试报告
版本:v1.3.1
集群拓扑及配置:
集群拓扑 | 计算节点 | 存储节点 | 管理节点 | haproxy | sysbench | benchmarksql |
---|---|---|---|---|---|---|
192.168.0.20 | √ | √ | √ | |||
192.168.0.21 | √ | √ | √ | |||
192.168.0.22 | √ | √ | √ | √ | √ | √ |
集群说明: 计算节点:3台机器各部署一个计算节点。 存储节点:3个shard,每一个shard为单主,三个shard的单主分别分布在这三台机器上。 管理节点:管理集群有三台机器组成,为3个节点,1主两备。
机器配置:CentOS8.5 32c 128g 1.9Tnvmessd 万兆网卡。
负载均衡:haproxy 2.5.0
sysbench:1.0.20
benchmarksql:5.0
压测前准备:
创建3shard-3计算节点集群
压测前计算节点系统变量修改:
alter system set statement_timeout=6000000;
alter system set mysql_read_timeout=1200;
alter system set mysql_write_timeout=1200;
alter system set lock_timeout=1200000;
alter system set log_min_duration_statement=1200000;
alter system set effective_cache_size = '8GB';
alter system set work_mem = '128MB';
alter system set wal_buffers='64MB';
alter system set autovacuum=false;
注意:各个节点节点修改后需重启生效。
压测前存储节点系统变量修改:
注意:本性能测试中没有挂备机,因此关掉了fullsync,并且由于所使用的服务器的IO系统性能有限,不得不设置sync_binlog=0,以及innodb_flush_log_at_trx_commit=2,也就是写入binlog和Innodb redo log到文件后,不fsync到存储介质。在金融级高可靠的环境中,应该每个shard安装至少2个备节点,并且设置enable_fullsync=true,sync_binlog=1,以及innodb_flush_log_at_trx_commit=1,也就是在完成上述写入后要fsync到存储介质。即便如此,本性能测试结果完全可以反映在网络性能以及服务器硬件性能特别是IO性能优秀(企业级服务器)的情况下可以达到的性能,并且彼时的性能完全可以大幅超越这里的性能数据。
mysql -h xxx -P xxx -upgx -ppgx_pwd #登录到各个shard的主上进行修改
set global innodb_buffer_pool_size=32*1024*1024*1024;
set global lock_wait_timeout=1200;
set global innodb_lock_wait_timeout=1200;
set global fullsync_timeout=1200000;
set global enable_fullsync=false;
set global innodb_flush_log_at_trx_commit=2;
set global sync_binlog=0;
set global max_binlog_size=1*1024*1024*1024;
set global enable_fullsync=off;
为各个shard设置免切,XPanel上操作【集群管理】->【集群免切设置】。
删除各个shard的备机。
Sysbench
oltp_point_select
压测时间 | 5min | 5min | 5min | 5min |
---|---|---|---|---|
并发数 | 100 | 300 | 600 | 900 |
95%latency(ms) | 0.81 | 2.86 | 64.47 | 70.55 |
TPS | 113007.37 | 95306.52 | 73943.31 | 66162.5 |
QPS | 113007.37 | 95306.52 | 73943.31 | 66162.5 |
cpu(32vC) | 20:29% 21:27% 22:27% | 20:28% 21:26% 22:27% | 20:27% 21:26% 22:26% | 20:27% 21:25% 22:26% |
内存(128g) | 20:33% 21:33% 22:33% | 20:33% 21:33% 22:33% | 20:33% 21:33% 22:33% | 20:34% 21:34% 22:34% |
io使用率 | 20:7% 21:7% 22:7% | 20:7% 21:5% 22:4% | 20:5% 21:3% 22:3% | 20:6% 21:7% 22:4% |
oltp_update_non_index
压测时间 | 5min | 5min | 5min | 5min |
---|---|---|---|---|
并发数 | 100 | 300 | 600 | 900 |
95%latency(ms) | 1.44 | 12.3 | 51.02 | 58.92 |
TPS | 66057.79 | 63286.77 | 54899.43 | 51132.19 |
QPS | 66057.79 | 63286.77 | 54899.43 | 51132.19 |
cpu(32vC) | 20:34% 21:32% 22:36% | 20:31% 21:33% 22:36% | 20:33% 21:30% 22:35% | 20:31% 21:32% 22:33% |
内存(128g) | 20:34% 21:34% 22:34% | 20:34% 21:34% 22:34% | 20:34% 21:34% 22:34% | 20:35% 21:35% 22:35% |
io使用率 | 20:27% 21:18% 22:39% | 20:99% 21:43% 22:95% | 20:95% 21:99% 22:95% | 20:94% 21:91% 22:96% |
oltp_update_index
压测时间 | 5min | 5min | 5min | 5min |
---|---|---|---|---|
并发数 | 100 | 300 | 600 | 900 |
95%latency(ms) | 2.43 | 11.24 | 46.63 | 55.82 |
TPS | 64748.63 | 54121.36 | 46875.16 | 46347.41 |
QPS | 64748.63 | 54121.36 | 46875.16 | 46347.41 |
cpu(32vC) | 20:40% 21:42% 22:40% | 20:33% 21:32% 22:29% | 20:33% 21:28% 22:28% | 20:32% 21:26% 22:34% |
内存(128g) | 20:20% 21:21% 22:18% | 20:20% 21:22% 22:19% | 20:21% 21:23% 22:23% | 20:21% 21:23% 22:21% |
io使用率 | 20:92% 21:97% 22:98% | 20:99% 21:91% 22:94% | 20:96% 21:94% 22:96% | 20:93% 21:92% 22:97% |
oltp_read_write
压测时间 | 5min | 5min | 5min | 5min |
---|---|---|---|---|
并发数 | 100 | 300 | 600 | 900 |
95%latency(ms) | 186.54 | 411.96 | 612.21 | 427.07 |
TPS | 642.61 | 1940.16 | 3095.1 | 3218.29 |
QPS | 2570.43 | 7760.64 | 12380.38 | 12869.15 |
cpu(32vC) | 20:11% 21:10% 22:12% | 20:20% 21:16% 22:21% | 20:23% 21:22% 22:25% | 20:25% 21:24% 22:26% |
内存(128g) | 20:35% 21:35% 22:35% | 20:36% 21:36% 22:36% | 20:37% 21:37% 22:37% | 20:38% 21:38% 22:38% |
io使用率 | 20:93% 21:98% 22:98% | 20:60% 21:13% 22:51% | 20:52% 21:54% 22:51% | 20:63% 21:57% 22:61% |
oltp_read_only
压测时间 | 5min | 5min | 5min | 5min |
---|---|---|---|---|
并发数 | 100 | 300 | 600 | 900 |
95%latency(ms) | 183.21 | 502.2 | 383.33 | 427.07 |
TPS | 644.99 | 865.42 | 3086 | 3450.44 |
QPS | 2579.96 | 3461.67 | 12334.18 | 13783.17 |
cpu(32vC) | 20:11% 21:11% 22:12% | 20:29% 21:27% 22:27% | 20:28% 21:27% 22:26% | 20:28% 21:27% 22:26% |
内存(128g) | 20:34% 21:34% 22:34% | 20:33% 21:33% 22:33% | 20:33% 21:33% 22:33% | 20:33% 21:33% 22:33% |
io使用率 | 20:100% 21:100% 22:100% | 20:55% 21:60% 22:58% | 20:65% 21:70% 22:68% | 20:75% 21:71% 22:68% |
oltp_write_only
压测时间 | 5min | 5min | 5min | 5min |
---|---|---|---|---|
并发数 | 100 | 300 | 600 | 900 |
95%latency(ms) | 183.21 | 260.72 | 459.18 | 637.08 |
TPS | 651.22 | 433.68 | 264.01 | 198.25 |
QPS | 2604.9 | 1812.3 | 896.45 | 503.76 |
cpu(32vC) | 20:4% 21:4% 22:14% | 20:5% 21:3% 22:10% | 20:6% 21:8% 22:9% | 20:6% 21:7% 22:8% |
内存(128g) | 20:34% 21:34% 22:34% | 20:35% 21:34% 22:34% | 20:35% 21:34% 22:34% | 20:36% 21:34% 22:34% |
io使用率 | 20:100% 21:99% 22:100% | 20:100% 21:100% 22:100% | 20:100% 21:100% 22:100% | 20:100% 21:100% 22:100% |
oltp_insert
压测时间 | 5min | 5min | 5min | 5min |
---|---|---|---|---|
并发数 | 100 | 300 | 600 | 900 |
95%latency(ms) | 0.87 | 7.84 | 27.66 | 43.39 |
TPS | 110055.32 | 98261.53 | 75309.96 | 77354.33 |
QPS | 110055.32 | 98261.53 | 75309.96 | 77354.33 |
cpu(32vC) | 20:34% 21:26% 22:27% | 20:33% 21:22% 22:29% | 20:29% 21:27% 22:24% | 20:25% 21:23% 22:38% |
内存(128g) | 20:34% 21:34% 22:34% | 20:34% 21:34% 22:34% | 20:34% 21:34% 22:34% | 20:35% 21:35% 22:35% |
io使用率 | 20:56% 21:58% 22:64% | 20:94% 21:47% 22:93% | 20:91% 21:85% 22:93% | 20:94% 21:96% 22:94% |
TPC-C
压测时间 | 10min | 10min | 10min | 10min | 10min | 10min | 10min | 10min | 10min | 10min | 10min | 10min | 10min | 10min | 10min | 10min | 10min | 10min | 10min | 10min | 10min |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
warehouse | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 |
并发数 | 50 | 50 | 60 | 70 | 80 | 90 | 100 | 150 | 200 | 300 | 400 | 500 | 600 | 700 | 50 | 90 | 100 | 200 | 300 | 400 | 500 |
tmpC(每分钟处理的订单数) | 86851.53 | 86653.63 | 84991.98 | 84124.6 | 81586.18 | 83623.86 | 46545.82 | 32384.51 | 21039.78 | 21356.62 | 21970.13 | 22568.62 | 24438.24 | 23091.88 | 87922.21 | 83814.54 | 81742.56 | 21620 | 21763.25 | 30612.03 | 28126.18 |
tmpTotal | 193198.13 | 192866.59 | 188799.55 | 186880.09 | 181188.01 | 185844.57 | 103319.45 | 71928.44 | 46754.49 | 47422.62 | 48836.39 | 50230.18 | 54333.98 | 51256.41 | 195284.6 | 186223.04 | 181779.72 | 48044.17 | 48319.84 | 68080.4 | 62555.91 |
备注 | node:18,19,20 | node:20,21,22 | |||||||||||||||||||
cpu(32vC) | 18:40% 19:39% 20:36% | 20:35% 21:33% 22:33% | 20:38% 21:35% 22:36% | 20:37% 21:32% 22:36% | 20:37% 21:34% 22:36% | 20:36% 21:29% 22:35% | 20:32% 21:30% 22:33% | 20:11% 21:31% 22:29% | 20:26% 21:9% 22:8% | 20:27% 21:8% 22:8% | 20:25% 21:7% 22:8% | 20:27% 21:19% 22:45% | 20:27% 21:19% 22:45% | 20:28% 21:11% 22:7% | 20:36% 21:28% 22:39% | 20:33% 21:35% 22:37% | 20:36% 21:34% 22:38% | 20:25% 21:7% 22:10% | 20:26% 21:8% 22:11% | 20:11% 21:10% 22:30% | 20:11% 21:27% 22:11% |
内存(128g) | 18:25% 19:20% 20:20% | 20:23% 21:20% 22:21% | 20:23% 21:22% 22:22% | 20:24% 21:22% 22:23% | 20:25% 21:22% 22:23% | 20:26% 21:22% 22:23% | 20:26% 21:23% 22:24% | 20:27% 21:24% 22:25% | 20:27% 21:24% 22:25% | 20:27% 21:24% 22:26% | 20:28% 21:24% 22:26% | 20:28% 21:24% 22:26% | 20:28% 21:24% 22:26% | 20:29% 21:25% 22:27% | 20:34% 21:34% 22:34% | 20:34% 21:34% 22:34% | 20:34% 21:34% 22:34% | 20:35% 21:34% 22:34% | 20:35% 21:34% 22:34% | 20:35% 21:35% 22:35% | 20:35% 21:35% 22:35% |
io使用率 | 18:70% 19:75% 20:72% | 20:65% 21:67% 22:62% | 20:73% 21:67% 22:72% | 20:78% 21:75% 22:71% | 20:62% 21:65% 22:66% | 20:82% 21:83% 22:85% | 20:81% 21:89% 22:89% | 20:30% 21:35% 22:55% | 20:28% 21:36% 22:44% | 20:22% 21:32% 22:21% | 20:25% 21:24% 22:23% | 20:25% 21:24% 22:31% | 20:32% 21:25% 22:31% | 20:34% 21:25% 22:17% | 20:78% 21:82% 22:81% | 20:81% 21:85% 22:85% | 20:81% 21:82% 22:87% | 20:29% 21:38% 22:31% | 20:31% 21:33% 22:32% | 20:34% 21:32% 22:35% | 20:33% 21:32% 22:29% |
TPC-H
泽拓昆仑Klustron 目前正在开发的向量化流水线技术的新执行引擎Tornado,经过实际测试,把TPC-H的性能在之前版本的基础上提升了十倍到数百倍,平均来说也有数十倍,详见下面的测试数据。下表中new-cost各列是使用Tornado执行的性能。
集群共三个shard,region和nation为镜像表,其他6张表(part,supplier,partsupp,customer,orders,lineitem)均为hash分区表,hash子分区为3个,每个hash子分区分别落在三个不同的shard中。
queries | cost(seconds)1G | new-cost(seconds)1G | new-cost(seconds)10G | new-cost(seconds)100G | new-cost(seconds)200G | new-cost(seconds)500G |
---|---|---|---|---|---|---|
Q1 | 15.8 | 1.6 | 0.59 | 4.48 | 44.39 | 88.79 |
Q2 | 1.36 | 0.79 | 0.94 | 8.05 | 26.95 | 167.71 |
Q3 | 1598.1 | 0.62 | 1.12 | 10.09 | 55.93 | 424.11 |
Q4 | 3.12 | 0.33 | 0.63 | 5.06 | 56.83 | 162.17 |
Q5 | 30.23 | 1.53 | 2.15 | 22.33 | 114.12 | 465.63 |
Q6 | 2.6 | 0.39 | 0.38 | 2.95 | 45.47 | 132.41 |
Q7 | 2262.64 | 0.45 | 1.15 | 12.36 | 70.03 | 209.25 |
Q8 | 5.3 | 0.46 | 1.45 | 31.36 | 112.17 | 467.98 |
Q9 | 14.33 | 15.45 | 2.67 | 29.43 | 111.07 | 717.72 |
Q10 | 5.15 | 0.04 | 1.19 | 10.61 | 63.72 | 266.68 |
Q11 | 0.88 | 0.04 | 0.31 | 2.6 | 14.99 | 135.14 |
Q12 | 3.77 | 0.29 | 0.79 | 6.99 | 64.27 | 128.81 |
Q13 | 2.54 | 2.45 | 1.75 | 15.73 | 33.62 | 178.35 |
Q14 | 2.79 | 0.43 | 0.55 | 4.04 | 53.79 | 174.23 |
Q15 | 5.36 | 0.06 | 0.78 | 7.57 | 102.97 | 330.5 |
Q16 | 0.88 | 0.87 | 0.27 | 3.76 | 17.49 | 641.42 |
Q17 | 10.97 | 0.94 | 1.92 | 15.41 | 96.64 | 445.27 |
Q18 | 13.9 | 0.04 | 4.02 | 39.05 | 177.66 | 4191.07 |
Q19 | 3.14 | 3.58 | 0.96 | 8.37 | 52.49 | 163.6 |
Q20 | 4.28 | 0.52 | 1.42 | 13.57 | 78.37 | 610.63 |
Q21 | 9.64 | 1.05 | 6.69 | 47.8 | 218.36 | |
Q22 | 0.71 | 0.48 | 0.76 | 6.29 | 20.24 | 95.79 |
TPC-DS
totalCost: 2986.81s
query | cost(seconds) | new-cost(seconds)1G | new-cost(seconds)10G |
---|---|---|---|
Q1 | 0.24 | 0.09 | 0.96 |
Q2 | 4.84 | 3.84 | 37.64 |
Q3 | 1.51 | 0.1 | 0.66 |
Q4 | 30.63 | 1.52 | 7.23 |
Q5 | 5.18 | 0.39 | 2.56 |
Q6 | 141.67 | 0.19 | 0.69 |
Q7 | 5.73 | 0.27 | 2.5 |
Q8 | 2.1 | 1.07 | 4.82 |
Q9 | 11.49 | 0.69 | 7.1 |
Q10 | 6.03 | 2.36 | 16.2 |
Q11 | 20.22 | 0.76 | 3.7 |
Q12 | 0.52 | 0.09 | 0.32 |
Q13 | 2.21 | 0.45 | 3.04 |
Q14 | 9.95 | 2.07 | 16.86 |
Q15 | 1.08 | 0.18 | 1 |
Q16 | 0.75 | 0.15 | 2.08 |
Q17 | 6.73 | 0.27 | 2.4 |
Q18 | 4.26 | 0.49 | 2.14 |
Q19 | 1.77 | 0.18 | 0.89 |
Q20 | 1.03 | 0.12 | 0.51 |
Q21 | 5.88 | 0.4 | 1.7 |
Q22 | 13.06 | 8.6 | 109.56 |
Q23 | 20.25 | 0.58 | 4.13 |
Q24 | 4.44 | 0.21 | 3.61 |
Q25 | 1292.55 | 0.36 | 2.54 |
Q26 | 3.52 | 0.19 | 0.99 |
Q27 | 3.47 | 0.23 | 2.29 |
Q28 | 7.56 | 1 | 9.84 |
Q29 | 2.66 | 0.31 | 2.3 |
Q30 | 0.35 | 0.13 | 0.42 |
Q31 | 19.19 | 0.31 | 1.26 |
Q32 | 2.14 | 0.12 | 0.79 |
Q33 | 3.07 | 0.59 | 3.04 |
Q34 | 0.09 | 0.1 | 0.75 |
Q35 | 5.05 | 2.21 | 18.27 |
Q36 | 0.07 | 0.15 | 6.86 |
Q37 | 0.04 | 0.3 | 1.67 |
Q38 | 4.56 | 1.05 | 9.74 |
Q39 | 15.45 | 9.35 | 107.29 |
Q40 | 1.38 | 0.13 | 0.73 |
Q41 | 0.05 | 0.1 | 0.23 |
Q42 | 1.6 | 0.1 | 0.67 |
Q43 | 0.06 | 0.13 | 0.75 |
Q44 | 1.1 | 0.15 | 2.71 |
Q45 | 1029.89 | 0.19 | 0.89 |
Q46 | 0.07 | 0.17 | 2.46 |
Q47 | 6.47 | 0.76 | 1.23 |
Q48 | 2.05 | 0.45 | 3.21 |
Q49 | 3.12 | 0.38 | 3.21 |
Q50 | 4.58 | 0.81 | 2.09 |
Q51 | 4.21 | 1.95 | 19 |
Q52 | 1.52 | 0.11 | 0.69 |
Q53 | 1.61 | 0.2 | 1.35 |
Q54 | 0.85 | 0.25 | 0.78 |
Q55 | 1.59 | 0.13 | 0.69 |
Q56 | 3.08 | 0.43 | 2.29 |
Q57 | 2.86 | 0.68 | 1.94 |
Q58 | 9.27 | 0.3 | 1.51 |
Q59 | 6.34 | 0.27 | 1.69 |
Q60 | 3.1 | 0.36 | 2.69 |
Q61 | 0.14 | 0.27 | 1.68 |
Q62 | 1 | 0.07 | 0.25 |
Q63 | 1.63 | 0.19 | 1.43 |
Q64 | 11.11 | 0.83 | 30 |
Q65 | 3.68 | 0.23 | 3.08 |
Q66 | 1.37 | 0.17 | 0.89 |
Q67 | 10.05 | 8.43 | 110.91 |
Q68 | 0.09 | 0.19 | 2.66 |
Q69 | 5.24 | 0.18 | 1.01 |
Q70 | 5.04 | 1 | 10.55 |
Q71 | 1.62 | 0.35 | 1.55 |
Q72 | 28.57 | 0.79 | 9.98 |
Q73 | 0.09 | 0.12 | 0.66 |
Q74 | 7.49 | 0.54 | 1.18 |
Q75 | 5.74 | 2.32 | 26.54 |
Q76 | 1.54 | 0.13 | 0.78 |
Q77 | 4.75 | 0.25 | 2.57 |
Q78 | 25.25 | 4.27 | 169.14 |
Q79 | 2.49 | 0.17 | 2.3 |
Q80 | 6.69 | 0.4 | 2.86 |
Q81 | 0.33 | 0.13 | 0.45 |
Q82 | 5.95 | 0.29 | 1.84 |
Q83 | 1.2 | 0.16 | 0.39 |
Q84 | 19.2 | 0.11 | 0.33 |
Q85 | 2.63 | 0.38 | 1.25 |
Q86 | 0.73 | 0.34 | 2.58 |
Q87 | 4.54 | 1.1 | 10.23 |
Q88 | 10.27 | 0.47 | 7 |
Q89 | 1.85 | 0.31 | 1.01 |
Q90 | 0.79 | 0.07 | 0.28 |
Q91 | 1.12 | 0.15 | 0.28 |
Q92 | 1.1 | 0.11 | 0.39 |
Q93 | 3.59 | 0.36 | 2.06 |
Q94 | 0.52 | 0.11 | 1.39 |
Q95 | 32.88 | 7.95 | 88.65 |
Q96 | 1.25 | 0.08 | 0.91 |
Q97 | 3.23 | 1.12 | 12.65 |
Q98 | 1.83 | 0.2 | 1.69 |
Q99 | 2.03 | 0.17 | 0.98 |