How to Choose the SSD Size for a Firebolt Engine

1

Introduction

Selecting the appropriate SSD size for a Firebolt engine is crucial for optimizing both performance and cost efficiency. This guide will help you understand the factors to consider and provide a straightforward approach to determine the right SSD size for your engine. By the end of this article, you will know how to assess your data storage needs and configure your Firebolt engine accordingly.

TL;DR

  • Understand your data volume.

  • Calculate the SSD size.

  • Choose an appropriate SSD Size.

  • Review and adjust.

Step-by-step guide

All the example SQL code uses the Ultra Fast Gaming data set. To familiarize yourself with this data set, visit this link.

Step 1: Assess Your Data Volume

Understanding your current data volume is the first step.

Example SQL code:

-- Calculate the current size of all the DIMENSION and FACT tables in a DB, also possible to filter for specific tables like the 'games' table"`
SELECT 
sum(case when type = 'FACT' then compressed_bytes else 0 end)/1024.0/1024.0/1024.0 AS fact_tables_total_size_gb,
sum(case when type = 'DIMENTION' then compressed_bytes else 0 end)/1024.0/1024.0/1024.0 AS dimension_tables_total_size_gb
FROM information_schema.tables
WHERE table_schema = 'public'
-- AND table_name IN ('games')
group by all;

-- Calculate the current size of all the aggregating indexes in a DB
select 
  sum(index_compressed_size)/1024.0/1024.0/1024.0 AS aggregating_indexes_total_size_gb
from information_Schema.indexes
where index_type = 'aggregating';

Step 2: Calculate the SSD size

Since FACT tables and AGGREGATING INDEXES stored in the cache are distributed across all nodes, and DIMENSION tables are fully stored on each node, you need to ensure that the size of a single node is at least the total size of the DIMENSION , FACT tables and AGGREGATING INDEXES. If you plan to use multiple nodes, you should sum the size of all DIMENSION tables and divide the total size of the FACT tables and AGGREGATING INDEXES by the number of nodes.

We need to divide by 0.8 to ensure we have enough SSD space before cache eviction kicks in. We recommend even taking a safety margin and dividing by 0.6, which will leave room for additional data in the cache without triggering eviction.

The formula to calculate the SSD size of a node in the engine is:

((fact_tables_total_size_gb + aggregating_indexes_total_size_gb / number_of_nodes) + dimension_tables_total_size_gb)/0.6

number_of_nodes parameter is for your discretion and can be changed, depending on your instance type and cost preferences.

Step 3: Choose an Appropriate SSD Size

Based on your data volume, choose an SSD size that balances performance and cost. Firebolt offers various SSD sizes to suit different needs. Here you can find the list of instance types and their SSD size: Initial Sizing.

Step 4: Review and Adjust

Option 1: SHOW CACHE command

Periodically review your storage usage with the SHOW CACHE command. Adjust the SSD size as necessary to ensure optimal performance and cost efficiency.

  • If the SHOW CACHE result is close to 80%, you should consider adding more nodes or increasing the instance type.

  • If the SHOW CACHE result is very low and you are using more than one node or a node type greater than S, consider reducing the number of nodes or downgrading the instance type.

Option 2: engine_metrics_history table

A more advanced way to estimate the amount of cache required for your use-case is by querying information_schema.engine_metrics_history table.The information under this table allows you to see the total cache consumption over time and estimate when you will need to increase the cache amount of the engine according to your usage (aka add more nodes or upgrade the instance type).The following query will present the total amount of data filling the cache each day, and with that, you can calculate/identify a consistent growth pattern of the cache and estimate when it will reach or approach 80%:

SELECT event_time::date, MAX(disk_used)
FROM information_schema.engine_metrics_history
GROUP BY ALL
ORDER BY 1 DESC;

For example, if you see that the cache fills up by 5% each week, and currently the cache is filled by 60%, it means that in about about 4 weeks you will need to add more disk, or take into account that cache eviction may come into play and result in slower queries.