Re: How to improve db performance with $7K?

Kevin Brown <kevin@sysexperts.com> writes:

> My question is: why does this (physical I/O scheduling) seem to matter
> so much?
>
> Before you flame me for asking a terribly idiotic question, let me
> provide some context.
>
> The operating system maintains a (sometimes large) buffer cache, with
> each buffer being mapped to a "physical" (which in the case of RAID is
> really a virtual) location on the disk.  When the kernel needs to
> flush the cache (e.g., during a sync(), or when it needs to free up
> some pages), it doesn't write the pages in memory address order, it
> writes them in *device* address order.  And it, too, maintains a queue
> of disk write requests.

I think you're being misled by analyzing the write case.

Consider the read case. When a user process requests a block and that read
makes its way down to the driver level, the driver can't just put it aside and
wait until it's convenient. It has to go ahead and issue the read right away.

In the 10ms or so that it takes to seek to perform that read *nothing* gets
done. If the driver receives more read or write requests it just has to sit on
them and wait. 10ms is a lifetime for a computer. In that time dozens of other
processes could have been scheduled and issued reads of their own.

If any of those requests would have lied on the intervening tracks the drive
missed a chance to execute them. Worse, it actually has to backtrack to get to
them meaning another long seek.

The same thing would happen if you had lots of processes issuing lots of small
fsynced writes all over the place. Postgres doesn't really do that though. It
sort of does with the WAL logs, but that shouldn't cause a lot of seeking.
Perhaps it would mean that having your WAL share a spindle with other parts of
the OS would have a bigger penalty on IDE drives than on SCSI drives though?

--
greg