[mssql/dbdesign] index tabel voor preformance

[foske]

goedenavond,

Ik vond hiervoor een pakkende titel bedenken beetje moeilijk, dus ik zal hem maar even uitleggen

De situatie:
Ik registreer bezoekers op mijn site, en iedere nacht worden de log tabellen verwerkt tot presentatie tabellen. Op dit moment zit er op deze log tabellen de alle indexes om het script wat 's nachts draait ook nog een beetje vlot te laten lopen. Enige minpunt, het wegschrijven van een bezoeker duurt dus ook langer. In mijn ogen te lang. Dus toen kwam ik met het idee:

- Ik haal alle indexes af van mijn log tabellen
- Maak een extra tabel (of temp tabel), ziet er hetzelfde uit, alleen nu met alle indexes
- Deze wordt gevuld met een bulk copy (of iets, moet ik nog uitzoeken, wil iig niet het log belasten, want de gegevens zijn toch dubbel, )
- als ie klaar is truncate table

De vraag die nu bij mij naar boven zijn vooral:
Is dit slim?

bijvoorbaat dank

[vinnux]

Mijn inziens moet het geen probleem zijn dat de stats generen "lang" duurt in het midden van de nacht als iedereen op één oortje ligt. Het is inderdaad slim om een platte structuur te maken om er zo voor te zorgen dat de pagina's snel verschijnen en geen hinder ondervinden van de teller.
Echter kan ik me niet voorstellen dat de performence winst gigantisch is aangezien je elke nacht de tabel truncate en daarmee het aantal rijen beperkt houd.

Daar ik geen diepe kennis heb van mssql hier enkele ideeën die misschien niet/wel mogelijk zijn:
- Memory tabbellen, tabel direct in het geheugen.
- Een indexed vieuw maken
- Stored procedure maken voor creeën statestieken, executie plan maken kost zo minder tijd
- Vast een stel lege rijen maken en deze met een stopro later vullen

Daarbij is het ook de verwachting dat jouw manier langer gaat duren dan gewoon de queries los the laten op de platte tabel. Dit omdat je in principe een hele tabel opnieuw opbouwt en de dag naspeelt. De indexes dienen dan allemaal opgebouwt te worden en dat kost meer tijd denk ik dan er een stuk of wat select's op los te laten. Maar dit ligt natuurlijk aan de verhouding van de INSERTS en de SELECTS ie gedaan moeten worden. Meer performence winst valt te halen in het optimaliseren van de stats queries.

[ Voor 67% gewijzigd door vinnux op 02-02-2004 23:28 ]

[foske]

Er komen ook steeds meer buitenlandse bezoekers (Amerika, die komen dus "s'nachts"), ik wil het script zo snel mogelijk klaar hebben om de server neit te veel te stressen. Ook stijgt het aantal bezoekers steeds meer, dus ik denk dat indexes erop wel nuttig kunnen zijn.

edit: mmm dat de indexes opnieuw moeten worden opgebouwd, heb je wel gelijk in, dat zou idd het nut weer kunnen wegdrukken...

Ik ben wel benieuwd naar wat een goede keus is qua bulk insert of temp tables.

edit:

vgouw schreef op 02 februari 2004 @ 23:27:
Hier stond per ongeluk het vorige bericht gequote.

tis al laat


edit3:
Ik ga denk gewoon eens testen wat er gebeurd als ik wel 1 tabel hou, maar alle indexes hiervan verwijder. Kijken of de preformance dan nog acceptabel is.

Want idd, tis misschien wat dubbelop en de winst maar minimaal..

[ Voor 53% gewijzigd door foske op 02-02-2004 23:36 ]

[Gomez12]

En als je jou idee nou eens in het uur doet??? Weet niet hoeveel pageviews / logregels je krijgt. Maar 1x per uur parsen betekent dat je users 1x per uur een performancedaling hebben ipv elke dat er iets in een logfile wordt weggeschreven.

[foske]

1x per uur, ik heb er wel aan zitten denken, maar ik had daar een rede voor om het niet te doen, maar wat was het ook alweer

[pistole]

1 van mijn sites registreert dagelijks zo'n 350k hits.
In het verleden had ik daar veel performanceproblemen op doordat er geen (goede) indices op waren gemaakt (en elk uur een update van de stats).

Enige tijd geleden ben ik overgegaan naar een vergelijkbaar systeem zoals je het omschrijft, maar dan verdeeld over twee servers:

-logging server registreert alle hits; heeft alleen een PK om elke hit afzonderlijk aan te spreken
-records worden elke 15 minuten gekopiëerd naar de rapportage server (waarop alle belangrijke indices zitten)
-op logging server wordt elke nacht een opschoonaktie gestart (gekopiëerde records worden verwijderd.

Dit leverde een aanzienlijke performancewinst, dus ja: lijkt me een goed plan

Op dit moment is de database van de rapportage server zo'n 23GB, en de front-end server slechts 400MB... performance is toppie

[ Voor 10% gewijzigd door pistole op 02-02-2004 23:49 . Reden: verdeelt is met een d in dit geval ]

[foske]

23gb das aardig wat , maak jij geen gebruik van berekende totalen? (per dag bijvoorbeeld) ik weet niet hoe snel het nu duurt bij jou om bijvoorbeeld je totale bezoeker aantal op te vragen, maar als je dan alle hits moet tellen, zal dat wel lang duren denk ik?

[pistole]

Het is een systeem van een klant (ik heb voornamelijk technische input geleverd, en geholpen met het systeem te optimaliseren), maar het komt grofweg neer op de volgende constructie:
-per uur worden achtereenvolgens uur- en dagstatistieken gemaakt
-per uur wordt bijgewerkt wat een user in een sessie heeft gedaan (uur- en dagdetail)

En vervolgens, iedere nacht, waarbij overige jobs (zoals datacopy uit wordt gezet)
-dagtotalen
-weektotalen
-maandtotalen
-jaartotalen

Alles wordt in "presentatietabellen" gezet, deze worden vervolgens gebruikt om de gegevens aan de klanten te presenteren; nooit direct op de enorme logtabel.
Aangezien het hier om een database gaat die meerdere websites van meerdere klanten bijhoudt, wordt in principe nooit een count(*) o.i.d. gedaan; tenzij de DBA (ik dus) nieuwsgierig is. Een dergelijke query doet ie vooral op de (PK)Index; en kan inderdaad een minuutje duren.

[EfBe]

Overzichten gegenereerd over veel data kan altijd veel tijd kosten. Je kunt je rapportage data echter wel opdelen in 'actief' en 'historisch' bv. Hierdoor splits je de data op over meerdere catalogs. De catalog met de grote historische hap is erg traag, maar wie een overzicht wil zien met 2 jaar oude logdata moet er maar wat voor over hebben (en tenslotte komen dat soort overzichten nauwelijks voor, wat is immers de semantische waarde van 2 jaar oude logdata)

De actieve database is dan kleiner en overzichten die vaker worden gegenereerd, dus de overzichten van 'vandaag', 'deze week' en 'deze maand' zijn dan stukken sneller.

[justmental]

Als je een vaste hoeveelheid log bewaart wil het ook nog wel eens lonen om de indexen er alleen op te zetten voor de batch.
Dus indexen maken-batch-indexen droppen.
Zo heb je altijd 'mooie' indexen tijdens je batch en geen verlies in je transacties.