OceanBase 3.1.2版本测试报告

已于 2022-03-03 12:54:35 修改
阅读量3.3k 收藏 4
点赞数 1
分类专栏： 笔记 文章标签： linux 服务器 centos 数据库
于 2022-03-02 22:50:48 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_54238817/article/details/123242529
版权

测试场景

OLTP场景，测试工具使用的是开源版本BenchmarkSQL5.0。

说明：我们已经通过修改源代码，实现了MySQL的支持。在单位进行分布式数据库POC选型时，都是使用该版本。

集群拓扑及硬件配置信息

7台物理机，使用OBD直接部署为2:2:2架构的OceanBase 3.1.2集群。

  • 机器信息如下：
机器类型主机信息
IP10.144.2.112,10.144.2.111,10.144.2.110,10.144.2.109,
10.144.2.108,10.144.2.107,10.144.2.107
网卡名bond0
OSCentOS Linux release 7.9.2009 (Core)
CPU64
内存256G，可用230G+
磁盘1/data/1
磁盘2/data/2
  • 机器规划如下：
角色机器备注
OBD10.144.2.112中控机，自动化安装部署软件，压力测试执行机
OBSERVER10.144.2.111OceanBase数据库，zone1
10.144.2.110OceanBase数据库，zone2
10.144.2.109OceanBase数据库，zone3
10.144.2.108OceanBase数据库，zone1
10.144.2.107OceanBase数据库，zone2
10.144.2.106OceanBase数据库，zone3
OBPROXY10.144.2.111OceanBase访问反向代理
10.144.2.110OceanBase访问反向代理
10.144.2.109OceanBase访问反向代理
10.144.2.108OceanBase访问反向代理
10.144.2.107OceanBase访问反向代理
10.144.2.106OceanBase访问反向代理
OBCLIENT10.144.2.112OceanBase命令行客户端

租户信息（内存、cpu、primary zone 等信息）

参数优化

内核参数修改

修改配置文件：vi /etc/sysctl.conf

添加以下内容

net.core.somaxconn = 2048
net.core.netdev_max_backlog = 10000
net.core.rmem_default = 16777216
net.core.wmem_default = 16777216
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216

net.ipv4.ip_local_port_range = 3500 65535
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.tcp_syncookies = 0
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.ipv4.tcp_max_syn_backlog = 16384
net.ipv4.tcp_fin_timeout = 15
net.ipv4.tcp_max_syn_backlog = 16384
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_slow_start_after_idle=0

vm.swappiness = 0
vm.min_free_kbytes = 2097152
vm.max_map_count=655360
fs.aio-max-nr=1048576

让配置生效

sysctl -p

会话变量修改

OceanBase 数据库的进程涉及的限制包括线程最大栈空间大小（Stack）、最大文件句柄数（Open Files）和 core 文件大小 （Core File Size）。

修改配置文件：vi /etc/security/limits.conf

  • 在全局级别修改。注意修改后，已经登录的会话需要退出重登录才生效。

将会话级别的最大栈空间大小设置为 unlimited，最大文件句柄数设置为 655350，Core 文件大小设置为 unlimited 。 修改 /etc/security/limits.conf 配置文件，如果已有设置值低于这个设置值

* soft nofile 655360
* hard nofile 655360
* soft nproc 655360
* hard nproc 655360
* soft core unlimited
* hard core unlimited
* soft stack unlimited
* hard stack unlimited

查看配置方法。退出当前会话，重新登录。执行以下命令，查看配置是否生效：

ulimit -a

关闭 SELinux

修改 SELinux 配置文件中的 SELINUX 选项。 注意：必须使用注释中的三个值之一。如果写错了，机器重启后操作系统会报错起不来，那时候就只能进入单用户模式修改了。

修改配置文件：vi /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled

配置文件修改后只会重启主机后生效，还需要使用下面命令立即生效。

setenforce 0

关闭防火墙

查看防火墙状态

systemctl status firewalld

如果是 inactive 那就不用管。如果是 active，那就永久关闭

systemctl disable firewalld 
systemctl stop firewalld
systemctl status firewalld

判断是否使用 ntpd同步时间。

systemctl status ntpd
Unit ntpd.service could not be found.

如果提示上面这个信息，表示没有使用 ntpd。 如果提示有 ntpd 服务，就卸载 ntpd 软件，检查chrony服务。

chrony服务检查

  • 查看时间同步活动

    chronyc activity

  • 查看时间服务器

    chronyc sources

  • 查看同步状态

    chronyc sources -v

  • 校准时间服务器：

    chronyc tracking

安装chrony服务

如果没有安装，可以采用 YUM 安装方法。您也可以下载相应的 RPM 包安装。

我的服务器已经安装，以下安装方法参考社区教程，仅供参考：

yum -y install chrony

chrony 配置说明

chrony 服务守护进程名是 chronyd，chronyc 是用来监控 chronyd 性能和配置参数的命令行工具。 chrony 的主配置文件: /etc/chrony.conf 。配置方法如下：

vi /etc/chrony.conf

# server 后面跟时间同步服务器
# 使用pool.ntp.org 项目中的公共服务器。按 server 配置，理论上您想添加多少时间服务器都可以。
# 或者使用 阿里云的 ntp 服务器
# Please consider joining the pool (http://www.pool.ntp.org/join.html).
server ntp.cloud.aliyuncs.com minpoll 4 maxpoll 10 iburst
server ntp.aliyun.com minpoll 4 maxpoll 10 iburst
server ntp1.aliyun.com minpoll 4 maxpoll 10 iburst
server ntp1.cloud.aliyuncs.com minpoll 4 maxpoll 10 iburst
server ntp10.cloud.aliyuncs.com minpoll 4 maxpoll 10 iburst

# 如果是测试环境，没有时间同步服务器，那就选取一台配置为时间同步服务器。
# 如果选中的是本机，则取消下面 server 注释
#server 127.127.1.0

# 根据实际时间计算出服务器增减时间的比率，然后记录到一个文件中，在系统重启后为系统做出最佳时间补偿调整。
driftfile /var/lib/chrony/drift

# chronyd 根据需求减慢或加速时间调整，
# 在某些情况下系统时钟可能漂移过快，导致时间调整用时过长。
# 该指令强制 chronyd 调整时期，大于某个阀值时步进调整系统时钟。
# 只有在因 chronyd 启动时间超过指定的限制时（可使用负值来禁用限制）没有更多时钟更新时才生效。
makestep 1.0 3

# 将启用一个内核模式，在该模式中，系统时间每11分钟会拷贝到实时时钟（RTC）。
rtcsync

# Enable hardware timestamping on all interfaces that support it.
# 通过使用hwtimestamp指令启用硬件时间戳
#hwtimestamp eth0
#hwtimestamp eth1
#hwtimestamp *

# Increase the minimum number of selectable sources required to adjust
# the system clock.
#minsources 2

# 指定一台主机、子网，或者网络以允许或拒绝NTP连接到扮演时钟服务器的机器
#allow 192.168.0.0/16
#deny 192.168/16

# 即使没有同步到时间源，也要服务时间
local stratum 10

# 指定包含NTP验证密钥的文件。
#keyfile /etc/chrony.keys

# 指定日志文件的目录。
logdir /var/log/chrony



# Select which information is logged.
#log measurements statistics tracking

最简单的配置文件如下：

server 127.127.1.0
allow 10.144.0.0/16
local stratum 10

基准压测说明

  • 使用 OBD 部署OceanBase 数据库集群。TPC-C测试程序benchmarkSQL5.0 单独部署在一台机器上, 作为客户端的压力执行机器。

  • OceanBase 集群规模为 2：2：2。部署成功后，新建执行 TPC-C 测试的租户及用户：租户tpcc，用户benchmarksql。将租户的 primary_zone 设置为 RANDOM。RANDOM 表示新建表分区的 Leader 随机到这 6 台机器。

  • 测试规格

warehouses=2000
loadWorkers=200
terminals=200
runMins=10
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
  • 测试并发数：200,400,600,800
  • 测试时间10分钟
  • 加仓结束后，执行合并

操作过程

安装 Benchmark SQL

按照以下步骤安装 Benchmark SQL：

  1. 下载 Benchmark SQL。

    本次使用的是开源社区进行适配了mysql的benchmarksql。所以不包含修改源代码适配过程。

  2. 解压 Benchmark SQL。

    unzip ./benchmarksql-5.0.zip

配置安装用户

  • 添加普通用户
groupadd -g 2000 admin 
useradd -u 2000 -g 2000 admin

  • passwd admin

    输入密码即可

  • 添加admin的sudo权限

[root@localhost ~]# visudo

在以下内容后添加内容：

## Allow root to run any commands anywhere
root    ALL=(ALL)       ALL

添加以下内容：

admin ALL=(ALL) ALL

初始化目录

查看磁盘分区情况
[root@localhost ~]# lsblk
NAME            MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT
sda               8:0    0   1.8T  0 disk 
sdb               8:16   0   1.8T  0 disk 
sdc               8:32   0 446.1G  0 disk 
├─sdc1            8:33   0     1G  0 part /boot/efi
├─sdc2            8:34   0     1G  0 part /boot
└─sdc3            8:35   0 444.1G  0 part 
  ├─vg00-lvroot 253:0    0   100G  0 lvm  /
  └─vg00-lvswap 253:1    0    16G  0 lvm  [SWAP]
loop0             7:0    0   387M  0 loop /usr/hxm/loadrunner
查看磁盘分区和挂载情况
[root@localhost ~]# lsblk -f
NAME            FSTYPE      LABEL       UUID                                   MOUNTPOINT
sda             LVM2_member             4bEcx7-m6bx-mL41-vKEO-DvOC-zEgE-H32Add 
sdb                                                                            
sdc                                                                            
├─sdc1          vfat                    0320-6ED4                              /boot/efi
├─sdc2          xfs                     93739afe-f19e-436e-aefb-be9522931fdf   /boot
└─sdc3          LVM2_member             22LfZ1-VwqX-MMKK-97L0-1l0T-iDlG-YPXHZ5 
  ├─vg00-lvroot xfs         lvroot      c2918725-45ec-4415-a7c1-d10598630178   /
  └─vg00-lvswap swap        lvswap      b9ce8c9c-03b2-4ceb-9044-945493566db5   [SWAP]
loop0           udf         T7330-15010 2010-08-25-10-57-00-0                  /usr/hxm/loadrunner
查看磁盘使用情况
[root@localhost ~]# df -hl
Filesystem               Size  Used Avail Use% Mounted on
devtmpfs                 126G     0  126G   0% /dev
tmpfs                    126G  4.0K  126G   1% /dev/shm
tmpfs                    126G  116M  126G   1% /run
tmpfs                    126G     0  126G   0% /sys/fs/cgroup
/dev/mapper/vg00-lvroot  100G   43G   58G  43% /
/dev/sdc2               1014M  227M  788M  23% /boot
/dev/sdc1               1022M   12M 1011M   2% /boot/efi
tmpfs                     26G   12K   26G   1% /run/user/42
tmpfs                     26G     0   26G   0% /run/user/0
/dev/loop0               386M  386M     0 100% /usr/hxm/loadrunner
查看系统支持的分区类型

mkfs然后敲两下tab键查看我们的系统支持的分区类型

[root@localhost ~]# mkfs
mkfs         mkfs.btrfs   mkfs.cramfs  mkfs.ext2    mkfs.ext3    mkfs.ext4    mkfs.fat     mkfs.minix   mkfs.msdos   mkfs.vfat    mkfs.xfs
格式化分区
[root@localhost ~]# mkfs -t ext4 /dev/sda
mke2fs 1.42.9 (28-Dec-2013)
/dev/sda is entire device, not just one partition!
Proceed anyway? (y,n) y
Discarding device blocks: done                            
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
117211136 inodes, 468843606 blocks
23442180 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2617245696
14308 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
        4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968, 
        102400000, 214990848

Allocating group tables: done                            
Writing inode tables: done                            
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done     


[root@localhost ~]# mkfs -t ext4 /dev/sdb
mke2fs 1.42.9 (28-Dec-2013)
/dev/sdb is entire device, not just one partition!
Proceed anyway? (y,n) y
Discarding device blocks: done                            
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
117211136 inodes, 468843606 blocks
23442180 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2617245696
14308 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
        4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968, 
        102400000, 214990848

Allocating group tables: done                            
Writing inode tables: done                            
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done
查看磁盘分区和挂载情况
[root@localhost ~]# lsblk -f
NAME            FSTYPE      LABEL       UUID                                   MOUNTPOINT
sda             ext4                    90551db9-8abf-4b2e-80f7-27cb6d3139ce   
sdb             ext4                    31120181-6143-4385-bc62-8841e21941db   
sdc                                                                            
├─sdc1          vfat                    0320-6ED4                              /boot/efi
├─sdc2          xfs                     93739afe-f19e-436e-aefb-be9522931fdf   /boot
└─sdc3          LVM2_member             22LfZ1-VwqX-MMKK-97L0-1l0T-iDlG-YPXHZ5 
  ├─vg00-lvroot xfs         lvroot      c2918725-45ec-4415-a7c1-d10598630178   /
  └─vg00-lvswap swap        lvswap      b9ce8c9c-03b2-4ceb-9044-945493566db5   [SWAP]
loop0           udf         T7330-15010 2010-08-25-10-57-00-0                  /usr/hxm/loadrunner
目录创建
[root@localhost ~]# mkdir -p /data/1
[root@localhost ~]# mkdir -p /data/2
目录挂载

mount /dev/sda /data/1

这句话的意思就是把本设备的sda分区挂载到data目录下的1目录上

[root@localhost ~]# mount /dev/sda /data/1
[root@localhost ~]# mount /dev/sdb /data/2
查看磁盘分区和挂载情况

可以看到sda分区和sdb分区已经分别被成功的被挂载到/data/1he /data/2目录上了。

[root@localhost ~]# lsblk -f
NAME            FSTYPE      LABEL       UUID                                   MOUNTPOINT
sda             ext4                    90551db9-8abf-4b2e-80f7-27cb6d3139ce   /data/1
sdb             ext4                    31120181-6143-4385-bc62-8841e21941db   /data/2
sdc                                                                            
├─sdc1          vfat                    0320-6ED4                              /boot/efi
├─sdc2          xfs                     93739afe-f19e-436e-aefb-be9522931fdf   /boot
└─sdc3          LVM2_member             22LfZ1-VwqX-MMKK-97L0-1l0T-iDlG-YPXHZ5 
  ├─vg00-lvroot xfs         lvroot      c2918725-45ec-4415-a7c1-d10598630178   /
  └─vg00-lvswap swap        lvswap      b9ce8c9c-03b2-4ceb-9044-945493566db5   [SWAP]
loop0           udf         T7330-15010 2010-08-25-10-57-00-0                  /usr/hxm/loadrunner
检查挂载是否成功
[root@localhost /]# ls /data/1
lost+found
[root@localhost /]# ls /data/2
lost+found

出现lost+found 就说明我们挂载成功了。

设置可以自动挂载（永久挂载）

检查当前挂载情况：

[root@localhost /]# cat /etc/fstab 

#
# /etc/fstab
# Created by anaconda on Sat Dec 18 00:52:03 2021
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/vg00-lvroot /                       xfs     defaults        0 0
UUID=93739afe-f19e-436e-aefb-be9522931fdf /boot                   xfs     defaults        0 0
UUID=0320-6ED4          /boot/efi               vfat    defaults,uid=0,gid=0,umask=0077,shortname=winnt 0 0
/dev/mapper/vg00-lvswap swap                    swap    defaults        0 0

后我们不用UUID，直接写清楚把这台设备的哪个分区挂载到哪个目录下：

输入 vim /etc/fstab,进入修改界面：

将/data/1和data/2进行填写

#
# /etc/fstab
# Created by anaconda on Sat Dec 18 00:52:03 2021
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/vg00-lvroot /                       xfs     defaults        0 0
UUID=93739afe-f19e-436e-aefb-be9522931fdf /boot                   xfs     defaults        0 0
UUID=0320-6ED4          /boot/efi               vfat    defaults,uid=0,gid=0,umask=0077,shortname=winnt 0 0
/dev/mapper/vg00-lvswap swap                    swap    defaults        0 0
/dev/sda /data/1                                ext4    defaults        0 0
/dev/sdb /data/2                                ext4    defaults        0 0

这个意思就是把这台机器的sda分区挂载到/data/1，sdb分区挂载到/data/2，默认我们改成0 0就行。

保存退出，然后执行mount -a指令（a指auto，意思是让它自动挂载）

[root@localhost /]# mount -a
[root@localhost /]#

然后就完成自动挂载了。

更改目录权限
[root@localhost /]# chown -R admin.admin /data/1 && chown -R admin.admin /data/2
[root@localhost /]#

安装 OBD 自动化部署软件

OBD 全称是 OceanBase Deployer，是 OceanBase 社区版的命令行下自动化部署软件。 根据中控机器能否连接公网，提供离线和在线两种安装方法，您可根据实际情况选择安装方式。内网环境，选择离线安装。

下载相关软件包

软件包可通过官网下载页进行自行下载。

下载后的软件包传输到中控机上指定目录中。

ob-deploy-1.2.0-15.el7.x86_64.rpm
离线安装 OBD

在登录中控机，我这里使用了root用户进行安装。一般情况下应该是应用用户安装。可自行选择。

ob-deploy 软件默认安装在 /usr/obd 下。不同版本可能有点变化，您可通过下面命令查看位置。

[root@localhost 3.1.2_ce]# rpm -ivh ob-deploy-1.2.0-15.el7.x86_64.rpm
warning: ob-deploy-1.2.0-15.el7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID e9b4a7aa: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:ob-deploy-1.2.0-15.el7           ################################# [100%]
Installation of obd finished successfully
Please source /etc/profile.d/obd.sh to enable it
[root@localhost 3.1.2_ce]#
检查安装是否成功

后续可以使用应用用户，比如admin用户执行检查obd是否安装成功。

[admin@localhost 3.1.2_ce]$ obd cluster list
Local deploy is empty
查看.obd/目录
[admin@localhost 3.1.2_ce]$ ls ~/.obd/
cluster  lock  log  mirror  plugins  version
关于~/.obd目录创建问题

刚安装obd 后, 没有创建任何~/.obd, 是直到敲任何一个obd 命令后, 才创建的.

而且不直接创建~/.obd 的原因是, obd 可以在任何一个用户下运行, 因此不知道在哪个用户下建~/.obd

obd命令

可使用 -h 查看 obd 命令使用帮助。

[admin@localhost 3.1.2_ce]$ obd -h
Usage: obd <command> [options]

Available commands:

cluster        Deploy and manage a cluster.

mirror         Manage a component repository for OBD.

repo           Manage local repository for OBD.

test           Run test for a running deployment.

update         Update OBD.


Options:
  --version      show program's version number and exit
  -h, --help     Show help and exit.
  -v, --verbose  Activate verbose output.
将软件包加到离线仓库
删除远程仓库

使用下面命令:

注意 :下面命令需在部署运行 OBD 的操作系统用户下操作。这里是用户 admin 。

/bin/rm -rf ~/.obd/mirror/remote/OceanBase.repo
  • 使用下面命令将前面的软件包复制到本地仓库。
[admin@localhost 3.1.2_ce]$ obd mirror clone /home/admin/soft/3.1.2_ce/*.rpm
name: libobclient
version: 2.0.0
release:2.el7
arch: x86_64
md5: f73cae67e2ff5be0682ac2803aba33a7ed26430e
add /home/admin/soft/3.1.2_ce/libobclient-2.0.0-2.el7.x86_64.rpm to local mirror
name: obagent
version: 1.1.0
release:1.el7
arch: x86_64
md5: d2416fadeadba35944872467843d55da0999f298
add /home/admin/soft/3.1.2_ce/obagent-1.1.0-1.el7.x86_64.rpm to local mirror
name: obclient
version: 2.0.0
release:2.el7
arch: x86_64
md5: 1d2c3ee31f40b9d2fbf97f653f549d896b7e7060
add /home/admin/soft/3.1.2_ce/obclient-2.0.0-2.el7.x86_64.rpm to local mirror
name: ob-deploy
version: 1.2.0
release:15.el7
arch: x86_64
md5: b1cfa5fb95b6d7b06927e4ddbcdd7651f19311a9
add /home/admin/soft/3.1.2_ce/ob-deploy-1.2.0-15.el7.x86_64.rpm to local mirror
name: obproxy
version: 3.2.0
release:1.el7
arch: x86_64
md5: 8d5c6978f988935dc3da1dbec208914668dcf3b2
add /home/admin/soft/3.1.2_ce/obproxy-3.2.0-1.el7.x86_64.rpm to local mirror
name: oceanbase-ce
version: 3.1.2
release:10000392021123010.el7
arch: x86_64
md5: 7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed
add /home/admin/soft/3.1.2_ce/oceanbase-ce-3.1.2-10000392021123010.el7.x86_64.rpm to local mirror
name: oceanbase-ce-libs
version: 3.1.2
release:10000392021123010.el7
arch: x86_64
md5: 94fff0ab31de053051dba66039e3185fa390cad5
add /home/admin/soft/3.1.2_ce/oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm to local mirror
name: oceanbase-ce-utils
version: 3.1.2
release:10000392021123010.el7
arch: x86_64
md5: 6ca7db146fee526f4201508f9bd30901e487b7c5
add /home/admin/soft/3.1.2_ce/oceanbase-ce-utils-3.1.2-10000392021123010.el7.x86_64.rpm to local mirror
查看仓库的 RPM 列表。
[admin@localhost 3.1.2_ce]$ obd mirror list local
+----------------------------------------------------------------------------------------------------------+
|                                            local Package List                                            |
+--------------------+---------+-----------------------+--------+------------------------------------------+
| name               | version | release               | arch   | md5                                      |
+--------------------+---------+-----------------------+--------+------------------------------------------+
| libobclient        | 2.0.0   | 2.el7                 | x86_64 | f73cae67e2ff5be0682ac2803aba33a7ed26430e |
| obagent            | 1.1.0   | 1.el7                 | x86_64 | d2416fadeadba35944872467843d55da0999f298 |
| obclient           | 2.0.0   | 2.el7                 | x86_64 | 1d2c3ee31f40b9d2fbf97f653f549d896b7e7060 |
| ob-deploy          | 1.2.0   | 15.el7                | x86_64 | b1cfa5fb95b6d7b06927e4ddbcdd7651f19311a9 |
| obproxy            | 3.2.0   | 1.el7                 | x86_64 | 8d5c6978f988935dc3da1dbec208914668dcf3b2 |
| oceanbase-ce       | 3.1.2   | 10000392021123010.el7 | x86_64 | 7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed |
| oceanbase-ce-libs  | 3.1.2   | 10000392021123010.el7 | x86_64 | 94fff0ab31de053051dba66039e3185fa390cad5 |
| oceanbase-ce-utils | 3.1.2   | 10000392021123010.el7 | x86_64 | 6ca7db146fee526f4201508f9bd30901e487b7c5 |
+--------------------+---------+-----------------------+--------+------------------------------------------+

安装OceanBase集群

编辑部署yml文件

obd可以支持对yml参数多种多样的配置安装，其中对生产环境有autodeploy模式，可以自适应参数。我这里做性能测试，所以选择了autodeploy模式。

从官网下载模板文件后进行编辑。下载地址：https://github.com/oceanbase/obdeploy/blob/master/example/autodeploy/distributed-with-obproxy-example.yaml

## Only need to configure when remote login is required
user:
  username: admin
  password: ******
#   key_file: your ssh-key file path if need
#   port: your ssh port, default 22
#   timeout: ssh connection timeout (second), default 30
oceanbase-ce:
  servers:
    - name: obce_zone1_01
      # Please don't use hostname, only IP can be supported
      ip: 10.144.2.106
    - name: obce_zone1_02
      ip: 10.144.2.107
    - name: obce_zone2_01
      ip: 10.144.2.108
    - name: obce_zone2_02
      # Please don't use hostname, only IP can be supported
      ip: 10.144.2.109
    - name: obce_zone3_01
      ip: 10.144.2.110
    - name: obce_zone3_02
      ip: 10.144.2.111
  global:
    # The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field.
    home_path: /home/admin/oceanbase-ce
    # The directory for data storage. The default value is $home_path/store.
    data_dir: /data/1/oceanbase-ce
    # The directory for clog, ilog, and slog. The default value is the same as the data_dir value.
    redo_dir: /data/2/oceanbase-ce
    # Please set devname as the network adaptor's name whose ip is  in the setting of severs.
    # if set severs as "127.0.0.1", please set devname as "lo"
    # if current ip is 192.168.1.10, and the ip's network adaptor's name is "eth0", please use "eth0"
    devname: bond0
    # External port for OceanBase Database. The default value is 2881.DO NOT change this value after the cluster is started.
    # mysql_port: 2881
    # Internal port for OceanBase Database. The default value is 2882. DO NOT change this value after the cluster is started.
    # rpc_port: 2882
    # Defines the zone for an observer. The default value is zone1.
    # zone: zone1
    # The maximum running memory for an observer. When ignored, autodeploy calculates this value based on the current server available resource.
    # memory_limit: 58G
    # The percentage of the maximum available memory to the total memory. This value takes effect only when memory_limit is 0. The default value is 80.
    # memory_limit_percentage: 80 
    # The reserved system memory. system_memory is reserved for general tenants. The default value is 30G. Autodeploy calculates this value based on the current server available resource.
    # system_memory: 22G
    # The size of a data file. When ignored, autodeploy calculates this value based on the current server available resource.
    # datafile_size: 200G
    # The percentage of the data_dir space to the total disk space. This value takes effect only when datafile_size is 0. The default value is 90.
    # datafile_disk_percentage: 90
    # System log level. The default value is INFO.
    # syslog_level: INFO
    # Print system logs whose levels are higher than WARNING to a separate log file. The default value is true. The default value for autodeploy mode is false.
    # enable_syslog_wf: false
    # Enable auto system log recycling or not. The default value is false. The default value for autodeploy mode is on.
    # enable_syslog_recycle: true
    # The maximum number of reserved log files before enabling auto recycling. When set to 0, no logs are deleted. The default value for autodeploy mode is 4.
    # max_syslog_file_count: 4
    # Cluster name for OceanBase Database. The default value is obcluster. When you deploy OceanBase Database and obproxy, this value must be the same as the cluster_name for obproxy.
    # appname: obcluster
    # Password for root. The default value is empty.
    # root_password:
    # Password for proxyro. proxyro_password must be the same as observer_sys_password. The default value is empty.
    # proxyro_password:
  obce_zone1_01:
    zone: zone1
  obce_zone1_02:
    zone: zone1
  obce_zone2_01:
    zone: zone2
  obce_zone2_02:
    zone: zone2
  obce_zone3_01:
    zone: zone3
  obce_zone3_02:
    zone: zone3
obproxy:
  depends:
    - oceanbase-ce
  servers:
    - 10.144.2.106
    - 10.144.2.107
    - 10.144.2.108
    - 10.144.2.109
    - 10.144.2.110
    - 10.144.2.111
  global:
    # The working directory for obproxy. Obproxy is started under this directory. This is a required field.
    home_path: /home/admin/obproxy
    # External port. The default value is 2883.
    # listen_port: 2883
    # The Prometheus port. The default value is 2884.
    # prometheus_listen_port: 2884
    # rs_list is the root server list for observers. The default root server is the first server in the zone.
    # The format for rs_list is observer_ip:observer_mysql_port;observer_ip:observer_mysql_port.
    # Ignore this value in autodeploy mode.
    # rs_list: 127.0.0.1:2881
    # Cluster name for the proxy OceanBase Database. The default value is obcluster. This value must be set to the same with the appname for OceanBase Database.
    # cluster_name: obcluster
    # Password for obproxy system tenant. The default value is empty.
    # obproxy_sys_password:
    # Password for proxyro. proxyro_password must be the same with proxyro_password. The default value is empty.
    # observer_sys_password:
执行安装
[admin@localhost ~]$ obd cluster autodeploy obtest -c distributed-with-obproxy-example.yaml
oceanbase-ce-3.1.2 already installed.
obproxy-3.2.0 already installed.
Cluster param config check ok
Open ssh connection ok
Generate observer configuration ok
Generate obproxy configuration ok
oceanbase-ce-3.1.2 already installed.
obproxy-3.2.0 already installed.
+-------------------------------------------------------------------------------------------+
|                                          Packages                                         |
+--------------+---------+-----------------------+------------------------------------------+
| Repository   | Version | Release               | Md5                                      |
+--------------+---------+-----------------------+------------------------------------------+
| oceanbase-ce | 3.1.2   | 10000392021123010.el7 | 7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed |
| obproxy      | 3.2.0   | 1.el7                 | 8d5c6978f988935dc3da1dbec208914668dcf3b2 |
+--------------+---------+-----------------------+------------------------------------------+
Repository integrity check ok
Parameter check ok
Open ssh connection ok
Remote oceanbase-ce-3.1.2-7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed repository install ok
Remote oceanbase-ce-3.1.2-7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed repository lib check ok
Remote obproxy-3.2.0-8d5c6978f988935dc3da1dbec208914668dcf3b2 repository install ok
Remote obproxy-3.2.0-8d5c6978f988935dc3da1dbec208914668dcf3b2 repository lib check ok
Cluster status check ok
Initializes observer work home ok
Initializes obproxy work home ok
obtest deployed
Get local repositories and plugins ok
Open ssh connection ok
Load cluster param plugin ok
Check before start observer ok
Check before start obproxy ok
Start observer ok
observer program health check ok
Connect to observer ok
Initialize cluster
Cluster bootstrap ok
Wait for observer init ok
+------------------------------------------------+
|                    observer                    |
+--------------+---------+------+-------+--------+
| ip           | version | port | zone  | status |
+--------------+---------+------+-------+--------+
| 10.144.2.106 | 3.1.2   | 2881 | zone1 | active |
| 10.144.2.107 |         | 0    | zone1 | active |
| 10.144.2.108 | 3.1.2   | 2881 | zone2 | active |
| 10.144.2.109 |         | 0    | zone2 | active |
| 10.144.2.110 | 3.1.2   | 2881 | zone3 | active |
| 10.144.2.111 |         | 0    | zone3 | active |
+--------------+---------+------+-------+--------+

Start obproxy ok
obproxy program health check ok
Connect to obproxy ok
Initialize cluster
+------------------------------------------------+
|                    obproxy                     |
+--------------+------+-----------------+--------+
| ip           | port | prometheus_port | status |
+--------------+------+-----------------+--------+
| 10.144.2.106 | 2883 | 2884            | active |
| 10.144.2.107 | 2883 | 2884            | active |
| 10.144.2.108 | 2883 | 2884            | active |
| 10.144.2.109 | 2883 | 2884            | active |
| 10.144.2.110 | 2883 | 2884            | active |
| 10.144.2.111 | 2883 | 2884            | active |
+--------------+------+-----------------+--------+
obtest running
查看集群
[admin@localhost ~]$ obd cluster list
+------------------------------------------------------------+
|                        Cluster List                        |
+--------+---------------------------------+-----------------+
| Name   | Configuration Path              | Status (Cached) |
+--------+---------------------------------+-----------------+
| obtest | /home/admin/.obd/cluster/obtest | running         |
+--------+---------------------------------+-----------------+

创建测试租户、测试用户

创建普通测试租户

  • 使用 OBD 创建普通租户。

    [admin@localhost ~]$ obd cluster tenant create obtest -n tpcc
Get local repositories and plugins ok
[WARN] No such create_tenant plugin for obproxy-3.2.0

Open ssh connection ok
Connect to observer ok
Create tenant tpcc ok
连接OceanBase
查看新建的资源单元
MySQL [oceanbase]> SELECT unit_config_id,name,max_cpu,min_cpu,max_memory,min_memory,max_disk_size FROM __all_unit_config;
+----------------+-----------------+---------+---------+--------------+--------------+---------------+
| unit_config_id | name            | max_cpu | min_cpu | max_memory   | min_memory   | max_disk_size |
+----------------+-----------------+---------+---------+--------------+--------------+---------------+
|              1 | sys_unit_config |       5 |     2.5 |  17179869184 |  12884901888 | 1431297851392 |
|           1001 | tpcc_unit       |       9 |       9 | 141733920768 | 141733920768 | 1431297851392 |
+----------------+-----------------+---------+---------+--------------+--------------+---------------+
2 rows in set (0.002 sec)
查看集群内实例
MySQL [oceanbase]> select a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, (cpu_total-cpu_assigned) cpu_free, round(mem_total/1024/1024/1024) mem_total_gb, round((mem_total-mem_assigned)/1024/1024/1024) mem_free_gb, usec_to_time(b.last_offline_time) last_offline_time, usec_to_time(b.start_service_time) start_service_time, b.status, usec_to_time(b.stop_time) stop_time, b.build_version 
    -> from __all_virtual_server_stat a join __all_server b on (a.svr_ip=b.svr_ip and a.svr_port=b.svr_port)
    -> order by a.zone, a.svr_ip;
+-------+-------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+--------+----------------------------+----------------------------------------------------------------------------------------+
| zone  | observer          | cpu_total | cpu_free | mem_total_gb | mem_free_gb | last_offline_time          | start_service_time         | status | stop_time                  | build_version                                                                          |
+-------+-------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+--------+----------------------------+----------------------------------------------------------------------------------------+
| zone1 | 10.144.2.106:2882 |        14 |      2.5 |          149 |           5 | 1970-01-01 08:00:00.000000 | 2022-01-14 00:56:20.552658 | active | 1970-01-01 08:00:00.000000 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) |
| zone1 | 10.144.2.107:2882 |        14 |        5 |          150 |          18 | 1970-01-01 08:00:00.000000 | 2022-01-14 00:56:24.739132 | active | 1970-01-01 08:00:00.000000 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) |
| zone2 | 10.144.2.108:2882 |        14 |      2.5 |          150 |           6 | 1970-01-01 08:00:00.000000 | 2022-01-14 00:56:22.918302 | active | 1970-01-01 08:00:00.000000 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) |
| zone2 | 10.144.2.109:2882 |        14 |        5 |          150 |          18 | 1970-01-01 08:00:00.000000 | 2022-01-14 00:56:25.136243 | active | 1970-01-01 08:00:00.000000 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) |
| zone3 | 10.144.2.110:2882 |        14 |      2.5 |          149 |           5 | 1970-01-01 08:00:00.000000 | 2022-01-14 00:56:23.249400 | active | 1970-01-01 08:00:00.000000 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) |
| zone3 | 10.144.2.111:2882 |        14 |        5 |          148 |          16 | 1970-01-01 08:00:00.000000 | 2022-01-14 00:56:25.437576 | active | 1970-01-01 08:00:00.000000 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) |
+-------+-------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+--------+----------------------------+----------------------------------------------------------------------------------------+
6 rows in set (0.005 sec)
查看资源池情况
MySQL [oceanbase]> select t1.name resource_pool_name, t2.`name` unit_config_name, t2.max_cpu, t2.min_cpu, t2.max_memory/1024/1024/1024 max_mem_gb, t2.min_memory/1024/1024/1024 min_mem_gb, t3.unit_id, t3.zone, concat(t3.svr_ip,':',t3.`svr_port`) observer,t4.tenant_id, t4.tenant_name
    -> from __all_resource_pool t1 join __all_unit_config t2 on (t1.unit_config_id=t2.unit_config_id)
    ->     join __all_unit t3 on (t1.`resource_pool_id` = t3.`resource_pool_id`)
    ->     left join __all_tenant t4 on (t1.tenant_id=t4.tenant_id)
    -> order by t1.`resource_pool_id`, t2.`unit_config_id`, t3.unit_id;
+--------------------+------------------+---------+---------+------------------+------------------+---------+-------+-------------------+-----------+-------------+
| resource_pool_name | unit_config_name | max_cpu | min_cpu | max_mem_gb       | min_mem_gb       | unit_id | zone  | observer          | tenant_id | tenant_name |
+--------------------+------------------+---------+---------+------------------+------------------+---------+-------+-------------------+-----------+-------------+
| sys_pool           | sys_unit_config  |       5 |     2.5 |  16.000000000000 |  12.000000000000 |       1 | zone1 | 10.144.2.106:2882 |         1 | sys         |
| sys_pool           | sys_unit_config  |       5 |     2.5 |  16.000000000000 |  12.000000000000 |       2 | zone2 | 10.144.2.108:2882 |         1 | sys         |
| sys_pool           | sys_unit_config  |       5 |     2.5 |  16.000000000000 |  12.000000000000 |       3 | zone3 | 10.144.2.110:2882 |         1 | sys         |
| tpcc_pool          | tpcc_unit        |       9 |       9 | 132.000000000000 | 132.000000000000 |    1001 | zone1 | 10.144.2.107:2882 |      1001 | tpcc        |
| tpcc_pool          | tpcc_unit        |       9 |       9 | 132.000000000000 | 132.000000000000 |    1002 | zone1 | 10.144.2.106:2882 |      1001 | tpcc        |
| tpcc_pool          | tpcc_unit        |       9 |       9 | 132.000000000000 | 132.000000000000 |    1003 | zone2 | 10.144.2.109:2882 |      1001 | tpcc        |
| tpcc_pool          | tpcc_unit        |       9 |       9 | 132.000000000000 | 132.000000000000 |    1004 | zone2 | 10.144.2.108:2882 |      1001 | tpcc        |
| tpcc_pool          | tpcc_unit        |       9 |       9 | 132.000000000000 | 132.000000000000 |    1005 | zone3 | 10.144.2.111:2882 |      1001 | tpcc        |
| tpcc_pool          | tpcc_unit        |       9 |       9 | 132.000000000000 | 132.000000000000 |    1006 | zone3 | 10.144.2.110:2882 |      1001 | tpcc        |
+--------------------+------------------+---------+---------+------------------+------------------+---------+-------+-------------------+-----------+-------------+
9 rows in set (0.001 sec)
查看集群可用资源
MySQL [oceanbase]> select a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, cpu_assigned, (cpu_total-cpu_assigned) cpu_free, mem_total/1024/1024/1024 mem_total_gb, mem_assigned/1024/1024/1024 mem_assign_gb, (mem_total-mem_assigned)/1024/1024/1024 mem_free_gb 
    -> from __all_virtual_server_stat a join __all_server b on (a.svr_ip=b.svr_ip and a.svr_port=b.svr_port)
    -> order by a.zone, a.svr_ip
    -> ;
+-------+-------------------+-----------+--------------+----------+------------------+------------------+-----------------+
| zone  | observer          | cpu_total | cpu_assigned | cpu_free | mem_total_gb     | mem_assign_gb    | mem_free_gb     |
+-------+-------------------+-----------+--------------+----------+------------------+------------------+-----------------+
| zone1 | 10.144.2.106:2882 |        14 |         11.5 |      2.5 | 149.000000000000 | 144.000000000000 |  5.000000000000 |
| zone1 | 10.144.2.107:2882 |        14 |            9 |        5 | 150.000000000000 | 132.000000000000 | 18.000000000000 |
| zone2 | 10.144.2.108:2882 |        14 |         11.5 |      2.5 | 150.000000000000 | 144.000000000000 |  6.000000000000 |
| zone2 | 10.144.2.109:2882 |        14 |            9 |        5 | 150.000000000000 | 132.000000000000 | 18.000000000000 |
| zone3 | 10.144.2.110:2882 |        14 |         11.5 |      2.5 | 149.000000000000 | 144.000000000000 |  5.000000000000 |
| zone3 | 10.144.2.111:2882 |        14 |            9 |        5 | 148.000000000000 | 132.000000000000 | 16.000000000000 |
+-------+-------------------+-----------+--------------+----------+------------------+------------------+-----------------+
6 rows in set (0.004 sec)
查看一下资源分配细节
MySQL [oceanbase]> select t1.name resource_pool_name, t2.`name` unit_config_name, t2.max_cpu, t2.min_cpu, t2.max_memory/1024/1024/1024 max_mem_gb, t2.min_memory/1024/1024/1024 min_mem_gb, t3.unit_id, t3.zone, concat(t3.svr_ip,':',t3.`svr_port`) observer,t4.tenant_id, t4.tenant_name
    -> from __all_resource_pool t1 join __all_unit_config t2 on (t1.unit_config_id=t2.unit_config_id)
    ->     join __all_unit t3 on (t1.`resource_pool_id` = t3.`resource_pool_id`)
    ->     left join __all_tenant t4 on (t1.tenant_id=t4.tenant_id)
    -> order by t1.`resource_pool_id`, t2.`unit_config_id`, t3.unit_id
    -> ;
+--------------------+------------------+---------+---------+------------------+------------------+---------+-------+-------------------+-----------+-------------+
| resource_pool_name | unit_config_name | max_cpu | min_cpu | max_mem_gb       | min_mem_gb       | unit_id | zone  | observer          | tenant_id | tenant_name |
+--------------------+------------------+---------+---------+------------------+------------------+---------+-------+-------------------+-----------+-------------+
| sys_pool           | sys_unit_config  |       5 |     2.5 |  16.000000000000 |  12.000000000000 |       1 | zone1 | 10.144.2.106:2882 |         1 | sys         |
| sys_pool           | sys_unit_config  |       5 |     2.5 |  16.000000000000 |  12.000000000000 |       2 | zone2 | 10.144.2.108:2882 |         1 | sys         |
| sys_pool           | sys_unit_config  |       5 |     2.5 |  16.000000000000 |  12.000000000000 |       3 | zone3 | 10.144.2.110:2882 |         1 | sys         |
| tpcc_pool          | tpcc_unit        |       9 |       9 | 132.000000000000 | 132.000000000000 |    1001 | zone1 | 10.144.2.107:2882 |      1001 | tpcc        |
| tpcc_pool          | tpcc_unit        |       9 |       9 | 132.000000000000 | 132.000000000000 |    1002 | zone1 | 10.144.2.106:2882 |      1001 | tpcc        |
| tpcc_pool          | tpcc_unit        |       9 |       9 | 132.000000000000 | 132.000000000000 |    1003 | zone2 | 10.144.2.109:2882 |      1001 | tpcc        |
| tpcc_pool          | tpcc_unit        |       9 |       9 | 132.000000000000 | 132.000000000000 |    1004 | zone2 | 10.144.2.108:2882 |      1001 | tpcc        |
| tpcc_pool          | tpcc_unit        |       9 |       9 | 132.000000000000 | 132.000000000000 |    1005 | zone3 | 10.144.2.111:2882 |      1001 | tpcc        |
| tpcc_pool          | tpcc_unit        |       9 |       9 | 132.000000000000 | 132.000000000000 |    1006 | zone3 | 10.144.2.110:2882 |      1001 | tpcc        |
+--------------------+------------------+---------+---------+------------------+------------------+---------+-------+-------------------+-----------+-------------+
9 rows in set (0.006 sec)
查询剩余可用资源
MySQL [oceanbase]> select a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, cpu_assigned, (cpu_total-cpu_assigned) cpu_free, mem_total/1024/1024/1024 mem_total_gb, mem_assigned/1024/1024/1024 mem_assign_gb, (mem_total-mem_assigned)/1024/1024/1024 mem_free_gb  from __all_virtual_server_stat a join __all_server b on (a.svr_ip=b.svr_ip and a.svr_port=b.svr_port) order by a.zone, a.svr_ip;
+-------+-------------------+-----------+--------------+----------+------------------+------------------+-----------------+
| zone  | observer          | cpu_total | cpu_assigned | cpu_free | mem_total_gb     | mem_assign_gb    | mem_free_gb     |
+-------+-------------------+-----------+--------------+----------+------------------+------------------+-----------------+
| zone1 | 10.144.2.106:2882 |        14 |         11.5 |      2.5 | 149.000000000000 | 144.000000000000 |  5.000000000000 |
| zone1 | 10.144.2.107:2882 |        14 |            9 |        5 | 150.000000000000 | 132.000000000000 | 18.000000000000 |
| zone2 | 10.144.2.108:2882 |        14 |         11.5 |      2.5 | 150.000000000000 | 144.000000000000 |  6.000000000000 |
| zone2 | 10.144.2.109:2882 |        14 |            9 |        5 | 150.000000000000 | 132.000000000000 | 18.000000000000 |
| zone3 | 10.144.2.110:2882 |        14 |         11.5 |      2.5 | 149.000000000000 | 144.000000000000 |  5.000000000000 |
| zone3 | 10.144.2.111:2882 |        14 |            9 |        5 | 148.000000000000 | 132.000000000000 | 16.000000000000 |
+-------+-------------------+-----------+--------------+----------+------------------+------------------+-----------------+
6 rows in set (0.001 sec)
查看成功创建的测试租户
MySQL [oceanbase]> select tenant_id,tenant_name,primary_zone from __all_tenant;
+-----------+-------------+-------------------+
| tenant_id | tenant_name | primary_zone      |
+-----------+-------------+-------------------+
|         1 | sys         | zone1;zone2,zone3 |
|      1001 | tpcc        | RANDOM            |
+-----------+-------------+-------------------+
2 rows in set (0.000 sec)
创建测试用户
创建测试用户

使用测试租户的root用户登录

[admin@localhost ~]$ mysql -h10.144.2.106 -uroot@tpcc -P2883 -p -c -A oceanbase
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]>

然后执行创建普通用户

MySQL [oceanbase]> create user benchmarksql@'%' identified by 'benchmarksql' ;
Query OK, 0 rows affected (0.03 sec)
查看创建成功的测试用户
MySQL [oceanbase]> SELECT user FROM mysql.user;
+--------------+
| user         |
+--------------+
| root         |
| ORAAUDITOR   |
| benchmarksql |
+--------------+
3 rows in set (0.06 sec)
授权
MySQL [oceanbase]> grant all privileges on *.* to benchmarksql@'%';
Query OK, 0 rows affected (0.02 sec)
查看授权
MySQL [oceanbase]> show grants for benchmarksql;
+-----------------------------------------------+
| Grants for benchmarksql@%                     |
+-----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'benchmarksql' |
+-----------------------------------------------+
1 row in set (0.01 sec)
创建测试数据库
测试用户登录

退出sys租户登录，使用新建测试用户登录。

[admin@localhost ~]$ obclient -h10.144.2.106 -ubenchmarksql@tpcc  -P2883 -p****** -c -A oceanbase
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]>
创建业务库
MySQL [oceanbase]> create database benchmark;
Query OK, 1 row affected (0.018 sec)

适配OceanBase

这里测试实验的是已经适配了mysql数据库的benchmarksql.

创建ob测试使用的配置文件

benchmarksql\run文件夹内创建prop.ob文件。

prop.ob中的参数说明：

  1. JDBC 连接串：conn=jdbc:mysql:loadbalance://10.144.2.106:2883,10.144.2.107:2883,10.144.2.108:2883,10.144.2.109:2883,10.144.2.110:2883,10.144.2.111:2883/benchmark?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=3000000
    user=benchmarksql@tpcc
    password=benchmarksql

  2. rewriteBatchedStatements：

  • 参数非常重要，会严重影响导数据效率，不可以忽略。
  • 如果导数据较慢，可以用对应租户登录上去通过show full processlist检查是否开启。
  • new order事务中也用到了batch update，因此导数和benchmark阶段都需要开启。

  1. 并发数量（terminals）：200，mysql 租户配置下并发需要结合具体配置动态调整。

  2. useLocalSessionState：是否使用autocommit，read_only和transaction isolation的内部值(jdbc端的本地值)，建议设置为true，如果设置为false，则需要发语句到远端请求，增加发送请求频次，影响性能。

  3. warehouses/loadWorkers这两项用于设置压测数据量，可以适当调整。

  4. numTerminals > 0 && numTerminals <= 10*numWarehouses，terminals的范围需要在这个区间内。

  5. db=mysql 目前开源版只支持mysql租户，所以这里设置mysql

  6. warehouses：指定仓库数。

    通常仓库数就决定了这个性能测试理论上的成绩。如果期望测试结果越高，仓库数就不能太低。生产环境机器测试，建议 5000 仓库起步。如果机器配置较差，建议 100 仓起步。

  7. loadWorkers：指定仓库数据加载时的并发。

    如果机器配置很好，该值可以设置大一些，比如说 100 个。 如果机器配置不高（尤其是内存），该值需要设置小一些，如 10 个并发。并发指定得过高，可能导致内存消耗太快，出现报错，导致数据加载前功尽弃。

  8. terminals：指定性能压测时的并发数。

    建议并发数不要高于仓库数 * 10 。否则，会有不必要的锁等待。在生产环境中，该并发数设置到 1000 就很高了。一般环境测试建议从 100 开始。

  9. runMins：指定性能测试持续的时间。

    时间越久，越能考验数据库的性能和稳定性。建议不要少于 10 分钟。生产环境中机器建议不少于 1 小时。

  10. LoadStartW 和 LoadStopW：指定补仓时的开始值和截止值。

    如果导数据时发现某个仓库数据导入失败（大事务超时），您可以指定这个仓库重新导入。

修改建表语句

修改benchmarksql/run/sql.mysql/tableCreates.sql

create table bmsql_config (
  cfg_name    varchar(30) primary key,
  cfg_value   varchar(50)
);

-- drop tablegroup tpcc_group;
create tablegroup tpcc_group partition by hash partitions 128;

create table bmsql_warehouse (
  w_id        integer   not null,
  w_ytd       decimal(12,2),
  w_tax       decimal(4,4),
  w_name      varchar(10),
  w_street_1  varchar(20),
  w_street_2  varchar(20),
  w_city      varchar(20),
  w_state     char(2),
  w_zip       char(9),
  primary key(w_id)
)tablegroup='tpcc_group' partition by hash(w_id) partitions 128;

create table bmsql_district (
  d_w_id       integer       not null,
  d_id         integer       not null,
  d_ytd        decimal(12,2),
  d_tax        decimal(4,4),
  d_next_o_id  integer,
  d_name       varchar(10),
  d_street_1   varchar(20),
  d_street_2   varchar(20),
  d_city       varchar(20),
  d_state      char(2),
  d_zip        char(9),
  PRIMARY KEY (d_w_id, d_id)
)tablegroup='tpcc_group' partition by hash(d_w_id) partitions 128;

create table bmsql_customer (
  c_w_id         integer        not null,
  c_d_id         integer        not null,
  c_id           integer        not null,
  c_discount     decimal(4,4),
  c_credit       char(2),
  c_last         varchar(16),
  c_first        varchar(16),
  c_credit_lim   decimal(12,2),
  c_balance      decimal(12,2),
  c_ytd_payment  decimal(12,2),
  c_payment_cnt  integer,
  c_delivery_cnt integer,
  c_street_1     varchar(20),
  c_street_2     varchar(20),
  c_city         varchar(20),
  c_state        char(2),
  c_zip          char(9),
  c_phone        char(16),
  c_since        timestamp,
  c_middle       char(2),
  c_data         varchar(500),
  PRIMARY KEY (c_w_id, c_d_id, c_id)
)tablegroup='tpcc_group' partition by hash(c_w_id) partitions 128;


create table bmsql_history (
  hist_id  integer,
  h_c_id   integer,
  h_c_d_id integer,
  h_c_w_id integer,
  h_d_id   integer,
  h_w_id   integer,
  h_date   timestamp,
  h_amount decimal(6,2),
  h_data   varchar(24)
)tablegroup='tpcc_group' partition by hash(h_w_id) partitions 128;

create table bmsql_new_order (
  no_w_id  integer   not null ,
  no_d_id  integer   not null,
  no_o_id  integer   not null,
  PRIMARY KEY (no_w_id, no_d_id, no_o_id)
)tablegroup='tpcc_group' partition by hash(no_w_id) partitions 128;

create table bmsql_oorder (
  o_w_id       integer      not null,
  o_d_id       integer      not null,
  o_id         integer      not null,
  o_c_id       integer,
  o_carrier_id integer,
  o_ol_cnt     integer,
  o_all_local  integer,
  o_entry_d    timestamp,
  PRIMARY KEY (o_w_id, o_d_id, o_id)
)tablegroup='tpcc_group' partition by hash(o_w_id) partitions 128;

create table bmsql_order_line (
  ol_w_id         integer   not null,
  ol_d_id         integer   not null,
  ol_o_id         integer   not null,
  ol_number       integer   not null,
  ol_i_id         integer   not null,
  ol_delivery_d   timestamp,
  ol_amount       decimal(6,2),
  ol_supply_w_id  integer,
  ol_quantity     integer,
  ol_dist_info    char(24),
  PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)
)tablegroup='tpcc_group' partition by hash(ol_w_id) partitions 128;

create table bmsql_item (
  i_id     integer      not null,
  i_name   varchar(24),
  i_price  decimal(5,2),
  i_data   varchar(50),
  i_im_id  integer,
  PRIMARY KEY (i_id)
) duplicate_scope='cluster';

create table bmsql_stock (
  s_w_id       integer       not null,
  s_i_id       integer       not null,
  s_quantity   integer,
  s_ytd        integer,
  s_order_cnt  integer,
  s_remote_cnt integer,
  s_data       varchar(50),
  s_dist_01    char(24),
  s_dist_02    char(24),
  s_dist_03    char(24),
  s_dist_04    char(24),
  s_dist_05    char(24),
  s_dist_06    char(24),
  s_dist_07    char(24),
  s_dist_08    char(24),
  s_dist_09    char(24),
  s_dist_10    char(24),
  PRIMARY KEY (s_w_id, s_i_id)
)tablegroup='tpcc_group' use_bloom_filter=true partition by hash(s_w_id) partitions 128;
修改索引创建语句

修改benchmarksql/run/sql.mysql/indexCreates.sql

create index bmsql_customer_idx1 on  bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;
create  index bmsql_oorder_idx1 on  bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;
修改删除语句

修改benchmarksql/run/sql.mysql/tableDrops.sql

drop table bmsql_config;
drop table bmsql_new_order;
drop table bmsql_order_line;
drop table bmsql_oorder;
drop table bmsql_history;
drop table bmsql_customer;
drop table bmsql_stock;
drop table bmsql_item;
drop table bmsql_district;
drop table bmsql_warehouse;

purge recyclebin;

-- tpcc_group
drop tablegroup tpcc_group

环境调优

OBProxy 调优

请在sys租户下执行。

在系统租户下执行命令。

（1）启动配置
alter proxyconfig set enable_strict_kernel_release=false;
alter proxyconfig set automatic_match_work_thread=false;
（2）跑性能需要调整
alter proxyconfig set proxy_mem_limited='4G'; --防止oom
alter proxyconfig set enable_compression_protocol=false; --关闭压缩，降低cpu%
alter proxyconfig set slow_proxy_process_time_threshold='500ms';
alter proxyconfig set enable_ob_protocol_v2=false;
alter proxyconfig set enable_qos=false;
alter proxyconfig set syslog_level='error';

初始后需要调整的参数

[admin@localhost ~]$ mysql -h10.144.2.106 -uroot@sys -P2883 -p -c -A oceanbase
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]> alter proxyconfig set enable_strict_kernel_release=false;
Query OK, 0 rows affected (0.01 sec)

MySQL [oceanbase]> alter proxyconfig set automatic_match_work_thread=false;
Query OK, 0 rows affected (0.01 sec)

跑性能前需要调整的参数

[admin@localhost ~]$ mysql -h10.144.2.106 -uroot@sys -P2883 -p -c -A oceanbase
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]> alter proxyconfig set proxy_mem_limited='4G';
Query OK, 0 rows affected (0.01 sec)

MySQL [oceanbase]> alter proxyconfig set enable_compression_protocol=false; 
Query OK, 0 rows affected (0.00 sec)

MySQL [oceanbase]> alter proxyconfig set slow_proxy_process_time_threshold='500ms';
Query OK, 0 rows affected (0.00 sec)

MySQL [oceanbase]> alter proxyconfig set enable_ob_protocol_v2=false;
Query OK, 0 rows affected (0.01 sec)

MySQL [oceanbase]> alter proxyconfig set enable_qos=false;
Query OK, 0 rows affected (0.00 sec)

MySQL [oceanbase]> alter proxyconfig set syslog_level='error';
Query OK, 0 rows affected (0.01 sec)

测试操作执行

以下命令均在 …/benchmarksql/run 目录下执行。按照以下步骤进行 TPC-C 测试：

导数
导数前调优
OceanBase 数据库导数据前sys租户调优

请在sys租户下执行。

在系统租户下执行 obclient -h$host_ip -P$host_port -uroot@sys -A 命令。

alter system set memory_chunk_cache_size ='0';
alter system set trx_try_wait_lock_timeout='0ms';
alter system set large_query_threshold='1s';
alter system set trace_log_slow_query_watermark='500ms';
alter system set syslog_io_bandwidth_limit='30m';
alter system set enable_async_syslog=true;
alter system set merger_warm_up_duration_time='0';
alter system set merger_switch_leader_duration_time='0';
alter system set large_query_worker_percentage=10;
alter system set builtin_db_data_verify_cycle = 0;
alter system set enable_merge_by_turn = False;
alter system set minor_merge_concurrency=30;
alter system set memory_limit_percentage = 85;
alter system set memstore_limit_percentage = 80;
alter system set freeze_trigger_percentage = 30;
alter system set enable_syslog_recycle='True';
alter system set max_syslog_file_count=100;
alter system set minor_freeze_times=500;
alter system set minor_compact_trigger=5;
alter system set max_kept_major_version_number=1;
alter system set sys_bkgd_io_high_percentage = 90;
alter system set sys_bkgd_io_low_percentage = 70;
alter system set merge_thread_count  = 45;
alter system set merge_stat_sampling_ratio = 1;
alter system set writing_throttling_trigger_percentage=75 tenant=xxx;
alter system set writing_throttling_maximum_duration='15m';
set global ob_plan_cache_percentage=20;
alter system set enable_perf_event='false';
alter system set use_large_pages='true';
alter system set micro_block_merge_verify_level=0;
alter system set builtin_db_data_verify_cycle=20;
alter system set net_thread_count=4;

[admin@localhost ~]$ mysql -h10.144.2.111 -uroot@sys -P2881 -p -c -A oceanbase
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3222798340
Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]> alter system set memory_chunk_cache_size ='0';
Query OK, 0 rows affected (0.03 sec)

MySQL [oceanbase]> alter system set trx_try_wait_lock_timeout='0ms';
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set large_query_threshold='1s';
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set trace_log_slow_query_watermark='500ms';
Query OK, 0 rows affected (0.01 sec)

MySQL [oceanbase]> alter system set syslog_io_bandwidth_limit='30m';
Query OK, 0 rows affected (0.01 sec)

MySQL [oceanbase]> alter system set enable_async_syslog=true;
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set merger_warm_up_duration_time='0';
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set merger_switch_leader_duration_time='0';
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set large_query_worker_percentage=10;
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set builtin_db_data_verify_cycle = 0;
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set enable_merge_by_turn = False;
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set minor_merge_concurrency=30;
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set memory_limit_percentage = 85;
Query OK, 0 rows affected (0.01 sec)

MySQL [oceanbase]> alter system set memstore_limit_percentage = 80;
Query OK, 0 rows affected (0.01 sec)

MySQL [oceanbase]> alter system set freeze_trigger_percentage = 30;
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set enable_syslog_recycle='True';
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set max_syslog_file_count=100;
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set minor_freeze_times=500;
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set minor_compact_trigger=5;
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set max_kept_major_version_number=1;
Query OK, 0 rows affected (0.01 sec)

MySQL [oceanbase]> alter system set sys_bkgd_io_high_percentage = 90;
Query OK, 0 rows affected (0.01 sec)

MySQL [oceanbase]> alter system set sys_bkgd_io_low_percentage = 70;
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set merge_thread_count  = 45;
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set merge_stat_sampling_ratio = 1;
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set writing_throttling_trigger_percentage=75 tenant=tpcc;
Query OK, 0 rows affected (0.01 sec)

MySQL [oceanbase]> alter system set writing_throttling_maximum_duration='15m';
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> set global ob_plan_cache_percentage=20;
Query OK, 0 rows affected (0.01 sec)

MySQL [oceanbase]> alter system set enable_perf_event='false';
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set use_large_pages='true';
Query OK, 0 rows affected (0.01 sec)

MySQL [oceanbase]> alter system set micro_block_merge_verify_level=0;
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set builtin_db_data_verify_cycle=20;
Query OK, 0 rows affected (0.02 sec)

MySQL [oceanbase]> alter system set net_thread_count=4;
Query OK, 0 rows affected (0.02 sec)
OceanBase 数据库导数据前业务租户调优

请在具体用户下执行。在测试用户下执行 obclient -h$host_ip -P$host_port -u$user@$tenant -p$password -A 命令。

数据库下租户设置，防止事务超时
set global ob_query_timeout=36000000000;
set global ob_trx_timeout=36000000000;
set global max_allowed_packet=67108864;
set global ob_sql_work_area_percentage=100;
/*
parallel_max_servers推荐设置为测试租户分配的resource unit cpu数的10倍
如测试租户使用的unit配置为：create resource unit $unit_name max_cpu 26
那么该值设置为260
parallel_server_target推荐设置为parallel_max_servers * 机器数*0.8
那么该值为260*3*0.8=624
*/
set global parallel_max_servers=260;
set global parallel_servers_target=624;

执行调优参数

[admin@localhost ~]$ obclient -h10.144.2.106 -ubenchmarksql@tpcc  -P2883 -p****** -c -A oceanbase
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]> set global ob_query_timeout=36000000000;
Query OK, 0 rows affected (0.012 sec)

MySQL [oceanbase]> set global ob_trx_timeout=36000000000;
Query OK, 0 rows affected (0.102 sec)

MySQL [oceanbase]> set global max_allowed_packet=67108864;
Query OK, 0 rows affected (0.001 sec)

MySQL [oceanbase]> set global ob_sql_work_area_percentage=100;
Query OK, 0 rows affected (0.002 sec)
MySQL [oceanbase]> set global parallel_max_servers=260;
Query OK, 0 rows affected, 1 warning (0.012 sec)

MySQL [oceanbase]> set global parallel_servers_target=624;
Query OK, 0 rows affected (0.012 sec)
调优参数设置完毕请重启集群
obd cluster restart $cluster_name
导数执行

  1. 运行以下命令，初始化环境：

    ./runDatabaseDestroy.sh prop.ob

  2. 运行以下命令，创建表并导入数据：

     ./runDatabaseBuild.sh prop.ob
导数后调优
合并

  1. 执行合并（需要使用sys租户登录）

    Major 合并将当前大版本的 SSTable 和 MemTable 与前一个大版本的全量静态数据进行合并，使存储层统计信息更准确，生成的执行计划更稳定。

    MySQL [(none)]> use oceanbase
Database changed
MySQL [oceanbase]> alter system major freeze;
Query OK, 0 rows affected

  2. 查看合并是否完成

    MySQL [oceanbase]> select name,value from oceanbase.__all_zone where name='frozen_version' or name='last_merged_version';
+---------------------+-------+
| name                | value |
+---------------------+-------+
| frozen_version      |     2 |
| last_merged_version |     2 |
| last_merged_version |     2 |
| last_merged_version |     2 |
| last_merged_version |     2 |
+---------------------+-------+

    frozen_version 和 last_merged_version 的值相等即表示合并完成。

OceanBase 数据库压力测试阶段sys租户调优

请在sys租户下执行。

在系统租户下执行 obclient -h$host_ip -P$host_port -uroot@sys -A 命令。

##如果导入阶段开启了限速需要关闭
alter system set writing_throttling_trigger_percentage=100 tenant=xxx;
alter system set writing_throttling_maximum_duration='1h';
alter system set memstore_limit_percentage = 80; 
alter system set freeze_trigger_percentage = 30; 
alter system set large_query_threshold = '200s';
alter system set trx_try_wait_lock_timeout = '0ms';
alter system set cpu_quota_concurrency = 4;
alter system set minor_warm_up_duration_time = 0;
alter system set minor_freeze_times=500;
alter system set minor_compact_trigger=3;
alter system set sys_bkgd_io_high_percentage = 90;
alter system set sys_bkgd_io_low_percentage = 70;
alter system set minor_merge_concurrency =20;
alter system set builtin_db_data_verify_cycle = 0;
alter system set trace_log_slow_query_watermark = '10s';
alter system set gts_refresh_interval='500us'; 
alter system set server_permanent_offline_time='36000s';
alter system set weak_read_version_refresh_interval=0;
alter system set  _ob_get_gts_ahead_interval = '5ms';
##为频繁空查的宏块建立bloomfilter并缓存，减少磁盘IO和CPU消耗，提升写入性能
alter system set bf_cache_priority = 10;
alter system set user_block_cache_priority=5;
alter system set merge_stat_sampling_ratio = 0;
##close sql audit
alter system set enable_sql_audit=false;
##调整日志级别及保存个数
alter system set syslog_level='PERF';
alter system set max_syslog_file_count=100;
alter system set enable_syslog_recycle='True';
alter system set ob_enable_batched_multi_statement=true tenant=all;
alter system set _cache_wash_interval = '1m';
alter system set plan_cache_evict_interval = '30s';
alter system set enable_one_phase_commit=false;
alter system set enable_monotonic_weak_read = false;
OceanBase 数据库测试阶段业务租户调优

