When calculating the storage requirements of in-memory tables, the size of each entry is the combined size of the data of the table plus three memory pointers (4 bytes each in 32-bit operating systems or 8 bytes each in 64-bit operating systems) of overhead per row. Additionally, there is overhead of two bytes per row and two bytes per each column of the row. You do not need to add 1 byte per column to take into account the length indicator; that is included in the 2 bytes per row.
In addition, the in-memory tables can have indexes, which are populated upon server startup. Each index entry contains the data of the columns defined in the index. Additionally, each index entry contains up to eight memory pointers. A copy of the primary key is NOT required for an in-memory index.
Furthermore, there is some other overhead that depends on the actual data values of the index. This is a percentage of the data size of the index. An exact value cannot be given exactly because it depends on the key value distribution, but the multiplier ranges between 1.0 and 2.0.
Additionally, the index structure itself needs an average of 4 bytes per index entry (that is, per row).
For the above example table and index, you first create an in-memory table as follows:
CREATE TABLE subscriber ( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(50), salary FLOAT) STORE MEMORY;
Then you create a secondary index as follows:
CREATE INDEX subscriber_idx_name ON subscriber (name);
You can then estimate the memory consumption in a 32-bit operating system as follows:
rows * 1.3 x ( ((3 x 4) + 2 + (4 + 2) + (50+5+2) + (8+2)) // Size of data in table + ((8 x 4) + 4 + 1.2 x 4) // Size of the primary key index + ((8 x 4) + 4 + 1.2 x (50+5))) // Size of the secondary index. )
((3 x 4) + 2 + (4 + 2)+(50+5+2)+(8+2)) // Size of data in the table | | | | | | | | | | | | | | | | | | | -> 2 bytes per col | | | | | | | | ---> 8 bytes for FLOAT (Salary) | | | | | | | -------> 2 bytes per col | | | | | | ---------> 5 bytes overhead per VARCHAR | | | | | ------------> 50 bytes for VARCHAR(50) | | | | ----------------> 2 bytes per col | | | --------------------> 4 bytes for INT (ID) | | -------------------------> 2 bytes per row | ------------------------------> pointer size (4 bytes on 32bit OS) ----------------------------------> 3 pointers
((8 x 4) + 4 + 1.2 x 4) // Size of the primary key index | | | | | | | | | ---------------> 4 bytes for INT | | | ---------------------> 1.2 index value distribution factor | | -------------------------> 4 bytes per index entry | ------------------------------> pointer size (4 bytes on 32bit OS) ----------------------------------> 8: up to 8 pointers
((8 x 4) + 4 + 1.2 x (50+5)) // Size of the secondary index | | | | | | | | | | | -----------> 4 bytes for VARCHAR overhead | | | | --------------> 50 bytes for VARCHAR(50) | | | ---------------------> 1.2 index value distribution factor | | -------------------------> 4 bytes per index entry | ------------------------------> pointer size (4 bytes on 32bit OS) ----------------------------------> 8 up to 8 pointers
In a 64-bit operating system, use a memory pointer size of 8 bytes rather than 4 bytes.
The factor 1.2 in the above estimate is the “TRIE index value distribution factor” whose exact value depends on the actual values of the indexed column. Its value is typically between 1 and 2. With random value distribution, the value is closer to 2.0. With sequential value distribution, it is closer to 1.0. The 4 bytes is the data overhead needed by an index entry on average.
The factor of 1.3 is to take into account the internal overhead of the memory allocator.
Note Indexes of main memory tables are created dynamically each time the server starts. The in-memory indexes are never written to disk and therefore they do not occupy any disk space. However, the tables themselves are written to disk during checkpoints and when the server shuts down, so the total amount of disk space that you have must be enough to store both the disk-based tables and the in-memory tables.