[mysql] tabel statistieken (views) in extern tabel?

[EricBruggema]

Hoi allemaal,

Ik heb een vraag over statistieken verzamelen pagina's. Veel van mijn dynamische (tabellen) pagina's hebben counters om te zien hoevaak ze zijn bekeken, echter vraag ik mij nu af of het niet beter is deze statistieken in een apart tabel op te slaan... om 2 redenen (denk ik)

1. een statistieken tabel is sneller te updaten
2. heeft voordelen m.b.t geheugen van mysql, aangezien tabellen die voor 'inhoudt' zorgen nu minder geupdated worden.

En ik snap dat voor een website van 10 gebruikers het weinig uitmaakt maar ik denk dat als je meer dan > 1000 bezoekers p/u hebt dit best een voordeel kan zijn..

Jullie idee/mening!

[CodeCaster]

Het maakt natuurlijk geen fluit uit of je hitcounter een kolom is in je artikelentabel of een kolom in een aparte tabel die op artikelID is gekoppeld. Hij zal er niet minder om worden geschreven. Een tabel hoeft ook niet helemaal in het geheugen te worden geladen om een row te updaten.

Je zou wel kunnen kijken naar accumulatie.

[ Voor 25% gewijzigd door CodeCaster op 08-11-2010 21:29 ]

[WouZz]

CodeCaster schreef op maandag 08 november 2010 @ 21:23:
Het maakt natuurlijk geen fluit uit of je hitcounter een kolom is in je artikelentabel of een kolom in een aparte tabel die op artikelID is gekoppeld. Hij zal er niet minder om worden geschreven. Een tabel hoeft ook niet helemaal in het geheugen te worden geladen om een row te updaten.

Je zou wel kunnen kijken naar accumulatie.

Dat maakt wel degelijk uit, afhankelijk van je storage engine. Een write op een MySQL MyISAM tabel resulteert namelijk in een table lock. Andere write requests zullen moeten wachten. Zeker als iedere pageview in een write resulteert krijg je dan dat de requests gaan 'stapelen' bij grote drukte, met vollopende web-servers als gevolg. InnoDB gebruikt row-level locking, dus daar treedt dit probleem minder snel op.

Verder wordt bij het updaten van de tabel de query cache waarschijnlijk steeds gepurged, dus hier heb je dan weinig aan.

Vanwege bovenstaande redenen is het dan ook aan te raden statistieken in een andere tabel bij te houden. Let wel op dat dit vaak ook niet 'slashdot effect proof" is. Het liefst wil je namelijk geen write voor iedere request. Een oplossing is dan het tijdelijk opslaan in bv memcache en deze periodiek uitlezen om de tabel bij te werken.

[cariolive23]

EricBruggema schreef op maandag 08 november 2010 @ 21:15:
En ik snap dat voor een website van 10 gebruikers het weinig uitmaakt maar ik denk dat als je meer dan > 1000 bezoekers p/u hebt dit best een voordeel kan zijn..

1000 bezoekers per uur, dat zijn dan gemiddeld 0,28 bezoekers per seconde. Zelfs op shared hosting moet je toch wel 100 eenvoudige queries per seconde kunnen verwerken, dus waar maak je je druk om?

Uiteraard zijn materialized views mogelijk, maar hier voegen ze niet zo veel toe.

Mocht je performance problemen hebben, ga die dan stuk voor stuk aanpakken. EXPLAIN is je beste vriend.

[CodeCaster]

WouZz schreef op maandag 08 november 2010 @ 21:52:
[...]

Dat maakt wel degelijk uit, afhankelijk van je storage engine. Een write op een MySQL MyISAM tabel resulteert namelijk in een table lock. Andere write requests zullen moeten wachten. Zeker als iedere pageview in een write resulteert krijg je dan dat de requests gaan 'stapelen' bij grote drukte, met vollopende web-servers als gevolg.

En dat los je op door counters in een andere tabel te zetten?

[dtech]

Een beetje een sidetrack: dit soort dingen hoef en wil je eigenlijk helemaal niet zelf te doen. Als je Google Analytics of iets vergelijkbaars gebruikt wordt het allemaal voor je gedaan en heb je veel uitgebreidere statistieken dan je ooit zelf kan schrijven.

