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:
As best practice, it is recommended to monitor these metrics in weekly intervals.
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;
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
;