Altibase Configuration File Guide#
Overview#
This document describes the main properties in the Altibase configuration file, altibase.properties, and how to set them.
This document is based on Altibase 7.1 and 7.3. We recommend reading it together with the System Data Capacity Estimation Guide for Altibase Operations and the Altibase Replication Configuration Guide.
Info
For errors and improvements related to this document, please contact the technical support portal or technical support center.
- Technical support portal: http://support.altibase.com/en
- Technical support center: +82-2-2082-1114
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.
Altibase Configuration Items#
For details on the properties of each version, see the General Reference in the Altibase Manual.
Querying and Setting Properties#
Query property values as follows.
iSQL> SELECT NAME, VALUE1 FROM V$PROPERTY;
You can also check $ALTIBASE_HOME/conf/altibase.properties, but the file contains only some of the properties. To check all properties and the values currently in effect, query them with SQL as shown above.
Property values are changed in the configuration file or with the ALTER SYSTEM or ALTER SESSION statement.
The ALTER statements change a value immediately while the server is running. Read-only properties cannot be changed during operation, and attempting to change one with an ALTER statement returns the following error.
[ERR-0104E: The property [property_name] is read-only.]
In the configuration file altibase.properties, write each property in the following format.
# PROPERTY_NAME = value
PORT_NO = 20300
The following example changes a property with ALTER statements. Property names are case-insensitive.
iSQL> ALTER SYSTEM SET QUERY_TIMEOUT = 30;
iSQL> ALTER SESSION SET QUERY_TIMEOUT = 30;
Depending on the property, it can be changed at the session level or the system level. In either case, the value reverts to the one in altibase.properties when Altibase restarts, so to make a change permanent, update the configuration file as well. Some properties cannot be changed after the database is created; see Properties That Cannot Be Changed After Creating a Database.
A "?" in a path value in the configuration file stands for the path defined in the $ALTIBASE_HOME environment variable.
Altibase Configuration Recommendations#
This section groups the properties in $ALTIBASE_HOME/conf/altibase.properties by category and describes recommended settings. Because user systems vary widely, these recommendations are based on general experience. Consult an Altibase engineer before changing the configuration file.
Properties to Set When Creating a Database#
Before creating a database, set the following properties to suit your system environment. The example settings assume the following disk volume layout.
| Disk volume | Description |
|---|---|
| /home/altibase | Altibase installation directory |
| /home/altidata | Directory for data files |
| /home/altilog | Directory for log files |
| /home/arch_log | Directory for archive log files |
| Property | Default | Description | Example |
|---|---|---|---|
| DB_NAME | mydb | Database name | Any name |
| MEM_DB_DIR | ?/dbs | Directory for the checkpoint image files of the memory database | /home/altidata |
| DEFAULT_DISK_DB_DIR | ?/dbs | Directory for the data files of the disk database | /home/altidata |
| LOGANCHOR_DIR | ?/logs | Directory for log anchor files | /home/altilog |
| LOG_DIR | ?/logs | Directory for log files | /home/altilog |
| ARCHIVE_DIR | ?/arch_logs | Directory for archive log files in archive log mode | /home/arch_log |
| MEM_MAX_DB_SIZE | 2G | Maximum size of the memory database Set it based on capacity planning. About 50% of physical memory is generally recommended. |
8G |
| BUFFER_AREA_SIZE | 128M | Size of the buffer for disk tables If physical memory allows, allocate a sufficient size for performance. |
1G |
| PORT_NO | 20300 | Port number for client connections Specify a port not used by any other process on the system. |
20300 |
| AUTO_COMMIT | 1 | Whether to commit automatically If 1, each DML statement is committed automatically. To control transactions explicitly, set it to 0. A value set in the session takes precedence over the configuration file. |
1 |
| SQL_PLAN_CACHE_SIZE | 64M | Maximum size of the SQL plan cache The more SQL statements are executed repeatedly, the more memory it saves. |
64M |
| REPLICATION_PORT_NO | 0 | Replication port number Specify it when using replication. |
30300 |
Properties That Cannot Be Changed After Creating a Database#
If any of the following properties is changed after the database is created, Altibase does not start normally. To change these values, you must create a new database and migrate all data.
| Property | Default | Description |
|---|---|---|
| DB_NAME | mydb | Specified when creating the database |
| LOG_FILE_SIZE | Altibase 7.1: 10M Altibase 7.3: 100M |
Specified when creating the database |
| EXPAND_CHUNK_PAGE_COUNT | 128 | Specified when creating the database |
| TRANSACTION_TABLE_SIZE | 1024 | Can only be increased |
| CHARACTERSET | - | Specified when creating the database |
| NATIONAL CHARACTERSET | - | Specified when creating the database |
File Path Properties#
This section covers the paths of log files and data files. Placing log files and data files on the same disk concentrates disk I/O and degrades performance. Referring to the Configuration Guide For Minimizing Disk I/O Contention, we recommend placing data files at least on a disk physically separate from LOG_DIR.
| Property | Default | Description |
|---|---|---|
| LOG_DIR | ?/logs | Directory that stores log files (transaction log files) |
| MEM_DB_DIR | ?/dbs | Directory that stores the checkpoint image files of the memory database |
| DEFAULT_DISK_DB_DIR | ?/dbs | Directory that stores the data files of the disk database |
Session Properties#
The session-related properties are as follows.
| Property | Default | Description |
|---|---|---|
| MAX_CLIENT | 1000 | Maximum number of concurrent sessions. If more sessions are expected, increase the value and restart the Altibase server. See Notes/Considerations when increasing the number of concurrent connection sessions (MAX_CLIENT). |
| MULTIPLEXING_THREAD_COUNT | Number of logical cores on the host e.g., 16 on a host with 16 logical cores |
Specifies the number of service threads. If not set, as many service threads as logical cores are created at startup. If more service threads are needed because many sessions are connected, the server creates them automatically, but redistributing work at that time can cause temporary performance jitter. It is therefore better to secure enough threads in advance. About twice the number of CPU cores is generally recommended; adjust it to suit the system. |
Resource Limit Properties#
Resources used by Altibase include physical memory, disk space, and logical tablespace space. This section describes properties that limit, at the session or system level, queries that can exhaust resources, such as bulk changes.
Rather than changing the defaults, we recommend changing these properties at the session level only in the sessions that need it.
| Property | Default | Description |
|---|---|---|
| LOCK_ESCALATION_MEMORY_SIZE | 100M | When a bulk change is performed on a memory table and the size of the changed records exceeds this value, a table lock is acquired before the query is executed. Can be changed only at the system level. |
| TRX_UPDATE_MAX_LOGSIZE | 10M | If the amount of log generated by a query reaches this value, the following error is returned.The update log size '10485873' is bigger than TRX_UPDATE_MAX_LOGSIZE '10485760' |
| PREPARE_STMT_MEMORY_MAXIMUM | 200M | If the memory used in the prepare phase of a query reaches this value, the following error is returned.The memory size allocated for the statement has exceeded the maximum limit ( Name : Query_Prepare, Wanted Memory Size : 1073741832, Max size : 1073741824 ) |
| EXECUTE_STMT_MEMORY_MAXIMUM | Altibase 7.1: 1024M Altibase 7.3: 2048M |
If the memory used in the execute phase of a query reaches this value, the following error is returned.The memory size allocated for the statement has exceeded the maximum limit ( Name : Query_Execute, Wanted Memory Size : 1073807360, Max size : 1073741824 ) |
| QUERY_TIMEOUT | 600 | If a query runs longer than this value, the following error is returned. The unit is seconds.Client's query exceeded the execution time limit. |
| FETCH_TIMEOUT | 60 | If the interval between client requests while fetching query results exceeds this value, the session is terminated. The unit is seconds.The session has been closed by the server |
| UTRANS_TIMEOUT | 3600 | If neither COMMIT nor ROLLBACK is executed within this time after a change query, the session is terminated. The unit is seconds.The transaction has exceeded the lock timeout specified by the user. |
| IDLE_TIMEOUT | 0 | If a session stays idle for this time, it is forcibly terminated. The unit is seconds; 0 means no limit.The session has been closed by the server |
The following example changes property values temporarily.
iSQL> ALTER SESSION SET TRX_UPDATE_MAX_LOGSIZE = 20000000;
iSQL> ALTER SYSTEM SET TRX_UPDATE_MAX_LOGSIZE = 10000000;
iSQL> ALTER SESSION SET QUERY_TIMEOUT = 3600;
iSQL> ALTER SESSION SET UTRANS_TIMEOUT = 60;
Errors caused by QUERY_TIMEOUT, IDLE_TIMEOUT, UTRANS_TIMEOUT, and FETCH_TIMEOUT are all recorded in altibase_boot.log. When one occurs, find the session in this log and take action.
A value changed with ALTER SYSTEM applies to sessions that connect afterwards, not to sessions already connected. To apply it to existing sessions, execute ALTER SESSION in each of them.
Disk I/O Performance Properties#
The properties related to disk I/O performance are as follows.
| Property | Default | Description |
|---|---|---|
| BUFFER_AREA_SIZE | 128M | Specifies the buffer size of the disk database. If system memory allows, set it as large as possible. |
| BUFFER_FLUSHER_CNT | 2 | Number of flusher threads that write dirty pages to disk to free buffer space. Adjust it to the number of CPUs and the disk I/O performance of the system. |
| PREPARE_LOG_FILE_COUNT | 5 | Number of empty log files that a separate thread creates in advance. If this value is too small, transactions may wait for empty log files to be created, degrading performance. If LF_PREPARE_WAIT_COUNT in V$LFG is larger than this value, increase it. Setting it too large increases the disk I/O load of creating empty log files, so change it carefully after testing. |
| CHECKPOINT_BULK_WRITE_PAGE_COUNT | 0 | If many pages must be written during a checkpoint of the memory database and the resulting disk I/O degrades performance, these properties spread out the checkpoint disk I/O. After writing the number of pages specified in CHECKPOINT_BULK_WRITE_PAGE_COUNT, the checkpoint waits for CHECKPOINT_BULK_WRITE_SLEEP_SEC plus CHECKPOINT_BULK_WRITE_SLEEP_USEC and then resumes writing. The unit of CHECKPOINT_BULK_WRITE_SLEEP_SEC is seconds, and that of CHECKPOINT_BULK_WRITE_SLEEP_USEC is microseconds. This is effective on machines with low disk performance. |
| CHECKPOINT_BULK_WRITE_SLEEP_SEC | 0 | |
| CHECKPOINT_BULK_WRITE_SLEEP_USEC | 0 | |
| DIRECT_IO_ENABLED | 1 | Writing to data files through the file cache writes data twice, to the file cache and to disk, and it is hard to predict when the file cache will be synchronized to disk. As a result, performance may drop unexpectedly when the operating system flushes the file cache. With direct I/O, a single write completes the operation. However, use it only when disk performance is sufficiently high. |
| DATABASE_IO_TYPE | 0 | |
| TOTAL_WA_SIZE | 128M | TOTAL_WA_SIZE is the maximum memory that can be allocated to sort and hash operations running concurrently in the disk database. Increasing SORT_AREA_SIZE and HASH_AREA_SIZE appropriately can improve disk database performance. |
| SORT_AREA_SIZE | 1M | |
| HASH_AREA_SIZE | 4M |
PBT (Problem Tracking) Properties#
The properties needed to trace the cause of a problem in detail are as follows.
| Property | Default | Description |
|---|---|---|
| QP_MSGLOG_FLAG | 2 | The default 2 records all DDL statements in altibase_qp.log. Useful for checking the DDL execution history. |
| RP_CONFLICT_MSGLOG_FLAG | 6 | The default 6 records both replication conflict messages and the SQL statements that caused the conflicts. |
| TIMED_STATISTICS | 0 | Specifies whether to measure wait times for wait events and execution times of SQL statements. 0 disables measurement and 1 enables it. When set to 1, the execution time of every SQL statement is recorded in V$STATEMENT, and the measurement overhead may reduce performance. |