You are here

Home

MySQL Cluster memory sizing

MySQL Cluster is pretty fast. The reason for this is, that it is completely memory based. Nowadays memory is still, in contrary to disk, limited to your systems. Thus, before installing a MySQL Cluster you have to calculate the amount of memory you need.

To say it in advance: You should consider to only use 64-bit Linux system with huge amount (4 - 64 GB) of RAM!

In release 5.1 MySQL Cluster became disk based. Now you have the possibility to swap out some data to disk. How much it is we will probably see a little further down...

Calculating or estimating

For calculating or estimating how much Memory you need, you have several different possibilities:

  • You can do it by hand.
  • This OO calc spread sheet helps you.
  • You can have it much easier by using ndb_size.pl (or the newer not yet official released version --> link).
  • Or you can extrapolate from a consisting data set.

Memory usage

First we want to see where memory is used in Cluster. When we do a ps we know how much memory our cluster process allocates:

# ps aux | grep ndb
USER    PID     VSZ     RSS  COMMAND
mysql  8788   15556    1044  ndbd -c master
mysql  9214  292328  292204  ndbd -c master
mysql  9193   19652    1920  ndb_mgmd
mysql  8823   15556    1100  ndbd -c master
mysql  9256  292328  292204  ndbd -c master

In total each node allocates about 285 Mbyte of RAM in total. when we analyse the memory map (mem_map.pl) a little more in detail we see that the majority of the memory is allocated in big chunk and some little chunks and only less than 10 Mbyte is used for binaries and libraries etc.

# ./mem_map.pl 9214

One big chunk                    rw-p   228428 Kbyte
One big chunk                    rw-p    22088 Kbyte
One big chunk                    rw-p    15844 Kbyte
One big chunk                    rw-p     9396 Kbyte
Several different small chunks   rw-p     7328 Kbyte
Several different small chunks   ---p      124 Kbyte

/lib/...                         r-xp     1684 Kbyte
/lib/...                         rw-p       60 Kbyte
ndbd                             r-xp     2464 Kbyte
ndbd                             rw-p     1136 Kbyte
[heap]                           rw-p     3688 Kbyte
[stack]                          rw-p       88 Kbyte
[vdso]                           ---p        4 Kbyte

share   =     4148 Kbyte
private =   288184 Kbyte
total   =   292332 Kbyte (1.42 % shareable)

When we consider the memory sizing from config.ini we get roughly the following memory distribution:

Total RAM = SendBufferMemory                2 Mbyte
per node  + TransactionBufferMemory         1 Mbyte (default)
          + DataMemory                     80 Mbyte (see calculation)
          + IndexMemory                     8 Mbyte (see calculation)
          + StringMemory                    5 Mbyte (default)
          + RedoBuffer                     16 Mbyte (16 - 64 Mbyte)
          + UndoDataBuffer                 24 Mbyte
          + UndoIndexBuffer                12 Mbyte
          + SharedGlobalMemory             20 Mbyte (default, 5.1)
          + ConcurrentTransaction memory    ? Mbyte (unknown)
          + ConcurrentOperation memory      2 Mbyte (= MaxNoOfConcurrentOperations * 1 kbyte)
          + UniqueHashIndex memory          2 Mbyte (= MaxNoOfUniqueHashIndexes * 15 kbyte)
          + OrderedIndex memory             1 Mbyte (= MaxNoOfOrderedIndexes * 10 kybte)
          + Table object buffer            10 Mbyte (= MaxNoOfTables * 20 kbyte)
          + LongMessageBuffer               1 Mbyte (default)
          + Attribute buffer                1 Mbyte (= MaxNoOfAttributes * 200 byte)
          + BackupDataBufferSize            2 Mbyte (default)
          + BackupLogBufferSize             2 Mbyte (default)
          + DiskPageBufferMemory           64 Mbyte (default)
          + binary + so + heap + stack +    9 Mbyte (see above)
                                          ---
                                          262 Mbyte

