Forum PHP.pl

witam,
istnieje strona tibiaring.com,
nie chce nawiazywac do tego o czym gromadzi ona informacje, tylko zapytac czy ma ktos pomysl, w jaki sposob to robi i w jaki sposob w tak szybkim czasie analizuje informacje juz zgromadzone,

zrobilem mechanizm pozwalajacy w jakims stopniu przewidziec kto moze miec jaka inna postac bazujac sie na plikach, ktore powstawaly przez ponad miesiac na serverze hostingujacym przy uzyciu crona,
tak juz zgromadzone dane 'wrzucilem' do mysql'a
struktura mojej bazy wygladala mniej wiecej tak:
dla kazdego nowego sprawdzenia (sprawdzanie bylo odnotowywane co 5 minut) powstawala nowa tabela, w tabeli byla jedna kolumna typu varchar o dlugosci ~35 znakow, srednio w kazdej tabeli istnieje 500 rekordow,
jesli gromadzilem takie dane przez miesiac, to tabel powstalo ~30*24*(60/5)=30*24*12[skryopt zapisuje dane o graczach online 12 razy na godzine; daje to +/-8640 tabel miesiecznie;

napisalem skrypt w php laczacy sie z baza danych
nastepnie pobieralem wszystkie tabele z bazy (liste sprawdzen kto byl online i kiedy)
wykonywalem petle na kazdej z tabel w bazie w poszukiwaniu danej nazwy gracza,
jesli takowa istnieje, skrypt wrzuca roznice zbioru graczy w tabeli, ktora jest aktualnie oraz sasiednich sprawdzen w bazie, czyli mam na mysli ze jesli mamy tabele A,B,C i w tabeli B znaleziono dana nazwe gracza, to B-A oraz A-B oraz B-C oraz C-B wedrowalo do, mmm, nazwijmy to sobie np lista podejrzanych.

w tym problem, ze ten caly proces trwa bardzo dlugo (okolo 5-10 minut), czy ma ktos pomysl, w jaki sposob moznaby zmienic strukture bazy czy sam mechanizm, aby to wszystko dzialalo sprawniej?

Dlaczego masz wiele tabel? To wszystko można (trzeba) wpakować do jednej. Jaka jest sruktura tabeli (ew. danych które tam przymasz). Piszesz że jest to jedno pole varchar. Co jest w nim zawarte?

skrypt zapisujacy zapisuje co 5 minut liste graczy, ktorzy sa online;
wyglada tomniej wiecej tak:

probowalem wrzucic to do jednej kolumny, ale proces 'rozbijania' komorki pojedynczej by jeszcze bardziej spowalnial (tak mysle)

aktualnie moja baza wyglada tak:
nazwa tabeli: czas sprawdzania
zawartosc tabeli:

pokaż kod i definicję tabeli, bo dalej nie rozumiem.

Ile rekordów wrzucasz podczas jednego sparawdzenia? Jeden rekord z kilkoma loginami oddzielonymi przecinkiem?

Ja bym zrobił tabelę:
czas(datetime),
login(varchar)

W skrypcie tworzysz sobie plik txt zawierające dane do bazy oddzielone powiedzmy średnikiem, a następnie ładujesz hurtem do bazy LOAD DATA INFILE - dzaiła dużo szybciej niż wiele INSERTów;

Wrzucanie nawet kilku tysięcy rekordów o tak prostej konstrukcji nawet co pięć minut nie jest wielkim obciążeniem dla bazy (na chyba że stoi na jakimś PC XT lub innym AT ) Do przyspieszenia wyszukiwania powinieneś założyć jeszcze indeksy.

[SQL] pobierz, plaintext 
CREATE TABLE `200712191455` (`n` varchar (35)); INSERT INTO `200712191455` VALUES ("nazwa1"),("nazwa2");
CREATE TABLE `200712191500` (`n` varchar (35)); INSERT INTO `200712191500` VALUES ("nazwa2"),("nazwa3");
[SQL] pobierz, plaintext 

takich wpisow mam w pliku [dzien gromadzenia informacji].sql okolo 250 na dzien;
przejscie przez importowanie do bazy jeszcze jakos mija, ale samo wyszukiwanie trwa bardzo dlugo;

co do tego oddzielania przecinkami, juz zastanawialem sie nad tym, ale jesli samo przeszukiwanie bazy trwa tyle, to ile bedzie trwalo odnalezienie kazdego wpisu+podzielenie go na ciagi nazw graczy+obliczenie roznic zbiorow sasiednich ciagow nazw graczy
w pierwszym poscie napisalem jak ten mechanizm wyglada, ale troche moze to byc niezrozumiale, wiec podam przyklad:
zalozmy ze o godzinie 11:30 sprwadzonie dalo wynik:

o 11:35
a o 11:40
to jesli jako osobe poszukiwana podam nazwe 'c', to skrypt powinien zwrocic {11:40}-{11:35} [tylko, poniewaz tylko na granicy 11:35 a 11:40 pojawia sie taka roznica w zbiorach, ze w kolejnym nie ma c,

chodzi tutaj o to, zeby wychwycic te momenty, kiedy dana postac z gry jest wylogowywana (i byc moze przelogowywana na inna, czyli ta ktora chcemy poznac);

i w tym tkwi problem, ze chce aby takie sprawdzanie przeszlo przez cala baze, czyli w moim wypadku juz przez ponad 8000 wpisow,

dlatego troszke moze potrwac rozdzielanie srednikow

Przeszukiwanie bazy trwa długo bo masz ją źle zrobioną. Jak wygląda zapytanie którym pobierasz odpowiednie rekordy ?

EDIT:

Mylisz ładowanie bazy do danych z wyszukiwaniem. Rozdzielanie średnikami (przecinkami) następuje podczas ładowania po stronie PHP. Dane trafiają do bazy do odpowiednich pól w rekordach (data,login). Podczas tego przeszukiwania baza nic nie musi dzielić. O ile wszystko siedzi w jednej tabeli (a nie w 250 bo nawet sobie nie wyobrażam zapytania) i masz założony indeks, wyszukiwanie w tak prostej tabeli trwa poniżej 1sek na 1000000 rekordów (tak na oko).

EDIT:
gotowe rozwiązanie (pewnie nie idealne) ale ma zaletę: nie potrzebuje super szybkiej bazy. Pójdzie na zwykłym MySQL na każdym blaszaku.

Tabelę "main" zakładasz taką:
seria_id INT // numer serii pomiarów - każda seria co 5 minut ma numer o 1 większy - łatwo to możesz przeliczyć na dokładną godzinę
osoba_id INT // identyfikator osoby

Tabela wygląda tak:

seria_id osoba_id
1 1
2 1
2 2
3 1
3 2
4 3
4 2
5 3
5 2
6 3
6 2
7 3
8 3
9 3
10 1
11 1
12 1



zakładasz dodatkowe tabele:
osoby (osoba_id, login)
serie (seria_id, czas_pomiaru)

Co 5 minut uzupełniasz tabele : 1 wpis do serie, x wpisów do tabeli "main" gdzie x to ilosc osób które w tym czasie policzyłeś
dodajesz identyfikatory nowych osób do tabeli osoby. Jak masz założony tam klucz unikalny to robisz to jednym INSERT IGNORE.

Teraz najważniejsze:
przykładowe zapytanie które pokazuje w którym momencie dana osoba przestała być widoczna:

SELECT t1.seria_id, t1.osoba_id, t2.osoba_id FROM(SELECT DISTINCT seria_id, osoba_id FROM main WHERE osoba_id=1) AS t1
LEFT JOIN (SELECT seria_id-1 AS seria_id, osoba_id FROM main WHERE osoba_id=1) AS t2 USING(seria_id)
WHERE t1.osoba_id IS NOT NULL AND t2.osoba_id IS NULL

Nie sprawdzałem tego, ale powinno działać.

Ten post edytował kitol 21.02.2008, 22:19:29

wyszukiwanie trwa kilka sekund, zgadza sie
ale w jaki sposob analize danych przeprowadzic?

do poprawnego dzialania potrzebuje jeszcze obliczania 'roznicy' danych zbiorow graczy

Ten post edytował test30 22.02.2008, 13:53:32

Trzeba ułożyć odpowiednie zapytanie. Ale do tego najlepsza jest jedna tabela. Przy 250 nie wyobrażam sobie skonstruowania zapytania.

twoje 'innowacyjne' rozwiazanie zapowiada sie naprawde genialnie i moze byc niezle,
czyli w wyniku dostane osoby, ktore sa w poprzedniej tabeli? [seria_id-1]?
a jesli chcialbym do tego dodac dodawanie nastepnej roznicy nastepnego i aktualniego rekordu, jesli nastepny nie zawiera powtorzenia danej osoby, ktra wystepuje w aktualnie sprawdzanym, to co musialbym wprowadzic do zapytania? kolejne left join?
i jeszcze pytanie zwiazane z wydajnoscia, no przy 100000 rekordow jakos sie wykona, ale jesli po pewnym czasie baza bedzie zawierala kilka miesiecy zapisywania sprawdzen, czyli 31*24*12[ilosc sprawdzen na godzine]*600[ilosc dodanych osob_id do danej serii] to czy to nie bedzie rowniez makabra? to daje 5,356,800 wpisow miesiecznie, po roku bedzie ich 64,281,600

Nie musisz przecież wypełniać tej tabeli w nieskończoność. Zbierasz dane przez 1 dzień. Kopiujesz do drugiej tabeli o strukturze:
id_osoby
czas_od - moment w którym osoba była 1 raz widoczna
czas_do - moment w którym ta osoba przestała się pojawiać w tabeli
z warunkiem takim że w czasie od-do osoba cały czas była zalogowana. Czasy pojawienia się o zniknięcia osoby możesz wyznaczyć z mojego zapytania. W nowej tabeli liczba wierszy odpowiada liczbom logowań wszystkich użytkowników.

Pierwszą tabelę czyścisz i dalej wypełniasz przez 1 dzień (i tak w kółko). A właściwe wyszukiwanie przeprowadzasz w tabeli z czasami od -do co nie powinno już być ani trudne, ani powolne.

Nie czytałem wszystkiego, co tutaj napisaliście, ale nietrudno się dziwić, że skrypt twórcy wątku chodzi przeraźliwie wolno.
Nie powinno się zmieniać struktury bazy w trakcie działania aplikacji (czyt. tabele tworzy się tylko raz - podczas projektowania bazy).
Jeżeli wykonujesz pętlę i robisz ~8640 selectów do bazy i łączysz się z tyloma tabelami na raz, to nietrudno się dziwić, że skrypt mieli Ci to 5 min., albo i dłużej. W dobrze zaprojektowanej aplikacji, w trakcie jednego wywołania nie powinno być nie więcej, niż ~10 zapytań. Da się to zrobić nawet przy bardzo skomplikowanych bazach. Jakich danych byś sobie nie wymyślił, to da się je zamknąć w kilku lub ewentualnie kilkunastu tabelach relacyjnie ze sobą powiązanych. Po co robić pętlę, skoro można zrobić mądrego selecta z kilku tabel, skorzystać z polecenia join, widoków lub innych dobrodziejstw MySQL-a? Należy unikać przetwarzania danych przez skrypty. Powinno się jak najwięcej pracy zrzucić na silnik bazy danych. Twój skrypt jest strasznie niewydajny i dlatego chodzi wolno. Pomyśl o jego optymalizacji z wykorzystaniem koncepcji, które Ci podałem.

Ten post edytował Speedy 23.02.2008, 12:26:54

dlatego uwazam ze zwrocenie sie z tym na forum bylo dobrym pomyslem, czlowiek uczy sie na bledach,
od miesiaca ucze sie mysqla, ale bardzo trafna uwaga z iloscia zapytan do bazy,

co do projektowania bazy to ona caly czas jeszcze nawet nie powstala, wszystkie dane mam w plikach tekstowych, ktore dopiero czesciowo zostaly dodane ,
kitol, jak narazie dal mi cenna lekcje - relatywizm nie bez powodu jest w głębszej nazwie baz danych
+twoja uwaga o ilosci polecen
juz czego nauczylem sie nowego

Ten post edytował test30 23.02.2008, 03:16:56

Dodam jeszcze szacunkową ilość zapytań:
Skrypt wykonujący się co 5 minut powinien przygotowywać dane do załadowania do bazy. Z doświadczenia wiem, że najszybszym importem dużej ilości danych jest użycie LOAD DATA INFILE. Po odpowiednim przygotowaniu plików ładujesz dane do dwóch tabel (main oraz tabeli z osobami) za pomocą 2 zapytań + 1 zapytanie INSERT do dodania czasu pomiaru do 3 tabeli. Raz na dobę (lub rzadziej) wykonujesz skrypt który wypełnia tabelę czasy_od_do. Można to zrobić nawet za pomocą 1 zapytania+ jedno na czyszczenie tabeli main. Skrypt wyszukujący podejrzane osoby - 1 zapytanie. Myślę że w ten sposób zbudowany system powinien działać całkiem sprawnie.

pliki, na szczescie, da sie do load data inputa przygotowac

wspomniales cos o tym, zeby te dane z listy online co jakis czas (dzien lub rzadziej) dodawca do tabeli, ktora bedzie miaal strukture:

tabela: osoby_od_do


czy o to chodzi?
i kiedy juz bedzie taka tabela, to bedzie do 'podejrzanych' dodawalo dany id osoby, ktorej odpowiednik czas_od-czas_do lub czas_do-czas_od == 1 lub -1?

chodzi mi o to, ze jesli bysmy poszukiwali osoby powiazanej z osobą o id 2 to zapytanie zwrocilo by osoby: 4 i 5 (dlatego, ze te osoby sa zalogowane od serii nr 2, a w tej serii sprawdzanej osoby o id 2 juz nie bylo[i sa to serie sasiednie])

tzn czy te osoby wystepowaly w sasiednik seriach?

jesli dobrze zrozumialem to chcilabym zaznaczyc ze dziennie taka baza bedzie powiekszala sie o okolo 4000 wpisow :S
czy to i tak nie bedzie za duzo?

Ten post edytował test30 24.02.2008, 13:30:44