Re: postgres performance: comparing 2 data centers

"Michael Nonemacher" <Michael_Nonemacher(at)messageone(dot)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