[SQL] Zware query die weinig runt: hoe beste performance?

[Tharulerz]

Ik heb een query in de vorm van:

SQL:

SELECT param, AVG(p1),AVG(p2),AVG(p3),AVG(p4),AVG(p5),AVG(p6),AVG(p7),AVG(p8)
FROM table
GROUP BY param

De tabel draait op een InnoDB engine en heeft een 40M records. Deze query is nu ontzettend traag, maar moet slechts 1 of 2 keer per dag gerunned worden (ik kan daarna de results cachen om de hele dag op een website te tonen).

Vraag: Hoe kan ik deze query sneller laten lopen? (Innodb settings, index op group by column, ...)
Indien er niet direct een degelijke oplossing is, hoe kan ik deze query op low priority laten lopen zodat een eventuele gebruiker van de server geen hoge load merkt op de server?

Google geeft geen eenduidig antwoord, en trial en error duurt vaak een aantal uur op zoveel records...

Edit: Records worden enkel aangevuld (1M nieuwe rows), geen update. 1 a 2 keer per dag, en dus elke keer na update moeten die Averages opnieuw berekend worden.

[ Voor 9% gewijzigd door Tharulerz op 03-04-2012 19:44 ]

[RobIII]

Indexen (anders dan die je nu hebt) gaat je hier niet helpen; je zult toch écht alle waardes moeten doorlopen om een Average te berekenen. Resultaat cachen is dus 't enige dat er op zit.

Low prio weet ik niet of MySQL dat kent. Ik weet wel dat MySQL (behalve met workarounds) geen Materialized views ondersteund; dat was hier wel handig geweest wellicht.

Als deze data, bijvoorbeeld, dagelijks wordt aangevuld kun je natuurlijk wel de query doen op, bijvoorbeeld, enkel de laatste dag/week. Met een datetime veld in je tabel (en daar een index op) kun je dan voorkomen dat je elke keer tot 40M records moet doorspitten en kun je dus alleen de averages van de laatste dag/week oid berekenen en cachen, of agregated, weer in andere tabellen opslaan. Dan kun je daar weer je queries doen op een boel minder records voor dezelfde (uiteindelijke) gegevens. Zo kun je een myrecords_per_day of myrecords_per_month aggregate tabel maken (ook afhankelijk van de "resolutie" die je wil hebben).

Voor een zinnig(er) antwoord zul je iets meer moeten vertellen over het doel van de tabel en de inhoud en wat je precies probeert te bereiken.

[ Voor 61% gewijzigd door RobIII op 03-04-2012 19:36 ]

[__fred__]

En hoevaak verversen die records? En welk deel ververst dan? Je kunt ook gemiddelden over subsets berekenen en dan alleen een gemiddelde over de nieuwe aanwas berekenen en die middelen met de gemiddelden die je al weet van de oude subsets.

[Tharulerz]

__fred__ schreef op dinsdag 03 april 2012 @ 19:39:
En hoevaak verversen die records? En welk deel ververst dan? Je kunt ook gemiddelden over subsets berekenen en dan alleen een gemiddelde over de nieuwe aanwas berekenen en die middelen met de gemiddelden die je al weet van de oude subsets.

Records worden enkel aangevuld (1M nieuwe rows), geen update. 1 a 2 keer per dag, en dus elke keer na update moeten die Averages opnieuw berekend worden. Lijkt me minder werk dan ze dynamisch bij de insert te gaan berekenen...

[Z-Dragon]

Misschien klink ik op een of andere wijze niet bijster slim, maar om een gemiddelde bij te werken hoef je toch niet alle records na te lopen? Het huidige gemiddelde en het aantal records waarover dat berekend is, samen met de nieuwe records zou genoeg moeten zijn (indien er dus geen sprake is van updates). In het ergste geval loop je wat precisie mis (afhankelijk van de data). Dat betekent dan na elke insert het opgeslagen gemiddelde bijwerken, of in dit geval liever dat door een stored procedure laten doen net na een mass-insert of voor je het resultaat ophaalt. Eventueel kun je eens in de zoveel tijd (op een rustig moment) weer het gemiddelde volledig laten berekenen om de ontstane afwijking recht te trekken.

[ Voor 44% gewijzigd door Z-Dragon op 03-04-2012 19:50 ]

[Tharulerz]

Z-Dragon schreef op dinsdag 03 april 2012 @ 19:44:
Misschien klink ik op een of andere wijze niet bijster slim, maar om een gemiddelde bij te werken hoef je toch niet alle records na te lopen? Het huidige gemiddelde en het aantal records waarover dat berekend is, samen met de nieuwe records zou genoeg moeten zijn (indien er dus geen sprake is van updates). In het ergste geval loop je wat precisie mis (afhankelijk van de data). Dat betekent dan na elke insert het opgeslagen gemiddelde bijwerken, of dat door een stored procedure laten doen net voor je het resultaat ophaalt. Eventueel kun je eens in de zoveel tijd (op een rustig moment) weer het gemiddelde volledig laten berekenen om de ontstane afwijking recht te trekken.

Dan moet je toch per parameter gaan bijhouden over hoeveel records het gemiddelde liep? En dan zou ik alsnog het gemiddelde moeten berekenen van de nieuwe data, maar dat kan ik dan met een index op hun insert ID ofzo...

Is een optie, iemand nog iets eleganter/beter?

[GlowMouse]

Een index op param ga je zeker merken, dat scheelt een sorteerstap. Gebruik FORCE INDEX als die index niet gebruikt wordt.

Het probleem met langlopende queries op innodb is dat je een hele lange transactie open hebt staan, en de undo log wordt dan heel groot als je tegelijk op dezelfde tabel veel rijen wijzigt. Verlagen van het isolatieniveau kan helpen.

Je kunt de query beter vermijden. Wat is het datatype van p1, ..., p6? Geef eens de output van SHOW CREATE TABLE.

[ Voor 4% gewijzigd door GlowMouse op 03-04-2012 19:54 ]

[Freeaqingme]

GlowMouse schreef op dinsdag 03 april 2012 @ 19:52:
Een index op param ga je zeker merken, dat scheelt een sorteerstap. Gebruik FORCE INDEX als die index niet gebruikt wordt.

Ik zou die force index nooit als advies geven. Dat is iets voor doorgewinterde DBA'ers. De kans dat een gebruiker beter kan bepalen welke index je RDBMS moet gebruiken dan je RDBMS zelf is nihiel.

[__fred__]

Je kunt toch de gemiddelden + rowid waar je gebleven was opslaan.

nieuw gemiddelde =  (nieuw rowid - rowid) * (gemiddelde where rowid > laatse keer) + rowid * oud gemiddelde
                               ------------------------------------------------------------------------------------------------------------
                                                                  nieuw rowid

(kost je trouwens wel precisie, dus wellicht af en toe eens herberekenen)

[ Voor 8% gewijzigd door __fred__ op 03-04-2012 20:03 ]

[GlowMouse]

Freeaqingme schreef op dinsdag 03 april 2012 @ 19:58:
[...]


Ik zou die force index nooit als advies geven.

Dat bepaal ik zelf wel.

[Avalaxy]

Ik weet niet of het in jouw situatie mogelijk is, maar wat ik zelf zou proberen is zoiets:

- Een on insert en on delete trigger maken die een SP aanroept.
- Die SP werkt de average die je in een aparte cachetabel bijhoudt bij.

Zo hoef je nooit een AVG() te doen maar heb je na elke insert en elke delete automatisch de juiste average in je cache.

Eigenlijk dus een beetje wat Z-Dragon zegt.

[Tharulerz]

GlowMouse schreef op dinsdag 03 april 2012 @ 19:52:

Je kunt de query beter vermijden. Wat is het datatype van p1, ..., p6? Geef eens de output van SHOW CREATE TABLE.

Allemaal INT, 1 of 2 doubles, allemaal NOT NULL. Vrij standaard dus.

[P-Storm]

Zoals ik het zo lees is dat je het beste een gemiddelde kan maken die telkens de oude resultaat meeneemt, zoals __fred__ voorsteld. Zelf heb ik geen idee hoe je het resultaat kan opslaan, je kan de volgende formule bijvoorbeeld gebruiken.

(Gemiddelde oud * aantal oud + gemiddelde nieuw * aantal nieuw) / (aantal oud + aantal nieuw)

[GlowMouse]

Tharulerz schreef op dinsdag 03 april 2012 @ 20:06:
[...]


Allemaal INT, 1 of 2 doubles, allemaal NOT NULL. Vrij standaard dus.

Als het INTs zijn dan is de snelste oplossing om een aparte tabel te komen met daarin de kolommen (param,aantal,p1som,p2som,etc). Het type voor de som is een LONGINT. Die tabel kun je met triggers bijwerken en je resultaten zijn dan altijd exact.

[cariolive23]

GlowMouse schreef op dinsdag 03 april 2012 @ 19:52:
Een index op param ga je zeker merken, dat scheelt een sorteerstap. Gebruik FORCE INDEX als die index niet gebruikt wordt.

Waarom zou je het gebruik van een index willen afdwingen? Je moet wel heel erg zeker van je zaak zijn dat random IO op de index sneller is dan een sequential scan op de tabel. Hier komt nog bij dat een index vandaag misschien wel sneller is, maar morgen met nieuwe data ineens véél langzamer is. Met een FORCE INDEX schiet je jezelf dan mooi in de voet!



Gebruik EXPLAIN, ga kijken wat het knelpunt is, IO optimaliseren, MySQL configuratie (RAM!) optimaliseren, etc. etc.

Het forceren van een index is een noodgreep en slechts zelden een echte oplossing. Wij hadden hier op een bepaald moment zoveel van dit soort noodgrepen in de oude MySQL database dat we zijn overgestapt op PostgreSQL. PostgreSQL werkt een factor 1000 sneller met complexe queries en veel data. Daarnaast heeft ook nog eens meer functionaliteit waardoor we ineens ook veel meer mogelijkheden krijgen.

En geen gedonder meer met de undo log, transacties werken razendsnel in PostgreSQL, ook de ROLLBACK.

[GlowMouse]

cariolive23 schreef op dinsdag 03 april 2012 @ 20:29:
[...]

Waarom zou je het gebruik van een index willen afdwingen?

Omdat het de enige juiste index is voor deze query. Je verdere pleidooi voor PostgreSQL is bekend

[Voutloos]

cariolive23 schreef op dinsdag 03 april 2012 @ 20:29:
PostgreSQL werkt een factor 1000 sneller met complexe queries en veel data.

Bla bla bla, insert pretty number. Mooi getal, maar weet je hoe je deze query echt een orde van grootte sneller krijgt? Door de database minder werk te laten doen. En dan lukt je met een materialized view.

Maak een extra tabelletje en sla averages per dag, maand of per welke je periode je je stats minimaal wilt hebben. Kost nu iets meer werk¹ dan bestaande query marginaal sneller maken, maar geeft ook een veel grotere boost: je hebt dan enkel queries over duizenden ipv tientallen miljoenen rows. En misschien is een specifieke query wat procentjes sneller in dbms A of B, maar dat is vergeleken de veel efficientere datastructuur relatief insignificant.<sup>2

1</sup>: Iets meer meer werk op de korte termijn. Op langere termijn scheelt het werk, omdat je niet elke zoveel maanden als er weer tientallen miljoenen rows bijgekomen met je handen @ haar hoeft te zitten.

²: TS lijkt het te hebben over 1M rows per dag en vindt nu de performance bij 40M rows onvoldoende.
Goed, ik doe eens aardig en neem aan dat PostgreSQL zomaar een whopping 2x zo snel is met deze query. \o/
Wat heb je dan? Juist, binnen 40 dagen weer een onvoldoende performance...

[Kajel]

Voutloos schreef op dinsdag 03 april 2012 @ 21:26:
[...]
Bla bla bla, insert pretty number. Mooi getal, maar weet je hoe je deze query echt een orde van grootte sneller krijgt? Door de database minder werk te laten doen. En dan lukt je met een materialized view.

Bovendien kunnen we aannemen dat er redenen zijn waarom de TS niet al met Postgres werkt...

