背景

测试oceanbase对比mysql，tidb的性能表现，数据存储压缩，探索多点内部项目一个数据库场景落地oceanbase（mysql->oceanbase）

单机测试

准备

OBD方式部署单机

文件准备

wget https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/download-center/opensource/oceanbase-all-in-one/7/x86_64/oceanbase-all-in-one-4.1.0.0-100120230323143519.el7.x86_64.tar.gz?Expires=1681878350&OSSAccessKeyId=LTAI5tGVLeRRycCRGerZJMNC&Signature=4E8%2FW77U1MAqq1ttNvuljadkTq0%3D

mv oceanbase-all-in-one-4.1.0.0-100120230323143519.el7.x86_64.tar.gz?Expires=1681878350 oceanbase-all-in-one-4.1.0.0-100120230323143519.el7.x86_64.tar.gz

tar -zxf oceanbase-all-in-one-4.1.0.0-100120230323143519.el7.x86_64.tar.gz -C /usr/local
安装相关包

/usr/local/oceanbase-all-in-one/bin/install.sh
.......
#####################################################################
 Install Finished 
=====================================================================
Setup Environment:     source ~/.oceanbase-all-in-one/bin/env.sh 
Start Web Service:     obd web 
Quick Start:           obd demo 
More Details:          obd -h 
=====================================================================


修改配置文件
(包含组件oceanbase-ce、obproxy-ce、obagent、grafana、Prometheus)

cp /usr/local/oceanbase-all-in-one/obd/usr/obd/example/all-components.yaml ./

memory_limit 64G
observer总内存（租户内存+系统内存）
system_memory 30G 系统内存datafile_size 1500G 数据文件大小（启动就会预分配）
log_disk_size 日志文件大小（启动就会预分配）

vi all-components.yaml

oceanbase-ce:
  servers:
    - name: server1
      ip: 127.0.0.1
  global:
    devname: lo
    memory_limit: 64G # The maximum running memory for an observer
    system_memory: 30G
    datafile_size: 1500G # Size of the data file. 
    log_disk_size: 800G # The size of disk space used by the clog files.
.......
server1:
........
    zone: zone1
obproxy-ce:
  depends:
    - oceanbase-ce
  servers:
    - 127.0.0.1
  global:
......
obagent:
  depends:
    - oceanbase-ce
  servers:
    - name: server1
      ip: 127.0.0.1
  global:
    home_path: /data/obagent1
ocp-express:
  depends:
    - oceanbase-ce
    - obproxy-ce
    - obagent
  servers:
    - 127.0.0.1
  global:
.....

部署启动

obd cluster deploy obtest -c all-components.yaml
+--------------------------------------------------------------------------------------------+
|                                          Packages                                          |
+--------------+---------+------------------------+------------------------------------------+
| Repository   | Version | Release                | Md5                                      |
+--------------+---------+------------------------+------------------------------------------+
| oceanbase-ce | 4.1.0.0 | 100000192023032010.el7 | 8439ecf8db5e0649bd49671b41ea9e8c85756b63 |
| obproxy-ce   | 4.1.0.0 | 7.el7                  | 2a9d9bf67f179dcca2a8c9e7c77373d94e7e2abe |
| obagent      | 1.3.0   | 22.el7                 | d57fbb4962b2fbecb6282358c59295fdfba4d6ac |
| ocp-express  | 1.0.0   | 100000432023032015.el7 | 42c6fc921063f24f9e1072d75bfa7f21f42146e3 |
+--------------+---------+------------------------+------------------------------------------+
------

obd cluster start obtest
 ...
+---------------------------------------------+
|                   observer                  |
+-----------+---------+------+-------+--------+
| ip        | version | port | zone  | status |
+-----------+---------+------+-------+--------+
| 127.0.0.1 | 4.1.0.0 | 4000 | zone1 | ACTIVE |
+-----------+---------+------+-------+--------+
obclient -h127.0.0.1 -P4000 -uroot -Doceanbase -A

