DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=ORJXVEHaGkIz1XxnW4TCpYz5lMH4KghCgHiJv5iXNeH/jEeX6P8vA0V30DfQUvy9G9AKlqRoH5NWFUgtFgQdH9wGRk4FXkuGNmjIu7Iak4NENL72/Cea70tpAn74fqVEqrQsy5GH8JJbCHTGd212AFGtwRMYE20N0DVgOZdRLj8=
Message-ID: <a728f9f90703021531t63cead2blb625e5afc47e337@mail.gmail.com>
Date: Fri, 2 Mar 2007 18:31:28 -0500
From: "Alex Deucher" <alexdeucher@gmail.com>
To: "Jeff Frost" <jeff@frostconsultingllc.com>
Subject: Re: strange performance regression between 7.4 and 8.1
Cc: "Guido Neitzer" <lists@event-s.net>,
	"PostgreSQL Performance" <pgsql-performance@postgresql.org>
In-Reply-To: 
 <Pine.LNX.4.64.0703021451420.11609@discord.home.frostconsultingllc.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <a728f9f90703011244r577d56d7y422ab4bd71bb760e@mail.gmail.com>
	<a728f9f90703020716g45b2d281u9894973abad3e68a@mail.gmail.com>
	<E1HN9zd-0001tV-Gi@elasmtp-galgo.atl.sa.earthlink.net>
	<a728f9f90703020803r2ab76789sa63def90642fe5c@mail.gmail.com>
	<E1HNC2e-0005Cl-II@elasmtp-scoter.atl.sa.earthlink.net>
	<a728f9f90703021143l2da54eb1r61d4fe7609629b17@mail.gmail.com>
	<E1HNEMf-00060s-4C@elasmtp-banded.atl.sa.earthlink.net>
	<a728f9f90703021320rddfaa88wbb315ef86547aaee@mail.gmail.com>
	<2EB9B5FC-E7D5-46A7-8068-6C5D059E7781@event-s.net>
	<Pine.LNX.4.64.0703021451420.11609@discord.home.frostconsultingllc.com>

On 3/2/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> On Fri, 2 Mar 2007, Guido Neitzer wrote:
>
> > On 02.03.2007, at 14:20, Alex Deucher wrote:
> >
> >> Ah OK.  I see what you are saying;  thank you for clarifying.  Yes,
> >> the SAN is configured for maximum capacity; it has large RAID 5
> >> groups.  As I said earlier, we never intended to run a DB on the SAN,
> >> it just happened to come up, hence the configuration.
> >
> > So why not dumping the stuff ones, importing into a PG configured to use
> > local discs (Or even ONE local disc, you might have the 16GB you gave as a
> > size for the db on the local machine, right?) and testing whether the problem
> > is with PG connecting to the SAN. So you have one factor less to consider
> > after all your changes.
> >
> > Maybe it's just that something in the chain from PG to the actual HD spindles
> > kills your random access performance for getting the actual rows.
>
> I am actually starting to think that the SAN may be introducing some amount of
> latency that is enough to kill your random IO which is what all of the queries
> in question are doing - look up in index - fetch row from table.
>
> If you have the time, it would be totally worth it to test with a local disk
> and see how that affects the speed.
>
> I would think that even with RAID5, a SAN with that many spindles would be
> quite fast in raw throughput, but perhaps it's just seek latency that's
> killing you.
>
> When you run the bonnie tests again, take note of what the seeks/sec is
> compared with the old disk.  Also, you should run bonnie with the -b switch to
> see if that causes significant slowdown of the writes...maybe minor synced
> write activity to pg_xlog is bogging the entire system down.  Is the system
> spending most of its time in IO wait?
>
> Also, another item of note might be the actual on disk DB size..I wonder if it
> has changed significantly going from SQL_ASCII to UTF8.
>
> In 8.1 you can do this:
>
> SELECT  datname,
>          pg_size_pretty(pg_database_size(datname)) AS size
> FROM pg_database;
>
> In 7.4, you'll need to install the dbsize contrib module to get the same info.
>

I'm beginning the think the same thing.  I'm planning to try the tests
above next week.  I'll let you know what I find out.

Thanks!

Alex