Re: select for update problem - Jon Stephens - com.mysql.lists.cluster

14 messages in com.mysql.lists.clusterRe: select for update problem

From	Sent On	Attachments
Yong Lee	12 Mar 2007 21:58
Jon Stephens	13 Mar 2007 04:21
Christiaan den Besten	13 Mar 2007 06:54
Stewart Smith	13 Mar 2007 08:16
Jon Stephens	13 Mar 2007 08:34
Yong Lee	13 Mar 2007 09:00
Yong Lee	13 Mar 2007 09:50
Martin Skold	13 Mar 2007 09:52
Martin Skold	13 Mar 2007 09:54
Stewart Smith	14 Mar 2007 00:51
Martin Skold	14 Mar 2007 06:33
Yong Lee	14 Mar 2007 09:53
Martin Skold	14 Mar 2007 11:08
Yong Lee	14 Mar 2007 12:58

Subject:	Re: select for update problem
From:	Jon Stephens (jo...@mysql.com)
Date:	03/13/2007 08:34:48 AM
List:	com.mysql.lists.cluster

Christiaan den Besten wrote:

Why not?

Is there a technical reason for not doing this? Using 2 boxes in Cluster (and a separate one for management) makes it very 'easy' to convert a master/slave setup to a 2 node cluster setup without addional costs for more hardware ...

Every non-essential process that's running on a data node host takes away CPU/RAM from the data node process. Cluster is an *in-memory* DB - in other words, your entire data store is in RAM.

And anything that makes the machine swap to disk pretty much kills the data node's performance. And (probably, eventually) the data node process. In fact, it states in the manual that a data node host swapping to disk is to be regarded as a very serious problem that needs to be corrected ASAP.

You're much better off running the mysqld and management server on one machine - the management server needs minimal resources. You could possibly get away with running the management server on an old 800MHz with 256 MB RAM. ;)

OTOH, 1GB for a data node host is on the low side.

bye, Chris

It's a very bad idea to run SQL nodes on the same machines as the data nodes. It's specifically recommended that you not do this.

Yong Lee wrote:

Hi all,

I'm using mysql 5.0 clusters using a 3 node setup (1 mgmt, 2 sql/ndb combo nodes) and am trying to test out a simple lock transaction. Through mysql I run a command :

begin work; select * from subscribers where id = 1000 for update;

In another window, I'll login and run : begin work; select * from subscribers where id = 1000 for update;

In the subscribers table, the id is an auto incrementing primary key

I'm expecting the second query to fail because of a lock timeout. What I'm getting is my swap space usage increasing dramatically, and the query taking a very long time to respond. It will eventually come back with a lock timeout message or crash mysql or crash ndbd

checking free

total used free shared buffers cached Mem: 1001 986 15 0 37 352 -/+ buffers/cache: 595 405 Swap: 1983 554 1429

total used free shared buffers cached Mem: 1001 973 27 0 0 12 -/+ buffers/cache: 960 40 Swap: 1983 1880 103

in top : 4993 mysql 17 0 2003m 679m 960 S 1 67.8 0:09.55 mysqld

Node 4: Forced node shutdown completed. Initiated by signal 0. Caused by error 2305: 'Node lost connection to other nodes and can not form a unpartitioned cluster, please investigate if there are error(s) on other node(s)(Arbitration error). Temporary err

So it looks like mysql is sucking up all the memory and when this runs out it's causing ndbd or mysql to die. I'm confused as to why a lock request would suck up so much memory. The database seems fine otherwise, in that I can do normal operations like select, insert, delete etc. no problem. I'm thinking that I may have a configuration file parameter set wrong, but I'm not too sure which one it may be.

I hope someone out there can shed some light into this problem,

thanks, Yong.