The memory that has been increased due to processes other than the memory of Altibas is not covered in this document. However, this chapter will describe how to deal with the increase of Altibase process memory.
As mentioned in the previous chapter, the memory increase of Altibase can be organized as follows:
- When data in the memory table is increased
- When the number of executing SQL statements increases
- When it is increased by the MVCC (Multi-Version Concurrency Control) technique
- When the deletion of aging target information is delayed
1. When data in the memory table is increased
The trend of memory data usage increase can be checked with the SQL statement below.
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;
Among the results from the above query, 'Alloc' includes both space where data is stored and the available space. ('Used' means only space where data is stored.)
If the user has deleted around '900M' after loading '1G' of data, the table will have '1G' allocated space, '100M' of used space, and the remaining '900M' will be empty space for that table only.
The part that most ALTIBASE users confuse is the part about '900M'. Since the data has been deleted,' users tend to be confused about whether it is not normal if this space is 'Free' in the ALTIBASE or OS system and the memory usage is reduced.
Most of the memory management structure of the OS does not immediately return to the OS even if the program is free for once allocated memory. In practice, an algorithm that allocates to a reserved area on the more and uses the free area when another application program runs out of memory is generally used. In other words, because the cost of the process of allocating and freeing memory is high, frequent memory allocation/free is not possible.
This algorithmic concept applies equally to Altibase. Altibase also keeps the used spaces as space to be resued in the table even if data is deleted.
If the user finds such a table, the action taken is as follows.
Compaction the table
Memory tables are allocated space from the memory tablespace. However, if space increases due to loading/deleting a large amount of data in one table, the space to be allocated to other tables in the same memory tablespace may become insufficient. The table can be returned so that it can be used, and this is called compaction.Restart Altiabse
By taking the above actions, the primary action is possible so that other tables in the same memory tablespace can be allocated space. However, the memory usage occupied by the OS still does not decrease. In this case, restarting Altibase can have the same effect as reducing memory usage, which is possible for the following reason.
In the restart process, the data pages of all memory tablespaces are loaded into memory, but empty pages that are not actually written to are not loaded into memory, thus reducing memory usage.
If it is difficult for the user to perform compaction on each table, the compaction effect can be seen by restarting Altibase in the regular PM process.
2. When the number of executing SQL statements increases
The user can check periodically with the number of queries recorded in v$statement. The queries recorded in v$statement are not accurate because only the queries executed in the currently connected session are recorded, but it is helpful to figure out the approximate number.
In this case, there is no action for the user to take as it is an increase in inquiries necessary for business purposes, but there are cases where the following items are checked with the developer.
Check whether all queries are closed after use
In general, after a query is used, the object for the query must be closed.Ex) JAVA/JDBC
Connection cn; prepareStatement ps; ps = cn.prepareStatement ("select…"); … ps.close();If a statement such as 'ps.close' does not exist in the above code, Altibase internal models such as "Query_Prepare" will continue to maintain the memory for the query, and if this situation is repeated, the memory will continue to be used unless the session is terminated.
Even if Altibase frees the memory once used, it is not immediately recovered from the actual OS, and as a result, the memory usage is increased. Therefore, it is necessary to check whether the object used for query execution is normally released.Check when similar queries are still used
Consider the following example.The above query can be solved with one statement by replacing it with a phrase such as “SELECT 1 FROM DUAL WHERE C1 = ?” and developing it in a form that only changes the variable value of the query condition.
If similar queries as above are accumulated and executed, the current version of Altibase allocates memory by analyzing each query as a different query.
3. When it is increased by the MVCC (Multi-Version Concurrency Control) Technique
The MVCC technique refers to a general concurrency control method of DBMS that improves performance by preventing inquiry/change operations from waiting for each other. Currently, the Altibase memory table operates in the form of creating a copy of a record at the time of change operation and recording the changed information in the copy.
Therefore, when the record is changed, the original and the duplicated exist together. (At the time of deletion, there is no increase in memory as above, and an increase may occur due to aging delay.)
This means that when a large amount of change operation is performed, as many copies as the number of records subject to change work are created. (It has a trade-off relationship that increases the use of resources as fast as performance.)
This copy is created in the space of the table. Therefore, when a large amount of change operation occurs, the usage of the table increases. If there is empty space in the table, the corresponding space is used, and if there is not enough space, space is allocated from the tablespace and used.
The user should use the usage result for each table to select tables with Alloc/Used differences and resolve it when an abnormal usage is found in the operation. As a countermeasure, the user should use a method such as table compaction or restart. Also, since a large amount of change operation in the DBMS work process is not recommended in general, it is recommended to perform change operations in units of a certain record.
For example, if it has to change to 10 million records, divide them into units of tens of thousands of records and perform the change. (Using the LIMIT clause)
4. When the deletion of aging target information is delayed.
The original record is subject to deletion because the clone created by the MVCC described above will become the final record at the time of commit.
If a rollback occurs, the duplicate is subject to deletion. (These records to be deleted are called “Old Version” or “Garbage Data”, and the process of deletion is internally used by the term aging, and an internal module called GC (Garbage Collector) is in charge.)
The problem is that there may be cases in which the deletion targets cannot be deleted for some reason. If this state persists, the deletion targets will continue to accumulate, which may cause an increase in memory usage.
Whether or not GC aging can be checked with the following query.
“Add_oid_cnt” refers to the target that transactions hand over to the aging target.
That is, it is the same as the number of transactions that requested aging, and “gc_oid_cnt” means the number of aging targets processed by GC.
If the above figures are constantly changing, it can be said that GC is operating normally even if there is a delay in processing speed. However, if “gc_oid_cnt” does not increase at a specific point in time, it can be considered that a problem has occurred.
The GC module can delete the record only if the record to be deleted is not referenced by any transaction, and the delete target list can be deleted only in the order requested.
Therefore, there is a problem with the GC, this is a type of problem in which a certain transaction still remains unfinished, so it is necessary to find and clean up the corresponding transaction.
These queries can be found with the following queries.
When the above query is executed, the query information that has a problem including the query is displayed.
If a query other than the monitoring query is displayed, tuning if the query execution or if the user did not terminate normally after executing the query, check the source of the program and take measures to enable the normal termination.
※ Memory increase related to MVCC and GC is limited to the memory table.