[sql] Query parallel uitvoeren?

[Verwijderd]

Momenteel ben ik bezig een postgresql query te optimaliseren die vrij lang draait. Naar nader onderzoek blijkt dat de query vooral CPU bound-is. Nu wordt er gedraait op een dual-core quad bak zodat er in het totaal 8 cores zijn.

Echter, postgresql (ik gebruik 8.1) gebruikt voor een enkele query nooit meer dan 1 core. Na wat rondgezocht te hebben lijkt het dat dit bij bijna elke DBMS het geval is. Nu wil echter het overbekende feit dat een enkele core de komende tijd niet bijster veel sneller meer gaat worden. Alle grote processor fabrikanten gaan vooral inzetten op massive multiple core designs.

Nu kan ik natuurlijk gaan proberen handmatig te gaan paralleliseren. In mijn specificieke query komt het eigenlijk neer op een sum en wat delingen over een kleine miljard rijen. Ik zou dus 2 queries naar de DB kunnen sturen met in de ene iets in de trend van "where key < (max_key/2)" en in de andere "where key >= (max_key/2). De resultaten zou ik dan weer gewoon in Java samen kunnen voegen.

De bovengenoemde aanpak zit me echter toch niet lekker. Ten eerste moet ik alle berekeningen in Java code gaan herhalen. Dit is een maintenance issue omdat dan bij aanpassing van de sql de java code gelijk mee moet veranderen en andersom. Ten tweede moet ik voor de join eigenlijk de 'group by' clause functionaliteit van een DBMS gaan herschrijven EN onderhouden, iets wat ik opzich wel wil doen ware het niet dat de DB dit al precies kan en waarschijnlijk veel beter.

Een andere mogelijkheid is om de resultaten van de aparte queries in een view 'op te slaan' op deze een union te doen, en daar weer de originele query (zonder de extra where clause) op de draaien. Hierba moet ik wel telkens unieke view namen genereren, de table namen voor de join query dynamisch inserten, en de views weer verwijderen na afloop.

Effin, veel gedoe allemaal en ik vraag me dan af of er eigenlijk niet meer mensen zijn die tegen dit probleem zijn opgelopen en hoe die dit opgelost hebben.

[P_de_B]

Je kunt natuurlijk een RDBMS gaan gebruiken die het wel ondersteunt. SQL Server heeft in bepaalde mate wel ondersteuning voor parallele queries, het parallel uitvoeren van de queries wordt dan helemaal door het RDBMS geleverd. En inderdaad zijn CPU bound queries dan een uitstekende kandidaat. Ik denk dat bijvoorbeeld Oracle dit ook wel kan.

Andere oplossingen lijken mij moeilijk.

[Gé Brander]

Is het niet mogelijk om met twee of meer query's en tijdelijke tabellen wat te doen? Ik zou zeker niet ophalen en in java gaan bewerken. Databases zijn goed en snel in dit soort acties, en zijn daarvoor gemaakt. Java zal het best kunnen maar om een miljard rijen over te halen, is ook weer niet slim voor de performance in verband met netwerkverkeer etc.

[Verwijderd]

Dit soort queries zijn soms wel goed te parallelliseren, bv. als je je 'group by' clause kunt omzetten naar een behapbaar aantal queries die hetzelfde doen, maar dan met een 'where' clause voor iedere group die eerst in je 'group by' zat.
Pas geleden op die manier een query opgesplitst in 40 threads, en de performance ging van 7 naar 3 seconden. En dat was op een dual processor single core Xeon machine, met 8 cores moet daar een hoop meer winst uit te halen zijn...

[P_de_B]

Je zit dan nog wel met de nadelen die TS opnoemt: je moet de resultaten weer samenvoegen aan de clientkant met alle onderhoudsgevoelige sores van dien.

Is de query echt de bottleneck dan zul je het inderdaad op zo'n manier moeten doen.

[Verwijderd]

c70070540 schreef op maandag 04 september 2006 @ 21:29:
Java zal het best kunnen maar om een miljard rijen over te halen, is ook weer niet slim voor de performance in verband met netwerkverkeer etc.

