tło
Mam sieć około 2000 czujników, z których każdy ma około 100 punktów danych, które zbieramy co 10 minut. Te punkty danych są zwykle wartościami całkowitymi, ale niektóre są łańcuchami i liczbami zmiennoprzecinkowymi. Te dane powinny być przechowywane przez 90 dni, więcej, jeśli to możliwe i nadal wydajne.
Projektowanie bazy danych
Kiedy pierwotnie zlecono mu ten projekt, napisałem aplikację C #, która napisała pliki oddzielone przecinkami dla każdego czujnika. W tamtym czasie nie było ich tak wiele, gdy ktoś chciał przyjrzeć się trendom, otworzyliśmy plik CSV w programie Excel i zobrazowaliśmy go w razie potrzeby.
Sprawy rosły i przeszliśmy na bazę danych MySQL. Stworzyłem tabelę dla każdego czujnika (tak, wiem, wiele tabel!); działał dobrze, ale ma pewne ograniczenia. Przy tak wielu tabelach oczywiście niemożliwe jest napisanie zapytania, które znajdzie dane wśród wszystkich czujników, gdy szuka się określonej wartości.
W następnej wersji przełączyłem się na Microsoft SQL Server Express i umieściłem wszystkie dane czujnika w jednej dużej tabeli. Działa to również i pozwala nam wyszukiwać wartości wśród wszystkich interesujących czujników. Jednak natrafiłem na limit 10 GB dla wersji Express i zdecydowałem się wrócić do MySQL zamiast inwestować w SQL Server Standard.
Pytanie
Jestem zadowolony z wydajności i skalowalności MySQL, ale nie jestem pewien, czy najlepiej zastosować podejście oparte na danych w jednym stole. 10 GB w jednym stole wydaje się wymagać innego projektu. Powinienem wspomnieć, że nadal istnieje potrzeba zapytania danych o wykresy i obawiam się, że pojawią się problemy z wydajnością zapytania, które przedstawia wykresy, na przykład dane dotyczące temperatury dla jednego czujnika przez pełne 90 dni. (Innymi słowy, wykres powinien być czymś, co można szybko wytworzyć, bez czekania, aż SQL posortuje stosy danych w celu odizolowania sensora).
Czy powinienem w jakiś sposób podzielić tę tabelę, aby zwiększyć wydajność? Czy to nie jest niezwykłe mieć tak duży stół?
Mam indeksy w kolumnach Identyfikator czujnika i Znacznik czasu, co stanowi w zasadzie granice definiujące każde zapytanie. (tzn. uzyskaj dane dla czujnika X od czasu A do czasu B).
Przeczytałem trochę o dzieleniu i dzieleniu na partycje, ale nie sądzę, aby były one odpowiednie w tym przypadku.
Edytować:
Na podstawie dotychczasowych komentarzy i odpowiedzi przydatne mogą być dodatkowe informacje:
Nieokreślony czas przechowywania: obecnie nie przechowuję danych z ostatnich 90 dni. Codziennie uruchamiam zapytanie, które usuwa dane starsze niż 90 dni. Jeśli stanie się to ważne w przyszłości, zgromadzę więcej, ale na razie wystarczy. Pomaga to utrzymać rozmiar pod kontrolą i wysoką (er) wydajność.
Typ silnika: Oryginalna implementacja MySQL używała MyISAM. Tym razem podczas tworzenia tabel dla nowej implementacji (jedna tabela danych zamiast wielu) domyślnie wybrano InnoDB. Nie wydaje mi się, żebym wymagał jednego lub drugiego.
Normalizacja: Istnieją oczywiście inne tabele oprócz tabeli gromadzenia danych. Te tabele wsparcia przechowują takie informacje, jak informacje o sieci dla czujników, dane logowania dla użytkowników itp. Nie ma wiele do normalizacji (o ile mi wiadomo). Tabela danych ma tak wiele kolumn, ponieważ istnieje tyle zmiennych z każdego czujnika. (Wiele temperatur, poziomów światła, ciśnienia powietrza itp.) Normalizacja według mnie oznacza, że nie ma zbędnych danych ani powtarzających się grup. (Przynajmniej dla 1NF.) Dla danego czujnika przechowywanie wszystkich wartości w określonym czasie wymaga jednego wiersza danych i nie ma w tym związku żadnych relacji 1: N (które widzę).
Mógłbym funkcjonalnie rozdzielić tabelę, tworząc (na przykład) wszystkie wartości związane z temperaturą w jednej tabeli, a wszystkie wartości związane z ciśnieniem powietrza w innej. Chociaż może to poprawić wydajność osoby wykonującej zapytanie tylko z temperaturą, nadal muszę wstawić wszystkie dane naraz. Mimo to wzrost wydajności może być opłacalny dla operacji SELECT. Oczywiście lepiej byłoby podzielić tabelę w pionie na podstawie tego, jak często użytkownicy żądają danych. Być może to wszystko, co powinienem zrobić. Wydaje mi się, że zadając moje pytanie, szukam potwierdzenia, że warto byłoby to zrobić.
Edycja 2:
Wykorzystanie danych: Ostatecznie większość danych nigdy nie jest przeglądana ani potrzebna, ponieważ zazwyczaj koncentrujemy się tylko na elementach z problemami. Ale próbując znaleźć problemy, używamy różnych narzędzi do wyszukiwania danych i określania, które elementy należy powiększyć.
Zauważyliśmy na przykład korelację między wartością użycia pamięci (zastrzeżonego oprogramowania klienta) a ponownym uruchomieniem / awarią. Jeden z punktów danych, który zbieram, odnosi się do tego użycia pamięci i mogłem spojrzeć na dane historyczne, aby pokazać, że urządzenia stają się niestabilne po przekroczeniu określonego użycia pamięci. Dzisiaj dla podzbioru urządzeń z tym oprogramowaniem sprawdzam tę wartość i wydaje polecenie ponownego uruchomienia, jeśli jest ona zbyt wysoka. Dopóki tego nie odkryłem, nie sądziłem, że zbieranie tych danych będzie miało wartość.
Z tego powodu utrzymywałem, że około 100 punktów danych jest gromadzonych i przechowywanych, nawet jeśli wartość jest wątpliwa. Ale w normalnym codziennym użytkowaniu użytkownicy zwykle sprawdzają może kilkanaście tych parametrów. Jeśli użytkownik zainteresuje się określonym obszarem geograficznym, może (przy użyciu oprogramowania) generować wykresy lub arkusze danych dla kilkudziesięciu czujników. Nierzadko patrzy się na 30-dniowy wykres z dwiema lub trzema liniami wykresu pokazującymi takie rzeczy, jak temperatura, ciśnienie powietrza i poziomy światła. W ten sposób uruchomione zostanie zapytanie podobne do tego:
SELECT sensor_id, location, data_timestamp, temp1, air1, light1
FROM data
WHERE data_timestamp >= '2012-02-01'
AND sensor_id IN (1, 2, 3);(W oryginalnej wersji MySQL, w której każdy czujnik miał własną tabelę, wydawane byłyby trzy osobne zapytania, ale wyniki były łączone w oprogramowanie do utworzenia wykresu.)
Ponieważ datatabela zawiera tak wiele wierszy (~ 10 milionów), pomimo włączonych wskaźników idi data_timestamp, wydajność jest wyraźnie gorsza niż scenariusz z wieloma tabelami (4500 wierszy zwróconych w ciągu 9 sekund w przeciwieństwie do mniej niż jednej sekundy w tym przykładzie). Możliwość znalezienia czujników spełniających określone kryteria jest praktycznie zerowa w schemacie wielostołowym, a zatem powodem przejścia do pojedynczego stołu.
Ten typ zapytania może być wykonywany przez wielu użytkowników w krótkich odstępach czasu, gdy wybierają oni różne grupy danych i porównują wykresy z każdego wyniku. Czasem frustracja może być odczekanie prawie 10 sekund na wykres lub arkusz kalkulacyjny.
Dane są odrzucane po 90 dniach. Można go zarchiwizować, ale obecnie nie jest to wymagane.
Mamy nadzieję, że te informacje pomogą bardziej odpowiednio pokazać, w jaki sposób dane są wykorzystywane po ich gromadzeniu i przechowywaniu.