Configuring for large databases in MySQL Cluster

Tweet 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:

• DataMemory
• IndexMemory


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=50
• IndexMemory=


(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).