在进行测试TPCC的租户下执行。

在测试用户下执行 obclient -h$host_ip -P$host_port -u$user@$tenant -p$password -A 命令。

alter system set _clog_aggregation_buffer_amount=8;
alter system set _flush_clog_aggregation_buffer_timeout='1ms';

[admin@localhost ~]$ mysql -h10.144.2.111 -uroot@tpcc -P2881 -p -c -A oceanbase
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3222798341
Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]> alter system set _clog_aggregation_buffer_amount=8;
Query OK, 0 rows affected (0.00 sec)

MySQL [oceanbase]> alter system set _flush_clog_aggregation_buffer_timeout='1ms';
Query OK, 0 rows affected (0.00 sec)
TPC-C测试操作执行

执行以下命令，执行压力测试：

./runBenchmark.sh prop.ob

测试结果

测试结果统计

  1. 2000仓，200并发

    Term-00, Running Average tpmTOTAL: 742679.46    Current tpmTOTAL: 49075944    Memory Usage: 964MB / 2834MB           
22:50:33,344 [Thread-36] INFO   jTPCC : Term-00, 
22:50:33,344 [Thread-36] INFO   jTPCC : Term-00, 
22:50:33,344 [Thread-36] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 334225.02
22:50:33,344 [Thread-36] INFO   jTPCC : Term-00, Measured tpmTOTAL = 742589.64
22:50:33,345 [Thread-36] INFO   jTPCC : Term-00, Session Start     = 2022-01-14 22:40:33
22:50:33,345 [Thread-36] INFO   jTPCC : Term-00, Session End       = 2022-01-14 22:50:33
22:50:33,345 [Thread-36] INFO   jTPCC : Term-00, Transaction Count = 7426997

  2. 2000仓，400并发

    Term-00, Running Average tpmTOTAL: 894273.20    Current tpmTOTAL: 59132196    Memory Usage: 2937MB / 3748MB          
