[PHP+Postgres] Database structuur wordt langzaam

[jsiegmund]

In het project waar ik aan bezig ben loop ik tegen wat optimalisatie kwesties aan. Het volgende is het probleem:

Uit de database wordt een lijst gehaald van "klussen". Deze lijst is 1 query waarbij de tabel (voor een deel) wordt geraadpleegd. Vervolgens wordt er voor iedere rij een class aangemaakt van het type "klus" waarin de gegevens gestopt worden.

Iedere klus bevat op zijn beurt weer een magazijn wat bestaat uit een x aantal werknemers, een y aantal vervoersmiddellen en een z aantal artikelen uit het totale magazijn. Nu wordt er per klus een nieuwe query gedaan om die zaken op te halen, eentje voor het personeel, eentje voor het vervoer en eentje voor de artikelen. Kortom: per klus 3 query's. Al deze zaken worden ook weer netjes in hun eigen class gestopt. Wanneer alle gegevens uiteindelijk opgehaald zijn worden ze in een XML boom gestopt die wordt afgedrukt en getransformeerd.

Er staan nu 138 klussen in de hoofdtabel, dus worden er voor het gehele overzicht 1+3x138 query's uitgevoerd: bah! Totale uitvoertijd van de pagina loopt tegen de 10 seconden aan (geen snelle server overigens)!

Nu heb ik wat lopen zoeken en kom ik tot verschillende oplossingen aan de database kant, zoals JOINS. Echter vraag ik me ook af of de traagheid niet veroorzaakt wordt door de grote hoeveelheid classes die ik aanmaak. Ik wilde het graag netjes OO doen, maar dat lijkt ineens helemaal niet zo'n handige oplossing meer aangezien het totaal zo traag wordt.

Welke tips&tricks hebben jullie zoal voor deze problemen? Ik weet wel hoe ik JOINs op moet stellen of hoe ik een groot deel van m'n OO structuur eruit knikker, maar wat een goeie compromis tussen de verschillende opties? Want ik kan me niet voorstellen dat een grote JOIN zoveel sneller gaat zijn dan wat verschillende queries.

Een andere optie zou zijn om het overzicht te laten genereren als XML en op te slaan voor gebruik. Dan hoef ik alleen bij wijzigingen opnieuw een overzicht uit te draaien waardoor in ieder geval de serverload beperkt wordt. Doe dat echter liever niet.

[Verwijderd]

iCe01 schreef op maandag 21 november 2005 @ 14:12:
In het project waar ik aan bezig ben loop ik tegen wat optimalisatie kwesties aan. Het volgende is het probleem:

Uit de database wordt een lijst gehaald van "klussen". Deze lijst is 1 query waarbij de tabel (voor een deel) wordt geraadpleegd. Vervolgens wordt er voor iedere rij een class aangemaakt van het type "klus" waarin de gegevens gestopt worden.

Iedere klus bevat op zijn beurt weer een magazijn wat bestaat uit een x aantal werknemers, een y aantal vervoersmiddellen en een z aantal artikelen uit het totale magazijn. Nu wordt er per klus een nieuwe query gedaan om die zaken op te halen, eentje voor het personeel, eentje voor het vervoer en eentje voor de artikelen. Kortom: per klus 3 query's. Al deze zaken worden ook weer netjes in hun eigen class gestopt. Wanneer alle gegevens uiteindelijk opgehaald zijn worden ze in een XML boom gestopt die wordt afgedrukt en getransformeerd.

Er staan nu 138 klussen in de hoofdtabel, dus worden er voor het gehele overzicht 1+3x138 query's uitgevoerd: bah! Totale uitvoertijd van de pagina loopt tegen de 10 seconden aan (geen snelle server overigens)!

Nu heb ik wat lopen zoeken en kom ik tot verschillende oplossingen aan de database kant, zoals JOINS. Echter vraag ik me ook af of de traagheid niet veroorzaakt wordt door de grote hoeveelheid classes die ik aanmaak. Ik wilde het graag netjes OO doen, maar dat lijkt ineens helemaal niet zo'n handige oplossing meer aangezien het totaal zo traag wordt.

