Dear Postgres gurus,
Yesterday, we run a purging process to purge a big number of records from one table (transaction_log).
The purging process run several times between 3 PM to 5 PM, and another one scheduled at 2 AM..
No of records was purge from 63,350,117 to 6,854,768 .
The “autovacuum : VACUUM ANALYZE” was automatically started at 16:15.
While the autovacuum running, there are several purging process run.
The autovacuum process is still running now.(so, it’s been running for about 23 hours).
This is from pg_stat_activity:
datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend
_start | xact_start | query_start | waiting | current_query
-------+-------------+---------+----------+-------------+--------------------------+--------------+-----------------+-------------+----------------
---------------+-------------------------------+-------------------------------+---------+---------------------------------------------------
16390 | tms | 22135 | 10 | postgres | | | | | 2013-12-12 16:1
5:09.413137-05 | 2013-12-12 16:15:09.435742-05 | 2013-12-12 16:15:09.435742-05 | f | autovacuum: VACUUM ANALYZE public.transaction_log
My questions:
1. Is it normal for vacumm to run that long (23 hour)? Is there a way to verify whether the vacuum process is running properly and making any progress?
If NOT, what should I do?
2. Can I increase the maintenance_work_mem parameter to make the existing process faster? Will it affect the existing process?
(I believe this parameter can be changed with reload command only?)
3. Do you think it’s better to do VACUUM FULL, consider the amount of records deleted?
4. Any other input/recommendation you can give us?
We’re using Vpostgres version 9.1.3 on CentOS 6.3. 12 cores and 64 GB memory.
But, we’re using default setup.
Thank you in advance for your help. Please let me know if you need any other information.
Thank you,
Jeni
I’m new to Postgres.. :)