Logging-Related Parameters

The following are parameters related to logs used for database backup and restore. The type and value range for each parameter are as follows:

Parameter Name

Type

Default Value

Min

Max

log_buffer_pages

int

50

3

media_failure_support

bool

yes

log_max_archives

int

INT_MAX

0

background_archiving

bool

yes

page_flush_interval_in_msecs

int

0

-1

checkpoint_interval_in_mins

int

720

1

checkpoint_every_npages

int

10000

10

adaptive_flush_control

bool

true

max_flush_pages_per_second

int

10000

1

INT_MAX

sync_on_nflush

int

200

1

INT_MAX

log_buffer_pages

log_buffer_pages is a parameter that configures the number of log buffer pages to be cached in the memory. The default value is 50. If the value of the log_buffer_pages parameter is big, performance can be improved (due to the decrease in disk I/O) when transactions are long and numerous. It is recommended to configure an appropriate value considering the memory size and operations of the system where CUBRID is installed.

The number of log buffer pages = the value of the log_buffer_pages parameter

Database page size = the value of the page size specified by the -s option of the cubrid createdb utility during the database creation

media_failure_support

media_failure_support is a parameter that specifies whether or not to store archive logs in case of storage media failure. If the parameter is configured to yes, which is the default value, all active logs are copied to archive logs when the active logs are full and the transaction is active. If it is configured to no, archive logs created after the active logs are full are deleted automatically. Note that archive logs are deleted automatically if the value of the parameter is configured to no.

Note If you specify this parameter to no, the backgroud_archiving parameter is deactivated, accordingly.

log_max_archives

log_max_archives is a parameter that sets the maximum number of archive log files to record if media_failure_support is set to yes. The minimum value is set to zero, and the default is INT_MAX. For example, when log_max_archives=3 in cubrid.conf, the most recent three archive log files are recorded. If a fourth archiving log file is generated, the oldest archive log file is automatically deleted. The information about the deleted archive log is recorded into the *_login file.
However, if an active transaction still refers to an existing archive log, the archive log will not be deleted. That is, if a transaction starts at the point that the first archive log is generated, and it is still active until the fifth archive log is generated, the first archive log cannot be deleted.

background_archiving

background_archiving is a parameter that generate a temporary archive log periodically at a specific time if media_failure_support is set to yes. This is useful when balancing disk I/O load due to the archive log process. The default is yes.

checkpoint_interval_in_mins

checkpoint_interval_in_mins is a parameter that sets cycle (in minutes) for checkpoint to be executed. The default value is 720.

Checkpoint flushes log files(dirty page) remained in data buffers to a disk. It can restore data back to the latest checkpoint if failure happens. If high volume of log files are stored in a disk due to checkpoint, it may cause disk I/O. Therefore, you should set the checkpoint cycle properly to prevent database operation failure.

The checkpoint_interval_in_mins and checkpoint_every_npages parameters are related to setting checkpoint cycle. The checkpoint is periodically executed whenever the time specified in checkpoint_interval_in_mins parameter has elapsed or the number of log pages specified in checkpoint_every_npages parameter has reached.

checkpoint_every_npages

checkpoint_every_npages is a parameter that sets checkpoint cycle by log page. The default value is 10,000. You can distribute disk I/O overload at the checkpoint by specifying lower number in the checkpoint_every_npages parameter, especially in the environment where INSERT/UPDATE are heavily loaded at a specific time.

page_flush_interval_in_msecs

The parameter page_flush_interval_in_msecs specifies the interval in milliseconds (msec) at which dirty pages in a data buffer are flushed to a disk. Its default value is 0. If this parameter is set to -1 (the minimum value), dirty pages are flushed to the disk only at the checkpoint, or when pages are swapped.

This is a parameter that is related to I/O load and buffer concurrency. For this reason, you must set its value in consideration of the workload of the service environment.

adaptive_flush_control

The parameter adaptive_flush_control automatically adjusts the flush capacity at every 50 ms depending on the current status of the flushing operation. Its default value is yes. That is, this capacity is increased if a large number of INSERT or UPDATE operations are concentrated at a certain point of time and the number of flushed pages reaches the max_flush_pages_per_second parameter value; and is decreased otherwise. In the same way, you can distribute the I/O load by adjusting the flush capacity on a regular basis depending on the workload.

max_flush_pages_per_second

The parameter max_flush_pages_per_second specifies the maximum flush capacity when the flushing operation is performed from a buffer to a disk. Its default value is 10,000. That is, you can prevent concentration of I/O load at a certain point of time by setting this parameter to control the maximum flush capacity per second.

If a large number of INSERT or UPDATE operations are concentrated at a certain point of time, and the flush capacity reaches the maximum capacity set by this parameter, only log pages are flushed to the disk, and data pages are no longer flushed. Therefore, you must set an appropriate value for this parameter considering the workload of the service environment.

sync_on_nflush

The parameter sync_on_nflush sets the interval in pages between after data and log pages are flushed from buffer and before they are synchronized with FILE I/O of operating system. Its default value is 200. That is, the CUBRID Server performs synchronization with the FILE I/O of the operating system whenever 200 pages have been flushed. This is also a parameter related to I/O load.