Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1ToMcw-00041g-Pi for pgsql-performance@arkaria.postgresql.org; Thu, 27 Dec 2012 23:12:38 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1ToMcw-0001qJ-Ae for pgsql-performance@arkaria.postgresql.org; Thu, 27 Dec 2012 23:12:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1ToMcv-0001qE-I9 for pgsql-performance@postgresql.org; Thu, 27 Dec 2012 23:12:37 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1ToMcr-0006iM-C1 for pgsql-performance@postgresql.org; Thu, 27 Dec 2012 23:12:37 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id qBRNCVIR021870; Thu, 27 Dec 2012 18:12:31 -0500 (EST) From: Tom Lane To: Nikolas Everett cc: "pgsql-performance@postgresql.org" Subject: Re: explain analyze reports that my queries are fast but they run very slowly In-reply-to: References: <17229.1356636060@sss.pgh.pa.us> <17694.1356637368@sss.pgh.pa.us> <19936.1356644001@sss.pgh.pa.us> Comments: In-reply-to Nikolas Everett message dated "Thu, 27 Dec 2012 17:01:42 -0500" Date: Thu, 27 Dec 2012 18:12:31 -0500 Message-ID: <21869.1356649951@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Nikolas Everett writes: > On Thu, Dec 27, 2012 at 4:33 PM, Tom Lane wrote: >> Nikolas Everett writes: >>> We straced the backend during the explain and it looked like the open >>> commands were taking several seconds each. >> Kind of makes me wonder if you have a whole lot of tables ("whole lot" >> in this context probably means tens of thousands) and are storing the >> database on a filesystem that doesn't scale well to lots of files in one >> directory. If that's the explanation, the reason the 8.3 installation >> was okay was likely that it was stored on a more modern filesystem. > We have 1897 files for our largest database which really isn't a whole lot. OK... > The old servers were EXT3 over FC to a NetApp running RHEL5 PPC. The new > servers are on NFS to the same NetApp running RHEL5 Intel. Now I'm wondering about network glitches or NFS configuration problems. This is a bit outside my expertise unfortunately, but it seems clear that your performance issue is somewhere in that area. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance