Piszę aplikację, która musi przechowywać i analizować duże ilości danych elektrycznych i temperaturowych.

Zasadniczo muszę przechowywać duże ilości godzinowych pomiarów zużycia energii elektrycznej przez ostatnie kilka lat i przez wiele lat, aby dotrzeć do dziesiątek tysięcy lokalizacji, a następnie przeanalizować dane w niezbyt skomplikowany sposób.

Informacje, które muszę przechowywać (na razie) to identyfikator lokalizacji, znacznik czasu (data i godzina), temperatura i zużycie energii elektrycznej.

Jeśli chodzi o ilość danych, które należy przechowywać, jest to przybliżone, ale coś w tym stylu: ponad
20 000 lokalizacji, 720 rekordów miesięcznie (pomiary godzinowe, około 720 godzin miesięcznie), 120 miesięcy (10 lat wstecz ) i wiele lat w przyszłość. Proste obliczenia dają następujące wyniki:

20 000 lokalizacji x 720 zapisów x 120 miesięcy (10 lat wstecz) = 1 728 000 000 zapisów .

Są to przeszłe rekordy, nowe rekordy będą importowane co miesiąc, więc jest to około 20 000 x 720 = 14 400 000 nowych rekordów miesięcznie .

Łączna liczba lokalizacji będzie również stale rosła.

Na wszystkich tych danych należy wykonać następujące operacje:

1. Pobierz dane dla określonej daty ORAZ okresu: wszystkie rekordy dla określonego identyfikatora lokalizacji między datami 01.01.2013 a 01.01.2017 oraz między 07:00 a 13:00.
2. Proste operacje matematyczne dla określonego zakresu ORAZ czasu, np. Temperatura MIN, MAX i AVG oraz zużycie energii elektrycznej dla określonego identyfikatora lokalizacji przez 5 lat od 07:00 do 13:00.

Dane będą zapisywane co miesiąc, ale będą odczytywane przez setki użytkowników (przynajmniej) stale, więc szybkość odczytu ma znacznie większe znaczenie.

Nie mam doświadczenia z bazami danych NoSQL, ale z tego, co zebrałem, są one najlepszym rozwiązaniem do zastosowania tutaj. Czytałem o najpopularniejszych bazach danych NoSQL, ale ponieważ są one całkiem różne i pozwalają na bardzo różną architekturę tabel, nie byłem w stanie zdecydować, która baza danych jest najlepsza do użycia.

Moimi głównymi wyborami były Cassandra i MongoDB, ale ponieważ mam bardzo ograniczoną wiedzę i nie mam prawdziwego doświadczenia, jeśli chodzi o duże dane i NoSQL, nie jestem bardzo pewien. Przeczytałem również, że PostreSQL dobrze radzi sobie z takimi ilościami danych.

Moje pytania są następujące:

1. Czy powinienem używać bazy danych NoSQL dla tak dużych ilości danych. Jeśli nie, mogę trzymać się MySQL?
2. Jakiej bazy danych powinienem użyć?
3. Czy powinienem przechowywać datę i godzinę w osobnych, indeksowanych (jeśli to możliwe) kolumnach, aby szybko pobierać i przetwarzać dane dla określonych okresów czasu i dat, czy też można to zrobić, przechowując znacznik czasu w jednej kolumnie?
4. Czy właściwe jest tutaj modelowanie danych szeregów czasowych, a jeśli nie, możesz podać mi wskazówki dotyczące dobrego projektu tabeli?

Dziękuję Ci.

Dokładnie to robię na co dzień, ale zamiast danych godzinowych korzystam z danych 5-minutowych. Codziennie pobieram około 200 milionów płyt, więc kwota, o której tu mówisz, nie stanowi problemu. Dane 5-minutowe mają rozmiar około 2 TB, a dane pogodowe sięgają 50 lat wstecz co godzinę według lokalizacji. Pozwól, że odpowiem na pytania na podstawie mojego doświadczenia:

