--- title: "案例分析 | OceanBase 文档中心" description: 案例分析 背景 本文从一个通用的角度，简单还原了排查 OceanBase 数据库在压测过程中可能遇到的、比较明显的性能问题以及解决方案。实际测试、生产环境遇到的性能问题复杂多样，在对本通用场景做过处理之后，还需要具体问题具体分析，优化手段不能一概而论，以偏概全。 本文基于线下实际的 BMSQL 压测环境，通过全链路的… image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.0.0 企业版 # 案例分析 更新时间：2026-05-22 08:55:14 ## 背景 本文从一个通用的角度，简单还原了排查 OceanBase 数据库在压测过程中可能遇到的、比较明显的性能问题以及解决方案。实际测试、生产环境遇到的性能问题复杂多样，在对本通用场景做过处理之后，还需要具体问题具体分析，优化手段不能一概而论，以偏概全。 本文基于线下实际的 BMSQL 压测环境，通过全链路的分析，逐步对性能进行调优。相关的过程分为以下几个步骤： 1. 环境部署。 2. 外部环境检查，包括：网络、磁盘、网卡软中断等。 3. 资源分配与部署。 4. 系统配置。 5. 事务模型。 6. 慢 SQL分析。 ## 问题现象 ```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。 ### 物理机 ```sql $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 之间的网络延迟 ```sql $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 延迟，具体如下： ```sql $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 资源、网络等限制，从而成为瓶颈。因此我们还需要关注对环境的检查。 #### 磁盘环境检查 ```sql $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 数据不是很高，显示是正常的。 ```sql 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 数据不是很高，显示是正常的。 ```sql 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 两侧均需要关注打散情况，本环境的情况如下： ```sql 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 个左右。 ### 资源分配与部署 #### 查看租户资源分配 ```sql 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，对于小租户而言，也是可以的。 #### 分区分布 ```sql --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 分布 ```sql 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 上下文切换带来的开销不容小觑，具体如下： ```sql 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 的进程，不用配置过大，在系统租户下，需要做如下调整： ```sql 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 的信息： ```sql 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 上，从而产生大量的远程计划和分布式事务。 ```sql 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 工具，可以通过查询内部表来判断： ```sql 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，分别进行分析： ```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`。 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线