This chapter briefly describes the parts that Altibase uses CPU during query processing.

3.1 CPU load according to application program implementation type

ALTIBASE query processing is largely executed with the following processes.

Among these processes, 1)~ 3) are called Prepare-Validation-Optimization (PVO), and the process of 4) is called EXECUTE. Generally, the process of executing PVO occupies about 60% to 70% of the entire query processing.

Based on the assumptions above, it is possible to guess how inefficient CPU usage is caused to execute the same query in the form of "Prepare-execute" every time. Therefore, if the following items are excessively increased with v$sysstat, it is recommended that the developer or operator modify the application program so as not to perform repetitive PREPARE or use the PLAN-CACHE function provided from Altibase version 5.

If the above value increases continuously, it means that there is a part of the application program that continuously repeats Prepare. The application program can be found by combining V$sesstat, another performance view that shows v$sysstat information in detail by session, and the V$session table, which shows process PID.

For more detailed information related to the above, please refer to the "Altibase Development Guide"

3.2 CPU Load by Data Scan

3.2.1  Data access of memory table

Altibase's memory table is kept loaded in the physical system memory. The unit to be stored is stored in a page unit of 32Kbyte, and even within this page, it is divided into individual units (Slot) for each table suitable for record storage.

The problem is, when accessing a table in a full-scan format, the cost of accessing all the data in the table is very high. If such queries are executed in multiple sessions at the same time, the CPU usage rate will inevitably increase.

So, when using an index, does the CPU utilization necessarily decrease?

Not necessarily. The index of the memory table also exists in the system memory. However, there is no actual data value in the index of the memory table. However, it has the physical pointer value where the data in the system memory is located, and this pointer value is calculated in conjunction with the index key value and is sorted in the converted value form.

Therefore, the actual physical data indicated by the index is accessed every time to obtain and compare the data value. (This process may seem unreasonable, but it does not induce disk I/O in terms of performance, so it is possible to process more transactions by occupying the CPU even when other DBMSs require waiting time due to I/O.)

Therefore, even for queries using indexes, the CPU usage rate increases as the number of access-cost increases. Therefore, the user must make efforts to remove inefficient queries by monitoring the execution plan of queries executed in a service operation on a regular basis.

3.2.2  Data access of disk table

The description of the disk table requires an understanding of the CPU usage in the I/O side as follows. Generally, the OS tries to guarantee I/O performance by allocating a memory area called File-Cache and loading it in memory in advance if the file I/O occurs frequently. However, since most RDBMSs achieve this effect by placing their own buffer in the middle, it is generally recommended to use Direct I/O without using the file-cache if the file system supports Direct I/O.

Buffered I/O uses CPU resources for Read/Write System-Call processing in the code of the application program, and CPU resources are used in the process of syncing the details recorded in the file-cache to disk by OS policy. At this point, waiting for the I/O operation to be completed and CPU utilization seems to decrease, but there is a possibility that the service processing decreases as other applications cannot use the CPU resources at this time.

Conversely, when using the Direct I.O, the CPU delegates the I/O itself to the dedicated I/O channel. Accordingly, there is no need for the CPU to wait for I/O processing, and other application programs can occupy the resource, and service processing is relatively more acceptable. But, compared to Buffer I/O, the CPU usage rate can be determined by the user as if it has increased.

For more detailed information on how to set up and operate Buffer I/O and Direct I/O, please refer to the "Volume Configuration Guide Considering Disk I/O Bottleneck".

In summary, there is no right answer on whether buffered I/O or direct I/O settings are appropriate because all of them have trade-offs for CPU usage and performance, so make sure to set them properly for the user's service environment.

3.3 Summary

It is conceptually impossible that some of the processes described above all require CPU resources, require CPU resources, and as a result, the query processing itself should produce fast performance while using a low CPU. However, it can be said that is possible according to the efforts of the user/developer to efficiently use the CPU and perform the required performance.