Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 2E3BB2E0041 for ; Wed, 30 Apr 2008 13:47:55 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 12109-08 for ; Wed, 30 Apr 2008 13:47:50 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from el-out-1112.google.com (el-out-1112.google.com [209.85.162.180]) by developer.postgresql.org (Postfix) with ESMTP id 1203B2E0048 for ; Wed, 30 Apr 2008 13:47:49 -0300 (ADT) Received: by el-out-1112.google.com with SMTP id j27so266597elf.23 for ; Wed, 30 Apr 2008 09:47:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=qWzy5Iq3rPBNtNztcYAHXtdV7+YFCETopnk00fJryNI=; b=xIBtfn1Fopz7sP+HZb1RgUI0XZmujEwUsFNgRrgl4SSRYMwwfq9U+I1mz95c1uHSn+TbbUa7K5i3mMm5OsRMabDaV7aVwGWqJEoltqgu1wnsNCBqBlWcNT/b0Za8gAEEREa3I30wvparsOZZ3PCoZWsCv+sbMQW9NUjg9egTIuc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=b1DsSkkR2DGDoQWEBWKYQrpI/3P01rySSFtI31OTE2+Dn8ItzYKeiticCs3yilKEoUc5G3NhcQIfsj6Q9oMjJq8MZQLno0TYM6972IqWQEeRSzDu5UNst/n6vnawPVSGXpTXXUCPu85FeazKzG3MWWVRjGB152//mvbP8PL+6MU= Received: by 10.115.73.20 with SMTP id a20mr1002345wal.142.1209574066863; Wed, 30 Apr 2008 09:47:46 -0700 (PDT) Received: by 10.114.240.17 with HTTP; Wed, 30 Apr 2008 09:47:46 -0700 (PDT) Message-ID: <7e4ba9550804300947q7ba316c5uc95f2b2249ffbb33@mail.gmail.com> Date: Wed, 30 Apr 2008 22:17:46 +0530 From: "Gauri Kanekar" To: "Pavan Deolasee" Subject: Re: Replication Syatem Cc: "Tom Lane" , "Shane Ambler" , "Alvaro Herrera" , "Greg Smith" , pgsql-performance@postgresql.org In-Reply-To: <2e78013d0804300915u32b9a190k86496c02dba7babd@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_8766_16547135.1209574066884" References: <7e4ba9550804280638ie81dc23w472785c83cd38fcf@mail.gmail.com> <4817514A.3060704@Sheeky.Biz> <7e4ba9550804292229x82e74e5y355349866c72245c@mail.gmail.com> <2e78013d0804292237l4c74a166u4ab082901aa0804f@mail.gmail.com> <7e4ba9550804292239n668b1f01v59ad7ac6ca9e092b@mail.gmail.com> <2e78013d0804292343hd9d1c0lf93df68e559f9af3@mail.gmail.com> <7e4ba9550804292346r46aa55e7g6cf1f731b6be7d40@mail.gmail.com> <2e78013d0804300025j10567a82x5845ab8bc3d4f2a2@mail.gmail.com> <18684.1209566788@sss.pgh.pa.us> <2e78013d0804300915u32b9a190k86496c02dba7babd@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.9 tagged_above=0 required=5 tests=FS_REPLICA=1.179, HTML_MESSAGE=0.001, SARE_SPEC_REPLICA=0.72 X-Spam-Level: * X-Archive-Number: 200804/471 X-Sequence-Number: 29964 ------=_Part_8766_16547135.1209574066884 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 wrote: > On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane wrote: > > "Pavan Deolasee" 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 ------=_Part_8766_16547135.1209574066884 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_8766_16547135.1209574066884--