案例分析 | OceanBase 文档中心

背景

本文从一个通用的角度，简单还原了排查 OceanBase 数据库在压测过程中可能遇到的、比较明显的性能问题以及解决方案。实际测试、生产环境遇到的性能问题复杂多样，在对本通用场景做过处理之后，还需要具体问题具体分析，优化手段不能一概而论，以偏概全。本文基于线下实际的 BMSQL 压测环境，通过全链路的分析，逐步对性能进行调优。相关的过程分为以下几个步骤：

环境部署。
外部环境检查，包括：网络、磁盘、网卡软中断等。
资源分配与部署。
系统配置。
事务模型。
慢 SQL分析。

问题现象

--从客户端角度看，性能达不到预期
17:18:30,859 [main] INFO   jTPCC : Term-00, resultDirectory=null
17:18:30,859 [main] INFO   jTPCC : Term-00, osCollectorScript=null
17:18:30,859 [main] INFO   jTPCC : Term-00,
17:18:31,125 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 73
17:18:31,125 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    138
17:18:31,125 [main] INFO   jTPCC : Term-00,
Term-00, Running Average tpmTOTAL: 138777.38    Current tpmTOTAL: 1522932    Memory Usage: 630MB / 3786MB

--从 OBServer 角度看，OBServer 进程基本上用了大部分 CPU
top - 17:19:08 up 47 days, 17:07,  3 users,  load average: 22.43, 7.27, 3.81
Tasks: 1421 total,   2 running, 1419 sleeping,   0 stopped,   0 zombie
%Cpu(s): 29.7 us,  6.8 sy,  0.0 ni, 62.3 id,  0.0 wa,  0.0 hi,  1.1 si,  0.0 st
KiB Mem : 79179116+total, 38146384+free, 28456528+used, 12576206+buff/cache
KiB Swap:        0 total,        0 free,        0 used. 49913868+avail Mem

   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 45497 test  20   0  101.7g  90.8g  68400 S  1329 12.0   2737:33 /home/test/obdocker.z3.obs0/bin/observer
 43217 test   0   94.8g  84.7g  72004 S  1257 11.2   2384:24 /home/test/obdocker.z2.obs0/bin/observer
 40579 test  20   0   91.8g  81.9g  76912 S  1374 10.8   2574:45 /home/test/obdocker.z1.obs0/bin/observer

环境部署

OBProxy

单台 OBProxy，网卡带宽均为 2000 Mb/s，机器 IP：100.xxx.xxx.xxx。

OBserver

1:1:1，Leader 打散，三个进程放到一个 96c 的环境，每个进程绑定到 16 个物理核，模拟 16c 的小规格机器性能，物理机 IP：100.xxx.xxx.xxx。

物理机

$lscpu
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                96
On-line CPU(s) list:   0-95
Thread(s) per core:    2
Core(s) per socket:    24
Socket(s):             2
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 85
Model name:            Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz
Stepping:              4
CPU MHz:               2499.804
CPU max MHz:           3100.0000
CPU min MHz:           1000.0000
BogoMIPS:              4998.90
Virtualization:        VT-x
Hypervisor vendor:     vertical
Virtualization type:   full
L1d cache:             32K
L1i cache:             32K
L2 cache:              1024K
L3 cache:              33792K
NUMA node0 CPU(s):     0-95
Flags:                 fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 fma cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm 3dnowprefetch ida arat epb pln pts dtherm tpr_shadow vnmi flexpriority ept vpid fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm cqm mpx avx512f avx512dq rdseed adx smap clflushopt avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 xsaves cqm_llc cqm_occup_llc

排查过程

外部环境检查

OBProxy 和 OBServer 之间的网络延迟

$ping 100.xxx.xxx.xxx
PING 100.xxx.xxx.xxx (100.xxx.xxx.xxx) 56(84) bytes of data.
64 bytes from 100.x.x.x: icmp_seq=1 ttl=64 time=1.07 ms
64 bytes from 100.x.x.x: icmp_seq=2 ttl=64 time=1.06 ms
64 bytes from 100.x.x.x: icmp_seq=3 ttl=64 time=1.06 ms
64 bytes from 100.x.x.x: icmp_seq=4 ttl=64 time=1.06 ms
64 bytes from 100.x.x.x: icmp_seq=5 ttl=64 time=1.07 ms
64 bytes from 100.x.x.x: icmp_seq=6 ttl=64 time=1.07 ms
64 bytes from 100.x.x.x: icmp_seq=7 ttl=64 time=1.07 ms

测试发现，OBProxy 和 OBServer 之间的网络通信延迟比较大，进一步分析 tc 相关配置发现，是 197 上配置了网络 1 ms 延迟，具体如下：

$tc qdisc list
qdisc mq 0: dev eth0 root
qdisc mq 0: dev eth1 root
qdisc netem 8004: dev bond0 root refcnt 17 limit 1000 delay 1.0ms

说明

客户端压力机器可能会受到 CPU 资源、网络等限制，从而成为瓶颈。因此我们还需要关注对环境的检查。

磁盘环境检查

$ll
total 0
lrwxrwxrwx 1 test users 55 Oct 23 22:31 clog -> /data/0/test/obdocker.z1.obs0.test/store/clog
lrwxrwxrwx 1 test users 55 Oct 23 22:31 ilog -> /data/0/test/obdocker.z1.obs0.test/store/ilog
lrwxrwxrwx 1 test users 55 Oct 23 22:31 slog -> /data/0/test/obdocker.z1.obs0.test/store/slog
lrwxrwxrwx 1 test users 53 Oct 23 22:31 sort_dir -> /data/1/test/obdocker.z1.obs0.test/sort_dir
lrwxrwxrwx 1 test users 52 Oct 23 22:31 sstable -> /data/1/test/obdocker.z1.obs0.test/sstable

[test@i39g15302.eu95sqa /home/test/obdocker.z1.obs0/store]
$df /data/0/test/obdocker.z1.obs0.test/store/clog
Filesystem            1K-blocks      Used  Available Use% Mounted on
/dev/mapper/vg0-data 7497910272 478950752 7018959520   7% /data/0

[test@i39g15302.eu95sqa /home/test/obdocker.z1.obs0/store]
$df /data/1/test/obdocker.z1.obs0.test/sstable
Filesystem            1K-blocks       Used  Available Use% Mounted on
/dev/mapper/vg1-data 7497910272 4947433384 2550476888  66% /data/1

$df
Filesystem            1K-blocks       Used  Available Use% Mounted on
/dev/sda3              51343996   33182756   15523416  69% /
devtmpfs              395884508          0  395884508   0% /dev
tmpfs                 395895592      66592  395829000   1% /dev/shm
tmpfs                 395895592       1724  395893868   1% /run
tmpfs                 395895592          0  395895592   0% /sys/fs/cgroup
/dev/sda2                999320     138896     791612  15% /boot
/dev/sda5             174995444  110602948   55433520  67% /home
/dev/mapper/vg5-data 7497910272  524321076 6973589196   7% /data/5
/dev/mapper/vg2-data 7497910272 1250971888 6246938384  17% /data/2
/dev/mapper/vg4-data 7497910272  118638368 7379271904   2% /data/4
/dev/mapper/vg0-data 7497910272  478950780 7018959492   7% /data/0
/dev/mapper/vg1-data 7497910272 4947433384 2550476888  66% /data/1
/dev/mapper/vg3-data 7497910272  531505784 6966404488   8% /data/3

$ll /dev/mapper/vg0-data
lrwxrwxrwx 1 root root 7 Sep  8 00:12 /dev/mapper/vg0-data -> ../dm-0

$ll /dev/mapper/vg1-data
lrwxrwxrwx 1 root root 7 Sep  8 00:12 /dev/mapper/vg1-data -> ../dm-1

dm-0 的相关监控数据，await 数据不是很高，显示是正常的。

