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 (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's applied scope is global. If "Applied" is "client parameter" or "client/server parameter", that parameter's applied scope is session.

"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  
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)  
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  
generic_vol_prealloc_size server parameter   byte 50M  
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 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 SYNTAX DBA only
error_log_warning client/server parameter   bool no DBA only
error_log_size client/server parameter   int 8,000,000 DBA only
Concurrency/Lock-Related Parameters deadlock_detection_interval_in_secs server parameter   float 1.0 DBA only
isolation_level client parameter O int 3 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 10,000 * log_page_size  
checkpoint_interval server parameter   msec 6min DBA only
force_remove_log_archives server parameter   bool yes DBA only
log_buffer_size server parameter   byte 128 * 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
sync_on_flush_size server parameter   byte 200 * db_page_size DBA only
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 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
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
no_backslash_escapes client parameter   bool yes  
only_full_group_by client parameter O 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 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
Query Plan Cache-Related Parameters max_plan_cache_entries client/server parameter O int 1,000  
max_filter_pred_cache_entries client/server parameter   int 1,000  
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
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  
multi_range_optimization_limit server parameter O int 100 DBA only
optimizer_enable_merge_join client parameter O bool no available
pthread_scope_process server parameter   bool yes  
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
  • 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 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=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 Min Max
access_ip_control bool no    
access_ip_control_file string      
agg_hash_respect_order bool yes    
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 10,000
optimizer_enable_merge_join bool no    
pthread_scope_process bool yes    
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    

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

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.

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.

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

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 string 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
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  
LOG_DIR string log/broker/sql_log  
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(sec)  
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

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 its 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

APPL_SERVER is a parameter to configure types of CAS generated and managed by the CUBRID broker. The default value is CAS.

In SHARD environment, it is used when you want to change the shard DB not CUBRID, but the other DB; if this value is CAS, CUBRID is used. If you want to use MySQL as a shard DB, set one of below values.

APPL_SERVER setting value Description
CAS_MYSQL MySQL C Connector 6.1 is used
CAS_MYSQL51 MySQL C Connector 5.1 is used
CAS_MYSQL61 MySQL C Connector 6.1 is used

By the setting vaule, that version's MySQL library should be installed; you can download MySQL library MySQL in MySQL homepage(http://dev.mysql.com/downloads/connector/c).

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

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

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.

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.

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_LOB) 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:

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

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.