System Parameters

This chapter provides information about configuring system parameters that can affect the system performance. System parameters determine overall performance and operation of the system. This chapter explains how to use configuration files for database server and broker as well as a description of each parameter.

This chapter covers the following topics:

  • Configuring the database server

  • Configuring the broker

Configuring the Database Server

Scope of Database Server Configuration

CUBRID consists of the database server, the broker and the CUBRID Manager. Each component has its configuration file. The system parameter configuration file for the database server is cubrid.conf located in the $CUBRID/conf directory. System parameters configured in cubrid.conf affect overall performance and operation of the database system. Therefore, it is very important to understand the database server configuration.

The CUBRID database server has a client/server architecture. To be more specific, it is divided into a database server process linked to the server library and the broker process linked to the client library. The server process manages the database storage structure and provides concurrency and transaction functionalities. The client process prepares for query execution and manages object/schema.

System parameters for the database server, which can be set in the cubrid.conf file, are classified into a client parameter, a server parameter and a client/server parameter according to the range to which they are applied. A client parameter is only applied to client processes such as the broker. A server parameter affects the behaviors of the server processes. A client/server parameter must be applied to both server and client.

Note

Location of cubrid.conf File and How It Works

The cubrid.conf file is located on the $CUBRID/conf directory. For setting by database, it divides into a section in the cubrid.conf file.

Changing Database Server Configuration

Editing the Configuration File

You can add/delete parameters or change parameter values by manually editing the system parameter configuration file (cubrid.conf) in the $CUBRID/conf directory.

The following parameter syntax rules are applied when configuring parameters in the configuration file:

  • Parameter names are not case-sensitive.

  • The name and value of a parameter must be entered in the same line.

  • An equal sign (=) can be to configure the parameter value. Spaces are allowed before and after the equal sign.

  • If the value of a parameter is a character string, enter the character string without quotes. However, use quotes if spaces are included in the character string.

Using SQL Statements

You can configure a parameter value by using SQL statements in the CSQL Interpreter or CUBRID Manager’s Query Editor. Note that you cannot change every parameter. For updatable parameters, see cubrid.conf Configuration File and Default Parameters.

SET SYSTEM PARAMETERS 'parameter_name=value [{; name=value}...]'

parameter_name is the name of a client parameter whose value is editable. In this syntax, value is the value of the given parameter. You can change multiple parameter values by separating them with a semicolon(;). You should be careful when you change a parameter.

The following example shows how to retrieve the result of an index scan in OID order and configure the number of queries to be stored in the history of the CSQL Interpreter to 70.

SET SYSTEM PARAMETERS 'index_scan_in_oid_order=1; csql_history_num=70';

DEFAULT for value will reset the parameter to its default value with an exception of call_stack_dump_activation_list parameter.

SET SYSTEM PARAMETERS 'lock_timeout=DEFAULT';

Using Session Commands of the CSQL Interpreter

You can configure system parameter values by using session commands (;SET) in the CSQL Interpreter. Note that you cannot change every parameter. For updatable parameters, see cubrid.conf Configuration File and Default Parameters.

The following example shows how to configure the block_ddl_statement parameter to 1 so that execution of DDL statements is not allowed.

csql> ;se block_ddl_statement=1
=== Set Param Input ===
block_ddl_statement=1

cubrid.conf Configuration File and Default Parameters

CUBRID consists of the database server, the broker and the CUBRID Manager. The name of the configuration file for each component is as follows. These files are all located in the $CUBRID/conf directory.

  • Database server configuration file: cubrid.conf

  • Broker configuration file: cubrid_broker.conf

  • CUBRID Manager server configuration file: cm.conf

cubrid.conf is a configuration file that sets system parameters for the CUBRID database server and determines overall performance and operation of the database system. In the cubrid.conf file, some important parameters needed for system installation are provided, having their default values.

Database Server System Parameters

The following are database server system parameters that can be used in the cubrid.conf configuration file. On the following table, “Applied” column’s “client parameter” means that they are applied to CAS, CSQL, cubrid utilities. Its “server parameter” means that they are applied to the DB server (cub_server process). For the scope of client and server parameters, see Scope of Database Server Configuration.

You can change the parameters that are capable of changing dynamically the setting value through the SET SYSTEM PARAMETERS statement or a session command of the CSQL Interpreter, ;set while running the DB. If you are a DBA, you can change parameters regardless of the applied classification. However, if you are not a DBA, you can only change “session” parameters. (on the below table, a parameter of which “session” item’s value is O.)

On the below table, if “Applied” is “server parameter”, that parameter affects to cub_server process; If “client parameter”, that parameter affects to CAS, CSQL or “cubrid” utilities which run on client/server mode (--CS-mode). “Client/server parameter” affects to all of cub_server, CAS, CSQL and “cubrid” utilities.

“Dynamic Change” and “Session or not” are marked on the below table. The affected range of the parameter which “Dynamic Change” is “available” depends on “Applied” and “Session” items.

  • If “Dynamic Change” is “available” and “Applied” is “server parameter”, that parameter’s changed value is applied to DB server. Then applications use the changed value of the parameter until the DB server is restarted.

  • If “Dynamic Change” is “available” and “Applied” is “client parameter”, this belongs to the “session” parameter and that parameter’s changed value is applied only to that DB session. In other words, the changed value is only applied to the application which requested to change that value. For example, if block_ddl_statement parameter’s value is changed into yes, then only the application requested to change that parameter cannot use DDL statements.

  • If “Dynamic Change” is “available”, “Applied” is “client parameter” and;

    • this belongs to the “session” parameter, that parameter’s changed value is applied only to that DB session. In other words, the changed value is only applied to the application requested to change that value. For example, if add_column_update_hard_default parameter’s value is changed into yes, then only the application requested to change that parameter lets the newly added column with NOT NULL constraint have hard default value.

    • this does not belong to the “session” parameter, the values of “client” side and “server” side are changed. For example, error_log_level parameter is applied to each of “server” side and “client” side; if this value is changed from “ERROR” into “WARNING”, this is applied only to “server” (cub_server process) and “client” (CAS or CSQL) which requested to change this value. Other “clients” keeps the value of “ERROR”.

Note

If you want to change the value of a parameter permanently, restart all of DB server and broker after changing configuration values of cubrid.conf.

Category

Parameter Name

Applied

Session

Type

Default Value

Dynamic Change

Connection-Related Parameters

cubrid_port_id

client parameter

int

1,523

check_peer_alive

client/server parameter

O

string

both

available

db_hosts

client parameter

O

string

NULL

available

max_clients

server parameter

int

100

tcp_keepalive

client/server parameter

bool

yes

use_user_hosts

client/server parameter

bool

off

Memory-Related Parameters

data_buffer_size

server parameter

byte

32,768 * db_page_size

index_scan_oid_buffer_size

server parameter

byte

4 * db_page_size

max_agg_hash_size

server parameter

byte

2,097,152(2M)

max_hash_list_scan_size

server parameter

byte

8,388,608(8M)

sort_buffer_size

server parameter

byte

128 * db_page_size

temp_file_memory_size_in_pages

server parameter

int

4

thread_stacksize

server parameter

byte

1,048,576

Disk-Related Parameters

db_volume_size

server parameter

byte

512M

dont_reuse_heap_file

server parameter

bool

no

log_volume_size

server parameter

byte

512M

temp_file_max_size_in_pages

server parameter

int

-1

temp_volume_path

server parameter

string

NULL

unfill_factor

server parameter

float

0.1

volume_extension_path

server parameter

string

NULL

double_write_buffer_size

server parameter

byte

2M

data_file_os_advise

server parameter

int

0

Error Message-Related Parameters

call_stack_dump_activation_list

client/server parameter

string

DEFAULT

DBA only

call_stack_dump_deactivation_list

client/server parameter

string

NULL

DBA only

call_stack_dump_on_error

client/server parameter

bool

no

DBA only

error_log

client/server parameter

string

cub_client.err, cub_server.err

error_log_level

client/server parameter

string

NOTIFICATION

DBA only

error_log_warning

client/server parameter

bool

no

DBA only

error_log_size

client/server parameter

int

512M

DBA only

Concurrency/Lock-Related Parameters

deadlock_detection_interval_in_secs

server parameter

float

1.0

DBA only

isolation_level

client parameter

O

int

4

available

lock_escalation

server parameter

int

100,000

lock_timeout

client parameter

O

msec

-1

available

rollback_on_lock_escalation

server parameter

bool

no

DBA only

Logging-Related Parameters

adaptive_flush_control

server parameter

bool

yes

DBA only

background_archiving

server parameter

bool

yes

DBA only

