[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: [PERFORM] Replication Syatem
From: "Gauri Kanekar" <meetgaurikanekar () gmail ! com>
Date: 2008-04-30 16:59:46
Message-ID: 7e4ba9550804300947q7ba316c5uc95f2b2249ffbb33 () mail ! gmail ! com
[Download RAW message or body]
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
[Attachment #3 (text/html)]
We have tried fillfactor for indices and it seems to work.<br>Need to try fillfactor \
for table. May for that reason the bulk update queries don't get the advantage of \
HOT<br>:)<br><br><br><div class="gmail_quote">On Wed, Apr 30, 2008 at 9:45 PM, Pavan \
Deolasee <<a href="mailto:pavan.deolasee@gmail.com">pavan.deolasee@gmail.com</a>> \
wrote:<br> <blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, \
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d">On Wed, \
Apr 30, 2008 at 8:16 PM, Tom Lane <<a \
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> wrote:<br>
> "Pavan Deolasee" <<a \
href="mailto:pavan.deolasee@gmail.com">pavan.deolasee@gmail.com</a>> writes:<br> \
> > That's weird. With that fillfactor, you should have a very \
high<br> > > percentage of HOT update ratio. It could be a very special \
case that<br> > > we might be looking at.<br>
><br>
> He's testing<br>
><br>
<br>
</div>It's "She" :-)<br>
<br>
Oh yes. Apologies if I sounded harsh; did not mean that. I was just<br>
completely confused why she is not seeing the HOT updates.<br>
<div class="Ih2E3d"><br>
> >> update table1 set delta1 = 100 where code/1000000 =999;<br>
><br>
> so all the rows being updated fall into a contiguous range of \
"code"<br> > values. If the table was loaded in such a way \
that those rows were<br> > also physically contiguous, then the updates \
would be localized and<br> > would very soon run out of freespace on those \
pages.<br> ><br>
<br>
</div>Yeah, that seems like the pattern. I tested with the similar layout<br>
and a fill factor 80. The initial few bulk updates had comparatively<br>
less HOT updates (somewhere 20-25%), But within 4-5 iterations of<br>
updating the same set of rows, HOT updates were 90-95%. That's because<br>
after few iterations (and because of non-HOT updates) the tuples get<br>
scattered in various blocks, thus improving chances of HOT updates.<br>
<br>
I guess the reason probably is that she is using fill factor for<br>
indexes and not heap, but she hasn't yet confirmed.<br>
<div><div></div><div class="Wj3C7c"><br>
Thanks,<br>
Pavan<br>
<br>
--<br>
Pavan Deolasee<br>
EnterpriseDB <a href="http://www.enterprisedb.com" \
target="_blank">http://www.enterprisedb.com</a><br> \
</div></div></blockquote></div><br><br clear="all"><br>-- <br>Regards<br>Gauri
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic