Hi!
Le 2014-04-02 à 10:26, Eliot Gable a écrit :
> 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,
Irun vacuum analyze and it takes about 90 seconds to complete. If I continue to run vacuum analyze on it, it continues
totake 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
underthe 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
afterwriting to it long enough, the PGSQL autovacuum process would kick in and force a vacuum saying something about
preventingwrap around. I don't understand why it would do this if it is append-only and we are using 64-bit sequences
asIDs without OIDs turned on. What would be wrapping around without a vacuum? We tried to mitigate this by manually
runningvacuum programmatically, but then we end up using all the disk IO just running vacuum all the time, because it
isconstantly 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?
You don't mention the version of PostgreSQL, but let me link you to this page in the manual:
23.1.5. Preventing Transaction ID Wraparound Failures
"""But since transaction IDs have limited size (32 bits) a cluster that runs for a long time (more than 4 billion
transactions)would suffer transaction ID wraparound"""
http://www.postgresql.org/docs/9.3/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
This is what the message in the log said.
Because the table is append only, old rows must receive the transaction ID which is guaranteed to be lower than all
othertransactions: FrozenXID.
If you run two vacuums back to back, presumably the table is mostly in RAM and returns more quickly the next time
around,hence the very different runtimes.
You say "if nothing had been done to the table": do you have a single very large append-only table in your cluster? If
so,it might be better to split the table in multiple partitions, and then when a partition is untouched, the vacuum
daemonshould not touch the table.
Please provide your exact PostgreSQL version, RAM, disk and other details, relevant postgresql.conf parameters so that
wemay help more.
Cheers!
François Beausoleil
Seevibes