From: | Eliot Gable <egable+pgsql-general(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Table Vacuum Taking a Long Time |
Date: | 2014-04-02 14:26:38 |
Message-ID: | CAD-6L_UbSoexZOARs29jYhmjWAt_eBvpwFG-L4RJwECTa4uD_w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have a table which is about 12 GB in size. It has had a vacuum full
analyze run on it, and then immediately after, I run vacuum analyze and it
takes about 90 seconds to complete. If I continue to run vacuum analyze on
it, it continues to take about 90 seconds each time. This system has a
single 7200 RPM drive in it, so it's not a very fast drive. I was under the
impression that if nothing had been done to the table since it was last
vacuumed, that it would return immediately. Further, this is an append-only
table, so why should it need to be vacuumed at all? We ran into cases where
after writing to it long enough, the PGSQL autovacuum process would kick in
and force a vacuum saying something about preventing wrap around. I don't
understand why it would do this if it is append-only and we are using
64-bit sequences as IDs without OIDs turned on. What would be wrapping
around without a vacuum? We tried to mitigate this by manually running
vacuum programmatically, but then we end up using all the disk IO just
running vacuum all the time, because it is constantly running through the
entire table even though very little (if anything) has been done to it
since the last vacuum.
Is this described behavior expected? If so, why?
If it is not expected, what should I be looking for which might explain why
it is taking so long?
Thanks.
--
Eliot Gable
"We do not inherit the Earth from our ancestors: we borrow it from our
children." ~David Brower
"I decided the words were too conservative for me. We're not borrowing from
our children, we're stealing from them--and it's not even considered to be
a crime." ~David Brower
"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live;
not live to eat.) ~Marcus Tullius Cicero
From | Date | Subject | |
---|---|---|---|
Next Message | François Beausoleil | 2014-04-02 14:44:49 | Re: Table Vacuum Taking a Long Time |
Previous Message | Shaun Thomas | 2014-04-02 14:13:10 | Re: Insert zero to auto increment serial column |