Property

Properties are the settings used by the Machbase server, stored as key-value pairs in the $MACHBASE_HOME/conf/machbase.conf file.

These values are set when the Machbase server starts and are used continuously at runtime. To change them for performance tuning, you must understand what each value means and set it carefully.

Index

• Index
• CPU_AFFINITY_BEGIN_ID
• CPU_AFFINITY_COUNT
• CPU_COUNT
• CPU_PARALLEL
• DBS_PATH
• DEFAULT_LSM_MAX_LEVEL
• DISK_BUFFER_COUNT
• DISK_COLUMNAR_INDEX_CHECKPOINT_INTERVAL_SEC
• DISK_COLUMNAR_INDEX_FDCACHE_COUNT
• DISK_COLUMNAR_INDEX_SHUTDOWN_BUILD_FINISH
• DISK_COLUMNAR_PAGE_CACHE_MAX_SIZE
• DISK_COLUMNAR_TABLE_CHECKPOINT_INTERVAL_SEC
• DISK_COLUMNAR_TABLE_COLUMN_FDCACHE_COUNT
• DISK_COLUMNAR_TABLE_COLUMN_MINMAX_CACHE_SIZE
• DISK_COLUMNAR_TABLE_COLUMN_PART_FLUSH_MODE
• DISK_COLUMNAR_TABLE_COLUMN_PART_IO_INTERVAL_MIN_SEC
• DISK_COLUMNAR_TABLE_TIME_INVERSION_MODE
• DISK_COLUMNAR_TABLESPACE_DWFILE_EXT_SIZE
• DISK_COLUMNAR_TABLESPACE_DWFILE_INT_SIZE
• DISK_COLUMNAR_TABLESPACE_MEMORY_EXT_SIZE
• DISK_COLUMNAR_TABLESPACE_MEMORY_MAX_SIZE
• DISK_COLUMNAR_TABLESPACE_MEMORY_MIN_SIZE
• DISK_COLUMNAR_TABLESPACE_MEMORY_SLOWDOWN_HIGH_LIMIT_PCT
• DISK_COLUMNAR_TABLESPACE_MEMORY_SLOWDOWN_MSEC
• DISK_IO_THREAD_COUNT
• DISK_TABLESPACE_DIRECT_IO_FSYNC
• DISK_TABLESPACE_DIRECT_IO_READ
• DISK_TABLESPACE_DIRECT_IO_WRITE
• DISK_TABLESPACE_SYNCHRONOUS
• DUMP_APPEND_ERROR
• DUMP_TRACE_INFO
• DURATION_BEGIN
• DURATION_GAP
• ENABLE_CASE_SENSITIVE_PASSWORD
• FEEDBACK_APPEND_ERROR
• GEN_CALLSTACK_FOR_ABORT_ERROR
• GEN_CORE_FILE
• GRANT_REMOTE_ACCESS
• BIND_IP_ADDRESS
• HTTP_AUTH
• HTTP_ENABLE
• HTTP_MAX_MEM
• HTTP_PORT_NO
• HTTP_THREAD_COUNT
• INDEX_BUILD_MAX_ROW_COUNT_PER_THREAD
• INDEX_BUILD_THREAD_COUNT
• INDEX_FLUSH_MAX_REQUEST_COUNT_PER_INDEX
• INDEX_LEVEL_PARTITION_AGER_THREAD_COUNT
• INDEX_LEVEL_PARTITION_BUILD_MEMORY_HIGH_LIMIT_PCT
• INDEX_LEVEL_PARTITION_BUILD_THREAD_COUNT
• LIN_HASH_BIT_SIZE
• LOOKUP_APPEND_UPDATE_ON_DUPKEY
• MAX_QPX_MEM
• MAX_SESSION_COUNT
• MAX_STMT_COUNT_PER_SESSION
• SESSION_IDLE_TIMEOUT_SEC
• SESSION_QUERY_TIMEOUT_SEC
• MEMORY_ROW_TEMP_TABLE_PAGESIZE
• PID_PATH
• PORT_NO
• PROCESS_MAX_SIZE
• PVO_CACHE_ENABLE
• PVO_CACHE_SHARD_COUNT
• PVO_CACHE_MAX_MEMORY_SIZE
• PVO_CACHE_MAX_PLANS_PER_SQL
• PVO_CACHE_MAX_SQL_ENTRIES
• QUERY_PARALLEL_FACTOR
• ROLLUP_FETCH_COUNT_LIMIT
• RS_CACHE_APPROXIMATE_RESULT_ENABLE
• RS_CACHE_ENABLE
• RS_CACHE_MAX_MEMORY_PER_QUERY
• RS_CACHE_MAX_MEMORY_SIZE
• RS_CACHE_MAX_RECORD_PER_QUERY
• RS_CACHE_TIME_BOUND_MSEC
• SHOW_HIDDEN_COLS
• TABLE_SCAN_DIRECTION
• TAG_CACHE_ENABLE
• TAG_CACHE_MAX_MEMORY_SIZE
• TAG_CACHE_POOL_COUNT
• TAG_MEMORY_INDEX_TYPE
• TAG_MEMORY_INDEX_PANOUT
• TAGDATA_AUTO_META_INSERT
• TAG_TABLE_META_MAX_SIZE
• TAG_PARTITION_COUNT
• TAG_DATA_PART_SIZE
• TRACE_LOGFILE_COUNT
• TRACE_LOGFILE_PATH
• TRACE_LOGFILE_SIZE
• TRACE_LOG_LEVEL
• UNIX_PATH
• VOLATILE_TABLESPACE_MEMORY_MAX_SIZE