1. Nie używaj do tego NoSQL. Dane są wysoce ustrukturyzowane i idealnie pasują do relacyjnej bazy danych.
2. Osobiście korzystam z SQL Server 2016 i nie mam problemów z zastosowaniem obliczeń dla tego wolumenu danych. Pierwotnie był on w instancji PostgreSQL, kiedy zaczynałem pracę, i nie mógł poradzić sobie z ilością danych, tak jak w małej instancji AWS.
3. Chciałbym bardzo polecić wydobycia część godzinny dzień i przechowywanie go oddzielić od samej daty. Uwierz mi, ucz się na własnych błędach!
4. Przechowuję większość danych na liście (DATA, CZAS, DATAPOINT_ID, WARTOŚĆ), ale nie w ten sposób ludzie będą chcieli interpretować dane. Przygotuj się na przerażające zapytania dotyczące danych i ogromną liczbę elementów przestawnych. Nie bój się stworzyć zdenormalizowanej tabeli dla zestawów wyników, które są po prostu zbyt duże, aby można je było obliczać w locie.

Porada ogólna: przechowuję większość danych między dwiema bazami danych, pierwsza to dane z szeregów czasowych i jest znormalizowana. Moja druga baza danych jest bardzo zdenormalizowana i zawiera wstępnie zagregowane dane. Tak szybki jak mój system, nie jestem ślepy na fakt, że użytkownicy nawet nie chcą czekać 30 sekund na załadowanie raportu - nawet jeśli osobiście uważam, że 30 sekund na złamanie 2 TB danych jest wyjątkowo szybkie.

Aby wyjaśnić, dlaczego zalecam przechowywanie godziny oddzielnie od daty, oto kilka powodów, dla których robię to w ten sposób:

1. Dane elektryczne są prezentowane według zakończenia godziny- dlatego 01:00 jest w rzeczywistości średnią energią elektryczną z poprzedniej godziny, a 00:00 oznacza koniec godziny 24. (Jest to ważne, ponieważ faktycznie musisz wyszukać dwie daty, aby uwzględnić wartość 24-godzinną - dzień, w którym szukają plus pierwszy znak następnego dnia.) Dane pogodowe są jednak prezentowane w sposób wyprzedzający (aktualny i prognozowany na następną godzinę). Na podstawie moich doświadczeń z tymi danymi konsumenci chcą przeanalizować wpływ pogody na cenę / zapotrzebowanie na energię. Jeśli miałbyś użyć prostego porównania dat, faktycznie porównałbyś średnią cenę za poprzednią godzinę ze średnią temperaturą za kolejną godzinę, mimo że znaczniki czasu są takie same.DATETIME kolumna.
2. Występ. Powiedziałbym, że co najmniej 90% raportów, które generuję, to wykresy, zwykle przedstawiające cenę w stosunku do godziny dla jednej daty lub zakresu dat. Konieczność rozdzielenia czasu od daty może spowolnić szybkość zapytania użytego do wygenerowania raportu w zależności od zakresu dat, który chcesz zobaczyć. Nierzadko konsumenci chcą widzieć jedną datę, rok do roku, w ciągu ostatnich 30 lat (w rzeczywistości w przypadku pogody jest to wymagane do wygenerowania 30-letnich normalnych) - może to być powolne. Oczywiście możesz zoptymalizować swoje zapytanie i dodać indeksy, i zaufaj mi Mam kilka szalonych indeksów, których wolałbym nie mieć, ale dzięki temu system działa szybko.
3. Wydajność. Nienawidzę konieczności pisania tego samego kodu więcej niż raz. Kiedyś zapisywałem datę i godzinę w tej samej kolumnie, aż musiałem powtarzać to samo zapytanie, aby wyodrębnić część czasu. Po jakimś czasie miałem już dość tego i wyodrębniłem go do własnej kolumny. Im mniej kodu musisz napisać, tym mniejsza jest szansa na błąd. Ponadto konieczność pisania mniej kodu oznacza, że ​​możesz szybciej wysyłać raporty, nikt nie chce czekać przez cały dzień na raporty.
4. Użytkownicy końcowi. Nie wszyscy użytkownicy końcowi są zaawansowanymi użytkownikami (tzn. Potrafią pisać SQL). Posiadanie danych już zapisanych w formacie, który można wprowadzić do Excela (lub innego podobnego narzędzia) przy minimalnym wysiłku sprawi, że staniesz się bohaterem w biurze. Jeśli użytkownicy nie mogą uzyskać dostępu do danych lub łatwo nimi manipulować, nie będą korzystać z twojego systemu. Uwierz mi, zaprojektowałem idealny system kilka lat temu i nikt go nie używał z tego powodu. Projektowanie baz danych polega nie tylko na przestrzeganiu predefiniowanego zestawu zasad / wytycznych, ale także na zapewnieniu użyteczności systemu.

