Key Techniques and Methods for OceanBase Database Diagnosis and Tuning

This article is excerpted from the e-book A Case Study of OceanBase Community Edition in Pan-Internet Scenarios. Click the link to get the full version.

Introduction

In the field of distributed databases, OceanBase—with its natively distributed architecture and financial-grade high availability—has become a core infrastructure for ultra-large-scale data processing. However, the complexity of a distributed architecture also brings diagnosis and tuning challenges. Unlike traditional single-machine databases, OceanBase faults may involve complex factors such as multi-node coordination, network latency, and uneven resource allocation. This article focuses on a systematic methodology for diagnosis and tuning, aiming to help developers build a “data-driven, tool-empowered” diagnosis and tuning system through structured processes and key techniques.

1. OceanBase Fault Classification and Root Causes

OceanBase database faults can be broadly divided into two major categories: SQL faults and non-SQL faults.

(1) SQL Faults and Their Causes

SQL faults mainly involve operations directly related to SQL statements, such as database queries and transaction processing. Such faults typically lead to longer query response times, data inconsistency, or transaction failures. Below are several common SQL faults and their causes.

1. Poor query performance.

This may be due to poorly written SQL statements—such as missing necessary indexes, using inefficient join methods (e.g., nested-loop joins instead of hash joins), or performing unnecessary full-table scans on large data volumes. It may also be that the optimizer produced an unreasonable execution plan—for example, when statistics are stale or not updated, or statistics are not re-collected after the data distribution changes.

2. Lock contention.

When multiple transactions try to access or modify the same data simultaneously, lock contention may occur. If the transaction isolation level is set improperly, it may cause locks to be held for a long time, affecting the execution of other transactions and degrading overall system performance.

3. Transaction processing errors.

These include transactions failing to commit or roll back correctly, deadlocks, and similar issues. Such problems often stem from design flaws in the concurrency control mechanism or logic errors in the application—for example, failing to correctly restore transaction state under exceptional conditions.

4. SQL injection attacks.

Although this is not a technical fault, it is a security threat that exploits SQL syntax vulnerabilities for malicious operations. The lack of effective input validation and parameterized queries is the primary cause of such problems.

(2) Non-SQL Faults and Their Causes

Non-SQL faults cover issues related to hardware resources, network communication, configuration management, and other factors. Although these faults are not directly tied to the SQL statements themselves, they likewise affect the database’s overall performance and availability.

1. Hardware resource limits.

These include bottlenecks in CPU, memory, disk I/O, and so on. As the data volume grows, if hardware resources are insufficiently provisioned, problems such as CPU overload, memory overflow, or slow disk read/write speeds may occur.

2. Network issues.

Network latency, packet loss, or insufficient bandwidth all affect the data synchronization and communication efficiency between nodes in a distributed database. Especially in cross-data-center deployment scenarios, the quality of the network directly affects the stability of the OceanBase cluster.

3. Configuration errors.

Incorrect parameter settings—such as cache size, connection pool limits, and log levels—may lead to poor system performance or even faults. Reasonable configuration should be tuned based on the specific business requirements and workload characteristics.

4. Software compatibility and version issues.

Compatibility issues between different versions or the presence of known bugs may also trigger faults. Regular updates and patching are among the effective means of preventing such problems.

5. External dependencies and service interruptions.

The database depends on the normal operation of third-party services (such as message queues and external APIs). If these services have problems, they will also indirectly affect the functionality and performance of the OceanBase database.

2. The Diagnosis and Tuning Process

To diagnose and tune the OceanBase database effectively, we recommend following five steps: problem identification, data collection, problem localization, solution formulation, and validation. Each step is crucial, and together they form a closed-loop diagnosis and tuning process.

Step 1: Problem identification. This is the foundation of the entire process. At this stage, you need to clearly define the problems the database has, such as slowing response times or service unavailability. Clues can be gathered through multiple channels, such as user feedback, application logs, and the database’s own monitoring metrics. The key is to quickly determine the rough scope of the problem, so that subsequent in-depth investigation can be targeted.

Step 2: Data collection. The goal of this stage is to gather enough information from the database and its environment for analysis. Data sources are broad and include, but are not limited to, database performance views (such as v$session and v$sql), operating-system-level monitoring tools (such as top and iostat), and network traffic analysis tools. It is especially important to keep the original environment as unchanged as possible when collecting data, so as not to interfere with the authenticity of the results. At the same time, you should record the time points of data collection, which is particularly important for subsequent comparative analysis.

Step 3: Problem localization. Based on the data collected earlier, begin a detailed analysis of the problem. This stage may involve considerations at multiple levels, from the application layer to the database layer to the operating-system layer. For example, you can analyze the query execution plan to check the efficiency of SQL statements, use an AWR report to view the database’s overall performance trends, or trace specific operational behavior with the help of trace files. The goal of this stage is to precisely find the root cause of the problem and determine whether a specific SQL statement, a parameter configuration, or a hardware resource limit caused the performance bottleneck.

Step 4: Solution formulation. Once the root cause of the problem is determined, the next step is to design a corresponding solution. This may include optimizing SQL statements, adjusting database parameters, upgrading hardware, and so on. When formulating a plan, you should consider its feasibility, cost-benefit ratio, and impact on the existing business. Especially for an online production environment, any change requires careful evaluation, and when necessary, a simulated test should first be conducted in a test environment.

Step 5: Validation. All proposed solutions should have their effectiveness validated in a controlled environment before formal implementation. During validation, you should not only confirm whether the problem has been resolved, but also observe whether any new problems have arisen. Only after a new solution has been thoroughly validated and proven effective can it be applied to the production environment. In addition, it is recommended to regularly review the entire tuning process, summarize lessons learned, and continuously refine the tuning strategy.

3. Key Techniques and Methods for Diagnosis and Tuning

In the process of database diagnosis and tuning, mastering a few key techniques and methods is critical to accurately identifying problems and implementing effective optimization measures. Below are some commonly used tools and techniques that play important roles in different tuning scenarios.

(1) Internal Views

Table 1 lists the internal views commonly used in OceanBase and their purposes.

Table 1: Commonly used internal views and their purposes

View name	Purpose
gv$ob_plan_cache_plan_stat	View the SQL execution plan cache status, including information such as execution count and average execution time.
gv$ob_sql_audit	Records audit information for all SQL requests, used to analyze slow queries and performance bottlenecks.
gv$ob_memstore	Displays the current MemStore (in-memory storage) usage, helping you understand memory usage and optimize configuration.
gv$latch	Provides information about latches (lightweight locks), helping to identify lock contention issues.
gv$sysstat	Contains system-level statistics, such as the number of I/O operations and the number of transaction commits.
gv$session	Shows information about currently active sessions, which is helpful for monitoring concurrent connections and diagnosing blocking issues.
gv$partition	Provides information about partitioned tables, including the number, size, and distribution of partitions, for easier management and optimization.

These views provide insight into different aspects of the OceanBase database—from query performance to system health to resource management—and are important tools for daily monitoring and troubleshooting. For more detailed views, see the official documentation.

(2) Log Analysis

The most important part of log analysis is OceanBase’s own logs. The log files of the OceanBase database log module fall into three types—observer.log, election.log, and rootservice.log (see Table 2)—and by default print logs at the INFO level and above. Each type of log file automatically generates a WARNING log file with a .wf suffix (observer.log.wf, election.log.wf, rootservice.log.wf), which prints only logs at the WARN level and above.

Table 2: The three types of log files

Log name	Log path
Startup and runtime logs (observer.log, observer.log.wf)	Under the $work_dir/log directory of the OBServer server.
Election module logs (election.log, election.log.wf)	Under the $work_dir/log directory of the OBServer server.
RootService logs (rootservice.log, rootservice.log.wf)	Under the $work_dir/log directory of the OBServer server.

The OceanBase database divides logs into six levels, with their meanings shown in Table 3. The log levels in the table are arranged from highest to lowest.

Table 3: Database log levels

Log level	Meaning
ERROR	A serious error. Used to record system fault information that must be troubleshot; otherwise the system is unavailable.
USER_ERROR	An error caused by user input.
WARN	A warning. Used to record potential errors that may occur.
INFO	An informational message. Used to record the current state of system operation; this is normal information.
TRACE	Records event messages in finer detail than INFO.
DEBUG	Debug information. Used during debugging to understand the system’s running state in greater detail, including the names of currently called functions, parameters, variables, function return values, and so on.

(3) Surrounding Tools

