OB 运维 | 一文讲透 OceanBase 单机版

su - admin
tar -xzf oceanbase-all-in-one-*.tar.gz
cd oceanbase-all-in-one/bin/
./install.sh
source ~/.oceanbase-all-in-one/bin/env.sh
1.
2.
3.
4.
5.

obd mirror disable remote
obd mirror list
# 查看 Type=remote 对应的 Enabled 变成了 False，说明已关闭远程镜像源
1.
2.
3.

obd mirror list local
1.

cd ~/.oceanbase-all-in-one/obd/usr/obd/example/
cp mini-single-example.yaml mini-single-20240221.yaml
vim mini-single-20240221.yaml
1.
2.
3.

user:
    username: admin
    password: OceanBase_123#
    port: 22
oceanbase-ce:
  servers:
    - 10.186.58.87
  global:
    home_path: /home/admin/observer
    data_dir: /data/1
    redo_dir: /data/log1
    devname: eth0
    mysql_port: 2881
    rpc_port: 2882
    zone: zone1
    cluster_id: 1
    memory_limit: 12G
    system_memory: 1G
    datafile_size: 10G
    datafile_next: 2G
    datafile_maxsize: 20G
    log_disk_size: 20G
    cpu_count: 12
    production_mode: false
    enable_syslog_wf: false
    enable_syslog_recycle: true
    max_syslog_file_count: 4
    root_password: OceanBase_123#
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.

obd cluster deploy obtest -c mini-single-20240221.yaml
1.

echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/admin/observer/lib' >> ~/.bash_profile
source ~/.bash_profile
1.
2.

/home/admin/observer/bin/observer --version
1.

obd cluster start obtest
1.

mysql -h10.186.58.87 -P2881 -uroot -p'OceanBase_123#' -Doceanbase -A
show full processlist;
1.
2.

MySQL [oceanbase]> SELECT TENANT_NAME, TENANT_ID, TENANT_ROLE, SCN_TO_TIMESTAMP(SYNC_SCN),NOW()  FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'tmysql';
+-------------+-----------+-------------+----------------------------+---------------------+
| TENANT_NAME | TENANT_ID | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) | NOW()               |
+-------------+-----------+-------------+----------------------------+---------------------+
| tmysql      |      1004 | STANDBY     | 2024-02-23 17:07:06.028739 | 2024-02-23 17:07:06 |
+-------------+-----------+-------------+----------------------------+---------------------+
1 row in set (0.02 sec)
1.
2.
3.
4.
5.
6.
7.

# 4k seq read
[root@10-186-58-85 disktest]# fio -filename=/data/disktest/file -direct=1 -iodepth 1 -thread -ioengine=psync -bs=4k -size=10G -numjobs=30 -runtime=60 -group_reporting -name=iotest4kW -rw=read

fio-3.7
...
   iops        : min= 3822, max= 5786, avg=4975.06, stdev=262.95, samples=3591
...

# 4K seq write
[root@10-186-58-85 disktest]# fio -filename=/data/disktest/file -direct=1 -iodepth 1 -thread -ioengine=psync -bs=4k -size=10G -numjobs=30 -runtime=60 -group_reporting -name=iotest4kW -rw=write
...
   iops        : min= 2370, max= 5004, avg=3919.07, stdev=481.46, samples=3577
...

# 4K rand read
[root@10-186-58-85 data]# fio -filename=/data/disktest/file -direct=1 -iodepth 1 -thread -ioengine=psync -bs=4k -size=10G -numjobs=30 -runtime=60 -group_reporting -name=iotest4kW -rw=randread
...
   iops        : min= 3746, max= 5390, avg=4768.96, stdev=259.29, samples=3583
...

# 4K rand write
[root@10-186-58-85 data]# fio -filename=/data/disktest/file -direct=1 -iodepth 1 -thread -ioengine=psync -bs=4k -size=10G -numjobs=30 -runtime=60 -group_reporting -name=iotest4kW -rw=randwrite
...
   iops        : min=  784, max= 4416, avg=3074.03, stdev=636.20, samples=3574
...
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.

[root@10-186-58-85 ~]# /root/sysbench-1.0.20/src/sysbench cpu --time=60 --threads=4 --report-interval=2 run
...
CPU speed:
    events per second: 16316.69

General statistics:
    total time:                          60.0011s
    total number of events:              979033

Latency (ms):
         min:                                    0.23
         avg:                                    0.24
         max:                                   40.26
         95th percentile:                        0.25
         sum:                               239493.26

