DEV Community: Qing

Performance Tuning-Analyzing Hardware Bottlenecks-Network

Qing — Mon, 24 Apr 2023 09:33:46 +0000

You can run the sar or ifconfig command to check the network status on each node in openGauss and analyze whether performance bottlenecks caused by network faults occur.

Checking Network Status
Check the server network status using either of the following two methods:

· Log in to the server as user root and run the following commands to check the network connection:

· errors indicates the total number of error packets received.
· dropped indicates the number of packets that have reached the ring buffer but are discarded before being copied to the memory due to system faults, for example, insufficient memory.
· overruns indicates the number of packets that have been discarded from the ring buffer. They are discarded because the kernel is incapable of processing ring buffer (a.k.a. Driver Queue) transmission.
In the command output, if the values of the three parameters keep increasing, the network is overloaded or hardware (such as NICs and memory) faults exist.

· Run the sar command to check the network connection.

rxkB/s indicates the number of kilobytes of data received per second and txkB/s indicates the number of those sent per second.

In the command output, check whether the amount of data received and sent by any NIC has reached the upper limit.

After the check, press Ctrl+Z to exit.

Performance Tuning-Analyzing Hardware Bottlenecks-I/O

Qing — Mon, 24 Apr 2023 09:30:03 +0000

You can run the iostat or pidstat command, or use openGauss heath check tools to check the I/O usage and throughput on each node in openGauss and analyze whether performance bottleneck caused by I/O exists.

Checking I/O Usage
Use one of the following methods to check the server I/O:

· Run the iostat command to check the I/O usage. This command focuses on the I/O usage and the amount of data read and written on a single hard disk per second.

rMB/s indicates the number of megabytes of data read per second, wMB/s indicates that of data written per second, and %util indicates the disk usage.

· Run the pidstat command to check the I/O usage. This command focuses on the amount of data read and written on a single process per second.

kB_rd/s indicates the number of kilobytes of data read per second, and kB_wr/s indicates that of data written per second.

Run the gs_checkperf command as user omm to check the I/O usage in openGauss.

The I/O usage, number of reads and writes, and time when data is read and written are displayed.

You can also run the gs_checkperf –detail command to query performance details of each node.

Analyzing Performance Parameters

Check whether the disk usage exceeds 60%. Disk usage exceeding 60% is called high.

Perform the following operations to reduce I/O usage if the I/O usage keeps high:

2.1 Reduce the number of concurrent tasks.

2.2 Do VACUUM FULL for related tables.

Performance Tuning-Analyzing Hardware Bottlenecks-Memory（2）

Qing — Mon, 24 Apr 2023 09:24:54 +0000

max_prepared_transactions
Parameter description: Specifies the maximum number of transactions that can stay in the prepared state simultaneously. Increasing the value of this parameter causes openGauss to request more System V shared memory than the default configuration allows.

When openGauss is deployed as an HA system, set this parameter on standby servers to a value greater than or equal to that on primary servers. Otherwise, queries will fail on the standby servers.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 0 to 262143

Default value: 10

work_mem
Parameter description: Specifies the amount of memory to be used by internal sort operations and hash tables before they write data into temporary disk files. Sort operations are required for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.

In a complex query, several sort or hash operations may run in parallel; each operation will be allowed to use as much memory as this parameter specifies. If the memory is insufficient, data will be written into temporary files. In addition, several running sessions could be performing such operations concurrently. Therefore, the total memory used may be many times the value of work_mem.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 64 to 2147483647. The unit is KB.

Default value: 64 MB

query_mem
Parameter description: Specifies the memory used by a query.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: 0 or an integer greater than 32 MB. The default unit is KB.

Default value: 0

query_max_mem
Parameter description: Specifies the maximum memory that can be used by a query.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: 0 or an integer greater than 32 MB. The default unit is KB.

Default value: 0

maintenance_work_mem
Parameter description: Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. This parameter may affect the execution efficiency of VACUUM, VACUUM FULL, CLUSTER, and CREATE INDEX.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 1024 to *INT*MAX_. The unit is KB.