+---------------------------------------------+
|                   obproxy                   |
+-----------+------+-----------------+--------+
| ip        | port | prometheus_port | status |
+-----------+------+-----------------+--------+
| 127.0.0.1 | 2883 | 2884            | active |
+-----------+------+-----------------+--------+
obclient -h127.0.0.1 -P2883 -uroot -Doceanbase -A
+------------------------------------------------------------------+
|                             obagent                              |
+---------------+--------------------+--------------------+--------+
| ip            | mgragent_http_port | monagent_http_port | status |
+---------------+--------------------+--------------------+--------+
| 10.xxxx | 8089               | 8088               | active |
+---------------+--------------------+--------------------+--------+
+------------------------------------------------------------------+
|                           ocp-express                            |
+---------------------------+----------+------------------+--------+
| url                       | username | default_password | status |
+---------------------------+----------+------------------+--------+
| http://10.xxxx:8180 | admin    | oceanbase        | active |
+---------------------------+----------+------------------+--------+

创建MySQL租户

create resource unit ut1 max_cpu 32,memory_size '16G';

create resource pool p1 unit 'ut1',unit_num 1;

create tenant mysql resource_pool_list=('p1') set ob_tcp_invited_nodes='%';
重要配置

	oceanbase	tidb	mysql
社区版本	v4.1.0	v6.1.5	v5.7.16
内存配置	租户memory_size 16G	block_cache_size 16G	innodb_buffer_pool_size 16G
单机器配置	32C RAID10 SSD	32C RAID10 SSD	32C RAID10 SSD
刷盘配置	sync强制刷盘	sync-log=1	sync_binlog=1 innodb_flush_log_at_trx_commit=2
并发数	5,10,20,30,60,120	5,10,20,30,60,120	5,10,20,30,60,120
测试模式	read_write,read_only,write_only	read_write,read_only,write_only	read_write,read_only,write_only

mysql一层架构、oceanbase二层架构（obproxy+observer）、tidb三层架构（tidb+pd+tikv），每多一层网络层面的延迟消耗会增加
QPS，平均延迟oceanbase表现相对于mysql表现均可以，QPS、延迟时间相对是mysql的1/3（最低QPS也过万，最低平均延迟3ms）；数据压缩率（表仅2 数值、2字符字段）是mysql的3/5
sysbench表相对简单和实践生产场景表有一定差异，本次测试作为参考

QPS表现

OceanBase 4.1 体验&单机&集群-1 OceanBase 4.1 体验&单机&集群-2

OceanBase 4.1 体验&单机&集群-3

延迟表现

OceanBase 4.1 体验&单机&集群-4

OceanBase 4.1 体验&单机&集群-6

OceanBase 4.1 体验&单机&集群-7

存储压缩

OceanBase 4.1 体验&单机&集群-8

mysql数据test库物理空间占用69G	tidb 数据test库物理空间占用39G	oceanbase数据test库磁盘占用43G
		oceabase预分配，启动就是占用1.2T空间(实际具体库物理空间通过ob节点资源消耗查询)
mysql> select count() from sbtest10; +----------+ \| count() \| +----------+ \| 30000000 \| +----------+ 1 row in set (4.62 sec)	mysql> select count() from sbtest10; +----------+ count() \| +----------+ 30000000 \| +----------+ 1 row in set (0.00 sec)	mysql> select count() from sbtest1; +----------+ \| count() \| +----------+ \| 30000000 \| +----------+ 1 row in set (0.00 sec)

mysql数据test库物理空间占用69G

tidb 数据test库物理空间占用39G

oceanbase数据test库磁盘占用43G

oceabase预分配，启动就是占用1.2T空间(实际具体库物理空间通过ob节点资源消耗查询)

mysql> select count(*) from sbtest10;
+----------+
| count(*) |
+----------+
| 30000000 |
+----------+
1 row in set (4.62 sec)

mysql> select count(*) from sbtest10;
+----------+
count(*) |
+----------+
30000000 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 30000000 |
+----------+
1 row in set (0.00 sec)

binlog兼容

迁移服务 OMS 能通过OMS同步数据变更至下游kafka
能兼容mysql binlog协议同步下游kafka很友好（我们这类场景多mysql->kafka->hive）
暂未有类似MySQL binlog原始文件功能

