Query faster 10 times when stop on data node
Posted by:
ray liu
Date: October 29, 2014 09:16PM
When stop a data node, keep only one running, my query faster 10X.
when two data node running, my query take 10 sec,
when I stop 1 data node, this query only take 0.3 sec
Can anyone explain this?
-------------------------------------------------------------
My config.ini
[ndbd default]
LockPagesInMainMemory=1
ODirect=1
NoOfReplicas=2
DataMemory=2000M
IndexMemory=200M
MaxNoOfTables=1800
MaxNoOfUniqueHashIndexes=3000
MaxNoOfAttributes=50000
MaxNoOfOrderedIndexes=16000
[tcp default]
#portnumber=2202
SendBufferMemory=2M
ReceiveBufferMemory=2M
[ndb_mgmd]
hostname=192.168.1.10
datadir=/var/lib/mysql-cluster
#[ndb_mgmd]
#hostname=192.168.1.11
#datadir=/var/lib/mysql-cluster
[ndbd]
hostname=192.168.1.10
datadir=/var/lib/mysql-cluster
[ndbd]
hostname=192.168.1.11
datadir=/var/lib/mysql-cluster
[mysqld]
hostname=192.168.1.10
[mysqld]
hostname=192.168.1.11
[mysqld]
[mysqld]
[mysqld]
My Query SQL:
SELECT t1.StoreId,
t1.Name AS StoreName,
t1.Latitude,
t1.Longitude,
t1.MemberLeverCode,
t1.Score,
t1.Address,
t1.MainFeatures,
1 AS LocationStatus,
GetDistance(t1.Longitude,
t1.Latitude,
113.69032,
34.81928)
AS DistanceSql
FROM store AS t1 , store_category_rel AS t2
WHERE ((t1.ApplyStatus = 1 && t1.IsRegComplete) OR t1.Source = 0)
AND t1.StoreId = t2.StoreId AND t2.StoreCategoryId = 2
AND t1.AreaCode LIKE '4101%'
ORDER BY LocationStatus DESC, DistanceSql ASC
LIMIT 0, 10