[MSSQL] invloed query op server testen

[PdeBie]

Wij hebben momenteel een vrij simpele query draaien op onze live omgeving die behoorlijk vaak wordt aangeroepen. Deze query wordt minimaal elke 3 minuten voor elke gebruiker eenmaal aangeroepen met (voor nu) een max van 1300 gebruikers. Kortweg doet deze query niks anders dan select count(*) from tabel...... Dit getal wordt weergegeven op de .NET webapplicatie van de gebruiker.

Nu wil ik graag testen wat voor invloed dit heeft op onze server. Dus ik wil deze query bijvoorbeeld 600x in een tijdspan van 10 seconden draaien en dan kijken wat dit aan processor load etc. heeft opgeleverd.

Hoe kan ik dit meten? SQL profiler? Of waarmee?
En hoe kan ik deze query ook daadwerkelijk op deze manier laten draaien? Zijn hier trucjes voor? Of moet ik gewoon een while lus bouwen in SQL?

--edit--
De applicatie draait al live, maar deze functie staat uitgeschakeld. Dus nu wil ik op SQL niveau deze query testen om te kijken wat dit aan performance kost. Wij hebben het vermoeden namelijk dat deze query problemen oplevert op de server. Kunnen we het live gebruik simuleren? Zijn hier trucjes voor? Of moet ik gewoon een while lus bouwen in SQL?


** -- alternatief -- **
Wij zijn tevens over een alternatief aan het nadenken waarin wij op de server een tabel vullen met deze getallen voor de gebruikers en dat de gebruiker maar 1 select hoeft uit te voeren op die tabel waar zijn waarde in staat. Wat is jullie mening hierop?

[ Voor 14% gewijzigd door PdeBie op 02-01-2013 10:57 ]

[Woy]

Kan je niet eerst eens gewoon een EXPLAIN van de query op de server doen? Dat geeft al een goed eerste beeld. Verder kan je natuurlijk gewoon een backup van de database maken en het op een andere server testen. Natuurlijk niet 100% vergelijkbaar met je productieserver, maar het zou wel een goede indruk moeten geven.

[PdeBie]

OK, die EXPLAIN ga ik eens proberen.
Nog nooit gebruikt.

--edit--
Ok, query execution plan heb ik nu gezien. Hoe kan ik het nu het handigste simuleren dat deze query X keer wordt aangeroepen in een korte tijdspan? While loop?

[ Voor 59% gewijzigd door PdeBie op 02-01-2013 11:37 ]

[frG]

Misschien een beetje overkill, maar als dat getal zo belangrijk is zou je kunnen kijken naar een oplossing met SignalR, en push je het getal naar de client zodra het veranderd.

[TheNephilim]

Je zegt minimaal elke 3 minuten, bedoel je dat het soms binnen 3 minuten kan voorkomen?

[n0fragger]

TheNephilim schreef op woensdag 02 januari 2013 @ 13:07:
Je zegt minimaal elke 3 minuten, bedoel je dat het soms binnen 3 minuten kan voorkomen?

Volgens mij bedoelt hij 1300 keer per 3 minuten

Ik weet niet hoe, maar ik zou het anders oplossen

[ Voor 3% gewijzigd door n0fragger op 02-01-2013 13:14 ]

[RobIII]

n0fragger schreef op woensdag 02 januari 2013 @ 13:13:
Ik weet niet hoe, maar ik zou het anders oplossen

Beetje denormaliseren kan een mogelijke optie zijn, net als 1001 andere dingen (memcached er tussen stoppen, partitioning, materialized views ... you name it). Maar zonder meer specifieke info kunnen we hier weinig zinnigs over zeggen. Voor 'tzelfde geld haalt SQL z'n info uit de statistics als er bijv. de juiste indices gebruikt worden en kost je query geen drol. Iets met premature en optimization enzo In the end geldt gewoon: meten == weten.

[ Voor 17% gewijzigd door RobIII op 02-01-2013 13:33 ]

[PdeBie]

