MySQL Forums
Forum List  »  NDB clusters

performance
Posted by: prashant sindhu
Date: October 03, 2017 01:23AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
performance
829
October 03, 2017 01:23AM
398
October 06, 2017 11:52AM


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.