CPU_AFFINITY_BEGIN_ID

The starting number of the CPUs used by the Machbase server. It is used to control the CPU usage of the Machbase server.

CPU_AFFINITY_COUNT

The number of CPUs that the Machbase server uses. If set to 0, the Machbase server uses all CPUs.

CPU_COUNT

Specifies the number of CPUs set in the system. Machbase determines the number of threads based on this value. If set to 0, all CPUs in the system are used.

CPU_PARALLEL

Specifies the number of threads to create per CPU. If this value is 2 and the number of CPUs is 2, two parallel threads are created per CPU, so the number of parallel processing threads is four. If this value is too large, memory can be consumed quickly.

DBS_PATH

Specifies the path where the basic data of the Machbase server is stored. The default is ?/dbs, which means $MACHBASE_HOME/dbs.

DEFAULT_LSM_MAX_LEVEL

Sets the default level of the LSM index. If you do not specify a MAX_LEVEL value when creating an index, this value applies.

DISK_BUFFER_COUNT

Specifies the number of buffers for disk I/O.

DISK_COLUMNAR_INDEX_CHECKPOINT_INTERVAL_SEC

Sets the checkpoint interval for indexes. If set too long, errors may occur during index builds.

DISK_COLUMNAR_INDEX_FDCACHE_COUNT

Specifies the number of open index partition file descriptors.

DISK_COLUMNAR_INDEX_SHUTDOWN_BUILD_FINISH

Sets whether to write all index information to disk when the Machbase server shuts down. If set to 1, the server writes all index information to disk before it shuts down, so shutdown can take longer.

DISK_COLUMNAR_PAGE_CACHE_MAX_SIZE

Sets the maximum size of the page cache.

DISK_COLUMNAR_TABLE_CHECKPOINT_INTERVAL_SEC

Sets the checkpoint interval for table data. If this value is too large, recovery at restart takes much longer. If it is too small, I/O occurs frequently and overall performance may degrade.

DISK_COLUMNAR_TABLE_COLUMN_FDCACHE_COUNT

Specifies the maximum number of open file descriptors for column data in tables.

DISK_COLUMNAR_TABLE_COLUMN_MINMAX_CACHE_SIZE

Sets the size of the default MINMAX cache set on the _ARRIVAL_TIME column.

DISK_COLUMNAR_TABLE_COLUMN_PART_FLUSH_MODE

Sets whether column partitions are flushed only when they are full.

DISK_COLUMNAR_TABLE_COLUMN_PART_IO_INTERVAL_MIN_SEC

Sets the interval at which partition files are written to disk. When more data is input than the configured number of partitions, it is written to disk regardless of this interval.

DISK_COLUMNAR_TABLE_TIME_INVERSION_MODE

If set to 1, input is allowed even if the value of the _ARRIVAL_TIME column decreases. If set to 0, a value smaller than the maximum value of the _ARRIVAL_TIME column is treated as an error.

DISK_COLUMNAR_TABLESPACE_DWFILE_EXT_SIZE

Specifies how much the double write file used for recovery at startup grows at a time.

DISK_COLUMNAR_TABLESPACE_DWFILE_INT_SIZE

Specifies the amount of space the double write file reserves when it is created.

DISK_COLUMNAR_TABLESPACE_MEMORY_EXT_SIZE

Specifies the block size of the memory reserved for column partitions.