Welke tips&tricks hebben jullie zoal voor deze problemen? Ik weet wel hoe ik JOINs op moet stellen of hoe ik een groot deel van m'n OO structuur eruit knikker, maar wat een goeie compromis tussen de verschillende opties? Want ik kan me niet voorstellen dat een grote JOIN zoveel sneller gaat zijn dan wat verschillende queries.

Een andere optie zou zijn om het overzicht te laten genereren als XML en op te slaan voor gebruik. Dan hoef ik alleen bij wijzigingen opnieuw een overzicht uit te draaien waardoor in ieder geval de serverload beperkt wordt. Doe dat echter liever niet.

Elke keer een nieuwe query trekt natuurlijk veel performance weg, een goede join zou veel vertraging kunnen wegwerken. Postgres is OO dus daarvoor wel geschikt...

[sariel]

en je moet even kijken of je je indices goed hebt staan. als je geen indices gebruikt, en je zoekt op iets, dan kan de database echt een factor duizend trager zijn.

[whoami]

Het is gewoon niet slim om voor iedere object dat je wilt instantieren een aparte query te gaan doen. Dat is gewoon vragen om problemen.
Het is beter als je één query doet, die alle gegevens ophaalt, en dat je dan, adhv de resultset die je terugkrijgt, je objecten gaat instantieren.

Trouwens, als je een overzicht van 'klussen' wilt, dan heeft het toch geen zin dat je ook de details voor die klus gaat gaan ophalen ? Je hoeft die andere 3 queries per klus pas te doen als je die gegevens ook daadwerkelijk wilt zien / gebruiken. Dus, als je bv een detail van die klus bekijkt (lazy loading).

Echter, zoals Janoz ook al aangeeft: deze gegevens kan je zeker in één query binnentrekken. Ipv (138*3) queries, kan je het dus in één query doen. Je objecten maak je dan adhv je resultset.

Verwijderd schreef op maandag 21 november 2005 @ 14:14:
[...]


Elke keer een nieuwe query trekt natuurlijk veel performance weg, een goede join zou veel vertraging kunnen wegwerken. Postgres is OO dus daarvoor wel geschikt...

Vziw is Postgress een relationele DB.

[ Voor 31% gewijzigd door whoami op 21-11-2005 14:19 ]

[Janoz]

De bottleneck hiervan ligt duidelijk in de queries. Al die gegevens zijn in 1 query op te halen. Door een beetje met order by te spelen kun je nog steeds redelijk simpel je hele classestructuur opbouwen.

[jsiegmund]

Ok dan ga ik maar eens moeite doen om die query te bouwen en in een view te proppen. Lazy Loading is overigens niet van toepassing omdat ik in het overzicht die gegevens wel nodig heb. Ik kan hooguit wat kolommen weglaten, maar dat heeft zo weinig prestatiewinst tot gevolg dat het echt niet interessant is op 1 enkele query.

Is microtime() voldoende om een redelijke schatting van de database tijd te krijgen, of moet je daar dieper induiken met speciale functionaliteit?

Ga er meteen aan beginnen dus laat tzt wel weten hoeveel tijd het nu daadwerkelijk scheelt.

[decipherer]

Door in postgresql (bv in pgadmin) het commando EXPLAIN ANALYSE <query> te geven krijg je een goed beeld van de tijd die elk onderdeel van de query in beslag neemt. Ook kan je zien of indices e.d gebruikt worden.

Wellicht overbodig, maar bij postgresql is het ook erg belangrijk het configuratiebestand goed in te stellen. Dit kan queries echt vele malen sneller maken.

[Verwijderd]

whoami schreef op maandag 21 november 2005 @ 14:16:
Het is gewoon niet slim om voor iedere object dat je wilt instantieren een aparte query te gaan doen. Dat is gewoon vragen om problemen.
Het is beter als je één query doet, die alle gegevens ophaalt, en dat je dan, adhv de resultset die je terugkrijgt, je objecten gaat instantieren.

