Thesql cluster table full error

MySQLmysql-clusterndbcluster

I installed a new mysql cluster on windows (for evaluation). I created a table using command below. After I insert just over 1 million records into the table it gives me the "table datadump is full" exception. I have plenty of hard disk space and ram free, and 1 million records is well below the max number of rows for the table. Also the MySql RAm usage has not increased during inserts. I am assuming that MySql has an internal cache limit for the table that was hit. is there a config.ini change I can make to disable caching of rows in cluster table or a modification to the create table script to do the same?

create table datadump (name varchar(30) not null) engine=ndb;

Best Answer

Here is the MySQL Documentation FAQ on MySQL Cluster that addresses your question

The cause is very likely to be that your setup does not provide sufficient RAM for all table data and all indexes, including the primary key required by the NDB storage engine and automatically created in the event that the table definition does not include the definition of a primary key.

It is also worth noting that all data nodes should have the same amount of RAM, since no data node in a cluster can use more memory than the least amount available to any individual data node. For example, if there are four computers hosting Cluster data nodes, and three of these have 3GB of RAM available to store Cluster data while the remaining data node has only 1GB RAM, then each data node can devote at most 1GB to MySQL Cluster data and indexes.

In some cases it is possible to get Table is full errors in MySQL client applications even when ndb_mgm -e "ALL REPORT MEMORYUSAGE" shows significant free DataMemory. You can force NDB to create extra partitions for MySQL Cluster tables and thus have more memory available for hash indexes by using the MAX_ROWS option for CREATE TABLE. In general, setting MAX_ROWS to twice the number of rows that you expect to store in the table should be sufficient.

For similar reasons, you can also sometimes encounter problems with data node restarts on nodes that are heavily loaded with data. In MySQL Cluster NDB 7.1 and later, the addition of the MinFreePct parameter helps with this issue by reserving a portion (5% by default) of DataMemory and IndexMemory for use in restarts. This reserved memory is not available for storing NDB tables or data.

Based on the 3rd paragraph, you should run the create table with max_rows:

create table datadump (name varchar(30) not null) engine=ndb max_rows=2000000;
Related Question