Re: Postgres log(pg_logs) have lots of message - Mailing list pgsql-performance

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.

 

>> 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.

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