Default value: 16 MB

psort_work_mem
Parameter description: Specifies the memory capacity to be used for partial sorting in a column-store table before writing to temporary disk files. This parameter can be used for inserting tables having a partial cluster key or index, creating a table index, and deleting or updating a table.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 64 to 2147483647. The unit is KB.

Default value: 512 MB

max_loaded_cudesc
Parameter description: Specifies the number of cudesc cached in each column when a column-store table is scanned. Increasing the value will improve query performance and increase memory usage, particularly when there are many columns in the column-store table.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: 100 to 1073741823

Default value: 1024

max_stack_depth
Parameter description: Specifies the maximum safe depth of the openGauss execution stack. The safety margin is required because the stack depth is not checked in every routine in the server, but only in key potentially-recursive routines, such as expression evaluation.

This parameter is a SUSET parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 100 to *INT*MAX_. The unit is KB.

Default value:

· If the value of ulimit -s minus 640 KB is greater than or equal to 2 MB, the default value of this parameter is 2 MB.
· If the value of ulimit -s minus 640 KB is less than 2 MB, the default value of this parameter is the value of ulimit -s minus 640 KB.

cstore_buffers
Parameter description: Specifies the shared buffer size used in column-store tables.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 16384 to 1073741823. The unit is KB.

Default value: 1 GB

Setting suggestions:

Column-store tables use the shared buffer specified by cstore_buffers instead of that specified by shared_buffers. When column-store tables are mainly used, reduce the value of shared_buffers and increase that of cstore_buffers.

bulk_read_ring_size
Parameter description: Specifies the size of the ring buffer used by the operation when a large amount of data is queried (for example, during large table scanning).

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 256 to 2147483647. The unit is KB.

Default value: 16 MB
Parameter description: Specifies whether the operator memory can be released in advance.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

· on indicates that the operator memory can be released in advance.
· off indicates that the operator memory cannot be released in advance.
Default value: on

local_syscache_threshold
Parameter description: Specifies the size of system catalog cache in a session.

This parameter is a PGC_SIGHUP parameter. Set it based on instructions provided in Table 1.

If enable_global_plancache is enabled, local_syscache_threshold does not take effect when it is set to a value less than 16 MB to ensure that GPC takes effect. The minimum value is 16 MB.

If enable_global_syscache and enable_thread_pool are enabled, this parameter indicates the total cache size of the current thread and sessions bound to the current thread.

Value range: an integer ranging from 1 x 1024 to 512 x 1024. The unit is KB.

Default value: 256MB

Performance Tuning-Analyzing Hardware Bottlenecks-Memory（1）

Qing — Mon, 24 Apr 2023 09:02:34 +0000

This section describes memory parameters.

memorypool_enable
Parameter description: Specifies whether to enable a memory pool.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

· on indicates that the memory pool is enabled.
· off indicates that the memory pool is disabled.
Default value: off

memorypool_size
Parameter description: Specifies the memory pool size.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 128 x 1024 to *INT*MAX_/2. The unit is KB.

Default value: 512 MB

enable_memory_limit
Parameter description: Specifies whether to enable the logical memory management module.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

· on indicates that the logical memory management module is enabled.
· off indicates that the logical memory management module is disabled.
Default value: on

max_process_memory
Parameter description: Specifies the maximum physical memory of a database node.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 2 x 1024 x 1024 to *INT*MAX_. The unit is KB.

Default value: 12 GB

Setting suggestions:

The value on the database node is determined based on the physical memory of the system and the number of master database nodes deployed on a single node. The recommended calculation formula is as follows: (Physical memory – vm.min_free_kbytes) \ x 0.7 /(1 + Number of primary nodes) This parameter is used to prevent node OOM caused by memory usage increase, ensuring system reliability. vm.min_free_kbytes indicates the OS memory reserved for the kernel to receive and send data. Its value is at least 5% of the total memory. That is, max_process_memory = Physical memory x 0.665 / (1 + Number of primary nodes)

