Diagnosis and Tuning with OceanBase: sql_audit Performance View Analysis

obdiag is a CLI diagnostic tool designed for OceanBase Database. It scans and collects information such as logs, SQL audit records, and process stack information of OceanBase. You may deploy your OceanBase cluster by using OceanBase Cloud Platform (OCP) or OceanBase Deployer (OBD), or manually deploy it based on the OceanBase documentation. Regardless of the deployment mode, you can use obdiag to collect diagnostic information with a few clicks. obdiag has now been officially open-sourced.

The obdiag team has compiled relevant experiences regarding OceanBase diagnosis and tuning and has commenced releasing a series of tutorial articles. This article will dive into the performance view analysis, and give a detailed introduction on the functions and usage instructions of sql_audit.

The SQL audit view: sql_audit

The SQL audit view (gv$sql_audit) is a very useful feature in OceanBase for SQL performance diagnosis. In OceanBase 4.0.0.0 and later versions, it is also known as gv$ob_sql_audit. It can help developers and operators to troubleshoot any SQL statement that has been run in OceanBase more conveniently, regardless of whether the SQL statement was executed successfully. It records detailed information such as client and server IP ports, SQL statements, execution time, execution node, execution plan ID, session ID, execution time, wait time, total time, queue time, related block read information, and execution error messages.

The sql_audit is a view based on the virtual table _all_virtual_sql_audit. Corresponding data is stored in a configurable memory space, capable of recording and displaying the source, execution status, and statistical information of each SQL request. As the memory space is limited, when it reaches a certain limit, it triggers an elimination mechanism.

The sql_audit checks background tasks every 1 second and decides whether to trigger the elimination based on the following criteria:

●  The maximum memory usage limit for sql_audit is avail_mem_limit = min (available memory on OBServer * 10%, sql_audit_memory_limit).

●  When the avail_mem_limit is within the range of [64M, 100M], elimination is triggered when the memory usage reaches avail_mem_limit - 20M.

●  When the avail_mem_limit is within the range of [100M, 5G], elimination is triggered when the memory usage reaches avail_mem_limit * 0.8.

●  When the avail_mem_limit is within the range of [5G, +∞), elimination is triggered when the memory usage reaches avail_mem_limit - 1G.

●  Elimination is also triggered when the number of sql_audit records exceeds 9 million.

If the elimination is triggered due to reaching the maximum record limit, the elimination will be stopped when the number of records is reduced to 8 million. If the elimination is triggered due to reaching the memory limit, sql_audit will decide whether to stop the elimination based on the following criteria:

●  When the avail_mem_limit is within the range of [64M, 100M], elimination will be stopped when the memory usage is reduced to avail_mem_limit - 40M.

●  When the avail_mem_limit is within the range of [100M, 5G], elimination will be stopped when the memory usage is reduced to avail_mem_limit * 0.6.

●  When the avail_mem_limit is within the range of [5G, +∞], elimination will be stopped when the memory usage is reduced to avail_mem_limit - 2G.

Fields of the sql_audit View

Diagnostic cases based on sql_audit

Monitor the top SQL queries in the last 100 seconds.

Statement:

-- For OceanBase 4.0.0.0 and later，replace tenant_name with actual tenant name 
select /*+read_consistency(weak),query_timeout(100000000)*/ SQL_ID,count(1),avg(ELAPSED_TIME),avg(EXECUTE_TIME),avg(QUEUE_TIME),avg(AFFECTED_ROWS),avg(GET_PLAN_TIME) 
from gv$ob_sql_audit  
where time_to_usec(now(6))-request_time <1000000000 
and tenant_name='test_tenant' 
group by SQL_ID order by avg(ELAPSED_TIME)*count(1) desc limit 20;

-- For versions earlier than OceanBase 4.0.0.0，replace tenant_name with actual tenant name 
select /*+read_consistency(weak),query_timeout(100000000)*/ SQL_ID,count(1),avg(ELAPSED_TIME),avg(EXECUTE_TIME),avg(QUEUE_TIME),avg(AFFECTED_ROWS),avg(GET_PLAN_TIME) 
from gv$sql_audit  
where time_to_usec(now(6))-request_time <1000000000 and tenant_name='test_tenant'  
group by SQL_ID order by avg(ELAPSED_TIME)*count(1) desc limit 20 ;

Expected value: Check if the overall SQL time duration, cpu_time, physical and logical reads are reasonable. Generally, single row insert or query with primary key should be within 500us.

Suggestion: Compare the SQL with the table structure to confirm if the execution plan is reasonable and the time duration is normal.

Check if the SQL request traffic in the cluster is evenly distributed.

Approach: You can start by retrieving all the SQL queries in the database within a specific time period, aggregating them at the server level, and then calculating the queries per second (QPS) for each server within that time frame.

Statement:

-- For OceanBase 4.0.0.0 and later，replace t1.tenant_id with actual tenant id
select t2.zone, t1.svr_ip,  count(*) as QPS
from oceanbase.gv$ob_sql_audit t1, oceanbase.__all_server t2
where t1.svr_ip = t2.svr_ip and t1.tenant_id = 1001
and IS_EXECUTOR_RPC = 0 and request_time > (time_to_usec(now()) - 1000000)
and request_time < time_to_usec(now())
group by t1.svr_ip   order by QPS;

-- For versions earlier than OceanBase 4.0.0.0，replace t1.tenant_id with actual tenant id
select t2.zone, t1.svr_ip,  count(*) as QPS
from oceanbase.gv$sql_audit t1, oceanbase.__all_server t2
where t1.svr_ip = t2.svr_ip and t1.tenant_id = 1001
and IS_EXECUTOR_RPC = 0 and request_time > (time_to_usec(now()) - 1000000)
and request_time < time_to_usec(now())
group by t1.svr_ip   order by QPS;

