Skip to content

Altibase CPU Usage Increase Analysis Guide#

Overview#

System CPU load occurs in two areas: the User area, used by application programs, and the Sys (Kernel) area, used by the kernel. The kernel mainly uses the CPU to handle events that use system resources, and application programs cause most of the remaining CPU usage. Altibase applies various algorithms to process user queries (transactions). It also communicates with clients, performs replication, and causes disk I/O on data files, using a wide range of system resources. Therefore, Altibase uses the CPU in both the User area and the Sys area.

This document describes what to examine when Altibase's CPU usage increases, how to determine whether it is normal, and how to resolve it when it is not. It first explains where Altibase uses the CPU while processing queries and introduces the information to collect routinely. It then walks through an analysis procedure that narrows down the cause step by step. Before reading this document, it is recommended to read the Altibase Development Guide and the Altibase Monitoring Queries Guide.

This document is based on Altibase 6 or later and Linux (2.6.32-504.el6.x86_64).

Info

For errors and improvements related to this document, please contact the technical support portal or technical support center.

Info

This document is provided for informational purposes only and is subject to change without prior notice. It may contain errors, and Altibase makes no express or implied warranties of any kind, including but not limited to warranties of merchantability or fitness for a particular purpose.

The development, release, and timing of any features or functionality described for Altibase products in this document are at the sole discretion of Altibase.

Altibase may hold patents, trademarks, copyrights, or other intellectual property rights related to the contents of this document.

How Altibase Uses the CPU#

Every step of query processing requires CPU resources. Therefore, it is conceptually impossible to process queries quickly while using little CPU. What matters is not the CPU usage itself but whether the CPU delivers performance in proportion to its usage. With effort from users and developers, Altibase can meet performance requirements while using the CPU efficiently. This section briefly describes where Altibase uses the CPU while processing queries.

CPU Cost by Query Processing Stage#

Altibase processes a query in four main stages. It first checks the query for statement and syntax errors, and then checks that the tables and columns the query uses exist. Next, it builds an optimal execution plan, and finally it accesses the data area and retrieves or modifies data with the required operations. The first three stages are called Prepare-Validation-Optimization (PVO), and the last stage is called Execute.

In general, PVO accounts for about 60 to 70% of the total query processing cost. Therefore, an application that prepares and executes the same query every time uses the CPU very inefficiently. To find such applications, see Repeated Prepare.

Memory Table Access#

Altibase keeps all memory table data in physical memory, so it does not wait for disk I/O when accessing the data. Therefore, the cost of accessing a memory table appears mostly as CPU usage, and CPU usage increases with the number of records a query accesses.

A full scan accesses every record in the table, so it has the highest cost. When multiple sessions run such queries at the same time, CPU usage spikes.

Queries that use an index also increase CPU usage when they access many records, that is, when their access cost is high. A memory table index holds the memory location (pointer) of each record instead of the data value, so Altibase accesses the actual record to read the value every time it compares a key value.

Disk Table Access#

Disk tables cause disk I/O on data files, and the way the CPU is used depends on the I/O method.

Buffered I/O reads and writes data files through the OS file cache. The CPU is used when it processes Read/Write system calls and when the OS synchronizes (syncs) the file cache contents to disk. In addition, a wait occurs until the I/O operation completes. During the wait, CPU utilization looks low, but query processing also stops, so throughput can decrease.

Direct I/O bypasses the file cache and hands the I/O to a dedicated I/O channel. The CPU does not wait for the I/O to complete and processes other work instead, so it processes more queries in the same amount of time. As a result, CPU utilization can appear higher than with Buffered I/O. Therefore, if CPU utilization increases after switching from Buffered I/O to Direct I/O, check whether throughput increased as well.

Routine Checklist#

A sudden CPU overload during operation can delay services. To determine whether the current CPU usage is normal, you need a history of normal CPU utilization and throughput. Therefore, collect and manage the following items periodically.

Check Items#

Collect CPU utilization with OS commands, and collect transaction throughput and the number of service threads from Altibase performance views. Record and manage each item by hour and by day.

CPU Utilization#

Periodically record the CPU utilization of the Altibase process with an OS command that reports CPU utilization.

Transaction Throughput#

Record the transaction throughput of the application. If this is difficult, periodically query the Prepare/Execute counts in V$SYSSTAT with the following query and record them.

SELECT NAME, VALUE
  FROM V$SYSSTAT
 WHERE NAME IN ('execute success count', 'prepare success count', 'prepare failure count');

Number of Sessions and Service Threads#

When sessions or workload increase beyond normal levels, service threads can increase as well. Periodically query the number of sessions and service threads with the following queries to check whether this pattern appears.

SELECT COUNT(*) AS thread FROM V$SERVICE_THREAD;
SELECT COUNT(*) AS session FROM V$SESSION;

Collection Script#

It is convenient to collect and record the check items at regular intervals with a shell script like the following. This script prints the CPU utilization of the Altibase process, the number of sessions, the number of service threads, and the cumulative Prepare/Execute count every 30 seconds.

## gettps.sh
ALTIPID=`ps -ef | grep $USER | grep  "bin/altibase -p boot"  | grep -v grep | awk '{print $2}'`
MAXCOUNT=3
COUNT=0
INTERVAL=30
LOGFILE=altitps.log

do_getExecuteCount()
{
is -silent <<EOF
set linesize 1024;
set colsize 50;
set feedback off;
set heading off;
SELECT 'RESULT='||( SELECT COUNT(*) FROM V\$SESSION )
       ||'=' || ( SELECT COUNT(*) FROM V\$SERVICE_THREAD )
       ||'=' || ( SELECT SUM(VALUE) FROM V\$SYSSTAT WHERE NAME IN ( 'execute success count','prepare success count','prepare failure count') )
FROM DUAL;
EOF
}

while [ $COUNT -lt $MAXCOUNT ]
do
ALTICPU=`ps -o pcpu -p $ALTIPID | grep -v CPU`
EXECRESULT=`do_getExecuteCount`
SESSIONCNT=`echo $EXECRESULT | grep "RESULT=" | cut -d'=' -f 2| tr -d ' '`
THREADCNT=`echo $EXECRESULT | grep "RESULT=" | cut -d'=' -f 3 | tr -d ' '`
EXECCNT=`echo $EXECRESULT | grep "RESULT=" | cut -d'=' -f 4 | tr -d ' '`
echo  `date "+%Y%m%d %H%M%S: CPU USAGE="`$ALTICPU" SESSIONCNT=$SESSIONCNT THREADCNT=$THREADCNT EXECCNT=$EXECCNT"

sleep  $INTERVAL
COUNT=`expr $COUNT + 1`
done

Running the script produces output like the following.

$ sh gettps.sh
20170203 150253: CPU USAGE= 12.3 SESSIONCNT=208 THREADCNT=16 EXECCNT=181813
20170203 150323: CPU USAGE= 24.8 SESSIONCNT=208 THREADCNT=10 EXECCNT=185834
20170203 150353: CPU USAGE= 32.1 SESSIONCNT=212 THREADCNT=15 EXECCNT=189865
# ... (omitted) ...

Organizing the Collected Data#

The Execute count in V$SYSSTAT is a cumulative value. Therefore, calculate the increase per unit of time by subtracting the previous value from the latest value. You can build a table like the following from the collected history, and an Excel formula makes it easy to calculate the increase per unit of time.

Time (HH:MI:SS) CPU(%) Session / Thread EXECUTE cumulative value EXECUTE increase per unit of time TPS (Transactions per second)
15:02:53 12.3 208/16 181813 - -
15:03:23 24.8 208/10 185834 4021=(185834-181813) 134=4021/30 seconds
15:03:53 32.1 212/15 189865 4031=(189865-185834) 134=4031/30 seconds

When CPU usage increases, first use this data to determine whether the difference in CPU usage between normal times and the time of the increase comes from increased transaction throughput.

Analysis Procedure#

The causes that increase CPU usage often fall into well-known types. Therefore, it is effective to check step by step, in the following order, whether the problem matches one of these types.

Step What to check
1 Has transaction throughput increased beyond normal levels?
2 Is there a newly added service (application)?
3 Are there long-running queries or queries whose execution plan changed?
4 Does an application repeat Prepare or DB connections?
5 Is there a problem with the character set, service threads, or OS settings?
6 Is a specific thread using an abnormal amount of CPU?

Step 1: Check Transaction Throughput#

If the number of sessions, the number of service threads, and the Prepare/Execute counts collected in the Routine Checklist all increased along with CPU usage, this is a normal increase pattern.

However, distinguish this from cases where unverified queries, such as queries that run for a long time or cause large-scale changes, increase the number of service threads. In this case, service threads increase through their own load balancing function. The pattern itself is normal, but you need to find out who ran these queries and why. Queries that a user ran ad hoc are hard to find once that moment has passed, so periodically check for long-running queries with the Long-Run Query Lookup.

Step 2: Check Newly Added Services#

If there is a newly added service and its start of operation coincides with the increase in CPU usage, the new service is likely the direct cause. In this case, first analyze the execution plans of the newly added queries and tune the queries with high execution time and access cost. After tuning, continue to monitor them periodically with the Long-Run Query Lookup.

Step 3: Check Long-Run Queries and Execution Plans#

If CPU usage increases even though the service configuration, the system, and the Altibase version have not changed, look for the cause in the queries currently running. Existing queries can use more CPU than before for the following two reasons.

First, data growth can reveal problems hidden in existing queries. For example, a query may run smoothly with an existing index while the data is small, but as the data grows over time, an unsuitable index can increase CPU usage.

Second, a change in the execution plan can increase the execution cost. Depending on the version, Altibase automatically collects optimizer statistics under certain conditions. Statistics are mainly updated when data grows or when DDL changes an index. When this happens, the optimizer occasionally selects a wrong execution plan with a higher cost than before. A query that runs with an inefficient execution plan greatly increases access cost and therefore CPU usage.

In both cases, the Long-Run Query Lookup often finds the problem query. Therefore, when the CPU usage pattern is abnormal, check long-running queries first.

Long-Run Query Lookup#

Use the following query to find queries whose execution time or result set fetch time is 1 second or more.

-- Find long-run queries whose execution time or result set fetch time is 1 second or more
SELECT a.SESSION_ID,
       a.CLIENT_PID,
       b.QUERY,
       b.EXECUTE_TIME,
       b.FETCH_TIME
  FROM V$SESSION a,
       V$STATEMENT b
 WHERE a.ID = b.SESSION_ID
   AND (b.EXECUTE_TIME > 1000000 OR b.FETCH_TIME > 1000000);

Execution Plan Check#

Configure the session to display the execution plan, run the long-running query again, and check its execution plan for problems. You can also check the runtime execution plan stored in V$PLANTEXT. For other monitoring queries that check query status, see the Query section of the Altibase Monitoring Queries Guide.

Step 4: Check How Applications Execute Queries#

The way an application executes queries can make it use the CPU inefficiently. This step checks for applications that repeat Prepare or repeatedly connect to and disconnect from the DB.

Repeated Prepare#

As explained in CPU Cost by Query Processing Stage, PVO accounts for most of the query processing cost. Check the Prepare count with the following query.

iSQL> SELECT * FROM V$SYSSTAT WHERE NAME LIKE '%prepare%count%';
SEQNUM      NAME                            VALUE
---------------------------------------------------------------------
37          prepare success count           25522
38          prepare failure count           56

If the VALUE keeps increasing sharply, an application is repeating Prepare. To find the application, combine V$SESSTAT, which shows V$SYSSTAT information per session, with V$SESSION, which shows the client process PID.

Developers or operators modify the application so that it does not repeat Prepare, or use the plan cache (PLAN-CACHE) feature available since Altibase 5. For details, see the Altibase Development Guide.

Frequent DB Connections#

Connecting to and disconnecting from the DB consumes DB resources. Therefore, an application that repeats Connect/Disconnect loses performance in proportion to that cost. In addition, such an application cannot prepare a statement once and reuse it, so query performance also drops. Check whether Connect/Disconnect is frequent with the following query.

-- Check whether Connect/Disconnect is frequent (frequent Connect/Disconnect degrades performance)
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%logon cumulative%';

It is best to change the application structure so that it keeps its connection to the database while it works.

Finding Problem Queries with Profiling#

V$STATEMENT holds execution information only for queries run by currently connected sessions. Therefore, in a structure that repeats Connect/Disconnect, it is very difficult to find problem queries in V$STATEMENT. If you cannot change the application structure, use the profiling feature as follows to find problem queries, even at the cost of degraded database performance.

ALTER SYSTEM SET TIMED_STATISTICS=1;
ALTER SYSTEM SET QUERY_PROF_FLAG=1;  -- Enable the feature
ALTER SYSTEM SET QUERY_PROF_FLAG=0;  -- Disable the feature

When profiling is enabled, Altibase records every subsequent query in *.prof files in the $ALTIBASE_HOME/trc directory. These files are binary, so convert them to text files with altiProfile as follows.

$ altiProfile alti-13109987-0.prof > alti_prof.txt

altiProfile is an analysis utility that Altibase provides. The converted file records the executed queries and detailed execution time information, so use it to find problem queries that took a long time. For details on altiProfile, see the Utilities Manual.

Caution

The profiling feature degrades database performance, and the files it writes can exhaust disk space, so use it with care.

Step 5: Check the Operating Environment#

If the queries and applications have no problems, check the database and OS settings.

Character Set Mismatch#

There have been cases where the database and the application used different character set (NLS_USE) settings, so Altibase converted (encoded) character sets internally, which degraded performance and increased CPU usage. In this case, match the character sets of the database server and the application. Check the character sets with the following queries.

-- Check the client's NLS_USE
iSQL> SELECT ID, COMM_NAME, CLIENT_NLS FROM V$SESSION;

-- Check the database character set
iSQL> SELECT * FROM V$NLS_PARAMETERS;
SESSION_ID : 1
NLS_USE : US7ASCII             -- Character set of the current client
NLS_CHARACTERSET : MS949       -- Character set of the database server
NLS_NCHAR_CHARACTERSET : UTF8
NLS_COMP : BINARY
NLS_NCHAR_CONV_EXCP : FALSE
NLS_NCHAR_LITERAL_REPLACE : FALSE
1 row selected.

Increase in Dedicated Service Threads#

Altibase assigns session connections to service threads to process transactions. When a long-running query occurs or all service threads are in the Executing state, Altibase creates new dedicated threads in real time to process new connections or waiting transactions. The newly created dedicated threads periodically check whether there are transactions to process. In this process, Select-Poll system calls increase and Sys(%) CPU usage rises. This occurred in Altibase 4.3.x.

Check the service thread state with the following query.

iSQL> SELECT TYPE, STATE, RUN_MODE, COUNT(*)
    2 FROM V$SERVICE_THREAD
    3 GROUP BY TYPE, STATE, RUN_MODE;
TYPE                  STATE       RUN_MODE   COUNT(*)
-----------------------------------------------------------------------
IPC                   POLL        DEDICATED  1
SOCKET(MULTIPLEXING)  EXECUTE     SHARED     1
SOCKET(MULTIPLEXING)  POLL        SHARED     31
3 rows selected.

In this case, you can increase the MULTIPLEXING_POLL_TIMEOUT property. A larger value lengthens the Select-Poll system call interval and reduces the number of calls. However, dedicated threads arise from increased sessions, increased transaction load, many long-running queries, lock contention, and similar conditions. Therefore, look for a way to resolve these conditions at the root.

OS Settings#

CPU usage can vary with OS settings. Because Altibase has a multi-threaded architecture, the related OS settings must be configured properly. These settings are not meant to reduce CPU usage; they address cases where performance does not match the CPU being used. For details, see the configuration guide for each OS (Linux, AIX, Solaris, HP-UX).

Step 6: Check CPU Usage by Thread#

Because Altibase has a multi-threaded architecture, also check whether a specific internal thread is using an abnormal amount of CPU. For how to check per-thread CPU usage and current state with OS utilities, see the Utility Guide for each OS for Problem Analysis.