Skip to content

Performance diagnostics for Altibase#

About this document.#

Purpose This document is for diagnosis when you meet the performance problem in Altibase.
applied to 4.3.9 or later
Prerequisite Altibase admin
  1. Application side diagnostics
  2. Database side diagnostics
  3. System side diagnostics

Application side diagnostics#

Note

Before testing the queries in this document, please make sure to set TIMED_STATISTICS property value to "1" as shown below.

ALTER SYSTEM SET TIMED_STATISTICS = 1;

Most of performance problems are caused by user application logic. Therefore, when facing performance problems, checking the application is always a good starting point.

The following are the most frequently observed cases from applications side.

Preparing statement for every execution ( or using dynamic SQL)#

When you prepare a statement, Altibase performs parsing, validating and optimizing for the given statement. (See also How a query is executed in Altibase) The statement prepared earlier can be reused, so the overhead of preparing statement can be eliminated. In the same context, Dynamic SQL can cause performance problems.

Even if you may not have access to the application source code, you can find out how many times an application prepares statements. Altibase provides v$sysstat / v$sesstat performance view tables which contain the information of statements.

  • The number of prepare operations occurred

    SELECT   TO_CHAR(SYSDATE, 'YYYYMMDD HH:MI:SS') CUR_TIME,
                 RPAD(NAME, 50) NAME ,
             VALUE
     FROM    V$SYSSTAT
     WHERE   SEQNUM IN (27, 29);
    

    Normally, "execute success count" is much bigger than "prepare success count". If not, you need to check your application.

    You can get the various information from V$SYSSTAT or V$SESSTAT. Please refer the V$STATNAME explanation in the Administrators Manual to get more information in detail.

A bad SQL that consumes a lot resources#

A SQL that performs full table or index scans can cause serious performance problems. You can locate "bad SQL statements" in your Altibase environment by using the following queries.

  • The longest "Top 5 queries" currently running in Altibase server

    SELECT  EXECUTE_TIME/ 1000000 EXEC_SECOND,
            RPAD(QUERY, 500) QUERY
    FROM    V$STATEMENT
    WHERE   EXECUTE_FLAG = 1
    ORDER BY 1 DESC
    LIMIT 5;
    
  • A query that has a "FULL SCAN" plan node.

    SELECT  RPAD(QUERY,200) ,
            COUNT(*) CNT
    FROM    V$STATEMENT
    WHERE  (SESSION_ID, ID) IN
           (
            SELECT SID , STMT_ID
            FROM V$PLANTEXT
            WHERE TEXT LIKE '%FULL%'
           )
    GROUP BY QUERY
    

Connecting and disconnecting for every DBMS request#

The cost of creating a connection is expensive for both Altibase and applications. It can cause serious performance degradation. You can find out how many times an application makes connections by using the following query.

  • The cumulative connection count after a server start-up.

    SELECT   TO_CHAR(SYSDATE, 'YYYYMMDD HH:MI:SS') CUR_TIME,
             RPAD(NAME, 50) NAME ,
             VALUE
     FROM    V$SYSSTAT
     WHERE   SEQNUM = 1;
    

    Note that this value is a snapshot of the current cumulative connection count. In order to get meaningful values, you have to get the results for several times in short period of time.

Consider IPC connection if a client and a database both reside on the same machine#

Altibase provides three communication protocols.

Connection Type Description
TCP / IP (CON_TYPE=1) Default connection type in Altibase. Altibase uses TCP/IP socket to communicate with the client. The client located on the different machine can connect to Altibase by using this connection.
Unix Domain (CON_TYPE=2) Unix Domain uses "pipe" provided by Unix OS. It is faster than TCP/IP but application and Altibase have to be running on the same machine.
IPC (Inter-Process Communication) (CON_TYPE=3) The fastest connection type between the client and Altibase. Like Unix domain, application and Altibase have to be running on the same machine.

Note

