2. Altibase Server Tuning#
This chapter discusses the elements that need to be considered when running Altibase.
- Log Files
- Checkpointing
- Buffers
- Service Threads
- Garbage Collector
- SQL Plan Cache
- CPU Usage
Log Files#
When a new log file is generated, an Altibase server generates extra log files in advance in order to prevent delayed transaction response time. If there is a lack of extra log files, transactions have to wait and this degrades database performance on the overall.
The following query checks the number of times a transaction has waited due to a lack of extra log files.
SELECT LF_PREPARE_WAIT_COUNT FROM V$LFG;
If this value is large, the user should set a larger value for the PREPARE_LOG_FILE_COUNT property so that the log file manager creates a sufficient number of log files in advance. Since the amount of memory used by the server increases as this value increases, the user is not advised to simply set a large number for this value. For more detailed information about the PREPARE_LOG_FILE_COUNT property, please refer to the General Reference > Chapter 2. Altibase Properties.
Checkpointing#
The performance of Altibase can drop due to disk I/O overloads while checkpointing.
Disk I/O should be monitored if steps 3 and 4 take a long time to output the following messages (from the checkpoint trace messages written to $ALTIBASE_HOME/trc/altibase_sm.log).
[CHECKPOINT-step3] Flush Dirty Page(s)
[CHECKPOINT-step4] sync Database File
Disk I/O bottlenecks can be checked with the sar, iostat and other commands.
$ sar 1 3
Linux 2.6.32-431.el6.x86_64 (qar6) 05/07/2025 _x86_64_ (8 CPU)
11:04:36 AM CPU %user %nice %system %iowait %steal %idle
11:04:37 AM all 2.39 0.88 0.63 11.18 0.00 84.92
11:04:38 AM all 0.88 1.64 0.75 10.44 0.00 86.29
11:04:39 AM all 1.01 2.27 0.88 9.58 0.00 86.25
Average: all 1.43 1.59 0.76 10.40 0.00 85.82
$ iostat 1
Linux 2.6.32-431.el6.x86_64 (qar6) 05/07/2025 _x86_64_ (8 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
1.78 0.00 1.39 0.07 0.00 96.76
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 1.57 4.84 78.20 71446842 1155611156
sdb 0.93 7.51 99.57 110980563 1471359736
avg-cpu: %user %nice %system %iowait %steal %idle
1.53 0.00 1.65 0.00 0.00 96.82
If a disk I/O bottleneck is causing performance degradation, the user can fix this by saving log files and data files on different disks.
Alternatively, Disk I/O can be dispersed by adjusting the values of the following checkpointing properties; however, this method can also increase the number of log files.
-
CHECKPOINT_BULK_WRITE_PAGE_COUNT
When checkpointing, a given number of dirty pages can be saved on disk multiple times. This property specifies the number of dirty pages to be saved on disk at one time.
-
CHECKPOINT_BULK_WRITE_SLEEP_SEC / CHECKPOINT_BULK_WRITE_SLEEP_USEC
This property specifies the amount of time to wait (in seconds or microseconds) after saving dirty pages on disk if CHECKPOINT_BULK_WRITE_PAGE_COUNT is not 0.
-
CHECKPOINT_BULK_SYNC_PAGE_COUNT
When checkpointing, this property specifies the number of data pages in memory and disk tables to be synchronized.
For more detailed information on checkpointing, please refer to the Administrator's Manual > 8. Managing Transactions > Checkpointing.
Buffers#
For disk tables, the Altibase server loads data into a limited memory buffer and then accesses it. Consequently, unoptimized queries can cause frequent disk I/O and this can result in performance degradation.
Information about buffers can be checked by querying the V$BUFFPOOL_STAT performance view.
SELECT HIT_RATIO 'HIT_RATIO(%)', VICTIM_SEARCH_WARP
FROM V$BUFFPOOL_STAT;
If the value of the HIT_RATIO column in the V$BUFFPOOL_STAT performance view is small, more pages are being read from disk than the memory buffer. In other words, the Altibase server is inefficiently handling queries.
If the value of the VICTIM_SEARCH_WARP column in the V$BUFFPOOL_STAT performance view keeps increasing, the page flushing operation of the flusher is being deprioritized. This problem can be solved by tuning queries that access a large number of pages in disk tables, or by increasing the value of the BUFFER_AREA_SIZE property.
Statistics are the accumulation of values after the server has started. To obtain the statistics of a certain period, all column values should be calculated as: (the present value – the value at the time measurement started).
Service Threads#
A service thread is a thread on the server which receives requests and executes requests from clients. The Altibase server creates and runs service threads in the following two modes.
-
Dedicated Thread Mode
When a multiple number of clients connect to the server and execute queries, the server creates one service thread for each client session to execute the queries.
-
Multiplexing Thread Mode
The Altibase server creates only the number of service threads optimized for the server, and client sessions share these.
Altibase is designed to always maintain the optimal number of service threads by dynamically adding or deleting them. Nevertheless, the minimum number of service threads specified in the DEDICATED_THREAD_INIT_COUNT or MULTIPLEXING_THREAD_COUNT property is always maintained.
If a very large number of clients are concurrently connected to the server, performance loss can ensue due to the creation of new service threads. Service thread overload can be checked by querying the V$SERVICE_THREAD performance view.
iSQL> SELECT RPAD(TYPE, 30), COUNT(*)
FROM V$SERVICE_THREAD GROUP BY TYPE
UNION ALL
SELECT RPAD(NAME, 30), VALUE1 FROM V$PROPERTY
WHERE NAME LIKE 'MULTIPLEXING%_THREAD_COUNT';
RPAD(TYPE, 30) COUNT
-----------------------------------------------
SOCKET 44
IPC 10
MULTIPLEXING_THREAD_COUNT 8
MULTIPLEXING_MAX_THREAD_COUNT 1024
If SOCKETS is larger than MULTIPLEXING_THREAD_COUNT, the following actions can be taken:
-
Set a larger value for the MULTIPLEXING_THREAD_COUNT property
-
Tune long running query statements
Garbage Collector#
Multi-Version Concurrency Control(MVCC) can create and retain old versions of data. The garbage collector (or ager) increases memory efficiency by recovering memory space that was occupied by obsolete versions of data for reuse.
However, the user should remember that MVCC can cause the following issues:
-
Database size increase due to long running transactions
If a particular transaction takes a long time to execute and is not committed, the previous images of other transactions can be read. As a consequence, the garbage collector becomes unable to delete the previous images of other transactions (previous record versions of memory tables and undo log records of disk tables), and the index keys for these records. This increases the memory table size and the amount of space used in disk undo tablespace. Also, since log files cannot be deleted in case the transaction needs to be rolled back, the file system containing log files may become full. -
Database size increase due to excessive concurrent transactions
Altibase assigns the garbage collector to clean up previous images created by MVCC. If the number of concurrent transactions is much larger than the number of CPUs, the database size may continue to increase because the garbage collector does not have enough time to delete previous images. -
Database size increase due to bulk UPDATE operations
If bulk updates (which generate large amounts of previous versions of data) are performed frequently, memory table and undo tablespace (used with disk tables) sizes increase. Excessive previous images decrease performance. -
Performance degradation due to an excessive number of previous images
If an excessive number of previous images remain in the database for the above reasons, the cost of searching for a specific record can increase and degrade overall performance.
The garbage collector's memory recoverability can be checked by querying the V$MEMGC performance view as follows. The GCGAP column value indicates the amount of obsolete versions of data that the garbage collector needs to delete.
iSQL> SELECT GC_NAME, ADD_OID_CNT, GC_OID_CNT , ADD_OID_CNT - GC_OID_CNT GCGAP FROM V$MEMGC;
ADD_OID_CNT GC_OID_CNT GCGAP
----------------------------------------------
113 113 0
The following query returns the uncommitted transactions that the garbage collector is waiting for to recover memory. The query statements executed by these transactions must be tuned.
SELECT SESSION_ID, TOTAL_TIME, EXECUTE_TIME, TX_ID, QUERY
FROM V$STATEMENT
WHERE TX_ID IN (SELECT ID FROM V$TRANSACTION
WHERE MEMORY_VIEW_SCN = (SELECT MINMEMSCNINTXS FROM V$MEMGC LIMIT 1))
AND EXECUTE_FLAG = 1
ORDER BY 2 DESC;
Excessive memory usage due to MVCC can also be prevented by adjusting the values for the AGER_WAIT_MINIMUM and AGER_WAIT_MAXIMUM properties so that memory recovery is frequently performed by the garbage collector.
SQL Plan Cache#
Please refer to Chapter 7. SQL Plan Cache.
CPU Usage#
The user can check the CPU usage of each thread on the Altibase server and the task being executed by the thread with the highest CPU usage.
The commands for checking thread CPU usage on each operating systems are as follows:
| OS | Command |
|---|---|
| AIX | ps -mo THREAD -p altibase_pid |
| HPUX | glance +s +G |
| LINUX | ps -Lfm -p altibase_pid |
Example
$ ps -mo THREAD -p 4522224
USER PID PPID TID ST CP PRI SC WCHAN F TT BND COMMAND
eheejung 4522224 1 - A 0 60 90 * 40001 - - /home/altibase_home/bin/altibase -p boot from admin
- - - 9568417 S 0 60 1 - 410400 - - -
- - - 9895977 S 0 60 1 f1000f0a10009740 8410400 - - -
- - - 9961701 S 0 60 1 - 410400 - - -
- - - 10420443 S 0 60 1 - 410400 - - -
- - - 10616915 S 0 60 1 - 410400 - - -
- - - 11010221 S 0 60 1 - 410400 - - -
- - - 14811193 S 0 60 1 - 410400 - - -
- - - 15663139 S 0 60 1 - 410400 - - -
- - - 16449727 S 0 60 1 - 410400 - - -
The user can check the thread with the highest CPU usage with the following commands.
-
HP-UX, Linux
pstack altibase_pid
-
AIX
procstack altibase_pid