How Much Memory Do I Need?
To get the most value out of your investment it is extremely important to size memory appropriately for a Vector deployment. Insufficient memory allocated to Vector may force you to enable spill to disk for various operations, in which case those queries that spill to disk may see significant performance degradation over those that do not.
Your server must have enough memory to support the Operating System (OS), Vector and any other applications running on the server. Whilst the OS can deal with insufficient memory by swapping memory to disk, this must be avoided as it will cause a severe slowdown of the system and result in poor and unpredictable performance.
Vector uses two memory pools:
• Execution memory: the execution memory pool should be large enough to support concurrent hash joins, aggregations and sorts against your biggest tables to prevent large queries from spilling to disk. Also, if you plan to define secondary indexes prepare for the unique entries in the indexed data to reside uncompressed in execution memory at all times plus an overhead for every column value.
• Column buffer manager (CBM) memory: a portion of main memory that is allocated to store data (compressed).
These memory pools are assigned per database and cannot be shared between databases. If you plan to run multiple databases concurrently then you must ensure to divide the available resources--memory in this case--between the databases that will run concurrently and make sure that the total memory consumption does not exceed the total amount of available RAM. Consider using multiple schemas instead of multiple databases to allow maximum memory resource utilization across multiple applications.
To identify how much memory is needed, identify how much memory you need for every pool (as discussed below), per database if you plan to run multiple databases concurrently, then add at least 2 GB for the OS and memory for any other applications you plan to run on the same server.
To simplify system sizing and to achieve the most predictable performance from Vector, you should dedicate your server to run Vector.
Realistically, an environment to run Vector for more than very basic testing should be configured with at least 8 GB of RAM.
Considering that multiple factors impact memory consumption, including the data size that is frequently accessed, the complexity of the queries, and query concurrency, there is no simple formula to determine the total amount of memory that will work well for your application. For an extremely rough estimate, consider the uncompressed total amount of source data you store in Vector divided by 10. For example, for a database that stores 5 TB of source data start by planning for 512 GB of RAM and adjust as necessary. Smaller databases will likely need more memory than one tenth of the source data size.