How to find bottleneck in mysqlcluster and apache/php server?

I have setup a mysql cluster 7.3.x with 2 Datanodes,2 SQL nodes and 1 Management Node in a LXD/LXC centos 6.7 containers and a separate Apache 2.2.x/PHP-5.5 web server. The website currently serves a phpweb-app which is both read/write intensive on mysql(InnoDB/MyISAM).

Below are physical specs of servers:

Server 1:-
CPU - Intel(R) Xeon(R) CPU X3450  @ 2.67GHz
Memory: 12G
180G SSD hdd

Installed mysqlcluster component-

a) 1 Data node
b) 1 SQL node

Server 2:-
CPU - Intel(R) Xeon(R) CPU X3430  @ 2.40GHz
Memory - 16G
180G SSD hdd
Installed Mysql CLuster component:- 1 management, 1 SQL and 1 Datanode.

Since the mysqldump of the database was < 80MB from old InnoDB/MyISAM DBserver, I thought it will be ok to change the table engine to "ndbcluster" and import. Below are my example config.ini(from Management node) and my.cnf files(from Data and SQL node).

#Config.ini

[tcp default]
SendBufferMemory=2M
ReceiveBufferMemory=2M

[ndb_mgmd]
NodeId=10
hostname=10.10.10.10
datadir=/var/lib/mycluster

[ndbd default]
NoOfReplicas=2
LockPagesInMainMemory=1
DataMemory=2000M
IndexMemory=500M
ServerPort=2202
ODirect=1
#CompressedLCP=1
#CompressedBackup=1
#table related things
MaxNoOfTables=4096
MaxNoOfAttributes=24756
MaxNoOfOrderedIndexes=2048
MaxNoOfUniqueHashIndexes=512
MaxNoOfConcurrentOperations=500000
MaxNoOfConcurrentTransactions=500000
NoOfFragmentLogFiles=128
TimeBetweenLocalCheckpoints=30
FragmentLogFileSize=256M

[ndbd]
NodeId=20
hostname=10.10.10.20
datadir=/var/lib/mycluster-data
#BackupDataDir=/dir/to/backup

[ndbd]
NodeId=30
hostname=10.10.10.21
datadir=/var/lib/mycluster-data
#BackupDataDir=/dir/to/backup

[mysqld] 
NodeId=40
hostname=10.10.10.30

[mysqld]
NodeId=50
hostname=10.10.10.31

[mysqld]

[mysqld]

[api]

[api]

[api]

[api]

-

DATA Nodes

# my.cnf
[mysqld]
ndbcluster
ndb-connectstring=10.10.10.10

[mysql_cluster]
ndb-connectstring=10.10.10.10

-

SQL node "/etc/my.cnf"

[mysqld]
ndbcluster
default-storage-engine=NDBCLUSTER
net_read_timeout=60000
connect_timeout=60000
max_allowed_packet=32M
max_connections=1000
query_cache_size=128M
query_cache_limit=16M
ndb-cluster-connection-pool=2
slow-query-log=1
slow-query-log-file=/var/log/mysql/slowquery.log
long-query_time=1
log-queries-not-using-indexes
[mysql_cluster]
ndb-connectstring=10.10.10.10

The webserver connection to the sql node works fine but the website performance is very slow(data insert is fast but select operations are very slow). the ping RTT is not that bad < 0.5ms and no packet loss. when i used mysql client from webserver and connect to SQLnode and execute queries involving join operations the sql result response time is 0.3ms. Any suggestions to improve the website/DBcluster performance? Thank you

- - , .

Recent Questions...