checkpoint_every_size

server parameter

byte

100,000 * log_page_size

checkpoint_interval

server parameter

msec

6min

DBA only

checkpoint_sleep_msecs

server parameter

msec

1

DBA only

force_remove_log_archives

server parameter

bool

yes

DBA only

log_buffer_size

server parameter

byte

16k * log_page_size

log_max_archives

server parameter

int

INT_MAX

DBA only

log_trace_flush_time

server parameter

msec

0

DBA only

max_flush_size_per_second

server parameter

byte

10,000 * db_page_size

DBA only

remove_log_archive_interval_in_secs

server parameter

sec

0

DBA only

sync_on_flush_size

server parameter

byte

200 * db_page_size

DBA only

ddl_audit_log

client parameter

bool

no

ddl_audit_log_size

client parameter

byte

10M

Transaction Processing-Related Parameters

async_commit

server parameter

bool

no

group_commit_interval_in_msecs

server parameter

msec

0

DBA only

Statement/Type-Related Parameters

add_column_update_hard_default

client/server parameter

O

bool

no

available

alter_table_change_type_strict

client/server parameter

O

bool

yes

available

allow_truncated_string

client/server parameter

O

bool

no

available

ansi_quotes

client parameter

bool

yes

block_ddl_statement

client parameter

O

bool

no

available

block_nowhere_statement

client parameter

O

bool

no

available

compat_numeric_division_scale

client/server parameter

O

bool

no

available

create_table_reuseoid

client parameter

O

bool

yes

available

cte_max_recursions

client/server parameter

O

int

2000

available

default_week_format

client/server parameter

O

int

0

available

group_concat_max_len

server parameter

O

byte

1,024

DBA only

intl_check_input_string

client parameter

O

bool

no

available

intl_collation

client parameter

O

string

available

intl_date_lang

client parameter

O

string

available

intl_number_lang

client parameter

O

string

available

json_max_array_idx

server parameter

O

string

65,536

available

no_backslash_escapes

client parameter

bool

yes

only_full_group_by

client parameter

O

bool

no

available

oracle_compat_number_behavior

server parameter

bool

no

oracle_style_empty_string

client parameter

bool

no

pipes_as_concat

client parameter

bool

yes

plus_as_concat

client parameter

bool

yes

require_like_escape_character

client parameter

bool

no

return_null_on_function_errors

client/server parameter

O

bool

no

available

string_max_size_bytes

client/server parameter

O

byte

1,048,576

available

unicode_input_normalization

client parameter

O

bool

no

available

unicode_output_normalization

client parameter

O

bool

no

available

update_use_attribute_references

client parameter

O

bool

no

available

Thread-Related Parameters

thread_connection_pooling

server parameter

bool

yes

thread_connection_timeout_seconds

server parameter

int

300

thread_worker_pooling

server parameter

bool

yes

thread_core_count

server parameter

int

# of system cores

thread_worker_timeout_seconds

server parameter

int

300

loaddb_worker_count

server parameter

int

8

Timezone Parameter

server_timezone

server parameter

string

OS timezone

available

timezone

client/server parameter

O

string

the value of server_timezone

available

tz_leap_second_support

server parameter

bool

no

available

Query Plan Cache-Related Parameters

max_plan_cache_entries

client/server parameter

int

1,000

max_plan_cache_clones

server parameter

int

1,000

xasl_cache_time_threshold_in_minutes

client/server parameter

int

360

max_filter_pred_cache_entries

client/server parameter

int

1,000

Query Cache-Related Parameters

max_query_cache_entries

server parameter

int

0

available

query_cache_size_in_pages

server parameter

int

0

available

Utility-Related Parameters

backup_volume_max_size_bytes

server parameter

byte

0

communication_histogram

client parameter

O

bool

no

available

compactdb_page_reclaim_only

server parameter

int

0

csql_history_num

client parameter

O

int

50

available

HA-Related Parameters

ha_mode

server parameter

string

off

Other Parameters

access_ip_control

server parameter

bool

no

access_ip_control_file

server parameter

string

agg_hash_respect_order

client parameter

O

bool

yes

available

auto_restart_server

server parameter

O

bool

yes

DBA only

enable_string_compression

client/server parameter

bool

yes

index_scan_in_oid_order

client parameter

O

bool

no

available

index_unfill_factor

server parameter

float

0.05

java_stored_procedure

server parameter

bool

no

java_stored_procedure_port

server parameter

int

0

java_stored_procedure_uds

server parameter

bool

yes

java_stored_procedure_jvm_options

server parameter

string

multi_range_optimization_limit

server parameter

O

int

100

DBA only

optimizer_enable_merge_join

client parameter

O

bool

no

available

use_stat_estimation

server parameter

bool

no

server

server parameter

string

service

server parameter

string

session_state_timeout

server parameter

sec

21,600

sort_limit_max_count

client parameter

O

int

1,000

available

sql_trace_slow

server parameter

O

msec

-1

DBA only

sql_trace_execution_plan

server parameter

O

bool

no

DBA only

use_orderby_sort_limit

server parameter

O

bool

yes

DBA only

vacuum_prefetch_log_mode

server parameter

int

1

DBA only

vacuum_prefetch_log_buffer_size

server parameter

int

3200 * log_page_size

DBA only

data_buffer_neighbor_flush_pages

server parameter

int

8

DBA only

data_buffer_neighbor_flush_nondirty

server parameter

bool

no

DBA only

tde_keys_file_path

server parameter

string

NULL

tde_default_algorithm

server parameter

string

AES

recovery_progress_logging_interval

server parameter

int

0

supplemental_log

client/server parameter

int

0

regexp_engine

client/server parameter

string

re2

available

vacuum_ovfp_check_threshold

server parameter

int

1000

vacuum_ovfp_check_duration

server parameter

int

45000

deduplicate_key_level

client/server parameter

int

-1

print_index_detail

client/server parameter

bool

no

  • log_page_size: A log volume page size specified by --log-page-size option when you are creating database. Default: 16KB. log page related parameter’s value is rounded off by page unit. For example, the value of checkpoint_every_size is divided by 16KB and its decimal point is dropped, then it is multiplied by 16KB.

  • db_page_size: A DB volume page size specified by --db-page-size option when you are creating database. Default: 16KB. DB page related parameter’s value is rounded off by page unit. For example, the value of data_buffer_size is divided by 16KB and its decimal point is dropped, then it is multiplied by 16KB.

Section by Parameter

Parameters specified in cubrid.conf have the following four sections:

  • Used when the CUBRID service starts: [service] section

  • Applied commonly to all databases: [common] section

  • Applied individually to each database: [@<database>] section

  • Used only when the cubrid utilities are run with stand-alone mode(--SA-mode): [standalone] section

Where <database> is the name of the database to which each parameter applies. If a parameter configured in [common] is the same as the one configured in [@<database>], the one configured in [@<database>] is applied.

.....
[common]
.....
sort_buffer_size=2M
.....
[standalone]

sort_buffer_size=256M
.....

Configuration defined in [standalone] is used only when cubrid utilities started with “cubrid” are run with stand-alone mode. For example, on the above configuration, if DB is started with --CS-mode(default)(cubrid databases start db_name), “sort_buffer_size=2M” is applied. However, if DB is stopped and “cubrid loaddb --SA-mode” is executed, “sort_buffer_size=256M” is applied. If you run “cubrid loaddb --SA-mode”, bigger size of sort buffer will be required during index creation; therefore, increasing sort buffer size will be better for the performance of “loaddb” execution.

Default Parameters

cubrid.conf, a default database configuration file created during the CUBRID installation, includes some default database server parameters that must be changed. You can change the value of a parameter that is not included as a default parameter by manually adding or editing one.

The following is the content of the cubrid.conf file.

# Copyright (C) 2008 Search Solution Corporation. All rights reserved by Search Solution.
#
# $Id$
#
# cubrid.conf#

# For complete information on parameters, see the CUBRID
# Database Administration Guide chapter on System Parameters

# Service section - a section for 'cubrid service' command
[service]

# The list of processes to be started automatically by 'cubrid service start' command
# Any combinations are available with server, broker and manager.
service=server,broker,manager

# The list of database servers in all by 'cubrid service start' command.
# This property is effective only when the above 'service' property contains 'server' keyword.
#server=demodb,testdb

# Common section - properties for all databases
# This section will be applied before other database specific sections.
[common]

# Read the manual for detailed description of system parameters
# Manual > System Configuration > Database Server Configuration > Default Parameters

# Size of data buffer are using K, M, G, T unit
data_buffer_size=512M

# Size of log buffer are using K, M, G, T unit
log_buffer_size=256M