Time           ------------------------------------------------------------------dm-0------------------------------------------------------------------
Time            rrqms   wrqms   %rrqm   %wrqm      rs      ws   rsecs   wsecs  rqsize  rarqsz  warqsz  qusize   await  rawait  wawait   svctm    util
25/10/21-14:29   0.00    0.00    0.00    0.00    1.97    4.1K   15.73   69.6K    8.57    4.00    8.57    0.00    0.03    0.08    0.03    0.02    8.02
25/10/21-14:30   0.00    0.00    0.00    0.00    2.00    4.1K   16.00   70.8K    8.55    4.00    8.56    0.00    0.03    0.09    0.03    0.02    7.79
25/10/21-14:31   0.00    0.00    0.00    0.00    2.00    4.0K   16.00   69.4K    8.66    4.00    8.66    0.00    0.03    0.07    0.03    0.02    8.34
25/10/21-14:32   0.00    0.00    0.00    0.00    1.97    4.1K   15.73   69.9K    8.57    4.00    8.57    0.00    0.03    0.07    0.03    0.02    9.01
25/10/21-14:33   0.00    0.00    0.00    0.00    2.00    4.0K   16.00   69.5K    8.63    4.00    8.63    0.00    0.03    0.12    0.03    0.02    9.25
25/10/21-14:34   0.00    0.00    0.00    0.00    2.00    3.9K   16.00   68.7K    8.69    4.00    8.70    0.00    0.03    0.08    0.03    0.02    9.69
25/10/21-14:35   0.00    0.00    0.00    0.00    2.00    4.0K   16.00   68.7K    8.68    4.00    8.68    0.00    0.03    0.07    0.03    0.02   10.09
25/10/21-14:36   0.00    0.00    0.00    0.00    1.97    4.0K   15.73   68.8K    8.63    4.00    8.64    0.00    0.03    0.13    0.03    0.02    9.85
25/10/21-14:37   0.00    0.00    0.00    0.00    2.00    3.9K   16.00   68.0K    8.67    4.00    8.67    0.00    0.03    0.10    0.03    0.02    9.75
25/10/21-14:38   0.00    0.00    0.00    0.00    2.00    3.9K   16.00   68.0K    8.73    4.00    8.73    0.00    0.04    0.07    0.04    0.02    9.54
25/10/21-14:39   0.00    0.00    0.00    0.00    2.00    3.9K   16.00   68.1K    8.73    4.00    8.73    0.00    0.04    0.09    0.04    0.02    9.64
25/10/21-14:40   0.00    0.00    0.00    0.00    1.97    3.9K   15.73   68.4K    8.68    4.00    8.68    0.00    0.04    0.09    0.04    0.02    9.60
25/10/21-14:41   0.00    0.00    0.00    0.00    2.00    3.8K   16.00   66.1K    8.61    4.00    8.61    0.00    0.03    0.10    0.03    0.02    9.63
25/10/21-14:42   0.00    0.00    0.00    0.00    4.28    3.7K  308.27   63.2K    8.55   35.98    8.52    0.00    0.03    0.16    0.03    0.03    9.51
25/10/21-14:43   0.00    0.00    0.00    0.00    1.97    4.1K   15.73   70.7K    8.69    4.00    8.69    0.00    0.03    0.08    0.03    0.02   10.39
25/10/21-14:44   0.00    0.00    0.00    0.00    2.00    4.1K   16.00   70.6K    8.71    4.00    8.72    0.00    0.03    0.13    0.03    0.03   10.40
25/10/21-14:45   0.00    0.00    0.00    0.00    2.00    4.1K   16.00   70.8K    8.70    4.00    8.70    0.00    0.03    0.12    0.03    0.03   10.61
25/10/21-14:46   0.00    0.00    0.00    0.00    2.00    4.0K   16.00   70.2K    8.67    4.00    8.67    0.00    0.03    0.10    0.03    0.02    9.99
25/10/21-14:47   0.00    0.00    0.00    0.00    1.97    4.0K   15.73   70.2K    8.68    4.00    8.68    0.00    0.03    0.07    0.03    0.02    9.92

dm-1 的相关监控数据，await 数据不是很高，显示是正常的。

Time           ------------------------------------------------------------------dm-1------------------------------------------------------------------
Time            rrqms   wrqms   %rrqm   %wrqm      rs      ws   rsecs   wsecs  rqsize  rarqsz  warqsz  qusize   await  rawait  wawait   svctm    util
25/10/21-14:24   0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00
25/10/21-14:25   0.00    0.00    0.00    0.00  434.95    0.00   16.1K    0.00   18.90   18.90    0.00    0.00    0.16    0.16    0.00    0.09    4.02
25/10/21-14:26   0.00    0.00    0.00    0.00  301.33    0.00   11.8K    0.00   19.97   19.97    0.00    0.00    0.17    0.17    0.00    0.11    3.35
25/10/21-14:27   0.00    0.00    0.00    0.00  343.47    0.00   12.1K    0.00   17.97   17.97    0.00    0.00    0.13    0.13    0.00    0.11    3.72
25/10/21-14:28   0.00    0.00    0.00    0.00  261.23    0.00    9.2K    0.00   17.99   17.99    0.00    0.00    0.13    0.13    0.00    0.10    2.72
25/10/21-14:29   0.00    0.00    0.00    0.00  287.60    0.00   10.0K    0.00   17.83   17.83    0.00    0.00    0.13    0.13    0.00    0.11    3.07
25/10/21-14:30   0.00    0.00    0.00    0.00  169.75    0.00    6.3K    0.00   19.10   19.10    0.00    0.00    0.15    0.15    0.00    0.11    1.85
25/10/21-14:31   0.00    0.00    0.00    0.00  114.92    0.00    4.5K    0.00   19.96   19.96    0.00    0.00    0.16    0.16    0.00    0.11    1.25
25/10/21-14:32   0.00    0.00    0.00    0.00   83.47    0.00    2.9K    0.00   18.00   18.00    0.00    0.00    0.13    0.13    0.00    0.11    0.95
25/10/21-14:33   0.00    0.00    0.00    0.00   63.85    0.00    2.1K    0.00   16.91   16.91    0.00    0.00    0.12    0.12    0.00    0.12    0.74
25/10/21-14:34   0.00    0.00    0.00    0.00   50.60    0.00    1.7K    0.00   16.99   16.99    0.00    0.00    0.11    0.11    0.00    0.11    0.57
25/10/21-14:35   0.00    0.00    0.00    0.00   43.72    0.00    1.5K    0.00   17.63   17.63    0.00    0.00    0.12    0.12    0.00    0.11    0.49
25/10/21-14:36   0.00    0.00    0.00    0.00   38.03    0.00    1.3K    0.00   17.04   17.04    0.00    0.00    0.11    0.11    0.00    0.11    0.43
25/10/21-14:37   0.00    0.00    0.00    0.00   34.05    0.00    1.2K    0.00   18.76   18.76    0.00    0.00    0.15    0.15    0.00    0.12    0.41

网卡软中断

通过 Top 命令，然后执行 1，查看每个 cpu 的相关统计信息，关于网卡软中断，主要关注 si 这一列的值。OBProxy 和 OBServer 两侧均需要关注打散情况，本环境的情况如下：

top - 14:07:35 up 58 days, 13:55,  1 user,  load average: 2.52, 2.84, 2.62
Tasks: 1377 total,   1 running, 1375 sleeping,   0 stopped,   1 zombie
%Cpu0  :  45.6 us,  19.3 sy,  0.0 ni, 4.9 id,  0.0 wa,  0.0 hi,  30.2 si,  0.0 st
%Cpu1  :  36.6 us,  20.6 sy,  0.0 ni, 17.4 id,  0.0 wa,  0.0 hi,  25.4 si,  0.0 st
%Cpu2  :  22.1 us,  2.0 sy,  0.0 ni, 75.8 id,  0.0 wa,  0.0 hi,  0.1 si,  0.0 st
%Cpu3  :  21.1 us,  3.0 sy,  0.0 ni, 75.9 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu4  :  10.3 us,  4.1 sy,  0.0 ni, 84.3 id,  0.0 wa,  0.0 hi,  0.3 si,  0.0 st
%Cpu5  :  12.1 us,  3.6 sy,  0.0 ni, 84.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu6  :  10.6 us,  3.6 sy,  0.0 ni, 85.8 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu7  :  12.3 us,  6.1 sy,  0.0 ni, 81.2 id,  0.0 wa,  0.0 hi,  0.4 si,  0.0 st
%Cpu8  :  21.2 us,  2.6 sy,  0.0 ni, 76.2 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu9  :  11.1 us,  4.1 sy,  0.0 ni, 84.8 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu10 :  11.0 us,  2.6 sy,  0.0 ni, 86.3 id,  0.0 wa,  0.0 hi,  0.1 si,  0.0 st
%Cpu11 :  11.7 us,  3.6 sy,  0.0 ni, 84.7 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu12 :  10.3 us,  2.6 sy,  0.0 ni, 86.8 id,  0.0 wa,  0.0 hi,  0.3 si,  0.0 st
%Cpu13 :  10.0 us,  4.6 sy,  0.0 ni, 85.4 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu14 :  13.8 us,  2.0 sy,  0.0 ni, 84.1 id,  0.0 wa,  0.0 hi,  0.1 si,  0.0 st
%Cpu15 :  12.2 us,  3.6 sy,  0.0 ni, 84.2 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu16 :  10.0 us,  1.9 sy,  0.0 ni, 88.0 id,  0.0 wa,  0.0 hi,  0.1 si,  0.0 st
%Cpu17 :  10.0 us,  3.2 sy,  0.0 ni, 86.8 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st

上图发现，网卡的软中断绑定到了该进程运行的前两个物理核上。但对于极致性能而言，可以尝试减少打散的核数，提高 CPU 的亲和性，从而提升性能。比如对于 16c 的环境，绑定到 4 个左右。

资源分配与部署

查看租户资源分配

OceanBase(root@oceanbase)>select MAX_CPU, MIN_CPU, MEMORY_SIZE/1024/1024/1024 from gv$ob_units where tenant_id=1002;
+---------+---------+----------------------------+
| MAX_CPU | MIN_CPU | MEMORY_SIZE/1024/1024/1024 |
+---------+---------+----------------------------+
|       2 |       2 |             3.000000000000 |
+---------+---------+----------------------------+
1 row in set (0.00 sec)

对于 16 核的物理资源，除去 OBProxy 和 sys 租户的 cpu 分配，该租户分配 11 个 CPU 是合理的；租户整体内存 75G，对于小租户而言，也是可以的。

