[PHP/MySQL] Efficient requests per pagina per dag opslaan.

[DePhille]

Ik ben momenteel een gratis website tracker aan het schrijven met behulp van PHP en MySQL, maar ik zit met een probleem. Eén van de onderdelen van de statistieken is een lijst waarin staat hoeveel requests een bepaalde pagina op een bepaalde dag heeft gekregen. Ik heb hier besloten om ook onderscheid te maken tussen verschillende GET requests. Dus deze twee URLs worden als verschillend beschouwd:
topic.php?id=1
topic.php?id=2

Momenteel heb ik een tabel "pagestats" met volgende kolommen:
id (VARCHAR 16, unique key), pageid (INT 10), unix_day (INT 5), pageviews (INT 10), uniques (INT 10). PageID is een numerieke representatie van de URL, die bijgehouden wordt in een aparte tabel "pagelist" om zo de grootte van deze tabel te beperken. ID is de concatenatie van unix_day met een underscore gevolgd door pageid (bijv: 14435_789).

Dit is echter erg omslachtig, op een drukkere site (of een gemiddeld forum) zal de bovenvernoemde tabel ongeveer 102MB groot zijn en +/- 1.000.000 records bevatten na slechts een maand (en dan heb ik nog de tabel waar de URLs omgezet worden naar IDs, die zal ongeveer 20MB groot zijn dan). Ook wordt voor elke gebruiker zo'n tabel aangemaakt, dus als ik enkele klanten heb met drukkere sites is de chaos al meteen compleet.

Hoe kan ik dit efficiënter aanpakken?

Ik heb al zitten denken aan de volgende oplossing, maar ik betwijfel of deze methode beter/sneller is:
De tabel "pagelist" (zie hierboven) maakt nu geen onderscheid meer tussen verschillende GET queries waardoor er al veel minder records in zitten. Vervolgens heeft de "pagestats" tabel twee extra kolommen:
queries_pageviews (BLOB of TEXT of ?);
queries_uniques (BLOB of TEXT of ?);
Deze kolommen bevatten elk een geserializede Array:
$querystats_pageviews[$querystring]=(INTEGER);
$querystats_uniques[$querystring]=(INTEGER);
$querystring is de GET request, dus bijvoorbeeld: "topic=3&page=2" of "search=Hallo&resultsperpage=100". en de INTEGER is het aantal.

Ik heb echter geen idee of deze oplossing enig voordeel biedt op het vlak van performance tegenover de eerstgenoemde oplossing.


Alvast bedankt,
DePhille

[Voutloos]

Geen van beide, bij beide datamodellen serialize of concat je meerdere waarden in 1 kolom. Begin eens eerst helemaal opnieuw, zonder de `pagelist` optimalisatie en met 1 echte waarde per kolom.

[DePhille]

Welke dan wel?
Ik heb het script aangepast zodat de pagelist niet meer gebruikt hoeft worden. Maar daarmee is het hoofdprobleem nog steeds niet opgelost, ik zit nog altijd met een enorme tabel van 1.000.000 records. Door het verwijderen van de pagelist ben ik ook een (weliswaar onbelangrijke) functie kwijt: met behulp van de pagelist kon ik per pagina de title opslaan ipv enkel de URL, maar dat is minder belangrijk dan de performance boost die ik zoek.

De concatenatie in de eerste oplossing heeft als nut om snel een opzoeking te kunnen doen tijdens het genereren van de statistieken-paginas, zonder een unieke index is dit onmogelijk (duurt veel te lang, waarschijnlijk >30 seconden). Als ik de tweede oplossing gebruik, heb ik een tabel met weinig records, wat de snelheid ten goede komt. Enkel bij het berekenen van de statistieken zou dit wel eens een probleem kunnen vormen omdat ik dan geen index of kolom heb om een bepaalde URL mee te selecteren en dus alle kolommen apart moet afgaan. Dit zou ook wel eens voor problemen kunnen zorgen in verband met het geheugengebruik, als de serialized array te groot wordt.

Nog verdere ideeën?

[Bob]

Als ik je volg dan wordt de tabel zo groot omdat er heel veel veschillende pagina's zijn? Verder zie ik niet wat er veel plek in kan nemen. Een site heeft toch maar een 20-tal pagina's waarvan je geinteresseerd bent in de pageviews, als het er meer zijn moet je maar leren leven met een grote DB.