In the process of database diagnosis and tuning, making good use of tools is key to working efficiently. Whether achieving comprehensive monitoring and management through OCP (OceanBase Cloud Platform), conducting in-depth security audits and diagnostic analysis with OAS (OceanBase Audit System), or quickly obtaining detailed diagnostic information with the help of obdiag, all of these can significantly improve the efficiency and accuracy of operations work. Reasonable use of professional tools not only helps us quickly locate problems and analyze causes in depth, but also guides us to take the most effective optimization measures, achieving more with less effort. Below is an overview of the tools commonly used for OceanBase diagnosis and tuning:

1. OCP (OceanBase Cloud Platform)

• Resource management: Provides full-lifecycle management of OceanBase resource objects such as clusters, tenants, hosts, and software packages, including management, installation, operations, performance monitoring, configuration, upgrades, and more.
• Monitoring and alerting: Global monitoring and alert settings, supporting real-time, accurate monitoring and alerting needs across different dimensions for all resource objects, with support for custom alerts to meet customized alerting needs.
• Backup and recovery: Supports full, incremental, and log backups at the cluster and tenant table levels, supports periodic backup tasks and multi-region backups, supports recovery to any point in time within the backup window, and supports backup and recovery across various cloud platform media.
• Autonomous service: In daily operations, it provides better manual or automated handling along the “discover–diagnose–locate–optimize/respond” chain, greatly reducing the cost of operating OceanBase for users.

2. OAS (OceanBase Autonomous Diagnosis Tool)

• Real-time diagnosis: Based on all-around monitoring data of the system and database, it achieves a closed-loop diagnostic chain from automatic detection of abnormal events, to problem identification, to root cause analysis, to optimization suggestions.
• SQL diagnosis: According to SQL execution characteristics, it classifies SQL into Suspicious SQL, TopSQL, SlowSQL, and ParallelSQL. When an anomaly occurs during SQL execution, OAS provides anomaly localization and root cause analysis for the SQL, along with optimization suggestions.
• Transaction diagnosis: OAS automatically identifies long transactions and dangling transactions that are blocked and affect the operation of the business system, and provides handling solutions.
• Session management: Session management provides the ability to view tenant sessions, view session statistics, and perform batch session management. It also provides the ability to detect deadlock events in real time and handle deadlocks.
• Capacity analysis: The capacity center lets you intuitively view the overall resource usage and usage trends of clusters, tenants, databases, tables, and even indexes; it alerts customers to capacity risks so they can scale out in time, and it predicts future storage space usage for reference.
• Optimization center: The optimization center provides TopSQL within a custom time range along with automatic checks of the corresponding SQL’s table structure and index structure, to see whether there are optimization points and to give optimization suggestions.

3. obdiag (Agile Diagnosis Tool)

• One-click cluster inspection: The obdiag check command helps inspect the status of an OceanBase database cluster, analyze the causes of existing or potential cluster anomalies, and provide operational recommendations.
• One-click analysis: The obdiag analyze command supports one-click analysis of OceanBase logs to find errors that have occurred, as well as one-click full-link diagnostic analysis, memory analysis, parameter analysis, and more.
• One-click information collection: The obdiag gather command helps collect diagnostic information related to the OceanBase database. It currently supports basic diagnostic information collection and one-click scenario-based diagnostic information collection.
• One-click root cause analysis: The obdiag rca command helps analyze diagnostic information related to the OceanBase database. It currently supports analyzing OceanBase anomaly scenarios to find the possible causes of a problem.

Conclusion

Diagnosis and tuning is an engineering practice that requires continuous iteration, and its core lies in establishing a closed-loop system of “monitor–analyze–optimize–prevent.” The value of database diagnosis and tuning lies not in how many faults that have already occurred are resolved, but in whether the system can be made to possess “self-healing capability” and “risk resilience”—when hardware failures, software defects, and human errors become “input parameters” of the system rather than “fatal threats”; when every crisis is turned into an opportunity to upgrade the defensive system, that is the essence of OceanBase’s diagnosis and tuning methodology. As The Art of War says: “The expert in battle wins with neither a reputation for wisdom nor credit for courage.” The finest diagnosis and tuning makes risk vanish before it ever takes shape.

Finally, we recommend the WeChat official account “Lao Ji’s Tech Talk,” run by Lao Ji, the OceanBase open-source lead. It continuously publishes a variety of technical content related to #Databases, #AI, and #Tech Architecture. Friends who are interested are welcome to follow!

“Lao Ji’s Tech Talk” not only hopes to keep bringing you valuable technical sharing, but also hopes to contribute to the open-source community together with everyone. If you appreciate the OceanBase open-source community, please light up a little star ✨! Every Star you give is the motivation behind our efforts.