DISK_COLUMNAR_TABLESPACE_MEMORY_MAX_SIZE

Specifies the maximum amount of memory allocated by log tables. If the server allocates more memory than this value, memory allocation waits until memory usage drops below this value, so performance degrades. It is recommended to set this value to 50~80% of physical memory.

DISK_COLUMNAR_TABLESPACE_MEMORY_MIN_SIZE

When the Machbase server starts, it pre-allocates this amount of memory to prevent performance degradation caused by memory allocation. Since this memory is used only as a data input buffer, it is recommended to use it only when memory is sufficient.

DISK_COLUMNAR_TABLESPACE_MEMORY_SLOWDOWN_HIGH_LIMIT_PCT

When data is input to log tables, input performance is throttled if the memory usage for column data files exceeds the limit calculated with this value as follows.

DISK_COLUMNAR_TABLESPACE_MEMORY_MAX_SIZE * (DISK_COLUMNAR_TABLESPACE_MEMORY_SLOWDOWN_HIGH_LIMIT_PCT / 100)

DISK_COLUMNAR_TABLESPACE_MEMORY_SLOWDOWN_MSEC

Sets the wait time applied to each record input when the memory usage for column data files exceeds the limit.

DISK_IO_THREAD_COUNT

Sets the number of I/O threads that write data to disk.

DISK_TABLESPACE_DIRECT_IO_FSYNC

When Direct I/O is used, fsync is unnecessary for data files. Disabling fsync while using Direct I/O (set to 0) improves data I/O performance. Without fsync, no data is lost in normal situations, but you must enable fsync if failures such as a power outage can occur.

DISK_TABLESPACE_DIRECT_IO_READ

Sets whether to use Direct I/O for data read operations.

DISK_TABLESPACE_DIRECT_IO_WRITE

Sets whether to use Direct I/O for data write operations. If the file system does not support Direct I/O (for example, ZFS), it must be set to 0.

DISK_TABLESPACE_SYNCHRONOUS

Sets the synchronization policy for disk tablespace files.

DUMP_APPEND_ERROR

If this value is set to 1, errors are recorded in the $MACHBASE_HOME/trc/machbase.trc file when the Append API fails. In this case, append performance can drop significantly, so it is recommended to use this only for testing.

To check for errors in a user application, it is helpful to use the SQLAppendSetErrorCallback API.

DUMP_TRACE_INFO

Sets the interval at which the server periodically records DBMS system status information in the machbase.trc file. If set to 0, the information is not recorded.

DURATION_BEGIN

Sets the start point of the default duration applied to SELECT statements that do not specify a DURATION clause: how many seconds before the current time the newer end of the search range is placed. If set to 60, the newer end of the search range is 60 seconds before the current time.

The default is 0. If both DURATION_BEGIN and DURATION_GAP are 0, no default time range is applied and all data is retrieved.

DURATION_GAP

Sets the period of the default duration applied to SELECT statements that do not specify a DURATION clause: the length, in seconds, of the search range going back from its newer end.

• If DURATION_BEGIN is 0 and DURATION_GAP is 60, data from 60 seconds ago up to the current time is retrieved.
• If both values are 60, data from 120 seconds ago up to 60 seconds ago is retrieved.

The default is 0. If both values are 0, all data is retrieved. To set a time range, specify a positive DURATION_GAP.

ENABLE_CASE_SENSITIVE_PASSWORD

Determines whether passwords are case-sensitive.

• 0: Case-insensitive. Passwords are converted to uppercase when a user is created or altered and during authentication.
• 1: Case-sensitive.

FEEDBACK_APPEND_ERROR

Sets whether to send error data to the client when an Append API error occurs. If 0, no error data is sent to the client. If 1, error information is sent to the client.

GEN_CALLSTACK_FOR_ABORT_ERROR

Sets whether to record call stacks after an abnormal server shutdown.

GEN_CORE_FILE

Sets whether to record core files after an abnormal server shutdown.

GRANT_REMOTE_ACCESS

Determines whether the database can be accessed remotely. If 0, remote connections are blocked.

BIND_IP_ADDRESS

Specifies the bind IP address for INET/HTTP listeners. The native INET listener uses this address only when GRANT_REMOTE_ACCESS=1; when GRANT_REMOTE_ACCESS=0, the native INET listener binds to loopback. The HTTP listener always uses BIND_IP_ADDRESS, so set this value to 127.0.0.1 if HTTP must be limited to loopback. 0.0.0.0 means all interfaces.

HTTP_AUTH