# Size of sort buffer are using K, M, G, T unit
# The sort buffer should be allocated per thread.
# So, the max size of the sort buffer is sort_buffer_size * max_clients.
sort_buffer_size=2M

# The maximum number of concurrent client connections the server will accept.
# This value also means the total # of concurrent transactions.
max_clients=100

# TCP port id for the CUBRID programs (used by all clients).
cubrid_port_id=1523

If you want to set data_buffer_size as 128M and max_clients as 10 only on testdb, set as follows.

[service]

service=server,broker,manager

[common]

data_buffer_size=512M
log_buffer_size=256M
sort_buffer_size=2M
max_clients=100

# TCP port id for the CUBRID programs (used by all clients).
cubrid_port_id=1523

[@testdb]
data_buffer_size=128M
max_clients=10

Timezone Parameter

The following are the parameters related to timezone. The type and the value range for each parameter are as follows:

Parameter Name

Type

Default

Min

Max

server_timezone

string

OS timezone

timezone

string

server_timezone

tz_leap_second_support

bool

no

  • timezone

    Specifies a timezone for a session. The default is a value of server_timezone. This value can be specified by a timezone offset (e.g. +01:00, +02) or a timezone region name (e.g. Asia/Seoul). This value can be changed during operating database.

  • server_timezone

    Specifies a timezone for a server. The default is a OS timezone. To apply the changed value, database should be restarted. The timezone of operating system is read depending on the operating system and information found in operating system configuration files:

    • on Windows, the API tzset() function and tzname[0] variable are used to retrieve an Windows style timezone name. This name is translated into IANA/CUBRID style name using the CUBRID mapping data (the mapping file is %CUBRID%\timezones\tzdata\windowsZones.xml).

    • on Linux, CUBRID attempts to read and parse the file “/etc/sysconfig/clock”. If this file is not available, then the value of link “/etc/localtime” is read and used.

    • on AIX, the value of “TZ” operating system environment variable is used.

    On all operating systems, if the server_timezone is not specified, and the value for timezone from operating system cannot be read, then “Asia/Seoul” zone is used as server timezone.

  • tz_leap_second_support

    Specifies to support a leap second or not as yes or no. The default is no.

    A leap second is a one-second adjustment that is occasionally applied to Coordinated Universal Time (UTC) in order to keep its time of day close to the mean solar time.

    To apply the changed value, database should be restarted.

Other Parameters

The following are other parameters. The type and value range for each parameter are as follows:

Parameter Name

Type

Default

Min

Max

access_ip_control

bool

no

access_ip_control_file

string

agg_hash_respect_order

bool

yes

auto_restart_server

bool

yes

enable_string_compression

bool

yes

index_scan_in_oid_order

bool

no

index_unfill_factor

float

0.05

0

0.5

java_stored_procedure

bool

no

java_stored_procedure_port

int

0

0

65535

java_stored_procedure_uds

bool

yes

java_stored_procedure_jvm_options

string

multi_range_optimization_limit

int

100

0

10,000

optimizer_enable_merge_join

bool

no

use_stat_estimation

bool

no

server

string

service

string

session_state_timeout

sec

21,600 (6 hours)

60(1 minute)

31,536,000 (1 year)

sort_limit_max_count

int

1000

0

INT_MAX

sql_trace_slow

msec

-1(inf)

0

86,400,000 (24 hours)

sql_trace_execution_plan

bool

no

use_orderby_sort_limit

bool

yes

vacuum_prefetch_log_mode

int

1

0

1

vacuum_prefetch_log_buffer_size

int

50M

25M

INT_MAX

data_buffer_neighbor_flush_pages

int

8

0

32

data_buffer_neighbor_flush_nondirty

bool

no

tde_keys_file_path

string

NULL

tde_default_algorithm

string

AES

recovery_progress_logging_interval

int

0 (off)

0

3600

supplemental_log

int

0 (off)

0

2

regexp_engine

string

re2

vacuum_ovfp_check_threshold

int

1000

2

INT_MAX

vacuum_ovfp_check_duration

int

45000

1

600000

deduplicate_key_level

int

-1

-1

14

print_index_detail

bool

no

access_ip_control

access_ip_control is a parameter to configure whether to use feature limiting the IP addresses that allow server access. The default value is no. For details, see Limiting Database Server Access.

access_ip_control_file

access_ip_control_file is a parameter to configure the file name in which the list of IP addresses allowed by servers is stored. If access_ip_control value is set to yes, database server allows the list of IP addresses only stored in the file specified by this parameter. For details, see Limiting Database Server Access.

agg_hash_respect_order

agg_hash_respect_order is a parameter to configure whether the groups in an aggregate function will be returned ordered or not. The default is yes. As a reference, see max_agg_hash_size.

If all the groups (keys and accumulators) can fit into hash memory, then “agg_hash_respect_order=no” will skip sorting them before writing to output, so it is fair to assume that the order cannot be guaranteed in this case. However, when overflows occur, then a sort step must be performed and you will get the results in-order even with “agg_hash_respect_order=no”.

auto_restart_server

auto_restart_server is a parameter to configure whether to restart the process when it stops due to fatal errors being occurred in database server process. If auto_restart_server value is set to yes, the server process automatically restarts when it has stopped due to errors; it does not restart in case it stops by following normal process (by using STOP command).

enable_string_compression

enable_string_compression is a parameter to configure whether string compression should be used when storing variable string type value into heap, index or list. If enable_string_compression value is set to yes, and the string is at least 255 bytes in size and the compressed string requires less size than original string, then the string is stored in compressed form.

index_scan_in_oid_order

index_scan_in_oid_order is a parameter to configure the result data to be retrieved in OID order after the index scan. If the parameter is set to no, which is the default value, results are retrieved in data order; if it is set to yes, they are retrieved in OID order.

index_unfill_factor

If there is no free space because index pages are full when the INSERT or UPDATE operation is executed after the first index is created, the split of index page nodes occurs. This substantially affects the performance by increasing the operation time. index_unfill_factor is a parameter to configure the percent of free space defined for each index page node when an index is created. The index_unfill_factor value is applied only when an index is created for the first time. The percent of free space defined for the page is not maintained dynamically. Its value ranges between 0 and 0.5. The default value is 0.05.

If an index is created without any free space for the index page node (index_unfill_factor is set to 0), the split of index page nodes occurs every time an additional insertion is made. This may degrade the performance.

If the value of index_unfill_factor is large, a large amount of free space is available when an index is created. Therefore, better performance can be obtained because the split of index nodes does not occur for a relatively long period of time until the free space for the nodes is filled after the first index is created.

If this value is small, the amount of free space for the nodes is small when an index is created. Therefore, it is likely that the index nodes are spilt by INSERT or UPDATE because free space for the index nodes is filled in a short period of time.

java_stored_procedure

java_stored_procedure is a parameter to configure whether to use Java stored procedures by running the Java Virtual Machine (JVM). If the parameter is set to no, which is the default value, JVM is not executed; if it is set to yes, JVM is executed so you can use Java stored procedures. Therefore, configure the parameter to yes if you plan to use Java stored procedures.

java_stored_procedure_port

java_stored_procedure_port is a parameter to configure the port number receiving a request that calls the java stored procedures from database server. the value must be unique and smaller than 65,535. The default value of java_stored_procedure_port is 0 which means the port number is automatically allocated, typically from an ephemeral port range. The value configured in this parameter affects only java_stored_procedure is set to yes. Note that an error occurs if the parameter is configured in [common].

.....
[common]
.....
# an error occurs. remove the following line.
java_stored_procedure_port=4333
.....
[@testdb]
.....
# the parameter is configured successfully for testdb
java_stored_procedure_port=4334
.....

java_stored_procedure_uds

java_stored_procedure_uds is a parameter to connect between the cub_javasp process and the cub_server process through a Unix domain socket instead of TCP when calling a Java stored procedure. The default value of java_stored_procedure_uds is yes. For Windows, regardless of the value of the parameter, TCP connection is used.

Note

For the CUBRID_TMP environment variable that specifies the UNIX domain socket file path of cub_javasp* processes, see Configuring Environment Variables.

java_stored_procedure_jvm_options

java_stored_procedure_jvm_options is a parameter to configure Java Virtual Machine (JVM) and Java options on which Java stored procedures are executed. Each option string should be separated by spaces. For JVM options, there are three types of options; standard, non-standard and advanced options. non-standard and advanced options are not guaranteed to be supported on all VM implementations. The default is an empty string. If the parameter value configured in [@<database>], it overwrites the value specified in [common].

.....
[common]
.....
java_stored_procedure_jvm_options="-Xms1024m -Xmx1024m -XX:PermSize=512m -XX:MaxPermSize=512m"
.....
[@testdb]
.....
java_stored_procedure=yes

# Note that -XX:PermSize=512m and -XX:MaxPermSize=512m will not be applied for testdb, Even though they specified in [common] section.
java_stored_procedure_jvm_options="-Xms2048m -Xmx2048m"
.....

multi_range_optimization_limit

If the number of rows specified by the LIMIT clause in the query, which has multiple ranges (col IN (?, ?, … ,?)) and is available to use an index, is within the number specified in the multi_range_optimization_limit parameter, the optimization for the way of index sorting will be performed. The default value is 100.

For example, if a value for this parameter is set to 50, LIMIT 10 means that it is within the value specified by this parameter, so that the values that meet the conditions will be sorted to produce the result. If LIMIT is 60, it means that it exceeds the parameter configuration value, so that it gets and sorts out all values that meet the conditions.

Depending on the setting value, the differences are made between collecting the result with on-the-fly sorting of the intermediate values and sorting the result values after collecting them, and the bigger value could make more unfavorable performance.

optimizer_enable_merge_join

optimizer_enable_merge_join is a parameter to specify whether to include sort merge join plan as a candidate of query plans or not. The default is no. Regarding sort merge join, see Using SQL Hint.

use_stat_estimation

use_stat_estimation is a parameter to specify whether to use the estimated information in calculating statistics or not. The default is no. The estimated information generated by the heap manager while processing DML is associated with the number of added objects. it is relatively accurate for the number of total objects, NOT for the number of distinct values.

server

server is a parameter used to register the name of database server process which will run automatically when CUBRID server starts.

service

service is a parameter to configure process that starts automatically when the CUBRID service starts. There are four types of processes: server, broker, manager, and heartbeat. Three processes are usually registered as in service=server,broker,manager.

  • If the parameter is set to server, the database process specified by the @server parameter gets started.

  • If the parameter is set to broker, the broker process gets started.

  • If the parameter is set to manager, the manager process gets started.

  • If the parameter is set to heartbeat, the HA-related processes get started.

session_state_timeout

session_state_timeout is a parameter used to define how long the CUBRID session data will be kept. The session data will be deleted when the driver terminates the connection or the session time expires. The session time will expire after the specified time if a client terminates abnormally.

Custom variables defined by SET and PREPARE statements can be deleted by DROP / DEALLOCATE statements before session timeout.

The default value is 21,600 seconds(6 hours), and this parameter’s unit is second.

sort_limit_max_count

Regarding SORT-LIMIT optimization which can be applied when top-N rows are sorted by “ORDER BY … LIMIT N” statement, this parameter specifies the LIMIT count to limit applying this optimization. When the value of N is smaller than a value of sort_limit_max_count, SORT-LIMIT optimization is applied. The default is 1000, the minimum is 0(which means that this optimization is disabled), and the maximum is INT_MAX.

For more details, see SORT-LIMIT optimization.

sql_trace_slow

sql_trace_slow is a parameter to configure the execution time of a query which will be judged as a long time execution. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, milliseconds(ms) will be applied. The default value is -1 and the maximum value is 86,400,000 msec (24 hour). -1 means that the infinite time, so any queries will not be judged as a long duration query. For details, see the below sql_trace_execution_plan.

Note

The system parameter sql_trace_slow judges the query execution time based on the server, but the broker parameter MAX_QUERY_TIMEOUT judges the query execution time based on the broker.

sql_trace_execution_plan

sql_trace_execution_plan is a parameter to configure if the query plan of the long running query is written to the log or not. The default value is no.

If it is set to yes, a long running SQL, a query plan and the output of cubrid statdump command are written to the server error log file(located on $CUBRID/log/server directory) and CAS log file(located on $CUBRID/log/broker/sql_log directory) .

If it is set to no, only a long running SQL is written to the server error log file and CAS log file, and this SQL is displayed when you execute cubrid statdump command.

For example, if you want to write the execution plan of the slow query to the log file, and specify the query which executes more than 5 seconds as the slow query, then configure the value of the sql_trace_slow parameter as 5000(ms) and configure the value of the sql_trace_execution_plan parameter as yes.

But, on the server error log file, the related information is written only when the value of error_log_level is NOTIFICATION.

use_orderby_sort_limit

use_orderby_sort_limit is a parameter to configure whether to keep the intermediate result of sorting and merging process in the statement including the ORDER BY … LIMIT row_count clause as many as row_count. If it is set to yes, you can decrease unnecessary comparing and merging processes because as many as intermediate results will be kept as the value of row_count. The default value is yes.

vacuum_prefetch_log_mode

vacuum_prefetch_log_mode is a parameter to configure the prefetch mode of log pages on behalf of vacuum.

In mode 0, the vacuum master thread prefetch the required log pages in a shared buffer. In mode 1 (default), each vacuum worker prefetches the required log pages in its own buffer. Mode 0 also requires that vacuum_prefetch_log_buffer_size system parameter is configured, in mode 0 this parameter is ignored and each vacuum worker prefetches an entire vacuum log block (default 32 log pages).

vacuum_prefetch_log_buffer_size

vacuum_prefetch_log_buffer_size is a parameter to configure the log prefetch buffer size of vacuum (it is used only if vacuum_prefetch_log_mode is set to 0).

data_buffer_neighbor_flush_pages

data_buffer_neighbor_flush_pages is a parameter to control the number of neighbor pages to be flushed with background flush (victim candidates flushing). When is less or equal to 1, the neighbor flush feature is considered deactivated.

data_buffer_neighbor_flush_nondirty

data_buffer_neighbor_flush_nondirty is a parameter to control the flushing of non-dirty neighbor pages. When victim candidates pages are flushed, and neighbor flush is activated (data_buffer_neighbor_flush_pages is greater than 1), than single non-dirty pages which completes a chain of neighbor (dirty) pages are also flushed.

tde_keys_file_path

tde_keys_file_path is a parameter to configure the path of the key file for TDE. The key file’s name is fixed as <database_name>_keys, and the directory where the key file exists is designated. If this system parameter is not set, the key file is searched in the same location as the database volume. For a detailed description of the key file, see File-based Master Key Management.

tde_default_algorithm

tde_default_algorithm is a parameter that configures the default algorithm used when creating the TDE encryption table. Log and temporary data are always encrypted using the algorithm set with this parameter when they have to be encrypted. AES or ARIA can be set. For more information on encryption algorithms, refer to Encryption Algorithm.

recovery_progress_logging_interval

recovery_progress_logging_interval is a parameter to decide whether the details of recovery are printed and configure its period in seconds. If it is set bigger than 0, the total works and remained works to do of the three phases of recovery: Analysis, Redo and Undo are printed. When this is set smaller than 5, it is set to 5.

supplemental_log

supplemental_log is a parameter to determine whether information needed to support the CDC (Change Data Capture) or flashback is written to the log volume. CDC and flashback must be able to see how transactions logically changed the database through the physical logs. Any additional information required to interpret the physical logs is saved as supplemental_log . Setting this parameter bigger than 0 affects performance and log space because more logs are created and stored in addition to the existing transaction logs. If this parameter is set to 1, the information necessary to interpret DML and DDL executed by the user is logged. If it is set to 2, only information necessary to interpret the DML is logged.

regexp_engine

regexp_engine is a parameter to choose a library in which regular expression operators and functions will perform. cppstd or re2 can be set and the default value is re2. For more information on regular expression functionalities, refer to Regular Expressions Functions and Operators.

vacuum_ovfp_check_threshold

vacuum_ovfp_check_threshold collects index information when the number of leaf’s overflow pages to be read is greater than the value, when index vacuum is performed. The default is 1000 pages.

vacuum_ovfp_check_duration

vacuum_ovfp_check_duration specifies the duration for which data related to the count of index overflow pages, gathered by vacuum threads, is retained. Data that remains unchanged within the specified duration will be automatically removed. The unit of it’s value is minutes.

deduplicate_key_level

deduplicate_key_level determines the automatic inclusion and value setting of the WITH DEDUPLICATE statement within the index creation statement. For details on DEDUPLICATE, see DEDUPLICATE. The default is -1(which means that the WITH DEDUPLICATE is not included implicitly).

Note

  • If deduplicate_key_level is set to -1, even if the deduplicate level is explicitly specified in the CREATE INDEX statement, it is ignored.

print_index_detail

It specifies whether option information in the WITH clause is displayed when index syntax information is displayed, such as in the SHOW CREATE TABLE statement. Default is NO. However, the unloaddb tool is not affected by this setting.

Broker Configuration

cubrid_broker.conf Configuration File and Default Parameters

Broker System Parameters

The following table shows the broker parameters available in the broker configuration file (cubrid_broker.conf). For details, see Common Parameters and Parameter by Broker. You can temporarily change the parameter of which configuration values can be dynamically changed by using the broker_changer utility. To apply configuration values even after restarting all brokers with cubrid broker restart, you should change the values in the cubrid_broker.conf file.

Category

Use

Parameter Name

Type

Default Value

Dynamic Changes

Common Parameters

Access

ACCESS_CONTROL

bool

no

ACCESS_CONTROL_FILE

string

Logging

ADMIN_LOG_FILE

string

log/broker/cubrid_broker.log

Broker server (cub_broker)

MASTER_SHM_ID

int

30,001

Parameter by Broker

Access

ACCESS_LIST

string

ACCESS_MODE

string

RW

available

BROKER_PORT

int

30,000(max : 65,535)

CONNECT_ORDER

string

SEQ

available

ENABLE_MONITOR_HANG

string

OFF

KEEP_CONNECTION

string

AUTO

available

MAX_NUM_DELAYED_HOSTS_LOOKUP

int

-1

PREFERRED_HOSTS

string

available

RECONNECT_TIME

sec

600

available

REPLICA_ONLY

string

OFF

Broker App. Server(CAS)

APPL_SERVER_MAX_SIZE

MB

Windows 32bit: 40, Windows 64bit: 80, Linux: 0(max: 2,097,151)

available

APPL_SERVER_MAX_SIZE_HARD_LIMIT

MB

1,024

available

APPL_SERVER_PORT

int

BROKER_PORT+1

APPL_SERVER_SHM_ID

int

30,000

AUTO_ADD_APPL_SERVER

string

ON

MAX_NUM_APPL_SERVER

int

40

MIN_NUM_APPL_SERVER

int

5

TIME_TO_KILL

sec

120

available

Transaction & Query

CCI_DEFAULT_AUTOCOMMIT

string

ON

LONG_QUERY_TIME

sec

60(max: 86,400)

available

LONG_TRANSACTION_TIME

sec

60(max: 86,400)

available

MAX_PREPARED_STMT_COUNT

int

2,000(min: 1)

available

MAX_QUERY_TIMEOUT

sec

0(max: 86,400)

available

SESSION_TIMEOUT

sec

300

available

STATEMENT_POOLING

string

ON

available

JDBC_CACHE

string

OFF

available

JDBC_CACHE_HINT_ONLY

string

OFF

available

JDBC_CACHE_LIFE_TIME

sec

1000

available

TRIGGER_ACTION

string

ON

available

Logging

ACCESS_LOG

string

OFF

available

ACCESS_LOG_DIR

string

log/broker

ACCESS_LOG_MAX_SIZE

KB

10M(max: 2G)

available

ERROR_LOG_DIR

string

log/broker/error_log

available

LOG_DIR

string

log/broker/sql_log

available

SLOW_LOG

string

ON

available

SLOW_LOG_DIR

string

log/broker/sql_log

available

SQL_LOG

string

ON

available

SQL_LOG_MAX_SIZE

KB

10,000

available

Shard

SHARD

string

OFF

SHARD_CONNECTION_FILE

string

shard_connection.txt

SHARD_DB_NAME

string

SHARD_DB_PASSWORD

string

SHARD_DB_USER

string

SHARD_IGNORE_HINT

string

OFF

SHARD_KEY_FILE

string

shard_key.txt

SHARD_KEY_FUNCTION_NAME

string

SHARD_KEY_LIBRARY_NAME

string

SHARD_KEY_MODULAR

int

256

SHARD_MAX_CLIENTS

int

256

SHARD_MAX_PREPARED_STMT_COUNT

int

10,000

SHARD_NUM_PROXY

int

1

SHARD_PROXY_CONN_WAIT_TIMEOUT

sec

8h

SHARD_PROXY_LOG

string

ERROR

available

SHARD_PROXY_LOG_DIR

string

log/broker/proxy_log

SHARD_PROXY_LOG_MAX_SIZE

KB

100,000

available

SHARD_PROXY_SHM_ID

int

SHARD_PROXY_TIMEOUT

sec

30(second)

Etc

MAX_STRING_LENGTH

int

-1

SERVICE

string

ON

SSL

string

OFF

SOURCE_ENV

string

cubrid.env

Default Parameters

The cubrid_broker.conf file, the default broker configuration file created when installing CUBRID, includes some parameters that must be modified by default. If you want to modify the values of parameters that are not included in the configuration file by default, you can add or modify one yourself.

The following is the content of the cubrid_broker.conf file provided by default.

[broker]
MASTER_SHM_ID           =30001
ADMIN_LOG_FILE          =log/broker/cubrid_broker.log

[%query_editor]
SERVICE                 =ON
BROKER_PORT             =30000
MIN_NUM_APPL_SERVER     =5
MAX_NUM_APPL_SERVER     =40
APPL_SERVER_SHM_ID      =30000
LOG_DIR                 =log/broker/sql_log
ERROR_LOG_DIR           =log/broker/error_log
SQL_LOG                 =ON
TIME_TO_KILL            =120
SESSION_TIMEOUT         =300
KEEP_CONNECTION         =AUTO

[%BROKER1]
SERVICE                 =ON
BROKER_PORT             =33000
MIN_NUM_APPL_SERVER     =5
MAX_NUM_APPL_SERVER     =40
APPL_SERVER_SHM_ID      =33000
LOG_DIR                 =log/broker/sql_log
ERROR_LOG_DIR           =log/broker/error_log
SQL_LOG                 =ON
TIME_TO_KILL            =120
SESSION_TIMEOUT         =300
KEEP_CONNECTION         =AUTO

Common Parameters

The following are parameters commonly applied to entire brokers; it is written under [broker] section.

Access

ACCESS_CONTROL

ACCESS_CONTROL is a parameter used to limit applications which are trying to connect a broker. The default value is OFF. For details, see Limiting Broker Access.

ACCESS_CONTROL_FILE

ACCESS_CONTROL_FILE is a parameter to configure the name of a file in which a database name, database user ID, and the list of IPs are stored. List of IPs can be written up to the maximum of 256 lines per <db_name>:<db_user> in a broker. For details, see Limiting Broker Access.

Logging

ADMIN_LOG_FILE

ADMIN_LOG_FILE is a parameter to configure the file in which time of running CUBRID broker is stored. The default value is a log/broker/cubrid_broker.log file.

Broker Server(cub_broker)

MASTER_SHM_ID

MASTER_SHM_ID is a parameter used to specify the identifier of shared memory which is used to manage the CUBRID broker. Its value must be unique in the system. The default value is 30001.

Parameter by Broker

The following describes parameters to configure the environment variables of brokers; each parameter is located under [%broker_name]. The maximum length of broker_name is 63 characters in English.

Access

ACCESS_LIST

ACCESS_LIST is a parameter to configure the name of a file where the list of IP addresses of an application which allows access to the CUBRID broker is stored. To allow access by IP addresses access 210.192.33.* and 210.194.34.*, store them to a file (ip_lists.txt) and then assign the file name with the value of this parameter.

ACCESS_MODE

ACCESS_MODE is a parameter to configure default mode of the broker. The default value is RW. For details, see cubrid_broker.conf.

BROKER_PORT

BROKER_PORT is a parameter to configure the port number of the broker; the value must be unique and smaller than 65,535. The default port value of query_editor’ broker is 30,000 and the port value of the broker1 is 33,000.

CONNECT_ORDER

CONNECT_ORDER is a parameter to specify whether a CAS tries to connect to one of hosts in the order or randomly in $CUBRID_DATABASES/databases.txt, when a CAS decides the order of connecting to a host. The default is SEQ; a CAS tries to connect to a host in the order. if this value is RANDOM, a CAS tries to connect to a host randomly. If PREFERRED_HOSTS parameter is specified, firstly a CAS tries to connect to one of hosts specified in PREFERRED_HOSTS, then uses db-host values in $CUBRID_DATABASES/databases.txt only when the connection is failed.

ENABLE_MONITOR_HANG

ENABLE_MONITOR_HANG is a parameter to configure whether to block the access from the application to the broker or not, when more than a certain ratio of CASes on that broker are hung. If the ENABLE_MONITOR_HANG parameter value is ON, blocking feature is processed. The default value is OFF. If it is OFF, don’t do the behavior.

The broker process judges the CAS as hung if the hanging status of the CAS keeps more than one minute, then block the access from applications to that broker; it brings the behavior which the applications try to access to the alternative hosts(altHosts) configured by the connection URL.

KEEP_CONNECTION

