Re: select for update problem - Martin Skold - 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:	Martin Skold (Mart...@mysql.com)
Date:	03/14/2007 11:08:44 AM
List:	com.mysql.lists.cluster

Hi!

The

Error data: DBTUP could not allocate memory for Page

points to that you have allocated more memory than you have physically. Check your configuration. ndbd nodes allocate memory statically.

BR -- Martin Yong Lee wrote:

This is pretty consistent on my 5.0.27 system.

I just need to have 2 mysql connections both of which run :

begin work; select * from <table> where <primary key> = <value> for update;

then my system begins to crawl as mysqld starts to suck up memory and eventually runs out of swap causing various problems with ndbd and/or mysql. What's interesting is that doing a commit on the connection with the lock does not affect the other connection waiting for the lock. On a first try, the hung connection will usually come back and say that it can't acquire the lock (after a long period ... usually a few minutes). Then doing the test again will cause mysqld to crash.

I find that doing the test against innodb tables works correctly.

If there's anything else I can do to provide any more information please let me know.

I couldn't find any logs of interest for either mysql or ndbd (after one or the other crashes there are logs detailing the crash) eg:

mysql: 070314 10:53:04 mysqld restarted 070314 10:53:13 InnoDB: Started; log sequence number 0 49446 070314 10:53:16 [Note] Recovering after a crash using mylogbin 070314 10:53:16 [Note] Starting crash recovery... 070314 10:53:16 [Note] Crash recovery finished. 070314 10:53:17 [Warning] 'user' entry 'root@localhost.localdomain' ignored in --skip-name-resolve mode. 070314 10:53:17 [Warning] 'user' entry '@localhost.localdomain' ignored in --skip-name-resolve mode. 070314 10:53:17 [Warning] 'user' entry 'cdradmin@localhost.localdomain' ignored in --skip-name-resolve mode. 070314 10:53:17 [Warning] 'user' entry 'locker@localhost.localdomain' ignored in --skip-name-resolve mode. 070314 10:53:17 [Warning] 'db' entry 'cdrtool cdradmin@localhost.localdomain' ignored in --skip-name-resolve mode. 070314 10:53:17 [Warning] 'db' entry 'cdrtool locker@localhost.localdomain' ignored in --skip-name-resolve mode. 070314 10:53:17 [Note] /usr/sbin/mysqld: ready for connections.

ndbd: Time: Monday 12 Mars 2007 - 22:38:27 Status: Permanent error, external action needed Message: Memory allocation failure, please decrease some configuration parameters (Configuration error) Error: 2327 Error data: DBTUP could not allocate memory for Page Error object: Requested: 32768x12800 = 419430400 bytes Program: /usr/sbin/ndbd Pid: 4747 Trace: /var/lib/mysql-cluster/ndb_4_trace.log.6 Version: Version 5.0.27 ***EOM***

Time: Monday 12 Mars 2007 - 22:54:51 Status: Temporary error, restart node Message: 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) Error: 2305 Error data: Arbitrator decided to shutdown this node Error object: QMGR (Line: 4555) 0x0000000e Program: /usr/sbin/ndbd

-----Original Message----- From: Martin Skold [mailto:Mart...@mysql.com] Sent: March 14, 2007 6:34 AM To: Yong Lee Cc: clus...@lists.mysql.com Subject: Re: select for update problem

Hi again!

Can you send the log/error files when this happened so we can try and analyze if this something that has been fixed in later versions, or if it is something new.

Thanks! -- Martin

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

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.