一、环境准备
1、安装规划
使用anolis8.9系统，1个zone，6台主机，1为rootserver，2 3安装obproxy 4安装ocp exp
关闭防火墙，避免影响实验
systemctl stop firewalld && systemctl disable firewalld
2、使用obd在线部署
当您选择在线部署时，可以参考本节命令在中控机上安装 obd。
sudo yum install -y yum-utils
sudo yum-config-manager –add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo
sudo yum install -y ob-deploy
source /etc/profile.d/obd.sh
3、安装java环境，OCP Express要求java1.8
https://www.java.com/en/download/manual.jsp
下载 Linux x64 RPM filesize: 104.47
安装
[root@ob2 ~]# rpm -ivh jre-8u431-linux-x64.rpm
警告：jre-8u431-linux-x64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID ad986da3: NOKEY
Verifying… ################################# [100%]
准备中… ################################# [100%]
正在升级/安装…
1:jre-1.8-1.8.0_431-10 ################################# [100%]
[root@ob2 ~]# java -version
java version “1.8.0_431”
Java(TM) SE Runtime Environment (build 1.8.0_431-b10)
Java HotSpot(TM) 64-Bit Server VM (build 25.431-b10, mixed mode)
4、配置优化
每台主机执行
echo -e “* soft nofile 655350\n* hard nofile 655350” >> /etc/security/limits.d/nofile.conf
echo “vm.max_map_count=655360” >> /etc/sysctl.conf; sysctl -p
echo “fs.file-max=6573688” >> /etc/sysctl.conf; sysctl -p
5、启动白屏安装
注意，命令需要在本地终端执行，不可以远程ssh执行 obd web
如果ssh远程执行obd web，会报
ceanbase-ce:ssh
原因：OBD-1013 : root@x.x.x.x connect failed: could not get keys from ssh-agent
建议：手动修复 Please check user config and network
二、安装环境
我这里安装OCP Express
我这里rootserver、ocpEXP、obproxy分散在各个主机上
等待部署完成
安装完成，记好密码
三、配置使用
登录ocp exp
http://IP:8180/
配置租户
等待租户创建完成后，创建数据库和用户
连接测试
可通过rootserver2881端口连接
也可通过obproxy 2883端口连接
四、性能测试
均在同一个超融合集群中
MYSQL8.4.3单机 32C64G
OB集群 1zone6主机 8Cx6 16Gx6
table_size=1000000 tables=350 threads=80 数据量大约140G
安装SysBench
配置epel源
yum install -y https://mirrors.aliyun.com/epel/epel-release-latest-8.noarch.rpm
sed -i s|^#baseurl=https://download.example/pub|baseurl=https://mirrors.aliyun.com| /etc/yum.repos.d/epel*
sed -i s|^metalink|#metalink| /etc/yum.repos.d/epel*
安装SysBench
yum install sysbench
OB集群OLTP读写混合场景清除数据
sysbench /usr/share/sysbench/oltp_read_write.lua –mysql-host=IP地址 –mysql-port=端口 –mysql-db=数据库名 –mysql-user=用户名 –mysql-password=密码 –table_size=1000000 –tables=350 –threads=80 –report-interval=10 —rand-type=uniform —time=60 cleanup
初始化测试数据
sysbench /usr/share/sysbench/oltp_read_write.lua –mysql-host=IP地址 –mysql-port=端口 –mysql-db=数据库名 –mysql-user=用户名 –mysql-password=密码 –table_size=1000000 –tables=350 –threads=80 –report-interval=10 —rand-type=uniform —time=60 prepare
执行测试
sysbench /usr/share/sysbench/oltp_read_write.lua –mysql-host=IP地址 –mysql-port=端口 –mysql-db=数据库名 –mysql-user=用户名 –mysql-password=密码 –table_size=1000000 –tables=350 –threads=80 –report-interval=10 —time=60 —rand-type=uniform –db-ps-mode=disable run
OB集群OLTP只读场景压测准备数据
sysbench –db-driver=mysql –mysql-host=IP地址 –mysql-port=端口 –mysql-user=用户名 –mysql-password=密码 –mysql-db=数据库名 –table_size=1000000 –tables=350 –events=0 –time=300 –threads=80 oltp_read_only prepare
运行压测
sysbench –db-driver=mysql –mysql-host=IP地址 –mysql-port=端口 –mysql-user=用户名 –mysql-password=密码 –mysql-db=数据库名 –table_size=1000000 –tables=350 –events=0 –time=300 –threads=80 –percentile=95 –report-interval=1 oltp_read_only run
清理数据
sysbench –db-driver=mysql –mysql-host=IP地址 –mysql-port=端口 –mysql-user=用户名 –mysql-password=密码 –mysql-db=数据库名 –table_size=1000000 –tables=350 –events=0 –time=300 –threads=80 –percentile=95 oltp_read_only cleanup
MYSQL单机OLTP读写混合场景清除数据
sysbench /usr/share/sysbench/oltp_read_write.lua –mysql-host=IP地址 –mysql-port=端口 –mysql-db=数据库名 –mysql-user=用户名 –mysql-password=密码 –table_size=1000000 –tables=350 –threads=80 –report-interval=10 —rand-type=uniform —time=60 cleanup
初始化测试数据
sysbench /usr/share/sysbench/oltp_read_write.lua –mysql-host=IP地址 –mysql-port=端口 –mysql-db=数据库名 –mysql-user=用户名 –mysql-password=密码 –table_size=1000000 –tables=350 –threads=80 –report-interval=10 —rand-type=uniform —time=60 prepare
执行测试
sysbench /usr/share/sysbench/oltp_read_write.lua –mysql-host=IP地址 –mysql-port=端口 –mysql-db=数据库名 –mysql-user=用户名 –mysql-password=密码 –table_size=1000000 –tables=350 –threads=80 –report-interval=10 —time=60 —rand-type=uniform –db-ps-mode=disable run
MYSQL单机OLTP只读场景压测准备数据
sysbench –db-driver=mysql –mysql-host=IP地址 –mysql-port=端口 –mysql-user=用户名 –mysql-password=密码 –mysql-db=数据库名 –table_size=1000000 –tables=350 –events=0 –time=300 –threads=80 oltp_read_only prepare
运行压测
sysbench –db-driver=mysql –mysql-host=IP地址 –mysql-port=端口 –mysql-user=用户名 –mysql-password=密码 –mysql-db=数据库名 –table_size=1000000 –tables=350 –events=0 –time=300 –threads=80 –percentile=95 –report-interval=1 oltp_read_only run
清理数据
sysbench –db-driver=mysql –mysql-host=IP地址 –mysql-port=端口 –mysql-user=用户名 –mysql-password=密码 –mysql-db=数据库名 –table_size=1000000 –tables=350 –events=0 –time=300 –threads=80 –percentile=95 oltp_read_only cleanup
测试结果
MYSQL单机OLTP读写混合场景
[ 10s ] thds: 80 tps: 279.82 qps: 5649.14 (r/w/o: 3961.11/1120.39/567.64) lat (ms,95%): 467.30 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 80 tps: 336.51 qps: 6786.91 (r/w/o: 4759.38/1354.52/673.01) lat (ms,95%): 390.30 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 80 tps: 246.00 qps: 4918.91 (r/w/o: 3441.11/985.80/492.00) lat (ms,95%): 520.62 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 80 tps: 306.70 qps: 6129.61 (r/w/o: 4290.91/1225.30/613.40) lat (ms,95%): 434.83 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 80 tps: 354.50 qps: 7104.99 (r/w/o: 4974.69/1421.30/709.00) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 80 tps: 339.50 qps: 6785.12 (r/w/o: 4741.72/1364.40/679.00) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00
MYSQL单机OLTP只读场景压测
[ 1s ] thds: 80 tps: 2322.83 qps: 37870.75 (r/w/o: 33145.27/0.00/4725.49) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 80 tps: 2927.99 qps: 46815.91 (r/w/o: 40960.92/0.00/5854.99) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 80 tps: 3440.21 qps: 55064.31 (r/w/o: 48183.89/0.00/6880.41) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 80 tps: 3973.17 qps: 63529.73 (r/w/o: 55585.39/0.00/7944.34) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 80 tps: 4175.91 qps: 66879.59 (r/w/o: 58526.76/0.00/8352.82) lat (ms,95%): 26.20 err/s: 0.00 reconn/s: 0.00
[ 76s ] thds: 80 tps: 4714.21 qps: 75534.39 (r/w/o: 66105.97/0.00/9428.42) lat (ms,95%): 22.69 err/s: 0.00 reconn/s: 0.00
[ 77s ] thds: 80 tps: 4679.42 qps: 74748.76 (r/w/o: 65388.92/0.00/9359.85) lat (ms,95%): 23.10 err/s: 0.00 reconn/s: 0.00
[ 78s ] thds: 80 tps: 4737.18 qps: 75766.94 (r/w/o: 66295.57/0.00/9471.37) lat (ms,95%): 22.28 err/s: 0.00 reconn/s: 0.00
[ 79s ] thds: 80 tps: 4650.49 qps: 74527.85 (r/w/o: 65222.87/0.00/9304.98) lat (ms,95%): 23.10 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 80 tps: 4664.70 qps: 74592.18 (r/w/o: 65266.78/0.00/9325.40) lat (ms,95%): 22.69 err/s: 0.00 reconn/s: 0.00
OB集群 1zone6主机OLTP读写混合场景
[ 10s ] thds: 80 tps: 194.03 qps: 3980.77 (r/w/o: 2799.12/785.60/396.05) lat (ms,95%): 960.30 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 80 tps: 585.61 qps: 11728.84 (r/w/o: 8211.80/2345.83/1171.21) lat (ms,95%): 267.41 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 80 tps: 1159.31 qps: 23173.24 (r/w/o: 16218.30/4636.33/2318.61) lat (ms,95%): 104.84 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 80 tps: 1573.49 qps: 31474.50 (r/w/o: 22032.76/6294.86/3146.88) lat (ms,95%): 70.55 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 80 tps: 1568.68 qps: 31364.68 (r/w/o: 21954.30/6272.92/3137.46) lat (ms,95%): 89.16 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 80 tps: 1876.77 qps: 37543.14 (r/w/o: 26280.21/7509.89/3753.04) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
OB集群 1zone6主机OLTP只读场景压测
[ 1s ] thds: 80 tps: 258.36 qps: 4791.07 (r/w/o: 4194.56/0.00/596.51) lat (ms,95%): 646.19 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 80 tps: 523.06 qps: 8405.99 (r/w/o: 7359.87/0.00/1046.12) lat (ms,95%): 601.29 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 80 tps: 678.97 qps: 10798.50 (r/w/o: 9440.56/0.00/1357.94) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 80 tps: 974.06 qps: 15628.02 (r/w/o: 13679.90/0.00/1948.13) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 80 tps: 1244.00 qps: 19876.04 (r/w/o: 17388.04/0.00/2488.01) lat (ms,95%): 207.82 err/s: 0.00
[ 76s ] thds: 80 tps: 5320.92 qps: 85129.68 (r/w/o: 74486.85/0.00/10642.83) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00
[ 77s ] thds: 80 tps: 5331.83 qps: 85282.34 (r/w/o: 74620.67/0.00/10661.67) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00
[ 78s ] thds: 80 tps: 5279.20 qps: 84432.24 (r/w/o: 73874.84/0.00/10557.41) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00
[ 79s ] thds: 80 tps: 5431.77 qps: 86835.31 (r/w/o: 75971.77/0.00/10863.54) lat (ms,95%): 18.61 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 80 tps: 5411.58 qps: 86724.27 (r/w/o: 75901.11/0.00/10823.16) lat (ms,95%): 18.61 err/s: 0.00 reconn/s: 0.00
总结
在只读场景下，OB集群性能领先约15%左右
在读写混合场景下，OB集群性能领先约6倍左右