Re: Response time in Mysql cluster - Jonathan Miller - 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:	Jonathan Miller (jmil...@mysql.com)
Date:	04/22/2008 01:54:59 PM
List:	com.mysql.lists.cluster

Hi,

Yes, you could run 2 DN each on a different host using 2 replicas and X number of mysqlds attached to the cluster.

If you take a look during heavy loads and you see that the mysqlds are maxing out on CPU and NDBD's are not, it would probably be time to add additional mysqlds to the cluster.

You can add additional slots in your config.ini now so you can just add mysqld's as you need them.

In addition, to conserve power, you can take down some of the hosts with mysqld's during the light loads.

Also, be sure to start a backup before you take down one of the data nodes just as a safe guard.

-- Best wishes, /Jeb

Jonathan Miller Cluster Lead QA Developer Certified Cluster Database 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,

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>