MySQL cluster DataMemory and DiskPageBufferMemory parameters

configurationMySQLmysql-clusterndbcluster

I want to setup a MySQL cluster.

The amount of data is more than double the amount of RAM.
I have 3 Data Nodes each with 8GB RAM.

QUESTIONS

What is a good rule for the DataMemory and DiskPageBufferMemory parameters?
Are 3GB DiskPageBufferMemory and 2GB DataMemory good values?

Best Answer

DISCLAIMER : Not SCMCDBA Certified

If you plan to hold data in memory, you need to

increase RAM on all data nodes to 16GB
increase the DataMemory to 6GB
increase the number of SQL nodes to 3 (Makes SQLNodes to DataNodes 1:1)

If you cannot make these changes, then you should offload data to local storage

MySQL Cluster -- Storing table data on data nodes

Here is one of the best answers from Frazer Clement : MySQL Cluster RAM requirement

He states in his answer

When configuring DataMemory and IndexMemory, it is best to limit yourself to somewhat less than the physical memory in each system. Some physical memory should be reserved for the OS and other processes.

There are three MySQL Cluster Gurus in this forum

Checks out their answers !!!

Related Solutions

Mysql – Setting up MySQL Cluster on Two Systems

Your management configuration seems fine to me. It sounds like ndb_mgmd didn't quite die or there is a problem with the contents in the /var/lib/mysql-cluster directory on the management server.

You might also want to make sure that ndb_mgmd isn't starting automatically, like through some startup script in /etc/init.d/.

To try and resolve the issue, I'd start with these steps first.

Since you are just getting started and don't have any real data yet.

Kill any left over ndb_mgmd processes with: killall ndb_mgmd or by rebooting if killall doesn't help.
Completely delete everything within /var/lib/mysql-cluster: rm -Rf /var/lib/mysql-cluster/*
Start the cluster process again: ndb_mgmd -f /etc/config.ini --initial
Verify that ndb_mgmd is listening with netstat

$> netstat -tlpn

Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:1186 0.0.0.0:* LISTEN 3251/ndb_mgmd

Connect to management node

$> ndb_mgm
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration


[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.9.26  (mysql-5.1.61 ndb-7.1.22)

Update

Here is a Quick Start guide from Oracle with all nodes, management, mysql and data running on one server.

http://downloads.mysql.com/tutorials/cluster/GetMySQLClusterRunning-LINUX.pdf

MySQL cluster fast inserts but slow selects

It seems that for solving your problem you chose the wrong tool.

MySQL Cluster is good when you do mostly key based lookups from multiple threads from memory based tables. Disk based tables may be useful when you have rarely read data. Or your work dataset is small portion of the table which should fit into memory cache whose size is defined by DiskPageBufferMemory config variable.

If your queries need many range or full scans - MySQL Cluster is slow even on physical machines. That is because such queries need a lot of data exchanges between data nodes. Try pinging between your data nodes. And for range scan data nodes may need to exchange hundreds and thousands of such messages.

Also MySQL previously stated that for data nodes your should use physical machines and have good interconnect for data node traffic. I doubt that this recommendation is no more valid nowadays.

And I think you should try cleaning up your config. For testing most of those things hardly changes anything and some setting may be slowing down things. Try such simplified NDBD_DEFAULT section:

[NDBD DEFAULT]
NoOfReplicas=1
DataMemory=1536M
IndexMemory=2560M

LockPagesInMainMemory=2
ODirect=1