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 (touples) are being removed and database space is reclaimed.
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:
plesk=> select relname,n_live_tup,n_dead_tup from pg_stat_all_tables order by n_dead_tup desc limit 20;
This metric will show you "live" and obsolete records for all tables. 20% of n_dead_tup (relatively to n_live_tup) clearly shows necessity of vacuuming.
Check database size:
plesk=> SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;
This metric will show how big tables are and, using data from the first step, it is possible to calculate amount of reclaimable disk space and therefore necessity of
All procedures below should be performed during the maintenance window because 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 pem stop service pau stop
More information about OA services could be obtained from KB 4642.
Reclaim unused disk space and perform obsolete touples cleanup by performing
plesk=> VACUUM FULL;
Duration of full vacuum scales with amount of obsolete records and overall size of the database. It is safe to plan around an hour of maintenance for this operation. This step could be skipped if you are performing regular weekly maintenance and could be performed monthly.
ANALYZEwhich will optimize SQL planner resulting in improved queries responsiveness:
plesk=> VACUUM ANALYZE;
Start OA services:
service pau start service pem start
Maintenance of indexes is better perform with caution. If you are experiencing frequent occurrences of index errors, please contact Odin technical support.