Maintaining BellaDati

It's highly recommended to monitor status of BellaDati system in order to avoid system slowdown or system outages.

We recommend to monitor following metrics:

http status (application status)
CPU Utilization
Free storage space
Free able memory

As best practice, it is recommended to monitor these metrics in weekly intervals.

Script 1 (Frequent imports)

In case imports in your BellaDati instance are scheduled in frequent time intervals, following database script should be executed (execute this script in PostgreSQL):

--Delete data_source_executions

delete from bi.data_source_execution where canceled_when is not null;

delete from bi.data_source_execution where executed_when < ‘2016-07-01’;--more than 30 days old

vacuum analyze bi.data_source_execution;

--Delete import_table entries

delete from bi.import_table it where it.id not in (select import_table_id from bi.data_source ds where ds.import_table_id is not null union all select e.import_table_id from bi.data_source_execution e where e.import_table_id is not null);

vacuum analyze bi.import_table;

Script 2 (Too many member tables)

In case there are too many member tables (sorting), following database script should be executed multiple times (execute this script in PostgresSQL):

CREATE OR REPLACE FUNCTION drop_tables(IN _schema TEXT, IN _parttionbase TEXT)

RETURNS void

LANGUAGE plpgsql

AS

$$

DECLARE

row record;

BEGIN

FOR row IN

SELECT

table_schema,

table_name

FROM

information_schema.tables

WHERE

table_schema = _schema

AND

table_name ILIKE (_parttionbase || '%')

LIMIT 1000

LOOP

EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);

RAISE INFO 'Dropped table: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);

END LOOP;

END;

$$;

BEGIN TRANSACTION;

SELECT drop_tables('public', 'members_');

COMMIT;

Page tree

Maintaining BellaDati

Script 1 (Frequent imports)

Script 2 (Too many member tables)