performance

[prashant sindhu]

NDB cluster7.5(MySQL 5.7) is taking more time in fetching data, if number of rows in table is large(2 million)

0
down vote
favorite


I am trying to setup NDB cluster(MYsql 5.7) for one of my real time application(with a high volume of read and write concurrency).

My Setup -

3 Data Nodes 1 Management Node 1 MySQL node

All nodes are of amazon EC2 r3.4xlarge type. OS - centos 7

i created one table and partioned by primary key to make sure same primary key data goes in single node.

Table - CREATE TABLE ContactsAgentContacts( uniqueid integer not null, did varchar(32) not null, nId varchar(50), companyname varchar(50), primary key (uniqueid,did) ) ENGINE=NDBCLUSTER PARTITION BY KEY(did);

Now i populated my table with 2 million of record in such a way that Each did contains 1K record.

Query Fired - SELECT DISTINCT ContactsAgentContacts.companyname AS 'fullname' from ContactsAgentContacts where did='xyz';

performance getting -

with single concurrency - fetching 1k record regarding one did - 800 ms with 25 concurrency - 1.5 sec with 50 concurrency - 3 sec

As i am trying to develop a real time system any value more then 300 ms is too much for me and this time is increaasing as number of rows are increasing in table.

Please let me know how to optimize my solution.

My configiration .
config.ini

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

[ndb_mgmd default]
# Directory for MGM node log files
DataDir=/var/lib/mysql-cluster

[ndb_mgmd]
#Management Node db1
HostName=10.2.25.129
NodeId=1

[ndbd default]
NoOfReplicas=1
DataMemory=2000M
IndexMemory=300M
LockPagesInMainMemory=1
#Directory for Data Node
DataDir=/var/lib/mysql-cluster
NoOfFragmentLogFiles=300
MaxNoOfConcurrentOperations=100000
SchedulerSpinTimer=400
SchedulerExecutionTimer=100
RealTimeScheduler=1
TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=200
RedoBuffer=32M

[ndbd]
#Data Node db2
HostName=10.2.18.81
NodeId=2