CUBRID Configuration Guide to Launch Services
This guide describes how to configure the volume and memory of CUBRID for live services, and how to configure CUBRID according to the number of concurrent users.
Table of Contents
DB Creation and Volume Configuration
If an operator creates a DB using the createdb utility after setting up CUBRID without options, a DB with a size of 20 MB (5,000 pages) will be created by default. While a DB of this size is fine for testing and development purposes, it is not optimal for live services.
To accommodate the data generated by live services, use the addvoldb utility to add more volume to a DB. If possible, create each volume on a physically separate drive. Distributing I/O increases performance.
The following is an example of configuring a DB so that it will have 400 MB allocated to the generic volume, 200 MB to the active log, 10 GB to the data volume, 4 GB to the index volume, and 2 GB to the temp volume. In this example, the generic, data, and index volumes are in the same partition, while the active log and temp volume shares another partition to distribute I/O.
cubrid createdb -F /db/boarddb/data -s 8192 -p 50000 ?L /db/boarddb/log -l 25000 boarddb cubrid addvoldb -S -p data -F /db/boarddb/data boarddb 1250000 cubrid addvoldb -S -p index -F /db/boarddb/data boarddb 500000 cubrid addvoldb -S -p temp -F /db/boarddb/log boarddb 250000
In the example above, the 64-bit version of CUBRID is used; for the 32-bit version, create multiple volumes of 2 GB (250,000 pages) instead, as resource usage is limited.
cubrid createdb -F /db/boarddb/data -s 8192 -p 50000 ?L /db/boarddb/log -l 25000 boarddb cubrid addvoldb -S -p data -F /db/boarddb/data boarddb 250000 cubrid addvoldb -S -p data -F /db/boarddb/data boarddb 250000 cubrid addvoldb -S -p data -F /db/boarddb/data boarddb 250000 cubrid addvoldb -S -p data -F /db/boarddb/data boarddb 250000 cubrid addvoldb -S -p data -F /db/boarddb/data boarddb 250000 cubrid addvoldb -S -p index -F /db/boarddb/data boarddb 250000 cubrid addvoldb -S -p index -F /db/boarddb/data boarddb 250000 cubrid addvoldb -S -p temp -F /db/boarddb/log boarddb 250000
Configuration Modification
Some parameters in the configuration file should be modified according to system specifications and the number of concurrent users. For more information on the parameters described below, see the Manual > Performance Tuning > Database Server Configuration, and Broker Configuration sections, respectively.
Parameters Related to the Number of Concurrent Users
Modify the value of max_clients in cubrid.conf and MAX_NUM_APPL_SERVER (the number of maximum CAS processes) in cubrid_broker.conf to configure the number of concurrent users.
The value of MAX in the WAS connection pool and the system memory specification affect this configuration.
First, configure the value of MAX in the WAS connection pool and the value of MAX_NUM_APPL_SERVER in cubrid_broker.conf so that the two values are the same. For example, the value above must be identical to the value configured in maxActive in Tomcat. Configure this value in consideration of system memory.
The maximum amount of memory that a single CAS process uses is 40 MB in the 32-bit version and 80 MB in the 64-bit version. For example, if the value of MAX_NUM_APPL_SERVER is 100 in the 64-bit version of CUBRID, up to 8 GB of system memory can be used.
Modify the following in $CUBRID/conf/cubrid_broker.conf:
[%query_editor] MAX_NUM_APPL_SERVER =20 [%BROKER1] MAX_NUM_APPL_SERVER =80
Next, modify max_clients according to the value of MAX_NUM_APPL_SERVER.
The value of max_clients should be a number that equals the sum of all the values of MAX_NUM_APPL_SERVER, added by 10.
Modify the parameter of $CUBRID/conf/cubrid.conf as shown below:
max_clients=110
Parameters Related to Memory and Disks
Modify and add the parameters of $CUBRID/conf/cubrid.conf that are related to memory and disks, as shown below:
data_buffer_pages=250000 sort_buffer_pages=64 temp_file_memory_size_in_pages=20 #add this parameter temp_file_max_size_in_pages=250000 #add this parameter
It is recommended to configure the value of the data_buffer_pages so that it does not exceed two-thirds of the total system memory. If the value of data_buffer_pages is 250,000, as in the example above, 2 GB (250,000 * 8K (the value configured by createdb -s)) of memory will be used. Modify this value so that it is appropriate for the broker memory usage.
temp_file_max_size_in_pages is a parameter that specifies the maximum number of pages that can be allocated to save to the temp volume on a disk. This parameter is necessary to prevent a bad query from causing a service failure by infinitely creating temp volumes until the disk becomes full. In the example above, it is set to use up to 2 GB of free space.
How to Configure Parameters That Execute Auto-Commit for the SELECT Statements in a PHP Application
When the connection between an application client and the CAS of a broker is terminated after they have processed a transaction, the CAS becomes idle and the broker establishes a new connection with other idle CASs in order to process the application request.
An infinite loop may occur when processing multiple time-consuming transactions, such as file downloads, with an application logic that is implemented to execute multiple queries within a single transaction. This is because all the CASs are bound to the client as long as there is a transaction that is being processed, preventing the broker from processing a request for a new connection.
To permanently solve this problem, make the unit of transactions in an application small. Should rewriting code prove to be difficult, configure the SELECT_AUTO_COMMIT broker parameter provided by CUBRID so that the SELECT statements in an application will be automatically committed when they are executed. By using this method, more idle CASs can be secured even when multiple connection requests are made in a short time, as each CAS commits a transaction only after the SELECT statements have been executed internally.
The SELECT_AUTO_COMMIT parameter can be modified even when the broker is currently in use.
- To modify the parameter when the broker is currently in use:
- Check the name of the broker that needs to be modified with the 'cubrid broker status -b' command.
- Execute broker_changer on the target broker to add the parameter.
- To modify the parameter when the broker is currently stopped:
- Add the SELECT_AUTO_COMMIT parameter to the corresponding broker (broker1) in CUBRID/conf/cubrid_broker.conf and save it.
- When cubrid broker restart broker1 is executed, the newly stored cubrid_broker.conf file will be applied.
[cubrid@mdb.002 ~]$ cubrid broker status -b @ cubrid broker status NAME PID PORT AS JQ REQ TPS QPS LONG-T LONG-Q ERR-Q =========================================================================== * query_editor 26832 30000 1 0 0 0 0 0/60.0 0/60.0 0 * broker1 26866 33000 29 0 1854013 1500655 8387645 127/60.0 139/60.0 208 [cubrid@mdb.002 ~]$ broker_changer broker1 SELECT_AUTO_COMMIT ON OK
[%BROKER1] SERVICE =ON BROKER_PORT =33000 MIN_NUM_APPL_SERVER =10 MAX_NUM_APPL_SERVER =100 APPL_SERVER_SHM_ID =33000 #LOG_DIR =log/broker/sql_log LOG_DIR =/home1/cubrid/sql_log ERROR_LOG_DIR =log/broker/error_log SQL_LOG =ON TIME_TO_KILL =120 SESSION_TIMEOUT =300 KEEP_CONNECTION =AUTO SELECT_AUTO_COMMIT =ON [cubrid@mdb.002 ~]$ cubrid broker restart broker1