Re: Concurrent table access from InnoDB

Hi Hiromichi,

Unfortunately this is not the way that MySQL/InnoDB works at the  
moment. Each connection within MySQL ties to a single thread. MySQL  
interacts with the storage engines in that single thread, and thus  
does talk to the storage engines in a serial nature. The access  
patterns are chosen by the optimizer, and then the actual table  
interactions are stepped through based on the JOIN order chosen, one  
by one.

This doesn't necessarily relate to the limitation of partitioned  
tables not having a multi-threaded read of partitions under the  
covers, as at the handler / SQL layer, MySQL is just waiting for the  
storage engine to return rows to it at the current step of JOIN  
execution - under the covers the storage engine could choose to access  
the partitions of a single table concurrently if it wanted to (we just  
don't have that in MySQL at the moment).

Cheers,

Mark

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
>
>
> ________________________________
> From: Shawn Green (MySQL) <[email protected]>
> To: Rick James <[email protected]>
> Cc: [email protected]
> Sent: Friday, November 18, 2011 5:16 PM
> Subject: Re: Concurrent table access from InnoDB
>
> On 11/18/2011 16:29, Rick James wrote:
>> Is this what keeps there from being parallelism in PARTITIONed  
>> tables?
>>
>> ...
>
> No. Those partitions are accessed internally. Multiple threads can  
> be spawned in the context of resolving a single query. Any lack of  
> parallelism in resolving PARTITIONed table queries is simply a  
> matter of design time and effort.
>
> Now, in the context of accessing internal resources any query that  
> requires a JOIN will need to touch at least two tables under the  
> same user context.  Perhaps I misunderstood the original question?
>
>>
>> Hi,
>> Is it possible for a client connection thread to access 2 tables  
>> concurrently from InnoDB, say using a slave thread ?  Or is access  
>> limited to one table at a time per client connection thread ?
>
> Perhaps he was asking about the optimizer processing a command that  
> needed to touch two or more tables?  If that's the purpose of the  
> question then of course more than one table can be accessed in the  
> context of a single connection. This happens very frequently.  I  
> cannot say with absolute certainty if those tables are processed in  
> parallel but I would be concerned if they were not.
>
> I was reading his question more like this: Can the same connection  
> be used to open multiple queries where each query is pointing to a  
> separate table? In that context, each connection can only have one  
> active command.
>
> -- Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
>
> -- MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/[email protected]
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/[email protected]
>

Best regards

Mark

-- 
Mark Leith
Development Manager
MySQL Enterprise Tools @ Oracle Corp.


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