Skip to content

Altibase Memory Usage Increase Analysis Guide#

Overview#

This document describes how to find and resolve the cause when the memory usage of the Altibase process increases. It first explains how Altibase uses memory and which items to check routinely, and then walks through checking and resolving the cause step by step when memory usage increases abnormally.

The memory usage of Altibase usually increases because of the data in memory tables and the queries that application programs execute. For high-performance data processing, Altibase stores the data of memory tables in a memory tablespace, so it occupies more memory as data accumulates. Altibase also uses memory to analyze and execute queries, so memory usage grows as more queries run at the same time.

For how to write queries in application programs, see the Altibase Development Guide. For the monitoring queries used in this document, see the Altibase Monitoring Query Guide.

This document is based on Altibase 7 or later.

Info

For errors and improvements related to this document, please contact the technical support portal or technical support center.

Info

This document is provided for informational purposes only and is subject to change without prior notice. It may contain errors, and Altibase makes no express or implied warranties of any kind, including but not limited to warranties of merchantability or fitness for a particular purpose.

The development, release, and timing of any features or functionality described for Altibase products in this document are at the sole discretion of Altibase.

Altibase may hold patents, trademarks, copyrights, or other intellectual property rights related to the contents of this document.

How Altibase Uses Memory#

Altibase uses memory to store the data of memory tables, to analyze and execute SQL statements, and to manage multiple versions of records. This chapter explains why memory usage increases in each of these areas and how to check memory usage by internal module.

Data Storage in Memory Tables#

The data and indexes of memory tables all reside in the memory tablespace. Therefore, as data accumulates in memory tables, the memory usage of the Altibase process also increases.

Deleting data from a memory table does not reduce memory usage. For example, if a user inserts 1GB of data into a table and then deletes 900MB, the space allocated to the table remains 1GB, of which the data uses 100MB. The remaining 900MB stays as free space that only this table can use.

This results from how memory space is managed. Because allocating and freeing memory are costly operations, most operating systems do not reclaim memory freed by a process immediately. Instead, they keep it as a reserved area and reuse it for later memory requests. Altibase manages the space of memory tables on the same principle. The space of deleted data remains as free space in the table, and Altibase reuses it when data is later inserted into the same table.

SQL Processing#

Before executing an SQL statement, Altibase parses and validates it and then builds an execution plan. This process is called prepare. In the prepare phase, Altibase stores the parse result, the execution plan, and binding information in memory. In the execute phase, it uses memory to sort data and to store intermediate results. Therefore, memory usage increases as more SQL statements are kept open at the same time.

How an application program is written also affects memory usage. If an application program does not close a Statement object after using it, Altibase keeps the information of that SQL statement until the session ends. In addition, if SQL statements that differ only in the constant values of their conditions are executed without bind variables, each one is analyzed as a different SQL statement and allocated its own memory.

MVCC and Aging#

Memory usage increases when a large change operation is performed on a memory table or when a transaction runs for a long time. This is because Altibase controls concurrency with MVCC. The memory increase described in this section applies only to memory tables.

MVCC (Multi-Version Concurrency Control) is a common DBMS concurrency control technique that improves performance by keeping read and change operations from waiting for each other. When Altibase changes a record in a memory table, it creates a copy of the record in the space of the table and writes the change to the copy. Therefore, the original and the copy exist together at the time of the change, and a large change operation creates as many copies as the number of records it changes. If the table has free space, the copy is created there; if free space is insufficient, new space is allocated from the tablespace. Deleting a record does not create a copy. MVCC trades higher resource usage for faster performance.

When a transaction commits, the copy becomes the final record and the original record becomes a deletion target. Conversely, when it rolls back, the copy becomes the deletion target. Such deletion targets are called Old Versions or Garbage Data, and deleting them is called aging. Aging is performed by an internal module called the garbage collector (GC).

The GC can delete only records that no transaction references, and it processes deletion targets only in the order in which they were requested. Therefore, if a transaction runs for a long time or does not end, the GC cannot process the deletion targets behind it, and the accumulated deletion targets increase memory usage.

