devel.cz

v jednom projekte robím logovanie "každého" HTTP requestu ... konkrétne len PHP scripty .. ktoré využijem na optimalizáciu systému
napíšem to všetko zjednodušene, len niektoré časti, aby ste pochopili pointu:

• ako databáza je použitá MariaDB
• logujem dátum vytvorenia, domenu, script, ip, počet sql, dlžku vykonávania sql, trvanie requestu, ...
• log PHP sciptu je jednoduchý SQL insert
• tabuľka obsahuje id, host, ip, script, ip_id, host_id, script_id ... kde *_id sú cudzie kľúče(default NULL) do iných tabuliek, ktorá obsahujú iba (ID a hodnotu)
• ip_id, host_id, script_id boli pridané kvôli optimalizácií vyhľadávania/filtrovania

• mám osobitnú tabuľku, do ktorej si ukladám to kedy boli aké ID spracované (date, idFrom, IdTo, convertStartDate, convertEndDate, generateStatsStart, generateStatsEnd)

• logy musím pred použitím upraviť, vzorová ukážka pre úpravu scriptu .. to isté robím aj pre doménu a IP ... vo finalnom SQL sú viaceré SQl spojené dokopy (napr. UPDATE table SET script=NULL, host=NULL, ip=NULL WHERE ...):
• uloženie unikátnych hodnôt, ktoré ešte nemám do osobitnej tabuľky:

  $selectIdsSQL = 'SELECT id from '.$tableLogs.' rl WHERE rl.id > '.$from.' and rl.id <= '.$to.' and rl.script NOT IN (SELECT value FROM '.$tableScript.')'
  $sql = 'INSERT INTO '.$tableScript.' (value) SELECT DISTINCT(script) FROM '.$tableLogs.' l WHERE l.id IN ('.$selectIdsSQL.')'

• úprava kľúčov:

  $sql = 'UPDATE '.$tableLogs.' rl SET rl.script_id=(SELECT id FROM '.$tableScript.' s WHERE s.value=rl.script) WHERE script_id IS NULL'

• vymazanie script textu:

  $sql = 'UPDATE '.$tableLogs.' rl SET rl.script=NULL WHERE rl.script IS NOT NULL'

následne mám ďalšiu tabuľku pre štatistiky, ktorá obsahuje zgrupované log dáta na každých 30 minút (ešte ju nemám vytvorenú ale bude sa vytvárať takto nejako):

$sql = 'UPDATE INTO '.$tableStats.' (time, ....) (SELECT round(time/1800), * from '.$tableLogs.' rl WHERE rl.id > '.$from.' and rl.id <= '.$to.' GROUP BY round(time/1800))'

• v logoch mám cca 900 unikátnych scriptov, 350 000 unikátnych IP, 700 unikátnych domén
• za posledný mesiac to bolo cca 20 000 000 logov (beží mi to iba mesiac, každý mesiac bude viac logov)
• momentálne len vytváram zobrazovanie a generovanie štatistík, takže všetky SQL aj optimalizujem ak to niekde pôjde
• všetky tieto štatistiky sa budú generovať na vyžiadanie(generovanie môže trvať aj pár minút) a nie automaticky

moja otázky sú:
1. je to dobrý návrh ukladania logov ?
2. je to dobrý návrh spracovania logov ?
3. ak to nie je dobre navhrnuté, ako inak by som to mal robiť ?

"konvertovanie" logov by som nemusel robiť, ak by som pred každým vložením logu zistil či už existuje IP, script a doména .. avšak rád by som sa vyhol tomu, aby som do každého requestu pridal 3 SQL naviac

EDIT:
Nejedná sa len o jednu databázu, ale o XYZ databáz.
Spustiť selecty nad produkčnou DB neviem, pretože systém je možné nakonfigurovať "nekonečne veľa možnosťami".
Optimalizácia SQL je len jedna z časti, rieším aj optimalizáciu pamäte, čas vykonania, čas renderovania, čas inicializácie frameworku, HTTP status, HTTP metódu, query string atď.
Pohľadov na tieto logy (štatistiky) bude viacero.

4. ak tomu správne rozumiem, tak sa jedna o spracovanie tzv. BIG DATA ?
5. ako to potom rieši napr. google, facebook, pri X milion násobne väčšom počte záznamov ? (ich riešenie je len viac serverov ?)