If your workload is I/O bound, then running multiple threads isn't really useful anyway.
If you are I/O bound and you're retrieving lots of data then the current InnoDB
implementation is rather bad at requesting multiple pages at the same time; it basically
just processes only one page at a time, and then requests the next. This is not good if
you're running a large multi-spindle I/O subsystem since one query will only ever be
accessing one physical disk at a time, limiting you to about 200 IOPS on magnetic disks
(for one query).
I wrote a patch about a year ago that fixes this by prefetching data up to 1024 pages
ahead in a smarter way than the existing data prefetch mechanism.
The patch is still sitting in MySQL's bug tracker, although it still needs polishing (and
It can speed up your mysqldumps (which are single threaded) by factors of 5 or even more
if you have lots of disks.
> On 19 Nov 2011, at 02:23, Hiromichi Watari wrote:
> > Hi Shawn and Rick,
> > Thank you for your response.
> > My question was whether a client thread can access more than one
> > table at a time in the course of join execution (parallel access of
> > multiple tables) to speed up the query if an extra slave thread is
> > available.
> > I do not know if there is anything in InnoDB (or storage engine in
> > general) which serializes access to tables preventing parallel access.
> > Hiromichi