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. For CUBRID Manager server configuration, see CUBRID Manager Manual.

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.

Location of cubrid.conf File and How It Works

  • A database server process refers only to the $CUBRID/conf/cubrid.conf file. Database-specific configurations are distinguished by sections in the cubrid.conf file.
  • A client process (i) refers to the $CUBRID/conf/cubrid.conf file and then (ii) additionally refers to the cubrid.conf file in the current directory ($PWD). The configuration of the file in the current directory ($PWD/cubrid.conf) overwrites that of the $CUBRID/conf/cubrid.conf file. That is, if the same parameter configuration exists in $PWD/cubrid.conf and in $CUBRID/conf/cubrid.conf, the configuration in $PWD/cubrid.conf has the priority.

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 semicolons (;). You must take caution when you apply changes of parameter values.

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';

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 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 "client" parameters.

On the below table, if "Applied" is "server parameter", that parameter's applied scope is global. If "Applied" is "client parameter" or "client/server parameter", that parameter's applied scope is session.

  • 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" or "client/server parameter", that parameter's changed value is applied only to that DB session. In other words, the changed value is only applied to the applications which requested to change that value. For example, if block_ddl_statement parameter's value is changed into no, then only the application who requested to change cannot use DDL statements.
Category Parameter Name Applied Type Default Value Dynamic Change
Connection-Related Parameters cubrid_port_id client parameter int 1523  
check_peer_alive client/server parameter string both available
db_hosts client parameter string NULL available
max_clients server parameter int 100  
tcp_keepalive client/server parameter bool yes  
Memory-Related Parameters data_buffer_size server parameter byte 32768 * db_page_size  
index_scan_oid_buffer_size server parameter byte 4 * db_page_size  
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 1048576  
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  
Error Message-Related Parameters call_stack_dump_activation_list client/server parameter string NULL available
call_stack_dump_deactivation_list client/server parameter string NULL available
call_stack_dump_on_error client/server parameter bool no available
error_log client/server parameter string cub_client.err, cub_server.err  
error_log_level client/server parameter string SYNTAX available
error_log_warning client/server parameter bool no available
error_log_size client/server parameter int 8000000 available
Concurrency/Lock-Related Parameters deadlock_detection_interval_in_secs server parameter float 1.0 available
isolation_level client parameter int 3 available
lock_escalation server parameter int 100000  
lock_timeout client parameter msec -1 available
lock_timeout_message_type server parameter int 0 available
rollback_on_lock_escalation server parameter bool no available
Logging-Related Parameters adaptive_flush_control server parameter bool yes available
background_archiving server parameter bool yes available
checkpoint_every_size server parameter byte 10000 * log_page_size  
checkpoint_interval server parameter msec 720min available
force_remove_log_archives server parameter bool yes available
log_buffer_size server parameter byte 128 * log_page_size  
log_max_archives server parameter int INT_MAX available
log_trace_flush_time server parameter msec 0 DBA only
max_flush_size_per_second server parameter byte 10000 * db_page_size available
sync_on_flush_size server parameter byte 200 * db_page_size available
Transaction Processing-Related Parameters async_commit server parameter bool no  
group_commit_interval_in_msecs server parameter msec 0 available
Statement/Type-Related Parameters add_column_update_hard_default client parameter bool no available
alter_table_change_type_strict client/server parameter bool no available
ansi_quotes client parameter bool yes  
block_ddl_statement client parameter bool no available
block_nowhere_statement client parameter bool no available
compat_numeric_division_scale client/server parameter bool no available
default_week_format client/server parameter int 0 available
group_concat_max_len server parameter byte 1024 available
intl_mbs_support client parameter bool no  
no_backslash_escapes client parameter bool yes  
only_full_group_by client parameter bool no available
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 bool no available
string_max_size_bytes client/server parameter int 1048576 available
Query Plan Cache-Related Parameters max_plan_cache_entries client/server parameter int 1000  
Utility-Related Parameters backup_volume_max_size_bytes server parameter byte 0  
communication_histogram client parameter bool no available
compactdb_page_reclaim_only server parameter int 0  
csql_history_num client parameter 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    
auto_restart_server server parameter bool yes available
index_scan_in_oid_order client parameter bool no available
index_unfill_factor server parameter float 0.05  
java_stored_procedure server parameter bool no  
multi_range_optimization_limit server parameter int 100 available
pthread_scope_process server parameter bool yes  
server server parameter string    
service server parameter string    
session_state_timeout server parameter sec 21600  
single_byte_compare server parameter bool no  
sql_trace_slow server parameter msec -1 available
sql_trace_execution_plan server parameter bool no available
use_orderby_sort_limit server parameter bool yes available
  • 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.
  • log_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 three sections:

  • Used when the CUBRID service starts: [service] section
  • Applied commonly to all databases: [common] section
  • Applied individually to each database: [@<database>] 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.

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=server, broker, manager

# 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=4M

# 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=4M
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

Other Parameters

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