Jak powiedziałem powyżej, wszystko opiera się na moim osobistym doświadczeniu i powiem wam, że ciężko było kilka lat i wiele przeprojektowań, aby dotrzeć do tego, gdzie jestem teraz. Nie rób tego, co zrobiłem, ucz się na własnych błędach i upewnij się, że angażujesz użytkowników końcowych twojego systemu (lub programistów, autorów raportów itp.) W podejmowanie decyzji dotyczących bazy danych.

Indeksy PostgreSQL i BRIN

Sprawdź to sam. To nie jest problem na 5-letnim laptopie z dyskiem SSD.

EXPLAIN ANALYZE
CREATE TABLE electrothingy
AS
  SELECT
    x::int AS id,
    (x::int % 20000)::int AS locid,  -- fake location ids in the range of 1-20000
    now() AS tsin,                   -- static timestmap
    97.5::numeric(5,2) AS temp,      -- static temp
    x::int AS usage                  -- usage the same as id not sure what we want here.
  FROM generate_series(1,1728000000) -- for 1.7 billion rows
    AS gs(x);

                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series gs  (cost=0.00..15.00 rows=1000 width=4) (actual time=173119.796..750391.668 rows=1728000000 loops=1)
 Planning time: 0.099 ms
 Execution time: 1343954.446 ms
(3 rows)

Utworzenie tabeli zajęło więc 22 minuty. W dużej mierze, ponieważ stół jest skromny 97 GB. Następnie tworzymy indeksy,

CREATE INDEX ON electrothingy USING brin (tsin);
CREATE INDEX ON electrothingy USING brin (id);    
VACUUM ANALYZE electrothingy;

Tworzenie indeksów zajęło też sporo czasu. Chociaż są BRIN, mają tylko 2-3 MB i łatwo przechowują w pamięci RAM. Czytanie 96 GB nie jest natychmiastowe, ale nie jest to prawdziwy problem dla mojego laptopa przy twoim obciążeniu.

Teraz pytamy o to.

explain analyze
SELECT max(temp)
FROM electrothingy
WHERE id BETWEEN 1000000 AND 1001000;
                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=5245.22..5245.23 rows=1 width=7) (actual time=42.317..42.317 rows=1 loops=1)
   ->  Bitmap Heap Scan on electrothingy  (cost=1282.17..5242.73 rows=993 width=7) (actual time=40.619..42.158 rows=1001 loops=1)
         Recheck Cond: ((id >= 1000000) AND (id <= 1001000))
         Rows Removed by Index Recheck: 16407
         Heap Blocks: lossy=128
         ->  Bitmap Index Scan on electrothingy_id_idx  (cost=0.00..1281.93 rows=993 width=0) (actual time=39.769..39.769 rows=1280 loops=1)
               Index Cond: ((id >= 1000000) AND (id <= 1001000))
 Planning time: 0.238 ms
 Execution time: 42.373 ms
(9 rows)

Zaktualizuj za pomocą znaczników czasu

Tutaj generujemy tabelę z różnymi znacznikami czasu w celu zaspokojenia żądania indeksowania i wyszukiwania w kolumnie znacznika czasu, tworzenie zajmuje trochę dłużej, ponieważ to_timestamp(int)jest znacznie wolniejsze niż now()(które jest buforowane dla transakcji)

EXPLAIN ANALYZE
CREATE TABLE electrothingy
AS
  SELECT
    x::int AS id,
    (x::int % 20000)::int AS locid,
    -- here we use to_timestamp rather than now(), we
    -- this calculates seconds since epoch using the gs(x) as the offset
    to_timestamp(x::int) AS tsin,
    97.5::numeric(5,2) AS temp,
    x::int AS usage
  FROM generate_series(1,1728000000)
    AS gs(x);

                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series gs  (cost=0.00..17.50 rows=1000 width=4) (actual time=176163.107..5891430.759 rows=1728000000 loops=1)
 Planning time: 0.607 ms
 Execution time: 7147449.908 ms
(3 rows)

Teraz możemy zamiast tego uruchomić zapytanie dotyczące wartości znacznika czasu,

