Witam,
Zarządzam bazą (postgresql) z dużą ilością rekordów. W bazie są gromadzone statystyki z wielu urządzeń.
Przyrost ilość rekordów w miesiącu to ok 50 mln.
W tej chwili największym problemem jest wygenerowanie statystyk dla wybranego urządzenia z zakresu do 3 miesięcy.
Zapytanie dotyczy jednej tabeli w bazie. Stosuję indeksy i partycjonowanie z podziałem miesięcznym.
Zastanawiam się nad wykorzystaniem widoków fizycznych do przygotowania danych dla każdego urządzenia.
Nie jestem pewien czy to będzie dobre rozwiązanie jeśli liczba urządzeń będzie rosła.
Może ktoś podzieli się swoimi doświadczeniami w tym temacie?
0
0
W czym konkretnie jest problem? Ile masz rekordów per urządzenie, i ile danych jest zwracanych jako wynik zapytania? Chodzi o statystyki systemowe (tj. postgres zbiera na tabeli), czy o jakieś statystyki biznesowe?
0
Na urządzenie przypada od 100 tyś do 6 mln rekordów miesięcznie. Problem mam z czasem wykonywania zapytania wyciągającego statystyki dla urządzenia. Czasami trwa to tak długo że można pomyśleć że witryna przestała odpowiadać. Postgres zbiera z tabeli w bazie. Do tabeli dane dopisują inne procesy. Zestawienie zlicza ilość wystąpień danego zdarzenia w zadanym okresie dla wybranego urządzenia.
0
Od 300k do 18 M rekordów za te 3 miesiące per urządzenia, a wszystkiego masz ~150M, czyli musisz odczytać
0,2% do 12% danych z samej tabeli (+ opcjonalnie indeks jeśli jest wykorzystywany). Spory rozrzut.
Jak rozumiem masz zapytanie typu:
select count(0) from tabelka where urzadzenie=:x and dataZdarzenia>=:a and dataZdarzenia<=:b and typZdarzenie=:e
Jeśli tak, to do uzyskania odpowiedzi wystarczyłby indeks na <urzadzenie, typZdarzenia, dataZdarzenia>, a taki indeks mógłby być dużo mniejszy niż tabela, którą odpytujesz.
Jaki jest rozmiar rekordu?
-- Inna opcja:
Robisz full table scana i wyliczasz statystyki dla każdego urządzenia i masz je odświeżane np. raz dziennie, co godzinę etc.
1
Może cyklicznie (np. dziennie) generuj dane do tablicy dla statystyk za dany cykl (np. dzień) i uruchamiaj zapytania na agregatach?
0
yarel napisał(a):
Od 300k do 18 M rekordów za te 3 miesiące per urządzenia, a wszystkiego masz ~150M, czyli musisz odczytać
0,2% do 12% danych z samej tabeli (+ opcjonalnie indeks jeśli jest wykorzystywany). Spory rozrzut.Jak rozumiem masz zapytanie typu:
select count(0) from tabelka where urzadzenie=:x and dataZdarzenia>=:a and dataZdarzenia<=:b and typZdarzenie=:eJeśli tak, to do uzyskania odpowiedzi wystarczyłby indeks na <urzadzenie, typZdarzenia, dataZdarzenia>, a taki indeks mógłby być dużo mniejszy niż tabela, którą odpytujesz.
Jaki jest rozmiar rekordu?-- Inna opcja:
Robisz full table scana i wyliczasz statystyki dla każdego urządzenia i masz je odświeżane np. raz dziennie, co godzinę etc.
Zapytanie typu:
select typZdarzenie, count(0) from tabelka where urzadzenie=:x and dataZdarzenia>=:a and dataZdarzenia<=:b group by( typZdarzenie)
Rozmiar rekordu 328 B
0
Pijany Kot napisał(a):
Może cyklicznie (np. dziennie) generuj dane do tablicy dla statystyk za dany cykl (np. dzień) i uruchamiaj zapytania na agregatach?
Dzięki za pomysł, sprawdzę to na bazie testowej.
0
marcinzel napisał(a):
Zapytanie typu:
select typZdarzenie, count(0) from tabelka where urzadzenie=:x and dataZdarzenia>=:a and dataZdarzenia<=:b group by( typZdarzenie)Rozmiar rekordu 328 B
Masz 150 mln rekordów za 3 mce -> 46 GB danych w tabelce.
Jeśli w indeksie masz datę (8 bajtów + 2 klucze - licząc po 20 bajtów każdy), to sam indeks byłby 6.8 razy mniejszy, więc szybciej te dane wyciągniesz z indeksu niż z tabeli.
Funkcjonalność nazywa się "Index scan only" i jest w postgresie od wersji 9.2
0
Dziękuję bardzo za podpowiedź.
Przetestuję sobie to rozwiązanie.
0
nie ma co gdybać tylko trzeba przedstawić plan zapytania i tyle
0
Zgadzam się, nie ma co gdybać, tu trzeba zastosować hurtownię danych.
To tematu należy podejść ciut bardziej kreatywnie, a nie wrzucać wszystkie dane do jednego wora (jedna tabela?)...
Na początek to:
https://wiki.postgresql.org/images/3/38/PGDay2009-EN-Datawarehousing_with_PostgreSQL.pdf
https://www.slideshare.net/PGExperts/really-big-elephants-postgresql-dw-15833438
A jak masz naprawdę dużo danych i trochę czasu na rozpoznanie ;-)
https://greenplum.org/
0
wloochacz napisał(a):
Zgadzam się, nie ma co gdybać, tu trzeba zastosować hurtownię danych.
To tematu należy podejść ciut bardziej kreatywnie, a nie wrzucać wszystkie dane do jednego wora (jedna tabela?)...
Na początek to:
https://wiki.postgresql.org/images/3/38/PGDay2009-EN-Datawarehousing_with_PostgreSQL.pdf
https://www.slideshare.net/PGExperts/really-big-elephants-postgresql-dw-15833438A jak masz naprawdę dużo danych i trochę czasu na rozpoznanie ;-)
https://greenplum.org/
A możesz opisać swoje doświadczenia z PG? Wypada lepiej niż inne serwery? Czy procedury składowane w czasie działania blokują tabele? I czy do kaskadowego kasowania danych pod względem wydajności lepiej sprawdzi się stosowanie triggerów czy kluczy obcych i constraintów?
1
Zakręcony Szczur napisał(a):
A możesz opisać swoje doświadczenia z PG?
Nie mogę, ponieważ nie posiadam na tyle dużych doświadczeń, aby traktować mnie poważnie w kontekście PostgreSQL. Mój system jedynie używa baz PostgreSQL z innego systemu. Ale mam na tyle duże doświadczenie w bazach danych, aby wiedzieć że baza tego systemu jest hmm... trochę nie halo.
Natomiast sam silnik PostgreSQL jest uznany, poważny, ma świetną dokumentację i ogrom różnych narzędzi i dodatków.
Wypada lepiej niż inne serwery?
Na tak postawione pytanie jest tylko jedna odpowiedź. To zależy.
Na pewno stawiałbym na PostgreSQL w obszarze serwerów za free i nie tylko. Np. nie wybrałbym niezbyt rozpoznawalnego rozwiązania komercyjnego (np. Sybase, Informix, Advantage, NexusDB, itd.) zamiast PostgreSQL.
A poza tym, tak naprawdę tu nie chodzi o to że jest lepszy, tylko o ten który Ty lepiej znasz.
Czy procedury składowane w czasie działania blokują tabele?
Nie znając żadnego serwera odpowiem Ci, że to nie zależy od serwera tylko od poziomu izolacji transakcji.
Pewnie w każdym serwerze można to tak spartolić aby blokować wszystko.
I czy do kaskadowego kasowania danych pod względem wydajności lepiej sprawdzi się stosowanie triggerów czy kluczy obcych i constraintów?
Zawsze stawiam na klucze obce.
Wyzwalaczy używam tylko i wyłącznie wtedy, kiedy nie da się danego ograniczenia zrobić za pomocą FK. Tak np. w MSSQL omijam problem z FOREIGN KEY constraint may cause cycles or multiple cascade paths
Ale to też wiele zależy od przyjętej przez Ciebie konwencji w zakresie np. programowania logiki.
Ja nie robię tego w bazie danych, a bazę danych traktuję jako wydajny i bezpieczny (w zakresie komplementarności danych) worek na dane.
Tj. używam indeksów, kluczy obcych, ograniczeń itd. ale unikam jak tylko to możliwe procedur i wyzwalaczy, które implementują logikę biznesową.
Nie chodzi mi o to, że absolutnie nie używam procedur składowanych. Oczywiście używam, ale tylko i wyłącznie wtedy, kiedy zadanie jest skomplikowane i na pewno zostanie wykonane znacznie wydajniej przez procedurę w bazie danych.
Natomiast jestem zawołany przeciwnikiem procedur typu CRUD.
0
wloochacz napisał(a):
Zgadzam się, nie ma co gdybać, tu trzeba zastosować hurtownię danych.
Dość odważna propozycja, zwłaszcza że autor pytania nie napisał nic na temat tego do czego wykorzystywane są te dane, i czy system ma charakter OLTP czy DWH ;)
0
yarel napisał(a):
wloochacz napisał(a):
Zgadzam się, nie ma co gdybać, tu trzeba zastosować hurtownię danych.
Dość odważna propozycja,
Raczej oczywista, a nie odważna.
Implikuje to ilość danych oraz to, że wrzuca je do jednego wora.
powinien od razu zastosować jakieś mechanizm ETL i raczej mam na myśli nie narzędzia, a praktyki.
Każda optymalizacja w tym przypadku skończy się tak samo - będzie za chwilę za słaba.
To tylko kwestia czasu.
zwłaszcza że autor pytania nie napisał nic na temat tego do czego wykorzystywane są te dane, i czy system ma charakter OLTP czy DWH ;)
Ależ napisał:
W bazie są gromadzone statystyki z wielu urządzeń. Przyrost ilość rekordów w miesiącu to ok 50 mln.
Dla mnie to nie jest OLTP, a właśnie ETL + DWH.
0
Rzeczywiście nie określiłem typ systemu. Dane na tą chwilę są analizowane w kontekście urządzenia (jego właściciel na dostęp do danych) ale pewnie za chwilę będzie trzeba przeprowadzać inne analizy (uwzględniając np. wersje systemu na urządzaniu, producenta itp) więc wszystko idzie w kierunku DWH. Mam świadomość że gromadzenie danych w taki sposób jak dotychczas nie jest optymalne pod kątem wykorzystania ich do takich analiz.. Musze zapoznać się z tematem DWH. Na dzień dzisiejszy widzę że tabela w której gromadzę dane będzie źródłem dla większego i poukładanego logicznie systemu.