Database voor statistieken

[Keiichi]

Op een webpagina van mij houd ik eenvoudige statistieken bij. De enige statistieken die ik zelf bijhoudt zijn het aantal opvragingen van een profielpagina van een gebruik van de site.

Dit doe ik nu in MySQL (InnoDB) met een tabel met de kolommen: id (autoincrement), gebruikers id, jaar+weeknummer, aantal opvragingen.
Indexes van deze tabel zit op het id en het gebruikers id. (De ID kolom is wellicht overbodig). Het gebruikers ID is tevens een FK naar de gebruikerstabel.

Ik heb het vermoeden dat deze strategie niet het beste is. Het ophalen van data met de gebruikers ID gaat gewoon vlot en het invoeren ook, al zou dit in theorie steeds langzamer moeten gaan als heel de index tree opnieuw gebouwd moeten worden.

Is dit een goede, schaalbare strategie of zijn er manieren die ik niet ken die dit veel beter doen?

[NMe]

Waarom bouw je dit überhaupt zelf in plaats van gebruik te maken van AWStats of iets vergelijkbaars?

En waarom vermoed je dat dit niet de beste oplossing is? Waar is dat vermoeden op gebaseerd?

[Keiichi]

Met awstats en google analytics heb ik wel website statistieken, maar hieruit kan ik geen overzicht van 1 specifieke pagina waar er weer een paar honderdduizend van zijn per tijdsperiode. (zie het als de profielviews op een tweakersprofiel, maar dan per week onderverdeeld)

Ik vermoed dat het geen beste oplossing is omdat de totale hoeveelheid data ten opzichte van de rest van de data groot is.

[NMe]

Normaal gesproken ga je bij het bijhouden van statistieken groeperen op logische breekpunten. Dat jij houdt per pagina per week bij hoe vaak een pagina bekeken is. Als je die data op dat niveau beschikbaar wil houden kun je niet verder optimaliseren zonder gegevens op te offeren.

[GlowMouse]

Keiichi schreef op zaterdag 28 december 2013 @ 23:43:
(De ID kolom is wellicht overbodig).

Ga je dat nog uitzoeken, of hoop je dat iemand hier het antwoord noemt? Want dubbele indexen aanleggen is zonde van de resources.

Of de methode goed is, hangt af van welke statistieken je wilt tonen, hoeveel bezoekers je hebt, en hoevaak je statistieken opvraagt.

_{"houd ik"}

[NMe]

GlowMouse schreef op zondag 29 december 2013 @ 00:23:
[...]

Ga je dat nog uitzoeken, of hoop je dat iemand hier het antwoord noemt? Want dubbele indexen aanleggen is zonde van de resources.

Compound primary keys en MySQL gaan niet altijd even goed samen, bijvoorbeeld als je op het tweede veld uit die key wil zoeken. Ik weet zo even niet meer of dat een MyISAM-dingetje is of dat InnoDB er ook last van heeft, maar als je wil zoeken op een deel van de key dat niet het eerste veld is moet je alsnog een extra index aanmaken op dat tweede veld. IMO kun je dan beter een extra ID-veld opnemen om te voorkomen dat je die index vergeet en er pas later achterkomt als er iets totaal niet performant blijkt.

[Keiichi]

GlowMouse schreef op zondag 29 december 2013 @ 00:23:
[...]

Ga je dat nog uitzoeken, of hoop je dat iemand hier het antwoord noemt? Want dubbele indexen aanleggen is zonde van de resources.

De index hiervoor 'nodig' voor een ORM dat ik gebruik, een optimalisatie slag hiervan is het lostrekken van deze data uit het ORM en deze kolom laten vallen

Of de methode goed is, hangt af van welke statistieken je wilt tonen, hoeveel bezoekers je hebt, en hoevaak je statistieken opvraagt.

De statitistieken zijn profielopvraging per week.
Het opvragen gebeurd weinig. Pak 'm beet 100x per dag, het updaten van de data ca 6000x per dag.

De situatie is op het moment hanteerbaar, maar ik wil altijd weten hoe het beter kan en als het even voorkomen dat ik stomme dingen ontwerp die niet houdbaar zijn als het echt grootschalig zou worden.

[RobIII]

NMe schreef op zondag 29 december 2013 @ 00:19:
Als je die data op dat niveau beschikbaar wil houden kun je niet verder optimaliseren zonder gegevens op te offeren.

Je kunt nog naar partitioning kijken. En dan wel liefst zo transparant mogelijk (en dus niet in je applicatielaag of op tabelniveau een "poor man's partitioning" maar gewoon partitioning features van je RDBMs gebruiken mits aanwezig, wat voor MySQL in ieder geval het geval is).

NMe schreef op zondag 29 december 2013 @ 00:32:
bijvoorbeeld als je op het tweede veld uit die key wil zoeken

Correct me if I'm wrong, maar dat werkt AFAIK in geen enkel RDBMs "optimaal". Zie 't alsof je op zoek bent naar een "Janssen, R.P." in de (papieren, analoge, ouderwetse) telefoongids ergens in Nederland. Als je alle telefoonboeken op een stapel legt zul je in ieder geval per "woonplaats" de Janssen's moeten gaan doorspitten (de index is dan namelijk woonplaats, achternaam_{, voorletter, etc. ...}). Het is nog steeds vele malen sneller dan een stapel gidsen doorspitten waar de sortering op telefoonnummer is gedaan, maar 't zal nog altijd onder doen voor een stapel gidsen waar de sortering op achternaam heeft plaatsgevonden (een hele lange lijst van A-Z dus op achternaam, ongeacht woonplaats).