Parameter Name Type Default Value Min Max
access_ip_control bool no    
access_ip_control_file string      
auto_restart_server bool yes    
index_scan_in_oid_order bool no    
index_unfill_factor float 0.05 0 0.5
java_stored_procedure bool no    
multi_range_optimization_limit int 100 0 10000
pthread_scope_process bool yes    
server string      
service string      
session_state_timeout sec 21600(6 hours) 60(1 minute) 31536000(1 year)
single_byte_compare bool no    
sql_trace_slow msec -1(inf) 0 86400000(24 hours)
sql_trace_execution_plan bool no    
use_orderby_sort_limit bool yes    

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.

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).

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.

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.

pthread_scope_process

pthread_scope_process is a parameter to configure the contention scope of threads. It only applies to AIX systems. If the parameter is set to no, the contention scope becomes PTHREAD_SCOPE_SYSTEM; if it is set to yes, it becomes PTHREAD_SCOPE_PROCESS. The default value is yes.

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 21600 seconds(6 hours), and this parameter's unit is second.

single_byte_compare

single_byte_compare is a parameter to configure whether to compare strings in single byte unit. If the parameter is set to no, which is the default value, strings are compared in two byte unit; if it is set to yes, they are compared in single byte unit. That is, you can retrieve/compare strings on data stored as UTF-8.

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 86400000 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.

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 the 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(cub_broker) MASTER_SHM_ID int 30001  
Parameter by Broker

Access

Broker App. Server(CAS)

ACCESS_LIST string    
ACCESS_LOG string ON available
ACCESS_MODE string RW available
BROKER_PORT int 30000(max : 65535)  
KEEP_CONNECTION string AUTO available
PREFERRED_HOSTS string    
APPL_SERVER string CAS  
APPL_SERVER_MAX_SIZE MB Windows 32bit: 40, Windows 64bit: 80, Linux: 0 available
APPL_SERVER_MAX_SIZE_HARD_LIMIT MB 1024 available
APPL_SERVER_PORT int BROKER_PORT+1  
APPL_SERVER_SHM_ID int 30000  
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 available
LONG_TRANSACTION_TIME sec 60 available
MAX_PREPARED_STMT_COUNT int 2000(min: 1)  
MAX_QUERY_TIMEOUT sec 0(max: 86400(sec)) available
SESSION_TIMEOUT sec 300  
STATEMENT_POOLING string ON available
Logging ERROR_LOG_DIR string log/broker/error_log  
LOG_BACKUP string OFF available
LOG_DIR string log/broker/sql_log  
SLOW_LOG string ON available
SLOW_LOG_DIR string log/broker/sql_log  
SQL_LOG string ON available
SQL_LOG_MAX_SIZE KB 10000 available
Etc MAX_STRING_LENGTH int -1  
SERVICE string ON  
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

Broker Configuration File Related Environment Variables

You can specify the location of broker configuration file (cubrid_broker.conf) file by using the CUBRID_BROKER_CONF_FILE variable. The variable is used when executing several brokers with different configuration.

Common Parameters

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

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 Server 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 Server Access.

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.

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_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_LOG

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

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.

APPL_SERVER

APPL_SERVER is a parameter to configure types of CAS generated and managed by the CUBRID broker. The default value is 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. At the time 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 1024 (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 configure the connection port of CAS that communicates with application clients; it is used only in Windows. In Linux, the application clients and CAS use the UNIX domain socket for communication; therefore, APPL_SERVER_PORT is not used. The default value is determined by adding plus 1 to the BROKER_PORT parameter value. The number of ports used is the same as the number of CAS, starting from the specified port's number plus 1. For example, when the value of BROKER_PORT is 30,000 and the APPL_SERVER_PORT parameter value has been configured, and if the MIN_NUM_APPL_SERVER value is 5, five CASes uses the ports numbering between 30,001 and 30,005, respectively. 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 exists between an application and a CUBRID broker, the communication port 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).

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.

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, ODBC, OLE DB, Perl, Python, and Ruby commit automatically. The default value is ON. This parameter does not affect applications implemented in JDBC. In case of using ODBC, malfunction can occur if this parameter is ON; you must set it to OFF, in this case.

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.

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.

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, OFF or AUTO. If this value is OFF, clients are connected to servers in transaction unit; for 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.

LOG_BACKUP

LOG_BACKUP is a parameter to configure whether to back up access and error log files of the broker when CUBRID stops. The default value is set to OFF. An access log file (broker_name.access) in the $CUBRID/log/broker directory is deleted when CUBRID stops. If the value is set to ON, an access log file is stored (backed up) as broker_name.access.YYYYMMDD.HHMI when CUBRID stops.

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.

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).

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 can be value in msec. with a decimal separator. For example, the value should be configured into 0.5 to configure 500 msec. Note that a parameter is configured to 0, it is not evaluated as a long-duration transaction.

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.

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.

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.

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).

PREFERRED_HOSTS

PREFERRED_HOSTS is a parameter that must be configured if the broker mode is set to PHRO. The default value is NULL. FOR details, see cubrid_broker.conf of "Administrator's Guide."

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.

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.

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.

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.

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.

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.

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.