分区分布

--1002租户分区分配情况：
MySQL [oceanbase]> select svr_ip, svr_port, count(1) from __all_virtual_ls_meta_table where tenant_id=1002 group by svr_port;
+----------------+----------+----------+
| svr_ip         | svr_port | count(1) |
+----------------+----------+----------+
| 100.x.x.x      |    40000 |      130 |
| 100.x.x.x      |    40001 |      130 |
| 100.x.x.x      |    40002 |      130 |
+----------------+----------+----------+
3 rows in set (0.00 sec)

--所有租户分区分配情况：
MySQL [oceanbase]> select svr_ip, svr_port, count(1) from __all_virtual_ls_meta_table group by svr_port;
+----------------+----------+----------+
| svr_ip         | svr_port | count(1) |
+----------------+----------+----------+
| 100.x.x.x      |    40000 |      135 |
| 100.x.x.x      |    40001 |      135 |
| 100.x.x.x      |    40002 |      135 |
+----------------+----------+----------+
3 rows in set (0.01 sec)

对于 1002 租户而言，单机上的分配是均衡的，分别为 130 个。整个集群三个机器分区总分布初步看是均衡的。

Leader 分布

MySQL [oceanbase]> select svr_ip, svr_port, count(1) from __all_virtual_ls_meta_table where role=1 and tenant_id=1002 group by svr_port;
+----------------+----------+----------+
| svr_ip         | svr_port | count(1) |
+----------------+----------+----------+
| 100.x.x.x      |    40000 |       43 |
| 100.x.x.x      |    40001 |       44 |
| 100.x.x.x      |    40002 |       43 |
+----------------+----------+----------+
3 rows in set (0.01 sec)

Leader 分布基本是均衡的，符合预期。

系统配置

