As described in previous chapters, for each item, this chapter summarizes how the user could routinely check and find the cause of the CPU overload.
5.1 Step-by-step inspection method
5.1 Data collection for analysis
Collect data to determine whether the current CPU load is normal. After collecting information such as Altibase's hourly/daily CPU usage and transaction throughput as in the previous-described '1.1 Routine Checklist" and the command provided by the OS, it is determined whether or not the CPU usage is normal due to the increase in the transaction.
5.2 Cause analysis
5.2.1 Check whether it is related to the impact of application change
If there is a newly added service (application) and the operation time of the newly added service and the time of an increase in CPU load amount coincide, it is necessary to check the relevance of the newly added service.
5.2.2 Relevance to data increase and check whether there is any change in the query plan
It is necessary to check whether the CPU increases due to access cost due to data accumulation over time. The old version of Altibase (below 6.1.1) has the function of automatically collecting statistics according to specific conditions. If the optimizer selects and uses a plan that is less efficient than the existing query plan due to the update of statistics information, an increase in access cost can cause an increase in CPU usage.
5.2.3 Mismatch of DB and application character set
When the character set (NLS_USE) is set differently from the DB and the application, there has been a reported case that Altibase performance is downgraded and CPU usage is increased due to internal character set encoding.
5.2.4 Increase of service thread
When the dedicated service thread that handles session requests is in an increased state, sys CPU usage may increase due to an increase in the 'select' poll system call function to periodically check whether a request exists.
5.2.5 Affect of OS environment variables
There may be changes in CPU usage depending on the OS environment variable setting. Altibase adopts a multi-threaded structure, so it is necessary to properly set the related settings for the OS configuration.
5.2.6 Frequent DB connection attempts
The processing of accessing and releasing DB is a task that uses DB resources, and it is difficult to detect malicious queries in the structure of repetitive DB connect/disconnect. Additionally, because the statement cannot be prepared and used, it may be a factor of query performance down gradation.
5.2 Navigation with OS utility
Altibase adopts a multi-thread structure. Therefore, it is necessary to determine whether a specific internal thread is abnormally using a lof CPU. In this case, the CPU usage and current state of each thread can be checked with the utility provided for each OS. For more detailed information, refer to the following document.
- "Utility Guide by OS for Problem Analysis'