RE: Concurrent table access from InnoDB

Hi Hiromichi,

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 fixing?): http://bugs.mysql.com/bug.php?id=60087

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
>

-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    http://lists.mysql.com/[email protected]