DiskPageBufferMemory

Tweet 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 innodb, then you know the innodb_buffer_pool. This is basically the same. You need to set it big so that you cache as much as possible of the active (most frequently used) data set on disk.

We need to know what the data node is using memory for so that we can set the DiskPageBufferMemory appropriately:

• Internal buffers (RedoBuffer, send buffers): 900MB (if you use the config files from the config tool)
• OS will use ~1GB (this really too much and can be trimmed)
• DataMemory: X MB (derived from the dimensioning tool), say 2GB (for the example below)
  
• IndexMemory: Y MB (derived from e.g the dimensioning tool), say 256MB (for the example below)
  

If I have 8GB of RAM in my computer, I would set the DiskPageBufferMemory to:

DiskPageBufferMemory = 0.8 x [ 8GB - 1GB (OS) -900MB (internal buffers) - 2GB (DataMemory) - 256MB (IndexMemory) ] = ~3072MB

I take 0.8x just to leave some slack if I need to reconfig som other buffer later. After a while, when the system is tuned in I can then chose to use those extra MB to extend the DiskPageBufferMemory.

And yes, the statistics to monitor the DiskPageBufferMemory (and all other params as well) are missing. But behold, it will come!

And let me know if you have tested disk data with SSD devices!