Security_Privileges table has grown to a huge size
Article ID: KB0083062
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | - |
Description
Autovacuum not running automatically for Security_Privileges table
Issue/Introduction
Security_Privileges table has grown to a huge size
Resolution
1) Validate that autovacuuming is running on the tables: (should see very recent last_autovacuum timestamp)
a. SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = ‘cisrepo’ and relname = ‘security_privileges';
b. SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = ‘cisrepo’ and relname = ‘security_privileges_garbage';
2) Check current physical disk usage:
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
3) Stop monitor service and perform manual vacuum to recover physical disk space of all tables:
a. VACUUM FULL ANALYZE VERBOSE;
4) Restart Monitor process
a. SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = ‘cisrepo’ and relname = ‘security_privileges';
b. SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = ‘cisrepo’ and relname = ‘security_privileges_garbage';
2) Check current physical disk usage:
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
3) Stop monitor service and perform manual vacuum to recover physical disk space of all tables:
a. VACUUM FULL ANALYZE VERBOSE;
4) Restart Monitor process
Feedback
Yes
No
Powered by