Sets whether Basic Authentication is enabled for the REST API service.

HTTP_ENABLE

Sets whether the REST API service is enabled.

HTTP_MAX_MEM

Sets the maximum memory per web session.

HTTP_PORT_NO

Sets the REST API port number.

HTTP_THREAD_COUNT

Sets the number of threads used by the Machbase web server.

INDEX_BUILD_MAX_ROW_COUNT_PER_THREAD

When the number of records not yet indexed reaches this value, the index build thread starts adding them to the index.

INDEX_BUILD_THREAD_COUNT

Specifies the number of index build threads. If set to 0, no index is built.

INDEX_FLUSH_MAX_REQUEST_COUNT_PER_INDEX

Specifies the maximum number of flush requests per index.

INDEX_LEVEL_PARTITION_AGER_THREAD_COUNT

Specifies the number of threads that delete index files that are no longer needed when LSM indexes are built.

INDEX_LEVEL_PARTITION_BUILD_MEMORY_HIGH_LIMIT_PCT

Sets the maximum memory usage for LSM index builds as a percentage. The percentage is relative to the maximum memory usage of Machbase. If memory usage exceeds the limit, LSM partition merges are stopped.

INDEX_LEVEL_PARTITION_BUILD_THREAD_COUNT

Determines the number of threads that perform merge operations for LSM index builds.

LIN_HASH_BIT_SIZE

Controls the initial bucket bit width used by the internal linear hash. The type is UINT32. Adjusting this value can change the internal scan order of hash-based operations, so the output order of queries without an explicit ORDER BY may differ from previous releases.

Verification SQL

SELECT name, value, type, min_value, max_value
  FROM v$property
 WHERE name = 'LIN_HASH_BIT_SIZE';

LOOKUP_APPEND_UPDATE_ON_DUPKEY

Specifies how to handle a duplicate primary key when appending to a lookup table.

• 0: Append fails.
• 1: The row for the corresponding primary key is updated.

MAX_QPX_MEM

Sets the maximum amount of memory the query processor uses to execute GROUP BY, DISTINCT, and ORDER BY clauses. If a query uses more memory than this value, the query is canceled. An error message is then sent to the client, and the details are recorded in the machbase.trc file.

MAX_SESSION_COUNT

Sets the maximum number of concurrent sessions. When exceeded, new sessions are rejected.

MAX_STMT_COUNT_PER_SESSION

Sets the maximum number of statements allowed per session. Statement creation fails when the limit is exceeded.

SESSION_IDLE_TIMEOUT_SEC

Sets the maximum idle time for a session in seconds. If the idle time exceeds this value, the connection is closed. 0 disables the idle timeout.

SESSION_QUERY_TIMEOUT_SEC

Sets the maximum query execution time in seconds. If the query time exceeds this value, the query is canceled. 0 disables the query timeout.

MEMORY_ROW_TEMP_TABLE_PAGESIZE

Sets the page size of the temporary tablespace for volatile tables and lookup tables. Because records of volatile and lookup tables are stored in these pages, the value must be larger than the maximum record size of volatile tables. To store N records in one page, set this value to the maximum record size * N.

PID_PATH

Specifies the location where the PID file of the Machbase server process is written. The default is ?/conf, which means $MACHBASE_HOME/conf.

PORT_NO

Specifies the TCP/IP port that the Machbase server process uses to communicate with clients. The default is 5656.

PROCESS_MAX_SIZE

Specifies the maximum memory size used by machbased, the Machbase server process. If the server tries to use more memory than this limit, it reduces memory usage as follows.

• Stops data input or treats it as an error.
• Slows down index builds.

In this case, performance degrades greatly, so find and fix the cause of the excessive memory usage.

PVO_CACHE_ENABLE

Turns the global PVO statement cache on or off. Available only in the Standard edition.

PVO_CACHE_SHARD_COUNT

Sets the number of shards for the PVO statement cache. It is applied only at initialization, so changing it requires a server restart; it cannot be changed at runtime.

PVO_CACHE_MAX_MEMORY_SIZE

Sets the total memory budget (bytes) for the PVO statement cache. The value is distributed evenly across shards. It can be changed at runtime.

PVO_CACHE_MAX_PLANS_PER_SQL

Sets the maximum number of cached plans (handles) per SQL statement. It can be changed at runtime.

PVO_CACHE_MAX_SQL_ENTRIES

Limits the number of SQL entries stored in the PVO statement cache; 0 means unlimited. The value is distributed across shards, and it can be changed at runtime.

