Introduction: 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.
Applicable system: CUBRID 10.2
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 512 MB 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 10 GB allocated to the permanent volume, 256 MB to the active log and 2 GB to the temp volume. In this example, the data 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 –L /db/boarddb/log --log-volume-size=256M --db-volume-size=5G boarddb cubrid addvoldb -S -p data -F /db/boarddb/data --db-volume-size=5G boarddb cubrid addvoldb -S -p temp -F /db/boarddb/log --db-volume-size=2G boarddb
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 > CUBRID Management > System Parameters > Configuring the Database Server 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 80 MB in the 64-bit Windows version by default. For example, if the value of MAX_NUM_APPL_SERVER is 100 in the 64-bit Windows 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 equal to 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:
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_size=2G sort_buffer_size=2M 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_size so that it does not exceed two-thirds of the total system memory. If the value of data_buffer_size 2G, as in the example above, 2 GB 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 creating temp volumes infinitely until the disk becomes full. In the example above, it is set to use up to 4 GB (250,000 * 16K (the default value of db page size) of free space.