Note that you must set the value of IPC_CHANNEL_COUNT in altibase.properties bigger than 0 before using IPC connection. IPC_CHANNEL_COUNT is the maximum concurrent connection counts for IPC connection type. Default value is 0. (IPC connection is disabled.)

In order to increase the performance of Altibase, you may want to consider using "IPC" connection type whenever possible. If you want to determine what connection type an application uses, you can use the following query.

  • The session count categorized by the connection type

    SELECT  SUBSTR(COMM_NAME, 1,4) CON_TYPE,
            COUNT(*) CNT
    FROM    V$SESSION
    GROUP BY SUBSTR(COMM_NAME, 1,4) ;
    

Database side diagnostics#

Once all the considerations on "Application side diagnostics" are applied, the next step is to look into the settings of Altibase.

Log file I/O contention#

Altibase makes use of WAL (Write Ahead Logging) protocol which requires that all modifications are written to a transaction log (logfile) before they are applied to DBMS. If you want to enhance the performance of the application, you will need to check the state of log files.

There are two threads related to the state of log files. The first one is "logfile prepare thread" which allocates new memory in order to write a redo log. The second one is "log write thread" which applies all modifications into DBMS.
Once the speed of "log write thread" exceeds "logfile prepare thread", "log write thread" has to wait for "logfile prepare thread" to prepare log files. This symptom is called as "logfile IO contention".
Altibase provides a parameter called "PREPARE_LOG_FILE_COUNT"(default value is 5) to address this issue. Increasing this value sufficiently can prevent the problem in advance.
You can monitor the state of logfile as follows:

iSQL> select LF_OPEN_COUNT, LF_PREPARE_COUNT, LF_PREPARE_WAIT_COUNT from v$lfg;

If the value of LF_PREPARE_WAIT_COUNT is greater than 1 from the result of the above query, you need to increase the value. The value can be estimated as following.
Double the parameter value of "PREPARE_LOG_FILE_COUNT", then see the value of LF_PREPARE_WAIT_COUNT is still larger than 1.
If so, double the value again and see the result until the value reaches 100.
If LF_PREPARE_WAIT_COUNT is still over 1, it means that the performance of disk is slow. Therefore, you have to consider changing the disk to resolve the problem.

caution

Altibase allocates memory for log files based on the value of PREPARE_LOG_FILE_COUNT. Therefore, larger value means larger memory usage.

Checkpoint I/O#

The fuzzy and ping-pong based implementation of Altibase checkpointing does not impact transaction response times. However, if the log files and data files are physically on the same file system there may be some read and write I/O contention.

In another case, If buffered I/O mechanisms of the Operating System (OS) is used, most of I/O the traffic resides in the OS buffer. Once OS flushes all data onto disk area, the performance again may fluctuate at that time. If buffered I/O of the OS causes performance degradations, you may want to consider changing it to DIRECT I/O. Please, refer to the OS manual how to change this setting.

The best approach to deal with such I/O contentions is to separate the physical location of the log files and datafiles.

Altibase provides the following parameters to scatter I/O writes.

  • CHECKPOINT_BULK_WRITE_PAGE_COUNT 0 => 100
  • CHECKPOINT_BULK_WRITE_SLEEP_SEC 0 => 0
  • CHECKPOINT_BULK_WRITE_SLEEP_USEC 0 => 5000
  • CHECKPOINT_BULK_SYNC_PAGE_COUNT 3200 => 100

caution

Checkpoint duration with tuned parameters as above is longer than when using the default parameters. Therefore in this case, you have to have more disk space available for log files.

MULTIPLEXING_THREAD_COUNT setting#

We highly recommend that the number of MULTIPLEXING_THREAD_COUNT be twice or four times bigger than the number of CPU cores. The best-suited value must be determined by testing with the application.

iSQL> select rpad(type, 30), count(*) from v$service_thread group by type
    2  union all
    3 select '# of IPC ', count(*) from v$session where comm_name like '%IPC%';
RPAD(TYPE, 30)                  COUNT
--------------------------------------------------------
DEDICATED                       1
SHARED                          16
# of IPC                        1

It is best to have the number of "DEDICATED" thread same as "# of IPC"
Having "DEDICATED" value greater than # of IPC means a long running query may exist, so you will need to locate and tune it.

iSQL> select rpad(type, 30), count(*) from v$service_thread group by type
    2  union all
    3 select rpad(name, 30), value1 from v$property where name like 'MULTIPLEXING%_THREAD_COUNT';
RPAD(TYPE, 30)                  COUNT
-----------------------------------------------
SOCKET                          16
MULTIPLEXING_THREAD_COUNT       16
MULTIPLEXING_MAX_THREAD_COUNT   1024

The number of "SOCKET" thread might grow up to the value of MULTIPLEXING_MAX_THREAD_COUNT (default 1024). If the value of "SOCKET" is bigger than the value of MULTIPLEXING_THREAD_COUNT, again there may exist some long running queries.

A long running query slows down the performance and takes much CPU usage in the system.

Memory Ager (Garbage Collector - GC)#

The Memory Ager (GC) reclaims garbage or memory occupied by objects that are no longer in use by the application - the committed old versions by transactions. The GC works independently formed by threads and is consisted with MEM_LOGICAL_AGER and MEM_DELTHR. The former mostly clears unused "index pages" while the latter does unused "data pages", respectively. If GC can not process the work fast enough, it affects the performance of transactions. The reason is that transactions may look up not only the current version of record and index pages, but also the old versions of them.

Altibase provides the performance view tables to monitor the state of GC.

iSQL> select add_oid_cnt, gc_oid_cnt , add_oid_cnt - gc_oid_cnt GCGAP from v$memgc;

If you get increasing values of GCGAP while executing the query above, there may exist some long running queries. You can locate such queries with the following SQL.

select c.session_id, comm_name, client_pid, execute_flag, total_time, execute_time, fetch_time, rpad(query,500)
  from (select * from v$memgc limit 1) a, v$transaction b, v$statement c, v$session d
 where (a.MINMEMSCNINTXS = b.MEMORY_VIEW_SCN or a.MINMEMSCNINTXS = b.MIN_MEMORY_LOB_VIEW_SCN)
   and b.id = c.tx_id
   and c.session_id = d.id;

If there are no symptoms related to the cases above, Altibase provides the parameters below to take control.

  • AGER_WAIT_MINIMUM = 200000 (microseconds)
  • AGER_WAIT_MAXIMUM = 1000000 (microseconds)

To enhance GC's performance, you need to lower the parameter values. Then the next wake-up time of GC, it will be faster.

caution

The faster wake-up time of GC is, the more CPU cycle is consumed by GC which may result in slower transaction response times due to high CPU consumption.

BUFFER_AREA_SIZE#

The Buffer Cache uses the LRU (least recently used) algorithm to keep track of what was used and when. The LRU allows HDB to determine which buffers have not been accessed for a long time so that they can be replaced first. In Altibase, the LRU list is separated into hot and cold zones, and can thus be called a “hot-cold LRU list”. Buffers that are accessed frequently are placed in the hot zone, whereas those that are not accessed frequently are placed in the cold zone. When a buffer needs to be replaced, only the cold zone is searched, meaning that hot buffers are not considered as replacement candidates.
When a page is first loaded into a buffer, it is inserted at the mid-point (LRU cold first) of the LRU list. When allocating a buffer to the new data page, if there are no free buffers in the prepare list, the end (LRU cold last) of this list is searched first, and a cold buffer is then replaced. The buffer that is replaced is called a “victim”. Buffers that are read frequently are moved to the “LRU hot first” position in the hot zone. Meanwhile, dirty buffers, that is, buffers containing pages that have been updated but haven't been flushed to disk, are moved to the flush list. Additionally, clean buffers, that is, buffers containing pages that haven't been updated, are designated as replacement buffers as long as they are not in the hot zone. The relative size of the hot zone can be set using the HOT_LIST_PCT property. The default is 50, which means that half of the LRU list is used as the hot zone. Users need to analyze access patterns in their application since HOT_LIST_PCT property values can influence the performance.

  • Example SQL how to alter HOT_LIST_PCT value
