Table memory can only be freed back to the system when large chunks of data is deleted. Small deletes usually do not free the memory back to the system, as the memory is still available to SingleStore DB for additional writes. This is due to the structuring of the in-memory indexes where the chunks can only be freed if they are completely empty, that is when every row in the chunk has been deleted. It is difficult to quantify the amount of data that needs to be deleted since it depends on number of factors like the number of rows in one of these chunks depends on the table schema, whether or not a chunk gets completely emptied depends on the exact pattern of inserts and deletes, and so on. This enables optimization for improved efficiency and performance.
Currently, there is no method or allocator to track the ‘real memory use’ versus ‘free memory but still allocated to SingleStore DB’. However, table memory can be freed by running commands like OPTIMIZE TABLE, TRUNCATE, DROP TABLE, and DELETE.
For example, the OPTIMIZE TABLE..FULL command moves rows from partially-full chunks of memory in order to consolidate the data into full and empty chunks, thus allowing the newly-created empty chunks to be freed back to the system. The ‘used by SingleStore DB’ memory and the free system memory is as accurate as possible, and can be referred to know the amount of free memory available on the hosts. However, it has its own limitations like it uses a lot of CPU and is a time consuming process.