压测过程中发现，单个进程的 rpc_io 和 TNT_1002 的线程数比较多，该情况下 CPU 上下文切换带来的开销不容小觑，具体如下：

   PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
 41757 hudson  20   0   87.0g  77.4g  77552 S  24.3 10.3  65:39.77 TNT_L0_1002
 41766 hudson  20   0   87.0g  77.4g  77552 S  24.3 10.3  61:49.21 TNT_L0_1002
 41821 hudson  20   0   87.0g  77.4g  77552 S  24.3 10.3  60:28.70 TNT_L0_1002
 41839 hudson  20   0   87.0g  77.4g  77552 S  24.3 10.3  56:18.97 TNT_L0_1002
 44240 hudson  20   0   86.2g  75.3g  73056 S  24.3 10.0  62:22.23 TNT_L0_1002
 46493 hudson  20   0   85.2g  74.8g  69380 S  24.3  9.9  82:10.87 TNT_L0_1002
 46541 hudson  20   0   85.2g  74.8g  69380 S  24.3  9.9  76:30.68 TNT_L0_1002
 46553 hudson  20   0   85.2g  74.8g  69380 S  24.3  9.9  73:08.60 TNT_L0_1002
 46569 hudson  20   0   85.2g  74.8g  69380 S  24.3  9.9  78:12.30 TNT_L0_1002
 41730 hudson  20   0   87.0g  77.4g  77552 S  24.1 10.3  30:52.16 TNT_L0_1002
 41731 hudson  20   0   87.0g  77.4g  77552 S  24.1 10.3  10:42.78 TNT_L0_1002
 41732 hudson  20   0   87.0g  77.4g  77552 S  24.1 10.3  45:38.79 TNT_L0_1002
 41751 hudson  20   0   87.0g  77.4g  77552 S  24.1 10.3  66:11.92 TNT_L0_1002
 41752 hudson  20   0   87.0g  77.4g  77552 S  24.1 10.3  65:01.33 TNT_L0_1002
 41759 hudson  20   0   87.0g  77.4g  77552 S  24.1 10.3  63:45.76 TNT_L0_1002
 41761 hudson  20   0   87.0g  77.4g  77552 S  24.1 10.3  63:22.69 TNT_L0_1002
 41764 hudson  20   0   87.0g  77.4g  77552 S  24.1 10.3  62:29.47 TNT_L0_1002
 41767 hudson  20   0   87.0g  77.4g  77552 S  24.1 10.3  61:19.79 TNT_L0_1002
 41769 hudson  20   0   87.0g  77.4g  77552 S  24.0 10.3  56:32.64 TNT_L0_1002
 41773 hudson  20   0   87.0g  77.4g  77552 S  23.6 10.3  63:28.14 TNT_L0_1002
 41780 hudson  20   0   87.0g  77.4g  77552 S  23.6 10.3  54:43.27 TNT_L0_1002
 41782 hudson  20   0   87.0g  77.4g  77552 S  23.6 10.3  50:04.99 TNT_L0_1002
 41792 hudson  20   0   87.0g  77.4g  77552 S  23.6 10.3  44:38.08 TNT_L0_1002
 41801 hudson  20   0   87.0g  77.4g  77552 S  23.6 10.3  34:51.42 TNT_L0_1002
 41824 hudson  20   0   87.0g  77.4g  77552 S  23.3 10.3  57:56.85 TNT_L0_1002
 41827 hudson  20   0   87.0g  77.4g  77552 S  23.3 10.3  56:15.64 TNT_L0_1002
 41833 hudson  20   0   87.0g  77.4g  77552 S  23.3 10.3  51:29.73 TNT_L0_1002
 44205 hudson  20   0   86.2g  75.3g  73056 S  2373 10.0  88:37.18 TNT_L0_1002
 43758 hudson  20   0   86.2g  75.3g  73056 S  14.2 10.0   2549:35 ILOGFlush
 40683 hudson  20   0   87.0g  77.4g  77552 S  10.2 10.3   3279:22 KVCacheWash
 42811 hudson  20   0   87.0g  77.4g  77552 S  8.7 10.3   9:31.68 RpcIO
 42797 hudson  20   0   87.0g  77.4g  77552 S  8.7 10.3   9:30.90 RpcIO
 42809 hudson  20   0   87.0g  77.4g  77552 S  8.7 10.3   9:28.93 RpcIO
 45254 hudson  20   0   86.2g  75.3g  73056 S  8.7 10.0   8:10.28 RpcIO
 45256 hudson  20   0   86.2g  75.3g  73056 S  8.7 10.0   8:10.00 RpcIO
 45266 hudson  20   0   86.2g  75.3g  73056 S  8.7 10.0   8:08.52 RpcIO
 45273 hudson  20   0   86.2g  75.3g  73056 S  8.7 10.0   8:06.83 RpcIO
 42783 hudson  20   0   87.0g  77.4g  77552 S  8.7 10.3   9:36.50 RpcIO
 42784 hudson  20   0   87.0g  77.4g  77552 S  8.7 10.3   9:36.38 RpcIO
 42785 hudson  20   0   87.0g  77.4g  77552 S  8.7 10.3   9:38.99 RpcIO
 42786 hudson  20   0   87.0g  77.4g  77552 S  8.7 10.3   9:37.14 RpcIO
 42787 hudson  20   0   87.0g  77.4g  77552 S  8.7 10.3   9:33.81 RpcIO
 42788 hudson  20   0   87.0g  77.4g  77552 S  8.7 10.3   9:34.72 RpcIO
 42789 hudson  20   0   87.0g  77.4g  77552 S  8.3 10.3   9:40.88 RpcIO
 42790 hudson  20   0   87.0g  77.4g  77552 S  8.3 10.3   9:33.97 RpcIO
 42791 hudson  20   0   87.0g  77.4g  77552 S  8.3 10.3   9:36.76 RpcIO
 42792 hudson  20   0   87.0g  77.4g  77552 S  8.3 10.3   9:36.84 RpcIO
 42793 hudson  20   0   87.0g  77.4g  77552 S  8.3 10.3   9:37.62 RpcIO
 42794 hudson  20   0   87.0g  77.4g  77552 S  8.3 10.3   9:36.26 RpcIO
 42795 hudson  20   0   87.0g  77.4g  77552 S  8.3 10.3   9:32.23 RpcIO
 42796 hudson  20   0   87.0g  77.4g  77552 S  8.2 10.3   9:27.86 RpcIO
 42798 hudson  20   0   87.0g  77.4g  77552 S  8.2 10.3   9:26.15 RpcIO
 42799 hudson  20   0   87.0g  77.4g  77552 S  8.2 10.3   9:32.53 RpcIO
 42800 hudson  20   0   87.0g  77.4g  77552 S  8.2 10.3   9:29.42 RpcIO
 42801 hudson  20   0   87.0g  77.4g  77552 S  8.2 10.3   9:32.58 RpcIO
 42802 hudson  20   0   87.0g  77.4g  77552 S  8.2 10.3   9:37.14 RpcIO
 42803 hudson  20   0   87.0g  77.4g  77552 S  8.2 10.3   9:30.59 RpcIO
 42804 hudson  20   0   87.0g  77.4g  77552 S  8.2 10.3   9:26.93 RpcIO
 42805 hudson  20   0   87.0g  77.4g  77552 S  8.1 10.3   9:32.03 RpcIO
 42806 hudson  20   0   87.0g  77.4g  77552 S  8.0 10.3   9:29.10 RpcIO
 42807 hudson  20   0   87.0g  77.4g  77552 S  8.0 10.3   9:27.79 RpcIO

