Re: Mostly read performance

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Michael Stone <mstone+postgres(at)mathom(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Mostly read performance
Date: 2005-08-12 07:32:37
Message-ID: 871x4za9uy.fsf@stark.xeocode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


Michael Stone <mstone+postgres(at)mathom(dot)us> writes:

> > Well, that's what you'd expect. But a first time 70MB fetch on a freshly
> > rebooted system took just as long as all secondary times. (Took over a
> > minute to fetch, which is too long for my needs, at least on secondary
> > attempts).

That's not impressively fast even for the disk. You should get up to about
40Mbit/s or 5MByte/s from the disk. Add some overhead for postgres; so I would
expect a full table scan of 70MB to take more like 15-30s, not over a minute.

What is your shared_buffers setting? Perhaps you have it set way too high or
way too low?

Also, you probably should post the "explain analyze" output of the actual
query you're trying to optimize. Even if you're not looking for a better plan
having hard numbers is better than guessing.

And the best way to tell if the data is cached is having a "vmstat 1" running
in another window. Start the query and look at the bi/bo columns. If you see
bi spike upwards then it's reading from disk.

--
greg

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Steve Poe 2005-08-12 08:47:08 [SPAM?] Re: PG8 Tuning
Previous Message Petr Kavan 2005-08-12 06:53:33 How many views is ok?