Dus ik zou het script aanpassen zodat het focust op bvb interessantepagina.php, nogeenpagina.php enzovoort, onafhankelijk van wat er nog van get data aan hangt. Of maak de opdeling nog beter, zorg dat in de php code een ID gegenereerd wordt die zegt welke pagina het is en gebruik die voor de DB.

Dus dan zit je toch maar met een rij per pagina per dag? Veel kan het dan niet meer zijn. Maar ik heb het gevoel dat ik de bal mis sla want ik zie totaal niet hoe je aan die 100 MB kwam

Hierin maak je dan van [pageID, dag] de primary key.

[Cartman!]

Geen antwoord op je vraa maareh...wat is jouw voordeel boven het gebruik van bijv. Google Analytics of Clicky? Die leveren veel meer gegevens dan jij nu doet en je kunt het op alle mogelijke manieren organiseren.

[DePhille]

Elke verschillende URL wordt apart beschouwd:
http://www.website.com/pagina.php?query1=abc is niet hetzelfde als
http://www.website.com/pagina.php?query1=def. Ze nemen dus beide een aparte plaats in. Als je dan een forum hebt of een wbeshop met de teller op, heb je dus heel veel verschillenbde URLs in die tabel (topic.php?id=1 bijvoorbeeld). 1.000.000 is uiteraard een extreem geval, maar met zo'n waarden moet ik rekening houden wil ik de service gratis kunnen aanbieden.

Google-analytics is niet echt een goede tracker. Het lijkt alsof er veel statistieken gegeven worden, maar de statistieken staan elk een paar keer op verschillende paginas. Daarbij is google-analytics niet real-time en zeker en vast niet accuraat. Google-analytics geeft ongeveer de helft van de bezoekers aan, een gemiddelde tracker (niet webalizer of awstats uiteraard) heeft een nauwkeurigheid van 80%. Toch bedankt voor de suggestie .

Zijn hier echt geen efficiente oplossing voor?

Bedankt voor de huidige reacties tot dusver,
DePhille

[BCC]

Waarom harvest je niet gewoon elke dag de Apache log? Aangezien je toch alleen maar wat stats wil. En waarom is google analytics niet goed genoeg? Aangezien je gemiddelden wil, heb je IMHO niets aan realtime gegevens.

Google-analytics geeft ongeveer de helft van de bezoekers aan

?! Hoe kom je hier bij?

Als je het toch echt zelf wil implementeren, moet je meta-data gaan bouwen. Dus bijvoorbeeld na elke dag/week gemiddelden en totalen berekenen en daarna de originele requestdata weggooien. Op die manier blijft de verzamelde data een stuk kleiner.

[ Voor 58% gewijzigd door BCC op 10-01-2009 21:19 ]

[Cartman!]

Realtime stats lijken leuk maar ga je in de praktijk continu stats kijken? Ik hoop dat je iets beters te doen hebt En ik denk dat het niet echt uitmaakt of ie maar 80% nauwkeurig is, het gaat meer om trending en om globale metingen vaak en daar is dat meer dan goed voor.

Trouwens, stop me het noemen van je naam onder je post, die staat aan de linkerkant ook al namelijk.

[Bob]

DePhille schreef op zaterdag 10 januari 2009 @ 20:53:
Elke verschillende URL wordt apart beschouwd:
http://www.website.com/pagina.php?query1=abc is niet hetzelfde als
http://www.website.com/pagina.php?query1=def. Ze nemen dus beide een aparte plaats in. Als je dan een forum hebt of een wbeshop met de teller op, heb je dus heel veel verschillenbde URLs in die tabel (topic.php?id=1 bijvoorbeeld). 1.000.000 is uiteraard een extreem geval, maar met zo'n waarden moet ik rekening houden wil ik de service gratis kunnen aanbieden.

En wat is het nut daar van? Dat zijn in het merendeel van de gevallen toch geen interessante statistieken? En voor die enkele gevallen maak je dan een uitzondering aan, en die uitzonderingen vormen dan de rijen van de tabel.

[DePhille]

