MySQL Cluster is pretty fast. The reason for this is, that it is completly 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...
For calculating or estimating how much Memory you need, you have several different possibilites:
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 anaylze the memory map a little more in detail we see that the majority of the memory is allocated in big junk and some little junks and only less than 10 Mbyte is used for binaries and libraries etc.
# ./mem_map.pl 9214 One big junk rw-p 228428 Kbyte One big junk rw-p 22088 Kbyte One big junk rw-p 15844 Kbyte One big junk rw-p 9396 Kbyte Serveral different small junks rw-p 7328 Kbyte Serveral different small junks ---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 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.
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:
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 type | size |
|---|---|
| VARCHAR(n) and VARBINARY(n): | n + 2 rounded up to next 4 byte boundary. In 5.1 VARxxx has dynamic lengt. 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, othewise: 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.
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
Every UK creates a new table with the UK attribute as PK and the PK as an attribute.
Every Ordered Index has a size of 16 bytes (wrong in docu!)
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
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;
|
...
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 follwing 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 |
Will follow soon...
MySQL provides a skript for determine MySQL Cluster memory: ndb_size.pl
There is an offical and a not yet offical version of this tool available:
To run this scrip you need the following prerequisits:
cd ~/tmp cp $basedir/share/mysql/ndb_size.tmpl . # ndb_size.pl foodmart localhost root > foodmart.html |
The output can be seen here.
| Parameter | 4.1 | 5.0 | 5.1 |
|---|---|---|---|
| DataMemory (kb) | 38624 | 38624 | 37088 |
| IndexMemory (kb) | 53816 | 44840 | 44840 |
Download here: ndb_size_new.pl
To run this scrip you need the following prerequisits:
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.
| Parameter | Default | 4.1 | 5.0 | 5.1 |
|---|---|---|---|---|
| DataMemory (KB) | 81920 | 51072 | 51072 | 48704 |
| IndexMemory (KB) | 18432 | 10952 | 5456 | 5456 |