Die auto-increment ID heeft echter totaal geen toegevoegde waarde (zolang je records niet op basis van dit ID benaderd althans of er andere dingen mee doet die je achterwege hebt gelaten in je topicstart). Mikker die dus weg als 't even kan. Als je ORM zo eigenwijs is er toch een auto-inc veld in te willen hebben: boeie, maar drop gerust die index _{en evt. constraints e.d.} (scheelt weer insert performance want je moet een index minder bijwerken) en zet alleen een index op gebruikers-id, jaarweeknummer er van uit gaande dat je voor gebruiker X een grafiekje wil kunnen plotten met "hits" als datapunten per week.

[ Voor 15% gewijzigd door RobIII op 29-12-2013 00:50 ]

[dcm360]

Mocht je alsnog willen overwegen om bestaande software te gebruiken, ik heb zojuist de volgende grafiek voor een bepaalde pagina uit Piwik weten te toveren:

[Zoijar]

Gewoon voor elke page view een record opslaan. Index op je user id. Counter eroverheen. Eventueel een hash van je cononical url met een index daarop helpt ook nog wel eens. Miljoenen records is geen enkel probleem voor een moderne database.

[m-m]

Zoijar schreef op zondag 29 december 2013 @ 10:26:
Gewoon voor elke page view een record opslaan. Index op je user id. Counter eroverheen. Eventueel een hash van je cononical url met een index daarop helpt ook nog wel eens. Miljoenen records is geen enkel probleem voor een moderne database.

^ dat ja, een tellertje bijhouden in je database is over het algemeen niet handig. Eventueel kan je op een vast moment records van de vorige tijdsperiode verhuizen naar een archief-tabel waar je wel gewoon het aantal opslaat (wijzigt dan immers niet meer), maar dikke kans dat dat niet nodig gaat zijn.

Zoals je het nu doet moet je 'n transactie starten, je row locken, huidige stand opvragen, verhogen, updaten, transactie committen. Alleen een row inserten is sneller en als je je indices goed hebt staan is een count ook super rap. Voordeel is dat je ook eventuele extra informatie over de hit kunt opslaan, bijvoorbeeld welke user het profiel bekeek. En als je gewoon het tijdstip opslaat kun je ook views over een custom periode ipv alleen weeknummers berekenen.

Maar als het je echt puur om schaalbaarheid van een simpel tellertje gaat zou je Redis kunnen overwegen. Het INCR-commando is hier zo ongeveer voor gemaakt, geeft je geen concurrency gezeik. Je kan per user per tijdsperiode een key maken en die ophogen/opvragen. Je moet alleen wel zelf de juiste tradeoff tussen snelheid en risico op dataverlies maken in de configuratie en zorgen dat je geen inconsistente data krijgt (een andere db als redis heeft immers geen idee of een userid wel/niet bestaat). En als je echt moet schalen kun je die keys weer gaan sharden tussen verschillende instanties.

Maar goed, dat is een overkill-oplossing hoor, maar je vroeg naar schaalbaarheid dus ik noem 't even

[ Voor 0% gewijzigd door m-m op 29-12-2013 11:03 . Reden: t is nog vroeg ]

[francoski]

Ik zou voor stats tabellen sowieso geen InnoDB gebruiken maar MyISAM. Je gaat die data nooit updaten ofzo, enkel maar een dom insert statement en wat statistieken er van genereren. Dus relaties doen er niet zo toe.

[Zoijar]

"Op een webpagina van mij houd ik eenvoudige statistieken bij." I.e.: het maakt allemaal niet zo veel uit. Sla het op zodat je zoveel mogelijk vrijheid hebt. Als er nou stond "op een grote website met honderd miljoen+ hits per dag..." Programmeurs zijn vaak veel te lang bezig met dingen die er totaal niet toe doen.

[creator1988]

Inderdaad, voor een oud-werkgever deed ik 70 miljoen van dit soort records per dag en dat ging gewoon SQL server in. Je moet een beetje slim zijn qua batchen van je transacties en partitioneren, maar dit soort dingen zijn geen probleem. Wel zorgen dat je index'en precies goed zijn en ze in RAM passen.

[Keiichi]

Op basis van iedereens reactie heb ik wel wat wijzigingen doorgevoerd.

- Primary key gedropped op de stats-tabel en deze tabel geheel uit het ORM gehaald.
- De 'hits' in een soort van buffer tabel opgeslagen. Een cron elke x-minuten leest deze uit en update de stats-tabel.

Een directe update op de stats-tabel kostte ongeveer 50 tot 100ms, met opslag in een vrijwel lege buffer tabel gaat dit stukje sneller en de stats worden nu iets later geupdated wat eigenlijk geen probleem is

In de tussentijd ben ik ook wat ervaring met Redis aan het opdoen

@Zoijar Als ik door een baas betaald zou krijgen, zou ik inderdaad hier geen tijd aan moeten besteden en gewoon denken 'goed is goed genoeg'. Ik denk graag over alles na. Stel dat in de toekomst met projecten aan de slag moet met websites van 100+ miljoen hits, dan hoop ik niet dat alles me onbekend is.