Re: Postgres 7.3.1 poor insert/update/search performance

On Mon, 2 Feb 2003, Neil Conway wrote:

> > As I remember, DIRECT doesn't return until the data hits the disk
> > (because there is no OS cache), so if you want to write a page so you
> > can reused the buffer, DIRECT would be quite slow.
> ...
> One possible problem would be the fact that it might mean that writing
> out dirty pages would become part of some key code paths in PostgreSQL
> (rather than assuming that the OS can write out dirty pages in the
> background, as it chooses to). But there are lots of ways to work around
> this, notably by using a daemon to periodically write out some of the
> pages in the background.

If you're doing blocking direct I/O, you really have to have and use
what I guess I'd call "scatter-scatter I/O": you need to chose a large
number of blocks scattered over various positions in all your open
files, and be able to request a write for all of them at once.

If you write one by one, with each write going to disk before your
request returns, you're going to be forcing the physical order of the
writes with no knowledge of where the blocks physically reside on the
disk, and you stand a snowball's chance in you-know-where of getting
a write ordering that will maximize your disk throughput.

This is why systems that use direct I/O, for the most part, use a raw
partition and their own "filesystem" as well; you need to know the
physical layout of the blocks to create efficient write strategies.

(MS SQL Server on Windows NT is a notable exception to this. They do,
however, make you pre-create the data file in advance, and they suggest
doing it on an empty partition, which at the very least would get you
long stretches of the file in contiguous order. They may also be using
tricks to make sure the file gets created in contiguous order, or they
may be able to get information from the OS about the physical block
numbers corresponding to logical block numbers in the file.)

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC