Re: PostgreSQL reads each 8k block - no larger blocks are used - even on sequential scans

On Sun, 2009-09-27 at 18:05 +0200, Gerhard Wiesinger wrote:

> So I saw, that even on sequential reads (and also on bitmap heap scan acces)
> PostgreSQL uses only 8k blocks. I think that's a major I/O bottleneck.
>
> A commercial software database vendor solved the problem by reading multiple
> continuous blocks by multiple 8k blocks up to a maximum threshold. Output per 5
> seconds on an equivalent "sequence scan":

Is systemtap counting actual I/Os or just requests to access 8192 blocks
once in OS cache? Postgres doesn't read more than one block at a time
into its buffer pool, so those numbers of requests look about right.

There is belief here that multi-block I/O was introduced prior to OS
doing this as a standard mechanism. Linux expands its read ahead window
in response to sequential scans and so this seems like something we
don't want to do in the database.

It's possible this is wrong. Is the table being scanned fairly sizable
and was it allocated contiguously? i.e. was it a large table loaded via
COPY?

I also wonder if more L2 cache effects exist.

--
Simon Riggs www.2ndQuadrant.com