> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend