Cluster Performance Tuning

The 3rd of November I will have a webinar on Cluster Performance Tuning.

For more information:

http://www.mysql.com/news-and-events/web-seminars/display-453.html

In addition to what is mentioned in the Agenda I will also present numbers on typical operations, how to design requests, explain how the optimizer works with MySQL Cluster etc etc.

See you there!

-johan

MySQL Cluster 7.0.8a and 6.3.27a released (source)

Fixes regression - bug 47844.

Configurator and Sandbox scripts are updated to use this version.

If you build from source then you can upgrade using these scripts:
Upgrade script from 7.0.7 -> 7.0.8a is here.
Upgrade script from 7.0.8 -> 7.0.8a is here.
Upgrade script from 6.3.26-> 6.3.27a is here.

Follow the instructions in this blog post!

MySQL Cluster 7.0.8/6.3.27 - serious regression!!!

A regression in MySQL Cluster 7.0.8 and 6.3.27 has caused them to be removed from download.

Problem:
If you have a mysql server with higher node id than 49, then it will cause cluster to crash.

An updated version of MySQL Cluster 7.0.8, called 7.0.8a will be released very soon.

The Configurator has been reverted back to use 7.0.7 and 6.3.26.

Configuring for large databases in MySQL Cluster

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