explain analyze
SELECT count(*), min(temp), max(temp)
FROM electrothingy WHERE tsin BETWEEN '1974-01-01' AND '1974-01-02';
                                                                        QUERY PLAN                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=296073.83..296073.84 rows=1 width=7) (actual time=83.243..83.243 rows=1 loops=1)
   ->  Bitmap Heap Scan on electrothingy  (cost=2460.86..295490.76 rows=77743 width=7) (actual time=41.466..59.442 rows=86401 loops=1)
         Recheck Cond: ((tsin >= '1974-01-01 00:00:00-06'::timestamp with time zone) AND (tsin <= '1974-01-02 00:00:00-06'::timestamp with time zone))
         Rows Removed by Index Recheck: 18047
         Heap Blocks: lossy=768
         ->  Bitmap Index Scan on electrothingy_tsin_idx  (cost=0.00..2441.43 rows=77743 width=0) (actual time=40.217..40.217 rows=7680 loops=1)
               Index Cond: ((tsin >= '1974-01-01 00:00:00-06'::timestamp with time zone) AND (tsin <= '1974-01-02 00:00:00-06'::timestamp with time zone))
 Planning time: 0.140 ms
 Execution time: 83.321 ms
(9 rows)

Wynik:

 count |  min  |  max  
-------+-------+-------
 86401 | 97.50 | 97.50
(1 row)

Tak więc w 83,321 ms możemy agregować 86 401 rekordów w tabeli zawierającej 1,7 miliarda wierszy. To powinno być rozsądne.

Godzina zakończenia

Obliczanie zakończenia godziny jest również dość łatwe, skróć znaczniki czasu, a następnie po prostu dodaj godzinę.

SELECT date_trunc('hour', tsin) + '1 hour' AS tsin,
  count(*),
  min(temp),
  max(temp)
FROM electrothingy
WHERE tsin >= '1974-01-01'
  AND tsin < '1974-01-02'
GROUP BY date_trunc('hour', tsin)
ORDER BY 1;
          tsin          | count |  min  |  max  
------------------------+-------+-------+-------
 1974-01-01 01:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 02:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 03:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 04:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 05:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 06:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 07:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 08:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 09:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 10:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 11:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 12:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 13:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 14:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 15:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 16:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 17:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 18:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 19:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 20:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 21:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 22:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 23:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-02 00:00:00-06 |  3600 | 97.50 | 97.50
(24 rows)

Time: 116.695 ms

Ważne jest, aby pamiętać, że nie używa indeksu w agregacji, choć może. Jeśli to jest twoje typowe zapytanie, prawdopodobnie potrzebujesz BRIN na date_trunc('hour', tsin)tym polega niewielki problem, który date_truncnie jest niezmienny, więc musisz najpierw go owinąć, aby tak było.

Partycjonowanie

Innym ważnym punktem informacji na temat PostgreSQL jest to, że PG 10 przynosi partycjonowanie DDL . Możesz na przykład łatwo tworzyć partycje na każdy rok. Podział skromnej bazy danych na mniejsze, małe. Robiąc to, powinieneś być w stanie używać i utrzymywać indeksy btree zamiast BRIN, co byłoby jeszcze szybsze.

CREATE TABLE electrothingy_y2016 PARTITION OF electrothingy
    FOR VALUES FROM ('2016-01-01') TO ('2017-01-01');

Lub cokolwiek.

Dziwi mnie, że nikt tutaj nie wspominał o testach porównawczych - to znaczy, dopóki @EvanCarroll nie przyszedł ze swoim doskonałym wkładem!

Gdybym był tobą, poświęciłbym trochę czasu (i tak, wiem, że jest to cenny towar!) Konfigurując systemy, uruchamiając to, co według ciebie będzie (uzyskaj informacje od użytkowników końcowych tutaj!), Powiedzmy, twoje 10 najczęstszych zapytań.

Moje własne myśli:

Rozwiązania NoSQL mogą działać bardzo dobrze w określonych przypadkach użycia, ale często są mało elastyczne w przypadku zapytań ad-hoc. Zabawne spojrzenie na NoSQL autorstwa Briana Akera - byłego głównego architekta MySQL - patrz tutaj !

Zgadzam się z @ Mr.Brownstone, że twoje dane doskonale pasują do rozwiązania relacyjnego (i ta opinia została potwierdzona przez Evana Carrolla )!