Threads fairness:
    events (avg/stddev):           244758.2500/1500.12
    execution time (avg/stddev):   59.8733/0.01
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

cd ~/oceanbase-all-in-one/rpms/
obd mirror clone oceanbase-diagnostic-tool-1.6.0-22024020410.el7.x86_64.rpm
obd obdiag deploy
1.
2.
3.

odb obdiag check <cluster_name>
# 巡检正常完成，会有如下提示：Check observer finished. For more details, please run cmd' cat ./check_report//check_report_observer_2024-02-27-14-16-19.table '
1.
2.

[root@10-186-58-75 oceanbase-diagnostic-tool]# cat ./check_report//check_report_observer_2024-02-27-14-16-19.table
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                    fail-tasks-report                                                                     |
+-------------------------------------------+--------------------------------------------------------------------------------------------------------------+
|                    task                   | task_report                                                                                                  |
+-------------------------------------------+--------------------------------------------------------------------------------------------------------------+
| sysbench.sysbench_free_test_network_speed | [fail] [remote:10.186.58.86] network_speed is  and the type is <class 'str'>, not int or float ordecimal !   |
+-------------------------------------------+--------------------------------------------------------------------------------------------------------------+
+----------------------------------------------------------------------------------------------------------------------------------------+
|                                                         critical-tasks-report                                                          |
+-------------------------------------------+--------------------------------------------------------------------------------------------+
|                    task                   | task_report                                                                                |
+-------------------------------------------+--------------------------------------------------------------------------------------------+
|   sysbench.sysbench_free_test_cpu_count   | [critical] [remote:10.186.58.86] cpu_count/os_cpu_count is 8%,is not between 80 and 100    |
|  sysbench.sysbench_free_test_memory_limit | [critical] [remote:10.186.58.86] memory_limit/os_memory is 26%,is not between 80 and 100   |
| sysbench.sysbench_test_cluster_parameters | [critical] [cluster:obcluster] cluster's enable_perf_event is true , need to change False  |
+-------------------------------------------+--------------------------------------------------------------------------------------------+
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                warning-tasks-report                                                                                               |
+----------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                     task                     | task_report                                                                                                                                                        |
+----------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|    sysbench.sysbench_free_test_cpu_count     | [warning] [cluster:obcluster] cpu_count is 2 , need to calculate the relationship between cpu_count and the number of system CPUs                                  |
| sysbench.sysbench_test_cluster_datafile_size | [warning] [remote:10.186.58.86] When the data disk and log disk are on the same disk, the log disk needs to occupy more than 30% of the disk space. now it is 11%  |
|                                              | [warning] [remote:10.186.58.86] When the data disk and log disk are on the same disk, the data disk needs to occupy more than 60% of the disk space. now it is 5%  |
| sysbench.sysbench_test_cluster_log_disk_size | [warning] [cluster:obcluster] log_disk_size <20G tenant: trenzy . log_disk_size need >20G                                                                          |
| sysbench.sysbench_test_tenant_cpu_parameters | [warning] [cluster:obcluster] cpu_count is 2. the min_cpu of tenant should cpu_count/2 ~ cpu_count. tenant: sys,trenzy need check                                  |
|                                              | [warning] [cluster:obcluster] the MAX_IOPS of tenant should max_cpu * 10000 ~ max_cpu * 1000000 .  tenant: trenzy need check                                       |
|                                              | [warning] [cluster:obcluster] the MIN_IOPS of tenant should max_cpu * 10000 ~ max_cpu * 1000000 .  tenant: trenzy need check                                       |
|                                              | [warning] [cluster:obcluster] cpu_count is 2. the max_cpu of tenant should cpu_count/2 ~ cpu_count. tenant: sys,trenzy need check                                  |
| sysbench.sysbench_test_tenant_log_disk_size  | [warning] [cluster:obcluster] log_disk_size <20G tenant: trenzy . log_disk_size need >20G                                                                          |
+----------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                  all-tasks-report                                                                                                 |
+----------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                     task                     | task_report                                                                                                                                                        |
+----------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|    sysbench.sysbench_free_test_cpu_count     | [critical] [remote:10.186.58.86] cpu_count/os_cpu_count is 8%,is not between 80 and 100                                                                            |
|                                              | [warning] [cluster:obcluster] cpu_count is 2 , need to calculate the relationship between cpu_count and the number of system CPUs                                  |
|   sysbench.sysbench_free_test_memory_limit   | [critical] [remote:10.186.58.86] memory_limit/os_memory is 26%,is not between 80 and 100                                                                           |
|  sysbench.sysbench_free_test_network_speed   | [fail] [remote:10.186.58.86] network_speed is  and the type is <class 'str'>, not int or float ordecimal !                                                         |
| sysbench.sysbench_test_cluster_datafile_size | [warning] [remote:10.186.58.86] When the data disk and log disk are on the same disk, the log disk needs to occupy more than 30% of the disk space. now it is 11%  |
|                                              | [warning] [remote:10.186.58.86] When the data disk and log disk are on the same disk, the data disk needs to occupy more than 60% of the disk space. now it is 5%  |
| sysbench.sysbench_test_cluster_log_disk_size | [warning] [cluster:obcluster] log_disk_size <20G tenant: trenzy . log_disk_size need >20G                                                                          |
|  sysbench.sysbench_test_cluster_parameters   | [critical] [cluster:obcluster] cluster's enable_perf_event is true , need to change False                                                                          |
| sysbench.sysbench_test_cpu_quota_concurrency | all pass                                                                                                                                                           |
|       sysbench.sysbench_test_log_level       | all pass                                                                                                                                                           |
| sysbench.sysbench_test_sql_net_thread_count  | all pass                                                                                                                                                           |
| sysbench.sysbench_test_tenant_cpu_parameters | [warning] [cluster:obcluster] cpu_count is 2. the min_cpu of tenant should cpu_count/2 ~ cpu_count. tenant: sys,trenzy need check                                  |
|                                              | [warning] [cluster:obcluster] the MAX_IOPS of tenant should max_cpu * 10000 ~ max_cpu * 1000000 .  tenant: trenzy need check                                       |
|                                              | [warning] [cluster:obcluster] the MIN_IOPS of tenant should max_cpu * 10000 ~ max_cpu * 1000000 .  tenant: trenzy need check                                       |
|                                              | [warning] [cluster:obcluster] cpu_count is 2. the max_cpu of tenant should cpu_count/2 ~ cpu_count. tenant: sys,trenzy need check                                  |
| sysbench.sysbench_test_tenant_log_disk_size  | [warning] [cluster:obcluster] log_disk_size <20G tenant: trenzy . log_disk_size need >20G                                                                          |
|  sysbench.sysbench_test_tenant_primary_zone  | all pass                                                                                                                                                           |
+----------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.

