If you need to create a big database into MySQL Cluster with:
- A lot of tables indexes, columns, and tables
- A lot of records
there are a few things to think about:
- If a table has > ~90M records, you have to create the table with
MAX_ROWS=<amount of records in table anticipating growth>:
CREATE TABLE t1(...) ENGINE=ndbcluster MAX_ROWS=200000000;
This way the data node will allocate more partitions for the table, since there is a limitation in how many records that can be stored in one partition (and the limit is around 90M records).
- Many tables / table objects --> Make sure you increase
MaxNoOfTables (kernel limit is 20320 tables). This creates a table object pool of size MaxNoOfTables.
- Each table you create will use one table object.
- Each unique index you create will use one table object
- Each ordered index you create will use one table object
- Each BLOB/TEXT attribute will use one table object.
For example if you create the following table:
CREATE TABLE `t1` (
`a` int(11) NOT NULL DEFAULT '0',
`b` char(32) DEFAULT NULL,
`c` blob,
`d` bigint(20) DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `b` (`b`),
KEY `d` (`d`)
) ENGINE=ndbcluster;
The table will require table objects as follows:
- PRIMARY KEY (there is an ordered index on the PK): 1 table objects
- UNIQUE KEY (unique hash index + ordered index): 1 + 1 table objects
- BLOB: 1 table object
- Actual Table: 1 table object
- IN TOTAL = 5 table objects
If you had specified PRIMARY KEY USING HASH, and UNIQUE KEY USING HASH, then you can save two table objects, one for each ordered index.
- Many UNIQUE indexes --> Increase
MaxNoOfUniqueHashIndexes
- Many ordered indexes --> Increase
MaxNoOfOrderedIndexes - Many attributes - -> Increase
MaxNoOfAttributes (should be sum of number of columns all tables and add 3-10% for spare).
Also you would probably want to increase:
If you have a lot of BLOB/TEXT columns or have many client apps/users connecting to one MySQL Server:
SendBufferMemory=8M
ReceiveBufferMemory=8M
If you get "error 306" then you should increase
StringMemory=25 to StringMemory=50IndexMemory=
(Note with 7.0.8 i can create 16000+ tables with 128 columns in each with
StringMemory=25).
This page will be updated if I find other things.
If you still have problems - let us/me know, e.g, on the
cluster mailing list!
The
Configurator has these parameters specified already in the config.ini so it is a small thing to change (just run the ./rolling-restart.sh script).