We know what for 262 Mbyte of RAM are allocated from a total of 285 Mbyte (23 Mbyte (8%) are still missing!!!)

What we want to calculate now is the DataMemory and IndexMemory usage.

MySQL Cluster memory sizing by hand (DataMemory and IndexMemory)

The memory allocated by DataMemory is used to store both the actual records and ordered indexes, the IndexMemory contains the hash indexes (primary keys).

So let us look at the different objects:

Table

A record can not be more than 8052 byte long.
A table has always an record header of 40 bytes. Every table must have a primary key (PK). Otherwise MySQL Cluster creates one with size of 8 bytes.
Consider to store TINYINTs as BIT fields.

data typesize
VARCHAR(n) and VARBINARY(n):n + 2 rounded up to next 4 byte boundary. In 5.1 VARxxx has dynamic length. If you have variable size records and additional 4 bytes of OH for a pointer from the fixes size to the variable size is added.
CHAR(n) and BINARY(n):n rounded up to the next 4 byte boundary.
ENUM:1 or 2 bytes
SET:1 to 8 bytes
BLOB/TEXT(n):n < 256 then n, otherwise: 256 + (n-256) rounded up to the next multiple of 2000. For each BLOB attribute an extra table is used to store the BLOB overflow data.
TINYINT - INT:4 bytes
BIGINT:8 bytes
FLOAT:4 bytes
DOUBLE:8 byte
BIT:4 (- 32bit) or 8 byte (-64 bit) are stored in record header
DECIMAL:Rougly 1/2 byte per digit rounded up to next 4 byte boundary.
DATE, TIMESTAMP, TIME, YEAR:4 byte
DATETIME:8 byte

With 5.1 it is possible to store the non-indexed columns on disk. In a Disk Data table, the first 256 bytes of a TEXT or BLOB column are stored in memory; only the remainder is stored on disk.

Primary Key (PK)

Every MySQL cluster table must have a primary key (PK). If you do NOT create onw, MySQL Cluster creates one for you with a size of 8 bytes. Every PK causes a hash index (HI) which has a size of 20 bytes. HI are stored in index memory while all other information are stored in data memory.
A PK also creates an ordered index (OI) unless you create it with USING HASH

Unique Key (UK)

Every UK creates a new table with the UK attribute as PK and the PK as an attribute.

Ordered Index

Every Ordered Index has a size of 16 bytes (wrong in documentation!)

Convert this per page

1 page has 32 k - 128 byte Page Overhead (POH)
rows per page = TRUNC((32 kbyte - 128 byte) / rows or index size)
pages = rows / rows per page
Data and Index memory per Node = (DataMemory blocks + IndexMemory blocks) * #Replicas / #Nodes

Example

CREATE TABLE object (
    id            INT NOT NULL AUTO_INCREMENT
  , name          VARCHAR(29) NOT NULL
  , ts            DATETIME NOT NULL
  , version       SMALLINT
  , object_number MEDIUMINT
  , data          BLOB
  , PRIMARY KEY (id)
  , UNIQUE INDEX (version, object_number)
  , INDEX (ts)
) ENGINE = NDB;

Tools which support us

After creation of the table above we can see what kind of objects were created:

# ndb_show_tables | sort

id    type                 state    logging database     schema   name
...
71    UserTable            Online   Yes     test         def      object
72    UserTable            Online   Yes     test         def      NDB$BLOB_71_5
73    OrderedIndex         Online   No      sys          def      PRIMARY
74    OrderedIndex         Online   No      sys          def      version
75    UniqueHashIndex      Online   Yes     sys          def      version$unique
76    OrderedIndex         Online   No      sys          def      ts

Where is the table for the unique key and why is the PK hash not shown???

With the following statement we see at least the UK:

# ndb_desc -u object -d test

-- object --
Version: 1
Fragment type: 5
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 6
Number of primary keys: 1
Length of frm data: 381
Row Checksum: 1
Row GCI: 1
TableStatus: Retrieved

