There are cases where the CPU is not being used as much as expected. These phenomena should consider the following cases.
4.1 Mismatch of DB and application program character set
When the character set (NLS_USE) setting is different from the DB and the application program, there has been a reported case that ALTIBASE performance is degraded and CPU usage is increased due to an internal character set encoding (Encoding). In this case, the problem is solved by matching the character set between the DB server and the application program.
# Query to check client's NLS_USE iSQL> select id, comm_name, client_nls from v$session; # Query to check DB charset iSQL> select * from v$nls_parameters; SESSION_ID : 1 NLS_USE : US7ASCII <---- current client's character set NLS_CHARACTERSET : MS949 <--- DB server's character set NLS_NCHAR_CHARACTERSET : UTF8 NLS_COMP : BINARY NLS_NCHAR_CONV_EXCP : FALSE NLS_NCHAR_LITERAL_REPLACE : FALSE 1 row selected.
4.2 Increase of Service Thread
In the Altibase 4.3.x version, there has been a case of an increase in CPU usage by the 'Select-Poll' system-call due to the increase of the 'Dedicated Thread'.
Altibase handles transactions by 'Service Thread' in charge of session access. When a long-run query occurs or all 'Service Threads' are in the Executing state, it processes new connections or waiting transactions. 'Dedicated Thread' is created in real-time.
Newly created 'Dedicated Threads' periodically check whether there is a transaction to be processed by ALTIBASE. In this process, ALTIBASE increases the number of 'Select-Poll' system-calls and increases sys(%) usage among CPU usages.
The DB service thread state can be retrieved with the query below.
SQL> 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 preparation for this type, there is a method to increase the attribute value of 'MULTIPLEXING_POLL_TIMEOUT'. This is to decrease the number of system-calls by increasing the execution cycle of the 'Select-Poll' system-call.
Fundamentally, the reason for the occurrence of 'Dedicated Thread' is an increase in sessions, an increase in transaction load, a large number of long-run queries, and for lock contention.
4.3 Affect of OS environment variable setting
There may be a change in CPU usage depending on the OS environment variable setting. Because ALTIBASE adopts a multi-threaded structure, it is necessary to properly set the related settings for the OS configuration. This setting can be understood not as a reduction in CPU usages, but as a setting for cases in which using the CPU is not under-performing.
For more detailed information, please refer to the "Altibase Installation Guide" provided for each OS.
4.4 Frequent DB connection attempts
The v$statement of Altibase contains execution information for queries executed by the currently connected session. Therefore, it is very difficult to retrieve malicious queries from this information in a structure in which connect/disconnect is repeatedly performed.
4.4.1 Checking whether frequent DB access occurs
This can be check with the following query.
4.4.2 Selecting turning target queries with profiling
Moreover, the performance of the application is bound to decrease as much as the cost of each connect/disconnect. Therefore, it is recommended for the user to change the structure of an application so that it is processed while connected to the DB. If this is not possible, the malicious query must be retrieved with the profiling functional as follows, even at the risk of degrading the DB performance.
All queries executed after executing the above function are recorded with the file name of *pro' with the extension of $ALTIBASE_HOME/trc/xxxxx.prof. This file is created as a binary file, so the user cannot immediately check it, and it must be converted to a plain text format as follows.
'altiProfile' is analysis utility provided by Altibase, and analysis files can be created by using this utility. Since the executed 'queries' and 'detailed information of execute time' are recorded in the file, malicious queries that took a long time to execute can be found based on the details.
For detailed usage of altiProfile, refer to AID's FAQ. (AID description page on how to log queries performed in Altibase: http://aid.altibase.com/x/ygSL)