OceanBase 4.1 体验&单机&集群-13

集群测试

集群方式目前暂对数据存储压缩率方式做了测试（生产监控场景mysql迁移至oceanbase）
后续还会对比监控场景mysql中各类型报表查询SQL在oceanbase表现
机器资源

	中控管理节点	数据节点	数据节点	数据节点
集群方式	10.xxx16C 64G /data 3.7T	10.xxx32C 50G /data3 15T	10.xxx32C 50G /data3 15T	10.xxx32C 50G /data3 15T

OCP管理组件嵌入到了OBD集群的部署流程中

机器相关初始化参考官方文档

在线部署准备

yum install -y yum-utils
yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo
yum install -y ob-deploy 
source /etc/profile.d/obd.sh

离线部署准备

https://www.oceanbase.com/softwarecenter 下载all-in-one包

tar -xzf oceanbase-all-in-one-*.tar.gz
cd oceanbase-all-in-one/bin/
./install.sh
source ~/.oceanbase-all-in-one/bin/env.sh

白屏部署

obd web -p 80

推荐白屏方式部署，可视化直观，有各种检查项

OceanBase 4.1 体验&单机&集群-15

预检查阶段会做全面check

OceanBase 4.1 体验&单机&集群-17

obd cluster display oceanbase41
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 |
+---------------+---------+------+-------+--------+
| 10.xxxx.208 | 4.1.0.0 | 5000 | zone2 | ACTIVE |
| 10.xxxx.209 | 4.1.0.0 | 5000 | zone1 | ACTIVE |
| 10.xxxx.210 | 4.1.0.0 | 5000 | zone3 | ACTIVE |
+---------------+---------+------+-------+--------+
obclient -h10.xxxx.208 -P5000 -uroot -p'xxxx' -Doceanbase -A

Connect to obproxy ok
+-------------------------------------------------+
|                     obproxy                     |
+---------------+------+-----------------+--------+
| ip            | port | prometheus_port | status |
+---------------+------+-----------------+--------+
| 10.xxxx.209 | 2883 | 2884            | active |
| 10.xxxx.208 | 2883 | 2884            | active |
| 10.xxxx.210 | 2883 | 2884            | active |
+---------------+------+-----------------+--------+
obclient -h10.xxxx.209 -P2883 -uroot -p'root1234' -Doceanbase -A
Connect to Obagent ok
+------------------------------------------------------------------+
|                             obagent                              |
+---------------+--------------------+--------------------+--------+
| ip            | mgragent_http_port | monagent_http_port | status |
+---------------+--------------------+--------------------+--------+
| 10.xxxx.209 | 8089               | 8088               | active |
| 10.xxxx.208 | 8089               | 8088               | active |
| 10.xxxx.210 | 8089               | 8088               | active |
+---------------+--------------------+--------------------+--------+
Connect to ocp-express ok
+------------------------------------------------------------------+
|                           ocp-express                            |
+---------------------------+----------+------------------+--------+
| url                       | username | default_password | status |
+---------------------------+----------+------------------+--------+
| http://10.xxxx.209:8180 | admin    | oceanbase        | active |
+---------------------------+----------+------------------+--------+
Trace ID: c3049c64-efa3-11ed-bf3a-525400b51421

创建ocp管理平台域名nginx 转发

办公环境仅能访问生产80端口，DNS域名->nginx 80->10.xxxx.209:8180（ocp管理平台）

http://ob-ocp-xxxx.db.dmall.com/

cat oceanbase-ocp.conf 
server { 
           listen       80; 
           server_name  ob-ocp-gz01.db.dmall.com; 

           location / { 
               proxy_pass http://10.xxxx.209:8180; 
               index  index.html index.htm index.jsp; 
           } 
       }

创建MySQL租户

命令行中创建租户存在timeout错误，登录时失败（和官方人员沟通后在ocp平台创建租户正常成功，推测命令行创建租户memory_size过大，ocp中创建限制为10G）

#创建资源单元
create resource unit ut1 max_cpu 30, memory_size '16G';

