Skip to content

Maintain Tables

Introduction

To assure optimal performance of your database, it is important to maintain the database.

Some general and important database maintenance advice for PostgreSQL databases includes:

  • Vacuum Analyze every week, keeps your stats updated.
  • Reindex your heavily updated tables every month.
  • Plan for vacuum full events.
  • Monitor your connections and load.

In MIKE OPERATIONS there are some core tables that needs to be maintained as well to assure optimal performance.

  • Change log
    The change log table of MIKE OPERATIONS, contains information about changes (create, update and deletes) made to entities of MIKE OPERATIONS like time series, spreadsheets, feature classes etc.
    Cleaning this table will ensure that reading, creating, updating and deleting entities runs optimal.
  • Events log
    The event table of MIKE OPERATIONS contains information about actions communicated to the DHI Event Manager service. This is mainly information about the status on running jobs.
    Cleaning this table will ensure that the Event manager keeps being responsive.
  • Job log
    The job log contains information about job execution history. Maintain this table to ensure a responsive job execution. Also, the job history in MIKE OPERATIONS Web will benefit of a cleanup.
  • Initial Conditions
    Initial conditions of scenarios, fills up the database, and can potentially slow down the initial startup of simulations.

Event information

The event table of MIKE OPERATIONS contains information about actions communicated to the DHI event manager service. This is mainly information about the status on running jobs.

Cleaning this table will ensure that the Event manager keeps being responsive.

JobLog

The job instance table contains information about job runs.

Clean this table will ensure optimal performance of the job log in especially MO Web.

Initial conditions

Initial Conditions should be maintained separately (see ManageInitialConditions job task).

Analysis

MIKE Workbench comes with a Database Usage analyzer. The analyzer is accessible through the Settings menu.

This opens a table showing the size of all tables in the current database workspace.

The biggest tables are shown on top.

Change Log

Change Logs are associated to all types of data (Feature class, Timeseries, etc.).

Every time a data object is edited, a change log is written.

This is visible in the "Change Log Entries" window.

Saving change logs can be stoped by editing specific configuration files in the bin folder. This is done by setting the property "ChangeLogEntryOnSave" to False. This is available for the following files:

  • DHI.Solutions.SpreadsheetManager.Config
  • DHI.Solutions.TimeseriesManager.Config

If the saving change logs is to be disable for all managers, then it is possible to delete the following entries from the runtime.config file.

  • DHI.Solutions.MetadataManager.Tools.ChangeLogQueryTool.ChangeLogQueryTool
  • DHI.Solutions.MetadataManager.Business.ChangeLogModule
  • DHI.Solutions.MetadataManager.Business.ChangeLogEntry

Deleting existing change logs can be done with the dedicated task in the Job Manager.

Time series

Time series can be analyzed to identify the ones taking the most space.

Timeseries can be cleaned up automatically with Job tasks:

Blobs

Blobs are binary files that can come from many different managers. For example:

  • Scenario manager stores models and initial conditions as BLOBs.
  • Timeseries manager can stores timeseries values as BLOBs.
  • All documents in Document managers are stored as BLOBs.
  • All spreadsheets in Spreadsheet Manager are stored as BLOBs.

Running the analysis shows the types of BLOB taking the most space. 

It is important to understand that sometime the reference can be lost between the original data object (e.g. timeseries) and the entries in the blob table (time series values). Those entries are called orphans and should be cleaned up. Please read the chapter One-off clean up below for more information.

GIS Data

All Feature Class and Raster stored in the GIS manager are saved in a specific table in the database. Those tables' names start with fc_. In order to know what type each entry starting with fc_ refers to, you should look at the column Entity Name.

Automatic clean up

In order to ensure real time systems do not grow in size indefinitely, it is important to set up some automatic cleaning routines. Depending on the implementation, it is possible to set up Job tasks or Workflow activities.

Job tasks

Several Job tasks are available in the Maintenance group. More information can be found in the Job Manager section.