品牌網(wǎng)站建設(shè) 杭州啟動互聯(lián)全網(wǎng)營銷推廣
?OceanBase?從4.3.0 版本開始，引入了列式存儲的支持。用戶可以根據(jù)業(yè)務(wù)的具體需求，選擇創(chuàng)建列存表、行存表或是行列混存表。無論選擇哪種表類型，在不同的Zone內(nèi)，租戶使用的副本模式都是一致的。詳見官網(wǎng)文檔：?https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001429675
為了達成TP與AP資源在物理層面上的嚴(yán)格隔離，OceanBase 4.3.3.0版本引入了一種創(chuàng)新的部署模式：它允許在原有集群的基礎(chǔ)上，增設(shè)獨立的zone來專門存儲列存副本（簡稱C副本）。但在4.3.3.0和4.3.3.1這兩個版本中，列存副本功能被界定為實驗性質(zhì)，因此并不推薦在生產(chǎn)環(huán)境中應(yīng)用。
副本類型的說明詳見官網(wǎng)文檔：
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001431874
| 副本類型 | 選舉投票 | 日志投票 | sstable | clog | memtable | 副本類型轉(zhuǎn)換 |
|---|---|---|---|---|---|---|
| F | 參與 | 參與 | 有，major為行存sstable | 有 | 有 | 可以轉(zhuǎn)為R副本 |
| R | 不參與 | 不參與 | 有，major為行存sstable | 有 | 有 | 可以轉(zhuǎn)為F副本 |
| C | 不參與 | 不參與 | 有，major為列存sstable | 有 | 有 | 不能轉(zhuǎn)為其他副本 |
創(chuàng)建列存副本前的環(huán)境
# 集群拓撲
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_cpu=3,max_cpu=3,memory_size='4g',log_disk_size='12g',max_iops=10000;create resource pool p1_1 unit='u1',zone_list=('zone1'),unit_num=1;
create resource pool p1_2 unit='u1',zone_list=('zone2'),unit_num=1;
create resource pool p1_3 unit='u1',zone_list=('zone3'),unit_num=1;create tenant test1 resource_pool_list=('p1_1','p1_2','p1_3'),
primary_zone='zone1,zone2,zone3',locality='F@zone1, F@zone2, F@zone3',
charset=utf8mb4,collate=utf8mb4_bin
set ob_tcp_invited_nodes='%';mysql -h127.0.0.1 -P12881 -uroot@test1 -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/log4
obd 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_type='user';
+-----------+-------------+-------------------+---------------------------------------------+
| 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、在增加副本之前，需要確認租戶在目標(biāo) zone 上是否有資源池，并記錄好當(dāng)前該租戶在各 zone 上的資源池名。
MySQL [oceanbase]> select * from dba_ob_resource_pools where tenant_id=(select tenant_id from dba_ob_tenants where tenant_name='test1');
+------------------+------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+
| 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 進行關(guān)聯(lián)
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 上創(chuàng)建 resource pool
create resource pool p1_4 unit='u1' ,unit_num=1,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 locality='f@zone1,f@zone2,f@zone3,c@zone4';7、確認 test1 租戶 locality 修改情況
select * from dba_ob_tenant_jobs
where job_type='alter_tenant_locality'
and tenant_id=(select tenant_id from dba_ob_tenants where tenant_name='test1')
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 locality='f@zone1,f@zone2,f@zone3,c@zone4'EXTRA_INFO: FROM: 'FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3', TO: 'FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3, COLUMNSTORE{1}@zone4'RS_SVR_IP: 11.xxx.xxx.191
RS_SVR_PORT: 12882
1 row in set (0.02 sec)新建租戶時創(chuàng)建列存副本
create resource unit u2 min_cpu=3,max_cpu=3,memory_size='4g',log_disk_size='12g',max_iops=10000;create resource pool p2_1 unit='u2',zone_list=('zone1'),unit_num=1;
create resource pool p2_2 unit='u2',zone_list=('zone2'),unit_num=1;
create resource pool p2_3 unit='u2',zone_list=('zone3'),unit_num=1;
create resource pool p2_4 unit='u2',zone_list=('zone4'),unit_num=1;create tenant test2
resource_pool_list=('p2_1','p2_2','p2_3','p2_4'),
primary_zone='zone1,zone2,zone3;zone4',
locality='F@zone1, F@zone2, F@zone3, C@zone4',
charset=utf8mb4,collate=utf8mb4_bin
set ob_tcp_invited_nodes='%';mysql -h127.0.0.1 -P12881 -uroot@test2 -p -A
alter user root identified by 'xxx';配置 obproxy
使用 root@proxysys 登錄對應(yīng)的 obproxy?
獨占的 obproxy
給列存副本單獨創(chuàng)建一個 obproxy 并登錄后進行如下配置
alter proxyconfig set obproxy_read_consistency='1';
alter proxyconfig set init_sql = 'set @@ob_route_policy="COLUMN_STORE_ONLY";';共享的 obproxy
沒有獨立的機器資源供列存副本使用，需要復(fù)用已有的 obproxy環(huán)境，此時可以設(shè)置 obproxy 多級配置，關(guān)于 obproxy 的多級配置可以詳見 官網(wǎng)文檔：
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_policy="COLUMN_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_policy="COLUMN_STORE_ONLY";', 'LEVEL_TENANT');
訪問列存副本測試
使用如上配置的 obproxy 登錄測試
# sys 租戶
MySQL [oceanbase]> select zone,tenant_id,name,value,default_value from gv$ob_parameters where tenant_id=1010 and name='default_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), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(t1.id)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([t1.id]), filter(nil), rowset=16 |
| force partition granule |
| 3 - output([t1.id]), filter(nil), rowset=16 |
| access([t1.id]), partitions(p[0-2]) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
+----------------------------------------------------------------------+
19 rows in set (0.01 sec)
- 表結(jié)構(gòu)沒有 with column group ，default_table_store_format 是默認的行存，執(zhí)行計劃展示上顯示 COLUMN TABLE FULL SCAN，說明使用到了列存的范圍掃描。
- 這里的測試表 t1 是在 test1 租戶下測試的，該租戶的拓撲 3F-1C ,有4個副本，但是在 show create table 和 show create tenant 結(jié)果中 replica_num都等于3，使用的是全功能副本的數(shù)量。
注意事項
1、observer 需要 4.3.3.0 及其之上的版本。
2、ocp 需要 4.3.3 及其之上的版本（當(dāng)前還沒有發(fā)布o(jì)cp 4.3.3）。
3、obd 需要 2.10.1-1 及其之上的版本。
4、obproxy 需要 4.3.2 及其之上的版本。
5、不建議部署 2 個及以上數(shù)目的列存副本。
6、全功能和只讀副本不支持轉(zhuǎn)為列存副本，列存副本也不支持轉(zhuǎn)為全功能和只讀副本。
7、物理恢復(fù)不支持恢復(fù)列存副本。
8、如果主庫未部署列存副本，備庫也不建議部署列存副本。
9、列存表是指表的分區(qū) Leader & Follower 的 Schema 均為列存格式，查詢可以是強讀；
列存副本是在保證表的分區(qū) Leader & Follower 的 Schema 為行存格式的前提下，只讀副本 Learner 為列存格式，并且 OLAP 的查詢只能是弱讀。
其他詳見官網(wǎng)文檔：
列存副本
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001428590