Re: Unexpected page allocation behavior on insert-only tables

From: Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Michael Renner <michael(dot)renner(at)amd(dot)co(dot)at>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Unexpected page allocation behavior on insert-only tables
Date: 2010-05-31 03:46:40
Message-ID: 20100531124640.F2AE.52131E4D@oss.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 3. After awhile, autovacuum notices all the insert activity and kicks
> off an autoanalyze on the bid table. When committed, this forces a
> relcache flush for each other backend's relcache entry for "bid".
> In particular, the smgr targblock gets reset.
>
> 4. Now, all the backends again decide to try to insert into the last
> available block. So everybody jams into the partly-filled block 10,
> until it gets filled.

The autovacuum process runs only analyze, but does not run vacuum at 3
because the workload is insert-only. Partially filled pages are never
tracked by freespace map. We could re-run an autovacuum if we saw the
report from the autoanalyze that says the table is low-density,
but the additional vacuum might be overhead in other cases.

> The obvious thing to do about this would be to not reset targblock
> on receipt of a relcache flush event

Even if we don't reset targblock, can we solve the issue when clients
connect and disconnect for each insert? New backends only check the end
of the table, and extend it as same as this case. If we are worrying
about the worst caase, we might need to track newly added pages with
freespace map. Of course we can ignore the case because frequent
connections and disconnections should be always avoided.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2010-05-31 04:03:59 Re: Unexpected page allocation behavior on insert-only tables
Previous Message Robert Haas 2010-05-31 03:44:02 Re: Unexpected page allocation behavior on insert-only tables