Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 2700E2E0059 for ; Wed, 30 Apr 2008 13:15:12 -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 04149-04 for ; Wed, 30 Apr 2008 13:15:06 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by developer.postgresql.org (Postfix) with ESMTP id 3A6DF2E0037 for ; Wed, 30 Apr 2008 13:15:07 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so1331952ugf.49 for ; Wed, 30 Apr 2008 09:15:06 -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:content-transfer-encoding:content-disposition:references; bh=Gu07o+LTFgGpnvgFfIuQTgEEKCrxu2LKlPrSApdAmaQ=; b=fpBv+KCjP7x+6L2skKBeNaEXQjAczOT35Cd3JRMHk/w3av/ktnAIMQIDh2WIWwiPG4N3GUvVWl7GaIEn+MFQpzIdli+0VUQkfbz8vu9ZgPskpnLFc4HSmGssNZ/YgKRlv9yNvTzbSlEYlM3T7tY6XSVFVbHf1IyO2w0a20thtRY= 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:content-transfer-encoding:content-disposition:references; b=rPv1BY69BPAcdtZ8g4GutdSeB30mOcPexFQsFXpmZGD7fzfrJ9o3rrOvGU/Ku58Wdg5/5VSAQYa8RedQZj/bND9a7KaSme+x/9oJKhynyEDnRTzd8bEhNrEB1RON18OTYbFh2TqFl9DmuOLAoiGznsqFVHyJIu6xIVpAEMgMjDc= Received: by 10.150.219.3 with SMTP id r3mr1285764ybg.65.1209572105453; Wed, 30 Apr 2008 09:15:05 -0700 (PDT) Received: by 10.151.84.7 with HTTP; Wed, 30 Apr 2008 09:15:05 -0700 (PDT) Message-ID: <2e78013d0804300915u32b9a190k86496c02dba7babd@mail.gmail.com> Date: Wed, 30 Apr 2008 21:45:05 +0530 From: "Pavan Deolasee" To: "Tom Lane" Subject: Re: Replication Syatem Cc: "Gauri Kanekar" , "Shane Ambler" , "Alvaro Herrera" , "Greg Smith" , pgsql-performance@postgresql.org In-Reply-To: <18684.1209566788@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <7e4ba9550804280638ie81dc23w472785c83cd38fcf@mail.gmail.com> <20080429130204.GA5652@alvh.no-ip.org> <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> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.232 tagged_above=0 required=5 tests=AWL=-1.667, FS_REPLICA=1.179, SARE_SPEC_REPLICA=0.72 X-Spam-Level: X-Archive-Number: 200804/470 X-Sequence-Number: 29963 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