iSQL> alter system set HOT_LIST_PCT = 60;

SQL PLAN CACHE#

The Plan Cache is a piece of memory that Altibase uses in order to store SQL plans. This feature has been introduced to HDB because the Prepare-Phase (Parse-Validate-Optimize) is expensive in terms of CPU cycles. It's functionality can be compared to the Library Cache of Oracle.

Altibase provide performance-views to find out about the state of the SQL plan cache.

  • V$SQL_PLAN_CACHE
  • V$SQL_PLAN_CACHE_PCO
  • V$SQL_PLAN_CACHE_SQLTEXT

Following parameters are used to tune the performance of the SQL plan cache.

name value
SQL_PLAN_CACHE_SIZE 67108864
SQL_PLAN_CACHE_BUCKET_CNT 127
SQL_PLAN_CACHE_HOT_REGION_LRU_RATIO 50
SQL_PLAN_CACHE_PREPARED_EXECUTION_CONTEXT_CNT 1
  • Check the hit_ratio of the plan cache in the time interval.

    iSQL> select CACHE_HIT_COUNT, CACHE_MISS_COUNT from v$sql_plan_cache;
    
  • If CACHE_HIT_COUNT is much bigger than CACHE_MISS_COUNT, that would be a desirable state.
    We highly recommend you that the ratio of "CACHE_MISS_COUNT / CACHE_HIT_COUNT" be normally less than 1/10000. Please also note that this figure is not applicable to all cases.

    Recommended Action:
    a. extend the size of PLAN_CACHE
    b. convert the source "direct-execute" to "prepare-execute"
    c. configure the SQL_PLAN_CACHE_HOT_REGION_LRU_RATIO. 2. Check the number of statements and SQL_PLAN_CACHE_BUCKET_CNT
    The plan cache is managed by a hash-table. SQL_PLAN_CACHE_BUCKET_CNT property indicates the number of hash table buckets in a SQL plan cache. To reduce the scan time, we highly recommend that SQL_PLAN_CACHE_BUCKET_CNT would be 1/4 of total number of statements based on our experience.

    iSQL > select count(*) as total_number_of_statement from v$statement;
    

    Set the SQL_PLAN_CACHE_BUCKET_CNT = total_number_of_statement/4 3. SQL_PLAN_CACHE_PREPARED_EXECUTION_CONTEXT_CNT
    This property indicates the number of execution contexts that are initially created when plans are generated. The initial number of execution contexts is specified before plans are created, however, this only determines the initial number. The number of execution contexts increases or decreases automatically
    as required during runtime. Increasing this value can help realize better performance when only one plan is executed at a time, however, in other cases the plan size is merely increased, without realizing improved performance. To avoid the concurrent contention, we recommend that SQL_PLAN_CACHE_PREPARED_EXECUTION_CONTEXT_CNT would be similar as the number of CPU's.

Others#

If all considerations above are applied, and still observing performance issues, the following procedure will be helpful to figure out what the problem is.

pstack $ALTIBASE_PID (ps -ef | grep altibase | grep "bin/altibase" | grep admin | grep -v grep | awk '{ print $2 }')

We highly recommend you to consecutively gather 3 times of pstack with sleep interval (for example: 10 seconds).

iSQL> select * from v$mutex order by miss_count desc limit 10;
iSQL> select event, wait_time from v$statement where wait_time > 1;
iSQL> select event, TOTAL_WAITS, time_waited from v$system_event order by 2,3;

System side diagnostics#

Kernel / Environment Variables (System Resources)#

It would be advisable to check the OS kernel and environment variables to make sure that all correct settings are applied as explained in the Installation manual and in the pre_install.sh script prior to checking other aspects of the system.

Disk I/O Performance#

Altibase's performance rely on the performance of the hardware. Therefore, it is necessary to check the performance of Disk I/O.

