From MySQL Cluster 7.1.9 (not yet released) it is possible to get better stats on disk data tables. In fact, the statistics makes it possible to tune the DiskPageBufferMemory parameter (similar to innodb_bufferpool), in order to avoid disk seeks. It is much (understatement) faster to fetch data from the DiskPageBufferMemory than disk.

Here is an example/tutorial how to use this information and how to check the hit ratio of the DiskPageBufferMemory. Next time, I will explain about other counters you can get from ndbinfo.diskpagebuffer.

Finally, no more educated guesswork is needed.

Let's take an example.

I have a table t1 with 650000 record

CREATE TABLE `t1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data1` varchar(512) DEFAULT NULL,
`data2` varchar(512) DEFAULT NULL,
PRIMARY KEY (`id`)
) …

Here is a summary about Disk Data tables in MySQL Cluster.

Data node configuration:

• SharedGlobalMemory=384M
  RAM from the shared global memory is used for the UNDO_BUFFER when you create the log file group.
  In the configuration generated by severalnines.com/config then you have to uncomment the SharedGlobalMemory in mysqlcluster-63/cluster/config/config.ini before you start the cluster.
  
• DiskPageBufferMemory=3072MB
  If you are relying a lot on the disk data, we recommend to set this to as much as possible.
  In the configuration generated by severalnines.com/config then you have to uncomment the DiskPageBufferMemory in …

In a previous blog "disk data counter" I did a couple of measurements how to configure the disk data and what we can expect.

Now I have redone the insert test to put as much load as possible with respect to the disk sub system.

1. Insert 5M records a' 4096B (1 threads, batches of five):
6.3.19: 286 sec (3721.12 QPS)

2. Insert 5M records a' 4096B (5 threads, batches of five):
6.3.19: 286 sec (8315.36 QPS)

At 2) the IO sub-system is quite loaded (98.2%) and I get (periodically,when the DiskPageBufferMemory is checkpointed):

"REDO buffers overloaded, consult online manual (increase RedoBuffer) - code=1221" (this does not matter, as i retry my transactions in the application).
I could increase the RedoBuffer (it is set to 32M), but there is no point as if I …

Here is a discussion about some caveats with allocation and deallocation of extents in MySQL Cluster (disk data).

Let's assume we have created a table space with one datafile having 5 extents (i.e the datafile is 5MB in since, since the size of each extent is 1MB).

Using the information_schema we can figure out how many free extents we have by executing the following query:

mysql> select free_extents, total_extents from information_schema.files where file_type='datafile';
+--------------+---------------+
| free_extents | total_extents |
+--------------+---------------+
| 5 | 5 |
| 5 | 5 |
+--------------+---------------+

The figure below shows a number of extents that are all free.
…

I have measured the time it takes to do some operations with Cluster (6.3.19, which is about to be released very soon - you should upgrade) with respect to the disk, like starting it, creating table spaces etc.

Below all of this you can find the setup I used.

Initial start of Cluster (40GB of redo log files):
6.3.19: 3min 27sec

Create a 20GB undo file:
6.3.19: 6min 17sec

Create a 128MB data file for the tablespace:
6.3.19: ~3 sec

Insert 1M records a' 4096B (1 thread, batches of five):
6.3.19: 286 sec (3721.12 QPS)
(we can probably provision faster with bigger batches or more threads)

I then provisioned another 4M records (total of 5M records in DB).

Evil test: 100K Random reads (read 4096B) (5M records in DB):
6.3.19: 1290.42QPS (20 threads, io util is ~90% so we are almost completely io bound). So …

I get a few questions how to set DiskPageBufferMemory.

Before discussing that let's recap a few things:

• Non-indexed attributes (or explicitly defined in CREATE TABLE..) are stored on disk in table spaces.
  
• Indexed attributes are stored in DataMemory
• The hash of the Primary Key is stored in the IndexMemory

Determining how much IndexMemory/DataMemory/Tablespaces that are needed, you can use the dimensioning toolkit.

Now you can setup Undobuffers etc according to my post on disk data a while back.

But what about the DiskPageBufferMemory?! If you have used …