enable_memory_context_control
Parameter description: Specifies whether to enable the function of checking whether the number of memory contexts exceeds the specified limit. This parameter applies only to the DEBUG version.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

· on indicates that the function of checking the number of memory contexts is enabled.
· off indicates that the function of checking the number of memory contexts is disabled.
Default value: off

uncontrolled_memory_context
Parameter description: Specifies which memory texts will not be checked when the enable_memory_context_control parameter is set to on. This parameter applies only to the DEBUG version.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

During the query, the title meaning string “MemoryContext white list:” is added to the beginning of the parameter value.

Value range: a string

Default value: empty

shared_buffers
Parameter description: Specifies the size of shared memory used by openGauss. Increasing the value of this parameter causes openGauss to request more System V shared memory than the default configuration allows.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 16 to 1073741823. The unit is 8 KB.

The value of shared_buffers must be an integer multiple of BLCKSZ. Currently, BLCKSZ is set to 8 KB. That is, the value of shared_buffers must be an integer multiple of 8 KB. The minimum value changes according to BLCKSZ.

Default value: 8 MB

Setting suggestions:

Set shared_buffers to a value less than 40% of the memory. Set it to a large value for row-store tables and a small value for column-store tables. For column-store tables: shared_buffers = (Memory of a single server/Number of database nodes on the server) x 0.4 x 0.25

If shared_buffers is set to a larger value, increase the value of checkpoint_segments because a longer period of time is required to write a large amount of new or changed data.

segment_buffers
Parameter description: Specifies the memory size of an openGauss segment-paged metadata page.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 16 to 1073741823. The unit is 8 KB.

The value of segment_buffers_buffers** must be an integer multiple of BLCKSZ. Currently, BLCKSZ is set to 8 KB. That is, the value of segment_buffers must be an integer multiple of 8 KB. The minimum value changes according to the value of BLCKSZ.

Default value: 8 MB

Setting suggestions:

segment_buffers is used to cache the content of segment-paged headers, which is key metadata information. To improve performance, it is recommended that the segment headers of common tables be cached in the buffer and not be replaced. You are advised to set this parameter based on the following formula: Number of tables (including indexes and toast tables) x Number of partitions x 3 + 128. This is because each table (partition) has some extra metadata segments. Generally, a table has three segments. At last, + 128 is added because segment- and page-based tablespace management requires a certain number of buffers.

If this parameter is set to a small value, it takes a long time to create a segment-paged table for the first time. Therefore, you are advised to set this parameter to the recommended value.

bulk_write_ring_size
Parameter description: Specifies the size of the ring buffer used by the operation when a large amount of data is written (for example, the copy operation).

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 16384 to 2147483647. The unit is KB.

Default value: 2 GB

Setting suggestions: Increase the value of this parameter on database nodes if a huge amount of data will be imported.

standby_shared_buffers_fraction
Parameter description: Specifies the shared_buffers proportion used on the server where a standby instance is deployed.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Value range: a double-precision floating-point number ranging from 0.1 to 1.0

Default value: 0.3

temp_buffers
Parameter description: Specifies the maximum size of local temporary buffers used by a database session.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

temp_buffers can be modified only before the first use of temporary tables within each session. Subsequent attempts to change the value of this parameter will not take effect on that session.

A session allocates temporary buffers based on the value of temp_buffers. If a large value is set in a session that does not require many temporary buffers, only the overhead of one buffer descriptor is added. If a buffer is used, additional 8192 bytes will be consumed for it.

Value range: an integer ranging from 100 to 1073741823. The unit is 8 KB.

Default value: 1 MB

Performance Tuning-Analyzing Hardware Bottlenecks-CPU

Qing — Mon, 24 Apr 2023 08:02:50 +0000

You can run the top command to check the CPU usage of each node in openGauss and analyze whether performance bottleneck caused by heavy CPU load exists. The top command is used to monitor the Linux OS status. It is a common performance analysis tool and can display the resource usage of each process in the system in real time.