KEEP_CONNECTION is a parameter to configure the way of connection between CAS and application clients; it is set to one of the following: ON or AUTO. If this value is ON, it is connected in connection unit. If it is AUTO and the number of servers is more than that of clients, transaction unit is used; in the reverse case, connection unit is used. The default value is AUTO.

MAX_NUM_DELAYED_HOSTS_LOOKUP

When almost all DB servers have the delay of replication in the HA environment where multiple DB servers on db-host of databases.txt are specified, check if the connection is established or not until the number of delayed replication servers; the number is specified in MAX_NUM_DELAYED_HOSTS_LOOKUP (whether the delay of replication in the DB server is judged only with the standby hosts; it is determined by the setting of ref:ha_delay_limit <ha_delay_limit>). See MAX_NUM_DELAYED_HOSTS_LOOKUP for further information.

PREFERRED_HOSTS

PREFERRED_HOSTS is a parameter to specify the order of a host to which a CAS tries to connect in a first priority. If the connection is failed after trying connection in the order specified in PREFERRED_HOSTS, a CAS tries to connect to the one of hosts specified in $CUBRID_DATABASES/databases.txt. The default value is NULL. For details, see cubrid_broker.conf.

RECONNECT_TIME

If the time specified by RECONNECT_TIME is elapsed in a certain status, CAS will try to reconnect to the other DB server. The default of this parameter is 600s(10min). You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, s will be applied.

a certain status which CAS tries to reconnect is as follows.

  • when CAS is connected to not a DB server in PREFERRED_HOSTS, but the DB server of db-host in databases.txt.

  • when CAS with “ACCESS_MODE=RO”(Read Only) is connected to not the standby DB server, but the active DB server.

  • when CAS is connected to the DB server of which replication is delayed.

When RECONNECT_TIME is 0, CAS does not try to reconnect.

REPLICA_ONLY

If a value of REPLICA_ONLY is ON, CAS is connected only to replicas. The default is OFF. Even though the value of REPLICA_ONLY is ON, when a value of ACCESS_MODE is RW, it is possible to write directly to the replica DB. However, the data to be written directly to the replica DB are not replicated.

Note

Please note that replication mismatch occurs when you write the data directly to the replica DB.

Broker App. Server(CAS)

APPL_SERVER_MAX_SIZE

APPL_SERVER_MAX_SIZE is a parameter to configure the maximum size of the process memory usage handled by CAS. You can set a unit as B, K, M or G, which stands for bytes, kilobytes(KB), megabytes(MB) or gigabytes(GB) respectively. If you omit the unit, M will be applied.

Specifying this parameter makes transactions terminate (commit or rollback) only when it is executed by a user. In contrast to this, specifying APPL_SERVER_MAX_SIZE_HARD_LIMIT makes transactions forcibly terminate (rollback) and restart CAS.

Note that the default values of Windows and Linux are different from each other.

For 32-bit Windows, the default value is 40 MB; for 64-bit Windows, it is 80 MB. The maximum value is the same on Windows and Linux as 2,097,151 MB. When current process size exceeds the value of APPL_SERVER_MAX_SIZE, broker restarts the corresponding CAS.

For Linux, the default value of APPL_SERVER_MAX_SIZE is 0; CAS restarts in the following conditions.

  • APPL_SERVER_MAX_SIZE is zero or negative: At the point when current process size becomes twice as large as initial memory

  • APPL_SERVER_MAX_SIZE is positive: At the point when it exceeds the value specified in APPL_SERVER_MAX_SIZE

Note

Be careful not to make the value too small because application servers may restart frequently and unexpectedly. In general, the value of APPL_SERVER_MAX_SIZE_HARD_LIMIT is greater than that of APPL_SERVER_MAX_SIZE. For details, see description of APPL_SERVER_MAX_SIZE_HARD_LIMIT.

APPL_SERVER_MAX_SIZE_HARD_LIMIT

APPL_SERVER_MAX_SIZE_HARD_LIMIT is a parameter to configure the maximum size of process memory usage handled by CAS. You can set a unit as B, K, M or G, which stands for bytes, kilobytes(KB), megabytes(MB) or gigabytes(GB) respectively. If you omit the unit, M will be applied. The default value is 1,024 (MB), and the maximum value is 2,097,151 (MB).

Specifying this parameter makes transactions being processed forcibly terminate (rollback) and restart CAS. In contrast to this, specifying APPL_SERVER_MAX_SIZE makes transactions terminate only when it is executed by a user.

Note

Be careful not to make the value too small because application servers may restart frequently and unexpectedly. When restarting CAS, APPL_SERVER_MAX_SIZE is specified to wait for normal termination of transactions although memory usage increases; APPL_SERVER_MAX_SIZE_HARD_LIMIT is specified to forcibly terminate transactions if memory usage exceeds the maximum value allowed. Therefore, in general, the value of APPL_SERVER_MAX_SIZE_HARD_LIMIT is greater than that of APPL_SERVER_MAX_SIZE.

APPL_SERVER_PORT

APPL_SERVER_PORT is a parameter to designate the TCP ports of CAS that communicates with application clients (Windows only).

On Linux, an established TCP connection between the BROKER and the client will be passed to the CAS. Therefore, the client can communicate with the CAS without additional TCP connection.

On the other hand, in Windows, all available CASes are waiting for a connection on an independent TCP port, for example, 33001/tcp, 33002/tcp. In these circumstances, when a client connects to a BROKER, the BROKER delivers the TCP port number, for example, 33001/tcp, for connecting to an available CAS to the client. In sequence, the client terminates the current network connection with the BROKER and establishes a new connection with the CAS using the port number received from the BROKER.

If the APPL_SERVER_PORT parameter is not additionally specified, this value is the value obtained by adding 1 to the value of BROKER_PORT. For example, if the value of BROKER_PORT is 30,000 and the APPL_SERVER_PORT parameter has not been specified, and if the MIN_NUM_APPL_SERVER value is 5, then five CASes use the TCP ports between 30,001 and 30,005, respectively. On the other hand, if the value of APPL_SERVER_PORT is 35,000 under the same conditions, 5 CASes use TCP ports from 35,000 to 35,004. The maximum number of CAS specified in the MAX_NUM_APPL_SERVER parameter in cubrid_broker_conf; therefore, the maximum number of connection ports is also determined by the value of MAX_NUM_APPL_SERVER parameter.

On the Windows system, if a firewall system exists between an application and a CUBRID broker, all TCP ports specified in BROKER_PORT and APPL_SERVER_PORT must be opened.

Note

For the CUBRID_TMP environment variable that specifies the UNIX domain socket file path of cub_master and cub_broker processes, see Configuring Environment Variables.

APPL_SERVER_SHM_ID

APPL_SERVER_SHM_ID is a parameter to configure the ID of shared memory used by CAS; the value must be unique within system. The default value is the same as the port value of the broker.

AUTO_ADD_APPL_SERVER

AUTO_ADD_APPL_SERVER is a parameter to configure whether CAS increase automatically to the value specified in MAX_NUM_APPL_SERVER in case of needed; the value will be either ON or OFF (default: ON).

MAX_NUM_APPL_SERVER

MAX_NUM_APPL_SERVER is a parameter to configure the maximum number of simultaneous connections allowed. The default value is 40.

MIN_NUM_APPL_SERVER

MIN_NUM_APPL_SERVER is a parameter to configure the minimum number of CAS even if any request to connect the broker has not been made. The default value is 5.

TIME_TO_KILL

TIME_TO_KILL is a parameter to configure the time to remove CAS in idle state among CAS added dynamically. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, s will be applied. The default value is 120 (seconds).

An idle state is one in which the server is not involved in any jobs. If this state continues exceeding the value specified in TIME_TO_KILL, CAS is removed.

The value configured in this parameter affects only CAS added dynamically, so it applies only when the AUTO_ADD_APPL_SERVER parameter is configured to ON. Note that times to add or remove CAS will be increased more if the TIME_TO_KILL value is so small.

Transaction & Query

CCI_DEFAULT_AUTOCOMMIT

CCI_DEFAULT_AUTOCOMMIT is a parameter to configure whether to make application implemented in CCI interface or CCI-based interface such as PHP, OLE DB, Perl, Python, and Ruby commit automatically. The default value is ON. This parameter does not affect applications implemented in JDBC.

If the CCI_DEFAULT_AUTOCOMMIT parameter value is OFF, the broker application server (CAS) process is occupied until the transaction is terminated. Therefore, it is recommended to execute commit after completing fetch when executing the SELECT statement.

Note