00:46:04,613 [Thread-368] INFO   jTPCC : Term-00, 
00:46:04,614 [Thread-368] INFO   jTPCC : Term-00, 
00:46:04,614 [Thread-368] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 402109.83
00:46:04,614 [Thread-368] INFO   jTPCC : Term-00, Measured tpmTOTAL = 894131.49
00:46:04,614 [Thread-368] INFO   jTPCC : Term-00, Session Start     = 2022-01-15 00:36:04
00:46:04,614 [Thread-368] INFO   jTPCC : Term-00, Session End       = 2022-01-15 00:46:04
00:46:04,614 [Thread-368] INFO   jTPCC : Term-00, Transaction Count = 8943132

  3. 2000仓，600并发

    Term-00, Running Average tpmTOTAL: 1036197.84    Current tpmTOTAL: 68482356    Memory Usage: 2370MB / 2662MB          
01:33:52,474 [Thread-449] INFO   jTPCC : Term-00, 
01:33:52,474 [Thread-449] INFO   jTPCC : Term-00, 
01:33:52,474 [Thread-449] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 466181.02
01:33:52,474 [Thread-449] INFO   jTPCC : Term-00, Measured tpmTOTAL = 1035911.66
01:33:52,475 [Thread-449] INFO   jTPCC : Term-00, Session Start     = 2022-01-15 01:23:52
01:33:52,475 [Thread-449] INFO   jTPCC : Term-00, Session End       = 2022-01-15 01:33:52
01:33:52,475 [Thread-449] INFO   jTPCC : Term-00, Transaction Count = 10362586

  4. 2000仓，800并发

    02:01:10,202 [Thread-514] INFO   jTPCC : Term-00, 
02:01:10,202 [Thread-514] INFO   jTPCC : Term-00, 
02:01:10,202 [Thread-514] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 482945.87
02:01:10,202 [Thread-514] INFO   jTPCC : Term-00, Measured tpmTOTAL = 1073274.03
02:01:10,202 [Thread-514] INFO   jTPCC : Term-00, Session Start     = 2022-01-15 01:51:09
02:01:10,202 [Thread-514] INFO   jTPCC : Term-00, Session End       = 2022-01-15 02:01:10
02:01:10,202 [Thread-514] INFO   jTPCC : Term-00, Transaction Count = 10739018

测试结果分析

本次测试，无预期结果，只是对开源OceanBase 3.1.2做一个基准的测试。

注意事项

  • 事务超时。报错信息如下：

    Worker 198: ERROR: Transaction is timeout
Worker 192: ERROR: Transaction is timeout

    需增大超时时间，测试租户下执行set global ob_query_timeout=36000000000;set global ob_trx_timeout=36000000000。

  • 修改Obproxy参数，开启二次路由，提高性能

    alter proxyconfig set enable_ob_protocol_v2=True;
alter proxyconfig set enable_reroute=True;  
alter proxyconfig set enable_index_route=True;

  • 关闭SQL审计

    ALTER SYSTEM SET enable_sql_audit = false;

  • 修改关闭性能收集

    alter system set  enable_perf_event=false;