n0fragger schreef op woensdag 02 januari 2013 @ 13:13:
[...]


Volgens mij bedoelt hij 1300 keer per 3 minuten

Ik weet niet hoe, maar ik zou het anders oplossen

Juist. 1300 gebruikers elke 3 minuten 1x minimaal.

Het getal is het aantal nieuwe orders wat een gebruiker heeft staan. Op de pagina staat een teller die elke 3 minuten ververst. Deze teller controleert dan met de select query of er nieuwe orders staan en geeft het aantal nieuwe orders weer. Dit is niks anders dan een select count op één order tabel.

SQL:

-- @receiver == ID van de ingelogde gebruiker
-- status 1 == nieuwe order
-- status 3 == nieuwe faxorder (binnen gekomen via de fax ipv via internet)
select count(*) from orderhead
where receiver = @receiver and (status = 1 OR status = 3)

Deze query wordt dus voor elke gebruiker minimaal 1x uitgevoerd per 3 minuten.
En we hebben maximaal 1300 gebruikers tegelijk op dit scherm.

Daarnaast heb ik dus al een alternatief aangegeven waar we aan zaten te denken.
Een job op de server die een tabel genereert/vult met daarin de aantallen. Bijvoorbeeld:

ReceiverID	Amount
1269	5
1270	3

Dan hoeft de gebruiker alleen maar 'select amount from tabel' te doen ipv een count.

[TheNephilim]

Zolang ReveiverID een index heeft, lijkt me deze query niet zoveel werk. ReceiverID + status in een index moet het beste zijn, al weet ik niet helemaal zeker hoe dat precies werkt.

EXPLAIN met index op de hiervoor genoemde tabellen moet meer duidelijkheid bieden. Echter, denk ik, kan dit geen hele zware query zijn toch. Als er nog meer WHERE statements bij komen wil dat nog wel eens lastiger zijn, maar dit lijkt me geen probleem.

Maar meten is weten..!

[Woy]

Met een index op receiver ( en eventueel status afhankelijk van hoeveel records er van een receiver zijn met verschillende statussen ) zou het totaal geen zware query moeten zijn lijkt me.

[ Voor 29% gewijzigd door Woy op 02-01-2013 13:36 ]

[HeSitated]

pdebie schreef op woensdag 02 januari 2013 @ 13:27:
Daarnaast heb ik dus al een alternatief aangegeven waar we aan zaten te denken.
Een job op de server die een tabel genereert/vult met daarin de aantallen. Bijvoorbeeld:

Dan hoeft de gebruiker alleen maar 'select amount from tabel' te doen ipv een count.

Maak het aub niet nog erger door dit soort zaken via een job te gaan bij houden....

Wat ik zou doen is een tabel aanmaken met daarin de openstaande orders per medewerker.
Bij de insert van de order ook een record aanmaken in deze nieuwe tabel en bij wijziging van status dit record verwijderen.

Het liefst niet via een trigger, maar bij de code of sp die je insert updates afhandeld, maar dat is een keuze die jullie zelf maar moeten maken.

Woy schreef op woensdag 02 januari 2013 @ 13:35:
Met een index op receiver ( en eventueel status afhankelijk van hoeveel records er van een receiver zijn met verschillende statussen ) zou het totaal geen zware query moeten zijn lijkt me.

Wel als er 1300 gebruikers zijn en de tabel frequent wordt bijgewerkt... Zeker het statusveld wordt dan regelmatig bijgeweerkt en kan door locking vertragend werken.

[ Voor 22% gewijzigd door HeSitated op 02-01-2013 13:39 ]

[PdeBie]

Woy schreef op woensdag 02 januari 2013 @ 13:35:
Met een index op receiver ( en eventueel status afhankelijk van hoeveel records er van een receiver zijn met verschillende statussen ) zou het totaal geen zware query moeten zijn lijkt me.