产生上述情况的主要原因跟两个配置项有关系：cpu_quota_concurrency = 4 和 net_thread_count = 32。前者控制租户并发工作线程数，后者控制 OBServer 网络线程数，对于 16c 的进程，不用配置过大，在系统租户下，需要做如下调整：

alter system set cpu_quota_concurrency = 2 tenant = xxx;
alter system set net_thread_count = 6;

注意

随着业务并发量和单个请求的耗时不同，可调整 cpu_quota_concurrency 配置项的取值，取值设置为 2~4 即可。net_thread_count 配置项可自适应，为了极限性能，也可以尝试调整。

事务模型

依次打开 enable_perf_event、enable_sql_audit 两个配置项，统计压测期间，事务 plan_type 的信息：

MySQL [oceanbase]> select plan_type, count(1) from gv$ob_sql_audit where tenant_id=1002 group by plan_type;
+-----------+----------+
| plan_type | count(1) |
+-----------+----------+
|         0 |   115557 |
|         1 |  1463222 |
|         2 |  1286613 |
|         3 |     8647 |
+-----------+----------+
4 rows in set (0.93 sec)

上述结果中，plan_type = 0 一般表示事务 commit / rollback 语句；plan_type = 1 表示 Local 计划；plan_type = 2 表示 Remote 计划，这种情况需要特别注意，如果 OBProxy 路由正确，这种计划出现的概率比较低。当前该环境，需要分析 Remote 计划多的原因。经过排查发现，OBProxy 的路由规则被误改成 Random，每次请求都会路由到跟上一次不同的 svr_ip 上，从而产生大量的远程计划和分布式事务。

MySQL [oceanbase]> show proxyconfig like '%server_routing_mode%';
+---------------------+--------+---------------------------------------------------------------------------+-------------+---------------+
| name                | value  | info                                                                      | need_reboot | visible_level |
+---------------------+--------+---------------------------------------------------------------------------+-------------+---------------+
| server_routing_mode | random | server routing mode: 1.oceanbase(default mode); 2.random; 3.mock; 4.mysql | false       | SYS           |
+---------------------+--------+---------------------------------------------------------------------------+-------------+---------------+
1 row in set (0.00 sec)

上述场景作为一个案例进行分析，实际生成环境需要改成 Oceanbase 路由规则，即：请求默认路由到分区 Leader 所在的位置。

慢 SQL 分析

借助于 OCP 等工具，可以清晰看到压测一段时间范围内，慢 SQL 的具体信息，接下来对每个慢 SQL 需要各个击破。本压测环境，由于没有部署 OCP 工具，可以通过查询内部表来判断：