Description

· -d: number of seconds, indicating the interval for updating the page displayed by running the top command. The default value is 5 seconds.
· -b: executes the top command in batches.
· -n: This parameter is used together with -b to indicate the number of times that the top command is executed.
· -p: specifies a PID for observation.

Checking CPU Usage
You can query the CPU usage of the server in the following ways:

On each storage node, run the top command to check the CPU usage. Then, press 1 to view the usage of each CPU core.

In the command output, focus on the CPU usage occupied by each process.

us indicates the CPU percentage occupied by the user space, sy indicates the CPU percentage occupied by the kernel space, and id indicates the idle CPU percentage. If id is less than 10%, the CPU load is high. In this case, you can reduce the CPU load by reducing the number of tasks on nodes.

Analyzing Performance Parameters

Run the top-H command to check the CPU usage. The following is displayed:

In the query result for Cpu(s), check whether the system CPU (sy) or user CPU (us) usage is high. 2.1 If the system CPU usage is too high, you need to identify the abnormal system processes and handle them. 2.2 If the CPU usage of the openGauss process whose USER is omm is too high, optimize the service-related SQL statements based on the running services queries. Based on the features of the currently running service, perform the following operations to check whether this process containing infinite loop logics. 2.1.1 Run the **top -H -p pid **command to identify the threads that use much CPU in the process.

The threads causing high CPU usage are displayed in the top column of the command output. In this section, thread 54775 is used as an example for analyzing the causes of the high CPU usage.

2.2.2 Run the following command to view the function invocation stack for each thread in the process. Check the thread number for the ID of the thread that occupies high CPU usage in the last step.

The query result is as follows. The thread number for the thread ID 54775 is 10.

Performance Tuning-Analyzing Hardware Bottlenecks

Qing — Mon, 24 Apr 2023 07:31:38 +0000

The CPU, memory, I/O, and network resource usage of each node in openGauss are obtained to check whether these resources are fully used and whether any bottleneck exists.

· CPU
You can run the top command to check the CPU usage of each node in openGauss and analyze whether performance bottleneck caused by heavy CPU load exists.
· Memory
Run the top command to check the memory usage of each node in openGauss and analyze whether a performance bottleneck occurs due to high memory usage.
· I/O
You can run the iostat or pidstat command, or use openGauss heath check tools to check the I/O usage and throughput on each node in openGauss and analyze whether performance bottleneck caused by I/O exists.
· Network
You can run the sar or ifconfig command to check the network status on each node in openGauss and analyze whether performance bottlenecks caused by network faults occur.

Performance Tuning-Determining the Scope of Performance Tuning

Qing — Mon, 24 Apr 2023 07:29:57 +0000

Database performance tuning often happens when users are not satisfied with the service execution efficiency and want to improve the efficiency. The database performance is affected by many factors as described in section Performance Elements. Therefore, performance tuning is a complex process and sometimes cannot be systematically described or explained. It depends more on the database administrator's experience. However, this section still attempts to illustrate the performance tuning methods that can be referred to by application development personnel and new openGauss database administrators.

Performance Elements
There are multiple performance factors that affect the database performance. Knowing these factors can help you identify and analyze performance-associated issues.

· System resources

Database performance greatly relies on disk I/O and memory usage. To accurately set performance counters, you need to have a knowledge of the basic performance of the hardware deployed in openGauss. Performance of hardware, such as the CPU, hard disk, disk controller, memory, and network interfaces, greatly affects database running speed.

· Load

The load indicates the total database system demands and it changes over time. The overall load contains user queries, applications, concurrent jobs, transactions, and system commands transferred at any time. For example, the system load increases if multiple users are executing multiple queries. The load will significantly affect the database performance. Identifying load peak hours helps improve resource utilization so that tasks are executed effectively.

· Throughput

The data processing capability of a database is defined by its throughput. Database throughput is measured by the number of queries or processed transactions per second or by the average response time. The database processing capacity is closely related to the underlying system performance (disk I/O, CPU speed, and storage bandwidth). You need to know about the hardware performance before setting a target throughput.

