Re: Read/Write block sizes

> Unfortunately I'm really afraid that this conversation is about trees
> when the forest is the problem. PostgreSQL doesn't even have an async
> reader, which is the sort of thing that could double or triple its
> performance. You're talking about block sizes and such, but the kinds
> of improvements you can get there are in the tens of percents at most.

Not 100% sure, but I'm fairly cirtain we were seeing significant performance
degradation by too much _scheduled_ I/O activity

ie: too much work being submitted to the kernel, due to excessive
parallelism already!!

The classic example of this is a seqscan being interleved by a index scan,
and the disks end up doing nothing but seek activity

Out of all the stuff talked about on this thread so far, only tweaking the
block size (and the madvise() stuff) makes any real-world sense, as its the
only thing talked about that increases the _work_per_seek_.

As for the async IO, sure you might think 'oh async IO would be so cool!!'
and I did, once, too. But then I sat down and _thought_ about it, and
decided well, no, actually, theres _very_ few areas it could actually help,
and in most cases it just make it easier to drive your box into lseek()
induced IO collapse.

Dont forget that already in postgres, you have a process per connection, and
all the processes take care of their own I/O.

Somebody mentioned having threaded backends too, but the only benefit would
be reduced memory footprint (a backend consumes 1-2MB of RAM, which is
almost enough to be a concern for largish systems with a lot of backends)
but personally I _know_ the complixities introduced through threading are
usually not worth it.

IMVVHO (naive experience) what is needed is a complete architecture change
(probably infeasible and only useful as a thought experiment), where:

* a network I/O process deals with client connections
* a limited pool of worker processes deal with statements (perhaps related
to number of spindles somehow)

so when a client issues a statement, the net-IO process simply forwards the
connection state to a worker process and says 'deal with this'.
(Clearly the state object needs to contain all user and transaction state
the connection is involved in).

- Guy Thornley