MySQL [oceanbase]> select plan_id, avg_exe_usec,hit_count,substr(query_sql,1,100) from gv$ob_plan_cache_plan_stat where tenant_id=1002 order by hit_count desc, avg_exe_usec desc limit 30;
+---------+--------------+-----------+------------------------------------------------------------------------------------------------------+
| plan_id | avg_exe_usec | hit_count | substr(query_sql,1,100)                                                                              |
+---------+--------------+-----------+------------------------------------------------------------------------------------------------------+
|   10171 |          135 |  27526934 | SELECT i_price, i_name, i_data     FROM bmsql_item     WHERE i_id = ?                                |
|   11699 |          345 |  25744804 | SELECT s_quantity, s_data,        s_dist_01, s_dist_02, s_dist_03, s_dist_04,        s_dist_05, s_di |
|   15675 |          450 |  18047508 | SELECT i_price, i_name, i_data     FROM bmsql_item     WHERE i_id = 2118                             |
|   15710 |          719 |  17907478 | SELECT s_quantity, s_data,        s_dist_01, s_dist_02, s_dist_03, s_dist_04,        s_dist_05, s_di |
|   10196 |         7853 |  17408860 | SELECT i_price, i_name, i_data     FROM bmsql_item     WHERE i_id = 4192                             |
|   10259 |         9069 |  17269469 | SELECT s_quantity, s_data,        s_dist_01, s_dist_02, s_dist_03, s_dist_04,        s_dist_05, s_di |
|   10246 |          265 |  10097426 | SELECT s_quantity, s_data,        s_dist_01, s_dist_02, s_dist_03, s_dist_04,        s_dist_05, s_di |
|   11620 |        26004 |   9479423 | SELECT i_price, i_name, i_data     FROM bmsql_item     WHERE i_id = 12112                            |
|   11685 |        26288 |   9404024 | SELECT s_quantity, s_data,        s_dist_01, s_dist_02, s_dist_03, s_dist_04,        s_dist_05, s_di |
|   15732 |          243 |   8829277 | SELECT s_quantity, s_data,        s_dist_01, s_dist_02, s_dist_03, s_dist_04,        s_dist_05, s_di |
|   11524 |          337 |   3613895 | SELECT d_tax, d_next_o_id     FROM bmsql_district     WHERE d_w_id = ? AND d_id = ?     FOR UPDATE   |
|   11521 |          385 |   2986335 | UPDATE bmsql_district     SET d_ytd = d_ytd + ?     WHERE d_w_id = ? AND d_id = ?                    |
|   11597 |          337 |   2793965 | DELETE FROM bmsql_new_order     WHERE no_w_id = ? AND no_d_id = ? AND no_o_id = ?                    |
|   11578 |          363 |   2772915 | UPDATE bmsql_warehouse     SET w_ytd = w_ytd + ?     WHERE w_id = ?                                  |
|   11575 |          426 |   2598535 | SELECT c_discount, c_last, c_credit, w_tax     FROM bmsql_customer     JOIN bmsql_warehouse ON (w_id |
|   11638 |          315 |   2598521 | UPDATE bmsql_district     SET d_next_o_id = d_next_o_id + ?     WHERE d_w_id = ? AND d_id = ?        |
|   11665 |          355 |   2598507 | INSERT INTO bmsql_oorder (    o_id, o_d_id, o_w_id, o_c_id, o_entry_d,     o_ol_cnt, o_all_local) VA |
|   11676 |          291 |   2598498 | INSERT INTO bmsql_new_order (    no_o_id, no_d_id, no_w_id) VALUES (?, ?, ?)                         |
|   11555 |          259 |   2487017 | SELECT d_name, d_street_1, d_street_2, d_city,        d_state, d_zip     FROM bmsql_district     WHE |
|   11596 |          252 |   2486968 | SELECT w_name, w_street_1, w_street_2, w_city,        w_state, w_zip     FROM bmsql_warehouse     WH |
|   11694 |          313 |   2486932 | INSERT INTO bmsql_history (    h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id,     h_date, h_amount, h_d |
|   11556 |         2100 |   2366600 | SELECT no_o_id     FROM bmsql_new_order     WHERE no_w_id = ? AND no_d_id = ?     ORDER BY no_o_id A |
|   11728 |          979 |   2334138 | UPDATE bmsql_stock     SET s_quantity = 79, s_ytd = s_ytd + 10,         s_order_cnt = s_order_cnt +  |
|   11644 |          487 |   2321619 | UPDATE bmsql_oorder     SET o_carrier_id = ?     WHERE o_w_id = ? AND o_d_id = ? AND o_id = ?        |
|   11668 |          250 |   2321608 | SELECT o_c_id     FROM bmsql_oorder     WHERE o_w_id = ? AND o_d_id = ? AND o_id = ?                 |
|   11689 |          833 |   2321601 | UPDATE bmsql_order_line     SET ol_delivery_d = ?     WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id  |
|   11709 |          390 |   2321589 | SELECT sum(ol_amount) AS sum_ol_amount     FROM bmsql_order_line     WHERE ol_w_id = ? AND ol_d_id = |
|   11621 |          350 |   2173603 | SELECT c_first, c_middle, c_last, c_street_1, c_street_2,        c_city, c_state, c_zip, c_phone, c_ |
|   11716 |          396 |   2087872 | UPDATE bmsql_customer     SET c_balance = c_balance + ?,         c_delivery_cnt = c_delivery_cnt + ? |
|   15557 |          791 |   1805761 | SELECT c_discount, c_last, c_credit, w_tax     FROM bmsql_customer     JOIN bmsql_warehouse ON (w_id |
+---------+--------------+-----------+------------------------------------------------------------------------------------------------------+
30 rows in set (0.01 sec)

根据上述统计的慢 SQL，分别进行分析：


MySQL [oceanbase]> select * from gv$ob_plan_cache_plan_explain where plan_id=15732 and svr_ip='100.xxx.xxx.xxx' and svr_port=40000 and tenant_id=1002\G;
*************************** 1. row ***************************
   TENANT_ID: 1002
          IP: 100.x.x.x      
        PORT: 40000
     PLAN_ID: 15732
  PLAN_DEPTH: 0
PLAN_LINE_ID: 0
    OPERATOR: PHY_TABLE_SCAN
        NAME: bmsql_stock
        ROWS: 1
        COST: 45
    PROPERTY: table_rows:100000, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, avaiable_index_name[bmsql_stock]
1 row in set (0.00 sec)

注意

上述虚拟表的查询，需要带上四个维度的参数：tenant_id 、svr_ip、svr_port、plan_id。