# cat min-2c4g.yaml
user:
    username: admin
    password: OceanBase_123#
    port: 22
oceanbase-ce:
  servers:
    - 10.186.56.101
  global:
    home_path: /home/admin/observer
    data_dir: /data/1
    redo_dir: /data/log1
    devname: eth0
    mysql_port: 2881
    rpc_port: 2882
    zone: zone1
    cluster_id: 1
    memory_limit: 4G
    system_memory: 1G
    datafile_size: 10G
    datafile_next: 2G
    datafile_maxsize: 20G
    log_disk_size: 20G
    cpu_count: 2
    production_mode: false
    enable_syslog_wf: false
    enable_syslog_recycle: true
    max_syslog_file_count: 4
    __min_full_resource_pool_memory: 1073741824
    root_password: OceanBase_123#
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.

[root@10-186-56-101 admin]# grep process /proc/cpuinfo
processor        : 0
processor        : 1

[root@10-186-56-101 admin]# free -m
              total        used        free      shared  buff/cache   available
Mem:           4797        2624         979          16        1194        1917
Swap:             0           0           0

[root@10-186-56-101 admin]# ps -ef | grep obs
admin     2636     1 29 07:25 ?        00:08:29 /home/admin/observer/bin/observer -r 10.186.56.101:2882:2881 -p 2881 -P 2882 -z zone1 -c 1 -d /data/1 -i eth0 -o __min_full_resource_pool_memory=1073741824,memory_limit=4G,system_memory=1G,datafile_size=10G,datafile_next=2G,datafile_maxsize=20G,log_disk_size=20G,cpu_count=2,enable_syslog_wf=False,enable_syslog_recycle=True,max_syslog_file_count=4
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

[root@10-186-58-75 example]# sysbench /root/sysbench-1.0.20/src/lua/oltp_read_write.lua --tables=5 --table_size=1000 --mysql-host=10.186.56.101 --mysql-port=2881 --mysql-user='root@trenzy' --mysql-password='OceanBase_123#' --mysql-db=sbtest --report-interval=2 --time=3600 --threads=1 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Report intermediate results every 2 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 2s ] thds: 1 tps: 37.42 qps: 749.45 (r/w/o: 524.42/149.69/75.34) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00
...
[ 342s ] thds: 1 tps: 60.50 qps: 1215.48 (r/w/o: 851.48/243.00/121.00) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 344s ] thds: 1 tps: 58.01 qps: 1159.20 (r/w/o: 812.14/231.04/116.02) lat (ms,95%): 23.95 err/s: 0.00 reconn/s: 0.00
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

