New answers tagged data-warehouse
0
What fills your disk are temporary files that store (perhaps intermediary) result sets. They are deleted as soon as the query ends.
Check for missing join conditions and examine the EXPLAIN output for large sorts and materialize nodes.
0
If your date key is a smart key (like 20200112 for today's date) don't join the date table
partition your fact tables by year, so you only read those tables
pre-aggregate your fact table, with the sums by year
use lots of SSD drives in RAID 10
2
Few Suggestions for yur setup
DB Setup
1) Use a temporary (staging) table to gather new records. Preferably you should range partition
this table with the interval of your finest aggregation (e.g. minute) using the insert timestamp. Insert timestamp contrary to the timestamp of the measurement (called sensor timestamp) is important to distinct - see the ...
2
The main problems I see:
The size of the table containing the detailed data. It sounds like it will take 2-3 terabytes per year
The ingestion speed of 2000 rows per second. Are you using SSD drives?
Purging 'old' data -- big DELETEs are a big cost.
so...
Do not store all the data in a database. You don't need it. Instead write it to a plain file if ...
Top 50 recent answers are included
