Re: Replication Syatem - Mailing list pgsql-performance

From Gauri Kanekar
Subject Re: Replication Syatem
Date
Msg-id 7e4ba9550804300947q7ba316c5uc95f2b2249ffbb33@mail.gmail.com
Whole thread Raw
In response to Re: Replication Syatem  ("Pavan Deolasee" <pavan.deolasee@gmail.com>)
List pgsql-performance
We have tried fillfactor for indices and it seems to work.
Need to try fillfactor for table. May for that reason the bulk update queries don't get the advantage of HOT
:)


On Wed, Apr 30, 2008 at 9:45 PM, Pavan Deolasee <pavan.deolasee@gmail.com> wrote:
On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Pavan Deolasee" <pavan.deolasee@gmail.com> writes:
>  > That's weird. With that fillfactor, you should have a very high
>  > percentage of HOT update ratio. It could be a very special case that
>  > we might be looking at.
>
>  He's testing
>

It's "She" :-)

Oh yes. Apologies if I sounded harsh; did not mean that. I was just
completely confused why she is not seeing the HOT updates.

>  >> update table1 set delta1 = 100 where code/1000000 =999;
>
>  so all the rows being updated fall into a contiguous range of "code"
>  values.  If the table was loaded in such a way that those rows were
>  also physically contiguous, then the updates would be localized and
>  would very soon run out of freespace on those pages.
>

Yeah, that seems like the pattern. I tested with the similar layout
and a fill factor 80. The initial few bulk updates had comparatively
less HOT updates (somewhere 20-25%), But within 4-5 iterations of
updating the same set of rows, HOT updates were 90-95%. That's because
after few iterations (and because of non-HOT updates) the tuples get
scattered in various blocks, thus improving chances of HOT updates.

I guess the reason probably is that she is using fill factor for
indexes and not heap, but she hasn't yet confirmed.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com



--
Regards
Gauri

pgsql-performance by date:

Previous
From: "Pavan Deolasee"
Date:
Subject: Re: Replication Syatem
Next
From: david@lang.hm
Date:
Subject: Re: Postgres replication