Обсуждение: Postgres log(pg_logs) have lots of message

Поиск
Список
Период
Сортировка

Postgres log(pg_logs) have lots of message

От
Nik Tek
Дата:
Hi,

Could some please explain what these warnings mean in postgres.
I see these messages a lot when automatic vacuum runs.

      1 tm:2013-04-10 11:39:20.074 UTC db: pid:13766 LOG:  automatic vacuum of table "DB1.nic.pvxt": could not (re)acquire exclusive lock for truncate scan
      1 tm:2013-04-10 11:40:22.849 UTC db: pid:14286 LOG:  automatic vacuum of table "DB1.nic.pvxt": could not (re)acquire exclusive lock for truncate scan
      1 tm:2013-04-10 11:41:17.500 UTC db: pid:14491 LOG:  automatic vacuum of table "DB1.nic.pvxt": could not (re)acquire exclusive lock for truncate scan

Thank you
Nik

Re: Postgres log(pg_logs) have lots of message

От
Bambi Bellows
Дата:

That’s just that some other process has some DML going on in the table that is supposed to be truncated.  No lock, no truncate.

HTH,

Bambi.

 

From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Nik Tek
Sent: Wednesday, April 10, 2013 4:58 PM
To: pgsql-performance@postgresql.org; pgsql-admin@postgresql.org
Subject: [ADMIN] Postgres log(pg_logs) have lots of message

 

Hi,

 

Could some please explain what these warnings mean in postgres.

I see these messages a lot when automatic vacuum runs.

 

      1 tm:2013-04-10 11:39:20.074 UTC db: pid:13766 LOG:  automatic vacuum of table "DB1.nic.pvxt": could not (re)acquire exclusive lock for truncate scan

      1 tm:2013-04-10 11:40:22.849 UTC db: pid:14286 LOG:  automatic vacuum of table "DB1.nic.pvxt": could not (re)acquire exclusive lock for truncate scan

      1 tm:2013-04-10 11:41:17.500 UTC db: pid:14491 LOG:  automatic vacuum of table "DB1.nic.pvxt": could not (re)acquire exclusive lock for truncate scan

 

Thank you

Nik

This email and any files included with it may contain privileged,
proprietary and/or confidential information that is for the sole use
of the intended recipient(s).  Any disclosure, copying, distribution,
posting, or use of the information contained in or attached to this
email is prohibited unless permitted by the sender.  If you have
received this email in error, please immediately notify the sender
via return email, telephone, or fax and destroy this original transmission
and its included files without reading or saving it in any manner.
Thank you.

Re: Postgres log(pg_logs) have lots of message

От
Nik Tek
Дата:
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).
When auto vacuum tries to run on this table, I get this warning.

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

Thank you
Nik

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

От
Jeff Janes
Дата:
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