Pivotal Knowledge Base

Follow

How to calculate the size of max_appendonly_tables in Greenplum

Environment

Pivotal Greenplum: 4.3.x

Purpose

The GUC max_appendonly_tables defines the maximum number of append-optimized relations that can be written to or loaded concurrently. This space is reserved against shared memory at a server start. Currently, there is no way to estimate how many units this GUC should be increased to fit a specific use case. Setting this GUC to very high values will cause a server startup failure. 

This article explains what is the size of memory (in bytes) that each unit of max_appendonly_tables reserve.

Cause

If an application is trying to open append-only tables concurrently and the number of concurrently open append-only tables crosses the threshold defined by max_appendonly_tables, it will encounter an error the one below:

ERROR: can't have more than 20000 different append-only tables open for writing data at the same time. if tables are heavily partitioned or if your workload requires, increase the value of max_appendonly_tables and retry (appendonlywriter.c:434) 

The calculation detailed here helps you in determining the approximate value by which you can increase max_appendonly_tables in order to avoid this error.

Implementation and Calculations

Note: All the units of memory are in bytes

  • A hash table is maintained in the shared memory for max_appendonly_tables, each entry of this table is of size 4104 bytes. Each entry of the table is of type AORelHashEntryData. The size of this data structure can be verified using the following method
Attach to primary postmaster using gdb

(gdb) p sizeof(AORelHashEntryData)
$3 = 4104

(gdb) p sizeof(AppendOnlyWriterData)
$2 = 4

When max_appendonly_tables is set to 10000

  • Amount of shared memory reserved by the GUC 
(gdb) p  hash_estimate_size((Size)MaxAppendOnlyTables, sizeof(AORelHashEntryData))
$4 = 41403272
  • Size of the hash table it needs is
/* The hash of append only relations */
    size = hash_estimate_size((Size)MaxAppendOnlyTables,
                              sizeof(AORelHashEntryData));

    /* The writer structure. */
    size = add_size(size, sizeof(AppendOnlyWriterData));

    /* safety margin */
    size = add_size(size, size / 10);
  • When max_appendonly_tables is set to 10000, the total size that this GUC reserves in the shared memory is 41403272 + 4+ 4140327.6 = 45543603.6 bytes

When max_appendonly_tables is set to 20000

  • Amount of shared memory reserved by the GUC 
(gdb) p hash_estimate_size((Size)MaxAppendOnlyTables, sizeof(AORelHashEntryData))
$3 = 82804384
  • Size of the hash table it needs is
/* The hash of append only relations */
    size = hash_estimate_size((Size)MaxAppendOnlyTables,
                              sizeof(AORelHashEntryData));

    /* The writer structure. */
    size = add_size(size, sizeof(AppendOnlyWriterData));

    /* safety margin */
    size = add_size(size, size / 10);
  • When max_appendonly_tables is set to 20000, the total size that this GUC reserves in the shared memory is 82804384 + 4 + ((82804384+4)/10) =91084826.8 bytes

When max_appendonly_tables was increased from 10000 to 20000, the shared memory it needed was increased from 41403272 to 82804384.

82804384 - 41403272 = 41401112 -> 41401112/4104 = 10087.9902534

Given the above facts, increasing max_appendonly_tables by 1 needs 4104 more bytes per primary/mirror postmaster.

Therefore if there are 10 segments (primary+mirror) on a server and max_appendonly_tables value is increased from 10000 to 11000, it will need additional shmax of (11000 - 10000) * 4104 * 10 = 41040000 bytes after hitting its maximum limit.

Comments

Powered by Zendesk