Re: Response time in Mysql cluster - Luciano Bertini - com.mysql.lists.cluster

15 messages in com.mysql.lists.clusterRe: Response time in Mysql cluster

From	Sent On	Attachments
Luciano Bertini	19 Apr 2008 10:27
John David Duncan	19 Apr 2008 16:41
Luciano Bertini	20 Apr 2008 08:34
Jonathan Miller	21 Apr 2008 05:33
Jonathan Miller	21 Apr 2008 05:37
Luciano Bertini	21 Apr 2008 05:47
Matteo Brancaleoni	21 Apr 2008 05:51
Jonathan Miller	21 Apr 2008 05:54
Jonathan Miller	21 Apr 2008 05:55
Jonathan Miller	21 Apr 2008 08:22
Stewart Smith	21 Apr 2008 10:09
Jonathan Miller	22 Apr 2008 08:12
John David Duncan	22 Apr 2008 13:20
Luciano Bertini	22 Apr 2008 13:42
Jonathan Miller	22 Apr 2008 13:54

Subject:	Re: Response time in Mysql cluster
From:	Luciano Bertini (lber...@ic.uff.br)
Date:	04/22/2008 01:42:43 PM
List:	com.mysql.lists.cluster

John,

So, for example, if I have 2 mysql nodes and 4 ndb nodes, but two of them, from different nodegroups, are turned off, the 2 mysql nodes would not be enough to impose full load at the 2 lasting ndb nodes? Or, puting it in another way, With my 4 mysql nodes all on, It would be better to configure my cluster with 2 ndb nodes and 2 replicas, so that I can run with 1 or 2 ndb nodes according to the load? As I said, I don't care for HA. My problem is to save power without losing the database consistency.

Thanks again, Luciano

John David Duncan wrote:

Luciano,

Based on what we usually see, if you only have 4 mysql servers, then 2 NDB nodes can probably do the job all the time. There should never be any need to run 4 NDB nodes at all.

NDB has a two-phase synchronous commit when you update a row -- the transaction does not complete until it is updated on all replicas. So a cluster with 4 replicas has higher overhead for updates than one with 2 replicas.

If you really don't care about HA and you have Dual Core systems, then you can use 2 replicas, and run 2 data nodes per Host (Computer). Then you could shutdown one Host during light times.

4 Replicas is not well tested as most customers do not want the over head it causes in a production system.

Question, why use cluster if you are not worried about HA?

--Best wishes, /Jeb

Jonathan Miller Cluster Lead QA Developer Certified Cluster Administrator Sun Microsystems / MySQL Austin, Texas US www.mysql.com www.sun.com

I'm Certified, are you? http://www.mysql.com/certification/index.html

Luciano Bertini wrote:

John, Thank you very much for your response. It helped a lot. I will try the release 6.2 when it becomes available. And I haven't heard about mod_ndb. I'll try it. Seems to be a good choice. One variable less in this huge equation may help. I was running mysqld with -10 nice value to give it more privileges. May I ask you another question? How does the data nodes affect the performance scalability? I configured with 4 replicas because I wanted sometimes to turn off data nodes so that I can reduce dynamically the peak capacity of the cluster. If I use 2 replicas I will have only two options: 2 or 4 data nodes running. What I want to do is to save power consumption when the load is low. I don't care about high availability. Luciano John David Duncan wrote:

Luciano,

I do not know why you might see this behavior. There are some ways you might get lower latency: (A) run with 2 replicas rather than 4; (B) use Dolphin cards between the data nodes rather than ethernet. Also, MySQL Cluster 6.2 (based on the former "carrier grade" edition) will be available very soon (next week, I think!), and perhaps you should try this release. Particularly in 6.2 you can set "ndb-cluster-connection-pool" to 3 or 4 in my.cnf, and get improved throughput from the mysqld nodes.

Alternately, you might consider using mod_ndb with Apache. This could take the mysqld out of the equation and turn the three-tier architecture into two tiers.

On Apr 19, 2008, at 10:27 AM, Luciano Bertini wrote:

Hello!

I am using mysql cluster as part of a multi-tier web architecture. It is working very well (version 5.1.21), but during my test workload, which has an average response time of about at most 100ms, sometimes, every 2 or 3 minutes, the response time goes to more than 10 or 20 times the expected value for about 3~4 s. I need to find what is causing this to eliminate it. If anyone can give me any hint I'd appreciate. My configuration is as follows. 9 machines are connected in a gigabit eth. network. The first, runs apache with load balancing and ndb_mgmd. This machine proxies requests to other 4 machines which run apache+php and mysqld. The other 4 machines are data nodes configured with 4 replicas. When the increase in the response time occur, all apache machines, specially the load balancer, becomes 90~100% idle, going back to normal when the response time normalizes.

I don't know how to figure out where the problem is. Does anybody know how I can find it out? I tried to look at the top command in every machine at the time the problem occur, and the only thing I can see is that all apache threads get out of the top processes. Is it IO in the mysql cluster? I tried diskless=true, but didn't help. Please, help.

Thanks in advance, Luciano

--John David Duncan, Systems Engineer MySQL / Sun San Francisco, California, USA Office: +1 408 213-6597 Mobile: +1 415 260-3202

--Best wishes, /Jeb

Jonathan Miller Cluster Lead QA Developer Certified Cluster Administrator Sun Microsystems / MySQL Austin, Texas US www.mysql.com www.sun.com

I'm Certified, are you? http://www.mysql.com/certification/index.html <jmiller.vcf>