List:Internals« Previous MessageNext Message »
From:Hui Lin Date:November 25 2009 12:14pm
Subject:Re: How MySQL exploit performance of a quad core processor?
View as plain text  
thanks for your information.

I have seen the article about oracle's parallel execution you
supplied. oracle parses SELECT into different operations(e.g scan,
sort),  and divides one operation into small jobs with multiple
parallel execution servers to parallel execute one operation.

some doubts:
1.about meaning. It is doubtless that IO is more important than
processor. Then is it worth to optimizing MySQL in multi-core
computer? which steps of one SELECT can benefit from multi-core?

2. regarding conditions. The article says, Parallel execution
benefits four operation - Queries, Creation of large indexes, Bulk
inserts, updates, and deletes, Aggregations and copying. But it looks
like parallel execution just suit for SMP and cluster since they have
independent disk and memory, owning sufficient I/O bandwidth. In one
computer with multi-core, different cores must share memory. If you
split one operation (such as scan, sort) into multiple small jobs, and
different jobs need read index or data in memory, do they access
memory serially?

3.If I want to achieve parallel query to benefit my quad core
computer, what is the main problem I will meet according to original
MySQL architecture? I have seen this
thread(http://lists.mysql.com/internals/37347), it points three
problems - Malloc in InnoDB, pre mature locking of Query Cache,
key_buffer locking. and "The problems are addressed to various degrees
in the "Google patch", the "Percona patch" (derived from Google), and
5.4." ? MySQL 5.4 has achieved parallel query? I want to know what
they have done for taking advantage of multi-core computer?



On Wed, Nov 25, 2009 at 11:57 AM, Venu Kalyan <mydb08@stripped> wrote:
> Looks like you are looking for "Paralle Query Execution" in MySQL..
> which is not yet supported from engine/handler/optimizer yet
>
> Worklog:
> http://forge.mysql.com/worklog/task.php?id=2006
>
> There are bits and pieces are done in individual storage engines to go
> parallel; which seems to scale well on multi-cores. But no real
> parallel query execution support (that of Oracle) where individual
> query can be split into multiple smaller jobs and run in parallel
> (handler and query engine should co-ordinate) and co-ordinate the
> final results before processing it...
>
> There will not be any major benefit for normal queries; but can lead
> to big boost on sub-queries or with queries which deals with multiple
> tables..
>
> In a side note, how oracle implements this:
> http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c20paral.htm
>
> On Tue, Nov 24, 2009 at 7:36 PM, Hui Lin <liova77@stripped> wrote:
>> hi, all.
>>
>> I want to modify MySQL source for taking advantage of the potential
>> performance of my quad core computer.
>> what MySQL has done to exploit performance of multi-core computer?
>> In MySQL, is it possible to excute one SELECT(INSERT, UPDATE or
>> DELETE)with multiple threads?
>> If not, what is main problem?
>> And where can I get information on optimizing MySQL for multiple
>> cores? My Google returned surprisingly little in this vein.
>> or IO is more important for databases than processor, nobody concern processor?
>>
>>
>> Thanks a lot.
>> -Hui Lin
>
Thread
How MySQL exploit performance of a quad core processor?Hui Lin25 Nov
  • Re: How MySQL exploit performance of a quad core processor?Venu Kalyan25 Nov
    • Re: How MySQL exploit performance of a quad core processor?Hui Lin25 Nov
      • Re: How MySQL exploit performance of a quad core processor?Timour Katchaounov25 Nov
        • Re: How MySQL exploit performance of a quad core processor?Hui Lin25 Nov
          • RE: How MySQL exploit performance of a quad core processor?Rick James25 Nov
          • Re: How MySQL exploit performance of a quad core processor?Timour Katchaounov30 Nov