-
安装部署
本地NTP服务配置
-
如何手工配置 Chrony Server 和 Chrony Client
服务端配置(OCP服务器做服务端)
## 关闭ntpd服务防止干扰
systemctl stop ntpd
systemctl disable ntpd
mv /etc/ntp.conf /etc/ntp.conf.bak
## 安装chrony服务
yum install chrony -y
## 编辑chrony配置文件
mv /etc/chrony.conf /etc/chrony.conf.bak
egrep -v '^#|^$' /etc/chrony.conf.bak >/etc/chrony.conf
vi /etc/chrony.conf
## 修改后
server 10.186.56.43 minpoll 4 maxpoll 6 iburst
maxslewrate 500
local stratum 10
driftfile /var/lib/chrony/drift
makestep 1.0 3
rtcsync
logdir /var/log/chrony
allow 10.186.0.0/16
## 启动chronyd服务
systemctl restart chronyd
systemctl status chronyd
systemctl enable chronyd
## 查看同步状态(多执行几次,观测到^*为正常同步)
chronyc -n sources
210 Number of sources = 1
MS Name/IP address Stratum Poll Reach LastRx Last sample
===============================================================================
^* 10.186.56.43 10 4 377 32 +307ns[-6487ns] +/- 21us
客户端配置(其他服务器做客户端)
-
客户端的chrony配置相较于服务端,只是减少了2个参数配置
-
local stratum 10
将本地作为时钟源 -
allow 10.186.0.0/16
允许访问时钟源的网段
-
## 关闭ntpd服务防止干扰
systemctl stop ntpd
systemctl disable ntpd
mv /etc/ntp.conf /etc/ntp.conf.bak
## 安装chrony服务
yum install chrony -y
## 编辑chrony配置文件
mv /etc/chrony.conf /etc/chrony.conf.bak
egrep -v '^#|^$' /etc/chrony.conf.bak >/etc/chrony.conf
vi /etc/chrony.conf
server 10.186.56.43 minpoll 4 maxpoll 6 iburst
maxslewrate 500
driftfile /var/lib/chrony/drift
makestep 1.0 3
rtcsync
logdir /var/log/chrony
## 启动chronyd服务
systemctl restart chronyd
systemctl status chronyd
systemctl enable chronyd
## 查看同步状态(多执行几次,观测到^*为正常同步)
chronyc -n sources
210 Number of sources = 1
MS Name/IP address Stratum Poll Reach LastRx Last sample
===============================================================================
^* 10.186.56.43 11 4 17 2 -12us[ -53us] +/- 913us
OB集群安装
-
搭建2-2-2的OB集群,题目会给出配置参数,照着填就行了
-
OCP 上添加主机,注意按照要求来对主机命名。(每个主机都有自己的名字,机房以及REGION,不要弄错了)
-
搭建OBPROXY,每台OB都搭建,按照题目给的配置参数设置OBPROXY
OB集群参数配置
关闭UNIT 均衡
-
resource_soft_limit 参数用来控制是否开启UNIT均衡,但其参数的取值受到enable_rebalance参数影响
- 资源软负载开关,控制资源均衡水位,默认为 50%,即 CPU 内存使用超过 50% 就进行 unit 均衡,线上建议调整为 100,达到手工控制 unit 分布的效果
-
enable_rebalance
配置项作为负载均衡的总开关,用于控制资源单元的均衡和分区副本均衡开关。当enable_rebalance
为False
时,分区副本均衡关闭;为True
时,分区副本均衡开启。
enable_rebalance | resource_soft_limit | 含义 |
---|---|---|
开启 | 小于100 | UNIT均衡开启 分区副本均衡开启 |
开启 | 大于等于100 | UNIT均衡关闭 分区副本均衡开启 |
关闭 | 取值范围内任意值 | UNIT均衡关闭 分区副本均衡关闭 |
实验要求的配置
- 开启分区副本均衡 + 关闭UNIT均衡
alter system set enable_rebalance = true;
alter system set resource_soft_limit = 100;
不同租户的变量调整
- 分别给OB-MYSQL 和 OB-ORACLE 设置相关变量,MySQL和Oracle租户的的变量设置要求值不太一样,会提供多个参数,基于不同的模式去设置即可。以下以undo_retention参数举例,其他参数类似。
show variables like 'undo%';
set global undo_retention=3600;
-
全链路诊断
-
一个是提供的JAVA程序,调用后,总共有10个事务,大概跑了10秒钟。 问题是这么简单的语句为啥跑10分钟? 要求使用tcpdump 抓包,分析为啥会慢。是网络、应用还是数据库? 并且贴出证据截图
本地模拟
创建测试库表
-- 创建测试库表
create database demo;
CREATE TABLE demo.`t1` (
`id` int(11) DEFAULT NULL,
`c1` varchar(10) DEFAULT NULL,
`value` int(11) DEFAULT NULL
);
insert into demo.t1 select 1,'a',null;
insert into demo.t1 select 2,'b',null;
insert into demo.t1 select 3,'c',null;
insert into demo.t1 select 4,'d',null;
创建java测试代码
- MySQLTransactionDemo.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class MySQLTransactionDemo {
// MySQL数据库连接信息
private static final String DB_URL = "jdbc:mysql://10.186.65.12:2883/demo";
private static final String USER = "root@obce_m#obce_zhenxing";
private static final String PASS = "aaBB11__";
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1. 注册 JDBC 驱动程序
Class.forName("com.mysql.jdbc.Driver");
// 2. 打开一个连接
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// 3. 关闭自动提交
conn.setAutoCommit(false);
// 4. 定义 SQL 语句,假设表 t1 有一个 id 和 value 列
String sql = "UPDATE t1 SET value = value + 1 WHERE id = ?";
// 5. 创建 PreparedStatement
pstmt = conn.prepareStatement(sql);
for (int i = 1; i <= 10; i++) {
// 设置参数
pstmt.setInt(1, i);
// 执行 SQL 语句
pstmt.executeUpdate();
// 提交事务
conn.commit();
System.out.println("Transaction " + i + " committed successfully.");
// 休眠 1 秒
Thread.sleep(1000);
}
} catch (Exception e) {
// 处理异常,如果发生异常则回滚事务
try {
if (conn != null) {
conn.rollback();
System.out.println("Transaction rollback.");
}
} catch (SQLException se) {
se.printStackTrace();
}
e.printStackTrace();
} finally {
// 6. 清理环境
try {
if (pstmt != null) pstmt.close();
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
}
编译java包
-- 编译
javac MySQLTransactionDemo.java
-- 执行
export CLASSPATH=/usr/java/jdk1.8.0_162/lib/mysql-connector-java-5.1.47.jar:$CLASSPATH
java MySQLTransactionDemo
Transaction 1 committed successfully.
Transaction 2 committed successfully.
Transaction 3 committed successfully.
Transaction 4 committed successfully.
Transaction 5 committed successfully.
Transaction 6 committed successfully.
Transaction 7 committed successfully.
Transaction 8 committed successfully.
Transaction 9 committed successfully.
Transaction 10 committed successfully
-- 抓包(在执行前开启)
tcpdump -i eth0 -w /tmp/tcpdump.raw
-- 格式化输出包内容
tcpdump -X -r /tmp/tcpdump.raw > /tmp/tcpdump.txt
-
SQL优化
统计信息默认选择率
-
选择率越低越好,唯一性越高越好
-
关于表记录数的默认统计信息,如果表有记录,按实际记录做评估。
-
NDV(Number of Distinct Values) 是指某一列中不同值的数量。它反映了该列中的数据分布情况,通常用于数据库的优化和查询计划生成。
-
自动采集(每日合并)
-
__all_virtual_meta_table
-
__all_virtual_column_statistic
-
-
手工收集(集群级表/MySQL租户级)
-
__all_virtual_table_stat_v2 / __all_table_stat_v2
-
__all_virtual_column_stat_v2 / __all_column_stat_v2
-
__all_virtual_histogram_stat_v2 / __all_histogram_stat_v2
-
-
手工采集(Oracle租户级表)
-
all_tab_statistics
-
all_tab_col_statistics
-
all_tab_histograms
-
obclient [oceanbase]> select tenant_id,tenant_name,table_id,table_name,database_name from gv$table where table_name='chap41';
+-----------+-------------+------------------+------------+---------------+
| tenant_id | tenant_name | table_id | table_name | database_name |
+-----------+-------------+------------------+------------+---------------+
| 1002 | obce_o | 1101710651081562 | CHAP41 | SYS |
| 1002 | obce_o | 1101710651081564 | CHAP41 | TPCC |
+-----------+-------------+------------------+------------+---------------+
2 rows in set (0.044 sec)
obclient [oceanbase]> select * from __all_virtual_table_stat_v2 where table_id=1101710651081564;
Empty set (0.053 sec)
obclient [oceanbase]> select * from __all_virtual_meta_table where table_id=1101710651081564\G
*************************** 1. row ***************************
tenant_id: 1002
table_id: 1101710651081564
partition_id: 0
svr_ip: 10.186.65.12
svr_port: 2882
gmt_create: 2024-08-14 14:22:23.332331
gmt_modified: 2024-08-14 14:22:23.377178
sql_port: 2881
unit_id: 1002
partition_cnt: 0
zone: zone1
role: 1
member_list: 10.186.65.12:2882:1723616543317579
row_count: 0
data_size: 0
data_version: 8
data_checksum: 0
row_checksum: 0
column_checksum:
is_original_leader: 0
is_previous_leader: 1723616543375615
create_time: 0
rebuild: 0
replica_type: 0
required_size: 0
status: REPLICA_STATUS_NORMAL
is_restore: 0
partition_checksum: 0
quorum: 1
fail_list:
recovery_timestamp: 0
memstore_percent: 100
data_file_id: 0
1 row in set (0.044 sec)
-- all_tab_statistics
obclient [TPCC]> select owner,table_name,OBJECT_TYPE,NUM_ROWS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from all_tab_statistics where table_name='CHAP41';
+-------+------------+-------------+----------+-----------+-------------+---------------+
| OWNER | TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_SPACE | AVG_ROW_LEN | LAST_ANALYZED |
+-------+------------+-------------+----------+-----------+-------------+---------------+
| SYS | CHAP41 | TABLE | 10001 | NULL | 59 | 12-AUG-24 |
| TPCC | CHAP41 | TABLE | NULL | NULL | NULL | NULL |
+-------+------------+-------------+----------+-----------+-------------+---------------+
2 rows in set (0.038 sec)
-- all_tab_col_statistics
obclient [TPCC]> select * from all_tab_col_statistics where table_name='CHAP41' and owner='TPCC';
+-------+------------+-------------+--------------+-----------+------------+---------+-----------+-------------+---------------+-------------+--------------+------------+-------+-------------+-----------+-------+
| OWNER | TABLE_NAME | COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | NUM_BUCKETS | LAST_ANALYZED | SAMPLE_SIZE | GLOBAL_STATS | USER_STATS | NOTES | AVG_COL_LEN | HISTOGRAM | SCOPE |
+-------+------------+-------------+--------------+-----------+------------+---------+-----------+-------------+---------------+-------------+--------------+------------+-------+-------------+-----------+-------+
| TPCC | CHAP41 | C3 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| TPCC | CHAP41 | C1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| TPCC | CHAP41 | C2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+------------+-------------+--------------+-----------+------------+---------+-----------+-------------+---------------+-------------+--------------+------------+-------+-------------+-----------+-------+
3 rows in set (0.073 sec)
-- all_tab_histograms
obclient [TPCC]> select * from all_tab_histograms;
+-------+------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+-------+
| OWNER | TABLE_NAME | COLUMN_NAME | ENDPOINT_NUMBER | ENDPOINT_VALUE | ENDPOINT_ACTUAL_VALUE | ENDPOINT_ACTUAL_VALUE_RAW | ENDPOINT_REPEAT_COUNT | SCOPE |
+-------+------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+-------+
| SYS | CHAP41 | C3 | 10000 | NULL | '001' | 16022EFF0330303100 | 10000 | NULL |
| SYS | CHAP41 | C3 | 10001 | NULL | '999' | 16022EFF0339393900 | 1 | NULL |
+-------+------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+-------+
2 rows in set (0.072 sec)
-
一个是计算列的选择率。(备注:选择率越低越好,唯一性越高越好)
- Oracle模式,总共8个填空,计算列的预估选择率和实际选择率
-
一个给了一个SQL语句,优化这个SQL,由大概9秒到最终不到1秒,需要贴出SQL的执行计划,需要查询这个表plan_cache_plan_explain,直接explain 语句不得分。优化用到了绑定outline,我这边是调整了表的join顺序优化的
-
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001054318
-
gv$sql_audit
- 获取gv$plan_cache_plan_explain表需要的字段和SQL语句
-
gv$plan_cache_plan_explain
- 查询时需要指定 IP、PORT、TENANT_ID、PLAN_ID 字段
obclient [oceanbase]> select PLAN_DEPTH,PLAN_LINE_ID,OPERATOR,NAME,ROWS,COST from gv$plan_cache_plan_explain where TENANT_ID=1002 and ip='10.186.65.12' and port=2882 and PLAN_ID=648 order by PLAN_LINE_ID; +------------+--------------+----------------------+---------------+------+------+ | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | +------------+--------------+----------------------+---------------+------+------+ | 0 | 0 | PHY_SCALAR_AGGREGATE | NULL | 1 | 424 | | 1 | 1 | PHY_TABLE_SCAN | CHAP41(C2_C3) | 1000 | 386 | +------------+--------------+----------------------+---------------+------+------+ 2 rows in set (0.008 sec)
-
-
一个计算表的统计信息,并提供SQL语句。使用dbms_stats.gather_table或者analyze table 都行,没有指定用哪一个。查询列的统计信息,需要自己写sql,sql需要粘贴到答题卡,sql查询的列需要带上分区id等字段
-- 对指定字段
call dbms_stats.gather_table_stats('tpcc','chap41', method_opt=>'for columns c3
size auto');
-- 对所有字段
CALL DBMS_STATS.GATHER_TABLE_STATS ('tpcc', 'chap41', method_opt=>'FOR ALL COLUMNS SIZE
AUTO', granularity=>'ALL', degree=>4);
-- 集群sys租户查询
select * from __all_virtual_column_stat_v2 where table_id='1101710651081564'\G
-
数据迁移
OAT部署
使用OAT部署OMS
-
配置 OMS 组件 metadb 所需环境
- 步骤实验手册文件里有,涉及磁盘分区(命令提前准备下)
## 步骤 1 使用 Linux fdisk 工具对/dev/vdb 建立两个如下分区
/dev/vdb1 2048 230688767 115343360 83 Linux (110G)
/dev/vdb2 230688768 272629759 20970496 83 Linux (20G)
## 步骤 2 创建文件系统
mkfs.ext4 /dev/vdb1
mkfs.ext4 /dev/vdb2
## 步骤 3 创建/data/1 和/data/log1 路径
mkdir -p /data/1
mkdir -p /data/log1
## 步骤 4 装载分区
mount /dev/vdb1 /data/1
mount /dev/vdb2 /data/log
迁移全量和增量MySQL5.7到OBMySQL
DBCAT迁移MySQL5.7的存储过程
-
https://www.oceanbase.com/docs/community-observer-cn-10000000000900966
-
https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000217828?back=kb
## 导出全部对象
/opt/dbcat-1.9.1-RELEASE/bin/dbcat convert \
-H 10.186.56.43 \
-P 3306 \
--user=demo \
--password=demo \
--database=demo \
--from mysql57 \
--to obmysql32x \
--all
## 单独导出存储过程对象
/opt/dbcat-1.9.1-RELEASE/bin/dbcat convert \
-H 10.186.56.43 \
-P 3306 \
--user=demo \
--password=demo \
--database=demo \
--from mysql57 \
--to obmysql32x \
--procedure '*'
-
运维管理
租户扩容
-
https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000946098
-
https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000944125
通过修改 unit_config 实现租户扩缩容
-
给OB-MYSQL 扩容,由原来的2c3G到3C6G,会有资源不足,自己想办法,涉及到Unit 的迁移命令。(题目会给你提示,按照提示做就可以了) 我这边白屏扩容后,未遇到资源不足的问题
通过修改 UNIT_NUM 实现租户扩缩容
- 给OB-ORACLE 扩容,资源单元由一个变为两个
SELECT
zone,
concat(svr_ip, ':', svr_port) observer,
cpu_capacity,
cpu_total,
cpu_assigned,
cpu_assigned_percent,
mem_capacity,
mem_total,
mem_assigned,
mem_assigned_percent,
unit_Num,
round(`load`, 2) `load`,
round(cpu_weight, 2) cpu_weight,
round(memory_weight, 2) mem_weight,
leader_count
FROM __all_virtual_server_stat
ORDER BY zone,svr_ip;
OBProxy管理
-
调整只读事务参数,贴出调整命令,那个 ob_proxy_readonly_transaction_routing_policy 为 True和 False的。
obclient [oceanbase]> show parameters like 'ob_proxy_readonly_transaction_routing_policy'\G
*************************** 1. row ***************************
zone: zone1
svr_type: observer
svr_ip: 10.186.65.12
svr_port: 2882
name: ob_proxy_readonly_transaction_routing_policy
data_type: NULL
value: True
info: Proxy route policy for readonly sql
section: OBSERVER
scope: TENANT
source: DEFAULT
edit_level: DYNAMIC_EFFECTIVE
1 row in set (0.004 sec)
obclient [oceanbase]> ALTER SYSTEM SET ob_proxy_readonly_transaction_routing_policy = false;
Query OK, 0 rows affected (0.061 sec)
资源限流
- sysbench 压测,截图保存内存瓶颈,和实验手册一样的。
select t1.*,USED/1024/1024 from gv$memory t1 where TENANT_ID=1002 order by used desc limit 10;
SELECT * FROM __all_virtual_memory_info ORDER BY used desc limit 10;
select svr_ip,left(query_sql,50),sql_id,plan_id,trace_id,rpc_count,ret_code,plan_type,elapsed_time,get_plan_time from gv$sql_audit where ret_code= '-4030' and query_sql like '%sbtest20%' order by elapsed_time limit 5;
-
sysbench 压测,想办法限流让不出现内存瓶颈。调整参数writing_throttling_trigger_percentage的值为90%,进行内存限流
-
当 MemStore 已使用的内存达到该阈值时,触发写入限速。在3.x的默认值为 100,表示关闭写入限速机制
-
obclient [oceanbase]> show parameters like 'writing_throttling_trigger_percentage'\G
*************************** 1. row ***************************
zone: zone1
svr_type: observer
svr_ip: 10.186.65.12
svr_port: 2882
name: writing_throttling_trigger_percentage
data_type: NULL
value: 100
info: the threshold of the size of the mem store when writing_limit will be triggered. Rang:(0,100]. setting 100 means turn off writing limit
section: TRANS
scope: TENANT
source: DEFAULT
edit_level: DYNAMIC_EFFECTIVE
1 row in set (0.007 sec)
-- 这个修改后的值,只能在对应租户里查看90%,全局还是为100%,除非不加租户做全局设置
obclient [oceanbase]> alter system set writing_throttling_trigger_percentage=90 tenant='obce_o';
Query OK, 0 rows affected (0.048 sec)
运维管理表
-
基础表
-
gv$sql_audit
-
gv$memory
-
gv$table
-
__all_virtual_meta_table
-
gv$memstore_info
-
-
统计信息相关
-
自动采集(每日合并)
-
__all_virtual_meta_table
-
__all_virtual_column_statistic
-
-
手工收集(集群级表/MySQL租户级)
-
__all_virtual_table_stat_v2 / __all_table_stat_v2
-
__all_virtual_column_stat_v2 / __all_column_stat_v2
-
__all_virtual_histogram_stat_v2 / __all_histogram_stat_v2
-
-
手工采集(Oracle租户级表)
-
all_tab_statistics
-
all_tab_col_statistics
-
all_tab_histograms
-
-
-
连接关联相关
-
oceanbase.__all_virtual_processlist(集群租户)
-
sys.all_virtual_processlist(Oracle sys租户)
-
运维命令
alter system major freeze;
select * from __all_zone;
-
主备库管理
搭建主备库
-
主备架构不均衡,主库2-2-2,备库1-1-1。
- 搭建备集群可以成功,但ob_oracle租户没过去,需要手动修改备集群ob_oracle租户的资源规格,调小后(1c1g),自动会过去
通过OCP做switchover
通过黑屏做switchover
通过黑屏做failover
- 主库需要down掉4个节点,才可以,需要贴出failover的命令以及OCP的结果。