#创建资源池
create resource pool p1 unit 'ut1',unit_num 1,ZONE_LIST ('zone1', 'zone2' ,'zone3');

#创建租户
select now(); create tenant if not exists mysql CHARSET='utf8mb4', ZONE_LIST=('zone1','zone2','zone3'), PRIMARY_ZONE='zone1,zone2,zone3',resource_pool_list=('p1') set ob_tcp_invited_nodes='%';select now();
ERROR 4012 (HY000): Timeout

#mysql租户登录
obclient -h127.0.0.1 -P4000 -uroot@mysql -Doceanbase -A
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 4725 (HY000): tablet does not exist

创建分区表示例

OceanBase分布式分片基于分区表实现，大表必须创建分区
OceanBase 数据库社区版暂不支持 utf8mb4_unicode_ci 和 utf16_unicode_ci
主键(多列)和唯一索引(多列)必须包含分区键

否则创建分区表报错：

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

时间分区键必须是datetime，不能是timestamp

ERROR 1486 (HY000): Constant or random or timezone-dependent expressions in (sub)partitioning function are not allowed

CREATE TABLE `snapshot_innodb_status_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `stat_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  ......
   PRIMARY KEY (`id`,stat_time),
  KEY `idx_port` (`port`),
  KEY `idx_ip` (`ip`),
  KEY `idx_stat_time` (`stat_time`)
)  partition by range (year(stat_time)*100+month(stat_time)) (
PARTITION p0  VALUES LESS THAN(202201),
PARTITION p1  VALUES LESS THAN(202203),
PARTITION p2  VALUES LESS THAN(202205),
PARTITION p3  VALUES LESS THAN(202207),
PARTITION p4  VALUES LESS THAN(202209),
PARTITION p5  VALUES LESS THAN(202211),
PARTITION p6  VALUES LESS THAN(202301),
PARTITION p7  VALUES LESS THAN(202303),
PARTITION p8  VALUES LESS THAN(202305),
PARTITION p9  VALUES LESS THAN(202307),
PARTITION p10  VALUES LESS THAN(202309),
PARTITION p11  VALUES LESS THAN(202311),
PARTITION p12  VALUES LESS THAN MAXVALUE
);