The CCI_DEFAULT_AUTOCOMMIT parameter has been supported from 2008 R4.0, and the default value is OFF for the version. Therefore, if you use CUBRID 2008 R4.1 or later versions and want to keep the configuration OFF, you should manually change it to OFF to avoid auto-commit of unexpected transaction.

Warning

In ODBC driver, the setting of CCI_DEFAULT_AUTOCOMMIT is ignored and worked as ON; therefore, you should set the autocommit on or off in the program directly.

LONG_QUERY_TIME

LONG_QUERY_TIME is a parameter to configure execution time of query which is evaluated as long-duration query. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, milliseconds(ms) will be applied. The default value is 60 (seconds) and the maximum value is 86,400(1 day). When you run a query and this query’s running time takes more than the specified time, a value of LONG-Q, which is printed out from “cubrid broker status” command, is increased 1; this SQL is written to SLOW log file ($CUBRID/log/broker/sql_log/*.slow.log) of CAS. See SLOW_LOG.

This value can be valued in milliseconds with a decimal separator. For example, the value can be configured into 0.5 to configure 500 msec.

Note that if a parameter value is configured to 0, a long-duration query is not evaluated.

LONG_TRANSACTION_TIME

LONG_TRANSACTION_TIME is a parameter to configure execution time of query which is evaluated as long-duration transaction. The default value is 60 (seconds) and the maximum value is 86,400(1 day).

This value can be valued in milliseconds with a decimal separator. For example, the value can be configured into 0.5 to configure 500 msec.

Note that if a parameter value is configured to 0, a long-duration transaction is not evaluated.

MAX_PREPARED_STMT_COUNT

MAX_PREPARED_STMT_COUNT is a parameter used to limit the number of prepared statements by user (application) access. The default value is 2,000 and the minimum value is 1. The problem in which prepared statement exceeding allowed memory is mistakenly generated by system can be prohibited by making users specify the parameter value.

Note

When you want to change the value of MAX_PREPARED_STMT_COUNT dynamically by broker_changer command, this can be changed only when this is bigger than the existing value; this cannot be changed when this is smaller than the existing value.

MAX_QUERY_TIMEOUT

MAX_ QUERY_TIMEOUT is a parameter to configure timeout value of query execution. When time exceeds a value specified in this parameter after starting query execution, the query being executed stops and rolls back. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, milliseconds(ms) will be applied. The default value is 0 (seconds) and it means infinite waiting. The value range is available from 0 to 86,400 seconds(one day).

The smallest value (except 0) between the MAX_QUERY_TIMEOUT value and query timeout value of an application is applied if query timeout is configured in an application.

Note

See the cci_connect_with_url() and cci_set_query_timeout() functions to configure query timeout of CCI applications. For configuring query timeout of JDBC applications, see the setQueryTimeout method.

SESSION_TIMEOUT

SESSION_TIMEOUT is a parameter to configure timeout value for the session of the broker. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, milliseconds(ms) will be applied. The default value is 300 (seconds).

If there is no response to the job request for the specified time period, session will be terminated. If a value exceeds the value specified in this parameter without any action taken after starting transaction, the connections are terminated.

STATEMENT_POOLING

STATEMENT_POOLING is a parameter to configure whether to use statement pool feature. The default value is ON.

CUBRID closes all handles of prepared statement in the corresponding client sessions when transaction commit or rollback is made. If the value of STATEMENT_POOLING is set to ON, the handles are reusable because they are maintained in the pool. Therefore, in an environment where libraries, such as general applications reusing prepared statement or DBCP where statement pooling is implemented is applied, the default configuration (ON) should be maintained.

If the prepared statement is executed after transaction commit or termination while STATEMENT_POOLING is set to OFF, the following message will be displayed.

Caused by: cubrid.jdbc.driver.CUBRIDException: Attempt to access a closed Statement.

JDBC_CACHE

JDBC_CACHE is a parameter to configure whether to use result-cache fetaure. The default value is OFF.

If the parameter is ON, all of SELECT queries from JDBC is cached at client for life time which is configured by JDBC_CACHE_LIFE_TIME

JDBC_CACHE_HINT_ONLY

JDBC_CACHE_HINT_ONLY is a parameter to configure whether to use result-cache feature only by query hint /*+ JDBC_CACHE */.

It works as if the parameter is ON when the query hint is given.

JDBC_CACHE_LIFE_TIME

JDBC_CACHE_HINT_ONLY is a parameter to configure JDBC client’s result-cache life time. The default value is 1000 (sec).

For only cache life time, the result-cache is available. After the cache lifetime expired, the prior cached results are no more available and a new result is cached.

The cache life time works only when the paramter JDBC_CACHE or JDBC_CACHE_HINT_ONLY is configured to “ON”.

WARNING

JDBC_CACHE, JDBC_CACHE_HINT_ONLY, and JDBC_CACHE_LIFE_TIME parameters are meaningless

when the system parameter both of max_query_cache_entries and query_cache_size_in_pages are not set to positive value.

For result cache working, the SELECT query must include query hint /*+ QUERY_CACHE */ together with these JDBC related paramter setting.

TRIGGER_ACTION

Turn on or off of the trigger’s action about the broker which specified this parameter. Specify ON or OFF as a value; The default is ON.

Logging

ACCESS_LOG

ACCESS_LOG is a parameter to configure whether to store the access log of the broker. The default value is OFF. The name of the access log file for the broker is broker_name.access and the file is stored under $CUBRID/log/broker directory.

ACCESS_LOG_DIR

ACCESS_LOG_DIR specifies the directory for broker access logging files(ACCESS_LOG) to be created. The default is log/broker.

ACCESS_LOG_MAX_SIZE

ACCESS_LOG_MAX_SIZE specifies the maximum size of broker access logging files(ACCESS_LOG); if a broker access logging file is bigger than a specified size, this file is backed up into the name of broker_name.access.YYYYMMDDHHMISS, then logging messages are written to the new file(broker_name.access). The default is 10M and the maximum is 2G. It can be dynamically changed during operating a broker.

ERROR_LOG_DIR

ERROR_LOG_DIR is a parameter to configure default directory in which error logs about broker is stored. The default value is log/broker/error_log. The log file name for the broker error is broker_ name_id.err.

LOG_DIR

LOG_DIR is a parameter to configure the directory where SQL logs are stored. The default value is log/broker/sql_log. The file name of the SQL logs is broker_name_id.sql.log.

SLOW_LOG

SLOW_LOG is a parameter to configure whether to log. The default value is ON. If the value is ON, long transaction query which exceeds the time specified in LONG_QUERY_TIME or query where an error occurred is stored in the SLOW SQL log file. The name of file created is broker_name_id.slow.log and it is located under SLOW_LOG_DIR.

SLOW_LOG_DIR

SLOW_LOG_DIR is a parameter to configure the location of directory where the log file is generated. The default value is log/broker/sql_log.

SQL_LOG

SQL_LOG is a parameter to configure whether to leave logs for SQL statements processed by CAS when CAS handles requests from a client. The default value is ON. When this parameter is configured to ON, all logs are stored. The log file name becomes broker_name_id.sql.log. The file is created in the log/broker/sql_log directory under the installation directory. The parameter values are as follows:

  • OFF : Does not leave any logs.

  • ERROR : Stores logs for queries which occur an error. only queries where an error occurs.

  • NOTICE : Stores logs for the long-duration execution queries which exceeds the configured time/transaction, or leaves logs for queries which occur an error.

  • TIMEOUT : Stores logs for the long-duration execution queries which exceeds the configured time/transaction.

  • ON / ALL : Stores all logs.

SQL_LOG_MAX_SIZE

SQL_LOG_MAX_SIZE is a parameter to configure the maximum size of the SQL log file. You can set a unit as B, K, M or G, which stands for bytes, kilobytes(KB) or megabytes(MB) or gigabytes(GB) respectively. If you omit the unit, M will be applied. The default value is 10,000* (KB).

  • If the size of the SQL log file, which is created when the SQL_LOG parameter is configured to ON, reaches to the size configured by the parameter, broker_name_id.sql.log.bak is created.

  • If the size of the SLOW SQL log file, which is created when the SLOW_LOG parameter is configured to ON, reaches to the size configured by the parameter, broker_name_id.slow.log.bak is created.

SHARD

To use SHARD feature, configure the below parameters in cubrid_broker.conf as referring to cubrid_broker.conf.shard.

SHARD

It specifies to activate/deactivate SHARD feature. You can set this value as ON or OFF. The default is OFF.

SHARD_CONNECTION_FILE

The path of the shard connection file. The shard connection file should be located in $CUBRID/conf. For more information, see the shard connection file.

SHARD_DB_NAME

The name of the shard DB, used to verify the request for connection from an application.

SHARD_DB_PASSWORD

The user password of the backend shard DB, used to connect to the backend DBMS for the CAS process as well as to verify the request for connection from an application. Passwords of all shard DBs should be identical.

The environment variable can be used when you don’t want to expose SHARD_DB_PASSWORD to cubrid_broker.conf. The format of this environment variable name is <broker_name>_SHARD_DB_PASSWORD, and <broker_name> always should be changed as upper cases. For example, if the name of broker is shard1, the name of an environment variable which configures the shard DB password will be SHARD1_SHARD_DB_PASSWORD. But, if the SHARD feature is restarted by “cubrid broker restart” command, the environment variable of SHARD_DB_PASSWORD or the value of SHARD_DB_PASSWORD in cubrid_broker.conf must be configured.

export SHARD1_SHARD_DB_PASSWORD=shard123

Note

SHARD_DB_USER/SHARD_DB_PASSWORD parameters is deprecated. Therefore, it is recommended to deliver the connection information in an application.

SHARD_DB_USER

The name of the backend shard DB user, used to connect to the backend DBMS for the CAS process as well as to verify the request for connection from an application. User names on all shard DBs should be identical.

Note

SHARD_DB_USER/SHARD_DB_PASSWORD parameters is deprecated. Therefore, it is recommended to deliver the connection information in an application.

SHARD_IGNORE_HINT

When this value is ON, the hint provided to connect to a specific shard is ignored and the database to connect is selected based on the defined rule. The default value is OFF. It can be used to balance the read load while all databases are copied with the same data. For example, to give the load of an application to only one node among several replication nodes, the proxy automatically determines the node (database) with one connection to a specific shard.

SHARD_KEY_FILE

The path of the shard key configuration file. The shard key configuration file should be located in $CUBRID/conf. For more information, see the shard key configuration file.

SHARD_KEY_FUNCTION_NAME

The parameter to specify the name of the user hash function for shard key. For more information, see Setting User-Defined Hash Function.

SHARD_KEY_LIBRARY_NAME

Specify the library path loadable at runtime to specify the user hash function for the shard key. If the SHARD_KEY_LIBRARY_NAME parameter is set, the SHARD_KEY_FUNCTION_NAME parameter should also be set. For more information, see Setting User-Defined Hash Function.

SHARD_KEY_MODULAR

The parameter to specify the range of results of the default shard key hash function. The result of the function is shard_key(integer) % SHARD_KEY_MODULAR. The minimum value is 1, and the maximum value is 256. For related issues, see shard key configuration file and Setting User-Defined Hash Function.

SHARD_MAX_CLIENTS

The number of applications that can be concurrently connected by using the proxy. The default value is 256 and the maximum value is 10,000 per proxy.

SHARD_MAX_PREPARED_STMT_COUNT

The maximum size of statement pool managed by proxy. The default is 10,000.

SHARD_NUM_PROXY

The number of proxy processes.

SHARD_PROXY_CONN_WAIT_TIMEOUT

If there is no request anymore during the time specified in this parameter, CAS disconnect with DB. The default is 8h. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, second(s) will be applied. CAS which has a previous password should be exit because it cannot be used anymore; this feature protects that CAS is still kept unnecessarily.

SHARD_PROXY_LOG

The proxy log level. It can be set to one of the following values:

Proxy log level policy: Setting the higher level leaves all the lower logs.

  • Example) When SCHEDULE is set, ERROR | TIMEOUT | NOTICE | SHARD | SCHEDULE log is left.

  • ALL : All logs

  • ON : All logs

  • SHARD : Logs for selecting and processing shard DBs.

  • SCHEDULE : Logs for scheduling tasks.

  • NOTICE : Logs for key notices.

  • TIMEOUT : Logs for timeouts.

  • ERROR : Logs for errors.

  • NONE : No logs recorded.

  • OFF : No logs recorded.

