Re: getting the most of out multi-core systems for repeated complex SELECT statements

On Fri, 4 Feb 2011, Chris Browne wrote:

> 2. The query needs to NOT be I/O-bound. If it's I/O bound, then your
> system is waiting for the data to come off disk, rather than to do
> processing of that data.

yes and no on this one.

it is very possible to have a situation where the process generating the
I/O is waiting for the data to come off disk, but if there are still idle
resources in the disk subsystem.

it may be that the best way to address this is to have the process
generating the I/O send off more requests, but that sometimes is
significantly more complicated than splitting the work between two
processes and letting them each generate I/O requests

with rotating disks, ideally you want to have at least two requests
outstanding, one that the disk is working on now, and one for it to start
on as soon as it finishes the one that it's on (so that the disk doesn't
sit idle while the process decides what the next read should be). In
practice you tend to want to have even more outstanding from the
application so that they can be optimized (combined, reordered, etc) by
the lower layers.

if you end up with a largish raid array (say 16 disks), this can translate
into a lot of outstanding requests that you want to have active to fully
untilize the array, but having the same number of requests outstanding
with a single disk would be counterproductive as the disk would not be
able to see all the outstanding requests and therefor would not be able to
optimize them as effectivly.

David Lang