CREATE TABLE `redis_app_minute_command_statistics` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `app_id` int(11) NOT NULL COMMENT '应用id',
  `collect_time` datetime NOT NULL,
  .........
  PRIMARY KEY (`id`,collect_time),
  UNIQUE KEY `app_id` (`app_id`,`collect_time`,`command_name`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_collect_time_app_id_command_name` (`collect_time`,`app_id`,`command_name`)
)  ENGINE=InnoDB  
partition by range (year(collect_time)*100+month(collect_time)) (
PARTITION p0  VALUES LESS THAN(202201),
PARTITION p1 VALUES LESS THAN(202203),
PARTITION p2 VALUES LESS THAN(202205),
PARTITION p3 VALUES LESS THAN(202207),
PARTITION p4 VALUES LESS THAN(202209),
PARTITION p5 VALUES LESS THAN(202211),
PARTITION p6 VALUES LESS THAN(202301),
PARTITION p7 VALUES LESS THAN(202303),
PARTITION p8 VALUES LESS THAN(202305),
PARTITION p9 VALUES LESS THAN(202307),
PARTITION p10 VALUES LESS THAN(202309),
PARTITION p11 VALUES LESS THAN(202311),
PARTITION p12 VALUES LESS THAN MAXVALUE
);

分区表使用建议

DRC同步mysql至oceanbase

DRC自研同步工具支持mysql->oceanbase（DRC还能支持mysql->mysql, mysql-tidb），官方OMS迁移工具安装管理者工具OAT
内部mysql监控快照库空间660G（单副本）迁移至oceanbase单副本空间110G，压缩率约为1/6

OceanBase 4.1 体验&单机&集群-18

mysql单副本：

OceanBase 4.1 体验&单机&集群-19

oceanbase单副本：

OceanBase 4.1 体验&单机&集群-20

mysql->oceanbase数据简单一致校验：

mysql采用show table status；oceanbase查询information_schema.tables （show table status中无rows信息，也和官方人员确认过）

实践中因有2张上百G大表在DRC工具全量期间目标ob时常有异常报错，2张上百G大表全量采用mydumper+myloader手动备份还原至ob，DRC工具增量同步

OceanBase 4.1 体验&单机&集群-21

#drc同步期间存在ob目标端出现错误
问题:目标端 执行sql出现错误!io.ReadFull(header) failed. err EOF: connection was bad,Table [redis_monitor_data_min -> redis_monitor_data_min]


####因drc同步大表时频繁出现以上错误，大表改为mydumper多线程逻辑备份还原 也偶尔出现异常

#myloader多线程还原出现异常错误
 CRITICAL **: 10:45:12.333: Error switching to database xxx whilst restoring table xxxx_data_min
** (myloader:118422): CRITICAL **: 10:46:42.195: Error restoring xxx.xxxx_status_log from file xxx.xxxx_status_log.00006.sql.gz: Lost connection to MySQL server during query
增大租户内存和减小转储比例后myloader多线程还原没有出现异常错误

老配置	新配置
freeze_trigger_percentage 70%	freeze_trigger_percentage 30%
租户memory_size 10G	租户memory_size 15G

关键配置

注意

datafile_size 不支持缩小（支持增大，需reload重启集群），配置后就会预分配（datafile_disk_percentage 默认95%）占用空间

ll -h /data/oceanbases1/sstable/block_file

-rw-r--r-- 1 root root 1.5T May 4 16:35 /data/oceanbases1/sstable/block_file

[oceanbase-ce] server1(127.0.0.1): DO NOT decrease datafile_size after startup

memory_limit

OBServer 的最大可使用内存数。由于最低内存配置为 64 GB，预留部分给系统使用，则建议 OBServer 独占 58 GB，即系统租户内存与程序内存共 58 GB

system_memory

系统预留给租户 ID 为 500 的租户的内存容量obclient> ALTER SYSTEM SET system_memory='xxG';

rootservice_memory_limit

Root Service 的最大内存容量限制obclient> ALTER SYSTEM SET rootservice_memory_limit='2G';

ob_query_timeout

SQL 最大执行时间，单位是微秒超时会报错ERROR 4012 (HY000): Timeout, query has reached the maximum query timeout: 10000000(us), maybe you can adjust the session variable ob_query_timeout or query_timeout hint, and try again.

常用命令

手动收集信息

CALL dbms_stats.gather_table_stats('test', 'sbtest1', granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE 128');

#使用oracle模式语法
ALTER SYSTEM SET ENABLE_SQL_EXTENSION = TRUE;
ANALYZE TABLE sbtest1 COMPUTE STATISTICS FOR ALL COLUMNS SIZE 128;
手动收集统计信息

租户资源的创建

创建资源配置

obclient> CREATE RESOURCE UNIT uc1 MAX_CPU 5, MIN_CPU 4, MEMORY_SIZE '36G', MAX_IOPS 128000, MIN_IOPS 128000, LOG_DISK_SIZE '2T' ;

必选：
MAX_CPU
MEMORY_SIZE

MIN_CPU 默认等于 MAX_CPU
MIN_IOPS 默认等于 MAX_IOPS

LOG_DISK_SIZE 默认等于 3 倍的内存大小，最小为 2G
MIN_IOPS 和 MAX_IOPS 的值根据 MIN_CPU 规格自动计算，1个 Core 对应 1 万 IOPS 值，即 MAX_IOPS = MIN_IOPS = MIN_CPU * 10000

创建资源池

obclient> CREATE RESOURCE POOL rp1 UNIT 'uc1', UNIT_NUM 2, ZONE_LIST ('zone1', 'zone2');

UNIT 'uc1' 表示为该资源池指定的资源配置为 uc1，该资源池下的每个资源单元使用 uc1 的规格进行配置。
ZONE_LIST ('zone1','zone2') 是为资源池指定的使用范围，表示该资源池要在 zone1 和 zone2 上创建资源单元。
UNIT_NUM 2 是为资源池指定资源单元的个数，表示在 ZONE_LIST 内的每个 Zone 上都创建 2 个资源单元。

创建租户

obclient> CREATE RESOURCE POOL pool1 UNIT 'uc1', UNIT_NUM 2, ZONE_LIST ('z1', 'z2');
obclient>CREATE RESOURCE POOL pool2 UNIT 'uc1', UNIT_NUM 1, ZONE_LIST ('z3');
obclient>CREATE TENANT tt resource_pool_list=('pool1','pool2');

tt租户下有2个资源池

删除租户

DRop tenant mysql force ;

Drop resource pool p1;

Drop RESOURCE UNIT ut1;

租户资源查看

租户资源配置
SELECT t1.name resource_pool_name,
       t2.`name` unit_config_name,
       t2.max_cpu,
       t2.min_cpu,
       t2.memory_size/1024/1024/1024 memory_size,
                                     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 | memory_size     | unit_id | zone  | observer       | tenant_id | tenant_name |
+--------------------+------------------+---------+---------+-----------------+---------+-------+----------------+-----------+-------------+
| sys_pool           | sys_unit_config  |       1 |       1 |  8.500000000000 |       1 | zone1 | 127.0.0.1:2882 |         1 | sys         |
| p1                 | ut1              |      32 |      32 | 16.000000000000 |    1001 | zone1 | 127.0.0.1:2882 |      1002 | mysql       |
+--------------------+------------------+---------+---------+-----------------+---------+-------+----------------+-----------+-------------+
         
         
资源单元配置
select
  name,
  max_cpu,
  round(memory_size / 1024 / 1024 / 1024, 2) 'memory_size_GB'
from
  __all_unit_config;
+-----------------+---------+----------------+
| name            | max_cpu | memory_size_GB |
+-----------------+---------+----------------+
| sys_unit_config |       1 |           8.50 |
| ut1             |      32 |          16.00 |
+-----------------+---------+----------------+

各observer剩余资源单元
select
  svr_ip,
  svr_port,
  zone,
  round((cpu_capacity_max - cpu_assigned_max), 2) 'cpu_free_num',
  cpu_capacity_max 'cpu_total_num',
  round((mem_capacity - mem_assigned) / 1024 / 1024 / 1024, 2) 'mem_free_GB',
  round(memory_limit / 1024 / 1024 / 1024, 2) 'mem_total_GB',
  round(DATA_DISK_IN_USE / 1024 / 1024 / 1024, 2) as data_used_GB,
  round(DATA_DISK_CAPACITY / 1024 / 1024 / 1024, 2) as data_total_GB,
  round(LOG_DISK_CAPACITY / 1024 / 1024 / 1024, 2) as log_total_GB,
  round(LOG_DISK_IN_USE / 1024 / 1024 / 1024, 2) as log_used_GB
from
  gv$ob_servers;
+-----------+----------+-------+--------------+---------------+-------------+--------------+--------------+---------------+--------------+-------------+
| svr_ip    | svr_port | zone  | cpu_free_num | cpu_total_num | mem_free_GB | mem_total_GB | data_used_GB | data_total_GB | log_total_GB | log_used_GB |
+-----------+----------+-------+--------------+---------------+-------------+--------------+--------------+---------------+--------------+-------------+
| 127.0.0.1 |     2882 | zone1 |         7.00 |            40 |        9.50 |        64.00 |       192.00 |        192.00 |       192.00 |       40.69 |
+-----------+----------+-------+--------------+---------------+-------------+--------------+--------------+---------------+--------------+-------------+

查看所有observer节点
select zone,svr_ip,svr_port,inner_port,with_rootserver,status,gmt_create from __all_server order by zone, svr_ip;

显示当前租户

 show Tenant ;
+---------------------+
| Current_tenant_name |
+---------------------+
| mysql               |
+---------------------+
1 row in set (0.002 sec)

修改资源池

obclient> CREATE RESOURCE UNIT uc1 MAX_CPU 5, MIN_CPU 4, MEMORY_SIZE '32G', MMAX_IOPS 128000, MIN_IOPS 128000, LOG_DISK_SIZE '2T';

obclient> CREATE RESOURCE POOL pool1 UNIT 'uc1', UNIT_NUM 2, ZONE_LIST ('z1', 'z2');

obclient> CREATE RESOURCE POOL pool2 UNIT 'uc1', UNIT_NUM 1, ZONE_LIST ('z3');

obclient> CREATE TENANT tt resource_pool_list=('pool1','pool2');

obclient> ALTER RESOURCE UNIT uc1 MAX_CPU 6,  MEMORY_SIZE '36G';

ALTER RESOURCE UNIT config_mysql_zone1_20C10G_vcq MEMORY_SIZE='12G';

支持的在线扩容资源单元
ALTER RESOURCE UNIT unit_name 
MEMORY_SIZE [=] 'size_value',
MAX_CPU [=] cpu_num, 
[MIN_CPU [=] cpu_num,]
[MAX_IOPS [=] iops_num,]
[MIN_IOPS [=] iops_num,] 
[LOG_DISK_SIZE [=] 'size_value'];
  
调大 OceanBase 集群磁盘最大空间百分比（默认预分配data_size的95%）
 ALTER system SET datafile_disk_percentage = 98;

调大 OceanBase 用户租户内存最大内存百分比（默认mem_size的50%） 
alter system set memstore_limit_percentage =70;
 
提高转储速度
 alter system set freeze_trigger_percentage=30;

管理资源池

限制

OceanBase 社区版暂不支持 utf8mb4_unicode_ci，utf8mb4_unicode_ci校对规则支持的类型更全面丰富

与mysql兼容性

OceanBase创建user支持identified by方式，密文方式identified as xxx 不支持，支持密文identified by password

CREATE USER 'dmall_idb2'@'10.%.%.%' identified by 'xxx';
CREATE USER 'dmall_idb2'@'10.%.%.%' identified by password '*xxxxx';

若超过磁盘限额配置写入会报错（除了机器磁盘空间报警外还需对observer data空间使用率做报警）

error, errno = 4184, state = '53100': Server out of disk space

处理方式为扩容资源单元或者edit-config修改datafile_size:后reload集群重启生效

若超过内存限额配置写入会报错（高TPS场景下oceanbase 很消耗内存）

SQL error, errno = 4013, state = 'HY001': No memory or reach tenant memory limit

处理方式为扩容资源单元、开启写入限速以及调整转储阈值（调高转储线程数，加快转储，让内存尽快释放）

https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001702785 租户内存写满

总结

oceanbase QPS、读写延迟、存储压缩方面均不错（生产mysql一监控场景库空间：oceanbase空间为6:1 单副本，660G:110G 单副本）
高TPS场景可以适当调整租户内存使用参数、转储参数
oceanbase不同租户资源隔离（iops,内存,cpu）更好适配多业务场景
obd白屏部署方式更加高效、直观，ocp管理平台功能全面
业务应用方使用oceanbase比mysql有点区别（大表创建分区表，用户名需带租户名等）

OceanBase 4.1 体验&单机&集群

背景

单机测试

准备

部署启动

创建MySQL租户

QPS表现

延迟表现

存储压缩

binlog兼容

集群测试

白屏部署

创建ocp管理平台域名nginx 转发

创建MySQL租户

创建分区表示例

DRC同步mysql至oceanbase

关键配置

常用命令

手动收集信息

租户资源的创建

删除租户

租户资源查看

修改资源池

限制

总结

相关文章

发布评论取消回复

大白菜程序猿

GreatSQL中 Insert 慢是什么情况？

Apache的Public-Key-Pins如何配置以提高SSL安全性

MySQL的"Row size too large"错误场景

OceanBase 4.1 体验&单机&集群

背景

单机测试

准备

部署启动

创建MySQL租户

QPS表现

延迟表现

存储压缩

binlog兼容

集群测试

白屏部署

创建ocp管理平台域名nginx 转发

创建MySQL租户

创建分区表示例

DRC同步mysql至oceanbase

关键配置

常用命令

手动收集信息

租户资源的创建

删除租户

租户资源查看

修改资源池

限制

总结

相关文章

发布评论 取消回复

发布评论取消回复