X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 75DBCD1B459 for ; Fri, 4 Jun 2004 13:07:29 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 89520-01 for ; Fri, 4 Jun 2004 16:07:27 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 6E1DFD1B273 for ; Fri, 4 Jun 2004 13:07:26 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i54G7PS6028867; Fri, 4 Jun 2004 12:07:25 -0400 (EDT) To: "Michael Nonemacher" Cc: pgsql-performance@postgresql.org Subject: Re: postgres performance: comparing 2 data centers In-reply-to: References: Comments: In-reply-to "Michael Nonemacher" message dated "Fri, 04 Jun 2004 10:42:59 -0500" Date: Fri, 04 Jun 2004 12:07:25 -0400 Message-ID: <28866.1086365245@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/37 X-Sequence-Number: 7095 "Michael Nonemacher" writes: > I have two instances of a production application that uses Postgres 7.2, > deployed in two different data centers for about the last 6 months. The > sizes, schemas, configurations, hardware, and access patterns of the two > databases are nearly identical, but one consistently takes at least 5x > longer than the other for some common operations. Does VACUUM VERBOSE show comparable physical sizes (in pages) for the key tables in both databases? Maybe the slow one has lots of dead space in the tables (not indexes). It would be useful to look at EXPLAIN ANALYZE output of both databases for some of those common ops, too. It could be that you're getting different plans in the two cases for some reason. > We 'vacuum analyze' nightly, and we recently rebuilt the indexes on the > slow database (using reindex table). This cut the number of index pages > dramatically: from ~1800 to ~50, but didn't noticeably change the time > or CPU utilization for the common operations described above. That's pretty suspicious. If it's not dead space or plan choice, the only other thing I can think of is physical tuple ordering. You might try CLUSTERing on the most-heavily-used index of each table. regards, tom lane