Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 501E364FC4D for ; Tue, 25 Nov 2008 00:54:05 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 30293-02 for ; Tue, 25 Nov 2008 00:54:01 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.183]) by mail.postgresql.org (Postfix) with ESMTP id BC89464FC38 for ; Tue, 25 Nov 2008 00:54:01 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id j4so1217870wah.19 for ; Mon, 24 Nov 2008 20:54:00 -0800 (PST) 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=MRQuDMJj7r3dAJhTAhN/AWG8a/tEeRh9HIpLcJucz78=; b=J2vdItfdbYamp4NTGm5QXzekk7FDpe5PZeksoYut7+FKYWnqTsrBBuhDhL7QKej3uI 8hbRI6iZFUqpA3T7lV/dY//llL02GDusLDlT8388BPnymT/p4CjBbz0dqCciZBPamzre VUJ7RE2WWRsDpP7DeFu5s61jA37XiRwCHR2lo= 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=VsG6BNy1auqrJx7c/KRziZtE8vA0XxBDOLKwfWJTe9Oj9PrGmf04TzVuHp93pb33Hu weUULvVnMKIYeLVE03k6DbNzHKf8M2AGzIdacMUtIcfPU+ls4FFWfz+fWqyOIA9AWHs2 08wQjlkaxCOt0uar6+m/QKnuUkQzcM3pQBT64= Received: by 10.114.103.4 with SMTP id a4mr2424702wac.91.1227588840354; Mon, 24 Nov 2008 20:54:00 -0800 (PST) Received: by 10.114.137.17 with HTTP; Mon, 24 Nov 2008 20:54:00 -0800 (PST) Message-ID: Date: Mon, 24 Nov 2008 21:54:00 -0700 From: "Scott Marlowe" To: "Greg Smith" Subject: Re: Monitoring buffercache... Cc: "Brad Nicholson" , "Kevin Kempter" , pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <200811241143.57182.kevin@kevinkempterllc.com> <1227556326.18990.98.camel@bnicholson-desktop> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200811/252 X-Sequence-Number: 31983 On Mon, Nov 24, 2008 at 9:40 PM, Greg Smith wrote: > On Mon, 24 Nov 2008, Scott Marlowe wrote: > >> My guess is that the period of time for which pg_buffercache takes locks >> on the buffer map are short enough that it isn't a real big deal on a fast >> enough server. > > As the server involved gets faster, the amount of time the locks are > typically held for drops. > > As your shared_buffers allocation increases, that amount of time goes up. > > So how painful the overhead is depends on how fast your CPU is relative to > how much memory is in it. Since faster systems tend to have more RAM in > them, too, it's hard to say whether the impact will be noticable. > > Also, noting that the average case isn't impacted much isn't the concern > here. The problem is how much having all partition locks held will increase > impact worst-case latency. True. I was just looking to see how it impacted my servers. Just FYI, it's an 8 core opteron 2.1GHz with 32 Gig 667MHz DDR2 ram. It runs on a fast RAID-10 set (12 15k drives under an areca 1680, but I don't know if that matters that much here.) It can pretty easily about 400 or so active transactions and still be responsive, but noticeably slower. At anything under about 50 or so transactions it's still quite fast. It's configured to have 8Gig of the 32Gig allocated as shared buffers, and a buffercache query takes about 1 second to run. Is the shared_mem locked all that time? And is it only locked against writes?