Trouwens, als je een overzicht van 'klussen' wilt, dan heeft het toch geen zin dat je ook de details voor die klus gaat gaan ophalen ? Je hoeft die andere 3 queries per klus pas te doen als je die gegevens ook daadwerkelijk wilt zien / gebruiken. Dus, als je bv een detail van die klus bekijkt (lazy loading).

Echter, zoals Janoz ook al aangeeft: deze gegevens kan je zeker in één query binnentrekken. Ipv (138*3) queries, kan je het dus in één query doen. Je objecten maak je dan adhv je resultset.


[...]



Vziw is Postgress een relationele DB.

PostGres is een OO Relationele DB dus hebben we allebei gelijk

Other advanced features include table inheritance, a rules systems, and database events. Table inheritance puts an object oriented slant on table creation, allowing database designers to derive new tables from other tables, treating them as base classes. Even better, PostgreSQL supports both single and multiple inheritance in this manner.

http://www.postgresql.org/about/

[jsiegmund]

DeCIpHeReR schreef op maandag 21 november 2005 @ 14:38:
Door in postgresql (bv in pgadmin) het commando EXPLAIN ANALYSE <query> te geven krijg je een goed beeld van de tijd die elk onderdeel van de query in beslag neemt. Ook kan je zien of indices e.d gebruikt worden.

Wellicht overbodig, maar bij postgresql is het ook erg belangrijk het configuratiebestand goed in te stellen. Dit kan queries echt vele malen sneller maken.

Zojuist even geprobeerd maar kan er maar weinig kaas van maken om eerlijk te zijn. Ik zie wel dat het ene deel veel meer tijd kost dan het andere, maar hoe je daar nou verandering in zou moeten brengen? Het komt meer over als: inderdaad, het is langzaam.

Configuratiebestand is wel een goeie, daar heb ik namelijk 0 aan veranderd, niets wat hierop betrekking zou kunnen hebben tenminste. Misschien wat hints/tips welke opties aardig te tunen zijn?

Heb met m'n JOINs de parsetijd terug weten te krijgen naar 4,5 seconden waarvan er volgens EXPLAIN ANALYSE 117.338 ms. in de query zouden zitten. Uiteindelijke view ziet er zo uit:

SQL:

CREATE VIEW klussen AS  
SELECT 
k.*,
to_char(k.begintijd,'DD-MM-YYYY') AS begintijd_format,
l.naam AS locatienaam, 
l.plaats AS locatieplaats, 
tl.naam AS templocatienaam, 
tl.plaats AS templocatieplaats, 
g.gebruikerid AS personeelid,
g.naam AS personeelnaam, 
gp.plandatum AS personeelplandatum,
v.naam AS vervoernaam, 
v.vervoerid AS vervoerid,
s.naam AS setnaam,
s.setid AS setid
   FROM klus k
   LEFT JOIN (gebruiktvervoer gv
   JOIN vervoer v ON v.vervoerid = gv.vervoerid) ON gv.klusid = k.klusid
   LEFT JOIN (gebruiktpersoneel gp
   JOIN gebruiker g ON g.gebruikerid = gp.personeelid) ON gp.klusid = k.klusid
   LEFT JOIN (gebruiktset gs
   JOIN "set" s ON s.setid = gs.setid) ON gs.klusid = k.klusid
   LEFT JOIN locatie AS l ON k.locatieid=l.locatieid
   LEFT JOIN templocatie AS tl ON k.templocatieid=tl.locatieid
ORDER BY 
k.begintijd,k.bedrijf,k.omschrijving;

Ben nu op een ander idee gekomen. Het aantal records in personeel / vervoer is dermate laag dat het misschien wel loont om die in een keer allemaal in te laden en vervolgens alleen de ID's in de tabel op te vragen. Scheelt een paar JOINs en PHP parse tijd. Echter om te proberen moet ik dan weer alles gaan omgooien... ideeen hierover?

[ Voor 47% gewijzigd door jsiegmund op 21-11-2005 18:54 ]

[ACM]