Gdybym zobowiązał się do jakichkolwiek wydatków, byłoby to związane z moją technologią dyskową! Wydawałbym wszelkie pieniądze, które miałem do dyspozycji, na NAS lub SAN, a może niektóre dyski SSD, aby przechowywać moje rzadko zapisywane dane zbiorcze!

Najpierw przyjrzę się temu, co mam teraz dostępne . Przeprowadź kilka testów i pokaż wyniki decydentom. Masz już pełnomocnika w formie pracy EC ! Ale szybki test lub dwa połączone na twoim sprzęcie byłyby bardziej przekonujące!

Następnie pomyśl o wydawanie pieniędzy! Jeśli zamierzasz wydawać pieniądze, najpierw spójrz na sprzęt, a nie na oprogramowanie. AFAIK, możesz wynająć technologię dyskową na okres próbny, lub jeszcze lepiej, wypróbować kilka dowodów koncepcji w chmurze.

Moim osobistym pierwszym portem do takiego projektu byłby PostgreSQL. Nie oznacza to, że wykluczyłbym zastrzeżone rozwiązanie, ale prawa fizyki i dysków są takie same dla wszystkich! „Yae cannae burta prawa fizyki Jim” :-)

Jeśli jeszcze tego nie zrobiłeś, spójrz na DBMS z szeregów czasowych, ponieważ jest on zoptymalizowany do przechowywania i wysyłania zapytań do danych, gdzie głównym celem jest typ daty / godziny. Zazwyczaj bazy danych szeregów czasowych są używane do rejestrowania danych w zakresach minut / sekunda / sub-sekund, więc nie jestem pewien, czy nadal jest odpowiedni dla przyrostów godzinowych. To powiedziawszy, wydaje się, że warto przyjrzeć się tego rodzaju DBMS. Obecnie InfluxDB wydaje się być najbardziej popularną i powszechnie stosowaną bazą danych szeregów czasowych.

Oczywiście nie jest to problem NoSQL, ale sugerowałbym, że chociaż rozwiązanie RDBMS mogłoby działać, myślę, że podejście OLAP będzie pasować znacznie lepiej, a biorąc pod uwagę bardzo ograniczone zakresy danych, zdecydowanie sugeruję zbadanie zastosowania bazy danych opartej na kolumnie zamiast jednego opartego na wierszach. Pomyśl o tym w ten sposób, możesz mieć 1,7 miliarda danych, ale nadal potrzebujesz tylko 5 bitów, aby zindeksować każdą możliwą wartość godziny lub dnia miesiąca.

Mam doświadczenie z podobną problematyczną domeną, w której Sybase IQ (obecnie SAP IQ) służy do przechowywania do 300 milionów liczników na godzinę danych zarządzania wydajnością sprzętu telekomunikacyjnego, ale wątpię, czy masz budżet na tego rodzaju rozwiązanie. Na arenie open source MariaDB ColumnStore jest bardzo obiecującym kandydatem, ale poleciłbym również zbadanie MonetDB.

Ponieważ wydajność zapytań jest dla Ciebie głównym czynnikiem, rozważ, w jaki sposób zapytania będą sformułowane. Tutaj OLAP i RDBMS pokazują swoje największe różnice: - dzięki OLAP normalizujesz wydajność zapytań, nie zmniejszając liczby powtórzeń, zmniejszając ilość pamięci, a nawet wymuszając spójność. Oprócz oryginalnego znacznika czasu (mam nadzieję, że pamiętasz uchwycenie jego strefy czasowej?) Masz osobne pole dla znacznika czasu UTC, inne dla daty i godziny, a jeszcze więcej dla roku, miesiąca, dnia, godziny, minuty i przesunięcie UTC. Jeśli masz dodatkowe informacje o lokalizacjach, możesz zachować je w osobnej tabeli lokalizacji, którą można sprawdzić na żądanie, i zachowaj klucz do tej tabeli w głównym rekordzie, ale zachowaj pełną nazwę lokalizacji w głównej tabeli jako w końcu

Ostateczna sugestia: użyj osobnych tabel dla popularnych danych zagregowanych i użyj zadań wsadowych do ich zapełnienia, dzięki czemu nie musisz powtarzać ćwiczenia dla każdego raportu, który używa zagregowanej wartości i sprawia, że ​​zapytania porównują bieżące z historycznymi lub od historycznego do historycznego o wiele łatwiej i znacznie, znacznie szybciej.