mysql ndbcluster slooooow on complex queries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

mysql ndbcluster slooooow on complex queries

Post by ody »

I spent a good deal of time building an ndb cluster thinking being a memory based storage engine would be shiz fast.. well turns out anything more complicated than a single table select results in performance loss of 100x+ that of a simple innodb / myisam standalone server.

Has anyone setup an ndb cluster and noticed the poor performance of complex queries? or am just a simple minded town idiot thats missed something obvious (d11wtq | :roll:).

cheers
ody
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

Connection speed?
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

All nodes are on 100mbit full duplex connections into a cisco switch.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

My experience with queries and what will slow them down:

1. If they're inside of a for/while/foreach/list loop
2. Using joins, I've only used a join once, but it was 20 times slower than a regular select query
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

Text indexes are not supported. Range statistics are not available. USE INDEX and FORCE INDEX will help that. You lose query caching too. No foreign key constraints.

I'm thinking NDB MySQL cluster isn't exactly meant to provide increase proformance but fail safe reliability. :?
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

Buddha443556 wrote:Text indexes are not supported. Range statistics are not available. USE INDEX and FORCE INDEX will help that. You lose query caching too. No foreign key constraints.

I'm thinking NDB MySQL cluster isn't exactly meant to provide increase proformance but fail safe reliability. :?
I was thinking the same thing, it seems to be more suited to situations where you need reliability and load handling.. mysql replication here I come!
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

ody wrote:I was thinking the same thing, it seems to be more suited to situations where you need reliability and load handling.. mysql replication here I come!
Hope you come back and tell us how that goes.
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

Yeap, replication works just as I needed. :)
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post by BDKR »

Buddha443556 wrote: I'm thinking NDB MySQL cluster isn't exactly meant to provide increase proformance but fail safe reliability. :?
Exactly.

However, I think it's not that difficult to get to an exteme level of reliability without too much work with replication. I've use replication in two different clusters (one based on Turbo Cluster 6 and the other using Cisco Local Directors hardware load balancers) and the only thing that's tough to overcome is automated fail over when a master takes one in the gut. The first solution I had to manage this worked somewhat but I later realized that there was a much easier way to do it. Henceforth, my next project to finish (I've allready started on it and gotten some way in, but I need to step back and take a 10k foot veiw then refactor).

Anyway, if you use only two boxes (master, slave), then at least make sure both have two mirrored drives at the very least. In that way, as far as I'm concerned, you have 4 seperate copies of your data set.

Good luck.
Post Reply