Cluster Query execution time slower

[Karthick Kumar]

Hi Team,

We are testing cluster setup for our company, the results are not as expected in Query execution time. We need your kind support in understanding the cause of this issue.

We tried our complex query in MYQSL SERVER version 5.6 & query execution time was 16 sec. Same query has been executed in Cluster with the below mentioned specification,
we got errors (ERROR 1296 (HY000): Got error -1 'Unknown error code' from NDBCLUSTER and ERROR 1297 (HY000): Got temporary error 20016 'Query aborted due to node failure' from NDBCLUSTER)
and we fixed the issue by running set ndb_join_pushdown=off; and the execution time was 577.359 sec.

so we tried to optimize query execution time by adaptive-query-localization method as mentioned in website , But when we try to run with ndb_join_pushdown=on; we are getting an error Error Code: 1297 Got temporary error 20008 'Query aborted due to out of query memory' from NDBCLUSTER
How can we solve this issue?

Cluster Specification:

Cluster Setup Version : mysql-cluster-gpl-7.3.0-win32
Cluster nodes : 1 Management node, 2 SQL Node & 4 DATA NODE (2 host machines)

Management Node & Data Nodes Specification are :
Windows XP,
Pentium CPU 3.40GHZ,
3.39GHZ, 2.98 GB RAM
SQL Nodes Specifications:
First SQL node
Windows7
Intel 2Duo CPU E8200 @ 2.66GHZ 2.67GZ
RAM 4 GB

Second SQL node
Windows Server 2008 R2 standard
Intel Xeon(R) CPU E5520 @ 2.27GHZ 2.27 GHZ (2 processors)
RAM : 16 GB

As mentioned in your website, we also tried below cases, but there is no luck
Case1 : Cluster having 2 Data nodes & 2 SqL node
Case2 : Cluster having 3 Data nodes & 3 SqL node
Case3 : Cluster having 4 Data nodes & 2 SqL node

Even running simple query (select with distinct) in cluster is slower than non-cluster MYSQL server, Just for your reference

Query execution time (100mb; diskless=0/1) Query execution time within cluster setup (1gb; diskless=0) Query execution time within cluster setup (1gb; diskless=1) Query execution time, Without cluster
9.907 8.188 7.797 3.328
9.953 7.543 7.062 1.75
9.891 7.766 7.875 1.75


Config file is as below

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2 # Number of replicas
DataDir=C:/mysql/bin/cluster-data # Directory for each data node's data files

DataMemory = 1024M
IndexMemory = 512M
MaxNoOfConcurrentOperations = 42000
Diskless=1
# MaxAllocate = 1G
# MaxNoOfConcurrentIndexOperations = 25000
# MaxNoOfExecutionThreads=4
# MaxNoOfOrderedIndexes = 8000
# MaxNoOfAttributes = 8000
# MaxNoOfTables = 2500


[ndb_mgmd]
# Management process options:
HostName=10.81.129.235 # Hostname or IP address of management node
DataDir=C:/mysql/bin/cluster-logs # Directory for management node log files

[ndbd]
# Options for data node "A": # (one [ndbd] section per data node)
nodeid=10
HostName=10.81.129.233 # Hostname or IP address
datadir=C:/mysql/data

[ndbd]
# Options for data node "B":
nodeid=20
HostName=10.81.129.234 # Hostname or IP address
datadir=C:/mysql/data

[ndbd]
# Options for data node "A": # (one [ndbd] section per data node)
nodeid=30
HostName=10.81.129.233 # Hostname or IP address
datadir=C:/mysql/data1

[ndbd]
# Options for data node "B":
nodeid=40
HostName=10.81.129.234 # Hostname or IP address
datadir=C:/mysql/data1

[mysqld]
# SQL node options:
HostName=10.81.128.133 # Hostname or IP address

[mysqld]
# SQL node options:
HostName=10.81.127.94 # Hostname or IP address

Much appreciate your help.

Regards,
Karthick K



Edited 1 time(s). Last edit at 12/06/2012 07:53AM by Karthick Kumar.