[mysql] Najbardziej optymalna baza dla statystyk

Mam zamiar zabrać się za napisanie własnych, prostych statystyk oglądalności w PHP i MySQL.

Poczatkowo myślałem o zbieraniu danych w tabelach: użytkownicy, wizyty i odsłony, ale szybko zdałem sobie sprawę, że po miesiącu pracy np. tabela wizyty urośnie do kilkuset tysięcy rekordów, powodując znaczne obniżenie wydajności strony oraz obciążenie serwera. Po roku wyciąganie statystyk z bazy może zająć parę godzin!

I teraz pytanie: co można zrobić, aby uniknać takiej sytuacji? Rozdzielić tabele np. na miesiące (wizyty_2009_styczeń, wizyty_2010_sierpień itp.) ? Jak Wy byście postąpili? Jak postepują np. Gemius czy Google Analytics?

zastanów się nad tym MySQLem. Postgres tez jest darmowy a ma ciekawą funkcję pozwalająca partycjonować dane (czyli w twoim przypadku dzielić według miesiąca).
Polecam lekture http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html.
Według mnie nie ma co sie samemu oprogramowywać podziału na miesiące jeśli jest produkt, który wspiera to natywnie.

poza tym, pewnie uzywają hurtowni danych, do ktorej przewalaja dane z RDB co jakiś okres.

Jak postepują np. Gemius czy Google Analytics?
Z pewnością nie używają PHP i MySQL...

AdamPL napisał(a)

Jak postepują np. Gemius czy Google Analytics?
Z pewnością nie używają PHP i MySQL...

Ani serwerów, które zatykają się na kilkuset tysiącach rekordów.
I na dodatek pewno wiedzą, że nie ma czegoś takiego jak "najbardziej optymalna".

Czyli posiadanie własnych statystyk na serwerze jest niemal niemożliwe bez własnego dedyka?

Proszę o odpowowiedź ekspertów od PHP/MySQL. Gdybyście chcieli wdrożyć staty na Waszej stronie jak byście to zrobili?

Google_has_you napisał(a)

Czyli posiadanie własnych statystyk na serwerze jest niemal niemożliwe bez własnego dedyka?

Niekoniecznie. Wszystko zależy od tego jakim hostingiem dysponujesz, ile i jakie dane będą wchodziły w zakres monitorowanych statystyk. Generalnie jeżeli będziesz prowadził statystyki tylko własnej strony to jak najbardziej może to szybko i sprawnie działać. Wyjątkiem będzie sytuacja gdy dysponujesz darmowym lub bardzo słabym płatnym hostingiem w takim wypadku statystyki mogą znacząco spowolnić lub nawet unieruchomić działanie serwisu.

Też tak myślalem, dopóki nie zacząłem projektować bazy. Policzmy:

Witryna z 100'000 uu/miesiąc i 1000'000 wizyt/miesiąc to 100 000 rekordów do tabeli użytkownicy(IP) i 1000000 rekordów do tabeli wizyty. Oczywiscie w kazdym rekordzie znajdzie sie sr. ilosć odsłon/wizyte, sredni czas spedzony na stronie, geolokalizacja uzytkownika i wiele innych.

Po 1 roku mamy 13'200'000 rekordów i ciągle rośnie.

Zakładając że jeden rekord waży 4kB w ciągu roku baza zajmie 4*13200000=52 800 000kB co daje 53 GB. Trochę za dużo jak na mój hosting.

Myślę, że masz złe założenia. Jeżeli zamierzasz mieć 100tyś unikalnych wizyt miesięcznie to po pierwsze hosting może nie udźwignąć samego serwisu bez statystyk, po drugie przy takiej oglądalności strona chyba zarobi na coś więcej niż hosting, po trzecie jak zamierzasz mieć rozmach jak Google to przyjmij do wiadomości, że ta firma ma więcej serwerów w chmurze niż wszystkie największe firmy razem wzięte, po czwarte jeżeli wiesz lepiej to po co się pytasz na forum?

Nie wiem lepiej, dlatego tu pytam.

Chciałbym tylko zapytać w jaki sposób najlepiej zabrać się za zrobienie statystyk dla własnej strony. Czy naprawdę nikt tego nie wie? Czy naprawdę nie ma możliwosci stworzenia prostych statyskyk na wlasnym serwerze? Czy jednyą opcją jest stat24 albo google analytics?

A Google podałem jako przykład, podejrzewam że tam wdrożone są najlepsze rozwiąznia.

PS.
Co do wielkosci strony - nawet mała strona po 1,5 roku uzbiera całkiem sporą baze danych ze statyastyk.

Na Google App Engine sesje leżą w bazie danych aż do momentu gdy się je samemu wysprząta. W ten sposób można by np w sesji trzymać akcje użytkownika i w odpowiednim momencie je zanalizować. Dodatkowo są dostępne logi serwera. Oczywiście trzymanie informacji o każdej akcji w nieskończoność jest bezsensowne i chyba nikt tak nie robi. Wyodrębnia się tylko interesujące informacje, a tych nie ma jakoś bardzo dużo.

