我正在尝试为我的一个实时应用程序设置NDB集群（MYsql 5.7）（具有大量的读写并发） .
我的设置 -
3数据节点1管理节点1 MySQL节点
所有节点均为亚马逊EC2 r3.4xlarge类型 . OS - centos 7
我创建了一个表并由主键分区，以确保相同的主键数据在单个节点中 .
Table Schema -
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（确实）;
现在我用200万条记录填充了我的表，每条记录都包含1K记录 .
查询被解雇 - SELECT DISTINCT ContactsAgentContacts.companyname AS 'fullname' from ContactsAgentContacts where did='xyz';
表现得到 -
单一并发 - fetching 1k record of one did
**with 1 read concurrency - 800 ms
with 25 read concurrency - 1.5 sec
with 50 read 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
#LockExecuteThreadToCPU=1
LockMaintThreadsToCPU=0
[ndbd]
#Data Node db3
HostName=10.2.20.15
NodeId=3
#LockExecuteThreadToCPU=1
LockMaintThreadsToCPU=0
[ndbd]
#Data Node db4
HostName=10.2.24.28
NodeId=4
#LockExecuteThreadToCPU=1
LockMaintThreadsToCPU=0
[mysqld]
#SQL Node db5
HostName=10.2.29.42
NodeId=5
1 回答
切换到主键（did，uniqueid）意味着主键上的有序索引将用于扫描，而不是在其中一个分区上进行全表扫描 .
这应该可以改善数量 .