Search code examples
singlestore

How much memory should be reserved to store 100GB data

I have 5 leaf nodes with each node having setting 

maximum_memory = 51200m
maximum_table_memory = 40960m
So total leaf table memory = 200GB
and total  Memsql Memmory = 250 GB

I am trying to load 100GB data of TPCH but not able to do so. LineItem Table loading is failed. Its count is 0.

Below is the picture of status table of memsql ops. It varies between 90GB and 108GB for leaf Table memory

enter image description here

enter image description here

When I tried to load LINEItem table again whose size is 74GB, status tab shows memsql is consuming all available memory enter image description here

So even after setting more that double amount of memory (200GB), Memsql is still not able to load 100GB data. What is wrong here? Looks like I am missing on some obvious parameters.Please guide me for correct settings.

Solution

  • Storing data in a rowstore table generally requires more space than the original data size, because of additional space consumed by indexes, overhead, etc. This is true of MemSQL and other databases. To reduce space usage, consider removing some indexes in your schema, for example. Or you may also want to check that you're using the right datatypes - e.g. using bigint instead of int will waste space too.

    You can estimate how much space is needed based on how much space MemSQL is actually using for your data - from the info you posted above it looks like MemSQL is using about 200GB to store 74GB raw data, so that multiplies to ~270GB for 100GB raw data.

    For more detailed information on what MemSQL is using memory for, you can view an individual MemSQL leaf node in Ops through the Cluster page and see memory usage info.