Memory Usage by Internal Module#

Memory usage by internal module is checked with the V$MEMSTAT performance view. The following modules are the ones mainly checked when analyzing a memory increase.

Module Description
Query_Prepare Memory that holds the parse result, statistics, execution plan, and binding information in the prepare phase of SQL statements. If an application program prepares an SQL statement every time it executes it (Direct Execute), usage can keep increasing. If SQL statements are prepared once and executed many times, usage increases to some extent and then stays at a constant size.
Query_Execute Memory used to sort data and store intermediate results while SQL statements are executed. It can increase when SQL statements query memory tables or use the TEMP_TBS_MEMORY hint. This memory is freed when the execution of an SQL statement ends, so under normal conditions usage rises and falls repeatedly and stays at a constant size.
Query_Binding Memory that stores the values of bind variables while SQL statements are executed. Usage depends on the number and size of the bind variables in an SQL statement. This memory is returned when a PreparedStatement is closed or the session ends, so under normal conditions usage rises and falls repeatedly and stays at a constant size. Usage increases when the number of SQL statements rises sharply or when values are bound to excessively large data types. It also increases when PreparedStatements are not closed or are created repeatedly during exception handling.
Storage_Memory_Manager Memory that stores memory tablespace management information and the data of memory tables. This value increases as data accumulates in memory tables or as MVCC copies and deletion targets pile up.
Index_Memory Memory used by the indexes created on memory tables. Dropping unnecessary indexes that use a lot of memory reduces this memory.
Storage_Disk_Buffer Memory of the disk buffer manager, which buffers the data of disk tablespaces in memory. Its size is set by the BUFFER_AREA_SIZE property (default 128MB), so it does not grow beyond this value even when the data in disk tables increases. If this item is large, check the BUFFER_AREA_SIZE setting rather than data growth.

Routine Checklist#

A sudden memory shortage during service operation can delay the service. To determine whether the current memory usage is normal, you need a history of usual usage. When a problem occurs, you can compare this history with the normal state to find what increased and trace the cause. Therefore, collect and manage the following items periodically.

Check Items#

Collect memory utilization with OS commands and the other items with performance views. Record and manage each item by time of day and by day.

Memory Utilization#

Periodically record the available memory of the system and the memory utilization of the Altibase process with the commands that the OS provides. For how to check them on each OS, see Step 1: Check System Memory and Swap.

Transaction Throughput#

Record the transaction throughput of the application programs. In Altibase, you can determine the throughput from the prepare and execute counts of SQL statements in V$SYSSTAT. Query these values periodically with the following query and record them. Because the values are cumulative, calculate the throughput per unit time as (latest value - previous value).

SELECT SUM(VALUE)
  FROM V$SYSSTAT
 WHERE NAME IN ('execute success count', 'prepare success count', 'prepare failure count');

Number of Sessions and Service Threads#

When sessions or workload increase beyond usual levels, service threads can also increase. Periodically query V$SESSION and V$SERVICE_THREAD with the following queries to check whether this pattern appears.

SELECT COUNT(*) AS thread FROM V$SERVICE_THREAD;
SELECT COUNT(*) AS session FROM V$SESSION;

Memory Usage by Module#

Periodically query memory usage by internal module with the following query and record it. For the meaning of each module, see Memory Usage by Internal Module.

SELECT * FROM V$MEMSTAT ORDER BY ALLOC_SIZE DESC;

Collection Script#

It is convenient to collect the check information at regular intervals with a shell script such as the following and record it. This script prints the CPU utilization and memory utilization of the Altibase process, the number of sessions, the number of service threads, and the cumulative prepare/execute count every 30 seconds.

## gettps.sh
ALTIPID=`ps -ef | grep $USER | grep  "bin/altibase -p boot"  | grep -v grep | awk '{print $2}'`
MAXCOUNT=3
COUNT=0
INTERVAL=30

do_getExecuteCount()
{
  is -silent <<EOF
  set linesize 1024;
  set colsize 50;
  set feedback off;
  set heading off;
  SELECT 'RESULT='||( SELECT COUNT(*) FROM V\$SESSION )
       ||'=' || ( SELECT COUNT(*) FROM V\$SERVICE_THREAD )
       ||'=' || ( SELECT SUM(VALUE) FROM V\$SYSSTAT WHERE NAME IN ( 'execute success count','prepare success count','prepare failure count') )
  FROM DUAL;
EOF
}

while [ $COUNT -lt $MAXCOUNT ]
do
  ALTICPU=`ps -o pcpu -p $ALTIPID | grep -v CPU`
  ALTIMEM=`ps -o pmem -p $ALTIPID | grep -v MEM`
  EXECRESULT=`do_getExecuteCount`
  SESSIONCNT=`echo $EXECRESULT | grep "RESULT=" | cut -d'=' -f 2| tr -d ' '`
  THREADCNT=`echo $EXECRESULT | grep "RESULT=" | cut -d'=' -f 3 | tr -d ' '`
  EXECCNT=`echo $EXECRESULT | grep "RESULT=" | cut -d'=' -f 4 | tr -d ' '`
  echo  `date "+%Y%m%d %H%M%S: CPU USAGE="`$ALTICPU" " MEM USAGE=$ALTIMEM" SESSIONCNT=$SESSIONCNT THREADCNT=$THREADCNT EXECCNT=$EXECCNT"
  sleep  $INTERVAL
  COUNT=`expr $COUNT + 1`
done

When you run the script, it prints output like the following.

$ sh gettps.sh
20170213 110718: CPU USAGE= 7.1  MEM USAGE= 0.6 SESSIONCNT=1 THREADCNT=25 EXECCNT=1201
20170213 110748: CPU USAGE= 7.1  MEM USAGE= 0.6 SESSIONCNT=1 THREADCNT=25 EXECCNT=1202
20170213 110818: CPU USAGE= 7.1  MEM USAGE= 0.6 SESSIONCNT=1 THREADCNT=25 EXECCNT=1203
# ... (omitted) ...

Check Procedure#

Memory increases have common, well-known causes. Therefore, it is effective to check step by step, in the following order, whether the problem matches one of them.

Step What to check
1 Is the system short of free memory, and has the memory usage of the Altibase process increased?
2 Which internal module has increased its memory usage?
3 Is there a memory table whose data has grown or that has a lot of free space?
4 Has the number of executing SQL statements increased?
5 Has a large change operation been performed on a memory table?
6 Is the GC performing aging normally?

Step 1: Check System Memory and Swap#

First, query the available memory of the machine with OS commands to check whether free memory is insufficient and whether Altibase is the process using a lot of memory.

Linux#

In Linux, query memory usage with the top command. Among the items, mainly check free on the Mem line and cached on the Swap line. The example below is output from RHEL 6 (kernel 2.6.32). In RHEL 7 and later, top and free combine buffers and cached into a single buff/cache value and also display available (avail Mem in top), an estimate of the memory actually available, so check that value instead.

$ top
top - 09:40:42 up 74 days, 16:47,  3 users,  load average: 0.00, 0.02, 0.08
Tasks: 212 total,   1 running, 210 sleeping,   0 stopped,   1 zombie
Cpu(s):  0.0% us,  0.0% sy,  0.0% ni, 99.9% id,  0.0% wa,  0.0% hi,  0.0% si
Mem:  16630888k total, 16559108k used,    71780k free,   100516k buffers
Swap: 16771776k total,    31920k used, 16739856k free, 16034200k cached
# ... (omitted) ...

Because Linux uses unused memory for buffers and the file cache, available memory is calculated as free + buffers + cached. In the result above, the total physical memory is 16,630,888KB. The memory actually available is 71,780KB + 100,516KB + 16,034,200KB = 16,206,496KB, and the memory actually in use is 424,392KB, which is used minus buffers and cached.

The free command shows the same information. The free value on the -/+ buffers/cache line (15821MB) is the memory actually available.

$ free -m
             total       used       free     shared    buffers     cached
Mem:         16241      16188         52          0         98      15669
-/+ buffers/cache:        419      15821
Swap:        16378         31      16347

AIX#

In AIX, check memory usage with the svmon command.

$ svmon -G
               size      inuse       free        pin    virtual
memory      2031616    1779678     251938     474697    1682009
pg space    4128768     495129

               work       pers       clnt      other
pin          404863          0          0      69834
in use      1225427          5     554246

The items on the memory and pg space lines are as follows.

Item Description
size The number of pages of total physical memory. One page is 4,096 bytes, so this system has 7,936MB of memory.
inuse The number of pages of physical memory actually in use (Computational + Persistent).
free The number of pages of physical memory not in use.
pin The number of pages of physical memory that cannot be swapped out.
virtual The number of pages created by the VMM (Virtual Memory Manager).
pg space The total size (size) and usage (inuse) of paging space.

HP-UX#

In HP-UX, run glance, type m, and press Enter to check memory information. The following is the memory usage summary from the glance memory screen.

Total VM :  72.8gb   Sys Mem  :   9.6gb   User Mem:  13.8gb   Phys Mem :  32.0gb
Active VM:  68.5gb   Buf Cache:     0mb   Free Mem:   7.9gb   FileCache:  722mb

In the result above, the total physical memory (Phys Mem) is 32.0GB. Of this, the system uses 9.6GB, user processes use 13.8GB, the file cache uses 722MB, and the buffer cache uses 0MB, and the available memory (Free Mem) is 7.9GB.

Step 2: Check Memory Usage by Module#

If the memory usage of the Altibase process has increased, query V$MEMSTAT, compare the result with the usual records, and find the modules whose usage has increased. For queries that show memory usage by module and the total memory usage, see [MS01] Altibase Memory Usage and [MS02] Total Altibase Memory Usage in the Altibase Monitoring Queries Guide.

What to check in the next steps depends on the module that has increased.

Increased module Step to check
Storage_Memory_Manager, Index_Memory Step 3, Step 5, Step 6
Query_Prepare, Query_Binding Step 4

Step 3: Check Memory Usage by Table#

Query V$MEMTBL_INFO with the following query to check memory usage by memory table, and compare the result with the usual records to find the tables whose usage has increased.

SET LINESIZE 100;
SET COLSIZE 30;
SELECT a.TABLE_NAME
     , (b.FIXED_ALLOC_MEM + b.VAR_ALLOC_MEM) alloc
     , (b.FIXED_USED_MEM + b.VAR_USED_MEM) used
  FROM SYSTEM_.SYS_TABLES_ a
     , V$MEMTBL_INFO b
 WHERE a.TABLE_OID = b.TABLE_OID
   AND a.TABLE_TYPE = 'T'
 ORDER BY 2 DESC;

In the result, alloc is the size of memory allocated to the table, and used is the size actually in use out of the allocated memory. If the data has grown because of business needs, no separate action is required. However, as explained in Data Storage in Memory Tables, deleted space remains in the table as free space. If a table has a used value that is much smaller than its alloc value, clean up the space by returning empty pages or restarting Altibase.

Returning Empty Pages#

Returning empty pages (COMPACT) gives the empty pages that a table does not use back to the tablespace so that other tables in the same memory tablespace can use that space.

Memory tables are allocated space from the memory tablespace. Therefore, if one table uses a lot of memory, including empty pages, other tables in the same memory tablespace may run short of space to allocate. In this case, return the empty pages of the table with the following SQL statement.

ALTER TABLE table_name COMPACT;

Restarting Altibase#

Returning empty pages lets other tables in the same memory tablespace use that space, but the memory usage of the Altibase process seen by the OS does not decrease. To reduce the memory usage of the process as well, restart Altibase.