De gehele miljard rijen gaan natuurlijk niet naar Java toe. In het algemeen bevat een tabel een aantal items (zeg 1000) waarvoor je de sum doet, bijvoorbeeld produkten. Voor elk zo'n item staan er bijvoorbeeld een miljoen records in de tabel, waarbij de query dan eigenlijk een selectie uit de records maakt (bv op datum en status om maar iets te noemen). Je houdt dan uiteindelijk iets minder dan 1000 rijen over, met voor elk item allemaal historische data gesummed.

Deel je dit op in 2 queries, dan stuur je dus zo'n 2000 rijen naar java. In elk van de 2 resultsets zoek je dan elk item op en doet de zelfde sum vanuit de SQL query op telkens 2 rijen.

Het aantal rijen is dus niet echt het probleem. Het idee van code duplicatie en nabouwen van DB functionaliteit is dat wel.

P_de_B schreef op maandag 04 september 2006 @ 21:27:
Je kunt natuurlijk een RDBMS gaan gebruiken die het wel ondersteunt. [... ]het parallel uitvoeren van de queries wordt dan helemaal door het RDBMS geleverd.

Dit is eigenlijk wel wat ik zoek. De kosten van Oracle zijn echter nog wel wat te hoog voor mij op dit moment, zeker omdat er per CPU betaald moet worden. Maar toch, is er iemand hier die hier echt ervaring mee heeft? Verdeeld Oracle echt de gehele load over alle aanwezige cores, of gebeurd dit alleen in bepaalde situaties? Natuurlijk kan niet alles geparalliseerd worden, maar als een query redelijkerwijs handmatig parallel gedraaid zou kunnen worden, doet Oracle dit dan inderdaad automatisch?

[ Voor 27% gewijzigd door Verwijderd op 04-09-2006 21:59 ]

[JaQ]

Postgresql kan geen parallel query aan, maar greenplum gebruikt meerdere databases en wat middleware om een parallel query optie te simuleren (is overigens niet gratis)

Misschien kan partitionering je in dit geval helpen? (Beetje afhankelijk van de inhoud van de tabel, maar in Oracle heb ik hiermee regelmatig flinke resultaten geboekt). Verder is het uiteraard belangrijk dat je regelmatig "vacumed" en statistieken berekend....

[P_de_B]

Verwijderd schreef op maandag 04 september 2006 @ 21:54:
[...]

Dit is eigenlijk wel wat ik zoek. De kosten van Oracle zijn echter nog wel wat te hoog voor mij op dit moment, zeker omdat er per CPU betaald moet worden. Maar toch, is er iemand hier die hier echt ervaring mee heeft? Verdeeld Oracle echt de gehele load over alle aanwezige cores, of gebeurd dit alleen in bepaalde situaties? Natuurlijk kan niet alles geparalliseerd worden, maar als een query redelijkerwijs handmatig parallel gedraaid zou kunnen worden, doet Oracle dit dan inderdaad automatisch?

Ik kan niet voor Oracle spreken, wel voor MS SQL Server. Een quote uit Books Online:

Microsoft® SQL Server™ 2000 detects the best degree of parallelism for each instance of a parallel query execution automatically by considering:

Is SQL Server running on a computer with more than one microprocessor or CPU, such as a symmetric multiprocessing computer (SMP)?
Only computers with more than one CPU can use parallel queries.

What is the number of concurrent users active on the SQL Server installation at this moment?
SQL Server monitors CPU usage and adjusts the degree of parallelism at the query startup time. Lower degrees of parallelism are chosen if CPU usage is high.

Is there sufficient memory available for parallel query execution?
Each query requires a certain amount of memory to execute. Executing a parallel query requires more memory than a nonparallel query. The amount of memory required for executing a parallel query increases with the degree of parallelism. If the memory requirement of the parallel plan for a given degree of parallelism cannot be satisfied, SQL Server decreases the degree of parallelism automatically or completely abandons the parallel plan for the query in the given workload context and executes the serial plan.

What is the type of query executed?
Queries heavily consuming CPU cycles are the best candidates for a parallel query. For example, joins of large tables, substantial aggregations, and sorting of large result sets are good candidates. Simple queries, often found in transaction processing applications, find the additional coordination required to execute a query in parallel outweigh the potential performance boost. To distinguish between queries that benefit from parallelism and those that do not benefit, SQL Server compares the estimated cost of executing the query with the cost threshold for parallelism value. Although not recommended, users can change the default value of 5 using sp_configure.

Is there a sufficient amount of rows processed in the given stream?
If the query optimizer determines the number of rows in a stream is too low, it does not introduce exchange operators to distribute the stream. Consequently, the operators in this stream are executed serially. Executing the operators in a serial plan avoids scenarios when the startup, distribution, and coordination cost exceeds the gains achieved by parallel operator execution.

The INSERT, UPDATE, and DELETE operators are executed serially; however, the WHERE clause of either an UPDATE or DELETE, or SELECT portion of an INSERT statement may be executed in parallel. The actual data changes are then serially applied to the database.

Static and keyset cursors can be populated by parallel execution plans. However, the behavior of dynamic cursors can be provided only by serial execution. The query optimizer always generates a serial execution plan for a query that is part of a dynamic cursor.

At execution time, SQL Server determines if the current system workload and configuration information allow for parallel query execution. If parallel query execution is warranted, SQL Server determines the optimal number of threads and spreads the execution of the parallel query across those threads. When a query starts executing on multiple threads for parallel execution, the query uses the same number of threads until completion. SQL Server reexamines the optimal number of thread decisions each time a query execution plan is retrieved from the procedure cache. For example, one execution of a query can result in use of a serial plan, a later execution of the same query can result in a parallel plan using three threads, and a third execution can result in a parallel plan using four threads.

Use SQL Profiler to monitor the degree of parallelism for individual statements. Use the Degree Of Parallelism event class in the Performance event category. For more information, see Performance Event Category.

The showplan output for every parallel query will have at least one of these logical operators:

Distribute Streams


Gather Streams


Repartition Streams

[FragFrog]

Verwijderd schreef op maandag 04 september 2006 @ 21:54:
Dit is eigenlijk wel wat ik zoek. De kosten van Oracle zijn echter nog wel wat te hoog voor mij op dit moment, zeker omdat er per CPU betaald moet worden. Maar toch, is er iemand hier die hier echt ervaring mee heeft?

Weet helaas niet hoe duur SQL Server 2005 is, heb hier namelijk enkel een gratis demo versie, maar die gebruikt op m'n dual-cpu server netjes beide CPU's. Wellicht een optie?

//edit
Aargh, meerdere tabs openen en na een kwartier nog eens een reply tikken is met die fanaten hier dus geen goed plan.. Nouja, wel even een screenshotje gebakken van je server properties dialogue om een beeld te geven van hoe hip het allemaal is

[ Voor 24% gewijzigd door FragFrog op 04-09-2006 22:21 ]

[JaQ]

Verwijderd schreef op maandag 04 september 2006 @ 21:54:
[...]
Dit is eigenlijk wel wat ik zoek. De kosten van Oracle zijn echter nog wel wat te hoog voor mij op dit moment, zeker omdat er per CPU betaald moet worden. Maar toch, is er iemand hier die hier echt ervaring mee heeft? Verdeeld Oracle echt de gehele load over alle aanwezige cores, of gebeurd dit alleen in bepaalde situaties? Natuurlijk kan niet alles geparalliseerd worden, maar als een query redelijkerwijs handmatig parallel gedraaid zou kunnen worden, doet Oracle dit dan inderdaad automatisch?

In Oracle kan je bij het aanmaken van een tabel de parallel optie meegeven. Als je vervolgens de parameter PARALLEL_AUTOMATIC_TUNING op true zet, doet Oracle de rest voor je. Uiteraard is het altijd beter (en mogelijk) om de parallel hint mee te geven bij je query. (het is zelfs mogelijk om binnen een RAC cluster de query te laten verdelen over de verschillende cluster nodes, iets wat ik zeer binnenkort ga testen voor een DWH).

Meer info: vraag het aan tom, check een faq of duik gewoon de docs in.

[Verwijderd]

P_de_B schreef op maandag 04 september 2006 @ 22:06:
[...]
Queries heavily consuming CPU cycles are the best candidates for a parallel query. For example, joins of large tables, substantial aggregations, and sorting of large result sets
[...]

Dat is inderdaad precies een voorbeeld van mijn situatie, een vrij zware aggregatie waarbij er weliswaar ook veel IO is (miljard records moeten toch opgehaald worden), maar waarbij de data sneller opgehaald kan worden dan de CPU deze kan verwerken.

Ik zal toch zeker eens moeten kijken of de wat duurdere DBs toch niet hun investering waard zijn.

[Gomez12]

Verwijderd schreef op maandag 04 september 2006 @ 22:24:
[...]


Dat is inderdaad precies een voorbeeld van mijn situatie, een vrij zware aggregatie waarbij er weliswaar ook veel IO is (miljard records moeten toch opgehaald worden), maar waarbij de data sneller opgehaald kan worden dan de CPU deze kan verwerken.

Ik zal toch zeker eens moeten kijken of de wat duurdere DBs toch niet hun investering waard zijn.

2e optie die ik redelijk vaak gebruik is om bij zware aggegrated functies gewoon periodiek een tussentabel te laten vullen.

Gewoon 1x per dag / uur via een cronjob je totale query draaien en outputten naar een tussentabel. Dan kan je al je aggegrated data vanuit je tussentabel trekken.
IMHE(xperience) interesseert het managers / analisten toch geen ene barst of ze wel of niet de laatste seconde erbij hebben, ze willen een beeld over de laatste x tijd. Dat vandaag er niet bij zit is niet boeiend als je analyses gaat maken over langere periodes.
Dus zeg ik meestal gewoon dat het werkt met data van gisteren / een uur geleden en normaal gesproken heeft iedereen hier vrede mee ( vooral als ik ze laat zien hoelang een real-time query kost ). Sommige mensen wensen hier niet aan te gaan omdat de data niet realtime is etc. Dan is het of echt 100% real-time de data halen ( met alle wachttijden van dien ) of een mengelmoes : tussentabel voor x data en dan realtime nog even de laatste dag erbij betrekken ( qua onderhoud een crime als je het niet strikt en alleen maar voor raadplegen / statistieken gebruikt )

[JaWi]

Als je nog even met PostgreSQL wil frobelen, kun je misschien kijken naar PgPool II. Hiermee kun je "parallelle" queries uitvoeren op 'n cluster van standaard PostgreSQL instanties...

[Verwijderd]

JaWi schreef op dinsdag 05 september 2006 @ 20:13:
Als je nog even met PostgreSQL wil frobelen, kun je misschien kijken naar PgPool II. Hiermee kun je "parallelle" queries uitvoeren op 'n cluster van standaard PostgreSQL instanties...

Hmmm, hoewel ik een groot vertrouwen heb in PostgreSQL zelf, weet ik toch niet zo zeker of ik dit soort projecten wel even in productie wil gaan toepassen. Net zoals al die andere PG cluster projecten is het totaal niet duidelijk wat de stabiliteit ervan is.

@Gomez12
Dat van die voorberekeningen is precies wat we al doen. Omdat het hoog dimensionale data is, kun je natuurlijk maar enkele combinaties voorberekenen. We doen dat nu in 4 levels, waarbij de eerste een algehele voorberekening is (vervangt compleet de originele tabel), en de andere 3 levels totalen van steeds langer geleden berekenen. Daarnaast hangen er nog een X aantal voorberekende tabellen omheen voor hele special (maar frequent) cases.

[P_de_B]

Volgens mij moet jij af van de standaard relationele databases voor deze analyses, en moet je naar een database die OLAP ondersteunt. Je maakt nu toch al gebruik van aparte tabellen voor de grootschalige analyses.

