Re: Hardware/OS recommendations for large databases (

Alan,

On 11/19/05 8:43 PM, "Alan Stange" <stange(at)rentec(dot)com> wrote:

> Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
> sdd 343.73 175035.73 277.55 5251072 8326
>
> while doing a select count(1) on the same large table as before.
> Subsequent iostat output all showed that this data rate was being
> maintained. The system is otherwise mostly idle during this measurement.

Yes - interesting. Note the other result using XFS that I posted earlier
where I got 240+MB/s. XFS has more aggressive readahead, which is why I
used it.

> Can you explain again why you think there's an IO ceiling of 120MB/s
> because I really don't understand?

OK - slower this time:

We've seen between 110MB/s and 120MB/s on a wide variety of fast CPU
machines with fast I/O subsystems that can sustain 250MB/s+ using dd, but
which all are capped at 120MB/s when doing sequential scans with different
versions of Postgres.

Understand my point: It doesn't matter that there is idle or iowait on the
CPU, the postgres executor is not able to drive the I/O rate for two
reasons: there is a lot of CPU used for the scan (the 40% you reported) and
a lack of asynchrony (the iowait time). That means that by speeding up the
CPU you only reduce the first part, but you don't fix the second and v.v.

With more aggressive readahead, the second problem (the I/O asynchrony) is
handled better by the Linux kernel and filesystem. That's what we're seeing
with XFS.

- Luke