-- Attributes --
id Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
name Varchar(29;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY
ts Datetime NOT NULL AT=FIXED ST=MEMORY
version Smallint NULL AT=FIXED ST=MEMORY
object_number Mediumint NULL AT=FIXED ST=MEMORY
data Blob(256,2000;16) NULL AT=FIXED ST=MEMORY

-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
version$unique(version, object_number) - UniqueHashIndex
version(version, object_number) - OrderedIndex
PRIMARY(id) - OrderedIndex
ts(ts) - OrderedIndex

MySQL Cluster memory sizing by OO calc spread sheet

Will follow soon...

MySQL Cluster memory sizing by using ndb_size.pl

MySQL provides a script for determine MySQL Cluster memory: ndb_size.pl
There is an official and a not yet official version of this tool available:

ndb_size.pl

To run this scrip you need the following prerequisites:

  • Perl installed
  • DBD::MySQL installed
  • HTML:Template installed
  • ndb_size.tmpl copied to your present location
  • MySQL database to calculate from
cd ~/tmp
cp $basedir/share/mysql/ndb_size.tmpl .
# ndb_size.pl foodmart localhost root > foodmart.html

The output can be seen here.

Parameter4.15.05.1
DataMemory (kb)386243862437088
IndexMemory (kb)538164484044840

ndb_size_new.pl

Download here: ndb_size_new.pl
To run this scrip you need the following prerequisites:

  • Perl installed
  • DBD::MySQL installed
  • Class::MethodMaker installed
  • MySQL database to calculate from
cd ~/tmp
cp $basedir/share/mysql/ndb_size.tmpl .
# ndb_size.pl foodmart localhost root > foodmart.html

The new version could still be a little buggy...

The output can be seen here.

ParameterDefault4.15.05.1
DataMemory (KB)81920510725107248704
IndexMemory (KB)184321095254565456

MySQL Cluster memory sizing by extrapolating

The following simple method should help to estimate you the size:

[foodmart]> SHOW TABLE STATUS LIKE '%';

+------------------+--------+--------+----------------+-------------+--------------+
| Name             | Engine | Rows   | Avg_row_length | Data_length | Index_length |
+------------------+--------+--------+----------------+-------------+--------------+
| account          | MyISAM |     11 |             44 |         492 |         3072 |
| category         | MyISAM |      4 |             36 |         144 |         3072 |
| currency         | MyISAM |     72 |             26 |        1920 |         2048 |
| customer         | MyISAM |  10281 |            183 |     1889108 |       704512 |
| days             | MyISAM |      7 |             20 |         140 |         1024 |
| department       | MyISAM |     12 |             33 |         396 |         2048 |
| employee         | MyISAM |   1155 |            135 |      156628 |        55296 |
| expense_fact     | MyISAM |   2400 |             35 |       86016 |        77824 |
| inventory_fact   | MyISAM |  11352 |             43 |      488136 |       508928 |
| position         | MyISAM |     18 |             66 |        1200 |         2048 |
| product          | MyISAM |   1560 |            107 |      167540 |       116736 |
| product_class    | MyISAM |    110 |             53 |        5864 |         1024 |
| promotion        | MyISAM |   1864 |             59 |      111236 |        44032 |
| region           | MyISAM |    110 |             54 |        5996 |         1024 |
| reserve_employee | MyISAM |    143 |             77 |       11020 |        13312 |
| salary           | MyISAM |  21252 |             34 |      722568 |         1024 |
| sales_fact       | MyISAM | 269720 |             35 |     9440200 |     13753344 |
| store            | MyISAM |     25 |            156 |        3908 |         3072 |
| time_by_day      | MyISAM |    730 |             43 |       31932 |         1024 |
| warehouse        | MyISAM |     24 |            113 |        2712 |         1024 |
| warehouse_class  | MyISAM |      6 |             26 |         156 |         1024 |
+------------------+--------+--------+----------------+-------------+--------------+
                                                            12.5 Mb        14.4 Mb

As rule of thumb you can say, that a MySQL Cluster database uses:

  • 3 - 5 times the size of your MyISAM database.
  • 2 - 3 times the size of your InnoDB database.

This should be verified by the following commands:

[foodmart]> SHOW TABLE STATUS LIKE '%';
+------------------+------------+--------+----------------+-------------+--------------+---------+
| Name             | Engine     | Rows   | Avg_row_length | Data_length | Index_length | 32k blk |
+------------------+------------+--------+----------------+-------------+--------------+---------+
| account          | NDBCLUSTER |     11 |             28 |      786432 |            0 |      24 |
| category         | NDBCLUSTER |      4 |             12 |      786432 |            0 |      24 |
| currency         | NDBCLUSTER |     72 |             36 |      786432 |            0 |      24 |
| customer         | NDBCLUSTER |  10281 |             52 |     3014656 |            0 |      92 |
| days             | NDBCLUSTER |      7 |             20 |      786432 |            0 |      24 |
| department       | NDBCLUSTER |     12 |             12 |      786432 |            0 |      24 |
| employee         | NDBCLUSTER |   1155 |             52 |      786432 |            0 |      24 |
| expense_fact     | NDBCLUSTER |   2400 |             44 |      917504 |            0 |      25 |
| inventory_fact   | NDBCLUSTER |  11352 |             80 |      917504 |            0 |      25 |
| position         | NDBCLUSTER |     18 |             28 |      786432 |            0 |      24 |
| product          | NDBCLUSTER |   1560 |             92 |      917504 |            0 |      25 |
| product_class    | NDBCLUSTER |    110 |             24 |      786432 |            0 |      24 |
| promotion        | NDBCLUSTER |   1864 |             36 |      786432 |            0 |      24 |
| region           | NDBCLUSTER |    110 |             28 |      786432 |            0 |      24 |
| reserve_employee | NDBCLUSTER |    143 |             52 |      786432 |            0 |      24 |
| salary           | NDBCLUSTER |  21252 |             64 |     1441792 |            0 |      44 |
| sales_fact       | NDBCLUSTER | 269720 |             64 |    19988480 |            0 |     610 |
| store            | NDBCLUSTER |     25 |             84 |      262144 |            0 |       8 |
| time_by_day      | NDBCLUSTER |    730 |             44 |      229376 |            0 |       7 |
| warehouse        | NDBCLUSTER |     24 |             32 |      229376 |            0 |       7 |
| warehouse_class  | NDBCLUSTER |      6 |             24 |      229376 |            0 |       7 |
+------------------+------------+--------+----------------+-------------+--------------+---------+
                                                                34.8 Mb                     1114

And with cluster means:

ndb_mgm> ALL DUMP 1000;

2007-03-23 13:04:13 [MgmSrvr] INFO     -- Node 10: Data usage is 80%(2050 32K pages of total 2560)
2007-03-23 13:04:13 [MgmSrvr] INFO     -- Node 10: Index usage is 64%(681 8K pages of total 1056)

2007-03-23 13:04:14 [MgmSrvr] INFO     -- Node 12: Data usage is 80%(2050 32K pages of total 2560)
2007-03-23 13:04:14 [MgmSrvr] INFO     -- Node 12: Index usage is 64%(681 8K pages of total 1056)

Which gives a total of 64.0 Mbyte of DataMemory and 5.3 Mbyte of IndexMemory.

Finally we can say that none of the methods above works correctly in all dimensions and you always have to try it out...

MySQL Cluster redo log sizing

4 x time between LCP is the time we should base our calculation on.
Let us assume our DB is 10 Gbyte in size and we are writing with 10 Mbyte/s our LCP to disk on a 4 node cluster.
4 x (10 Gbyte * 2 Replicas / 4 Nodes) / 10 Mybte/s = 2048 s = 35 min
To calculate the redo log size we need to know now, how much traffic we will get on our cluster.
Le us assume our cluster has to carry 10 Mbyte/s write load.
10 Mbyte/s / 4 Nodes * 2 Replicas = 5 Mbyte/s REDO log * 2048 s = 10 Gbyte REDO log

tags: 
MySQL Cluster
memory
sizing