SHARD_PROXY_LOG_DIR

The directory path where the proxy logs will be saved.

SHARD_PROXY_LOG_MAX_SIZE

The maximum size of the proxy log file. You can set a unit as B, K, M or G, which stands for bytes, kilobytes(KB) or megabytes(MB) or gigabytes(GB) respectively. If you omit the unit, K will be applied. The maximum value is 1,000,000(KB).

SHARD_PROXY_SHM_ID

A parameter to configure the ID of shared memory used by proxy

SHARD_PROXY_TIMEOUT

The maximum waiting time by which the statement is prepared or CAS is available to use. The default value is 30(seconds). If this value is 0, the waiting time is decided by the value of the query_timeout system parameter; if the value of query_timeout is also 0, the waiting time is infinite. IF the value SHARD_PROXY_TIMEOUT is larger than 0, the maximum value between query_timeout and SHARD_PROXY_TIMEOUT decides the waiting time. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, s will be applied.

Note

Required parameters for configuring proxy

To configure CUBRID proxy, you should specify SHARD_MAX_CLIENTS, MAX_NUM_APPL_SERVER and SHARD_NUM_PROXY.

  • In Linux, the number of file descriptors(fd) per proxy process is limited as follows.

    • “((SHARD_MAX_CLIENTS + MAX_NUM_APPL_SERVER) / SHARD_NUM_PROXY) + 256” <= 10,000

The following are detail descriptions on above formulas.

  • SHARD_MAX_CLIENTS is the maximum number of applications which access the SHARD system.

  • MAX_NUM_APPL_SERVER is the maximum number of all CASes which can access proxy system.

  • SHARD_NUM_PROXY is the maximum number of proxy processes which can use on the SHARD system.

  • “SHARD_MAX_CLIENTS / SHARD_NUM_PROXY” is the maximum number of applications which can access per proxy process.

  • “MAX_NUM_APPL_SERVER / SHARD_NUM_PROXY” is the maximum number of CASes which can access per proxy process.

  • 256 is the number of file descriptors which are used internally per process on Linux.

As an example of configuring SHARD parameters in Linux system, if you specify the maximum concurrent access number of applications (SHARD_MAX_CLIENTS) as 5,000, the maximum number of CASes(MAX_NUM_APPL_SERVER) as 200 and the maximum number of proxy process(SHARD_NUM_PROXY) as 1, then file descriptors per proxy process becomes (5,000 + 200)/1 + 256 = 5,456, and it is less than 10,000; it is possible configuration.

Regarding above, the following is the connection-relationship between each process. “proxy” intermediates a connection between “app. client” and “CAS”.

In the below, [] indicates a process, and -> indicates the requesting direction.

[app. client]   --(initial access request)-----------> [broker] (select proxy)
                <--(announce proxy to access)---
                -------------------------------------> [proxy] --(select CAS)--> [CAS] ---> [DB server]
                <-------------------------------------         <----------------       <---
                <--(now use the same proxy)---------->         <--------------->       <-->

broker is used only once when the application client requires the initial access, and then CUBRID keep connections among “[app. client] - [proxy] - [CAS] - [DB Server]”.

Also, CAS keeps connection with DB server after DB connection is completed.

Etc

MAX_STRING_LENGTH

MAX_STRING_LENGTH is a parameter to configure the maximum string length for BIT, VARBIT, CHAR and VARCHAR data types. If the value is -1, which is the default value, the length defined in the database is used. If the value is 100, the value acts like 100 being applied even when a certain attribute is defined as VARCHAR(1000).

SERVICE

SERVICE is a parameter to configure whether to run the broker. It can be either ON or OFF. The default value is ON. The broker can run only when this value is configured to ON.

SSL

SSL is a parameter to configure whether to apply packet encryption (SSL) to the broker. It can be either ON or OFF. The default value is OFF. When this value is configured to ON, packet encryption using TLS will be applied to the broker/CAS.

Warning

When the broker is configured to do TLS (SSL=ON), clients such as jdbc client must connect in encryption mode, otherwise the connection request to the broker will be rejected. The opposite is also true. The connection request of SSL clients to the non-SSL broker will be rejected.

SOURCE_ENV

SOURCE_ENV is a parameter used to determine the file where the operating system variable for each broker is configured. The extension of the file must be env. All parameters specified in cubrid.conf can also be configured by environment variables. For example, the lock_timeout parameter in cubrid.conf can also be configured by the CUBRID_LOCK_TIMEOUT environment variable. As another example, to block execution of DDL statements on broker1, you can configure CUBRID_BLOCK_DDL_STATEMENT to 1 in the file specified by SOURCE_ENV.

An environment variable, if exists, has priority over cubrid.conf. The default value is cubrid.env.

HA Configuration

Regarding HA configuration, see Environment Configuration.

SHARD Configuration

Regarding SHARD configuration, see Configuration.