一、实验环境概述：
本文主要通过实验来测试oceanbase社区版(oceanbase-ce-3.1.3)的3节点集群与mysql5.7的3节点集群的读写性能。
由于实验环境有限，因此本文所涉及的操作是在同一台可用内存约48G，cpu为24核的服务器上完成。
先完成OB的性能测试后，在同一台机器上布署mysql innodb集群，完成性能测试。最后对比测试结果。
[root@master ~]# free -m
total used free shared buff/cache available
Mem: 48089 1237 46298 10 554 45903
Swap: 0 0 0
二、数据库环境布署
2.1 使用OBD快速布署OB集群：
注：关于服务器参数配置，参见官方文档或我之前发表的文档：使用OBD自动化布署三节点集群
OBD 配置文件: obconfig.yaml
oceanbase-ce:
servers:
- name: server1
ip: 192.168.18.28
- name: server2
ip: 192.168.18.28
- name: server3
ip: 192.168.18.28
global:
devname: br1
cluster_id: 1
memory_limit: 10G
system_memory: 2G
stack_size: 512K
cpu_count: 16
cache_wash_threshold: 1G
__min_full_resource_pool_memory: 268435456
workers_per_cpu_quota: 10
schema_history_expire_time: 1d
net_thread_count: 16
major_freeze_duty_time: 00:01
minor_freeze_times: 20
enable_separate_sys_clog: 0
enable_merge_by_turn: FALSE
enable_syslog_wf: false
enable_syslog_recycle: true
max_syslog_file_count: 4
appname: obcluster
server1:
mysql_port: 2890
rpc_port: 2900
home_path: /home/admin/oceanbase/observer1
data_dir: /data/observer1
zone: zone1
server2:
mysql_port: 2891
rpc_port: 2901
home_path: /home/admin/oceanbase/observer2
data_dir: /data/observer2
zone: zone2
server3:
mysql_port: 2892
rpc_port: 2902
home_path: /home/admin/oceanbase/observer3
data_dir: /data/observer3
zone: zone3
obproxy:
depends:
- oceanbase-ce
servers:
- 192.168.18.28
global:
listen_port: 2883
prometheus_listen_port: 2884
home_path: /home/admin/obproxy
rs_list: 192.168.18.28:2890;192.168.18.28:2891;192.168.18.28:2892
enable_cluster_checkout: false
cluster_name: obcluster
skip_proxy_sys_private_check: true
根据配置文件，创建必要的目录：
su - root
mkdir -p /data/{observer1,observer2,observer3}
chown -R admin:admin /data
su - admin
mkdir -p ~/oceanbase
开始布署oceanbase软件：
obd cluster deploy obcluster -c obconfig.yaml
Update OceanBase-community-stable-el7 ok
Update OceanBase-development-kit-el7 ok
Download oceanbase-ce-3.1.3-10000292022032916.el7.x86_64.rpm (47.11 M): 100% [#########################################################] Time: 0:03:09 260.45 kB/s
Package oceanbase-ce-3.1.3 is available.
Download obproxy-3.2.0-1.el7.x86_64.rpm (7.80 M): 100% [###############################################################################] Time: 0:00:29 280.23 kB/s
Package obproxy-3.2.0 is available.
install oceanbase-ce-3.1.3 for local ok
install obproxy-3.2.0 for local ok
+-------------------------------------------------------------------------------------------+
| Packages |
+--------------+---------+-----------------------+------------------------------------------+
| Repository | Version | Release | Md5 |
+--------------+---------+-----------------------+------------------------------------------+
| oceanbase-ce | 3.1.3 | 10000292022032916.el7 | eab08e5d473bd4884fdf2ac4d7dff6a329b68abe |
| obproxy | 3.2.0 | 1.el7 | 8d5c6978f988935dc3da1dbec208914668dcf3b2 |
+--------------+---------+-----------------------+------------------------------------------+
Repository integrity check ok
Parameter check ok
Open ssh connection ok
Remote oceanbase-ce-3.1.3-eab08e5d473bd4884fdf2ac4d7dff6a329b68abe repository install ok
Remote oceanbase-ce-3.1.3-eab08e5d473bd4884fdf2ac4d7dff6a329b68abe repository lib check !!
[WARN] server1(192.168.18.28) oceanbase-ce-3.1.3-eab08e5d473bd4884fdf2ac4d7dff6a329b68abe require: libmariadb.so.3
[WARN] server2(192.168.18.28) oceanbase-ce-3.1.3-eab08e5d473bd4884fdf2ac4d7dff6a329b68abe require: libmariadb.so.3
[WARN] server3(192.168.18.28) oceanbase-ce-3.1.3-eab08e5d473bd4884fdf2ac4d7dff6a329b68abe require: libmariadb.so.3
Remote obproxy-3.2.0-8d5c6978f988935dc3da1dbec208914668dcf3b2 repository install ok
Remote obproxy-3.2.0-8d5c6978f988935dc3da1dbec208914668dcf3b2 repository lib check ok
Try to get lib-repository
Download oceanbase-ce-libs-3.1.3-10000292022032916.el7.x86_64.rpm (155.22 K): 100% [###################################################] Time: 0:00:00 262.23 kB/s
Package oceanbase-ce-libs-3.1.3 is available.
install oceanbase-ce-libs-3.1.3 for local ok
Use oceanbase-ce-libs-3.1.3-c68c3aca8a1329a360fe5d65e1c3d4fa0f93f2d5 for oceanbase-ce-3.1.3-eab08e5d473bd4884fdf2ac4d7dff6a329b68abe
Remote oceanbase-ce-libs-3.1.3-c68c3aca8a1329a360fe5d65e1c3d4fa0f93f2d5 repository install ok
Remote oceanbase-ce-3.1.3-eab08e5d473bd4884fdf2ac4d7dff6a329b68abe repository lib check ok
Cluster status check ok
Initializes observer work home ok
Initializes obproxy work home ok
obcluster deployed
检查软件安装情况：
[admin@master ~]$ obd cluster list
+------------------------------------------------------------------+
| Cluster List |
+-----------+------------------------------------+-----------------+
| Name | Configuration Path | Status (Cached) |
+-----------+------------------------------------+-----------------+
| obcluster | /home/admin/.obd/cluster/obcluster | deployed |
+-----------+------------------------------------+-----------------+
启动和初始化集群：
[admin@master ~]$ obd cluster start obcluster
Get local repositories and plugins ok
Open ssh connection ok
Load cluster param plugin ok
Check before start observer ok
[WARN] (192.168.18.28) clog and data use the same disk (/)
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 |
+---------------+---------+------+-------+----------+
| 192.168.18.28 | 3.1.3 | 2890 | zone1 | inactive |
| 192.168.18.28 | 3.1.3 | 2891 | zone2 | active |
| 192.168.18.28 | 3.1.3 | 2892 | zone3 | active |
+---------------+---------+------+-------+----------+
Start obproxy ok
obproxy program health check ok
Connect to obproxy ok
Initialize cluster
+-------------------------------------------------+
| obproxy |
+---------------+------+-----------------+--------+
| ip | port | prometheus_port | status |
+---------------+------+-----------------+--------+
| 192.168.18.28 | 2883 | 2884 | active |
+---------------+------+-----------------+--------+
obcluster running
发现输出的日志中zone1的observer状态为inactive，再次通过命令检查下：
[admin@master log]$ obd cluster display obcluster
Get local repositories and plugins ok
Open ssh connection ok
Cluster status check ok
Connect to observer ok
Wait for observer init ok
+-------------------------------------------------+
| observer |
+---------------+---------+------+-------+--------+
| ip | version | port | zone | status |
+---------------+---------+------+-------+--------+
| 192.168.18.28 | 3.1.3 | 2890 | zone1 | active |
| 192.168.18.28 | 3.1.3 | 2891 | zone2 | active |
| 192.168.18.28 | 3.1.3 | 2892 | zone3 | active |
+---------------+---------+------+-------+--------+
Connect to obproxy ok
+-------------------------------------------------+
| obproxy |
+---------------+------+-----------------+--------+
| ip | port | prometheus_port | status |
+---------------+------+-----------------+--------+
| 192.168.18.28 | 2883 | 2884 | active |
+---------------+------+-----------------+--------+
OK，没有问题！
连接测试：
[admin@master obproxy]$ obclient -h 192.168.18.28 -P2883 -uroot@sys
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39)
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 [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| SYS |
| LBACSYS |
| ORAAUDITOR |
| test |
+--------------------+
7 rows in set (0.012 sec)
[admin@master obproxy]$ obclient -h 192.168.18.28 -P2883 -uroot@proxysys
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.25
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 [(none)]> show databases;
+--------------------------+-------------------+
| Variable_name | Value |
+--------------------------+-------------------+
| tx_isolation | READ-COMMITTED |
| system_time_zone | +08:00 |
| time_zone | +08:00 |
| character_set_server | utf8mb4 |
| character_set_client | utf8mb4 |
| interactive_timeout | 28800 |
| query_cache_size | 1048576 |
| character_set_results | utf8mb4 |
| max_allowed_packet | 4194304 |
| sql_mode | STRICT_ALL_TABLES |
| net_buffer_length | 16384 |
| wait_timeout | 28800 |
| lower_case_table_names | 2 |
| query_cache_type | OFF |
| init_connect | |
| transaction_isolation | READ |
| character_set_connection | utf8mb4 |
| net_write_timeout | 60 |
+--------------------------+-------------------+
18 rows in set (0.000 sec)
创建租户：
[admin@master obproxy]$ obclient -h 192.168.18.28 -P2883 -uroot@sys -A -c oceanbase
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39)
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]> 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 | 192.168.18.28:2900 | 14 | 2.5 | 11.5 | 8.000000000000 | 2.000000000000 | 6.000000000000 |
| zone2 | 192.168.18.28:2901 | 14 | 2.5 | 11.5 | 8.000000000000 | 2.000000000000 | 6.000000000000 |
| zone3 | 192.168.18.28:2902 | 14 | 2.5 | 11.5 | 8.000000000000 | 2.000000000000 | 6.000000000000 |
+-------+--------------------+-----------+--------------+----------+----------------+----------------+----------------+
3 rows in set (0.034 sec)
可看到当前服务器中新建租户可用的内存需要小于6GB，可用的cpu需要小于11.5。
CREATE resource unit u1 max_cpu=8, min_cpu=8, max_memory='5G', min_memory='5G', max_iops=10000, min_iops=10000, max_session_num=1000, max_disk_size='500G';
create resource pool p1 unit='u1' , unit_num=1, zone_list=('zone1' ,'zone2','zone3') ; <<--让主副本平均分布到三台主机上
create tenant if not exists obmysql resource_pool_list=('p1'), primary_zone='RANDOM',comment 'mysql tenant/instance', charset='utf8mb4' set ob_tcp_invited_nodes='%' ;
创建租户后，服务器的资源使用情况：
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 | 192.168.18.28:2900 | 14 | 10.5 | 3.5 | 8.000000000000 | 7.000000000000 | 1.000000000000 |
| zone2 | 192.168.18.28:2901 | 14 | 10.5 | 3.5 | 8.000000000000 | 7.000000000000 | 1.000000000000 |
| zone3 | 192.168.18.28:2902 | 14 | 10.5 | 3.5 | 8.000000000000 | 7.000000000000 | 1.000000000000 |
+-------+--------------------+-----------+--------------+----------+----------------+----------------+----------------+
3 rows in set (0.002 sec)
租户登录测试：
[admin@master obproxy]$ obclient -h 192.168.18.28 -P2883 -uroot@obmysql -A -c oceanbase
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39)
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]> show proxysession;
+--------------+------+-----------+---------+------+---------------------+-----------+-------------+-------------------+-------------------+------+------+
| proxy_sessid | Id | Cluster | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid |
+--------------+------+-----------+---------+------+---------------------+-----------+-------------+-------------------+-------------------+------+------+
| 5 | 10 | obcluster | obmysql | root | 192.168.18.28:64658 | oceanbase | 0 | 1 | MCS_ACTIVE_READER | 9209 | 9209 |
+--------------+------+-----------+---------+------+---------------------+-----------+-------------+-------------------+-------------------+------+------+
1 row in set (0.001 sec)
MySQL [oceanbase]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| test |
+--------------------+
4 rows in set (0.004 sec)
MySQL [oceanbase]> alter user root@'%' identified by 'root';
Query OK, 0 rows affected (2.754 sec)
OB的布署告一段落，等后面OB性能测试完成后，关闭OB集群以释放内存资源，因为我需要在同一台主机上配置一个3节点的mysql cluster。
2.2 布署Mysql InnoDB Cluster + 中间件dble
架构说明：在同一台主机上布署3个mysql5.7实例组成MGR集群，由于innodb cluster自带的mysql router只能做读写分离且读和写的端口必须分开配置，对应用端不太友好。因此我还使用了类似mycat的中间件dble来实现读写分离（一个应用只需要连接1个指定端口，即可对其发出的DML和DQL语句进行分离，查询时随机分发给所有节点，写入时只分发给主节点）。
布署过程：略
为了使内存池与OB集群相同，在布署时，每个节点的innodb_buffer_pool_size参数设置为：
default_storage_engine=InnoDB
innodb_buffer_pool_size=5120M
max_connections = 1000
布署mysql集群后的节点状态：
# mysql -uroot -p -P3317 -h127.0.0.1
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.33-log MySQL Community Server (GPL)
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 [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 559e40dc-bb01-11ec-aac3-b82a72d18227 | master | 3315 | ONLINE |
| group_replication_applier | 5e2db7c9-bb01-11ec-9164-b82a72d18227 | master | 3316 | ONLINE |
| group_replication_applier | 610a7670-bb01-11ec-adc4-b82a72d18227 | master | 3317 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
通过中间件dble连接到mysql集群：
# mysql -uroot -p -P8066 -h127.0.0.1
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.33-dble-3.21.02.1-b108e6810739c36af95804cd484d1ac6c8d8a6cb-20210528041415 MySQL Community Server (GPL)
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 [(none)]>
三、安装压测工具sysbench
RHEL/CentOS:
wget https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh
$ cat /etc/os-release |grep ID
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
chmod a+x script.rpm.sh
os=centos dist=7 ./script.rpm.sh
yum -y install sysbench
[root@master opt]# rpm -ql sysbench
/usr/bin/sysbench
/usr/share/doc/sysbench-1.0.20
/usr/share/doc/sysbench-1.0.20/COPYING
/usr/share/doc/sysbench-1.0.20/ChangeLog
/usr/share/doc/sysbench-1.0.20/README.md
/usr/share/licenses/sysbench-1.0.20
/usr/share/licenses/sysbench-1.0.20/COPYING
/usr/share/sysbench
/usr/share/sysbench/bulk_insert.lua
/usr/share/sysbench/oltp_common.lua
/usr/share/sysbench/oltp_delete.lua
/usr/share/sysbench/oltp_insert.lua
/usr/share/sysbench/oltp_point_select.lua
/usr/share/sysbench/oltp_read_only.lua
/usr/share/sysbench/oltp_read_write.lua
/usr/share/sysbench/oltp_update_index.lua
/usr/share/sysbench/oltp_update_non_index.lua
/usr/share/sysbench/oltp_write_only.lua
......省略一些输出
分别在Oceanbase和mysql集群中创建测试数据库和用户：
Oceanbase上执行：
obclient -uroot@obmysql -proot -P2883 -h192.168.18.28 -e "create database sbtest;"
obclient -uroot@obmysql -proot -P2883 -h192.168.18.28 -e "grant all on sbtest.* to sbtest@'%' identified by 'sbtest';"
mysql集群上执行：
mysql -uroot -p123456 -P8066 -h127.0.0.1 -e "create database sbtest;"
mysql -uroot -p123456 -P8066 -h127.0.0.1 -e "grant all on sbtest.* to sbtest@'%' identified by 'sbtest';"
四、压测
4.1 对Oceanbase的读写测试：
–准备数据
/usr/share/sysbench/oltp_read_write.lua --mysql-host=192.168.18.28 --mysql-port=2883 --mysql-user=root@obmysql --mysql-password=root --mysql-db=sbtest \
--tables=16 --table-size=100000 --report-interval=2 \
--time=60 --threads=16 --auto_inc=off --mysql-ignore-errors --db-ps-mode=disable prepare
发现在执行该脚本时，会报错：
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Initializing worker threads...
Creating table 'sbtest7'...
Creating table 'sbtest1'...
Creating table 'sbtest2'...
Creating table 'sbtest4'...
Creating table 'sbtest6'...
Creating table 'sbtest15'...
Creating table 'sbtest8'...
Creating table 'sbtest9'...
Creating table 'sbtest12'...
Creating table 'sbtest11'...
Creating table 'sbtest14'...
Creating table 'sbtest13'...
Creating table 'sbtest3'...
Creating table 'sbtest16'...
Creating table 'sbtest10'...
Creating table 'sbtest5'...
Inserting 100000 records into 'sbtest7'
Inserting 100000 records into 'sbtest1'
Inserting 100000 records into 'sbtest2'
Inserting 100000 records into 'sbtest4'
Inserting 100000 records into 'sbtest6'
Inserting 100000 records into 'sbtest8'
Inserting 100000 records into 'sbtest15'
FATAL: mysql_drv_query() returned error 4012 (Timeout) for query 'INSERT INTO sbtest8(id, k, c, pad) VALUES(1, 50216, '37995383632-96831892448-13834617111-03288165365-98215351924-61835230531-26938366841-20852559367-97450947472-50942960202'
...省略一部分... FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:230: db_bulk_insert_next() failed
调整OB参数：
MySQL [sbtest]> show variables like '%timeout%';
+---------------------+------------------+
| Variable_name | Value |
+---------------------+------------------+
| connect_timeout | 10 |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| ob_pl_block_timeout | 3216672000000000 |
| ob_query_timeout | 10000000 |
| ob_trx_idle_timeout | 120000000 |
| ob_trx_lock_timeout | -1 |
| ob_trx_timeout | 100000000 |
| wait_timeout | 28800 |
+---------------------+------------------+
11 rows in set (0.003 sec)
MySQL [sbtest]> set global ob_query_timeout=100000000;
Query OK, 0 rows affected (12.295 sec)
MySQL [sbtest]> set global ob_trx_idle_timeout=200000000;
Query OK, 0 rows affected (0.148 sec)
MySQL [sbtest]> set global ob_trx_timeout=180000000;
Query OK, 0 rows affected (0.128 sec)
先执行下面的清理数据脚本，然后再重新执行准备脚本：
/usr/share/sysbench/oltp_read_write.lua --mysql-host=192.168.18.28 --mysql-port=2883 --mysql-user=root@obmysql --mysql-password=root --mysql-db=sbtest \
--tables=15 --table-size=100000 --report-interval=2 \
--time=60 --threads=15 --auto_inc=off --mysql-ignore-errors --db-ps-mode=disable prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Initializing worker threads...
Creating table 'sbtest14'...
Creating table 'sbtest8'...
Creating table 'sbtest13'...
Creating table 'sbtest15'...
Creating table 'sbtest7'...
Creating table 'sbtest5'...
Creating table 'sbtest12'...
Creating table 'sbtest4'...
Creating table 'sbtest3'...
Creating table 'sbtest10'...
Creating table 'sbtest1'...
Creating table 'sbtest11'...
Creating table 'sbtest9'...
Creating table 'sbtest6'...
Creating table 'sbtest2'...
Inserting 100000 records into 'sbtest14'
Inserting 100000 records into 'sbtest13'
Inserting 100000 records into 'sbtest15'
Inserting 100000 records into 'sbtest5'
Creating a secondary index on 'sbtest14'...
Creating a secondary index on 'sbtest13'...
Inserting 100000 records into 'sbtest8'
Inserting 100000 records into 'sbtest7'
Inserting 100000 records into 'sbtest12'
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest8'...
Inserting 100000 records into 'sbtest4'
Creating a secondary index on 'sbtest12'...
Inserting 100000 records into 'sbtest10'
Inserting 100000 records into 'sbtest11'
Inserting 100000 records into 'sbtest1'
Creating a secondary index on 'sbtest4'...
Inserting 100000 records into 'sbtest3'
Creating a secondary index on 'sbtest11'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest15'...
Inserting 100000 records into 'sbtest9'
Inserting 100000 records into 'sbtest6'
Inserting 100000 records into 'sbtest2'
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest2'...
查看表的主副本分布情况：
MySQL [oceanbase]> select c.tenant_name,
-> b.table_name,
-> a.zone,
-> case when a.role=1 then 'leader' when a.role=2 then 'follower' else null end role,
-> concat(a.svr_ip,':',a.svr_port) server
-> from __all_virtual_meta_table a,__all_virtual_table b,gv$tenant c
-> where a.tenant_id=b.tenant_id and a.table_id=b.table_id and a.tenant_id=c.tenant_id and a.role=1
-> order by server;
+-------------+------------+-------+--------+--------------------+
| tenant_name | table_name | zone | role | server |
+-------------+------------+-------+--------+--------------------+
| obmysql | sbtest13 | zone1 | leader | 192.168.18.28:2900 |
| obmysql | sbtest8 | zone1 | leader | 192.168.18.28:2900 |
| obmysql | sbtest4 | zone1 | leader | 192.168.18.28:2900 |
| obmysql | sbtest1 | zone1 | leader | 192.168.18.28:2900 |
| obmysql | sbtest6 | zone1 | leader | 192.168.18.28:2900 |
| obmysql | sbtest15 | zone2 | leader | 192.168.18.28:2901 |
| obmysql | sbtest7 | zone2 | leader | 192.168.18.28:2901 |
| obmysql | sbtest10 | zone2 | leader | 192.168.18.28:2901 |
| obmysql | sbtest3 | zone2 | leader | 192.168.18.28:2901 |
| obmysql | sbtest2 | zone2 | leader | 192.168.18.28:2901 |
| obmysql | sbtest14 | zone3 | leader | 192.168.18.28:2902 |
| obmysql | sbtest5 | zone3 | leader | 192.168.18.28:2902 |
| obmysql | sbtest12 | zone3 | leader | 192.168.18.28:2902 |
| obmysql | sbtest11 | zone3 | leader | 192.168.18.28:2902 |
| obmysql | sbtest9 | zone3 | leader | 192.168.18.28:2902 |
+-------------+------------+-------+--------+--------------------+
15 rows in set (0.096 sec)
在查询结果中显示15张表的主副本是平均分布到每个observer节点上的，符合预期。
–执行压测
/usr/share/sysbench/oltp_read_write.lua --mysql-host=192.168.18.28 --mysql-port=2883 --mysql-user=root@obmysql --mysql-password=root --mysql-db=sbtest \
--tables=15 --table-size=100000 --report-interval=2 \
--time=60 --threads=15 --auto_inc=off --mysql-ignore-errors --db-ps-mode=disable run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 15
Report intermediate results every 2 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 2s ] thds: 15 tps: 103.37 qps: 2187.22 (r/w/o: 1543.54/429.45/214.23) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 15 tps: 111.02 qps: 2215.94 (r/w/o: 1551.81/442.09/222.04) lat (ms,95%): 244.38 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 15 tps: 111.00 qps: 2216.00 (r/w/o: 1554.00/440.00/222.00) lat (ms,95%): 248.83 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 15 tps: 110.00 qps: 2215.00 (r/w/o: 1545.00/450.00/220.00) lat (ms,95%): 257.95 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 15 tps: 120.50 qps: 2378.49 (r/w/o: 1663.50/474.00/241.00) lat (ms,95%): 267.41 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 15 tps: 80.50 qps: 1620.50 (r/w/o: 1141.50/318.00/161.00) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 15 tps: 118.00 qps: 2356.98 (r/w/o: 1642.99/478.00/236.00) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 15 tps: 109.99 qps: 2219.82 (r/w/o: 1557.87/441.96/219.98) lat (ms,95%): 272.27 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 15 tps: 73.51 qps: 1457.67 (r/w/o: 1020.62/290.03/147.02) lat (ms,95%): 694.45 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 15 tps: 119.50 qps: 2376.94 (r/w/o: 1661.96/475.99/238.99) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 15 tps: 112.00 qps: 2265.50 (r/w/o: 1585.50/456.00/224.00) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 15 tps: 130.00 qps: 2598.06 (r/w/o: 1818.04/520.01/260.01) lat (ms,95%): 231.53 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 15 tps: 112.00 qps: 2236.47 (r/w/o: 1568.48/444.00/224.00) lat (ms,95%): 303.33 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 15 tps: 99.50 qps: 1991.52 (r/w/o: 1394.51/398.00/199.00) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 15 tps: 109.99 qps: 2203.34 (r/w/o: 1539.39/443.97/219.98) lat (ms,95%): 272.27 err/s: 0.00 reconn/s: 0.00
[ 32s ] thds: 15 tps: 70.51 qps: 1412.62 (r/w/o: 989.58/282.02/141.01) lat (ms,95%): 397.39 err/s: 0.00 reconn/s: 0.00
[ 34s ] thds: 15 tps: 104.50 qps: 2082.52 (r/w/o: 1460.01/413.50/209.00) lat (ms,95%): 694.45 err/s: 0.00 reconn/s: 0.00
[ 36s ] thds: 15 tps: 120.49 qps: 2414.81 (r/w/o: 1688.36/485.46/240.98) lat (ms,95%): 257.95 err/s: 0.00 reconn/s: 0.00
[ 38s ] thds: 15 tps: 106.01 qps: 2125.15 (r/w/o: 1484.11/429.03/212.02) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 15 tps: 116.99 qps: 2315.86 (r/w/o: 1623.90/457.97/233.99) lat (ms,95%): 257.95 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 15 tps: 111.00 qps: 2227.58 (r/w/o: 1563.56/442.02/222.01) lat (ms,95%): 257.95 err/s: 0.00 reconn/s: 0.00
[ 44s ] thds: 15 tps: 114.00 qps: 2282.92 (r/w/o: 1592.94/461.98/227.99) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
[ 46s ] thds: 15 tps: 127.50 qps: 2555.03 (r/w/o: 1790.02/510.01/255.00) lat (ms,95%): 231.53 err/s: 0.00 reconn/s: 0.00
[ 48s ] thds: 15 tps: 24.50 qps: 500.02 (r/w/o: 347.02/104.00/49.00) lat (ms,95%): 1561.52 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 15 tps: 68.00 qps: 1328.97 (r/w/o: 936.98/255.99/136.00) lat (ms,95%): 2198.52 err/s: 0.00 reconn/s: 0.00
[ 52s ] thds: 15 tps: 110.50 qps: 2229.60 (r/w/o: 1558.57/450.02/221.01) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
[ 54s ] thds: 15 tps: 121.00 qps: 2406.43 (r/w/o: 1684.45/479.99/241.99) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 15 tps: 122.00 qps: 2463.00 (r/w/o: 1721.00/498.00/244.00) lat (ms,95%): 248.83 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 15 tps: 105.50 qps: 2108.01 (r/w/o: 1477.01/420.00/211.00) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 15 tps: 111.50 qps: 2207.93 (r/w/o: 1544.95/439.99/222.99) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 88550
write: 25300
other: 12650
total: 126500
transactions: 6325 (105.08 per sec.)
queries: 126500 (2101.55 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.1916s
total number of events: 6325
Latency (ms):
min: 39.83
avg: 142.47
max: 2359.15
95th percentile: 292.60
sum: 901120.41
Threads fairness:
events (avg/stddev): 421.6667/11.15
execution time (avg/stddev): 60.0747/0.06
4.2 对mysql集群的读写测试：
/usr/share/sysbench/oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=8066 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest \
--tables=15 --table-size=100000 --report-interval=2 \
--time=60 --threads=15 --auto_inc=off --mysql-ignore-errors --db-ps-mode=disable prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Initializing worker threads...
Creating table 'sbtest12'...
Creating table 'sbtest9'...
Creating table 'sbtest3'...
Creating table 'sbtest7'...
Creating table 'sbtest10'...
Creating table 'sbtest14'...
Creating table 'sbtest13'...Creating table 'sbtest4'...
Creating table 'sbtest11'...
Creating table 'sbtest2'...
Creating table 'sbtest5'...
Creating table 'sbtest6'...
Creating table 'sbtest1'...
Creating table 'sbtest8'...
Creating table 'sbtest15'...
Inserting 100000 records into 'sbtest15'
Inserting 100000 records into 'sbtest6'
Inserting 100000 records into 'sbtest2'
Inserting 100000 records into 'sbtest1'
Inserting 100000 records into 'sbtest5'
Inserting 100000 records into 'sbtest10'
Inserting 100000 records into 'sbtest3'
Inserting 100000 records into 'sbtest9'
Inserting 100000 records into 'sbtest13'
Inserting 100000 records into 'sbtest8'
Inserting 100000 records into 'sbtest14'
Inserting 100000 records into 'sbtest7'
Inserting 100000 records into 'sbtest4'
Inserting 100000 records into 'sbtest11'
Inserting 100000 records into 'sbtest12'
Creating a secondary index on 'sbtest13'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest14'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest11'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest12'...
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest15'...
Creating a secondary index on 'sbtest4'...
–开始测试
/usr/share/sysbench/oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=8066 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest \
--tables=15 --table-size=100000 --report-interval=2 \
--time=60 --threads=15 --auto_inc=off --mysql-ignore-errors --db-ps-mode=disable run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 15
Report intermediate results every 2 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 2s ] thds: 15 tps: 0.00 qps: 112.29 (r/w/o: 104.81/0.00/7.49) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 15 tps: 0.00 qps: 8.00 (r/w/o: 0.00/8.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 15 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 15 tps: 2.00 qps: 42.04 (r/w/o: 26.02/12.01/4.00) lat (ms,95%): 8038.61 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 15 tps: 5.00 qps: 96.92 (r/w/o: 71.94/14.99/9.99) lat (ms,95%): 9118.47 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 15 tps: 12.01 qps: 234.23 (r/w/o: 161.66/48.55/24.02) lat (ms,95%): 10343.39 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 15 tps: 22.00 qps: 468.95 (r/w/o: 314.47/110.49/44.00) lat (ms,95%): 1739.68 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 15 tps: 57.96 qps: 1157.11 (r/w/o: 811.37/229.82/115.91) lat (ms,95%): 475.79 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 15 tps: 45.54 qps: 910.79 (r/w/o: 637.55/182.16/91.08) lat (ms,95%): 569.67 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 15 tps: 43.50 qps: 870.97 (r/w/o: 608.98/174.99/87.00) lat (ms,95%): 759.88 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 15 tps: 56.95 qps: 1137.40 (r/w/o: 797.23/226.28/113.89) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 15 tps: 53.03 qps: 1046.14 (r/w/o: 742.46/197.62/106.07) lat (ms,95%): 549.52 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 15 tps: 60.02 qps: 1208.47 (r/w/o: 840.33/248.10/120.05) lat (ms,95%): 569.67 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 15 tps: 31.97 qps: 648.48 (r/w/o: 447.64/136.89/63.95) lat (ms,95%): 773.68 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 15 tps: 53.54 qps: 1063.72 (r/w/o: 749.51/207.14/107.07) lat (ms,95%): 404.61 err/s: 0.00 reconn/s: 0.00
[ 32s ] thds: 15 tps: 50.96 qps: 1026.12 (r/w/o: 713.39/210.82/101.91) lat (ms,95%): 612.21 err/s: 0.00 reconn/s: 0.00
[ 34s ] thds: 15 tps: 56.00 qps: 1099.99 (r/w/o: 783.99/204.00/112.00) lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00
[ 36s ] thds: 15 tps: 36.50 qps: 747.55 (r/w/o: 511.03/163.51/73.00) lat (ms,95%): 746.32 err/s: 0.00 reconn/s: 0.00
[ 38s ] thds: 15 tps: 55.50 qps: 1106.43 (r/w/o: 775.45/219.99/110.99) lat (ms,95%): 511.33 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 15 tps: 47.54 qps: 931.75 (r/w/o: 658.53/178.14/95.08) lat (ms,95%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 15 tps: 15.99 qps: 342.88 (r/w/o: 232.42/78.47/31.99) lat (ms,95%): 1506.29 err/s: 0.00 reconn/s: 0.00
[ 44s ] thds: 15 tps: 38.48 qps: 748.18 (r/w/o: 538.77/132.44/76.97) lat (ms,95%): 831.46 err/s: 0.00 reconn/s: 0.00
[ 46s ] thds: 15 tps: 42.00 qps: 840.05 (r/w/o: 588.04/168.01/84.01) lat (ms,95%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 48s ] thds: 15 tps: 49.50 qps: 1011.92 (r/w/o: 692.95/219.98/98.99) lat (ms,95%): 759.88 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 15 tps: 37.53 qps: 724.16 (r/w/o: 520.97/128.12/75.07) lat (ms,95%): 926.33 err/s: 0.00 reconn/s: 0.00
[ 52s ] thds: 15 tps: 21.50 qps: 455.99 (r/w/o: 305.49/107.50/43.00) lat (ms,95%): 1479.41 err/s: 0.00 reconn/s: 0.00
[ 54s ] thds: 15 tps: 42.98 qps: 841.19 (r/w/o: 600.28/154.94/85.97) lat (ms,95%): 960.30 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 15 tps: 4.00 qps: 99.94 (r/w/o: 57.47/34.48/8.00) lat (ms,95%): 467.30 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 15 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 15 tps: 10.01 qps: 188.59 (r/w/o: 134.07/34.52/20.01) lat (ms,95%): 5813.24 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 26866
write: 7676
other: 3838
total: 38380
transactions: 1919 (31.90 per sec.)
queries: 38380 (638.03 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.1521s
total number of events: 1919
Latency (ms):
min: 45.50
avg: 469.87
max: 10414.80
95th percentile: 1109.09
sum: 901672.26
Threads fairness:
events (avg/stddev): 127.9333/3.82
execution time (avg/stddev): 60.1115/0.03
4.3 测试结果对比：
OB集群的TPS=105.08，QPS=2101.55
mysql集群的TPS=31.90，QPS=638.03
4.4 清除数据
/usr/share/sysbench/oltp_read_write.lua --mysql-host=192.168.18.28 --mysql-port=2883 --mysql-user=root@obmysql --mysql-password=root --mysql-db=sbtest \
--tables=15 --table-size=100000 --report-interval=2 \
--time=60 --threads=15 --mysql-ignore-errors --db-ps-mode=disable cleanup
/usr/share/sysbench/oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=8066 --mysql-user=cqiwen --mysql-password=123456 --mysql-db=sbtest \
--tables=15 --table-size=100000 --report-interval=2 \
--time=60 --threads=15 --mysql-ignore-errors --db-ps-mode=disable cleanup
五、实验有感：
通过本次实验，我得到以下体验：
1）OceanBase集群的安装布署非常方便，相比mysql集群的布署要更简单容易一些；
2）本实验中的服务器磁盘IO很差，因此得到的QPS和TPS并不是很高。IO的影响非常大；
3）通过该实验，我发现Oceanbase集群在同样配置的情况下，比mysql集群的性能要高，TPS和QPS大概是mysql集群的3倍。
更多相关测试，请转到：玩转oceanbase之SQL查询与DML性能测试！
最后修改时间：2022-04-14 15:27:02
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
