大型数据库sysbench测试结果 oceanbase4.3.4集群 MYSQL8.0.40单机调优 PGSQL17调优

一、系统配置和调优参数
MYSQL8.0.40单机 24C128G调优

key_buffer_size=2048MB
join_buffer_size=17000KB
tmp_table_size=4096MB
innodb_buffer_pool_size=8200MB
sort_buffer_size=8200KB
read_buffer_size=8200KB
read_rnd_buffer_size=4096KB
thread_stack=1024KB
binlog_cache_size=512KB
thread_cache_size=512
table_open_cache=4096
max_connections=10000

PGSQL17单机 24C128G调优

max_connections = 10000
shared_buffers = 2048MB
work_mem = 512MB
maintenance_work_mem = 1024MB

OB集群 1zone6主机 租户24C48G
默认设置

二、测试参数
每个表行数1千万，350个表，总行数35亿，并发数80，在同一个超融合集群中

三、测试数据

OB集群OLTP读写混合场景

[ 10s ] thds: 80 tps: 260.33 qps: 5296.02 (r/w/o: 3718.86/1048.51/528.65) lat (ms,95%): 802.05 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 80 tps: 371.01 qps: 7423.65 (r/w/o: 5196.51/1485.13/742.02) lat (ms,95%): 502.20 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 80 tps: 608.00 qps: 12156.95 (r/w/o: 8509.74/2431.21/1216.01) lat (ms,95%): 193.38 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 80 tps: 793.99 qps: 15880.87 (r/w/o: 11117.61/3175.27/1587.99) lat (ms,95%): 137.35 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 80 tps: 922.80 qps: 18465.23 (r/w/o: 12924.92/3694.71/1845.60) lat (ms,95%): 114.72 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 80 tps: 929.30 qps: 18603.13 (r/w/o: 13023.95/3720.59/1858.59) lat (ms,95%): 130.13 err/s: 0.00 reconn/s: 0.00

OB集群OLTP只读场景压测

[ 10s ] thds: 80 tps: 1893.03 qps: 30354.81 (r/w/o: 26560.74/0.00/3794.06) lat (ms,95%): 68.05 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 80 tps: 2909.96 qps: 46562.29 (r/w/o: 40742.58/0.00/5819.71) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 80 tps: 2642.26 qps: 42274.92 (r/w/o: 36990.19/0.00/5284.73) lat (ms,95%): 50.11 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 80 tps: 3473.07 qps: 55563.89 (r/w/o: 48617.76/0.00/6946.14) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 80 tps: 3646.52 qps: 58341.39 (r/w/o: 51048.65/0.00/7292.74) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 80 tps: 3812.57 qps: 61006.78 (r/w/o: 53381.54/0.00/7625.23) lat (ms,95%): 29.19 err/s: 0.00 reconn/s: 0.00

MYSQL单机OLTP读写混合场景

[ 10s ] thds: 80 tps: 0.40 qps: 96.67 (r/w/o: 84.68/3.20/8.80) lat (ms,95%): 9799.46 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 80 tps: 8.00 qps: 144.01 (r/w/o: 93.10/34.90/16.00) lat (ms,95%): 19778.24 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 80 tps: 12.00 qps: 234.50 (r/w/o: 164.20/46.30/24.00) lat (ms,95%): 9284.15 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 80 tps: 10.90 qps: 238.60 (r/w/o: 173.20/43.60/21.80) lat (ms,95%): 10722.67 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 80 tps: 14.70 qps: 289.10 (r/w/o: 199.20/60.50/29.40) lat (ms,95%): 10722.67 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 80 tps: 16.40 qps: 323.10 (r/w/o: 225.80/64.50/32.80) lat (ms,95%): 8795.93 err/s: 0.00 reconn/s: 0.00

MYSQL单机OLTP只读场景压测

[ 10s ] thds: 80 tps: 19.20 qps: 372.12 (r/w/o: 325.73/0.00/46.39) lat (ms,95%): 6713.97 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 80 tps: 41.10 qps: 650.72 (r/w/o: 568.51/0.00/82.20) lat (ms,95%): 5217.92 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 80 tps: 59.50 qps: 958.60 (r/w/o: 839.60/0.00/119.00) lat (ms,95%): 3706.08 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 80 tps: 78.90 qps: 1260.00 (r/w/o: 1102.20/0.00/157.80) lat (ms,95%): 2932.60 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 80 tps: 94.80 qps: 1521.49 (r/w/o: 1331.90/0.00/189.60) lat (ms,95%): 2320.55 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 80 tps: 102.90 qps: 1640.71 (r/w/o: 1434.91/0.00/205.80) lat (ms,95%): 2082.91 err/s: 0.00 reconn/s: 0.00

PG单机OLTP读写混合场景

[ 10s ] thds: 80 tps: 8.29 qps: 249.21 (r/w/o: 188.56/36.07/24.58) lat (ms,95%): 8638.96 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 80 tps: 13.51 qps: 276.96 (r/w/o: 195.21/54.73/27.02) lat (ms,95%): 10343.39 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 80 tps: 15.90 qps: 308.30 (r/w/o: 214.70/61.80/31.80) lat (ms,95%): 9118.47 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 80 tps: 16.50 qps: 324.00 (r/w/o: 224.40/66.60/33.00) lat (ms,95%): 8638.96 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 80 tps: 15.10 qps: 308.70 (r/w/o: 217.20/61.30/30.20) lat (ms,95%): 9452.83 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 80 tps: 17.50 qps: 358.40 (r/w/o: 252.70/70.70/35.00) lat (ms,95%): 9118.47 err/s: 0.00 reconn/s: 0.00

PG单机OLTP只读场景压测

[ 10s ] thds: 80 tps: 317.53 qps: 5149.51 (r/w/o: 4506.46/0.00/643.05) lat (ms,95%): 1032.01 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 80 tps: 525.90 qps: 8423.06 (r/w/o: 7371.25/0.00/1051.81) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 80 tps: 789.40 qps: 12622.80 (r/w/o: 11044.10/0.00/1578.70) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 80 tps: 1327.89 qps: 21255.90 (r/w/o: 18600.01/0.00/2655.89) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 80 tps: 1347.00 qps: 21545.69 (r/w/o: 18851.69/0.00/2694.00) lat (ms,95%): 102.97 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 80 tps: 1450.39 qps: 23206.88 (r/w/o: 20306.30/0.00/2900.59) lat (ms,95%): 95.81 err/s: 0.00 reconn/s: 0.00

四、测试结果

OB集群占用空间大约628G
MYSQL单机占用空间大约1.4T
PG单机数据量大约860G

读写混合场景下
OB 53倍领先 >PG≈MYSQL PG、MYSQL几乎无法使用

只读场景下
OB 2.5倍领先>PG 10倍领先>MYSQL