MySQL Forums
Forum List  »  NDB clusters

find the bottleneck if any in the mysqlcluster
Posted by: urgen sherpa
Date: February 17, 2016 09:17AM

find the bottleneck on mysqlcluster or apache/php

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 < 3ms 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/tips to improve the website performance?

Thank you

Options: ReplyQuote


Subject
Views
Written By
Posted
find the bottleneck if any in the mysqlcluster
1965
February 17, 2016 09:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.