Sysbench data

[Pete]

Hello,

Apologies for the long post.

Having looked at MySQL cluster documentation I thought that this would be a suitable high availability reasonable performance balance.

With this in mind I've put together a test configuration as below;

1x MGM
2x SQL servers
2x Data servers

All nodes are dual opteron units with 8GB RAM

This is then replicated to a second cluster configured in the same manner.

On installing a local large database and having converted it to engine ndb it was quickly found that select * was v. rapid BUT any order by queries were taking significantly longer (approx 8 fold as an average) over a stand alone MyISAM based database.

To test this I've installed Sysbench on the relevant nodes and I've compared MyISAM, NDB cluster and NDB cluster over replication. The output is listed below (along with conifg files) and confirms a significant performance hit accross the board. This is comparable in scope (although possibly for different reasons) with that observed with our local databases when moved to ndb.

Now given this is a first attempt I'm sure that I've missed something, particularly as there appears to be significant IO across the nodes and the SQL instance queried will hit and remain at 99.9 %. Can anyone help guide me in the right direction ? I have tried varying the Data and Index memory to no avail.

Many thanks

Pete

MyISAM
SySbench output summary (OLTP read only)

$SYSBENCH --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --oltp-read-only --mysql-db=test run
OLTP test statistics:
queries performed:
read: 1400000
write: 0
other: 200000
total: 1600000
transactions: 100000 (555.01 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400000 (7770.08 per sec.)
other operations: 200000 (1110.01 per sec.)

Test execution summary:
total time: 180.1783s
total number of events: 100000
total time taken by event execution: 2881.2587
per-request statistics:
min: 0.0021s
avg: 0.0288s
max: 0.1245s
approx. 95 percentile: 0.0505s

Threads fairness:
events (avg/stddev): 6250.0000/25.94
execution time (avg/stddev): 180.0787/0.02

sysbench v0.4.7: multi-threaded system evaluation benchmark


cf
NDB
SySbench output summary (OLTP read only)

OLTP test statistics:
queries performed:
read: 1400000
write: 0
other: 200000
total: 1600000
transactions: 100000 (289.33 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400000 (4050.67 per sec.)
other operations: 200000 (578.67 per sec.)

Test execution summary:
total time: 345.6220s
total number of events: 100000
total time taken by event execution: 5528.3857
per-request statistics:
min: 0.0293s
avg: 0.0553s
max: 0.0809s
approx. 95 percentile: 0.0634s

Threads fairness:
events (avg/stddev): 6250.0000/12.62
execution time (avg/stddev): 345.5241/0.01

sysbench v0.4.7: multi-threaded system evaluation benchmark

And for non-transactional the execution time difference for MyISAM approx = 2.99 secs vs 18.79 till completion.

Config file details:

config.ini file
[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=300M
IndexMemory=128M
MaxNoOfConcurrentOperations=1000000
MaxNoOfConcurrentTransactions=100000
MaxNoOfLocalOperations=100000
TimeBetweenWatchDogCheck= 20000
TimeBetweenLocalCheckpoints=11
TransactionDeadlockDetectionTimeout=6000
TransactionBufferMemory=30M
StartPartialTimeout= 30000
StartPartitionedTimeout= 60000
TimeBetweenGlobalCheckpoints=1500
TimeBetweenInactiveTransactionAbortCheck=5000
UndoIndexBuffer=64M
RedoBuffer=128M
NoOfFragmentLogFiles=144
Diskless=1

#[TCP DEFAULT]
#portnumber=2202

[NDB_MGMD]
hostname=xxxxxx
datadir=/usr/local/mysql-cluster

[NDBD]
hostname=xxxxxx
datadir=/usr/local/mysql/data
MaxNoOfLocalOperations=10000000

[NDBD]
hostname=xxxxxx
datadir=/usr/local/mysql/data
MaxNoOfLocalOperations=10000000

[MYSQLD]
hostname=xxxxxx

[MYSQLD]
hostname=xxxxxx


my.cnf
[MYSQLD]
ndbcluster
ndb-connectstring=xxxxxx
default-table-type=NDBCLUSTER
engine_condition_pushdown=1
ndb_autoincrement_prefetch_sz=250
skip-external-locking
read_buffer_size=10M
sort_buffer_size=10M

[MYSQL_CLUSTER]
ndb-connectstring=xxxxxx