[PHP / MySQL] Hoe statistieken database beheersbaar houden?

[Reveller]

Enige tijd geleden ben ik begonnen met het bouwen van een statistieken-script voor mijn website. Tot nu toe gaat alles voorspoedig: via een javascript bestandje verzamel ik informatie over de bezoeker en stop deze in een MySQL database. Momenteel kan ik de volgende statistieken uitlezen:

• procentuele verdeling van gebruikte OS, browser, resolutie, etc.
• clickpad van alle bezoekers, met IP, host en totale tijd op site doorgebracht
• oude bezoeken van gebruikers en aantal keer dat een bezoeker al eerder hier geweesr is (dankzij een cookie)
• aantal pageviews en populairste pagina's
• verdeling van de bezoekers over de tijd

Ik gebruik onder andere de volgende tabellen. Niet-afgebeelde tabellen zijn tabellen die helpen met het normaliseren van de database, waaronder stats_browsers, stats_os, stats_ip, etc.

code:

stats_index     stats_hits     stats_paths
-----------     ----------     -----------
sid             title          sid
session         location       location
cookie          hits           referrer
ip                             title
host                           created
palette
resolution
os
browser

De tabel stats_index is de hoofdtabel. Hier komt elke sessie in te staan. Bij elke sessie sla ik op welke browser, os, etc de bezoeker gebruikt. Ook lees ik een cookie id op, zodat ik kan zien of de bezoeker met deze sessie al andere sessies op de site heeft gehad. Bij elke klik controleer ik of de sessie al in stats_index staat. Zo ja, dan sla ik de klik op in stats_path, zodat ik later het klikpad kan bekijken. Ook wordt stats_hits geupdate, zodat ik aan het aantal hits kan zien welke pagina het meest populair is.

Tot zo ver was alles vrij makkelijk. Maar ik kom er nu achter dat de echte uitdaging bij heen statistiekentool is, om de database beheersbaar te houden. Met een 100 bezoekers per dag neemt de omvang van zo'n database door het vastleggen van de klikpaden al vrij snel toe. Het is geen optie om de klikpaden dan maar niet vast te leggen, want dan verlies ik ook statistieken als "aantal bezoekers op maandag" en "aantal pageviews tussen 22:00 en 23:00 uur". Het dilemma is: hoe minder je opslaat hoe beter beheersbaar, maar hoe ongedetailleerder de statistieken.

Dus zat ik eraan te denken om een stats_archive tabel te maken. Eens per maand lees ik dan de stats_paths tabel uit, en sla hiervan een samenvatting op in stats_archive. Vervolgens gooi ik de tabel leeg:

code:

stats_archive
------------------------
date      2005-07-12
sessions  45
ips       43
browsers  1:10 2:15 3:20
os        1:43 2:2
pageviews 472

etcetera. De kolom "browsers" is dan een varchar. De waarde kan ik ophalen en exploden. Dan kan ik uitlezen dat 10 bezoekers IE 6 gebruikten, 15 bezoekers FF 1.0, etc. Het voordeel is dat de database klein blijft en weinig onderhoud vergt. Nadeel is dat ik bv. geen oude bezoeken, bijvoorbeeld van iemand die al 5 keer op de site is geweest, kan terugkijken. Het aantal malen dat iemand op de site is geweest zal ik ook in het cookie moeten opslaan, in plaats van het DISTINCT uit de stats_index te halen.

Mijn vragen:

• is zo'n history tabel een goed idee / gebruikelijk
• hoe ver ga jij met het opslaan van gegevens (ook complete klikpaden?)
• hoelang bewaar jij je statistieken?
• heb je misschien tips / opmerkingen over mijn aanpak?

[pjvandesande]

Statistieken bewaren wij hier oneindig. Ze gaan elke keer in een history table. Zo kunnen we over 10 jaar kijken hoe het 10 jaar geleden ging.

Er zijn ook projecten waar wij een apparte history database hebben. Hiermee vervuil je niet de database voor de rest van je site.

[sariel]