Ook is het niet heel erg slim om al je pagina's in tabellen op te slaan, beter is om pagina's gewoon als files te hebben met een templating engine als Smarty (ivm met de scheiding van opmaak/code en de caching die zo'n engine geeft)

Om terug te komen op de originele vraag: Een apart tabel is het beste zoals al uitgelegd is. Eventueel kun je de engine op MEMORY (HEAP) zetten zodat het echt heel snel is.

Oo en als laatste is het niet heel handig om het over views t gaan hebben bij een databasetopic tenzij je echte VIEWs bedoelt

[ Voor 11% gewijzigd door dtech op 08-11-2010 22:06 ]

[EricBruggema]

Bedankt voor jullie reacties, even een paar reacties.

@CodeCaster: Accumulatie? heb je daar goed leesvoer over dat betrekking heeft op mysql/scripting? daar kon ik namelijk weinig tot niets over vinden.

@WouZz: daar zat ik dus ook al mee, je tabellen worden gecached maar moeten steeds opnieuw geladen worden omdat een tabel wordt aangepast. Zelf wil ik per onderdeel / id statistieken bewaren zodat ik kan bepalen welke gegevens ik aan de gebruiker wil laten zien. Ook kan ik dan snel achterhalen welke gebruiker misbruik maakt...

@Cariolive23: tuurlijk maakt het wel uit, het laat je een andere manier van scripten zien zeg maar

@dtech; ik wil GEEN GOOGLE statistieken, jij geeft je boekhouding toch ook niet in zijn totaal aan de belastingdienst? rare vergelijking maar ik hoef google niet van data te voorzien die ik google niet wil geven. En google statistieken werken weer niet als je afbeeldingen serveert of downloads, alleen bij hap klare door de browser leesbare HTML.

Keuze is gemaakt maar vroeg mij af of er nog meer puntjes waren m.b.t. preformance ongeacht of ik nu 1 bezoeker of miljoenen op mijn website krijg!

En had mijn topic inderdaad beter een iets andere naam moeten geven maar goed, blijkbaar was het duidelijk genoeg

[ Voor 5% gewijzigd door EricBruggema op 09-11-2010 09:16 ]

[cariolive23]

EricBruggema schreef op dinsdag 09 november 2010 @ 09:15:
Keuze is gemaakt maar vroeg mij af of er nog meer puntjes waren m.b.t. preformance ongeacht of ik nu 1 bezoeker of miljoenen op mijn website krijg!

Ongeacht of je nu 1 bezoeker of miljoenen bezoekers op je site krijgt? Hoe wil je hier "ongeacht" toepassen? Dit is juist het meest relevante deel van de vraag, samen met het beschikbare budget.

Donald Knuth (computer scientist and winner of the 1974 Turing Award):
Premature optimization is the root of all evil.

Kortom, los alleen de problemen op dit je hebt en ga vooral geen "problemen" oplossen die je helemaal niet hebt. Je kunt niet eens controleren of je deze niet-bestaande problemen wel hebt opgelost. Je gooit dan tijd en geld weg zonder ooit te weten of het wel iets heeft opgeleverd. Er is wel een grote kans dat nieuwe problemen aanmaakt, problemen die je zonder deze "optimalisaties" helemaal niet had.

Ga netjes normaliseren, schrijf nette en logische SQL en ga optimaliseren wanneer dat nodig is. En daarvoor gebruik je EXPLAIN.

[CodeCaster]

EricBruggema schreef op dinsdag 09 november 2010 @ 09:15:
Bedankt voor jullie reacties, even een paar reacties.

@CodeCaster: Accumulatie? heb je daar goed leesvoer over dat betrekking heeft op mysql/scripting? daar kon ik namelijk weinig tot niets over vinden.

Ik neem aan dat je meer wil dan simpele hitcounters. Ik zou daarom een aparte tabel maken voor bijvoorbeeld statistieken per maand of per categorie, zodat je niet je hits iedere keer allemaal bij elkaar op hoeft te tellen.

Al heeft dat weinig met je oorspronkelijke "probleem" te maken, meer een opmerking.

[Verwijderd]

Persoonlijk zou ik het scheiden van elkaar,
naast de argumenten die hierboven al genoemd zijn zoals query-caching en table locking is er ook nog het punt van schaalbaarheid en simpelweg het logisch indelen van je datamodel.

Het veld dat aangeeft hoe vaak een bijvoorbeeld een artikel/pagina bekeken is, maakt niet echt deel uit van de pagina in de zin van logica en dient dus gewoon gescheiden te worden.

[dtech]

"Logisch" indelen is niet erg eenduidig natuurlijk.

Standaard databasetheorie geeft je dat er simpelweg een 1-op-1 relatie is tussen een pagina en het aantal hits op die pagina, wat dus gewoon op een veld zou duiden. Een "logische" indeling zegt dus juist het tegenovergestelde.

offtopic:
Ook een mooie cirkelredenering hoor: Het is logisch dat de hitcounter in een aparte tabel moet want de logica zegt dat dat zo is

[ Voor 22% gewijzigd door dtech op 09-11-2010 12:22 ]

[Verwijderd]

@dtech ... Daar heb je enigszins gelijk in, maar gezien het 2 verschillende entiteiten zijn : pagina(gegevens) en statistieken zou je bij het normaliseren al weer op losse tabellen uitkomen ... tenzij je redeneert dat de hitcount een property is van een pagina en dan zou je dus een veld daarvoor aanmaken ... en dan zijn we weer rond

[drm]

Hoewel je het hier over hitcounters heb, kan ik me voorstellen dat je meer hebt aan een log, omdat je daar meer informatie in kunt stoppen dan alleen "deze row is weer 's een keertje opgehaald".

Het kan dan de moeite waard zijn INSERT DELAYED queries te gebruiken op een aparte logtabel. MySQL verwerkt die queries met lage prioriteit en in een aparte thread, zodat de client niet hoeft te wachten totdat de insert klaar is. Dat maakt de schaalbaarheid ook eenvoudig, want je kunt dan je hele logging systeem in één keer naar een andere databaseserver verplaatsen, mocht dat nodig blijken.

Je moet alleen wel realiseren dat INSERT DELAYED ook grote nadelen heeft, je kunt die nalezen in de manual. Desalniettemin de moeite waard om even te bekijken cq te beoordelen.

[Gomez12]

Waar ik meestal voor kies is een combinatie van log + statistieken.

Ik heb een droge log zonder uitzonderlijke indexen en dergelijke ( zo af en toe als het heel groot wordt kan het ook buiten de db staan ), hier valt supersnel iets aan toe te voegen.
En dan 1x per x tijd een cronjob die uit de log de statistieken perst ( per uur / dag / app etc ).

Het log wil je ook bijna nooit openen ( is traaaaaag ) en is enkel voor incidenten, en zelfs met een incident haal je er liever een export uit dan dat je direct met de raw data werkt.
Statistieken zijn dan wel snel te benaderen.

Voordeel hiervan is dat als je eenmaal het log goed en volledig opstelt dat je als je iets nieuws wilt meten dat je dan enkel je statistieken opnieuw hoeft op te bouwen. Alle data is al aanwezig in je log.
Logs kan je dan desgewenst ook weer periodiek archiveren ( je statistieken zijn dan namelijk al gemaakt )