From: Alex Deucher <alexdeucher@gmail.com>
To: Jeff Frost <jeff@frostconsultingllc.com>
Cc: Guido Neitzer <lists@event-s.net>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: strange performance regression between 7.4 and 8.1
Date: Fri, 2 Mar 2007 18:31:28 -0500
Message-ID: <a728f9f90703021531t63cead2blb625e5afc47e337@mail.gmail.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0703021451420.11609@discord.home.frostconsultingllc.com>
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
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: alexdeucher@gmail.com, jeff@frostconsultingllc.com, lists@event-s.net
Subject: Re: strange performance regression between 7.4 and 8.1
In-Reply-To: <a728f9f90703021531t63cead2blb625e5afc47e337@mail.gmail.com>
* 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