Maak een extra tabelletje en sla averages per dag, maand of per welke je periode je je stats minimaal wilt hebben. Kost nu iets meer werk¹ dan bestaande query marginaal sneller maken, maar geeft ook een veel grotere boost: je hebt dan enkel queries over duizenden ipv tientallen miljoenen rows. En misschien is een specifieke query wat procentjes sneller in dbms A of B, maar dat is vergeleken de veel efficientere datastructuur relatief insignificant.²

En dit is inderdaad de oplossing.

Avalaxy schreef op dinsdag 03 april 2012 @ 20:05:
Ik weet niet of het in jouw situatie mogelijk is, maar wat ik zelf zou proberen is zoiets:

- Een on insert en on delete trigger maken die een SP aanroept.
- Die SP werkt de average die je in een aparte cachetabel bijhoudt bij.

Zo hoef je nooit een AVG() te doen maar heb je na elke insert en elke delete automatisch de juiste average in je cache.

En zie hier een mogelijke methode om dat netjes te implementeren

Problem solved!

[Tharulerz]

Ik probeer de oplossing hedendaags te implementeren

Bedankt voor de tips allen!

[TheNephilim]

Avalaxy schreef op dinsdag 03 april 2012 @ 20:05:
Ik weet niet of het in jouw situatie mogelijk is, maar wat ik zelf zou proberen is zoiets:

- Een on insert en on delete trigger maken die een SP aanroept.
- Die SP werkt de average die je in een aparte cachetabel bijhoudt bij.

Zo hoef je nooit een AVG() te doen maar heb je na elke insert en elke delete automatisch de juiste average in je cache.

Eigenlijk dus een beetje wat Z-Dragon zegt.

Mooie oplossing! Ga ik ook even onthouden, mocht ik em ooit nodig hebben

[DamadmOO]

En dan krijg je dus 1 miljoen extra updates/deletes...

Het bijhouden van dag-totalen in een andere tabel is natuurlijk een stuk netter. Maar dan is het slimmer om niet de AVG op te slaan maar gewoon de SUM. En dan te delen met het aantal raden wanneer je die averages ophaalt in je applicatie. Dan heb je ook geen probleem met afrondingen.

[ Voor 7% gewijzigd door DamadmOO op 04-04-2012 09:53 ]

[Big Womly]

P-Storm schreef op dinsdag 03 april 2012 @ 20:12:
Zoals ik het zo lees is dat je het beste een gemiddelde kan maken die telkens de oude resultaat meeneemt, zoals __fred__ voorsteld. Zelf heb ik geen idee hoe je het resultaat kan opslaan, je kan de volgende formule bijvoorbeeld gebruiken.

Als je wil kan je dan nog steeds je index plaatsen om nog wat extra power te geven.

(Gemiddelde oud * aantal oud + gemiddelde nieuw * aantal nieuw) / (aantal oud + aantal nieuw)

Beste performanceboost krijg je volgens mij ook met bovenstaande formule. Dan hoef je enkel het gemiddelde te berekenen van de laatste 1M records die zijn toegevoegd en te verrekenen met de oude data.

[ Voor 6% gewijzigd door Big Womly op 04-04-2012 10:06 ]

[TheNephilim]

Is MongoDB niet een idee voor dit probleem? Ik weet niet hoe je aan je data komt en of deze naar een andere database kan, maar met MongoDB skip je volgens mij het probleem gewoon.

http://www.mongodb.org/display/DOCS/Aggregation

[Voutloos]

Ehm nee. Lees nogmaals mijn post, en doe alsof er Mongo ipv Postgres staat.

Als je de keuze hebt tussen een alogritme over miljoenen of duizenden rows, is de keuze voor welke software je gebruikt amper relevant.

I stand corrected als MongoDB automagisch materialized views doet, maar dan nog blijft het punt staan dat de verbetering in de efficientere datastructuur zit

[Tharulerz]

DamadmOO schreef op woensdag 04 april 2012 @ 09:50:
En dan krijg je dus 1 miljoen extra updates/deletes...

Het bijhouden van dag-totalen in een andere tabel is natuurlijk een stuk netter. Maar dan is het slimmer om niet de AVG op te slaan maar gewoon de SUM. En dan te delen met het aantal raden wanneer je die averages ophaalt in je applicatie. Dan heb je ook geen probleem met afrondingen.