When Altibase starts, it loads the data pages of the memory tablespace into memory but does not load empty pages that hold no data. As a result, memory usage decreases after a restart. If it is difficult to return empty pages for each table, restart Altibase during regular maintenance (PM) to get the same effect.

Step 4: Check the Number of SQL Statements#

Query V$STATEMENT periodically to check whether the number of executing SQL statements is increasing. V$STATEMENT shows only the SQL statements of the currently connected sessions, so the value is not exact, but it helps to identify the overall trend.

SELECT COUNT(*) FROM V$STATEMENT;

If the SQL statements have increased because of business needs, no separate action is required. However, SQL statements often increase because of how application programs are written, so check the following two items together with the developers.

Check Whether Statement Objects Are Closed#

An application program must close a Statement object after executing the SQL statement. The following is an example of closing a Statement object in JDBC.

// Java (JDBC) example
Connection cn;
PreparedStatement ps;
ps = cn.prepareStatement("SELECT ...");
// ... (omitted) ...
ps.close();

If ps.close() is not called, Altibase keeps the information of the SQL statement in internal modules such as Query_Prepare. If this happens repeatedly, memory usage keeps increasing until the session ends. Even when the memory is freed at the end of the session, the OS does not reclaim it immediately, so the Altibase process keeps running with the increased memory usage. Therefore, check that the application program closes every Statement object it uses.

Repeated Execution of SQL Statements That Differ Only in Constant Values#

The following SQL statements differ only in the constant values of their conditions.

SELECT * FROM t1 WHERE c1 = 1;
SELECT * FROM t1 WHERE c1 = 2;
SELECT * FROM t1 WHERE c1 = 3;

Altibase analyzes these SQL statements as different SQL statements and allocates memory for each one, so memory usage increases as such SQL statements keep running. If the application program is written to use a bind variable, as in SELECT * FROM t1 WHERE c1 = ?, and to change only the variable value, they are processed as a single SQL statement.

Step 5: Check Large Change Operations#

As explained in MVCC and Aging, a large change operation on a memory table creates as many copies as the number of records it changes, which increases the usage of the table.

From the usage by table in Step 3, pick out the tables with an excessive difference between alloc and used, and check whether a large change operation was performed on them. If the usage is abnormal compared with the workload, resolve it by returning empty pages or restarting Altibase.

Do not change a large number of records at once; divide the change into units of a fixed size. For example, to change 10 million records, change tens of thousands of records at a time with the LIMIT clause of the UPDATE statement and commit after each change.

Step 6: Check the Aging Status of the GC#

If the GC cannot delete the deletion targets for a long time, the deletion targets accumulate and memory usage increases. Check whether the GC is performing aging normally by querying V$MEMGC.

SELECT ADD_OID_CNT, GC_OID_CNT FROM V$MEMGC;

ADD_OID_CNT is the number of transactions that handed aging targets over to the GC, and GC_OID_CNT is the number of times the GC processed aging targets. If both values keep increasing, the GC is working normally even if processing is slow. However, if GC_OID_CNT stops increasing at some point, the GC is unable to proceed with aging.

The GC cannot proceed with aging when a transaction that still references the deletion targets keeps running without committing or rolling back. Aging resumes only after that transaction ends, so use the following query on V$TRANSACTION and V$STATEMENT to find the queries that the transaction is executing.

SELECT a.SESSION_ID
     , b.QUERY
  FROM V$TRANSACTION a
     , V$STATEMENT b
     , V$MEMGC c
 WHERE a.ID = b.TX_ID
   AND (a.MEMORY_VIEW_SCN = c.MINMEMSCNINTXS
        OR a.MIN_MEMORY_LOB_VIEW_SCN = c.MINMEMSCNINTXS);

If a query found this way takes too long, tune it. If an application program did not end the transaction properly after executing a query, check the source of the program and modify it to commit or roll back the transaction.

For other queries that check the GC gap and find the queries of the transactions the GC is waiting for, see [GC01] Memory Database GC Gap and [GC02] Queries in the Transaction That Memory Database GC Is Waiting For in the Altibase Monitoring Queries Guide.