Dzień dobry,
Mam sobie system, który przechowuje w jednej tabelce dane z zewnętrznych uprzedzań. Urządzania te nadają co kilka minut nowe dane (EDIT docelowo ma być co 5 sekund). W 80% przypadków będę potrzebował tylko ostatni wpis z każdego urządzenia. Jak zoptymalizować takie coś? Pomysły, jakie mi przyszły do głowy (oczywiście poza dodaniem indeksów) to zmaterializowany widok, z który będzie zawierał ostatnie odczyty z urządzeń. Osobna tabelka zawierająca tylko ostatni odczyt dla każdego urządzenia uzupełniana na trgigerze na tabeli głównej. Spratycjonowanie tabletki po id urządzenia i/lub dacie. Danych w tej tabelce będzie około 2mln rekordów (w miarę stała ilość, bo dane będą trzymane określony czas). Celem optymalizacji jest osiągnięcie jak najkrótszego czasu uzyskania danych ostatniego zapisu. Za wszelkie wskazówki będę wdzięczny.
będę potrzebował tylko ostatni wpis z każdego urządzenia
no a nie prościej dodać tam timestampa, albo sortowanie po ID (które pewnie będzie też kluczem) i po prostu weź sortuj malejąco i pobieraj 1 wynik?
Jak zoptymalizować takie coś?
A czy tych urządzeń będą dziesiątki milionów, żeby w ogóle potrzeba była jakaś optymalizacja? Bo kilkanaście/kilakdziesiąt urządzeń generujących co parę minut wpis to daje nam kilkanaście tysięcy wpisów na dobę, czyli wartość pomijalną dla jakiejkolwiek bazy
Osobna tabelka zawierająca tylko ostatni odczyt dla każdego urządzenia uzupełniana na trgigerze na tabeli głównej
Ale po co? Patrz - pierwszy punkt
Danych w tej tabelce będzie około 2mln rekordów
Jakikolwiek Postgres Ci to ogarnie z palcem w otworze pod ogonkiem ;)
EDIT
Popieram pomysł podany poniżej przez @Miang - skoro potrzebujesz na szybko odczytać tylko ostatnie położenie, to jednocześnie z zapisem do SQL, ostatnie położenie wrzucaj do Redis'a i z niego sobie odczytaj ostatnią lokalizację. Chociaż nadal uważam tak, jak napisałem powyżej - przy takiej ilości zapisów, postgres nie powinien mieć kłopotów ze zwróceniem od ręki ostatniego zapisu, raczej te optymalizacje są niepotrzebne (przynajmniej na chwilę obecną).
@UglyMan: a ile tych urządzeń? bo może po prostu trzymaj w pamięci systemu ostatnią wartość
Miang napisał(a):
@UglyMan: a ile tych urządzeń? bo może po prostu trzymaj w pamięci systemu ostatnią wartość
Urządzeń jest koło 100 (w porywach każde będzie generowało jeden wpis co 5 sekund). Dane przychodzą z jednej strony i są wrzucane do bazy a pobierane z drugiej. Zaczynają się pojawiać małe, ale zauważalne opóźnienia i myślałem, że może jest jakiś wzorzec o rozwiązania takiego typu problemów,
W mojej ocenie przy 2 mln rekordów nie ma za bardzo co optymalizować.
Przy tabeli w układzie
ID_URZADZENIA | CZAS_WPISU | WARTOŚĆ
wystarczy że kluczem głównym (primary key) będzie ID_URZADZENIA, CZAS_WPISU.
Dzięki temu odczyt powinien być ekspresowy.
Jedyne co wpada mi do głowy przy dłuższym używaniu tabeli to vacuum full po każdym oczyszczeniu tabeli ze starych danych. Wyrzuci to martwe rekordy i zniweluje efekt dłuższego odczytu wraz z "wiekiem" tabeli.
A może by urządzenia pisały do tabeli z aktualnymi danymi (update), na tej tabelce byłby trigger, który by wpisywał dane do tabeli z danymi historycznymi (bo jak zrozumiałem, chciałeś to odwrotnie zrobić - wpis do dużej tabeli i na niej trigger, który by robił update na małej tabelce z ostatnim wpisem). Jak możesz wpłynąć na kod na urządzeniach, to może by 'odczyt' zawierał insert do tabeli z wszystkimi danymi oraz update na tabelce z ostatnimi wynikami ? Jeszcze z doświadczenia dopowiem, że raz bawiłem się w tworzenie tabel na ramdisku i za dużo mi to nie pomogło (ale nie wiem dokładnie, co rozumiesz przez 'zauważalne' opóźnienie, więc może u Ciebie zda to egzamin).
Czytam i się zastanawiam, na jakiej podstawie sądzicie, że 2mln rekordów to mało, skoro nie wiecie nic na temat struktury tabeli?
Co, jeśli ma 200 kolumn stałoznakowych, każda o długości 40 znaków, a do tego nie istnieje żaden sensowny indeks, którego można użyć i za każdym wyszukiwaniem serwer czesze całą tabelę, żeby zwrócić ten jeden rekord?
Wiem, że @UglyMan w żadnym wypadku nie jest nowicjuszem i takich kwiatków nie odstawia raczej, ale może warto byłoby dywagacje na temat konkretnego przypadku rozpocząć od DDL tabeli?
Znalazłem jedne powód złej wydajności - okazało się, że parsowanie jsona w pg nie jest wydajne.
A jak bardzo "parsujesz" tego JSONa? Może wystarczzy na nim indeks założyć?
Wyciągałem z niego wszystkie dane - roparoswywąłem wszystkie klucze. Okazało się, że jest to też trochę (trochę dużo) mojej niewiedzy. Przerobiłem to tak, że dane, do których sięgam zawsze i zawsze są wyciągnąłem do osobnych kolumn (przyspieszyło kilka razy - ok 10X) resztę danych - które są albo nie będą w JSONie dalej - ale tego jeszcze nie sprawdzałem.