Analyze the SQL with the Top-N number of requests within a specific time frame.

Approach: Retrieve all the SQL queries in the database within the specified time period, aggregate them at the sql_id level, and then calculate the QPS for each sql_id within that time frame. Finally, get the TOP-N values.

Statement:

-- For OceanBase 4.0.0.0 and later，replace tenant_id with actual tenant id
select SQL_ID, count(*) as QPS, avg(t1.elapsed_time) RT
from oceanbase.gv$ob_sql_audit t1
where   tenant_id = 1001       and IS_EXECUTOR_RPC = 0
and request_time > (time_to_usec(now()) - 10000000)
and request_time < time_to_usec(now())
group by t1.sql_id order by QPS desc limit 10;

-- For versions earlier than OceanBase 4.0.0.0，replace tenant_id with actual tenant id
select SQL_ID, count(*) as QPS, avg(t1.elapsed_time) RT
from oceanbase.gv$sql_audit t1
where   tenant_id = 1001       and IS_EXECUTOR_RPC = 0
and request_time > (time_to_usec(now()) - 10000000)
and request_time < time_to_usec(now())
group by t1.sql_id order by QPS desc limit 10;

Identify the SQL with the highest CPU consumption.

Approach: The CPU usage time can be calculated as elapsed_time - queue_time, as the queuing process does not consume CPU. This can be particularly useful for identifying SQL queries that contribute to high CPU usage.

Statement:

-- For OceanBase 4.0.0.0 and later，replace tenant_id with actual tenant id
select sql_id, substr(query_sql, 1, 20) as query_sql,       
    sum(elapsed_time - queue_time) sum_t, count(*) cnt,       
    avg(get_plan_time), avg(execute_time)    
from oceanbase.gv$ob_sql_audit     
where   tenant_id = 1001             
    and request_time > (time_to_usec(now()) - 10000000)            
    and request_time < time_to_usec(now())
group by sql_id order by sum_t desc   limit 10;

-- For versions earlier than OceanBase 4.0.0.0，replace tenant_id with actual tenant id
select sql_id, substr(query_sql, 1, 20) as query_sql,       
    sum(elapsed_time - queue_time) sum_t, count(*) cnt,       
    avg(get_plan_time), avg(execute_time)    
from oceanbase.gv$sql_audit     
where   tenant_id = 1001             
    and request_time > (time_to_usec(now()) - 10000000)            
    and request_time < time_to_usec(now())
group by sql_id order by sum_t desc   limit 10;

Check if there is a large number of unreasonable remote execution.

Approach: The "plan_type" field in sql_audit indicates the type of execution plan for the SQL query:

●  plan_type=1: Local execution plan, which offers the best performance.

●  plan_type=2: Remote execution plan.

●  plan_type=3: Distributed execution plan, which includes both local and remote execution plans.

In general, when there is a significant amount of remote execution, it may lead to leader switching or inaccurate routing of the the proxy client.

Statement:

-- For OceanBase 4.0.0.0 and later，replace tenant_id with actual tenant id
select count(*), plan_type    
from oceanbase.gv$ob_sql_audit     
where tenant_id = 1001                
    and IS_EXECUTOR_RPC = 0                
    and request_time > (time_to_usec(now()) - 10000000)               
    and request_time < time_to_usec(now()) 
group by plan_type ;

-- For versions earlier than OceanBase 4.0.0.0，replace tenant_id with actual tenant id
select count(*), plan_type    
from oceanbase.gv$sql_audit     
where tenant_id = 1001                
    and IS_EXECUTOR_RPC = 0                
    and request_time > (time_to_usec(now()) - 10000000)               
    and request_time < time_to_usec(now()) 
group by plan_type ;

Query SQL statements that perform full table scans.

Approach: The table_scan field in sql_audit identifies whether a statement performs a full table scan. Further analysis can be conducted to determine if adding indexes can prevent full table scans for these SQL statements.

Statement:

-- For OceanBase 4.0.0.0 and later，replace tenant_id with actual tenant id
select query_sql 
from oceanbase.gv$ob_sql_audit 
where table_scan = 1 and tenant_id = 1001 
group by sql_id;

-- For versions earlier than OceanBase 4.0.0.0，replace tenant_id with actual tenant id
select query_sql 
from oceanbase.gv$sql_audit 
where table_scan = 1 and tenant_id = 1001 
group by sql_id;

Analyze SQL queries that cause an abrupt RT jitter.

Approach: If RT jitter occurs online, but the RT is not consistently high, consider disabling sql_audit immediately after the jitter occurs (alter system set ob_enable_sql_audit = 0) to ensure the presence of the target SQL in sql_audit. Then identify the abnormal SQL by analyzing the SQL queries with the Top-N number of requests.

Upon identifying the RT abnormal requests in sql_audit, the following aspects can be further analyzed:

●  Check the retry count (RETRY_CNT). If the count is high, there may be locking conflicts or leader switches.

●  Check if the queue time value is too high (QUEUE_TIME).

●  Check the time taken to acquire the execution plan (GET_PLAN_TIME). If the time is long, usually IS_HIT_PLAN = 0, indicating a plan cache miss.

●  Check the execution time (EXECUTE_TIME). If the value is abnormally high, check if there are long wait events or if the number of rows read is high using SSSTORE_READ_ROW_COUNT and MEMSTORE_READ_ROW_COUNT.