MySQL Clustering Config

MySQL Clustering Config

Posted October 6, 2008
Filed under: Uncategorized | Tags: cluster, Clustering, MySQL |

I’ve had two positive comments to my previous post so I figured it was time to write an update regarding how my work has been going.

The MySQL clustered database is part of a large project I’ve been working on for the last 2 months. The basic server setup is two clustered JBoss 4.2.3Application Servers running on top of two clustered MySQL 5.0.67 servers. There is a 3rd Server which is a backup which currently only runs the MySQL manager ment console. I’ve noticed that even if there is high load on the 2 NDB data nodes, the Management console does not do much.

The four main servers run 2 Dual-Core AMD Opteron(tm) Processors with 8 gig of ram.

Even though alot of my work has been to rework my own code in order to optimize and improve it, alot of time has been spent looking for a configuration for the MySQL cluster that would cope with the load as I found that it was quite easy to kill the cluster in the beginning.

The config I am currently using is below and following are some results of the load testing I’ve been doing.

[TCP DEFAULT]

SendBufferMemory=2M

ReceiveBufferMemory=2M

 

[NDBD DEFAULT]

NoOfReplicas=2

 

# Avoid using the swap

LockPagesInMainMemory=1

 

#DataMemory (memory for records and ordered indexes)

DataMemory=3072M

 

#IndexMemory (memory for Primary key hash index and unique hash index)

IndexMemory=384M

 

#Redolog

# (2*DataMemory)/64MB which is the LogFileSize for 5.0

NoOfFragmentLogFiles=96

 

#RedoBuffer of 32M. If you get "out of redobuffer" then you can increase it but it

#more likely a result of slow disks.

RedoBuffer=32M

 

MaxNoOfTables=4096

MaxNoOfAttributes=24756

MaxNoOfOrderedIndexes=2048

MaxNoOfUniqueHashIndexes=512

 

MaxNoOfConcurrentOperations=1000000

 

TimeBetweenGlobalCheckpoints=1000

 

#the default value for TimeBetweenLocalCheckpoints is very good

TimeBetweenLocalCheckpoints=20

 

# The default of 1200 was too low for initial tests. But the code has been improved alot

# so 12000 may be too high now.

TransactionDeadlockDetectionTimeout=12000

DataDir=/var/lib/mysql-cluster

BackupDataDir=/var/lib/mysql-cluster/backup

 

[MYSQLD DEFAULT]

 

[NDB_MGMD DEFAULT]

 

# Section for the cluster management node

[NDB_MGMD]

# IP address of the management node (this system)

HostName=10.30.28.10

 

# Section for the storage nodes

[NDBD]

# IP address of the first storage node

HostName=10.30.28.11

 

[NDBD]

# IP address of the second storage node

HostName=10.30.28.12

 

# one [MYSQLD] per storage node

[MYSQLD]

[MYSQLD]

 

Here are some numbers of what I’ve been able to get out of the MySQL cluster. One iteration of my code results in:
12 selects
10 inserts
10 updates
4 deletes
This might sound like a lot, but it is a service oriented application that relies on persistent JBoss queues and demands 100% redundancy so even if the server dies, it will pickup where it died and does not loose any data.

My first benchmark was 10 000 iterations which is the current load I can expect on the application over the course of an hour i.e.
12 000 selects
10 000 inserts
10 000 updates
4 000 deletes
This took a total of 93 seconds producing just over 100 iterations per second.

The second test to really put the application and environment to the test was to run 100 000 iterations. This test completed in roughly 15 minutes producing around 110 iterations per second. This is about 10x the load we’d expect to see during the course of one hour but it is nice to see the setup has the ability to grow quite a bit before we need more hardware. 

I am currently working on setting up a third test which will run 100 000 iterations every hour for 10 hours producing 1 millions rows of data.

---

Possibly related posts: (automatically generated)

·         I’m half way to being MySQL certified

·         Oracle buying Sun, therefore adquiring MySQL!!!!!

·         Tips & Tricks from MySQL Experts

 

 출처 : http://bieg.wordpress.com/2008/10/06/mysql-clustering-config/