List:Cluster« Previous MessageNext Message »
From:Mikael Ronström Date:August 4 2004 6:01pm
Subject:Re: performance question
View as plain text  
Hi Luke,

2004-08-04 kl. 19.49 skrev Crouch, Luke H.:

> is there a list or chart or something that shows which kinds of 
> scans/evaluations/processes occur on the NDB side, and which ones 
> occur on the mysqld ?
>


At the moment it is very simple. All index support is on the NDB side 
so if an index gets used it will filter out records on the NDB side. 
All other
evaluations takes place in the MySQL server.

When filters on the NDB side is used we will have a more complex 
scenario but technically all evaluations on a table can be moved to the 
NDB side
except for UDF's. Join processing will remain on the MySQL server.

Rgrds Mikael

> thanks,
> -L
>
>> -----Original Message-----
>> From: Mikael Ronström [mailto:mikael@stripped]
>> Sent: Wednesday, August 04, 2004 12:46 PM
>> To: Crouch, Luke H.
>> Cc: Paul G. Weiss; cluster@stripped
>> Subject: Re: performance question
>>
>>
>> Hi Luke,
>>
>> 2004-08-04 kl. 18.47 skrev Crouch, Luke H.:
>>
>>> I thought the tables in the cluster are of type NDB, so they aren't
>>> InnoDB or MyISAM...the table just happened to be in MyISAM on the
>>> regular mysqld.
>>>
>>> do you know how I can remove the record count on the regular mysqld
>>> MyISAM tables? then I could see how long it actually takes
>> to do the
>>> full count on MyISAM and it may be similar in length of time to the
>>> count on the NDB tables.
>>>
>>
>> This feature of MyISAM is not optional as far as I know, but Paul's
>> advice seems a good one.
>>
>>> is there not a way to have the cluster keep a count record
>> as well? in
>>> the same kind of way as MyISAM?
>>>
>>
>> Technically yes, but not implemented yet.
>>
>> What you are experiencing is that MySQL Cluster is suffering when not
>> using indexes. The problem is that the count is performed in
>> the MySQL
>> Server
>> and so records are shipped to the MySQL server over the network which
>> is costly even when using main memory. So you are getting 130.000
>> records a second
>> over the wire which is pretty good speed from networking
>> point of view.
>> There are work log items to push most of these evaluations to
>> the ndbd
>> servers which
>> means all scan activity and evaluations can be performed without
>> network transport. At the moment it is crucial to have indexes on
>> queries with proper
>> response times, otherwise the network traffic takes the whole
>> cost. So
>> even compared to InnoDB, NDB will suffer due to the network traffic.
>> Moving things
>> internally in memory is 10 times faster than using TCP/IP transports.
>> This is why we also support other network interconnects such
>> as shared
>> memory and
>> SCI.
>>
>> Rgrds Mikael
>>
>>> thanks again,
>>> -L
>>>
>>>> -----Original Message-----
>>>> From: Paul G. Weiss [mailto:pgweiss@stripped]
>>>> Sent: Wednesday, August 04, 2004 11:44 AM
>>>> To: Crouch, Luke H.; cluster@stripped
>>>> Subject: Re: performance question
>>>>
>>>>
>>>> Try it with InnoDB tables for a fairer comparison.  I
>> believe MyISAM
>>>> tables keep a record count as part of the data structure, so
>>>> it doesn't
>>>> really "count the records" -- it just knows.
>>>>
>>>>
>>>> On Wed, 4 Aug 2004 11:37:10 -0500, Crouch, Luke H.
>>>> <Luke.Crouch@stripped> wrote:
>>>>
>>>>> okay, thanks to all the help from you awesome mysql guys
>>>> (Vinay, Mikael,
>>>>> Pekka, Peter), we now have our table loaded into
>>>> cluster...a full 4
>>>>> million records, too!
>>>>> show table status:
>>>>> name: gmargin
>>>>> type: MyISAM
>>>>> row_format: Fixed
>>>>> rows: 4360571
>>>>> avg_row_length: 100
>>>>> data_length: 436057100
>>>>> max_data_length: 429496729599
>>>>> index_length: 140764160
>>>>> data_free: 0
>>>>> but, when we took it over to ndb, the performance seems to
>>>> sag a TON!
>>>>> here's the comparison of counting the records...
>>>>> on regular mysql server:
>>>>> mysql> select count(*) from gmargin;
>>>>> +----------+
>>>>> | count(*) |
>>>>> +----------+
>>>>> |  4360571 |
>>>>> +----------+
>>>>> 1 row in set (0.00 sec)
>>>>> on cluster mysql server:
>>>>> mysql> select count(*) from gmargin;
>>>>> +----------+
>>>>> | count(*) |
>>>>> +----------+
>>>>> |  4355766 |
>>>>> +----------+
>>>>> 1 row in set (30.89 sec)
>>>>> why does it take more than 30x as long to count up the
>>>> records on the
>>>>> cluster?! it's all in main memory, right? I would think
>>>> it'd be just as
>>>>> fast!
>>>>> thanks,
>>>>> -L
>>>>>
>>>>> Luke Crouch
>>>>> 918-461-5326
>>>>> luke.crouch@stripped
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>> Mikael Ronström, Senior Software Architect
>> MySQL AB, www.mysql.com
>>
>> Clustering:
>> http://www.infoworld.com/article/04/04/14/HNmysqlcluster_1.html
>>
>> http://www.eweek.com/article2/0,1759,1567546,00.asp
>>
>>
>>
>
> -- 
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:    
> http://lists.mysql.com/cluster?unsub=1
>
>
>
Mikael Ronström, Senior Software Architect
MySQL AB, www.mysql.com

Clustering:
http://www.infoworld.com/article/04/04/14/HNmysqlcluster_1.html

http://www.eweek.com/article2/0,1759,1567546,00.asp


Thread
performance questionLuke H. Crouch4 Aug
  • Re: performance questionPaul G. Weiss4 Aug
RE: performance questionLuke H. Crouch4 Aug
  • Re: performance questionPaul Weiss4 Aug
  • Re: performance questionMikael Ronström4 Aug
RE: performance questionLuke H. Crouch4 Aug
RE: performance questionLuke H. Crouch4 Aug
  • Re: performance questionMikael Ronström4 Aug