OceanBase主备库搭建.pdf

百分

158

9页

2次

2024-01-10

免费下载

OceanBase 主备库搭建（一张大表导致

磁盘打满）

前言 OceanBase 数据库的主备库搭建比较简单，通过 ocp 白屏可以快

速的搭建起来，本文主要介绍一个极端场景的处理过程。数据库版本： 3.

2.3.3

案例架构主备集群均为 2-2- 2 同规格资源集群。异构集群有细节不同（对

于备集群因为单台机器存储小，一个 zone 内需要比主库更多机器搭建的

情况需要在备集群搭建后手工快速修改 unit_ num ）。

主集群信息检查

2.1 主集群上单表和 tablegroup 的 pr im ary _zon e 设置取消

##查看表和 tablegr oup 名字和原始设置， ( 因为我们租户设置了 prim ar y _

zone，所以表的 prim ary_zone 顺序与租户不同的就是单独设置的 )。

select * from __all_tablegroup;

select database_name,table_name,tenant_name,primary_zone from gv$table where te

nant_id>1000 and primary_zone <>'zone3;zone2;zone1';

##修改表和 tablegroup 的 prim ary_zone 为默认：

alter tablegroup tg_xxx set primary_zone='default';

ALTER TABLE xxxn.ccccccc PRIMARY_ZONE=default;

2.2 主库存储使用，大表检查

MySQL [oceanbase]> SELECT svr_ip, svr_port, CONCAT(ROUND(total_size 1024 / 1024 / 10

24, 2), 'G') AS TOTAL, CONCAT(ROUND(free_size / 1024 / 1024 / 1024, 2), 'G') AS FRE

E, CONCAT(ROUND((total_size - free_size) / total_size * 100, 2),'%') AS USED FROM __

all_virtual_disk_stat order by svr_ip;

+---------------+----------+----------+----------+--------+

+---------------+----------+----------+----------+--------+

| 10.XX.XX.55 | 2882 | 3006.82G | 1540.53G | 48.77% |

| 10.XX.XX.77 | 2882 | 3006.82G | 1383.01G | 54.00% |

| 111.XX.XX.113 | 2882 | 3012.77G | 1545.12G | 48.71% |

| 111.XX.XX.114 | 2882 | 3012.77G | 1390.33G | 53.85% |

| 111.XX.XX.115 | 2882 | 3012.77G | 1545.89G | 48.69% |

| 111.XX.XX.116 | 2882 | 3006.82G | 1383.60G | 53.98% |

##主库可以看到存储使用大约 50% ，但是同步时候会有宏块膨胀，查看下

占用和有没有大表：

MySQL [oceanbase]> select svr_ip,sum(size)/1024/1024/1024 data_size from __all_virtu

al_table_mgr group by 1 order by data_size desc limit 20 ;

+---------------+-------------------+

| svr_ip | data_size |

+---------------+-------------------+

| 10.XX.XX.77 | 2455.093179729767 |

| 111.XX.XX.116 | 2443.278376303612 |

| 111.XX.XX.114 | 2441.437496948056 |

| 111.XX.XX.113 | 2013.635538168251 |

| 111.XX.XX.115 | 2011.677529040724 |

| 10.XX.XX.55 | 2000.085263901390 |

+---------------+-------------------+

6 rows in set (1.34 sec)

MySQL [oceanbase]> select svr_ip,table_id,sum(size)/1024/1024/1024 data_size from __

all_virtual_table_mgr group by 1,2 order by data_size desc limit 10;

+---------------+------------------+-------------------+

| svr_ip | table_id | data_size |

+---------------+------------------+-------------------+

| 111.XX.XX.114 | 1100611139652449 | 1508.943823604844 |

| 111.XX.XX.116 | 1100611139652449 | 1508.943823604844 |

| 10.XX.XX.77 | 1100611139652449 | 1508.943823604844 |

| 111.XX.XX.113 | 1100611139649270 | 206.068651821464 |

| 10.XX.XX.55 | 1100611139649270 | 206.068651821464 |

| 111.XX.XX.115 | 1100611139649270 | 206.020522594451 |

| 10.XX.XX.77 | 1100611139712594 | 163.333016555756 |

| 10.XX.XX.55 | 1100611139652887 | 161.007432742044 |

| 111.XX.XX.115 | 1100611139652887 | 161.007322357967 |

MySQL [oceanbase]> SELECT svr_ip, svr_port, CONCAT(ROUND(total_size 1024 / 1024 / 10

24, 2), 'G') AS TOTAL, CONCAT(ROUND(free_size / 1024 / 1024 / 1024, 2), 'G') AS FRE

E, CONCAT(ROUND((total_size - free_size) / total_size * 100, 2),'%') AS USED FROM __

all_virtual_disk_stat order by svr_ip;

+---------------+----------+----------+----------+--------+

+---------------+----------+----------+----------+--------+

| 10.XX.XX.55 | 2882 | 3006.82G | 1540.53G | 48.77% |

| 10.XX.XX.77 | 2882 | 3006.82G | 1383.01G | 54.00% |

| 111.XX.XX.113 | 2882 | 3012.77G | 1545.12G | 48.71% |

| 111.XX.XX.114 | 2882 | 3012.77G | 1390.33G | 53.85% |

| 111.XX.XX.115 | 2882 | 3012.77G | 1545.89G | 48.69% |

| 111.XX.XX.116 | 2882 | 3006.82G | 1383.60G | 53.98% |

##主库可以看到存储使用大约 50% ，但是同步时候会有宏块膨胀，查看下

占用和有没有大表：

MySQL [oceanbase]> select svr_ip,sum(size)/1024/1024/1024 data_size from __all_virtu

al_table_mgr group by 1 order by data_size desc limit 20 ;

+---------------+-------------------+

| svr_ip | data_size |

+---------------+-------------------+

| 10.XX.XX.77 | 2455.093179729767 |

| 111.XX.XX.116 | 2443.278376303612 |

| 111.XX.XX.114 | 2441.437496948056 |

| 111.XX.XX.113 | 2013.635538168251 |

| 111.XX.XX.115 | 2011.677529040724 |

| 10.XX.XX.55 | 2000.085263901390 |

+---------------+-------------------+

6 rows in set (1.34 sec)

MySQL [oceanbase]> select svr_ip,table_id,sum(size)/1024/1024/1024 data_size from __

all_virtual_table_mgr group by 1,2 order by data_size desc limit 10;

+---------------+------------------+-------------------+

| svr_ip | table_id | data_size |

+---------------+------------------+-------------------+

| 111.XX.XX.114 | 1100611139652449 | 1508.943823604844 |

| 111.XX.XX.116 | 1100611139652449 | 1508.943823604844 |

| 10.XX.XX.77 | 1100611139652449 | 1508.943823604844 |

| 111.XX.XX.113 | 1100611139649270 | 206.068651821464 |

| 10.XX.XX.55 | 1100611139649270 | 206.068651821464 |

| 111.XX.XX.115 | 1100611139649270 | 206.020522594451 |

| 10.XX.XX.77 | 1100611139712594 | 163.333016555756 |

| 10.XX.XX.55 | 1100611139652887 | 161.007432742044 |

| 111.XX.XX.115 | 1100611139652887 | 161.007322357967 |

of 9

免费下载

oceanbase主备库搭建

关注

评论