QUERY_PARALLEL_FACTOR

Specifies the number of execution threads of the parallel query executor. The default is 0 for Standard builds and 4 for Cluster builds.

ROLLUP_FETCH_COUNT_LIMIT

Limits the amount of data the rollup thread fetches at one time.

If set to 0, there is no limit.

RS_CACHE_APPROXIMATE_RESULT_ENABLE

Determines whether to use the approximate result mode of the result cache. If this value is 1, approximate values are returned when the result cache is used (very fast, but the data may be inaccurate). If it is 0, exact values are returned.

RS_CACHE_ENABLE

Determines whether to use the result cache.

RS_CACHE_MAX_MEMORY_PER_QUERY

Sets the amount of result cache memory that the result of a single query can use. If the memory usage of a query result exceeds this value, that result is not stored in the result cache.

RS_CACHE_MAX_MEMORY_SIZE

Specifies the maximum memory usage of the result cache.

RS_CACHE_MAX_RECORD_PER_QUERY

The maximum number of records stored in the result cache. If a query returns more records than this value, its result is not stored in the cache.

RS_CACHE_TIME_BOUND_MSEC

Results of queries that run very quickly are better left out of the result cache, because this reduces memory usage.

This value determines how fast a query must run for its result not to be cached. When set to 0, all query results are stored in the result cache.

SHOW_HIDDEN_COLS

With the default value 0, the _ARRIVAL_TIME column is not displayed by a SELECT * FROM query. If this value is set to 1, the column is displayed.

TABLE_SCAN_DIRECTION

Sets the scan direction of tag tables. The value is one of -1, 0, and 1, and the default is 0.

• -1: Reverse scan
• 0: Tag Table (forward scan), Log Table (reverse scan)
• 1: Forward scan

TAG_CACHE_ENABLE

Enables the key-value (TAG) table cache by bitwise OR flags.

• 0: Disable cache
• 1: TAG map cache
• 2: Row data cache
• 4: Data file cache
• 8: External VARCHAR file cache
• 16: Delete vector cache

TAG_CACHE_MAX_MEMORY_SIZE

Sets the maximum memory size (bytes) per TAG cache pool. The total cache limit is TAG_CACHE_MAX_MEMORY_SIZE * TAG_CACHE_POOL_COUNT.

TAG_CACHE_POOL_COUNT

Sets the number of TAG cache pools.

TAG_MEMORY_INDEX_TYPE

Selects the memory index type for TAG tables.

• 0: RBTree
• 1: BTree

TAG_MEMORY_INDEX_PANOUT

Sets the B-Tree order (fanout) for the TAG memory index. Effective when TAG_MEMORY_INDEX_TYPE=1.

TAGDATA_AUTO_META_INSERT

Specifies how to handle data input through APPEND/INSERT into the TAGDATA table when there is no matching TAG_NAME.

• 0: Input fails.
• 1: The TAG_NAME value is inserted. If there are additional metadata columns, their values are all entered as NULL.
• 2: The TAG_NAME value is inserted together with the additional metadata column values.
  • This setting is valid only for APPEND. INSERT works like 1 because it cannot enter additional metadata column values.
  • With this setting, APPEND must always use APPEND parameters that include the metadata column values.

TAG_TABLE_META_MAX_SIZE

Sets the maximum size of memory used to store the metadata area when a TAGDATA table is created.

TAG_PARTITION_COUNT

Specifies the number of key-value tables that make up a tag table.

TAG_DATA_PART_SIZE

Determines the partition size of tag data storage.

TRACE_LOGFILE_COUNT

Specifies the maximum number of log trace files created in TRACE_LOGFILE_PATH. To save disk space, the oldest log file is deleted when more log files than the maximum are created.

When the oldest file is deleted because the maximum number is exceeded, the name of the deleted file is reused for the newest log file.

TRACE_LOGFILE_PATH

Sets the path of the log trace files (machbase.trc, machadmin.trc, machsql.trc). These files continuously record internal information when Machbase starts, stops, and runs. The default ?/trc means $MACHBASE_HOME/trc.

TRACE_LOGFILE_SIZE

Sets the maximum size of a log trace file. If more data than this size needs to be recorded, a new log file is created.

TRACE_LOG_LEVEL

Sets the trace log detail level. Higher values write more detailed logs.

UNIX_PATH

Sets the Unix domain socket name.

VOLATILE_TABLESPACE_MEMORY_MAX_SIZE

Sets the limit on the total memory usage of all volatile and lookup tables in the system.