Zoals eerder vermeld in de posts gaat het hier over een publieke service. Ik ga dus niet om de seconde mijn statistieken refreshen of bekijken, en ik heb beschikking over uitgebreide Apache log parsers (webalizer en awstats) waarmee ik meer dan tevreden ben. De features "real-time" stats en "80% nauwkeurigheid" zijn bijna puur marketing. Die 80% is ook zeer belangrijk in trending. De 20% die buiten de boot vallen (of zelfs 50% bij google analytics) hangen af van de gebruikte software bij de bezoeker. Neem dit als voorbeeld: browser X struikelt ergens over een lijn code in de javascript, dan wordt deze hit niet geregistreerd. Als gevolg zal browser X vrijwel nergens voorkomen in de statistieken waardoor de trending ook niet volledig juist is. Het schrijven van een website die op alle vlakken cross-browser compatible is, is vrijwel onmogelijk (vandaar dat de nauwkeurigheid van de gemiddelde off-site tracker meestal niet hoger komt dan 80%) en dus zullen er altijd wel bepaalde software-eigenschappen of instellingen voor een vertekend beeld zorgen bij de statistieken - en dan vooral bij trending.

Over Google-analytics bestaan er veel verschillende meningen, zeker over de nauwkeurigheid ervan. Als je de statistieken van awstats of webalizer vergelijkt (die ik trauwens als basis neem bij het berekenen van de vernoemde percentages) met die van google, geeft google slechts de helft aan (dit is op elk vlak, het gaat hier niet over verschillende interpretaties van een unieke bezoeker bijvoorbeeld). Merk ook op dat dit niet 'een willekeurige helft' is, het is de helft die voldoet aan een bepaalde software-opstelling of instelling.

Bedankt voor het idee BCC, de real-time eigenschap maakt inderdaad alleen een verschil op 'vandaag' of 'deze week' categorïen dus kan ik bijvoorbeeld de categoriën 'vorige week' of 'vorige maand' inderdaad gaan opslaan als een statisch rapport en de betreffende dynamische data verwijderen.

Cartman, bedankt voor je reactie, maar kunnen we nu terug on-topic? Als je over de verschillende trackers en counters wilt praten dan wil ik dit gerust doen in een ander topic of iets dergelijks, maar hier zou ik toch graag een oplossing vinden voor mijn probleem ongeacht het nut van mijn doelstelling .

EDIT: Deze statistieken kunnen juist wel intressant zijn vind ik. Zeker als je bijvoorbeeld een webshop hebt en je wilt weten welk artikel het meeste bekeken werd bijvoorbeeld. In de gewone off-site tracker zal dan te staan komen dat de pagina artikel.php een bepaald aantal hits heeft gekregen terwijl ik doel op statiestieken voor de individuele producten in dit geval. De uitzonderingen zijn inderdaad een goed idee, want ik bedenk me juist dat er ook soms ongewenste data staat in een GET-query die niet geïndexeerd hoeft te worden zoals een SessionID ofzo. Ik ga deze oplossing implementeren nu en kijken welk effect dit heeft op mijn huidige tabel, bedankt!

[ Voor 12% gewijzigd door DePhille op 10-01-2009 22:11 ]

[Gomez12]