Hoe veel bezoekers heb je nu?
Op zich is het wel handig om deze gegevens in een aparte database te zetten, als je problemen krijgt op je huidige database, dan kan je die gewoon overzetten naar een andere server, met meer ruimte en zo.
op zich is het wel handig om gewoon alle informatie in de database op te slaan, want hoe meer info, hoe nuttiger je database is. Als je te weinig gegevens onthoudt, dan heb je weinig aan je hele database.
Zorg er ook voor dat je de scripts heel erg optimaliseert, want het kan erg lastig zijn voor de bezoekers ( en je database) als je slechte scripts bouwt, die langzaam zijn, en rotte queries sturen.

[sariel]

Voor m'n werk hebben we geen echte logging naar database, maar een pakket wat iedere nacht alle logging doorstampt, en daar statjes op maakt. Deze statistieken worden echter maar een half jaar bewaard, omdat anders de server overlijdt (1.25 miljoen bezoekers/week, met redelijk veel hits/visit).
Hoe lang jij je gegevens bewaard, moet je eigenlijk zelf zien. Bij hoeveel data wordt de database dusdanig traag dat je niet goed kan werken er mee? Verwijder dan data (eerst backupje maken)

[Confusion]

Reveller schreef op woensdag 07 december 2005 @ 15:19:
Dus zat ik eraan te denken om een stats_archive tabel te maken. Eens per maand lees ik dan de stats_paths tabel uit, en sla hiervan een samenvatting op in stats_archive.

Waarom een samenvatting? Opslagruimte is goedkoop. Hooguit zou ik een samenvatting van veelgevraagde stats maken, die direct beschikbaar blijven.

[Reveller]

Confusion schreef op woensdag 07 december 2005 @ 16:04:
[...]
Waarom een samenvatting? Opslagruimte is goedkoop.

Je hebt gelijk, in zoverre dat harde schijven bijna niets meer kosten. Maar het is wel zo dat mijn site op een shared hosting accountje staat. Dan is schijfruimte al duurder. Maar wat nog belangrijker is: je deelt de server resources met 50, 60 anderen. Je wilt dus ook geen diepe data-mining op je database loslaten

sariel schreef op woensdag 07 december 2005 @ 15:47:
Bij hoeveel data wordt de database dusdanig traag dat je niet goed kan werken er mee?

Nooit getest vanwege shared hosting gebeuren Overigens is het logging script al redelijk geoptimaliseerd. Slechts 2 tot 3 eenvoudige queries per pagina. Met de queries om de pagina zelf te renderen erbij opgeteld, zit ik in totaal op 8 - 10 queries per pagina. Dat is heel redelijk denk ik.

[ Voor 34% gewijzigd door Reveller op 07-12-2005 17:31 ]

[BCC]

Mijn vragen:
• is zo'n history tabel een goed idee / gebruikelijk

Ja, het genereren van rapporten is zeer gebruikelijk

• hoe ver ga jij met het opslaan van gegevens (ook complete klikpaden?)

Alleen visits.

• hoelang bewaar jij je statistieken?

Statistieken niet lang, rapporten wel.

• heb je misschien tips / opmerkingen over mijn aanpak?

Het lijkt mij naast het meest logische ook het meest praktische. Bij statistieken is het vooral een kwestie van de resolutie terugbrengen. Waarom je willen weten of Japie 2 jaar geleden ook op je site is geweest en wat hij toen aangeklikt heeft? Sites zijn daarnaast toch in beweging, net als IP adressen. Het is bijvoorbeeld wel nuttig om van elke maand te weten wat de meest gebruikte klikpaden zijn, maar waarom zou je dat van elk individu bewaren?

[ripexx]

Wellicht klinkt het gek maar waarom ga je niet de logfiles analyseren. Daarmee belast je de website/database niet met onnodige statistieken. De logfiles kan je dan gewoon via een andere applicatie analyseren. Zelfs bij een shared hoster is het mogelijk om eigen logfiles te hebben. Zowel apache als IIS ondersteunen aparte logfiles.

Na de verwerking van de logfiles kan je er uitgebreide statistieken op los laten. De compressed logfiles kan je eventueel als archief off-site bewaren. Met behulp van de orginele logfiles kan je dan weer je database vullen. Dus je maakt dan je eiegn datawarehouse. Voordeel hiervan is dat je met een applicatie of stored procedures handige tabellen kan maken waar je dan weer analyses of grafieken van kan maken.

Maar de aller belangrijkste vraag is wat wil ik bij houden en waarom. Want ik snap dat het leuk is om te maken maar wat wil je nu daadwerkelijk bijhouden? Voor de grote jongens/portals zijn met name de gevolgde paden belangrijk. Want als ik het menu nu zo en zo verander dan gebeurt er dit en dit. Daarmee kan je dus je site optimaliseren en meer rendement behalen of mensen sturen naar de juiste plekken.

[Reveller]

ripexx schreef op woensdag 07 december 2005 @ 22:08:
Wellicht klinkt het gek maar waarom ga je niet de logfiles analyseren.

Dat is natuurlijk ook een optie, maar ik heb daar niet voor gekozen omdat het minder flexibel is. Zo kun je niet zien welke bezoekers al eerder op je site geweest zijn bijvoorbeeld. Een cookie wordt immers niet in de logfiles opgeslagen.

Voor de grote jongens/portals zijn met name de gevolgde paden belangrijk. Want als ik het menu nu zo en zo verander dan gebeurt er dit en dit. Daarmee kan je dus je site optimaliseren en meer rendement behalen of mensen sturen naar de juiste plekken.

Ik denk dat als de interface duidelijk en bondig is, ook kleinere sites veel kunnen hebben aan enige klikpad analyse. Ik ben het wel met anderen eens, dat het voor een kleinere site niet nodig is om te zien hoe pietje in zijn derde bezoek vier maanden door de site heeft geklikt. Maar een overzicht van meest gevolgde paden is altijd mooi natuurlijk. Naarmate ook het MKB steeds meer het belang van een goede informatieve website inziet en professioneler met hun website wil omspringen, is het volgens mij een logisch gevolg dat ook zij binnenkort zwaardere eisen gaan stellen aan beschikbare statistieken. En natuurlijk kun je altijd een webtrends accountje afsluiten, maar als alles in 1 CMS zit, heeft dit vele voordelen (gebruiksgemakt, single sign on, etc).

Overigens ben ik gisteren wat aan het prutsen geweest met het weergeven van meest gevolgde paden. Ik heb daartoe de volgende tabel gemaakt:

code:

stats_map
---------
location
referrer
count

Hiermee kan ik ziet hoe vaak men van een bepaalde pagina naar een andere pagina klikt. Deze informatie wil ik grafisch weergeven, maar ik weet niet goed hoe. Bijvoorbeeld: hoe diep wil je zo'n pad weergeven? En zijn er applets / flashfilms waar je een array in kunt laden met info uit de stats_map tabel en die deze data omzetten tot een boom of iets dergelijks?

[MTWZZ]

Ik ben het eens met BCC over het bewaren van statistieken, als je die allemaal houdt zelfs in een history database moet je eens bekijken hoeveel je dat gaat gebruiken. Het is waarschijnlijk efficienter om de rapporten te bewaren.
Als je rapporten wil genereren over een periode van maanden/jaren/whatever dan kun je dat prima uit je rapporten halen. Tenzij je dit natuurlijk over een variabele periode wil doen wat dan problemen oplevert.

[Reveller]

Dan ben ik daar in ieder geval uit:

• maandelijks een rapport genereren
• het aantal malen dat iemand op de site is geweest en wanneer dit voor het laatst was opslaan in het cookie
• maandelijks de gedetailleerde visitor klikpaden weggooien
• wel belangrijk: een meest-gebruikt klikpad opslaan. Mijn vraag blijft nog staan hoe je dit grafisch weer moet geven. Kent iemand hier een applet of flash movie voor?

[BalusC]