Heb inderdaad gekozen voor SUMs op te slaan. Die extra 1 miljoen updates neem ik er dan wel bij, dat is maar 1 a 2 keer per dag.

[Gomez12]

Voutloos schreef op woensdag 04 april 2012 @ 12:51:
Ehm nee. Lees nogmaals mijn post, en doe alsof er Mongo ipv Postgres staat.

Als je de keuze hebt tussen een alogritme over miljoenen of duizenden rows, is de keuze voor welke software je gebruikt amper relevant.

I stand corrected als MongoDB automagisch materialized views doet, maar dan nog blijft het punt staan dat de verbetering in de efficientere datastructuur zit

Het verschil Mongo en Postgres is dat Postgres simpelweg een andere RDBMS is terwijl Mongo intern andere algoritmen gebruikt (kolom-georienteerd ipv rij-georienteerd).

Mocht dit het enige zijn wat er met deze tabel gebeurt dan zou Mongo (of een andere kolom-georienteerde db) een serieuze overweging kunnen zijn. Ik voorspel dat Mongo hier veel en veel sneller mee is dan een Mysql / Postgres.
Doe je echter ook nog andere dingen met die tabel dan heb je met een Mongo weer een kans dat die veel en veel trager zijn.

Tharulerz schreef op woensdag 04 april 2012 @ 16:52:
[...]
Heb inderdaad gekozen voor SUMs op te slaan. Die extra 1 miljoen updates neem ik er dan wel bij, dat is maar 1 a 2 keer per dag.

Hoe laad je die 1 miljoen records in? Persoonlijk zou ik (mits het op een rustig tijdstip gebeurt) ervoor kiezen om laatste id op te halen, dan alle indexen en triggers van de tabel af te halen, dan inlezen en dan indexen en triggers terugzetten en daarna je SUM's updaten (vanaf je laatste id).
Gaat een factor xx sneller dan 1 miljoen indexen updaten en 1 miljoen triggers triggeren

[Tharulerz]

Gomez12 schreef op woensdag 04 april 2012 @ 19:44:
[...]

Het verschil Mongo en Postgres is dat Postgres simpelweg een andere RDBMS is terwijl Mongo intern andere algoritmen gebruikt (kolom-georienteerd ipv rij-georienteerd).

Mocht dit het enige zijn wat er met deze tabel gebeurt dan zou Mongo (of een andere kolom-georienteerde db) een serieuze overweging kunnen zijn. Ik voorspel dat Mongo hier veel en veel sneller mee is dan een Mysql / Postgres.
Doe je echter ook nog andere dingen met die tabel dan heb je met een Mongo weer een kans dat die veel en veel trager zijn.


[...]

Hoe laad je die 1 miljoen records in? Persoonlijk zou ik (mits het op een rustig tijdstip gebeurt) ervoor kiezen om laatste id op te halen, dan alle indexen en triggers van de tabel af te halen, dan inlezen en dan indexen en triggers terugzetten en daarna je SUM's updaten (vanaf je laatste id).
Gaat een factor xx sneller dan 1 miljoen indexen updaten en 1 miljoen triggers triggeren

De huidige import van 1 miljoen records duurt een 10tal minuten, ik ga heus niet elke keer de index opnieuw berekenen van 40M records... (zeker omdat er meerdere indexen opstaan).

Ik denk niet dat de triggers zoveel overhead zouden creeeren, een andere optie is dat ik gewoon na de import een query doe en de som van de laatste miljoen records ophaal.

[Tim]

Ik zie niet zo goed hoe je het nu heb geïmplementeerd, maar als het inderdaad over miljoenen records gaat, denk dan ook nog aan overflows (als je de sum opslaat) en de minimale precisie van doubles (als je het gemiddelde opslaat).

[JaQ]

Tharulerz schreef op woensdag 04 april 2012 @ 21:30:
De huidige import van 1 miljoen records duurt een 10tal minuten, ik ga heus niet elke keer de index opnieuw berekenen van 40M records... (zeker omdat er meerdere indexen opstaan).

Ik denk niet dat de triggers zoveel overhead zouden creeeren, een andere optie is dat ik gewoon na de import een query doe en de som van de laatste miljoen records ophaal.

Index berekenen? In welke database moet je tegenwoordig je index nog handmatig bijwerken?

Triggers geven wel veel overhead. Iedere insert wordt met een trigger meer dan 1 insert, dus overhead. En een trigger is altijd row-by-row processing (ook wel slow-by-slow processing genoemd), terwijl je bulk processing wil. (kent MySQL eigenlijk wel bulk processing?)

Persoonlijk denk ik dat het snelste de reeds genoemde oplossing is: per dag het totaal aantal rijen op te gaan slaan met de bijbehorende gemiddelden. Daarmee krijg je een redelijk zuiver en snel resultaat. Let op met afrondingsverschillen voor echt zuivere berekeningen (boeit dat eigenlijk voor jouw doel?).

Een andere mogelijkheid is partitionering. Je partitioneringssleutel is param. Voor dit specifieke voorbeeld heeft partitionering zin voor performance. Geen idee wat de caveats zijn op mysql, maar allicht leuk om eens uit te zoeken.

Overigens zie ik niet zo goed waarom naar materialized views wordt gehint. Voor zover mij bekend is een materialized view niets meer dan een opgeslagen query resultaat. Als je deze resultaten wilt verversen, zal je alsnog de query moeten uitvoeren (full refresh). Oracle kent ook nog een fast refresh waardoor je min of meer incremental bijwerkt (wijzigingen sinds laatste refresh), maar dat heeft ook links en rechts wat gevolgen. Van andere databases ken ik de hele mview implementatie niet. Eigenlijk ken ik weinig tot geen goede implementatie met een fast refresh, maar dat kan ook aan mijn klantenkring liggen. Een oplossing die ik zelf niet zo snel zou kiezen.

Tharulerz schreef op woensdag 04 april 2012 @ 16:52:
Heb inderdaad gekozen voor SUMs op te slaan. Die extra 1 miljoen updates neem ik er dan wel bij, dat is maar 1 a 2 keer per dag.

Waarom 1.000.000 updates? Werkt je alsnog met een trigger, ipv de SUM te bepalen met 1 query nadat je alles hebt geinsert? (insert vanaf timestamp X selecteren met een query lijkt me iets sneller ).

[ Voor 9% gewijzigd door JaQ op 06-04-2012 13:52 ]

[DamadmOO]

JaQ schreef op vrijdag 06 april 2012 @ 13:48:
Overigens zie ik niet zo goed waarom naar materialized views wordt gehint. Voor zover mij bekend is een materialized view niets meer dan een opgeslagen query resultaat. Als je deze resultaten wilt verversen, zal je alsnog de query moeten uitvoeren (full refresh). Oracle kent ook nog een fast refresh waardoor je min of meer incremental bijwerkt (wijzigingen sinds laatste refresh), maar dat heeft ook links en rechts wat gevolgen. Van andere databases ken ik de hele mview implementatie niet. Eigenlijk ken ik weinig tot geen goede implementatie met een fast refresh, maar dat kan ook aan mijn klantenkring liggen. Een oplossing die ik zelf niet zo snel zou kiezen.

In MSSQL wordt een materialized view automatisch door de database engine geupdate wanneer er veranderinge gebeuren in de onderliggende tabellen. Er zijn wel een aantal eisen waar je aan moet voldoen voordat je een materialized view kan gebruiken.

Hier is een link naar de MS whitepaper: http://msdn.microsoft.com/en-us/library/dd171921.aspx.

[JaQ]

DamadmOO schreef op maandag 09 april 2012 @ 00:01:
In MSSQL wordt een materialized view automatisch door de database engine geupdate wanneer er veranderinge gebeuren in de onderliggende tabellen. Er zijn wel een aantal eisen waar je aan moet voldoen voordat je een materialized view kan gebruiken.

Hier is een link naar de MS whitepaper: http://msdn.microsoft.com/en-us/library/dd171921.aspx.

Het valt flink op dat ik weinig tot niets van MSSQL weet. Thx (en weer wat geleerd ).