ik heb een index op receiver + status staan. (en ook een op sender + status. De sender is het afzenderID)
Als ik de query 1x draai voor 1 persoon vliegt hij er uiteraard doorheen. Dat kost 4/100 van een seconde.
Maar 1300x tegelijk lijkt hij toch niet prettig te vinden en dat is dus wat ik wil testen wat het effect van 1300x tegelijk aanroepen is.

-- extra info --
De kolom status veranderd per order een aantal keer. Bij het bevestigen van een order (status 2), bij het retourneren (status 5) en bij het afhandelen (status 20). Dit zijn de belangrijkste en meest voorkomende statussen.
De receiver veranderd niet vrijwel nooit per order regel. Er komen wel tientallen tot honderden order regels per dag bij.

-- edit --

HeSitated schreef op woensdag 02 januari 2013 @ 13:36:
[...]
Wat ik zou doen is een tabel aanmaken met daarin de openstaande orders per medewerker.
Bij de insert van de order ook een record aanmaken in deze nieuwe tabel en bij wijziging van status dit record verwijderen.

Maar dan krijg je toch alsnog 1300x een select count, maar dan op een andere tabel?

[ Voor 43% gewijzigd door PdeBie op 02-01-2013 14:03 ]

[MueR]

Persoonlijk zou ik niet per receiver queries gaan draaien, maar dit in je serverside applicatie voor alle receivers tegelijk doen en fijn cachen, zeker met 1300 gebruikers.

[HeSitated]

pdebie schreef op woensdag 02 januari 2013 @ 13:41:
ik heb een index op receiver + status staan. (en ook een op sender + status. De sender is het afzenderID)
Als ik de query 1x draai voor 1 persoon vliegt hij er uiteraard doorheen. Dat kost 4/100 van een seconde.
Maar 1300x tegelijk lijkt hij toch niet prettig te vinden en dat is dus wat ik wil testen wat het effect van 1300x tegelijk aanroepen is.

Maar heb je een probleem qua performance? Welke versie van sqlserver gebruiken jullie?

Want wil je dit soort zaken echt tackelen, als er echt een probleem is, dan moet je het eerst een dag nog erger maken door op de server en de profiler en windows performance counters te laten lopen....

Met die twee gecombineerd kun je pas echt zeggen wat het probleem is...

-- extra info --
De kolom status veranderd per order een aantal keer. Bij het bevestigen van een order (status 2), bij het retourneren (status 5) en bij het afhandelen (status 20). Dit zijn de belangrijkste en meest voorkomende statussen.
De receiver veranderd niet vrijwel nooit per order regel. Er komen wel tientallen tot honderden order regels per dag bij.

Voor mij een reden om bij mijn bovenstaande voorstel te blijven: is meer werk bij insert en de updat waarbij de status niet meer 1 of 3 is, maar het aantal records in de tabel is veel kleiner.

[PdeBie]

HeSitated schreef op woensdag 02 januari 2013 @ 13:54:
[...]

Maar heb je een probleem qua performance? Welke versie van sqlserver gebruiken jullie?

Want wil je dit soort zaken echt tackelen, als er echt een probleem is, dan moet je het eerst een dag nog erger maken door op de server en de profiler en windows performance counters te laten lopen....

Met die twee gecombineerd kun je pas echt zeggen wat het probleem is...

We hebben inderdaad een performance probleem, dus we zijn aan het kijken waar we winst kunnen boeken.
We maken gebruik van MS SQL 2008 R2.

De profiler en performance tellers draaien al en zijn we aan het bekijken.

[v-god]

Wat is de tabeldefinitie? (inclusief indexes)
Hoeveel records verwacht je erin?
Wat is de Cost van de query in het execution plan?
Wat is de serverspecificatie?
Is de Live server dedicated voor de live database?
Kan je het execution plan ergens posten (mag in xml)?

[ Voor 11% gewijzigd door v-god op 02-01-2013 14:38 ]

[Gomez12]

pdebie schreef op woensdag 02 januari 2013 @ 14:06:
[...]
We hebben inderdaad een performance probleem, dus we zijn aan het kijken waar we winst kunnen boeken.
We maken gebruik van MS SQL 2008 R2.