Ja, maar wat is je probleem dan? Zolang je het zo gedetailleerd en real-time wilt kunnen tonen kost het gewoon ongelofelijk veel ruimte ( Real-time betekent ook snel opslaan, dus geen ingewikkelde query's, liefst iets van een persistent dbase connectie of een flat-file formaat, dit kost gewoon ruimte )
Het aggregeren van al die platte resultaten kost weer veel performance.

Wat je nog zou kunnen doen is real-time over vandaag, dit haal je gewoon uit de platte data. Dan op een 2e server periodiek een verzamel query gaan doen die je platte data normaliseert / zinniger opslaat.
Dan mis je alleen in je statistieken je real-time data maar dit is toch niet erg lijkt me?

Maar simpel gezegd zou ik het niet zo gedetailleerd doen, en de gebruiker de mogelijkheid geven om in de tracking code eigen tags mee te geven.
Dan kan een gebruiker zelf aangeven waarop hij wil filteren ipv dat de GET-query dit bepaalt.

[DePhille]

Inderdaad, ik denk dat ik het daarmee zal moeten doen . Bedankt voor je inzichten, Gomez12!

[ACM]

DePhille schreef op zaterdag 10 januari 2009 @ 22:07:
Over Google-analytics bestaan er veel verschillende meningen, zeker over de nauwkeurigheid ervan. Als je de statistieken van awstats of webalizer vergelijkt (die ik trauwens als basis neem bij het berekenen van de vernoemde percentages) met die van google, geeft google slechts de helft aan (dit is op elk vlak, het gaat hier niet over verschillende interpretaties van een unieke bezoeker bijvoorbeeld). Merk ook op dat dit niet 'een willekeurige helft' is, het is de helft die voldoet aan een bepaalde software-opstelling of instelling.

Toen wij er wat beter naar keken viel het verschil met google analytics wel mee ten opzichte van onze eigen log-parsing. Je moet uiteraard in je log-parsing wel rekening houden met crawlers en ander soortige browsers die niet direct voor een echte bezoeker werken. Als ik domweg alle unieke ip's van afgelopen maandag uit onze access-log tel kom ik op bijna 225k, google analytics vindt dat er zelfs iets meer unieke bezoekers waren die dag... Dus dat is zelfs een afwijking omhoog ipv omlaag.

Daarnaast ben je doorgaans niet heel erg in de losse pageviews geinteresseerd, meer in het unieke bereik en de interesse in bepaalde delen van een site. Hou er iig rekening mee dat je heel snel heel veel data krijgt en dat dat niet erg handig is om per losse pageview of afhankelijk van je interesses zelfs per pagina per dag bij te blijven houden.
Je kan beter proberen zo goed mogelijk te voorspellen welke data er op wat voor manier nodig is om zo zoveel mogelijk data vooraf te aggregeren. Je kan bijvoorbeeld data per pagina per dag aggregeren en dan opnieuw per week en per maand.

Daarnaast moet je heel goed naar je opslag kijken, je noemt bijvoorbeeld een 'int 5', dat bestaat niet echt in MySQL, als je daadwerkelijk niet meer dan 65535 dagen wilt kunnen opslaan moet je een smallint gebruiken. Dan is ie nog maar de helft in opslag.
Verder zou ik geen varchar(16) als identifier gebruiken (ik snap sowieso niet wat dat veld in je tabel moet?), die zijn langzamer dan ints en een heel stuk groter. Als je zo'n veld per se wilt bewaren, kijk dan naar een codering naar bigint toe (twee 32bits ints passen tenslotte in eentje van 64), dan bespaar je een behoorlijke hoeveelheid data per record (11 bytes ofzo).
De kans dat een site op 1 dag op 1 pagina 2 of 4miljard pageviews krijgt is verder nogal klein, dus ook die velden zou ik een stuk compacter maken. Misschien dat je die keuze zelfs kunt laten afhangen van wat instellingen van de klant, als die niet meer dan 100k hits per dag verwacht is het niet echt nodig gelijk al te beginnen met velden die groter dan smallint zijn.

Op mysqlperformanceblog stond pasgeleden ook nog een artikeltje over dit onderwerp.

Je kan overigens ook nagaan of MySQL wel de beste oplossing is. Er zijn diverse alternatieven voor opslag van dergelijke hoeveelheden bulkdata. Een van de oplossingen waar ik wel es naar gekeken heb is een column storage db, zoals monetdb en luciddb. Met name die laatste vond ik veelbelovend.
Daarnaast bestaan er tegenwoordig diverse omgevingen die specifiek bedoeld zijn voor de opslag en analyse van terabytes aan data, zoals bijvoorbeeld hadoop met daarbovenop eventueel weer hbase of hive.

Owja, en een klein puntje, op het moment dat je de uniques per dag per pagina weet, weet je ze nog niet per dag voor de hele site of een deel van de pagina's.

[BCC]

DePhille schreef op zaterdag 10 januari 2009 @ 22:07:
De uitzonderingen zijn inderdaad een goed idee, want ik bedenk me juist dat er ook soms ongewenste data staat in een GET-query die niet geïndexeerd hoeft te worden zoals een SessionID ofzo.

offtopic:
Je weet dat dat een erg groot beveiligings lek is ivm session fixation?

[ Voor 11% gewijzigd door BCC op 11-01-2009 11:54 ]