Verwijderd schreef op maandag 21 november 2005 @ 15:01:
PostGres is een OO Relationele DB dus hebben we allebei gelijk

Maar geen OO database. Er zit een wereld van verschil in, waarbij Postgres' OO-systeem maar marginaal lijkt op wat je van bijvoorbeeld Java als OO gewend bent.

[jsiegmund]

Na wat geklooi zit ik toch tegen een paar dingen aan die me niet helemaal helder zijn.

Bijvoorbeeld!
Een klus heeft 2 velden: locatieid, templocatieid. Dat zijn foreign keys van de tabellen "locatie" en "templocatie". Het verschil daarin is dat een templocatie wordt weggegooid wanneer de klus wordt verwijderd, het andere type locatie blijft beschikbaar. Dat is handig wanneer je sommige locaties vaak wilt hergebruiken en van andere locaties weet dat je er toch maar 1x komt.

Nu wil ik die informatie in een keer ophalen. Dat kan met een JOIN natuurlijk, maar ik zou hetvolgende willen doen:
- controleren of er een templocatieid of locatieid is ingevuld
- de juiste locatie ophalen uit de betreffende tabel (kolommen zijn hetzelfde)
- de gegevens als enkele rij teruggeven, lieft met locatie_ als prefix voor de locatienamen

Is zoiets uberhaupt te doen binnen Postgres of ga ik het mezelf nu erg moeilijk maken?

Het gaat hierbij overigens wel om lazy loading! Dus evt kan ik het laten zoals het was: gegevens gewoon met een extra query uit de database halen. Uit welke tabel ze moeten komen kan ik dan in PHP wel uitzoeken.

[Verwijderd]

iCe01 schreef op maandag 21 november 2005 @ 21:11:
Na wat geklooi zit ik toch tegen een paar dingen aan die me niet helemaal helder zijn.

Bijvoorbeeld!
Een klus heeft 2 velden: locatieid, templocatieid. Dat zijn foreign keys van de tabellen "locatie" en "templocatie". Het verschil daarin is dat een templocatie wordt weggegooid wanneer de klus wordt verwijderd, het andere type locatie blijft beschikbaar. Dat is handig wanneer je sommige locaties vaak wilt hergebruiken en van andere locaties weet dat je er toch maar 1x komt.

Nu wil ik die informatie in een keer ophalen. Dat kan met een JOIN natuurlijk, maar ik zou hetvolgende willen doen:
- controleren of er een templocatieid of locatieid is ingevuld
- de juiste locatie ophalen uit de betreffende tabel (kolommen zijn hetzelfde)
- de gegevens als enkele rij teruggeven, lieft met locatie_ als prefix voor de locatienamen

Is zoiets uberhaupt te doen binnen Postgres of ga ik het mezelf nu erg moeilijk maken?

Het gaat hierbij overigens wel om lazy loading! Dus evt kan ik het laten zoals het was: gegevens gewoon met een extra query uit de database halen. Uit welke tabel ze moeten komen kan ik dan in PHP wel uitzoeken.

Heb je al eens een garbage collect gedaan in postgres? De DB ruimt zijn oude records namelijk niet zelf op, volgens mij heet dat VACUUM. (gejat uit ander topic )

[ Voor 4% gewijzigd door Verwijderd op 22-11-2005 09:28 ]

[jsiegmund]

Verwijderd schreef op dinsdag 22 november 2005 @ 08:59:
[...]

Heb je al eens een garbage collect gedaan in postgres? De DB ruimt zijn oude records namelijk niet zelf op, volgens mij heet dat VACUUM. (gejat uit ander topic )

Gedaan maar maakt qua prestaties niks uit. Beetje loze opmerking ook vind ik, zal vast handig zijn als je al een hele hoop records in je database hebt gehad maar op die 138 die ik nu heb maakt het geen donder uit.

[Verwijderd]

iCe01 schreef op dinsdag 22 november 2005 @ 14:01:
[...]


