Operations Automation in its day-to-day work relies on the integrated database. What actions could be done by system administrators to keep database in good shape?
Despite that OA database uses modern PostgreSQL engine, it requires some maintenance operations to be performed on a regular basis by system administrator. Poorly maintained database could lead to slow performance of queries which will proportionally slow down all interactions with OA interface and even may interfere with services provisioning.
Universal solution for the database maintenance is Vacuuming. It is standard procedure during which obsolete records (tuples) are being removed and database space is reclaimed.
Since OA 7.1 with PostgreSQL 9.6 autovacuum routine is enabled. Autovacuum performs periodic cleanup of DB tables basing on bloat level and resources required for vacuuming. While autovacuum tries to be 'invisible' in terms of DB performance and does not eliminate all obsolete tuples on sight, it allows to maintain reasonable level of dead records across the database.
At first step, gather "maintenance" metrics via executing the following queues. These queries do not require stop of service and could be executed anytime.
Check the necessity of vacuuming:
oss=# SELECT relname,n_live_tup,n_dead_tup, CASE WHEN n_live_tup=0 THEN round((n_dead_tup::numeric/100), 2) || '%' ELSE round((n_dead_tup::numeric/n_live_tup::numeric*100), 2) || '%' END as bloat, pg_size_pretty(pg_total_relation_size('"' || t.table_schema || '"."' || t.table_name || '"')) AS size FROM pg_stat_all_tables p JOIN information_schema.tables t on p.relname = t.table_name ORDER BY pg_total_relation_size('"' || t.table_schema || '"."' || t.table_name || '"') desc limit 50;
This metric will show you "live" and obsolete records for all tables sorted by table sizes. 20% of bloat of big table clearly shows the necessity of vacuuming. The decision should take into account table size and its bloat level. Example of output follows:
relname | n_live_tup | n_dead_tup | bloat | size --------------------------------------+------------+------------+--------+-------- aps_property_value | 711182 | 31818 | 4.47% | 243 MB proxies_config_files_contents | 478 | 0 | 0.00% | 201 MB aps_resource_link | 235545 | 1006 | 0.43% | 90 MB resources_usage_log | 289225 | 27806 | 9.61% | 65 MB
In this example, 9.6% bloat level of a table
resources_usage_log consume less space then 4.47% of
aps_property_value. Effectively, bloat level shows the amount of reclaimable disk space and therefore necessity of
All procedures below should be performed during the maintenance window since some operations require stop of OA services and database performance will degrade during vacuuming.
Perform OA database backup as described in KB 113963:
# /usr/local/pem/bin/backup.sh [-o output_directory] [-d dbhost] –t db
Stop OA services on the management node:
service pa-agent stop service pau stop
It is required to stop any services which use database since VACUUM will block any requests to processed tables. More information about OA services could be obtained from KB 4642.
Reclaim unused disk space and perform obsolete tuples cleanup by performing
oss=> VACUUM FULL;
VACUUM FULLcopies all 'alive' records from each table to temporary one. So this step requires amount of free diskspace equal to the biggest table of the database.
Duration of full vacuum scales with amount of obsolete records and overall size of the database. It is safe to plan at least two hours of maintenance for this operation. This step could be skipped if you are performing regular weekly maintenance and could be performed monthly.
Notice: VACUUM could be canceled at runtime with to harmful consequences.
It is required to update planner after
VACUUM FULLsince Postgres planner relies on statistics on data distribution across the tables and VACUUMing changes how data is distributed. Perform
ANALYZEwhich will optimize SQL planner resulting in improved queries responsiveness:
oss=> VACUUM ANALYZE;
Start OA services:
service pau start service pa-agent start
Maintenance of indexes is not usually required unless indexes are corrupted or invalid. If you are experiencing frequent occurrences of index errors, please contact Odin technical support.