Re: [PERFORM] Postgres log(pg_logs) have lots of message

On Wednesday, April 10, 2013, Nik Tek wrote:

> Hi Bambi,
>
> Thank you the prompt reply.
>
> This table is very volatile, lot of inserts/updates happen on this
> tables(atleast 20~30 inserts/min).
>

That number of inserts per minute is not all that many. I suspect that you
have sessions which are holding open transactions (and thus locks) for much
longer than necessary, and it is this idling on the locks, not the active
insertions, that is causing the current problem.

If this is true, you should try to find the idle-in-transaction connections
and fix them, because even if they didn't cause this particular problem,
they will cause other ones.

> When auto vacuum tries to run on this table, I get this warning.
>

>> LOG: automatic vacuum of table "DB1.nic.pvxt": could not (re)acquire
exclusive lock for truncate scan

You have at least 8 MB of empty space at the end of the table, but to
remove it it needs to acquire a lock that it cannot get and so it gives up.
Unfortunately it now gives up on the following autoanalyze as well. In
9.2.2 and before, it would also give up on reclaiming the free space, but
would likely still do the autoanalyze, which is probably why you didn't see
it before.

> Is there a way, I force it to happen, because the table/indexes statistics
> are becoming stale very quickly.
>

This is something introduced in 9.2.3, and will probably be fixed whenever
9.2.5 comes out.

In the mean time, a manual ANALYZE (But not a VACUUM ANALYZE, because would
fail the same was autvac does) would fix the stats, but it would have to be
repeated often as they would just get stale again.

You could try a VACUUM FULL or CLUSTER if you can tolerate the lock it
would hold on the table while it operates. The reason that might solve the
problem for you is that it would clear out the empty space, and therefore
future autovac won't see that empty space and try to truncate it.
Depending on how your table is used, either more empty space could
accumulate at the end of the table causing the problem to recur, or maybe
it would fix the problem for good.

Cheers,

Jeff

>