All Products
Document Center

SQL tunning overview

Last Updated: Jan 10, 2020

SQL tuning is a process of analyzing various factors that affect SQL performance and adjusting SQL execution strategies to meet expected goals. The factors include SQL execution plans, monitoring information, system configurations, and workloads.

SQL tuning types

Statement-specific SQL tuning

This type of SQL tuning focuses on the execution of a single SQL statement that is used for debugging. The main purposes include reducing the response time and mitigating the resources consumed during queries. Common tuning methods include modifying access paths, adjusting execution orders, and rewrite logic.

System-level SQL tuning

This type of SQL tuning is more complicated than statement-specific SQL tuning. You need to analyze workloads of the current system and the execution plans of multiple SQL statements to troubleshoot performance issues. The main purposes include improving the system throughput and optimizing the resource usage. System-level SQL tuning focuses on global factors such as frequently queried rows and the cache hit ratio.

Causes of SQL performance issues

Improper SQL statements

The syntax in which SQL statements are written determines SQL performance. When writing queries, you must follow the development specification of OceanBase.

Defective cost model

Execution plans are generated based on the built-in cost model of OceanBase. The cost model determines the inherent logic of the server. Therefore, if inappropriate execution plans are automatically selected due to the cost model, you can only bind appropriate execution plans for queries.

Inaccurate statistical information

SQL tuning depends on the accuracy of data statistics. By default, the OceanBase optimizer collects statistical information during data merging. If a large amount of data is modified, the statistics may be inaccurate. You can merge data on a daily basis to update statistical information.

The optimizer also collects sample data from the storage layer according to the query conditions for subsequent optimization. The OceanBase optimizer can only collect sample data from the local data store. If data partitions reside on remote nodes, you can only use statistical information for cost estimation, which may cause certain deviations.

Invalid database design

SQL performance depends to a large extent on the physical design of the database, including the schema information of the objects that are accessed. For example, for a secondary index, if the required projected column is not included in the index column, queries are directed back to the primary table and the cost of queries increases significantly. In this case, you can create a covering index by adding the projected column to the index column to avoid the additional lookup to the table.

High system workloads

System workloads affect the throughput of the system and the response time of a single SQL statement. OceanBase uses a queue-based model to handle SQL requests. If all available threads are occupied, new requests need to be queued until a thread completes the current request. In the (g)v$sql_audit view, you can check the waiting time of a request in the queue.

Invalid routing of requests from the client

The OBProxy is used to route SQL queries to an appropriate node. If you do not specify the weak read consistency for queries, the proxy automatically forwards a request to the primary node of the table or partition that is searched to avoid unnecessary forwarding. If you specify the weak read consistency for queries, the proxy forwards the request to an appropriate node based on the specified rules.

Due to the loose coupling between the proxy and nodes, the data cached on the proxy may not be updated in a timely manner and invalid routing may occur. Invalid routing may occur when the primary node switches in the following scenarios:

  • Networks are unstable.
  • Nodes go offline or are merged.
  • Load balancing is performed.

Troubleshoot the preceding problems if you find a large number of remote queries in the SQL audit view or plan cache. Routing feedback logic exists between the client and server. When an error occurs, the client automatically refreshes the data distribution information and adopts a valid routing strategy.

Differences of SQL tuning between OceanBase and traditional database systems

SQL tuning is one of the most common issues for databases, and a large amount of related documents, manuals, and books are available. As a relational database system, OceanBase has the similar SQL tuning methods with traditional database systems. However, OceanBase has SQL tuning methods that are different from traditional database systems due to its own features.

LSM-tree storage engine

The OceanBase storage engine uses a two-layer LSM-tree architecture. The data is divided into static data (SSTable) and dynamic data (MemTable). For write requests, updated data is written to the MemTable in memory based on the B-tree structure, and are regularly merged into the SSTable on a disk. For read requests, data in the MemTable and SSTable are queried and merged into final rows. OceanBase differs significantly from traditional database systems in the cost model of access paths. For example, the query efficiency when the MemTable has no data (all data is stored in the SSTable) is much higher than that when the MemTable has data. Therefore, when data is merged into the SSTable, the query efficiency improves significantly.

Another example is buffer tables. Buffer tables refer to the tables that store temporary data. Data is written, modified, and deleted in buffer tables within minutes or hours. When you perform DML operations in OceanBase, data is not directly deleted from buffer tables, but marked in the cache and then deleted. Accessing the deletion marker takes time even if a row is deleted. To solve the problem, OceanBase provides the row purge feature to determine a data range for deletion markers in the B-tree structure. The row purge process is asynchronous. Therefore, if you immediately access the data range for deletion markers after a large amount of data is deleted, the SQL execution may take a long time to access the node.

Distributed architecture

OceanBase features a distributed shared-nothing architecture. For traditional database systems that adopt a shared-disk architecture, nodes are not physically separated for execution plans and all disks are accessible from all cluster nodes. However, OceanBase stores different data on different nodes. SQL execution plans are generated based on the physical distribution of data, and have different performance. For example, if two tables that are distributed on different nodes are joined, data is transferred across nodes and the execution plan becomes a distributed execution plan. The response time in this scenario increases compared with that when two tables are distributed on the same node.

In another scenario, you can switch the primary node in a cluster. An on-premises execution plan may become a remote or distributed execution plan so that the response time increases. This problem can be typically caused by server merges. After the primary node is switched, invalid routing may occur because the physical distribution information of data is not updated on the client.