Database Server Redo Log

This is one of the technical infrastructure data gathering methodologies and performance recommendations for server machines. The full list is defined in the Server Environment Recommendations topic here.

The STEP database has a redo log for recovery operations. The redo log consists of two or more pre-allocated files that store all changes made to the database as they occur. Every instance of the database has an associated redo log to protect the database in case of an instance failure.

Every time the database performs, a redo log switch triggers a database checkpoint, which brings the database to a halt while the checkpoint runs. The general performance of the database is impacted if the redo log switch is performed too often.

Important: The database should perform a redo log switch of about 50 times per day (every 30 minutes) and the redo log switches per day should be stable.

Follow these steps to analyze the redo log file of the database via the DB ToolBox:

  1. On the application server, go to the DB ToolBox, which is typically saved in the '/opt/stibo/step/admin/app-server-toolbox/' folder.
  2. Start the ToolBox using one of these commands:
  • ./db-toolbox.pl
  • perl db-toolbox.pl
  1. Execute DB ToolBox number 42: 'Show redolog history'

Example

This database performed a redo log switch 2-to-3 times per minute in several days over the last 28 days. That negatively impacts the general performance of the database. Additionally, over the last 28 days, the redo log switch varies from about 20 times per day to about 500 times per day.

The redo log file size is 256 MB.

The statistics of the number of redo log switches per day are as follows:

In this example, the recommendation is to increase the size of the redo log files, and to minimize the number of redo log switches to one every half hour. This will positively impact the database performance and can be achieved by:

  • Increasing the size of the redo log files from 256 MB to 5 GB.
  • Setting the database parameter ARCHIVE_LAG_TARGET=1800, which will make it switch log files every 30 minutes.