The arrangement of data into cubes avoids a limitation of relational databases which are not well suited for near instantaneous analysis of large amounts of data. Relational databases are better suited for creating records from a series of transactions (known as OLTP or on-line transaction

OLAP cubes can be thought of as extensions to the two-dimensional array of a spreadsheet. For example a company might wish to analyse some financial data by product, by time-period, by city, by type of revenue and cost, and by comparing actual data with a budget.

En nog uit de SQL Server help:

About OLAP
Whereas data warehouses and data marts are the data stores for analysis data, online analytical processing (OLAP) is the technology that enables client applications to efficiently access this data. OLAP provides many benefits to analytical users, for example:

• An intuitive multidimensional data model makes it easy to select, navigate, and explore the data.


• An analytical query language provides power to explore complex business data relationships.


• Precalculation of frequently queried data enables very fast response time to ad hoc queries.
Microsoft® SQL Server™ 2000 Analysis Services is a robust OLAP tool that can be used with data stored in various data warehouse databases, including SQL Server, Microsoft Access, and Oracle databases.

[flowerp]

Nu meerdere cores veel meer gemeenschappelijk beginnen te worden, zit ik ook steeds meer met deze vraag. Entry level servertjes (in de 2000,- range) komen tegenwoordig al met 8 cores, en voor 1 van onze main DB servers hebben we momenteel een 16-core in bestelling staan (op basis van de 8350), die relatief nog niet eens zo duur was.

Wat echter wel duur blijft is Oracle. Dat pakket blijft gewoon niet te betalen, zeker niet omdat het (gedeeltelijk) per core gaat en CPUs alleen maar meer en meer cores krijgen. Daarnaast zijn de queries van onze 2 belangrijkste producten op Postgresql resp. MySql gebaseerd. (ja, SQL zou je misschien eigenlijk DB onafhankelijk moeten schrijven, maar de praktijk is dat dat nu eenmaal niet altijd is gebeurd )

Ik vraag me dus af wat iedereen momenteel doet om meerdere cores te gebruiken voor 1 query. Een makkelijke mogelijkheid, die de TS ook al noemt, is gewoon je query zelf op te delen en die los naar de DB te sturen. Als elke losse query een aantal rijen berekent die helemaal klaar zijn en direct in het eindresultaat kunnen komen, is de overhead aan de Java kant minimaal (alleen wat resultsets samenvoegen tot 1'tje).

Specifiek vraag ik me af of iemand enig idee heeft wanneer PostgreSQL en MySql zelf 1 query over meerdere cores kunnen verdelen.

[whoami]

flowerp schreef op zaterdag 01 december 2007 @ 14:34:
(ja, SQL zou je misschien eigenlijk DB onafhankelijk moeten schrijven, maar de praktijk is dat dat nu eenmaal niet altijd is gebeurd )

Dat is zowiezo gewoon praktisch onmogelijk. Als je meerdere DB's wilt ondersteunen (en je gebruikt geen o/r mapper), dan zal je genoodzaakt zijn om voor iedere db dat je ondersteunt , een DAL laag te schrijven die aan een bepaalde interface voldoet, zodanig dat je makkelijk van implementatie kunt wisselen.

Ik vraag me dus af wat iedereen momenteel doet om meerdere cores te gebruiken voor 1 query. Een makkelijke mogelijkheid, die de TS ook al noemt, is gewoon je query zelf op te delen en die los naar de DB te sturen. Als elke losse query een aantal rijen berekent die helemaal klaar zijn en direct in het eindresultaat kunnen komen, is de overhead aan de Java kant minimaal (alleen wat resultsets samenvoegen tot 1'tje).

Ik vraag me af of dit bijzonder zinvol is .... IMHO is snelheidswinst bij het uitvoeren van DB queries toch vooral te halen in het beperken van I/O operaties.

[flowerp]

whoami schreef op zaterdag 01 december 2007 @ 14:37:
[...]
Dat is zowiezo gewoon praktisch onmogelijk. Als je meerdere DB's wilt ondersteunen (en je gebruikt geen o/r mapper), dan zal je genoodzaakt zijn om voor iedere db dat je ondersteunt , een DAL laag te schrijven die aan een bepaalde interface voldoet,

In zekere zin hebben we ook wel een DAL. Zowel bij het PHP/MySQL team als het Java/PostgreSQL team is er van af het begin wel de gedachte geweest dat er ooit van DB gewisseld zou moeten kunnen worden. De grote hoeveel complexe queries maakt dat echter een praktisch zeer lastige zaak.

Voor wat (kleinere) nieuwe projecten gebruiken we een O/R mapper (Hibernate), die de SQL inderdaad specifiek voor een DB genereert. Dit heeft echter wel weer z'n hele eigen reeks van performance moeilijkheden, maar dat is meer stof voor een andere topic

Ik vraag me af of dit bijzonder zinvol is .... IMHO is snelheidswinst bij het uitvoeren van DB queries toch vooral te halen in het beperken van I/O operaties.

Met handmatige tests kwam ik in veel gevallen op een nagenoeg totaal lineaire snelheidswinst. Met 2 cores doet de query er in het totaal ~50% van de tijd over, met 4 cores ~25% en met 8 cores 13 ~ 14%. De 16 core machine is nog niet binnen, dus ik weet niet of het daarop inderdaad 6~7 zal gaan worden. Naarmate je meer opsplitst zal de overhead van meerdere threads instantieeren op een gegeven moment toch wel gaan meespelen. In mijn geval was alle data overigens wel uit de buffer beschikbaar (test machine heeft 32GB geheugen, dataset waar het over gaat is zo'n 20GB, maar de totale DB zit ergens rond de 70 of 80GB).

Een vervelende moeilijk is wel de mate van opsplitsing te relativeren aan de huidige load. Als alle 8 cores lopen te pompen wil ik mijn query niet 8x opsplitsen. Als alle 8 cores nagenoeg vrij zijn juist wel. Een DBMS zelf zou dat veel beter moeten kunnen (zie SQL Server).

[EfBe]

Mja, het is wel een wat eenzijdig verhaal. Want ben jij in de praktijk in je eentje op die machine aan het werk? Veelal niet. Dus is het wel zoveel sneller om te parallelliseren? Immers: als je 8 cores hebt en je hebt 8 queries te verwerken van 8 verschillende connections, dan is het IMHO beter om per core een query te doen ipv elke query te parallelliseren, want dan verlies je veel performance door overhead.

Dus idd, aan het RDBMS overlaten ,die veelal die mogelijkheid ingebakken hebben zitten.

[flowerp]

EfBe schreef op zaterdag 01 december 2007 @ 16:18:
als je 8 cores hebt en je hebt 8 queries te verwerken van 8 verschillende connections, dan is het IMHO beter om per core een query te doen ipv elke query te parallelliseren, want dan verlies je veel performance door overhead.

Inderdaad, maar het hangt dus sterk van de huidige belasting af. In de praktijk gebeurd het namelijk heel vaak op ons systeem dat 1 core op 100% staat voor 1 enkele query en dat een klant (te) lang moet wachten (en daarop dan weer boos aan de telefoon hangt). Op het moment dat die ene core op 100% staat is het eerder regel dan uitzondering dat -niet- alle andere cores ook op 100% staan. Op een dergelijke moment staan de 7 andere cores b.v. gemiddeld op 10%.

In die gevallen staat er dus een groot gedeelte van de hardware even weinig te doen, terwijl die ene klant wel veels te lang zit te wachten.

Dus idd, aan het RDBMS overlaten ,die veelal die mogelijkheid ingebakken hebben zitten.

Het probleem is dat PostgreSQL en MySQL die mogelijkheden dus niet ingebouwd hebben en we het daar voorlopig toch mee moeten doen (dus zowel qua kosten als qua techniek).

[justmental]

Een aggregatie van een miljard rijen en je bent cpu bound?
Hoe ziet je executie plan eruit?

[flowerp]

justmental schreef op zaterdag 01 december 2007 @ 21:21:
Een aggregatie van een miljard rijen en je bent cpu bound?
Hoe ziet je executie plan eruit?

Eigenlijk heel erg basic, zonder gekke dingen erin. De query die het zo goed deed op meerdere cores is feitelijk in de kern niets meer dan een "sum( 'some column' ) where some_id = something". Het plan reflecteert die simpelheid dan ook. Door zowel naar de IO belasting als naar de CPU belasting te kijken bleek dat het inderdaad nagenoeg 100% CPU bound is.

Natuurlijk geldt dit niet voor de meerderheid van de queries. Naarmate queries meer io bound zijn, zal de speedup relatief steeds minder zijn bij het gebruik van meerdere cores.

Blijft natuurlijk het feit dat onafhankelijk van mijn toevallige queries, SQL Server en Oracle al heel lang parallellisatie mogelijkheden hebben en Postgres en MySQL nog steeds niet. Op korte termijn lijkt daar ook weinig verandering in te komen, maar wellicht dat iemand meer weet?

[Xiphalon]

En met een denormalisatie kom je niet sneller?

Als something maar een paar waarden bevat, kan je best een los tabelletje vullen met de sums doormiddel van een paar triggers.

Blijft wel de vraag wat belangrijker is: het snel draaien van deze query of de wat langzamere insert/edit/delete's.