· Competition

Competition indicates that two or more load components try to use system resources in a conflicting way. For example, competition occurs when multiple queries attempt to update the same data at the same time, or when a large number of loads compete for system resources. When competition increases, the throughput decreases.

· Optimization

The database optimization can affect the performance of the whole system. Before executing the SQL statements, configuring database parameters, designing tables, and performing data distribution, enable the database query optimizer can help you obtain the most efficient execution plan.

Determining the Tuning Scope
Performance tuning depends on the usage of hardware resources, such as the CPU, memory, I/O, and network of each node in openGauss. Check whether these resources are fully utilized, and whether any bottlenecks exist, and then perform performance tuning as required.

· If a resource reaches the bottleneck:

Check whether the key OS parameters and database parameters are properly set and perform System Optimization.
Find the resource consuming SQL statements by querying the most time-consuming SQL statements and unresponsive SQL statements, and then perform SQL Optimization.

· If no resource reaches the bottleneck, the system performance can be improved. In this case, query the most time-consuming SQL statements and the unresponsive SQL statements, and then perform SQL Optimization as required.

· Analyzing Hardware Bottlenecks
The CPU, memory, I/O, and network resource usage of each node in openGauss are obtained to check whether these resources are fully used and whether any bottleneck exists.

· Querying SQL Statements That Affect Performance Most

· Checking Blocked Statements

Performance Tuning-Overview

Qing — Mon, 24 Apr 2023 07:24:14 +0000

To fine-tune openGauss performance, you need to identify performance bottlenecks, adjust key parameters, and optimize SQL statements. During performance tuning, locate and analyze performance issues based on performance elements, such as system resources, throughput, and loads to achieve required system performance.

Various factors must be considered during openGauss performance tuning. Therefore, optimization personnel must know well about knowledge, such as system software architecture, hardware and software configuration, database parameter configuration, concurrency control, query processing, and database applications.

Tuning Process
Figure 1 shows the procedure of performance tuning.

Figure 1 openGauss performance tuning

Table 1 lists the details about each phase.

Table 1 openGauss performance tuning

Comparison – Disk vs. MOT

Qing — Mon, 24 Apr 2023 07:21:38 +0000

The following table briefly compares the various features of the openGauss disk-based storage engine and the MOT storage engine.

Table 1 Comparison – Disk-based vs. MOT

Appendices
References
[1] Y. Mao, E. Kohler, and R. T. Morris. Cache craftiness for fast multicore key-value storage. In Proc. 7th ACM European Conference on Computer Systems (EuroSys), Apr. 2012.

[2] K. Ren, T. Diamond, D. J. Abadi, and A. Thomson. Low-overhead asynchronous checkpointing in main-memory database systems. In Proceedings of the 2016 ACM SIGMOD International Conference on Management of Data, 2016.

[3] Tu, S., Zheng, W., Kohler, E., Liskov, B., and Madden, S. Speedy transactions in multicore in-memory databases. In Proceedings of the Twenty-Fourth ACM Symposium on Operating Systems Principles (New York, NY, USA, 2013), SOSP ’13, ACM, pp. 18–32.

[4] H. Avni at al. Industrial-Strength OLTP Using Main Memory and Many-cores, VLDB 2020.

[5] Bernstein, P. A., and Goodman, N. Concurrency control in distributed database systems. ACM Comput. Surv. 13, 2 (1981), 185–221.

[6] Felber, P., Fetzer, C., and Riegel, T. Dynamic performance tuning of word-based software transactional memory. In Proceedings of the 13th ACM SIGPLAN Symposium on Principles and Practice of Parallel Programming, PPOPP 2008, Salt Lake City, UT, USA, February 20-23, 2008 (2008),

pp. 237–246.

7] Appuswamy, R., Anadiotis, A., Porobic, D., Iman, M., and Ailamaki, A. Analyzing the impact of system architecture on the scalability of OLTP engines for high-contention workloads. PVLDB 11, 2 (2017),

