MySQL Bugs: #67609: NDB on high wait IO CPU load => complete service crash

Bug #67609	NDB on high wait IO CPU load => complete service crash
Submitted:	16 Nov 2012 13:32	Modified:	25 Feb 2013 9:26
Reporter:	Gerald Degn	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	mysql-5.5.27 ndb-7.2.8	OS:	Linux (RHEL 6.3)
Assigned to:	Daniel Smythe	CPU Architecture:	Any

Description:
Hello,

I have a problem, when running MySQL cluster with 2 NDBMT nodes on high query load, NDB node stops responding to MYSQLD. This happens sometimes after few minutes, sometimes after 10 hours or more.

At the point when the problem starts, I see high CPU wait IO load (6 CPUs 100 % on WA). Followed, the server is completly out of operation. I cannot login by SSH anymore, also direct console shows no output anymore. No iostat tracing possible.

The only way to recover this situation is to reboot the server.

The setup I have:
- 2 HP ProLiant DL380 G7 servers
- NDB uses direct connected LAN interfaces (no switch)
- 48 GB RAM
- 2 Intel(R) Xeon(R) CPU X5670 @ 2.93GHz CPUs with 6 cores each (2 threads 
  per CPU core)

The point is, I have absolutely now INSERT/UPDATE operations on MYSQL and NDB, so it is very strange to have this high CPU I/O load.

Thanks
Gerald

How to repeat:
- Start high query load to MYSQL DB accessing table in NDB (I am using a simple Java program to execute queries)

output of ndb_error_reporter

Attachment: ndb_error_report_20121116142131.tar.bz2 (application/octet-stream, text), 444.15 KiB.

output of CPU, showing 6 CPUs on 100 wa load

Attachment: cpu_top.txt (text/plain), 7.94 KiB.

Hi,

I don't think there is a problem here - what kind of disk hardware is on
the ndbmtd nodes? 

I'm seeing around 4-5GB of data memory used out of 10GB or so configured,
but you have TimeBetweenLocalCheckpoints = 31 which is 8GB...
( http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-ti... )

So this explains the variable amount of time between local checkpoints... 
Then I think when local checkpoint starts your disk is saturated from the
writing. So that is why I ask about disk hardware - if it cannot reliably
write out DataMemory then things will get slow regardless of how long you
wait between local checkpoints.

You can also correlate the high CPU IO Wait time with the cluster logs
by setting ndb_mgm -e 'ALL CLUSTERLOG CHECKPOINT=15' - then we will see 
in ndb_1_cluster.log when LCP starts/ends.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi,

sorry for long delay with feedback.

First, thanks for feedback and hints to check settings.
Looks you're right that the issue was caused by checkpoints. We made a 2nd installation, where we left checkpoint parameters on default, and could not reproduce this issue anymore.

We will investigate on checkpoint parameters, to find optimal settings for our installation.

I will close this ticket.

Regards
Gerald