Ale po co trzymać informacje o każdym wejściu w bazie?
Agreguj dane codziennie / co miesiąc i trzymaj zagregowane.
Oszczędzasz zarówno miejsce jak i czas dostępu.
IMHO najtańszy lub darmowy hosting bez problemu uciągnie.

BTW: Czemu nie chcesz użyć Google Analytics? Tak by było chyba najprościej.

AdamPL napisał(a)

Jak postepują np. Gemius czy Google Analytics?
Z pewnością nie używają PHP i MySQL...

LOL :) Google używa MySQL :) ale rozbudowanego o swoje dodatki.
Jeżeli chodzi o Gemius'a (na pewno używa PHP) - nie wiem jak to wygląda od strony bazy danych, ale oni tworzą nawet swoje własne systemy plików, więc pewnie też trochę pokombinowali.

Dlaczego nie chce darmowego rozwiazania? Ponieważ różne systemy przedstawiają odmienne wyniki - a im większy serwis tym większe rozbieżśności. Lepiej polegać na sobie.

Ale po co trzymać informacje o każdym wejściu w bazie?
Agreguj dane codziennie / co miesiąc i trzymaj zagregowane.

Chciałbym mieć dostep do konkretnych przedzialow czasu jak w stat24 czy GA. Np. dzisiaj, wczoraj, ostatni miesiac, ostatni rok, wrzesien 2010, cały czas etc. W takim wytpadku musze trzymac wsyzstkie dane.

@leadersmind
Widzę że jesteś niezłym specjalista. Jak byś wykonał takie zadanie?

http://stackoverflow.com/questions/362956/what-database-does-google-use

Google_has_you napisał(a)

Ale po co trzymać informacje o każdym wejściu w bazie?
Agreguj dane codziennie / co miesiąc i trzymaj zagregowane.

Chciałbym mieć dostep do konkretnych przedzialow czasu jak w stat24 czy GA. Np. dzisiaj, wczoraj, ostatni miesiac, ostatni rok, wrzesien 2010, cały czas etc. W takim wytpadku musze trzymac wsyzstkie dane.

A nie możesz trzymać statystyk dla kolejnych dni?
W bazie pamiętałbyś tylko krotki <data, ilość wizyt>.

Google_has_you napisał(a)

Nie wiem lepiej, dlatego tu pytam.

Chciałbym tylko zapytać w jaki sposób najlepiej zabrać się za zrobienie statystyk dla własnej strony. Czy naprawdę nikt tego nie wie? Czy naprawdę nie ma możliwosci stworzenia prostych statyskyk na wlasnym serwerze? Czy jednyą opcją jest stat24 albo google analytics?

Po 1.
W początkowych założeniach błędnie przyjąłeś, że pojedynczy wpis będzie miał 4kB. Raczej stawiałbym na kilkadziesiąt bajtów.

Po 2.
Czegoś o funkcjonalności choćby zbliżonej do Google Analytics i tak nie stworzysz.

Po 3.
Zamiast odkrywać od nowa koło, sprawdź, czy Twój usługodawca udostępnia ci dostęp do logów serwera. Praktycznie wszystkie informacje, które mógłbyś zebrać tego typu mechanizmem już i tak są zapisywane w logach.

A do generowania ładnych, fajnych i pożytecznych statystyk na podstawie logów już wiele narzędzi zostało stworzonych.

PS. Co do wielkosci strony - nawet mała strona po 1,5 roku uzbiera całkiem sporą baze danych ze statyastyk.

Wspomniane wyżej logi jakoś straszną tragedią (objętościowo) nie są - tym bardziej, że przeważnie się je kompresuje co jakiś czas, a jako pliki tekstowe kompresują się bardzo ładnie.

leadersmind napisał(a)

AdamPL napisał(a)

Jak postepują np. Gemius czy Google Analytics?
Z pewnością nie używają PHP i MySQL...

LOL :) Google używa MySQL :) ale rozbudowanego o swoje dodatki.
Jeżeli chodzi o Gemius'a (na pewno używa PHP) - nie wiem jak to wygląda od strony bazy danych, ale oni tworzą nawet swoje własne systemy plików, więc pewnie też trochę pokombinowali.

Google używa GWT który tłumaczy kod Java na ECMAScript, baza danych to DataStore który nie jest relacyjny (nie ma JOINów), za to wspiera polimorfizm (tak, można przenieść obiektowość wraz z polimorfizmem tutaj), działający na własnym systemie plików GFS (z rozproszoną replikacją danych i wymianą węzłów w trakcie pracy), własnej dystrybucji Linuxa oraz własnej architekturze typu cloud. Mają nawet własną sieć miedzykrajową, a może i międzykontynentalną (w każdym razie są porządne rurki między niektórymi datacenter). Google praktycznie wszystko ma swoje, a rozwiązania desktopowe czy mainstreamowe nie przeżyłyby w Googlu nawet ułamka sekund. Świadczy o tym nawet fakt, że najbardziej wysublimowane bazy od Oracle nie były w stanie sprostać wymaganiom Googla (w zasadzie skala działalności Googla wymaga dużo większej skalowalności od typowych rozwiązań).