121–134.

[8] R. Sherkat, C. Florendo, M. Andrei, R. Blanco, A. Dragusanu, A. Pathak, P. Khadilkar, N. Kulkarni, C. Lemke, S. Seifert, S. Iyer, S. Gottapu, R. Schulze, C. Gottipati, N. Basak, Y. Wang, V. Kandiyanallur, S. Pendap, D. Gala, R. Almeida, and P. Ghosh. Native store extension for SAP HANA. PVLDB, 12(12):

2047–2058, 2019.

[9] X. Yu, A. Pavlo, D. Sanchez, and S. Devadas. Tictoc: Time traveling optimistic concurrency control. In Proceedings of the 2016 International Conference on Management of Data, SIGMOD Conference 2016, San Francisco, CA, USA, June 26 - July 01, 2016, pages 1629–1642, 2016.

[10] V. Leis, A. Kemper, and T. Neumann. The adaptive radix tree: Artful indexing for main-memory databases. In C. S. Jensen, C. M. Jermaine, and X. Zhou, editors, 29th IEEE International Conference on Data Engineering, ICDE 2013, Brisbane, Australia, April 8-12, 2013, pages 38–49. IEEE Computer Society, 2013.

[11] S. K. Cha, S. Hwang, K. Kim, and K. Kwon. Cache-conscious concurrency control of main-memory indexes on shared-memory multiprocessor systems. In P. M. G. Apers, P. Atzeni, S. Ceri, S. Paraboschi, K. Ramamohanarao, and R. T. Snodgrass, editors, VLDB 2001, Proceedings of 27th International Conference on Very Large Data Bases, September 11-14, 2001, Roma, Italy, pages 181–190. Morga Kaufmann, 2001.

Glossary
Table 2 Glossary

MOT JIT Diagnostics

Qing — Mon, 24 Apr 2023 06:22:54 +0000

mot_jit_detail
This built-in function is used to query the details about JIT compilation (code generation).

Usage Examples

Output Description

mot_jit_profile
This built-in function is used to query the profiling data (performance data) of the query or stored procedure execution.

Usage Examples

Output Description

Miscellaneous
Another useful system table to get information about stored procedures and functions is pg_proc.

For example, body of a stored procedure can be queried using the following query:

MOT Query Native Compilation (JIT)

Qing — Mon, 24 Apr 2023 04:52:02 +0000

Native Compilation (JIT) is one of key technologies of MOT for delivering exceptionally low latency and high throughput performance. Two types of Native Compilation (JIT) are supported (using a PREPARE statement): 1) JIT for Stored Procedures (JIT SP), and 2) JIT for Queries (JIT Query). The following sections describe how to use both mechanisms in your application.

JIT SP
JIT SP refers to code generation, compiling and execution of stored procedures (SP) by LLVM runtime code generation and compilation library. JIT SP is available to SPs accessing MOT tables (only) and is completely transparent to users. Acceleration level depends on the SP logic. For example, a real customer application achieved acceleration of 20%, 44%, 300% and 500% for different SPs in latency. During the PREPARE phase of a query invoking an SP, or the first SP execution, the JIT module performs an attempt to translate the SP SQL into a C-based function and compile it in runtime (using LLVM). If the consecutive SP invocation is successful, MOT will execute a compiled function, leading to performance gains. In case of failure to produce a compiled function, the SP will be executed by standard PGPLSQL. Both scenarios are fully transparent to users.

JIT Query
MOT enables you to prepare and parse pre-compiled full queries in a native format (using a PREPARE statement) before they are needed for execution.

This native format can later be executed (using an EXECUTE command) more efficiently. This type of execution is much more efficient because during execution the native format bypasses multiple database processing layers. This division of labor avoids repetitive parse analysis operations. The Lite Executor module is responsible for executing prepared queries and has a much faster execution path than the regular generic plan performed by the envelope. This is achieved using Just-In-Time (JIT) compilation via LLVM. In addition, a similar solution that has potentially similar performance is provided in the form of pseudo-LLVM.

