MySQL Lists are EOL. Please join:

List:Cluster« Previous MessageNext Message »
From:Vivid Design Date:September 11 2009 2:43pm
Subject:Increase performance of cluster - Disable temporary table?
View as plain text  
Hi all,

I need help in improving the performance (focusing on speed of selects at
the moment) of my MySQL Cluster db. Currently when executing a SELECT with
WHERE clause from a table with 3 million rows the result is returned in
approximately 20 seconds. EXPLAIN query returns 'Using where with pushed
condition; Using temporary; Using filesort'. I have optimised the
performance of the db in terms of ndb parameters and also through the
creation of keys on relevant fields. I think a large performance gain would
be had if the 'Using temporary' table option could be disabled (am i right
in thinking that this is performed on disk?) is this possible?

Any other points which i have overlooked would be greatly received!

The configuration of the cluster is:
2 hardware nodes
    on each node there is a mysqld, ndb and mgmd
MySQL cluster version 7.06
Linux Ubuntu 9.04

configuration (config.ini, my.conf) follows...

config.ini
----------

# config.ini for 2 sets of data, management and sql nodes located with each
set on a different ip address
[TCP DEFAULT]
# recommended is 2M
SendBufferMemory=4M
#recommended is 1M
ReceiveBufferMemory=2M

[NDB_MGMD DEFAULT]
PortNumber=1186
Datadir=/data/mysqlcluster/

[ndb_mgmd]
id=101
hostname=192.168.0.122
ArbitrationRank=0

[ndb_mgmd]
id=102
hostname=192.168.0.126
ArbitrationRank=0

[ndbd default]
noofreplicas=2
Datadir=/data/mysqlcluster/
#DataMemory=1024M
DataMemory=4096M
#IndexMemory=128M
IndexMemory=2048M
#LockPagesInMainMemory=0
TransactionDeadlockDetectionTimeout=12000

#SharedGlobalMemory=1024M
#DiskPageBufferMemory=3072M


Many thanks!,
Chris

Thread
Increase performance of cluster - Disable temporary table?Vivid Design11 Sep
  • Re: Increase performance of cluster - Disable temporary table?ewen fortune11 Sep