De profiler en performance tellers draaien al en zijn we aan het bekijken.

Maar heb je ook echt een goede indicatie dat deze query de boosdoener is (lijkt mij niet namelijk).

Maar de invloed van de query op de server testen doe ik over het algemeen heel simpel :
- Maak een simpele php/asp/nodejs pagina die de actie uitvoert
- Zoek een apache stresstester en richt die op die pagina met 1300 concurrent uses.

En qua oplossing zou ik het niet al te fancy uitzoeken, zorg gewoon dat je server die query kan cachen en je probleem is weg. Een doodsimpele query als dit die 1300x per 3 minuten uitgevoerd wordt moet sql-server gewoon kunnen cachen.

[PdeBie]

We hebben het control welke de query uitvoert van de pagina verwijderd en zagen de processor-load behoorlijk inzakken. We vermoeden dat de performance issues niet enkel door deze query komen, maar wel dat het één van de boosdoeners is. Een combinatie van diverse zaken zeg maar, die bij elkaar opgeteld het systeem zo vertragen.

[Grijze Vos]

Hoe vaak updaten die 1300 klanten die order tabel? Vergeet niet dat indices relatief duurd zijn als er veel mutaties plaatsvinden op je tabel. Ga eens serieus met een profiler aan de slag om te kijken wat nou echt de dure queries zijn, en ga die eerst optimaliseren..

[v-god]

Kan je niet eens gewoon de tabeldefinitie en het execution plan posten?
Ik onderhoud best wel wat sql servers, en ofwel is je server een pentium 3 ofwel moeten die 13000 users gewoon die query kunnen draaien zonder problemen.

Hoeveel andere queries gebruik je om uw pagina op te bouwen? Dat zullen er flink wat meer zijn dan die ene, en die worden even vaak uitgevoerd.

Een eenvoudige select count(*) zou helemaal geen probleem mogen zijn als uw indexes correct zijn, uw statistics up to date en de indexes overgefragmenteerd zijn, Tenzij je tegen een heel specifiek probleem aanloopt.

Mijn gok is dat je design fout zit.

[v-god]

Gomez12 schreef op woensdag 02 januari 2013 @ 22:16:
[...]

Maar heb je ook echt een goede indicatie dat deze query de boosdoener is (lijkt mij niet namelijk).

Maar de invloed van de query op de server testen doe ik over het algemeen heel simpel :
- Maak een simpele php/asp/nodejs pagina die de actie uitvoert
- Zoek een apache stresstester en richt die op die pagina met 1300 concurrent uses.

En qua oplossing zou ik het niet al te fancy uitzoeken, zorg gewoon dat je server die query kan cachen en je probleem is weg. Een doodsimpele query als dit die 1300x per 3 minuten uitgevoerd wordt moet sql-server gewoon kunnen cachen.

Alleen heb je fout hoe sql cache werkt. SQL Cached geen resultaten maar "pages" (laten we om het simpel te houden dit als een gedeelte van de tabel bekijken van 8K) De query wordt gewoon opnieuw op exact dezelfde manier uitgevoerd de 2e keer, maar dan hoeft ie de pages niet van disk te halen (logical reads ipv physical reads ga je dan zien in het execution plan)

[v-god]

pdebie schreef op donderdag 03 januari 2013 @ 10:03:
We hebben het control welke de query uitvoert van de pagina verwijderd en zagen de processor-load behoorlijk inzakken. We vermoeden dat de performance issues niet enkel door deze query komen, maar wel dat het één van de boosdoeners is. Een combinatie van diverse zaken zeg maar, die bij elkaar opgeteld het systeem zo vertragen.

Kijk ook even je disk queue length na op je datadisks. Een hoge wachtrij voor I/O vertraagt de boel over het algemeen een stuk meer dan een hoge cpu load (afhankelijk van wat je hoog noemt)

