二级分销系统开发,黑帽seo是什么意思,网站建设初级工程师,个人怎么做ckmov解析网站OceanBase 从4.3.0 版本开始#xff0c;引入了列式存储的支持。用户可以根据业务的具体需求#xff0c;选择创建列存表、行存表或是行列混存表。无论选择哪种表类型#xff0c;在不同的Zone内#xff0c;租户使用的副本模式都是一致的。详见官网文档#xff1a; https://w… OceanBase 从4.3.0 版本开始引入了列式存储的支持。用户可以根据业务的具体需求选择创建列存表、行存表或是行列混存表。无论选择哪种表类型在不同的Zone内租户使用的副本模式都是一致的。详见官网文档 https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001429675 为了达成TP与AP资源在物理层面上的严格隔离OceanBase 4.3.3.0版本引入了一种创新的部署模式它允许在原有集群的基础上增设独立的zone来专门存储列存副本简称C副本。但在4.3.3.0和4.3.3.1这两个版本中列存副本功能被界定为实验性质因此并不推荐在生产环境中应用。
副本类型的说明详见官网文档
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001431874
副本类型选举投票日志投票sstableclogmemtable副本类型转换F参与参与有major为行存sstable有有可以转为R副本R不参与不参与有major为行存sstable有有可以转为F副本C不参与不参与有major为列存sstable有有不能转为其他副本
创建列存副本前的环境 # 集群拓扑
MySQL [oceanbase] select * from dba_ob_servers order by zone;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| SVR_IP | SVR_PORT | ID | ZONE | SQL_PORT | WITH_ROOTSERVER | STATUS | START_SERVICE_TIME | STOP_TIME | BLOCK_MIGRATE_IN_TIME | CREATE_TIME | MODIFY_TIME | BUILD_VERSION | LAST_OFFLINE_TIME |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 11.xxx.xxx.191 | 12882 | 1 | zone1 | 12881 | YES | ACTIVE | 2024-11-04 10:27:09.942001 | NULL | NULL | 2024-10-22 20:07:13.974171 | 2024-11-04 10:27:22.872264 | 4.3.3.1_101000012024102216-2df04a2a7a203b498f23e1904d4b7a000457ce43(Oct 22 2024 17:46:45) | NULL |
| 11.xxx.xxx.191 | 22882 | 2 | zone2 | 22881 | NO | ACTIVE | 2024-11-04 10:28:31.472704 | NULL | NULL | 2024-10-22 20:07:13.986746 | 2024-11-04 10:28:31.882765 | 4.3.3.1_101000012024102216-2df04a2a7a203b498f23e1904d4b7a000457ce43(Oct 22 2024 17:46:45) | NULL |
| 11.xxx.xxx.192 | 32882 | 3 | zone3 | 32881 | NO | ACTIVE | 2024-11-04 10:29:29.111769 | NULL | NULL | 2024-10-22 20:07:13.995302 | 2024-11-04 10:29:30.161822 | 4.3.3.1_101000012024102216-2df04a2a7a203b498f23e1904d4b7a000457ce43(Oct 22 2024 17:46:45) | NULL |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.01 sec)# 模拟已有的租户
create resource unit u1 min_cpu3,max_cpu3,memory_size4g,log_disk_size12g,max_iops10000;create resource pool p1_1 unitu1,zone_list(zone1),unit_num1;
create resource pool p1_2 unitu1,zone_list(zone2),unit_num1;
create resource pool p1_3 unitu1,zone_list(zone3),unit_num1;create tenant test1 resource_pool_list(p1_1,p1_2,p1_3),
primary_zonezone1,zone2,zone3,localityFzone1, Fzone2, Fzone3,
charsetutf8mb4,collateutf8mb4_bin
set ob_tcp_invited_nodes%;mysql -h127.0.0.1 -P12881 -uroottest1 -p -A
alter user root identified by xxx;扩展 zone4 供列存副本使用
参考 obd 集群扩容 https://www.oceanbase.com/docs/community-obd-cn-1000000001477803 oceanbase-ce:servers:- name: server4ip: 11.xxx.xxx.192server4:zone: zone4obshell_port: 45881mysql_port: 42881rpc_port: 42882local_ip: 11.xxx.xxx.192home_path: /home/heshun.lxd/observer4data_dir: /obdata/data/data4redo_dir: /obdata/log/log4obd cluster scale_out ob433 -c ob433_scale_out_zone4.yaml -v 扩容后的集群拓扑 MySQL [oceanbase] select * from dba_ob_servers order by zone;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| SVR_IP | SVR_PORT | ID | ZONE | SQL_PORT | WITH_ROOTSERVER | STATUS | START_SERVICE_TIME | STOP_TIME | BLOCK_MIGRATE_IN_TIME | CREATE_TIME | MODIFY_TIME | BUILD_VERSION | LAST_OFFLINE_TIME |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 11.xxx.xxx.191 | 12882 | 1 | zone1 | 12881 | YES | ACTIVE | 2024-11-04 10:27:09.942001 | NULL | NULL | 2024-10-22 20:07:13.974171 | 2024-11-04 10:27:22.872264 | 4.3.3.1_101000012024102216-2df04a2a7a203b498f23e1904d4b7a000457ce43(Oct 22 2024 17:46:45) | NULL |
| 11.xxx.xxx.191 | 22882 | 2 | zone2 | 22881 | NO | ACTIVE | 2024-11-04 10:28:31.472704 | NULL | NULL | 2024-10-22 20:07:13.986746 | 2024-11-04 10:28:31.882765 | 4.3.3.1_101000012024102216-2df04a2a7a203b498f23e1904d4b7a000457ce43(Oct 22 2024 17:46:45) | NULL |
| 11.xxx.xxx.192 | 32882 | 3 | zone3 | 32881 | NO | ACTIVE | 2024-11-04 10:29:29.111769 | NULL | NULL | 2024-10-22 20:07:13.995302 | 2024-11-04 10:29:30.161822 | 4.3.3.1_101000012024102216-2df04a2a7a203b498f23e1904d4b7a000457ce43(Oct 22 2024 17:46:45) | NULL |
| 11.xxx.xxx.192 | 42882 | 4 | zone4 | 42881 | NO | ACTIVE | 2024-11-04 11:48:24.538274 | NULL | NULL | 2024-11-04 11:09:44.030541 | 2024-11-04 11:48:26.306543 | 4.3.3.1_101000012024102216-2df04a2a7a203b498f23e1904d4b7a000457ce43(Oct 22 2024 17:46:45) | NULL |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 rows in set (0.00 sec) 给已有的租户扩列存副本
1、扩容前租户副本分布 MySQL [oceanbase] select tenant_id,tenant_name,primary_zone,locality from dba_ob_tenants where tenant_typeuser;
----------------------------------------------------------------------------------------
| tenant_id | tenant_name | primary_zone | locality |
----------------------------------------------------------------------------------------
| 1010 | test1 | zone1,zone2,zone3 | FULL{1}zone1, FULL{1}zone2, FULL{1}zone3 |
----------------------------------------------------------------------------------------
1 row in set (0.03 sec) 2、在增加副本之前需要确认租户在目标 zone 上是否有资源池并记录好当前该租户在各 zone 上的资源池名。 MySQL [oceanbase] select * from dba_ob_resource_pools where tenant_id(select tenant_id from dba_ob_tenants where tenant_nametest1);
------------------------------------------------------------------------------------------------------------------------------------------------
| RESOURCE_POOL_ID | NAME | TENANT_ID | CREATE_TIME | MODIFY_TIME | UNIT_COUNT | UNIT_CONFIG_ID | ZONE_LIST | REPLICA_TYPE |
------------------------------------------------------------------------------------------------------------------------------------------------
| 1008 | p1_1 | 1010 | 2024-11-04 11:01:36.377693 | 2024-11-04 11:02:00.918615 | 1 | 1004 | zone1 | FULL |
| 1009 | p1_2 | 1010 | 2024-11-04 11:01:36.395700 | 2024-11-04 11:02:01.221993 | 1 | 1004 | zone2 | FULL |
| 1010 | p1_3 | 1010 | 2024-11-04 11:01:36.410597 | 2024-11-04 11:02:01.224139 | 1 | 1004 | zone3 | FULL |
------------------------------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.02 sec) 3、确认各 resource pool 使用的 unit ,和 dba_ob_resource_pools 的 unit_config_id 进行关联 MySQL [oceanbase] select * from dba_ob_unit_configs;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| UNIT_CONFIG_ID | NAME | CREATE_TIME | MODIFY_TIME | MAX_CPU | MIN_CPU | MEMORY_SIZE | LOG_DISK_SIZE | DATA_DISK_SIZE | MAX_IOPS | MIN_IOPS | IOPS_WEIGHT | MAX_NET_BANDWIDTH | NET_BANDWIDTH_WEIGHT |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | sys_unit_config | 2024-10-22 20:07:12.701353 | 2024-10-22 20:07:12.701353 | 2 | 2 | 2147483648 | 3221225472 | NULL | 9223372036854775807 | 9223372036854775807 | 2 | 9223372036854775807 | 2 |
| 1004 | u1 | 2024-11-04 11:01:30.256177 | 2024-11-04 11:01:30.256177 | 3 | 3 | 4294967296 | 12884901888 | NULL | 10000 | 10000 | 0 | 9223372036854775807 | 3 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.01 sec)4、给 test1 租户在 zone4 上创建 resource pool create resource pool p1_4 unitu1 ,unit_num1,zone_list(zone4); 5、修改 test1 租户的 resource_pool_list alter tenant test1 resource_pool_list(p1_1,p1_2,p1_3,p1_4); 6、修改 test1 租户的 locality alter tenant test1 localityfzone1,fzone2,fzone3,czone4; 7、确认 test1 租户 locality 修改情况 select * from dba_ob_tenant_jobs
where job_typealter_tenant_locality
and tenant_id(select tenant_id from dba_ob_tenants where tenant_nametest1)
order by start_time desc limit 1 \G
*************************** 1. row ***************************JOB_ID: 2JOB_TYPE: ALTER_TENANT_LOCALITYJOB_STATUS: SUCCESS
RESULT_CODE: 0PROGRESS: 100START_TIME: 2024-11-04 12:01:55.851907
MODIFY_TIME: 2024-11-04 12:02:26.819124TENANT_ID: 1010SQL_TEXT: alter tenant test1 localityfzone1,fzone2,fzone3,czone4EXTRA_INFO: FROM: FULL{1}zone1, FULL{1}zone2, FULL{1}zone3, TO: FULL{1}zone1, FULL{1}zone2, FULL{1}zone3, COLUMNSTORE{1}zone4RS_SVR_IP: 11.xxx.xxx.191
RS_SVR_PORT: 12882
1 row in set (0.02 sec) 新建租户时创建列存副本 create resource unit u2 min_cpu3,max_cpu3,memory_size4g,log_disk_size12g,max_iops10000;create resource pool p2_1 unitu2,zone_list(zone1),unit_num1;
create resource pool p2_2 unitu2,zone_list(zone2),unit_num1;
create resource pool p2_3 unitu2,zone_list(zone3),unit_num1;
create resource pool p2_4 unitu2,zone_list(zone4),unit_num1;create tenant test2
resource_pool_list(p2_1,p2_2,p2_3,p2_4),
primary_zonezone1,zone2,zone3;zone4,
localityFzone1, Fzone2, Fzone3, Czone4,
charsetutf8mb4,collateutf8mb4_bin
set ob_tcp_invited_nodes%;mysql -h127.0.0.1 -P12881 -uroottest2 -p -A
alter user root identified by xxx; 配置 obproxy
使用 rootproxysys 登录对应的 obproxy
独占的 obproxy
给列存副本单独创建一个 obproxy 并登录后进行如下配置 alter proxyconfig set obproxy_read_consistency1;
alter proxyconfig set init_sql set ob_route_policyCOLUMN_STORE_ONLY;; 共享的 obproxy
没有独立的机器资源供列存副本使用需要复用已有的 obproxy环境此时可以设置 obproxy 多级配置关于 obproxy 的多级配置可以详见 官网文档
https://www.oceanbase.com/docs/common-odp-doc-cn-1000000001409917 replace into proxy_config(cluster_name, tenant_name, name, value, config_level) values (obcluster, test1, obproxy_read_consistency, 1, LEVEL_TENANT);
replace into proxy_config(cluster_name, tenant_name, name, value, config_level) values (obcluster, test1, init_sql, set ob_route_policyCOLUMN_STORE_ONLY;, LEVEL_TENANT);replace into proxy_config(cluster_name, tenant_name, name, value, config_level) values (obcluster, test2, obproxy_read_consistency, 1, LEVEL_TENANT);
replace into proxy_config(cluster_name, tenant_name, name, value, config_level) values (obcluster, test2, init_sql, set ob_route_policyCOLUMN_STORE_ONLY;, LEVEL_TENANT);访问列存副本测试
使用如上配置的 obproxy 登录测试 # sys 租户
MySQL [oceanbase] select zone,tenant_id,name,value,default_value from gv$ob_parameters where tenant_id1010 and namedefault_table_store_format;
--------------------------------------------------------------------
| zone | tenant_id | name | value | default_value |
--------------------------------------------------------------------
| zone1 | 1010 | default_table_store_format | row | row |
| zone4 | 1010 | default_table_store_format | row | row |
| zone3 | 1010 | default_table_store_format | row | row |
| zone2 | 1010 | default_table_store_format | row | row |
--------------------------------------------------------------------
4 rows in set (0.03 sec)# test1 租户
MySQL [test] show create table t1 \G
*************************** 1. row ***************************Table: t1
Create Table: CREATE TABLE t1 (id int(11) DEFAULT NULL
) DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_bin ROW_FORMAT DYNAMIC COMPRESSION zstd_1.3.8 REPLICA_NUM 3 BLOCK_SIZE 16384 USE_BLOOM_FILTER FALSE TABLET_SIZE 134217728 PCTFREE 0partition by hash(id)
(partition p0,
partition p1,
partition p2)
1 row in set (0.01 sec)MySQL [test] explain select * from t1;
----------------------------------------------------------------------
| Query Plan |
----------------------------------------------------------------------
| |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |7 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |7 | |
| |2 | └─PX PARTITION ITERATOR | |1 |7 | |
| |3 | └─COLUMN TABLE FULL SCAN|t1 |1 |7 | |
| |
| Outputs filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(t1.id)]), filter(nil), rowset16 |
| 1 - output([INTERNAL_FUNCTION(t1.id)]), filter(nil), rowset16 |
| dop1 |
| 2 - output([t1.id]), filter(nil), rowset16 |
| force partition granule |
| 3 - output([t1.id]), filter(nil), rowset16 |
| access([t1.id]), partitions(p[0-2]) |
| is_index_backfalse, is_global_indexfalse, |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
----------------------------------------------------------------------
19 rows in set (0.01 sec)表结构没有 with column group default_table_store_format 是默认的行存执行计划展示上显示 COLUMN TABLE FULL SCAN说明使用到了列存的范围扫描。这里的测试表 t1 是在 test1 租户下测试的该租户的拓扑 3F-1C ,有4个副本但是在 show create table 和 show create tenant 结果中 replica_num都等于3使用的是全功能副本的数量。 注意事项
1、observer 需要 4.3.3.0 及其之上的版本。
2、ocp 需要 4.3.3 及其之上的版本当前还没有发布ocp 4.3.3。
3、obd 需要 2.10.1-1 及其之上的版本。
4、obproxy 需要 4.3.2 及其之上的版本。
5、不建议部署 2 个及以上数目的列存副本。
6、全功能和只读副本不支持转为列存副本列存副本也不支持转为全功能和只读副本。
7、物理恢复不支持恢复列存副本。
8、如果主库未部署列存副本备库也不建议部署列存副本。
9、列存表是指表的分区 Leader Follower 的 Schema 均为列存格式查询可以是强读
列存副本是在保证表的分区 Leader Follower 的 Schema 为行存格式的前提下只读副本 Learner 为列存格式并且 OLAP 的查询只能是弱读。 其他详见官网文档
列存副本
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001428590