Gedaan maar maakt qua prestaties niks uit. Beetje loze opmerking ook vind ik, zal vast handig zijn als je al een hele hoop records in je database hebt gehad maar op die 138 die ik nu heb maakt het geen donder uit.

als je bijvoorbeeld al 400 tests gedaan hebt kan je maximaal 138*400 = 55.200 records, dus heel veel troep hebben, zo loos vind ik het ook niet dan...

[ Voor 10% gewijzigd door Verwijderd op 22-11-2005 14:46 ]

[ACM]

iCe01 schreef op dinsdag 22 november 2005 @ 14:01:
Gedaan maar maakt qua prestaties niks uit. Beetje loze opmerking ook vind ik, zal vast handig zijn als je al een hele hoop records in je database hebt gehad maar op die 138 die ik nu heb maakt het geen donder uit.

Daar kan je vrij stevig de mist in gaan, stel je de volgende situatie voor:
Stel je hebt 10 records van elk 1KB met informatie, die werk je elke seconde bij om status ofzo te veranderen. Elke seconde heb je dan dus je originele 10 records en 10 kopieen met de vorige versie.
Als je die niet automatisch opruimt neemt dat elke seconde dus met 10KB toe, ondanks dat je dacht maar 10KB in totaal te hebben.
Dat een paar dagen laten draaien met de gedachte "och, het is toch maar 10KB" zorgt er dus voor dat het met 86400 * 10KB per dag groeit... 860MB per dag erbij dus. En elke dag zal de toegang tot je records dan ook weer een heel stuk trager worden, omdat postgres van elke oude record moet achterhalen of het inderdaad een oude is.

Als je weinig doet met je records, dan is het vacuum-en een stuk minder belangrijk inderdaad. Maar als je er dus wel veel mee doet, maar toch weinig records hebt kan het al heel snel lonend zijn om (vaak) vacuum-commando's uit te voeren. Gelukkig is er sinds postgres 8.0 de "auto vacuum daemon", die zoekt zelf uit wanneer zo'n vacuum nodig is.

Overigens heeft het doen van veel queries, zonder gunstig index gebruik in jouw geval meer invloed op de prestaties, gok ik zo

[ Voor 5% gewijzigd door ACM op 22-11-2005 14:45 ]

[jsiegmund]

Heb de VIEW nu wat aangepast, overbodige personeel en vervoer JOINs eruit gegooid en met 2 extra queries haal ik al het vervoer en al het personeel op. Conclusie is dat ie er sneller van wordt, niet langzamer, hoewel het niet heel veel scheelt.

Zit nu op ongeveer 1,5 seconde om een pagina met 75 rijen op te halen. Daarbij genomen dat het een trage server is die ook nog een hoop andere dingen staat te doen ben ik al best tevreden. Vind toch dat het nog wat sneller moet kunnen, wil graag onder de seconde uitkomen

VACUUM is dus blijkbaar wel belangrijk maar dan knal ik die deamon wel aan, net zo handig. Ik merkte er geen verschil in verder. Excuses voor de onwetendheid

[ Voor 3% gewijzigd door jsiegmund op 22-11-2005 14:54 ]

[jsiegmund]

Nog een volgende query vraag (sorry!)

Ik krijg nu een lijst terug van een hoop klussen, waartussen zich door de JOINs een aantal dubbele regels bevinden. Ok, dat is gedrag zoals verwacht, maar wel lastig navigeren!

Normaal doe ik dat gewoon emet LIMIT en OFFSET, wat prima werkt. Maar aangezien er nu steeds een aantal regels weg valt gaat dat niet meer op. Ik kan niet voorspellen hoeveel dubbele regels erin zitten dus weet ik niet wat de LIMIT moet zijn om een bepaald aantal regels terug te krijgen. Hoe kun je dit fatsoenlijk oplossen? Kan natuurlijk een hele hoop opvragen en dan in PHP een zwikje eruit gooien, maar dan ben ik heel m'n mooie snelheidswinst weer kwijt!

[ACM]

Kijk eens naar GROUP BY. Daarin stel je groepen van records samen, maar let wel op dat je geen samenhang in losse records hebt dan.