In de versie van SQL die jij hebt kan je rechts klikken op de server en activity monitor doen. Daar staat een blok met wait types, geef eens aan welke wait type je ziet oplopen als je de control aan hebt.

[PdeBie]

Inmiddels hebben we de crashdumps ook kunnen bekijken en zien dat deze query inderdaad ontzettend vaak aangeroepen wordt.

Echter is de reden dat hij zo vaak aangeroepen wordt niet direct af te leiden aan het aantal gebruikers en lijkt het dus meer op een lus (of recursieve functie) ergens die blijft hangen. Hier ben ik nu naar aan het kijken middels de logging in het systeem iets uit te breiden. Daarbij zag ik inderdaad een lus ontstaan. Nu nog ontdekken wanneer dit optreedt en vooral ook 'waarom?'.

[ Voor 18% gewijzigd door PdeBie op 03-01-2013 16:27 ]

[TheNephilim]

pdebie schreef op donderdag 03 januari 2013 @ 16:23:
Inmiddels hebben we de crashdumps ook kunnen bekijken en zien dat deze query inderdaad ontzettend vaak aangeroepen wordt.

Echter is de reden dat hij zo vaak aangeroepen wordt niet direct af te leiden aan het aantal gebruikers en lijkt het dus meer op een lus (of recursieve functie) ergens die blijft hangen. Hier ben ik nu naar aan het kijken middels de logging in het systeem iets uit te breiden. Daarbij zag ik inderdaad een lus ontstaan. Nu nog ontdekken wanneer dit optreedt en vooral ook 'waarom?'.

Even een andere invalshoek; is de tabel wel InnoDB? Anders zou het kunnen zijn dat je last hebt van table-locks waarop de grote hoeveelheid SELECT's steeds moet wachten.

In MyISAM locked de gehele tabel bij een schrijfactie op één row en dan moeten dus alle acties -die op dezelfde of een andere row plaatsvinden- wachten tot die schrijfactie klaar is. Schrijfacties duren langer omdat indices aangepast moeten worden en dergelijke.

[RobIII]

TheNephilim schreef op vrijdag 04 januari 2013 @ 17:07:
[...]


Even een andere invalshoek; is de tabel wel InnoDB? Anders zou het kunnen zijn dat je last hebt van table-locks waarop de grote hoeveelheid SELECT's steeds moet wachten.

In MyISAM locked de gehele tabel bij een schrijfactie op één row en dan moeten dus alle acties -die op dezelfde of een andere row plaatsvinden- wachten tot die schrijfactie klaar is. Schrijfacties duren langer omdat indices aangepast moeten worden en dergelijke.

MSSQL doet niet aan InnoDB en MyISAM toestanden

[PdeBie]

pdebie schreef op donderdag 03 januari 2013 @ 16:23:
Inmiddels hebben we de crashdumps ook kunnen bekijken en zien dat deze query inderdaad ontzettend vaak aangeroepen wordt.

Echter is de reden dat hij zo vaak aangeroepen wordt niet direct af te leiden aan het aantal gebruikers en lijkt het dus meer op een lus (of recursieve functie) ergens die blijft hangen. Hier ben ik nu naar aan het kijken middels de logging in het systeem iets uit te breiden. Daarbij zag ik inderdaad een lus ontstaan. Nu nog ontdekken wanneer dit optreedt en vooral ook 'waarom?'.

Even hierop verder borduren:

Het was inderdaad een lus in de code die bleef hangen. In deze code werd o.a. de query uit de beginpost aangeroepen. De fout in de lus is inmiddels verholpen en nu lijkt alles weer naar behoren te werken.

[Schnoop]

Ik heb zelf eens ooit een tool geschreven om load op onze webservers en databases te testen:

Tooltje. (Gebruik op eigen risico, en alleen voor legale zaken....).

Eigenlijk is het niets meer dan een soort ddos tooltje wat een bak me requests op een site afvuurt. Op deze manier kan je redelijk goed testen hoe je database en webserver het uithouden.

Wellicht dat je er iets aan hebt.