The following is an example of a PREPARE syntax in SQL –

The following is an example of how to invoke a PREPARE and then an EXECUTE statement in a Java application –

Prepare
PREPARE creates a prepared statement. A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed and rewritten.

If the tables mentioned in the query statement are MOT tables, the MOT compilation takes charge of the object preparation and performs a special optimization by compiling the query into IR byte code based on LLVM.

Whenever a new query compilation is required, the query is analyzed and a proper tailored IR byte code is generated for the query using the utility GsCodeGen object and standard LLVM JIT API (IRBuilder). After byte-code generation is completed, the code is JIT‑compiled into a separate LLVM module. The compiled code results in a C function pointer that can later be invoked for direct execution. Note that this C function can be invoked concurrently by many threads, as long as each thread provides a distinct execution context (details are provided below). Each such execution context is referred to as JIT Context.

To improve performance further, MOT JIT applies a caching policy for its LLVM code results, enabling them to be reused for the same queries across different sessions.

Execute
When an EXECUTE command is issued, the prepared statement (described above) is planned and executed. This division of labor avoids repetitive parse analysis work, while enabling the execution plan to depend on the specific setting values supplied.

When the resulting execute query command reaches the database, it uses the corresponding IR byte code which is executed directly and more efficiently within the MOT engine. This is referred to as Lite Execution.

In addition, for availability, the Lite Executor maintains a preallocated pool of JIT sources. Each session preallocates its own session-local pool of JIT context objects (used for repeated executions of precompiled queries).

You may refer to the Unsupported JIT features section in MOT SQL Coverage and Limitations page.

JIT for Stored procedures
JIT for Stored Procedures (JIT SP) is supported by the openGauss MOT engine (starting from 5.0 version), and its goal is deliver even higher performance and lower latency. Refer to JIT for SP for more details.

MOT Recovery Concepts

Qing — Mon, 24 Apr 2023 04:48:27 +0000

The MOT Recovery Module provides all the required functionality for recovering the MOT tables data. The main objective of the Recovery module is to restore the data and the MOT engine to a consistent state after a planned (maintenance for example) shut down or an unplanned (power failure for example) crash.

openGauss database recovery, which is also sometimes called a Cold Start, includes MOT tables and is performed automatically with the recovery of the rest of the database. The MOT Recovery Module is seamlessly and fully integrated into the openGauss recovery process.

MOT recovery has two main stages – Checkpoint Recovery and WAL Recovery (Redo Log).

MOT checkpoint recovery is performed before the envelope's WAL recovery takes place. This is done only at cold-start events (start server). It recovers the metadata first (schema) and then inserts all the rows from the current valid checkpoint, which is done in parallel by checkpoint_recovery_workers, each working on a different table. The indexes are created during the insert process.

When checkpointing a table, it is divided into 16 MB chunks, so that multiple recovery workers can recover the table in parallel. This is done in order to speed-up the checkpoint recovery, it is implemented as a multi-threaded procedure where each thread is responsible for recovering a different segment. There are no dependencies between different segments therefore there is no contention between the threads and there is no need to use locks when updating table or inserting new rows.

WAL records are recovered as part of the envelope's WAL recovery. openGauss envelope iterates through the XLOG and performs the necessary operation based on the xlog record type. In case of entry with record type MOT, the envelope forwards it to MOT RecoveryManager for handling. The xlog entry will be ignored by MOT recovery, if it is 'too old' – its LSN is older than the checkpoint's LSN (Log Sequence Number).

Performance is the key benefit of MOT, and low latency RTO in high availability scenarios is not an exception. With openGauss 5.0 release, MOT engine includes a Parallel Recovery mechanism to achieve low RTO as per openGauss specifications.

In an active-standby deployment, the standby server is always in a Recovery state for an automatic WAL recovery process.

The MOT recovery parameters are set in the mot.conf file explained in the MOT Recovery section.