Mysql cluster - Yet another "slow" question
Hi all! I've managed to set-up a working MySQL cluster setup, I am in production mode and no bugs occured. However, the select queries are VERY slow. Example, I have a view which is made of:
CREATE VIEW vw_users AS SELECT u.*, pc.*, ci.*, pr.*
FROM users AS u
LEFT JOIN postalcodes AS pc ON pc.id = u.postalid
LEFT JOIN cities AS ci ON ci.id = pc.cityid
LEFT JOIN provinces AS pr ON pr.id = ci.provinceid
The users table has 276 records, this is very few. Yet, this query takes up to 0.9 seconds to run. This is slowing my pages to a crawl.
My setup:
mysqld + management : Dell PowerEdge 2650 (2 Xeon)
ndb (2x): Dell PowerEdge 2650 (2 Xeon) (2x)
I am using a full-duplex isolated Cisco Catalyst 2900XL 100Mbps switch. Traffic is not enough for a gigabit switch. The machines have 2GB RAM and run CentOS 6.2
My config.ini file:
[ndbd default]
NoOfReplicas = 2
DataDir = /var/lib/mysql-ndb-data
DataMemory = 768M
IndexMemory = 128M
StringMemory = 256M
TransactionBufferMemory = 1M
MaxNoOfOrderedIndexes = 1024
MaxNoOfAttributes = 10000
MaxNoOfTables = 2500
MaxNoOfConcurrentOperation
MaxNoOfConcurrentIndexOper
MaxNoOfExecutionThreads = 2
LockPagesInMainMemory = 1
RealtimeScheduler = 1
According to my tests, the delay comes from the select query. I had a PHP script testing all that, and I got:
Connection: 0.000945 seconds
Select DB: 0.0002169 seconds
Query the 276-row users view: 0.880137 seconds (WHOA!)
Nothing else is on the same network as my nodes. No error in log files. All my indexes are correct and nothing wrong according to EXPLAIN SELECT.
In your opinion, what's wrong?
Thanks
CREATE VIEW vw_users AS SELECT u.*, pc.*, ci.*, pr.*
FROM users AS u
LEFT JOIN postalcodes AS pc ON pc.id = u.postalid
LEFT JOIN cities AS ci ON ci.id = pc.cityid
LEFT JOIN provinces AS pr ON pr.id = ci.provinceid
The users table has 276 records, this is very few. Yet, this query takes up to 0.9 seconds to run. This is slowing my pages to a crawl.
My setup:
mysqld + management : Dell PowerEdge 2650 (2 Xeon)
ndb (2x): Dell PowerEdge 2650 (2 Xeon) (2x)
I am using a full-duplex isolated Cisco Catalyst 2900XL 100Mbps switch. Traffic is not enough for a gigabit switch. The machines have 2GB RAM and run CentOS 6.2
My config.ini file:
[ndbd default]
NoOfReplicas = 2
DataDir = /var/lib/mysql-ndb-data
DataMemory = 768M
IndexMemory = 128M
StringMemory = 256M
TransactionBufferMemory = 1M
MaxNoOfOrderedIndexes = 1024
MaxNoOfAttributes = 10000
MaxNoOfTables = 2500
MaxNoOfConcurrentOperation
MaxNoOfConcurrentIndexOper
MaxNoOfExecutionThreads = 2
LockPagesInMainMemory = 1
RealtimeScheduler = 1
According to my tests, the delay comes from the select query. I had a PHP script testing all that, and I got:
Connection: 0.000945 seconds
Select DB: 0.0002169 seconds
Query the 276-row users view: 0.880137 seconds (WHOA!)
Nothing else is on the same network as my nodes. No error in log files. All my indexes are correct and nothing wrong according to EXPLAIN SELECT.
In your opinion, what's wrong?
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am using RAID1 (mirror) 10K SCSI disks on each server node, isn't that enough ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, InnoDB is indeed 10x faster, but I am running a
Cluster. Can I mix innodb tables (the values will never
Change since all postalcodes stay the same) with my ndb cluster
Tables? And yes i have all the indexes correct
Cluster. Can I mix innodb tables (the values will never
Change since all postalcodes stay the same) with my ndb cluster
Tables? And yes i have all the indexes correct
Of course, I should have realized that with a cluster, you are working with NDB tables.
I don't know if you can mix in InnoDB tables.
I don't know if you can mix in InnoDB tables.
ASKER
When I call an "explain select", I only see "ref" and "eq_ref", so it seems good, the problem may reside in my cluster installation, any advice?
THanks