FATAL: mysql_stmt_execute() returned error 4013 (No memory or reach tenant memory limit) for query 'SELECT c FROM sbtest4 WHERE id BETWEEN ? AND ? ORDER BY c'
FATAL: `thread_run' function failed: /usr/local/share/sysbench/oltp_common.lua:432: SQL error, errno = 4013, state = 'HY001': No memory or reach tenant memory limit
FATAL: mysql_stmt_execute() returned error 4013 (No memory or reach tenant memory limit) for query 'SELECT c FROM sbtest2 WHERE id BETWEEN ? AND ? ORDER BY c'
1.
2.
3.

# 默认 2G，可创建 unit 的最小内存阈值，若不设置创建 1G 内存规格的 unit 将失败。
alter system set __min_full_resource_pool_memory=1073741824;

# 默认 5s，设置查询执行时间的阈值。超过时间的请求可能被暂停，暂停后自动被判断为大查询，执行大查询调度策略
alter system set large_query_threshold='600s';

# 默认 true，是否开启审计日志；开发或性能要求高的场景可关闭，生产环境需开启
alter system set enable_sql_audit=false;

# 默认 true，是否开启信息采集；开发或性能要求高的场景可关闭，生产环境需开启
alter system set enable_perf_event=false;

# 默认 true，是否开启 Trace Log 功能
alter system set enable_record_trace_log=false;

# 默认 WDIAG，系统日志的日志级别
alter system set syslog_level='INFO';
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

# 默认 false，是否启用批处理功能的成组执行优化
alter system set ob_enable_batched_multi_statement=true;

# 默认 false，用于对日志传输是否使用压缩，建议开启减少网卡压力
alter system set log_transport_compress_all=true;
1.
2.
3.
4.
5.

# 默认 MANUAL,用于设置并行度选择策略。
set global parallel_degree_policy=AUTO;

# 默认 5，SQL 执行的租户内存百分比限制
set global ob_sql_work_area_percentage=20; 

# 默认 10000000，设置 SQL 最大执行时间，单位是微秒
set global ob_query_timeout=13888000000;

# 默认 16777216，设置最大网络包大小，单位是 Byte
set global max_allowed_packet=41943040;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

[mysql]
default-character-set = utf8mb4
user         = root
password =

[mysqld]
# Basic Settings
server-id = 1019938001
port = 8001
pid-file = mysqld.pid
socket =        mysqld.sock
mysqlx_socket = mysqldx.sock
character_set_server =        utf8mb4
default_storage_engine = InnoDB
lower_case_table_names = 1
skip_name_resolve = 1
skip_external_locking =        1
skip_slave_start =        1
basedir = /data/mysql/8001/base
datadir = /data/mysql/8001/data
tmpdir =  /data/mysql/8001/tmp

# P_S Settings
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

# Session Memory Settins
join_buffer_size = 2M
sort_buffer_size = 2M
tmp_table_size = 8M
max_heap_table_size = 8M
max_allowed_packet = 128M

# Global Memory and Cache Settings
temptable_max_ram = 1G
open_files_limit =  65535
max_connections = 1024
table_open_cache = 2048

# InnoDB Settings
innodb_buffer_pool_size = 8G
innodb_data_file_path = ibdata1:12M:autoextend
innodb_buffer_pool_instances = 1
innodb_file_per_table = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 10000
innodb_flush_method = O_DIRECT
innodb_strict_mode = 1
innodb_flush_neighbors = 1
innodb_page_cleaners = 8
innodb_purge_threads = 8
innodb_log_file_size = 1024M
innodb_log_buffer_size = 16777216
innodb_print_all_deadlocks =  1
innodb_sort_buffer_size = 67108864

# Log Settings
log_error = mysql-error.log
log_bin = mysql-bin.log
slow_query_log_file = mysql-slow.log
relay_log        = mysql-relay.log
log_slave_updates        = 1
sync_binlog =         1
relay_log_recovery = 1
binlog_format         = row
binlog_expire_logs_seconds = 2592000
slow_query_log = 1
long_query_time = 3
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# Replication Settings
slave_skip_errors = ddl_exist_errors
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1
binlog_rows_query_log_events = 1
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 8
slave_preserve_commit_order = 1
binlog_group_commit_sync_delay=10000
binlog_group_commit_sync_no_delay_count=32
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.