Obecnie mam za zadanie wdrożenie schematu pamięci masowej dla stosunkowo dużej ilości danych. Dostęp do danych będzie przede wszystkim możliwy w celu ustalenia bieżącej data pointwartości, ale jestem również zobowiązany do śledzenia ostatnich sześciu miesięcy historii trendów / analiz danych.
Dodano ostatnie wymaganie do śledzenia wartości min/ max/ sumz ostatniej godziny.
UWAGA: Idealnie chciałbym rozważyć opcję MongoDB, ale najpierw muszę wykazać, że wyczerpałem opcje serwera SQL.
Dane
Poniższa tabela przedstawia główne źródło danych (najczęściej wyszukiwane). Tabela będzie miała około pięciu milionów wierszy. Zmiany danych będą przede wszystkim UPDATEinstrukcjami z bardzo okazjonalnymi INSERTinstrukcjami po początkowym załadowaniu danych. Zdecydowałem się na grupowanie danych według, dataPointIdjak zawsze wybierasz all values for a given data point.
// Simplified Table
CREATE TABLE [dbo].[DataPointValue](
[dataPointId] [int] NOT NULL,
[valueId] [int] NOT NULL,
[timestamp] [datetime] NOT NULL,
[minimum] [decimal](18, 0) NOT NULL,
[hourMinimum] [decimal](18, 0) NOT NULL,
[current] [decimal](18, 0) NOT NULL,
[currentTrend] [decimal](18, 0) NOT NULL,
[hourMaximum] [decimal](18, 0) NOT NULL,
[maximum] [decimal](18, 0) NOT NULL
CONSTRAINT [PK_MeterDataPointValue] PRIMARY KEY CLUSTERED ([dataPointId],[valueId])
)Druga tabela jest wyraźnie większa i wynosi około 3,1 miliarda wierszy (co stanowi dane z ostatnich sześciu miesięcy). Dane starsze niż sześć miesięcy zostaną usunięte; w przeciwnym razie INSERTinstrukcje danych ściśle (~ 200 wierszy / s, 720 000 wierszy / godzinę, 17 milionów wierszy / tydzień).
// Simplified Table
CREATE TABLE [dbo].[DataPointValueHistory](
[dataPointId] [int] NOT NULL,
[valueId] [int] NOT NULL,
[timestamp] [datetime] NOT NULL,
[value] [decimal](18, 0) NOT NULL,
[delta] [decimal](18, 0) NOT NULL
CONSTRAINT [PK_MeterDataPointHistory] PRIMARY KEY CLUSTERED ([dataPointId], [valueId], [timestamp]))
Oczekuje się, że ta tabela podwoi rozmiar, ponieważ liczba wartości śledzonych punktów danych wzrośnie do 400 wierszy / s (więc osiągnięcie ~ 10 miliardów nie jest wykluczone).
Pytania) (tak, zadaję więcej niż jedno ... wszystkie są ze sobą ściśle powiązane).
Obecnie używam bazy danych SQL-Server 2008 R2 Standard Edition. Prawdopodobnie poprę aktualizację do wersji Enterprise Edition, jeśli można uzyskać żądany poziom wydajności z partycjami tabel (lub MongoDB, jeśli nie można osiągnąć wymaganego poziomu wydajności za pomocą SQL-Server). Chciałbym uzyskać informacje na temat:
1) Biorąc pod uwagę, że trzeba obliczyć min, maxa sumprzez ostatnią godzinę (jak w now - 60 minutes). Jakie jest najlepsze podejście do śledzenia ostatnich danych:
Przechowuj najnowsze dane w pamięci usługi danych. Zapisuj obliczoną min / maks / średnią przy każdej aktualizacji danych.
Zapytanie o najnowszą historię z tabeli historii (wpływa na następne pytanie?) Podczas każdej instrukcji UPDATE. Zapytanie dotyczyłoby dostępu do najnowszych danych w celu uzyskania wartości punktu danych i powinno być skanowane tylko w ciągu ostatniego miliona rekordów?
Czy przechowywać najnowszą historię w samym wierszu DataPointValue, aby uniknąć wyszukiwania w tabeli historii? Być może przechowywany jako łańcuch rozdzielany i przetwarzany w ramach procedury UPDATE?
Inna opcja, której nie rozważałem?
2) Ponieważ DataPointValueHistoryzapytania względem danych zawsze będą dotyczyć dataPointIdjednego lub więcej valueId. Dane, o które pytamy, będą zwykle dotyczyły ostatniego dnia, tygodnia lub miesiąca, ale w niektórych przypadkach mogą dotyczyć pełnych sześciu miesięcy.
Obecnie generuję przykładowy zestaw danych, aby eksperymentować z tym, czy bardziej sensowne jest klastrowanie według dataPointId / valueId / timeStamp lub timeStamp / dataPointId / valueId. Jeśli ktoś ma doświadczenie w pracy ze stołem tej wielkości i chce zaoferować swój wgląd, będzie to mile widziane. Opieram się na tej drugiej opcji, aby uniknąć fragmentacji indeksu, ale wydajność zapytań ma kluczowe znaczenie.
Klaster
DataPointValueHistorywedług dataPointId -> valueId -> timeStampKlaster
DataPointValueHistorywedług timeStamp -> dataPointId -> valueId
3) Wreszcie, jak wspomniano powyżej, myślę, że sensowne będzie podzielenie DataPointValueHistorytabeli. Wszelkie sugestie dotyczące najlepszego podziału danych historycznych byłyby bardzo mile widziane.
Jeśli najpierw skupię się na znaczniku czasu, myślę, że dane powinny być podzielone na partycje według tygodnia (łącznie 27 partycji). Najstarszy podział zostanie wyczyszczony po 27 tygodniu.
Jeśli najpierw klastrowane przez dataPointId, myślę, że dane powinny być podzielone na partycje według jakiegoś modułu identyfikatora?
Ponieważ mam bardzo ograniczone doświadczenie w partycjonowaniu tabel, twoja wiedza będzie mile widziana.