Background
This topic describes typical performance issues that may occur in the stress testing of OceanBase Database and provides corresponding solutions to them. Performance issues vary by the actual testing and production environments. We recommend that you analyze performance issues based on the actual environment by taking various optimization measures after handling the typical issues listed in this topic.
Based on an actual offline BMSQL stress testing environment, this topic describes the performance tuning process step by step based on end-to-end analysis. The process involves the following steps:
Deploy the environment.
Check the external environment, including the network, disks, and NIC soft interrupts.
Allocate and deploy resources.
Configure the system.
Check the transaction model.
Analyze slow SQL queries.
Symptom
--The performance is not as expected from the perspective of the client.
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
--The observer process uses most of the CPU resources.
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
Deploy the environment
ODP
Deploy a single ODP. The NIC bandwidth is 2000 Mbit/s. The IP address is 100.88.105.196.
OBServer
Evenly distribute three OBServer nodes in three zones, one for each zone. Deploy the leaders across these OBServer nodes. Run three observer processes in a 96c environment, and each process is bound to 16 physical cores to simulate the performance of a small 16-core server. The IP address of the physical server is xx.xx.xx.197.
Physical server
$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
Troubleshooting
Check the external environment
Network latency between the ODP and the OBServer nodes
$ping xx.xx.xx.196
PING xx.xx.xx.196 (xx.xx.xx.196) 56(84) bytes of data.
64 bytes from xx.xx.xx.196: icmp_seq=1 ttl=64 time=1.07 ms
64 bytes from xx.xx.xx.196: icmp_seq=2 ttl=64 time=1.06 ms
64 bytes from xx.xx.xx.196: icmp_seq=3 ttl=64 time=1.06 ms
64 bytes from xx.xx.xx.196: icmp_seq=4 ttl=64 time=1.06 ms
64 bytes from xx.xx.xx.196: icmp_seq=5 ttl=64 time=1.07 ms
64 bytes from xx.xx.xx.196: icmp_seq=6 ttl=64 time=1.07 ms
64 bytes from xx.xx.xx.196: icmp_seq=7 ttl=64 time=1.07 ms
According to the test results, the network communication delay between the ODP and the OBServer nodes is relatively large. Further analysis on TC-related configurations shows that a network delay of 1 ms is configured on 197, as shown in the following code:
$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
Note: The stress testing client may be subject to the CPU and network resource limits and become a bottleneck. Therefore, we need to check the environment.
Disk environment check
$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
The monitoring statistics of dm-0 shows a moderately high await value, which seems normal.
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
The monitoring statistics of dm-1 shows a moderately high await value, which seems normal.
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
NIC soft interrupts
Run the Top command and then run 1 to view the statistics of each CPU. The si value indicates the percentage of CPU time with NIC soft interrupts. Pay attention to the distribution on both the ODP and the OBServer nodes. The result returned for this environment is as follows:
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
As shown in the preceding figure, the NIC soft interrupt is bound to the first two physical cores where the process is running. However, for excellent performance, we can reduce the number of distributed cores to enhance CPU affinity, thus improving performance. For example, for a 16c environment, you can bind soft interrupts to four cores.
Allocate and deploy resources
Query tenant resource allocation
MySQL [oceanbase]> select max_cpu, min_cpu, max_memory/1024/1024/1024, min_memory/1024/1024/1024 from gv$unit where tenant_id=1002;
+---------+---------+---------------------------+---------------------------+
| max_cpu | min_cpu | max_memory/1024/1024/1024 | min_memory/1024/1024/1024 |
+---------+---------+---------------------------+---------------------------+
| 11 | 11 | 75.068677425384 | 75.068677425384 |
| 11 | 11 | 75.068677425384 | 75.068677425384 |
| 11 | 11 | 75.068677425384 | 75.068677425384 |
+---------+---------+---------------------------+---------------------------+
3 rows in set (0.01 sec)
In a 16-core environment, considering the CPUs allocated to the ODP and sys tenant, it is reasonable to allocate 11 CPUs to these tenants. The overall memory size of the tenants is 75 GB, which is also acceptable for small ones.
Check partition distribution
--Check the partition allocation of tenant 1002.
MySQL [oceanbase]> select svr_ip, svr_port, count(1) from __all_virtual_meta_table where tenant_id=1002 group by svr_port;
+----------------+----------+----------+
| svr_ip | svr_port | count(1) |
+----------------+----------+----------+
| xx.xx.xx.197 | 40000 | 130 |
| xx.xx.xx.197 | 40001 | 130 |
| xx.xx.xx.197 | 40002 | 130 |
+----------------+----------+----------+
3 rows in set (0.00 sec)
--Check the partition allocation for all tenants.
MySQL [oceanbase]> select svr_ip, svr_port, count(1) from __all_virtual_meta_table group by svr_port;
+----------------+----------+----------+
| svr_ip | svr_port | count(1) |
+----------------+----------+----------+
| xx.xx.xx.197 | 40000 | 135 |
| xx.xx.xx.197 | 40001 | 135 |
| xx.xx.xx.197 | 40002 | 135 |
+----------------+----------+----------+
3 rows in set (0.01 sec)
The allocation of the 1002 tenants is balanced, which is about 130 tenants on each server. In an initial view, the overall partition distribution of the three servers in the cluster are balanced.
Check leader distribution
MySQL [oceanbase]> select svr_ip, svr_port, count(1) from __all_virtual_meta_table where role=1 and tenant_id=1002 group by svr_port;
+----------------+----------+----------+
| svr_ip | svr_port | count(1) |
+----------------+----------+----------+
| xx.xx.xx.197 | 40000 | 43 |
| xx.xx.xx.197 | 40001 | 44 |
| xx.xx.xx.197 | 40002 | 43 |
+----------------+----------+----------+
3 rows in set (0.01 sec)
The leader distribution is largely balanced and is in line with the expectation.
Configure the system
During the stress testing, rpc_io of a single process and TNT_1002 occupy a large number of threads. The overhead of CPU context switching in this case should not be underestimated.
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
The issue is mainly caused by the configuration of the following parameters: cpu_quota_concurrency = 4 and net_thread_count = 32. The former specifies the concurrent worker threads for tenants, while the latter specifies the number of OBServer network thread. For 16c processes, do not set the values too large. You can run the following statements to modify the values:
alter system set cpu_quota_concurrency = 2;
alter system set net_thread_count = 6;
Note: You can adjust the value of cpu_quota_concurrency when the business concurrency and the time consumption for a single request change. We recommend that you set it to a value that ranges from 2 to 4. The net_thread_count parameter is adaptive. You can also adjust the value to achieve ultimate performance.
Check the transaction model
Enable the enable_perf_event and enable_sql_audit parameters to collect plan_type statistics of transactions during a stress test:
MySQL [oceanbase]> select plan_type, count(1) from gv$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)
In the preceding result, plan_type = 0 is usually a transaction commit/rollback statement; plan_type = 1 indicates a local plan; plan_type = 2 indicates a remote plan. However, if the ODP route is correct, remote plans occur at a relatively low probability. Therefore, we must analyze for the reason for the large number of remote plans in the current environment.
Upon troubleshooting, we find that the routing rule of the ODP was mistakenly changed to Random. In this case, each request was routed to a svr_ip different from that of the previous one, resulting in a large number of remote plans and distributed transactions.
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)
The preceding analysis and configuration is an example. In an actual production environment, use the Oceanbase routing rule, that is routing requests to the location of the partition leader by default.
Analyze slow SQL queries
Use tools such as OCP to view the information about slow SQL queries in the stress testing period. Then, you can troubleshoot each slow SQL query separately. Since OCP is not deployed in this stress testing environment, in this example, we check the internal tables to identify slow SQL queries:
MySQL [oceanbase]> select plan_id, avg_exe_usec,hit_count,substr(query_sql,1,100) from gv$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)
Analyze the preceding statistics on slow SQL queries one by one:
MySQL [oceanbase]> select * from gv$plan_cache_plan_explain where plan_id=15732 and ip='xx.xx.xx.197' and port=40000 and tenant_id=1002\G;
*************************** 1. row ***************************
TENANT_ID: 1002
IP: xx.xx.xx.197
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)
Note: To query the preceding virtual table, you must provide the parameters tenant_id, IP, Port, and Plan_id.