Mocht je puur door de joins dubbele records krijgen, zonder verschillen in specifieke velden (dus meerdere klussen en per klus een andere uitvoerder ofzo), kijk dan of je niet je query wat aan kan passen zodat je toch gewoon maar 1 resultaat per klus terug krijgt. Subqueries kunnen je dan helpen om de boel het gewenste resultaat te laten geven.

[jsiegmund]

Hmmm ik snap niet helemaal welke kant je daarmee op wilt.

Klein voorbeeldje: een klus is gerelateerd aan een aantal vervoersmiddelen dmv de tabel "gebruiktvervoer". Die bevat dus 2 velden: klusid en vervoerid.

Wanneer er nu 2 vervoersmiddelen op 1 klus staan krijg ik dus het volgende resultaat:

code:

klusid   |  verschillende klusdata   |   vervoerid
------------------------------------------------------------------------
120                blabla                        1
120                nog eens bla                  2

logisch aangezien de JOIN ervoor zorgt dat iedere rij uit gebruiktvervoer een keertje voor komt. Nog erger wordt dat wanneer hetzelfde gebeurd met 2 personeelsleden, dan worden het dus 4 rijen! Dit product loopt niet snel uit de hand aangezien er nooit heel erg veel combinaties zijn, maar is toch wel vervelend. Een klus komt nu meerdere malen voor waardoor LIMIT en OFFSET moeilijk te gebruiken is.

Wat ik precies met GROUP BY kan doen om dit op te lossen snap ik niet?

[ Voor 5% gewijzigd door jsiegmund op 22-11-2005 22:41 ]

[ACM]

Als je de moeite wilt nemen een eigen "aggregate function" in php te maken (leuke oefening), kan je zoiets doen:

SQL:

SELECT k.klusid, 
k....
    my_agg(verschillende klusdata) as etc,
    my_agg(v.vervoerid) as vervoerids
FROM klussen k JOIN vervoer v ON v.klusid = k.klusid
GROUP BY k.klusid ...
LIMIT 123

Als je my_agg dan alles in elkaar plakt ala: "veld1, veld2, veld3 ....", dan kan je dat vrij eenvoudig in php weer uit elkaar halen. Echt mooi is het niet, maar in sommige gevallen handiger dan weer allerlei losse queries uitvoeren. Bij mijn weten heeft postgres standaard niet een functie die zoiets kan.

Een andere oplossing is zoiets:

SQL:

SELECT k..., v...
FROM 
(SELECT * FROM klussen ORDER BY ... LIMIT ...) k
JOIN vervoer v ON v.klusid = v.klusid
...

Waarbij je dus in je php-code dan voor elke klus wel meerdere records kunt hebben, maar wel een beperkt aantal klussen. Maar als je bijhoudt met welke klus je bezig bent te tonen, kan je vrij eenvoudig detecteren dat er een nieuwe klus aan zit te komen in het resultaatset.

[jsiegmund]

Thanks, zal morgen eens kijken naar die functies, ben niet vies van wat zoekwerk dus dat komt wel goed. Zal de uiteindelijke oplossing hier plaatsen of misschien verdere vragen mochten die boven komen drijven!

[jsiegmund]

Aggregate functie heb ik nu als volgt gebruikt:

SQL:

SELECT 
ko.klusid,
array_accum(ko.personeelid || '/' || ko.personeelplandatum) AS personeel,
array_accum(ko.vervoerid) AS vervoer,
array_accum(ko.setid) AS set
FROM klusoverzicht ko
GROUP BY ko.klusid;

Wat er gebeurd is dat de verschillende opties van de velden personeelid, personeelplandatum, vervoerid en setid in een array worden gestopt. Dus krijg je uiteindelijk maar een enkele rij terug per klus!

Bijkomend voordeel is dat ik in PHP die array makkelijk kan manipuleren en dus de dubbele keys eruit kan gooien zodat ik alleen over houdt wat ik wil hebben. Moet dat deel nog even implementeren maar dat moet geen probleem zijn.