List:Internals« Previous MessageNext Message »
From:Steve Hardy Date:November 21 2011 10:19am
Subject:RE: Concurrent table access from InnoDB
View as plain text  
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
>
Thread
Concurrent table access from InnoDBhwatari18 Nov
  • Re: Concurrent table access from InnoDBMySQL)18 Nov
    • Re: Concurrent table access from InnoDBRick James18 Nov
      • Re: Concurrent table access from InnoDBMySQL)18 Nov
        • Re: Concurrent table access from InnoDBHiromichi Watari19 Nov
          • Re: Concurrent table access from InnoDBMark Leith21 Nov
            • Re: Concurrent table access from InnoDBStewart Smith22 Nov
              • Re: Concurrent table access from InnoDBRick James22 Nov
                • Re: Concurrent table access from InnoDBHiromichi Watari22 Nov
                  • Re: Concurrent table access from InnoDBRick James22 Nov
                    • Re: Concurrent table access from InnoDBHiromichi Watari23 Nov
RE: Concurrent table access from InnoDBSteve Hardy21 Nov