From: Nam Luc Tran
Date: September 3 2009 8:31am
Subject: MySQL Cluster contention when increasing amount connections
List-Archive: http://lists.mysql.com/benchmarks/264
Message-Id: <1251966686.4a9f7ede74cc3@euranova.eu>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="_=_swift_v4_12519666864a9f7ede78091_=_"

--_=_swift_v4_12519666864a9f7ede78091_=_
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hello,

I have set up a cluster with 4 servers (quad core opteron=
, 8Gb Ram,
Gigabit Ethernet, Fedora 11) hosting 4 datanodes and 1 SQ=
L node + 1
separate management node using MySQL Cluster=C2=A0 7.0.6 C=
ommunity
Edition. I have run several benchmarks on it.
When runn=
ing the Hammerora benchmark=C2=A0 (adapting a version of the
TPC-C) my=
 throughput values are increasing until ca. 50 users (~140000
tpm). =
From a certain amount of users (>50-60) the test starts with
increas=
ing values of throughput then drops violently and ends
stagnating ro=
und poor values (~6000 tpm) till the end. Most of the
users report "=
Lock wait timeout exceeded; try restarting transaction"
error. When I=
 run the same benchmark on 2 additional SQL nodes in the
cluster at =
the same time on the same DB, the problem does not happen
as the glo=
bal throughput is higher (3*~70000 tpm), so this let me
think that th=
is is not a network or load problem between the nodes.

The same =
happens with the Sysbench oltp benchmark. Starting from ~50
running t=
hreads, I get poor response time and transactions/min values.

S=
o where can the problem come from? Is the maximum amount of
connectio=
ns to the SQL node reached? 50 seems quite little. I already
set tho=
se values to my.cnf on the SQL node:
engine_condition_pushdown=3D1=

max_connections=3D1000
ndb-cluster-connection-poo=
l=3D2

And here is an overview of my config.ini:
[begin]=

[ndbd default]
NoOfReplicas=3D2=C2=A0=C2=A0=20
DataMemory=3D=
5400M=C2=A0=C2=A0=C2=A0=20
IndexMemory=3D675M=20

#LockPagesInM=
ainMemory=3D1=20
RedoBuffer=3D32M
NoOfFragmentLogFiles=3D768=C2=A0=
=C2=A0=C2=A0=C2=A0=20
MaxNoOfConcurrentOperations=3D500000=C2=A0=
=20
MaxNoOfLocalOperations=3D700000
MaxNoOfConcurrentTransactions=
=3D10000
MemReportFrequency=3D30
TransactionDeadlockDetectionTim=
eout=3D10000

BackupMaxWriteSize=3D1M
BackupDataBufferSiz=
e=3D16M
BackupLogBufferSize=3D4M
BackupMemory=3D20M

[=
tcp default]
#portnumber=3D2202=C2=A0=20

[ndb_mgmd]
ho=
stname=3D192.168.0.42=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=20
datadir=3D/=
var/lib/mysql-cluster=C2=A0=20
ArbitrationRank=3D1

[ndbd]=

hostname=3D192.168.0.35=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=20
datadir=3D/usr/local/mysql/data=C2=A0=C2=A0=
=20

[ndbd]
hostname=3D192.168.0.38=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=20
datadir=3D/usr/local/mysq=
l/data=C2=A0=C2=A0=20

[ndbd]
hostname=3D192.168.0.37
d=
atadir=3D/usr/local/mysql/data

[ndbd]
hostname=3D192.168.0.=
34
datadir=3D/usr/local/mysql/data
[mysqld]
[...]
[end]=


Thanks for your help

Tran Nam-Luc


--_=_swift_v4_12519666864a9f7ede78091_=_--