You can use following command to see the performance of a disk (this will create a 1GB file full of 0’s.)

time dd if=/dev/zero of=MyTestFile bs=1M count=1024

It simply displays the time it takes to copy a file.

Direct I/O Use#

Altibase provides two disk I/O methods related to database files:
• Direct I/O
• Buffered I/O

One advantage of Direct I/O is that it reduces CPU resource consumption during occurrence of Disk I/O. On the other hand, since the Buffered I/O uses techniques of read-ahead and asynchronous write, it may not access disk every time when an disk I/O is required. It means that the Buffered I/O is faster than the Direct I/O. However the Buffered I/O consumes a higher level of CPU resources when compared to the Direct I/O.

There are three properties that users have to look at to use the Direct I/O in Altibase

  • LOG_IO_TYPE
  • DATABASE_IO_TYPE
  • DIRECT_IO_ENABLED

To use Direct I/O, you have to mount disk with following options.

OS File System mount option
Solaris UFS none
HP-UX Veritas VxFS mount with convosync=direct
Solaris Veritas VxFS mount with convosync=direct
AIX Veritas VxFS mount with convosync=direct
AIX JFS mount with use -o dio
Windows NT/2000 All none
Tru64 Unix AdvFS none
Linux (2.4 > k) All none

In systems where direct I/O cannot be used, Altibase will use Buffered I/O regardless of the property settings.

vmstat utility#

The vmstat is a system utility that is available in most Unix-based operating systems, and it is used to assess the state of the system in conjunction with memory and CPU. Some OSs support their own proprietary utilities such as "nmon analyzer". The resources controlled by OS is closely related to the performance problems. Therefore, important thing is to gather this information effectively. So here is an example way you can do this:

-- vmstat.sh
while true
do
        DATE=`date +"%y%m%d"`
        TIME=`date +"%H%M%S"`
        tmp=`vmstat 1 1 | tail -1`
        echo "[$TIME] $tmp" >> vmstat_$DATE.log
done

Please refer to the man page in each OS for further details. This shell above is applicable to AIX OS.

iostat utility#

The iostat which is a system tool to assess the I/O state of the system. It is similar to vmstat. I/O flow is also coupled with the performance on the system. Here is an example shell using of iostat:

-- iostat.sh
while true
do
        echo "======================================" >> iostat_$DATE.log
        DATE=`date +"%y%m%d"`
        TIME=`date +"%H%M%S"`
        iostat 1 1 > iostat_tmp

#### hdisk0, hdisk1, hdisk2 belongs to MEMORY storage.
        tmp=`cat iostat_tmp | egrep "hdisk0|hdisk1|hdisk2" |
           awk ' BEGIN { c2 += $2 }
                       { c3 += $3 }
                       { c4 += $4 }
                       { c5 += $5 }
                       { c6 += $6 }
                   END { print c2, c3, c4, c5, c6} '`
        echo "[$TIME] MEM $tmp" >> iostat_$DATE.log

#### hdisk3, hdisk4, hdisk5 belongs to DISK storage.
        tmp=`cat iostat_tmp | egrep "hdisk3|hdisk4|hdisk5" |
           awk ' BEGIN { c2 += $2 }
                       { c3 += $3 }
                       { c4 += $4 }
                       { c5 += $5 }
                       { c6 += $6 }
                   END { print c2, c3, c4, c5, c6} '`
        echo "[$TIME] DISK $tmp" >> iostat_$DATE.log

#### hdisk6, hdisk7 belongs to LOGS storage.
        tmp=`cat iostat_tmp | egrep "hdisk6|hdisk7" |
           awk ' BEGIN { c2 += $2 }
                       { c3 += $3 }
                       { c4 += $4 }
                       { c5 += $5 }
                       { c6 += $6 }
                   END { print c2, c3, c4, c5, c6} '`
        echo "[$TIME] LOGS $tmp" >> iostat_$DATE.log
        echo "" >> iostat_$DATE.log
done

Please refer to the man page in each OS for further detail. This shell above is applicable to AIX OS.