Ik hou ook realtime statistieken bij op mijn website. Ik heb het eigenlijk als volgt aangepakt:
* httpd.conf zo instellen dat apache naar een MySQL tabel logt (én een cronlogfile als backup)
* een cron-statsgenerator dat elke minuut draait (en ook wordt aangeroepen bij elke refresh van 1 van de statistiekenpagina's op mijn website) die dan de logs omzet naar leesbare statistieken in verschillende tabellen (atm zijn dat er 36; 20 voor website en 16 voor forum)

Geen javascript dus. De belangrijkste getallen (sessies, unieke bezoekers, pageviews, MB's, etc) worden simultaan incrementeel in 3 tabellen opgeslagen: dag, maand en jaar. Tot zover vind ik dit het handigst werken. Wanneer je de gegevens per jaar wil inzien, dan hoef je enkel de cijfertjes uit de jaar-tabel halen ipv een SUM te doen in de dag-tabel.

Ook kun je inzien hoeveel maal iemand (aan de hand van de IP) per dag, per maand danwel per jaar op de website is geweest.

Het tonen van de klikpaden is in theorie ook mogelijk, maar op het moment vraagt dit nét teveel load van de server (klikpaden van de laatste 24 uur tonen, bij ca 1000 pageviews per dag). Al ben ik er op het moment wel mee bezig Grafisch weergeven kan prima met HTML/CSS. Althans, dat is wat ik heb gedaan op mijn website.

Mocht je meer informatie willen over de inrichting danwel de code: shoot

[djluc]

Je kunt ook extra gegevens in je logfiles bij laten houden. Heb je daar al eens naar gekeken?

[Reveller]

Ik wil dus voor de statistiekentool binnen mijn CMS een efficiente manier vinden om historische data op te slaan. Ik heb er een tijdje over nagedacht, en denk dat 1 tabel het makkelijkste en ook efficientste is in dit geval. De hoeveelheid schijfruimte die ervoor nodig is, is beperkt en het genereren van statistieken uit 1 tabel is imho erg resource-arm.

Met onderstaande tabel zou ik voor elk uur van de dag het aantal pageviews, sessies, ip's gebruikte browser, os, resolutie, etc. op kunnen slaan. Omdat de uren, dagen, maanden en jaren in aparte kolommen staan, kan ik razendsnel een grafiekje hieruit maken zoals "aantal pageviews verdeeld over de uren van de dag" of "aantal unieke ip's verdeeld over maanden van het jaar".

Deze tabel krijgt dus 24 * 365 = 8.760 regels per jaar erbij en dat is ideaal weinig in in mijn optiek. Wel maak ik me zorgen om het aantal kolommen. Ik heb er slechts een aantal weergegeven, maar in principe komt er voor alles dat je meet een kolom bij. Dus: pageviews, sessies, ips, FF 1.0, IE 4.0, IE 5.0, IE 6.0, Opera 8.0, Opera 8.5, Andere browser, Windows 95, Windows NT 4.0, Windows 98, Windows XP, Windows 2003, 800x600, 1024x768, 1152x864, (etc), 8 bit, 16 bit, 32 bit, Realplayer (geinstalleerd of niet), Mediaplayer, Quicktime, etc. etc. etc. Dat loopt dus al snel op tot 50 kolommen:

code:

+-----+-----+-------+------+-----------+---------+-----+--------+--------+-----+
| uur | dag | maand | jaar | pageviews | sessies | ips | FF 1.0 | IE 6.0 | etc |
+-----+-----+-------+------+-----------+---------+-----+--------+--------+-----+
|  15 |  22 |    11 | 2006 |      2532 |    1710 | 505 |    400 |    654 | ... |
+-----+-----+-------+------+-----------+---------+-----+--------+--------+-----+
|  16 |  22 |    11 | 2006 |      2376 |    1533 | 643 |    543 |    534 | ... |
+-----+-----+-------+------+-----------+---------+-----+--------+--------+-----+
|  17 |  22 |    11 | 2006 |      3523 |    2000 | 778 |    432 |    754 | ... |
+-----+-----+-------+------+-----------+---------+-----+--------+--------+-----+

Als uit de statistieken van vandaag blijkt dat er een niet eerder gesignaleerde browser (bv. IE 8.0) op mijn site is geweest, wil ik automatisch een "IE 8.0" kolom in die history-tabel aanmaken. Een aantal vragen:

• is het splitsen van de datum in een uur, dag, maand en jaar kolom gangbaar of kun je net zo snel statistieken genereren wanneer je 1 datum-kolom maakt en dan wat extra mysql datum functies in je query gebruikt? Ik heb daar al een mee gewerkt, maar meen mij te herinneren dat deze aanpak efficienter is voor bovenstaande overzichten
• voorzie jij problemen met deze manier van werken die ik nu over het hoofd zie? Wat vind je er goed / slecht aan?

Update: ik heb zojuist bij de Tweakers.net stats gekeken, en op deze pagina staan de tweakers browserstats per browser weergegeven, zonder onderscheid te maken tussen versies. Dat is eigenlijk wel voldoende. In dat geval is het niet nodig een kolom IE 6.0, IE 7.0, etc. aan te maken. De toegevoegde waarde van versienummers is achteraf gezien toch beperkt; je hebt er evt. alleen wat aan als je hard zou moeten debuggen omdat een bepaalde IE versie een css-hack niet accepteert, oid...

[ Voor 14% gewijzigd door Reveller op 05-03-2006 15:31 ]

[djluc]

Ik vind het niet handig. Maak dan gewoon een paar gekoppelde tabellen, dus een tabel met browsers die je met een koppeltabelletje koppelt aan het uur o.i.d. Automatisch kolommen aanmaken is traag, onbeheersbaar en wordt een zooitje even heel lomp gezegd.

[BalusC]

BalusC schreef op donderdag 08 december 2005 @ 20:05:
Mocht je meer informatie willen over de inrichting danwel de code: shoot

Ik heb de code overigens sinds een maandje opensource gemaakt. Zie ook Realtime Stats Howto.

En mbt Browsers: normaliseer en maak gewoon een nieuwe tabel 'browsers' aan met daarin de velden datum/tijd, browsernaam, versie en hits. Als je indexeert op datum en eventueel ook op hits (alleen wanneer je daarop een groupby en/of orderby wil doen) dan is het wel snel zat

[ Voor 32% gewijzigd door BalusC op 06-03-2006 06:55 ]

[Reveller]

Dank je, BalusC! Ik zie dat je gekozen hebt voor een pure logfiles oplossing. Nadeel hiervan vind ik dat je een aantal zaken niet kunt meten, zoals schermresolutie en browser-plugins. Voordeel is dat je een hoop info over dataverkeer en downloads krijgt. Had ik niet aan gedacht, maar het is erg cool! Imho zou een statstool die logfiles en een webbug combineert, idaal zijn. Waarom koos jij voor alleen logfiles?

BalusC schreef op donderdag 08 december 2005 @ 20:05:
Het tonen van de klikpaden is in theorie ook mogelijk, maar op het moment vraagt dit nét teveel load van de server (klikpaden van de laatste 24 uur tonen, bij ca 1000 pageviews per dag). Al ben ik er op het moment wel mee bezig Grafisch weergeven kan prima met HTML/CSS. Althans, dat is wat ik heb gedaan op mijn website.

Ik ben erg geinteresseerd in de code die jij gebruikt om een klikpad te visualiseren. Momenteel is dit nog niet toegevoegd aan je stats-programma. Als je deze code zou willen delen, dan zeer graag!

Ook heb ik net je .sql bestand bekeken, en vraag me wel af hoe jij statistieken wil maken die data per uur van de dag groeperen (als je dat al wilt ). Je slaat immers alleen een datum en een dag van de week op. Als je statistieken als deze hieronder wilt, zou je dan je datum in een datetime veranderen of zou je een extra kolom "hour of day" oid aanmaken?



Wel heb je me met je open-source tool erg geinspireerd om eens wat dieper naar de Apache logfiles te kijken. Dat heb ik nog niet eerder echt gedaan, en er staat een zee aan informatie in!

[ Voor 15% gewijzigd door Reveller op 06-03-2006 21:18 ]

[FragFrog]

Reveller schreef op maandag 06 maart 2006 @ 21:10:
Ook heb ik net je .sql bestand bekeken, en vraag me wel af hoe jij statistieken wil maken die data per uur van de dag groeperen (als je dat al wilt ). Je slaat immers alleen een datum en een dag van de week op. Als je statistieken als deze hieronder wilt, zou je dan je datum in een datetime veranderen of zou je een extra kolom "hour of day" oid aanmaken?

Ik neig zelf altijd sterk naar gewoon datetime column. Stuk overzichtelijker EN makkelijker dan losse columns voor uren / minuten / seconden etc. Niet onderschatten hoe makkelijk het is in SQL om daar specifieke data uit te halen. Wil je iets vinden tussen 15.00u en 16.00u bijvoorbeeld gooi je simpelweg iets in de trant van 'WHERE HOUR(datetime_field) BETWEEN 15 AND 16' in je select query. Bovendien kun je op die manier ook makkelijker een unix timestamp verkrijgen wat altijd een fijn laatste redmiddel is wanneer je een datetime wilt doorgeven van de ene applicatie aan de andere

[BalusC]

Reveller schreef op maandag 06 maart 2006 @ 21:10:
Dank je, BalusC! Ik zie dat je gekozen hebt voor een pure logfiles oplossing. Nadeel hiervan vind ik dat je een aantal zaken niet kunt meten, zoals schermresolutie en browser-plugins. Voordeel is dat je een hoop info over dataverkeer en downloads krijgt. Had ik niet aan gedacht, maar het is erg cool! Imho zou een statstool die logfiles en een webbug combineert, idaal zijn. Waarom koos jij voor alleen logfiles?

Nou, omdat dit het makkelijkst (overal) implementeerbaar is

Ik ben erg geinteresseerd in de code die jij gebruikt om een klikpad te visualiseren. Momenteel is dit nog niet toegevoegd aan je stats-programma. Als je deze code zou willen delen, dan zeer graag!

Dit heb ik niet toegevoegd, omdat het in mijn geval redelijk veel load vraagt. Ik zou ook enkel de klikpaden van het laatste uur kunnen weergeven ipv de laatste 24 uur, dat is in minder dan een halve seconde gepiept, maar dat vind ik dan ook weer niet echt nuttig. Ik wil de source wel met jou delen. Ik zal het je vanavond nog wel toemailen

edit:
Sterker, ik heb het zojuist geintegreerd in de opensource statsgenerator Zie dus de downloadpage.

Ook heb ik net je .sql bestand bekeken, en vraag me wel af hoe jij statistieken wil maken die data per uur van de dag groeperen (als je dat al wilt ). Je slaat immers alleen een datum en een dag van de week op. Als je statistieken als deze hieronder wilt, zou je dan je datum in een datetime veranderen of zou je een extra kolom "hour of day" oid aanmaken?

Aangezien mijn statsgenerator realtime is en voor elke minuut wordt gescheduled, is een extra kolom "HourOfDay" imho sneller en handiger bij het toevoegen van de nieuwe hits aan de bestaande hits. De kolom DayOfWeek is overigens ongebruikt, maar het leek me op een of andere manier wel handig Dit kan best wel als deprecated bestempeld worden. Ik groepeer de dayofweek gewoon mbv dataformat %w op de date veld. Ietsjes trager, maar dat maakt de code wel herbruikbaarder wanneer ik op bijv dag, maand of jaar wil groeperen

Wel heb je me met je open-source tool erg geinspireerd om eens wat dieper naar de Apache logfiles te kijken. Dat heb ik nog niet eerder echt gedaan, en er staat een zee aan informatie in!

Apache kan nog meer dingen loggen: http://httpd.apache.org/docs/1.3/mod/mod_log_config.html Maar andere gegevens dan IP, HTTP status, request, verzonden bytes, useragent en referrer vind ik niet zo belangrijk

[ Voor 4% gewijzigd door BalusC op 07-03-2006 22:36 ]