Service delay may occur due to sudden CPU overload during the service operation. In this process, when a phenomenon caused by Altibase CPU overload continuously occurs. This chapter describes all the checklist and solutions for this case.
1.1 Routine Checklist
The user needs the following history management to determine whether the current CPU load is normal or not. Altibase does not have a function to provide information on the CPU usage rate used by Altibase by itself, so the CPU usage rate must use the command provided by the OS.
Collection Plan | |
---|---|
Whether the service thread is increased or decreased | There are cases where the number of service threads increases due to an increase in sessions or an increase in workload than usual. Check whether this pattern occurs. SELECT COUNT(*) AS THREAD FROM V$SERVICE_THREAD; |
Hourly/daily CPU utilization | Record/manage periodically with the method of obtaining the CPU utilization rate of the process provided by the OS. |
Transaction throughput by time/day | Record/manage the transaction throughput of the application program.If this is difficult, the following information is periodically acquired and recorded/managed with V$SYSSTAT provided by ALTIBASE. select name,value from V$SYSSTAT where name in ( 'execute success count','prepare success count','prepare failure count'); |
It may be more convenient to record information collected at regular intervals in a file using the following shell script.
Since EXECUTE COUNT obtained by V$sysstat is an accumulated value, to obtain the increase per unit time, the unit time increase value = (last value-previous value) must be calculated.
When the above shell script is executed, the following output can be displayed.
$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
......
With the above history information, the user will be able to create a history in the form of a table as follows. It is convenient to use the Excel cell calculation formula for the increment per unit time.
Time(HH:MI:SS) | CPU (%) | Session / Thread | EXECUTE cumulative value | EXECUTE increase per unit time | TPS(Transaction per second) |
---|---|---|---|---|---|
15:03:53 | 32.1 | 212/17 | 189865 | 4031=(189865-185834) | 134=4031/30 seconds |
15:03:23 | 24.8 | 208/16 | 185834 | 4021=(185834-181813) | 134=4021/30 seconds |
15:02:53 | 12.3 | 208/16 | 181813 | - | - |
With this type of data, it is necessary to first determine whether the difference in load between the normal point of time and the point of CPU overload is due to an increase in transaction throughput.