BUG提交

  1. 对OceanBase的CPU参数疑问
    系统参数server_cpu_quota_max 用于设置系统可以使用的最大 CPU 配额。
属性描述
参数类型双精度浮点数
默认值5
取值范围[0, 16]
是否重启 OBServer 生效是

​ 全程部署运维使用OBD操作，开始压测时发现性能并不高，资源使用较少。这个问题原因我找了一个礼拜，最后通过寻找找企业版的解决方案部门人员，现场帮我找到了原因：发现测试租户最大只能用9C，但是我物理机是64C的。OBD自动创建，并且是生产模式的auto_depoly模式，竟然只给分配9C。给sys租户竟然是5C,总共16C可用。这个16C默认参数设计有些不解，不合理啊，我个人觉得是bug，生产部署这样肯定是有问题的，希望在后续版本有改进。
2. sys租户修改系统参数，OBD重启OceanBase集群后，参数不生效
我通过sys租户修改了server_cpu_quota_max参数，然后使用OBD重启生效。在重启后，发现又变回去了16C。企业版解决方案部的人也无解，又找了开源社区的人，告诉我说在OBD的1.2.0版本默认没有–wop参数，导致我修改的参数重启不生效，还是使用了OBD的参数模板。最终再次修改，OBD启动命令上加上–wop才可以。对于使用者的我来说，这是一个bug。

【Alpha阶段测试报告】命劫开发
mjkf_66666666的博客
05-09 330
文章目录测试中的小bug：新发现的问题&战略调整：场景测试场景测试1：场景测试2：场景测试3：测试矩阵：Alpha版本出口条件 测试中的小bug： 在正式测试之前我们在内部发行了version1.2版本，在内部版本发行，我们在发行前解决了如下问题： Stomp后端配置更改问题 Spring.Session功能放弃替代问题 团队组件右侧按钮展示band消失问题 发送文字文本框被右侧弹窗干扰问题 用户头像随机分配展示问题 团队头像生成脚本修复 Socket握手慢的问题 即时通讯历史消息接口传输方法修改
实践练习二（必选）：手动部署 OceanBase 3.1.2集群（离线安装）
weixin_54238817的博客
02-27 2489
手动部署 OceanBase 3.1.2集群
查看数据库版本
weixin_45755465的博客
12-26 294
select VERSION()
Oceanbase 使用OBD 一键性能测试（Sysbench，TPCC，TPCH）
最新发布
dnuiking的博客
08-29 824
首先需要了解TPCC 模拟工具，这一工具在之前的博客有提到。实操环境： oceanbase 单节点 all-in-one部署环境，也就是obd demo这里我们都用obd一键测试，比较简单，如果obd不满足需求可以选择手动测试的方式。
XX系统XX版本 测试报告
niangoagao的博客
06-02 1324
XX平台XX版本 系统测试报告 XXX （撰写人） 20XX年XX月XX日 （日期） 第1章 引言 1.1项目概述 1.1.1项目背景 1、项目的提出原因 ； 2、项目环境背景 ； 3、项目运作的可行性 ； 4、项目优势分析（资源、技术、人才、管理等方面）； 5、项目的独特与创新分析； 项目背景是站在客观的角度观察行业、政策、竞争者、客户、技术等方面的变化和情况。也可以从（重要性、价值、条件、问题等角度入手） 如果实在不会写，可以考虑复制项目章程中的项目背景 1.1.2项目范围 1.范围说明书： 这是项目
项目名称+版本号_测试报告（大版本）--测试报告模版
08-18
用于编写功能测试报告的模版
版本性能测试报告格式(一)
iteye_7163的博客
05-03 102
产品名称Product name 密级Confidentiality level ta 公开 产品版本Product version Total 13 pages 共13页 V100R002C00B018 :P ta V100R002C00B018版本 性能测试报告 (仅供内部使用) 拟制: XXX 日期： xxx 审核: 日期： 审核: 日期： ...
实践练习五（可选）：对 OceanBase 做性能测试
weixin_54238817的博客
02-27 2727
对一个OB集群（带 OBProxy）进行Benchamrk测试
猿创征文 | 小白入门 OceanBase 开发
MissingPastDay的博客
10-14 688
不得不说，OceanBase 真的很棒，Navicat 对 OceanBase 的连接也进行了支持，之前使用华为的 openGauss，无论是使用华为自家的连接工具，还是像是 Navicat 这样的第三方工具都无法连接，简直让人崩溃😥，而使用 Navicat 连接 OceanBase 一下就连上了。根据墨天轮发布的国产数据库流行度排行榜可以看到，国内数据库的流行度近期都在上涨，尤其是蚂蚁集团自主研发的 OceanBase，有着冲上榜一的趋势，从中可以看到国产数据库的崛起，也可以看到国内相关技术的不断进步。
OceanBase 开源负责人纪君祥聊 社区版 2022 Roadmap
seeyousoonhhh的博客
03-29 7099
昨日， OceanBase 开源社区为大家展开了《深入浅出 OceanBase 》系列第一期直播分享：在本次直播中，OceanBase 开源研发总监纪君祥带来了OceanBase 社区版 2022 Roadmap 精彩演讲，吸引了数百名观众线上观看，群内互动交流热火朝天。 错过了本次直播？没关系！ 看过还觉得意犹未尽？ 小编这就为你总结一下全文！ 为什么我们要发起《深入浅出 OceanBase 》系列直播？ 之前在跟社区一些朋友交流的过程中发现，不少朋友都希望 OceanBa.
基于CentOS系统安装OceanBase数据库
m0_60028455的博客
04-08 2515
镜像下载、域名解析、时间同步请点击 阿里云开源镜像站 一、OceanBase介绍 OceanBase是由蚂蚁集团完全自主研发的金融级分布式关系数据库，始创于2010年。OceanBase具有数据强一致、高可用、高性能、在线扩展、高度兼容SQL标准和主流关系数据库、低成本等特点。 OceanBase 社区版是一款开源分布式 HTAP（Hybrid Transactional/Analytical Processing）数据库管理系统，具有原生分布式架构，支持金融级高可用、透明水平扩展、分布式事务、多租户和语法
版本性能测试报告格式(二)
iteye_7163的博客
05-03 104
ta V100R002C00B018 性能测试报告 1 概述 V100R002C00B018性能专项测试，依据《ta 性能基线(V1.0)》作为性能测试的标准开展，机型上选择了HP DL380G5最高配置进行测试。 2 测试时间、地点及人员 版本名称 测试时间 测试人员 测试地点 起始时间 结束时间 ta V100R002C00B018 xxx xxx XXX xx...
Final版本测试报告和版本发布说明
LWCT
08-02 650
Final版本测试报告 Bug分类及数量 Alpha版本测试报告 BUG版本类型 A(可修复) B(不能重现) C(产品设计) D(无能力修复) E(延迟修复) BUG数量 11 0 0 0 1 A.可修复Bug: 未选择便签时点击"设置提醒"报错. 未选择便签时点击"删除"报错. 显示便签的标题出现奇怪换行. ...
Beta版本发布报告
qq_40662086的博客
07-27 900
测试报告   版本测试报告 BUG版本类型 A（可修复） B（不可重现） C（产品设计） D（无能力修复） E（延迟修复） BUG数量 2 0 0 0 ...
TPCH10G 测试：OceanBase TiDB Oracle SPL
Raqsoft
06-16 1044
前几天听说 OceanBase 打榜了 TPCH 的第一名，之后又看到这样一个测试报告《OceanBase 开源版与 TiDB 对比测试报告》。 先点评一下这个测试报告： 1. 坦白地说，这个测试还不太专业，并不能比出分布式大数据计算的关键能力。TPCH10G 的数据量相对于这些机器内存实在太小了（单机内存都超过数据量 18 倍以上）。这种情况下，再加上预热，数据已经全部缓存到内存了，列存基本不起作用，也就谈不上对比列存引擎的能力了。而且，数据量太小，会被作为小表复制到集群中的每个节点，分布式计算中重要
软件测试报告模板
test20190505的博客
08-11 1483
01 为什么编写测试报告 对于软件测试人员 一方面，测试报告是测试人员成果的输出，体现了测试人员的工作与成绩。另一方面，在输出测试报告时，测试人员同时也是在自行进行测试情况的分析总结，会项目组后续的项目提供建议，更好的提升质量。比如通过分析缺陷，可以为修复和预防bug提供建议；通过分析过程，可以评估执行是否和计划相符，为以后制定计划提供参考；通过对测试结果的分析，可以得到对软件质量的评价，为后续的改进提供经验。 对于其他人员，如产品、开发人员、项目管理、领导层、用户 项目的相关人员比包括但不限于产.
mysql导入数据时产生：ERROR 1366 (HY000): Incorrect string value解决办法
小白的成长之路
11-06 2115
执行以下命令： SET names utf8mb4; 完事，问题解决！ 验证修改结果： SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+--------------------+ | Variab...
第四章 oceanbase学习之查看oceanbase执行计划
RookieDBA的博客
04-27 1769
oceanbase执行计划
OceanBase详解
热门推荐
qq_22162093的博客
07-27 3万+
OceanBase是阿里开发的分布式关系型(SQL)数据库，其目标是支持数百TB的数据量以及数十万TPS、数百万QPS的访问量，无论是数据量还是访问量，即使采用非常昂贵的小型机甚至是大型机，单台关系数据库系统都无法承受。 案例：OceanBase分布式关系数据库渡过了一个成功的双十一：支持了支付宝核心的交易、支付、会员和账务等，并且创造了新的纪录：交易创建17.5万笔/秒、交易支付12万笔/秒、全天累计支付10.5亿笔 1、特性 1.1、高扩展性 传统关系型数据库，比如Oracle或者MySQL功能已经很完
Python库vsc-base 3.1.2版本发布及下载指南
vsc-base-3.1.2.tar.gz文件名称中的vsc-base代表该软件库的名称，而3.1.2则是该库的版本号，表明当前下载的是该库的3.1.2版本。压缩包中通常包含了该Python库的代码、文档以及其他资源文件，这些文件可能以源代码的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值