pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Nonemacher <Michael_Nonemacher@messageone.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: postgres performance: comparing 2 data centers
Date: Fri, 04 Jun 2004 12:07:25 -0400
Message-ID: <28866.1086365245@sss.pgh.pa.us> (raw)
In-Reply-To: <E3A41572DB871B42AB6939873D95E8CA038748@auscorpex-1.austin.messageone.com>
References: <E3A41572DB871B42AB6939873D95E8CA038748@auscorpex-1.austin.messageone.com>

"Michael Nonemacher" <Michael_Nonemacher@messageone.com> 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



view thread (9+ messages)  latest in thread

Message-ID: <28866.1086365245@sss.pgh.pa.us>
Permalink:  ../28866.1086365245@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/28866.1086365245@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, Michael_Nonemacher@messageone.com
  Subject: Re: postgres performance: comparing 2 data centers
  In-Reply-To: <28866.1086365245@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox