Mysql cluster - Yet another "slow" question

alex3948
alex3948 used Ask the Experts™
on
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
MaxNoOfConcurrentOperations = 250000
MaxNoOfConcurrentIndexOperations = 250000
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
Do you really need to use LEFT JOIN?  It doesn't look like a user can have a postal code that is not among postalcodes, same with postalcode and city, and city and province.  Using INNER JOIN should improve performance.

Author

Commented:
Thanks for the fast reply, but I am not seeing any improvement in speed. Here is something else I found:

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
Top Expert 2014
Commented:
For your cluster storage use fast disks or SSDs since it can't sit in server RAM as failover wouldn't be valid if it did.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
I am using RAID1 (mirror) 10K SCSI disks on each server node, isn't that enough ?
When doing a join, it is crucial to make sure that there is an index on each field involved in the join. From your view definition, there appear to be a few:
   postalcodes.id
   users.postalid
   cities.id
   postalcodes.cityid
   provinces.id
   cities.provinceid

The other thing I would look at is the engine under which the tables were created. If the engine is MyISAM, you could have locking issues slowing down queries. InnoDB is faster because it can use a memory cache to make row fetches 1000 times faster than from disk.

Author

Commented:
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
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial