Performance aspecten in een (R)DBMS – Deel 2

Performance aspecten in een (R)DBMS.  Deel 2: Mogelijkheden binnen de applicatie

Gebruik van stored procedures, verwerking op server

Als door een client een overzicht op scherm of een rapport aangevraagd wordt waarvoor veel selecties op de database nodig zijn, mogelijk gecombineerd met andere bewerkingen en waarbij aan het einde mogelijk maar weinig gegevens teruggaan naar de client, dan is dit vaak op de meest efficiënte, eenvoudige (is ook: onderhoudbare) en betrouwbare manier te schrijven als volgt:

• De client roept een stored procedure aan die alle selecties en bewerkingen op de server uitvoert. Aan het einde wordt het resultaat teruggegeven aan de client.
• De client verzorgt alleen de opmaak van de teruggekomen gegevens en berekent hooguit nog een totaalregel over de ontvangen records.

Dit geeft bijna altijd de beste performance omdat bijna alles binnen de server kan worden afgehandeld en er dus weinig communicatie tussen client en server nodig is. Verder is de SQL code vaak beter te lezen dan code van de client of een combinatie van client code met nog wat selecties en bewerkingen in de stored procedures. Natuurlijk moet de server wel voldoende capaciteit hebben om bewerkingen voor soms heel veel clients te kunnen verwerken.

Ook voor minder complexe situaties (insert een record, verwijder alle records die ouder zijn dan een bepaalde datum) zal een stored procedure meestal (iets, maar niet veel) sneller zijn dan het losse SQL commando. Het is echter niet zo dat een stored procedure altijd sneller is. Als op een scherm een gebruiker gegevens kan opvragen aan de hand van een aantal selectievelden (query by form, bijvoorbeeld: selecteer alle klanten aan de hand van de opgegeven achternaam en/of postcode en/of woonplaats en/of klanttype en/of …) dan kan hiervoor een ingewikkeld SQL commando geschreven worden in de trant van:

select naam, straatnaam, huisnummer, postcode, woonplaats
 from klant
 where (naam like  or  is null)
 and (postcode like  or  is null)
 and etc.

Zeer waarschijnlijk zal de optimizer hier een table scan kiezen om de gegeven op te halen. Veel 4GL’s kunnen echter een query genereren die alleen de ingevulde velden bevat en waarin de “or .. is null” dus niet nodig is. Deze query wordt geëvalueerd en geoptimaliseerd en waarschijnlijk zal hier wel een goede index gebruikt kunnen worden en zal de losse query dus een betere performance geven.

Een ander probleem met bovenstaande query is dat de gebruiker selectieparameters kan inbrengen waaraan zeer veel records voldoen (woonplaats like “A%”). Er moet dan een enorme hoeveelheid gegevens door de database server worden gelezen, naar de client verstuurd en op de client verwerkt. Op elk punt (database server, netwerk en op de client) kan dit tot performance problemen leiden. Op de client kan het mogelijk zelfs leiden tot vastlopen van het programma. Ook andere gebruikers hebben hiervan last, hun responstijden zullen ook teruglopen. Om dit probleem te voorkomen kan voor een bepaald soort functies (bijvoorbeeld het opzoeken van een klantnummer [zie ook 8]) afgesproken worden dat er maximaal 100 records worden opgehaald. Als de gezochte klant er niet bij zit kan de gebruiker andere of meer zoekcriteria opgeven of mogelijk opdracht geven om het aantal terug te geven rijen te verhogen (telkens met 100). De gegevens mogen hierbij ook niet opgevraagd worden met een order by clause omdat het DBMS dan toch eerst alle gegevens ophaalt, sorteert en er slechts 100 teruggeeft aan de client en niet 100 records ophaalt en die gesorteerd teruggeeft!

Let ook op als een gebruiker geautoriseerd is zelf om queries uit te voeren op de database, dus buiten de applicatie om. Hierbij kunnen gemakkelijk queries geschreven worden die een enorm performance probleem geven waar ook alle andere gebruikers last van hebben. Beter is om de gebruiker alleen de database te laten benaderen via de applicatie. Voor eventuele incidentele vragen kunnen door het onderhoudsteam stored procedures geschreven en getest worden. De gebruiker kan dan via deze stored procedures (met de goede parameters) extra queries uitvoeren zonder dat er veel overhead voor het schrijven van nieuwe programma’s nodig is.

Triggers, rules en constraints

Triggers op tabellen zijn stukjes coding die worden afgevuurd telkens wanneer op een tabel een insert, update of delete wordt uitgevoerd. Rules en constraints bevatten validaties die op de tabel of een kolom gedefinieerd worden, bijvoorbeeld de primary en foreign key constraints of een rule die bepaalt dat de kolom sexe in de medewerkertabel alleen de waarde M of V mag hebben. Triggers kunnen gebruikt worden voor ingewikkelde validaties die (bijna) niet in een rule of constraint te definiëren zijn. Verder gebruik ik triggers voor het bijhouden van redundante gegevens (saldo klant, zie elders in dit artikel) of historie [zie 5].

Bedenk dat een trigger enige overhead geeft, zelfs al wordt geen coding uitgevoerd omdat de trigger bijvoorbeeld alleen gebruikt wordt als een bepaalde kolom gewijzigd is. Ook rules en constraints zorgen voor overhead.

Bij client/server toepassingen op een MS Windows PC of een UNIX programma en VT220 terminal als client worden controles vaak 2 maal uitgevoerd:

• eerst al de gebruiker het veld verlaat (is de waarde correct, bestaat deze klant)
• en daarna nogmaals bij het inserten in de database door het afvuren van de trigger, rule of constraint.

Bij blokmode terminals (3270 terminals van IBM mainframe of de schermen van een AS400) zal vaak eerst een heel scherm volgetikt worden waarna de inhoud door het systeem wordt gecontroleerd en verwerkt. Dit gebeurt meestal ook zo bij WEB applicaties als er validaties tegen de database nodig zijn, dit om communicatie over het internet tussen client en de ver verwijderde server te vermijden.
De eerste methode (meteen controleren) is gebruikersvriendelijker (de gebruiker wordt direct op zijn fout gewezen) maar levert wel meer overhead op omdat een controle 2 maal wordt uitgevoerd (dus 2 maal het lezen van de klanttabel in de database). Het alternatief om de 2e controle over te slaan is ook minder gewenst omdat tussen de 1e controle en de feitelijke update de inhoud van de database gewijzigd kan zijn en er aldus inconsistenties kunnen ontstaan.

Dit alles is natuurlijk een standaardisatie of architectuur vraag waarbij de overhead van extra controles (voor de performance, maar ook dubbele coding in de programmatuur) moet worden afgewogen tegen voordelen (gebruikersvriendelijk) en de mogelijkheden (op een blokmode terminal is er geen keuze).

Nette where clause

Het schrijven van nette coding leidt zeker tot minder fouten en een beter onderhoudbaar systeem, maar mogelijk ook tot een betere performance of tenminste tot coding waar een performance probleem gemakkelijker geanalyseerd kan worden. Met name van de where clause weten sommige programmeurs een onoverzichtelijke kluwen te maken waar ze zelf ook nauwelijks meer een begin of einde aan zien. De volgende regels gegeven een nette, goed leesbare en onderhoudbare where clause. [Zie voor een uitgebreide toelichting ook 6].

De where clause dient steeds geformuleerd te worden met links van de operator (=, <, >, etc.) een kolom uit de tabel en rechts van de operator de waarde waarmee deze kolom wordt vergeleken (een constante, een programmavariabele, een andere kolom uit dezelfde rij of een berekening met een combinatie van deze mogelijkheden, Bijvoorbeeld: postcode = “1111AA”).
De where clause dient te beginnen met de condities voor de primaire sleutel, dan de condities voor eventuele kandidaatsleutels, dan de condities op buitensleutels of op de kolommen waarop (waarschijnlijk) een index staat en daarna de andere condities. Natuurlijk kunnen later indexen worden verwijderd of toegevoegd zonder dat dit leidt tot aanpassingen in de query.

Bij een join van 2 of meer tabellen worden eerst de condities voor de 1e tabel, daarna de condities voor de 2e tabel geschreven, etc. Bij 2e tabel wordt begonnen met de conditie die deze tabel joint met de eerste tabel. De condities voor de tweede tabel zijn verder gelijk aan de condities voor de eerste tabel, echter rechts van de operator kan nu (behalve bijvoorbeeld een constante of een programma-variabele) ook een waarde uit de eerste tabel staan.
Daarna worden de condities voor de derde tabel geschreven die weer met de 2e of de 1e tabel wordt gejoint, etc.

Voor een goede performance moeten hierbij de tabellen in de volgende volgorde worden benaderd:

•  vermeldt eerst de condities voor de tabel die de resultaatset zoveel mogelijk beperkt;
• vermeldt daarna de condities voor de tweede tabel, die daarbij met de eerste tabel wordt gecombineerd;
• vermeldt daarna de condities voor de derde tabel, die daarbij met de eerste of de tweede tabel wordt gecombineerd, etc.

Er zijn nog een aantal performance aspecten waarbij rekening gehouden moet worden bij het schrijven van de where clause (of mogelijk eerder bij het opstellen van het gegevensmodel):

• Voer rechts van de operator geen bewerkingen of berekeningen die voor elk record herhaald moeten worden, bijvoorbeeld het ophalen van de systeemtijd (where datum < sysdate()). Deze bewerking kan ook voor de query worden uitgevoerd waarbij het resultaat in een programmavariabele worden geplaatst.
• Gebruik links van de operator zoveel mogelijk een kolom van de tabel en geen bewerking op deze kolom (where upper(naam) = “JAN”) omdat dan waarschijnlijk geen index gebruikt kan worden.
• Als links en rechts niet hetzelfde type staat (bijvoorbeeld links een integer en rechts een decimal of float) dan zal het RDBMS (links of rechts) een impliciete conversie plaatsen met ook hier weer de mogelijkheid dat de index niet gebruikt kan worden.
• Bedenk dat bij een vraag als geen index gebruikt kan worden maar bij kan wel een index gebruikt worden. Vermijd derhalve queries waarbij een wildcard aan het begin staat en wijs de gebruiker op het feit dat bij bepaalde queries een langere wachttijd onvermijdelijk is.

Optimizers

Aan de hand van de coding, al of niet volgens bovenstaande aanwijzingen geschreven, moet de optimizer een querypad bepalen. Bij ingewikkelde queries met subselects etc. behoeft dit niet altijd het meest optimale resultaat te zijn. Het kan dan voordelig worden om de query zelf te splitsen in enkele delen met een tijdelijke tabel voor de tussenresultaten. Een performance cursus van de leverancier zal dieper ingaan op de specifieke problemen bij de optimizer van hun product. Maak hiervan echter verstandig/beperkt gebruik. Bedenk dat in een volgende versie de optimizer gewijzigd (verbeterd) zal zijn en betere of in elk geval andere conclusies kan trekken. Ik heb meegemaakt dat we een systeem volledig getuned hadden, dwz dat alle queries waren uiteindelijk zo geschreven dat de optimizer het meest efficiënte pad koos om de gegevens op te halen. De optimizer van de volgende versie van het systeem was waarschijnlijk wel beter, maar de queries werden opnieuw bekeken en het kon hierbij eigenlijk niet meer beter; alleen op enkele punten kan het wel anders en in ons geval was dat dus altijd slechter. Mijn ervaring is dan ook dat bij een zeer goed getuned systeem altijd een goede performance test noodzakelijk is als op een nieuwe release van het DBMS wordt overgestapt om te controleren of de performance nergens terugloopt.

De optimizer bepaald zijn pad vaak aan de hand van gegevens over tabel grootte en verdeling van sleutels in de index. Hiervoor moeten deze gegevens wel actueel zijn bijgewerkt. Dit kan (zie ook de performance tips van de leverancier) met een commando “update statistics”. Het is dan ook belangrijk dat dit statement (voor elke tabel) wordt uitgevoerd, zeker als in het begin van het gebruik van het systeem de vullingsgraad van de tabellen nog vaak wijzigt.

Het reorganiseren van een tabel kan ook soms (erg product afhankelijk) verbeteringen geven. Bijvoorbeeld als in een tabel de gegevens op een bepaalde volorde liggen (orders op ordernummer) en oude geheel verwerkte orders uit het systeem worden verwijderd. Als er nog wat oude maar niet verwerkte order blijven staan, dan kan het zijn dat een groot aantal datapages slechts 1 (oude) rij bevat. Deze page kan dan niet voor nieuwe gegevens worden gebruikt. Een tabel scan over deze tabel zal dus veel bijna lege pages lezen en dus veel IO moeten doen. Na reorganiseren zal een table scan veel sneller zijn. Voor het prikken op zo’n tabel zal de reorganisatie minder voordeel hebben. Bedenk ook dat bij reorganisatie de tabel mogelijk (waarschijnlijk) niet door het systeem gebruikt kan worden (tijdelijk gelockt is).

Locking en deadlock aspecten

Als een transactie (= enkele bij elkaar horende wijzigingen op de database, bijvoorbeeld een order met orderregels en het bijwerken van de voorraad bij het artikel) naar de database wordt geschreven, dan mogen andere processen (gebruikers) deze gegevens niet zien zolang de transactie niet geheel op de database is verwerkt. Dit wordt door de programmatuur aangegeven door aan het begin van de transactie het commando “begin work” en aan het einde het commando “commit work” of “rollback work” naar de server te sturen. Als een andere gebruiker deze gegevens wil opvragen dan zal hij even moeten wachten. De gebruiker zal niet weten waarop hij wacht, hij zit maar naar zijn zandlopertje te staren, en hij ervaart deze situatie (als die te lang duurt) dus gewoon als een slechte performance.

Om dit probleem zoveel mogelijk te voorkomen moeten transacties zo kort mogelijk gehouden worden, dat betekent:

• Online functies mogen nooit een transactie open hebben staan terwijl de gebruiker gegevens op het scherm inbrengt of wijzigt. Meestal zal zo’n functie alleen maar lezen (voor validaties tegen de database of om referentiegegevens op te halen; hiervoor is geen transactie nodig) en aan het einde, bij een klik op de verwerk toets (of bij Ctrl-S) zullen de ingetoetste gegevens in één transactie op de database worden verwerkt (optimistic concurrency control).
• Batch functies die tegelijk met online functies kunnen draaien en die grote hoeveelheden gegevens verwerken (een tape of diskette met orders inlezen) moeten per eenheid (order of zelfs per orderregel) een transactie definiëren.

Alleen een batch functie die alleen op de database draait (’s nachts, geen andere batch en geen on-line gebruikers) mag wel grote transacties gebruiken. Dit is zelfs beter omdat elke transactie enige overhead geeft en een klein aantal grote transactie sneller verwerkt zullen worden dan een groot aantal kleine transacties. Een commando om een tabel in zijn geheel te locken kan gebruikt worden om extra overhead van het locken van individuele pages of records tijdens de transactie te vermijden.

Als het RDBMS de keuze biedt tussen record locking (row level locking) of page level locking zal page level locking altijd een betere performance geven. Alleen de kans dat een andere gebruiker op gelockte gegevens moet wachten en ook de kans op een deadlock is bij page level locking groter. Een deadlock doet zich voor als gebruiker A wacht op een gegeven dat door gebruiker B is gelockt en gebruiker B wacht op een gegeven dat door gebruiker A is gelockt. Dit wordt door het RDBMS gesignaleerd waarna de transactie van één van de twee gebruikers wordt afgebroken en teruggedraaid met een foutmelding aan de gebruiker. Deze kan daarna proberen de transactie opnieuw aan het systeem aan te bieden waarbij het waarschijnlijk wel goed zal gaan. Bij een deadlock op transacties in een belangrijk batch proces (verwerking van een financiële transactie bij een bank) zal het batchprogramma de deadlock status moeten afvangen en de transactie opnieuw aan het RDBMS aanbieden.

Batchverwerking

Kleine lijsten of rapporten, die binnen enkele seconden samengesteld kunnen worden, kunnen het beste direct door het on-line gedeelte van het systeem worden samengesteld en opgemaakt. Wordt de doorlooptijd langer, dan is het beter dat deze opdracht wordt afgesplitst naar een batchverwerking.

Een batchverwerkingsfunctie kan eenvoudig worden gebouwd als volgt [zie ook 8]:

•  Een tabel in de database (de batchqueue) met als kolommen onder meer: userid-aanvrager, datum-tijd-aanvraag, datum-tijd-verwerking, prioriteit, status, rapport-code, parameters.
• Bij het on-line gedeelte van de functie geeft de gebruiker eventuele selectieparameters op en wordt een record in deze tabel geplaatst.
• De batchverwerking draait, afhankelijk van de omgeving of programmeertaal, op de UNIX of NT database server of op een speciaal hiervoor ingerichte PC. Dit programma draait continue of bijvoorbeeld dagelijks van 1 uur in de nacht tot 10 uur in de avond (buiten de backup tijd). Dit programma leest in de batchqueue geplaatste opdrachten en voert deze uit.

Omdat de opdrachten na elkaar worden uitgevoerd wordt voorkomen dat een systeem verstopt raakt (en de on-line gebruikers dus een slechte performance van hun functies ervaren) wanneer een gebruiker een tiental rapporten direct na elkaar opstart. Ook kan aan zeer zware functies (door het systeem of de gebruiker) worden aangegeven dat deze alleen in de nacht of weekend mogen draaien. Dit geldt zeker voor functies die intensief wijzigen op de database, bijvoorbeeld een schoningsrun, niet alleen vanwege de belasting van de machine maar ook in verband met de kans op lockingproblemen.
Verder kan de batch functie onder UNIX en ook de hierbij afgevuurde SQL opdrachten in de database server een lagere prioriteit gegeven worden, zodat on-line gebruikers altijd eerst worden geholpen en dus een betere performance ervaren.

De batchverwerker kan natuurlijk ook, naast on-line opgegeven opdrachten, elke nacht of weekend beginnen met een aantal vaste opdrachten die dagelijks resp. wekelijks uitgevoerd moeten worden, bijvoorbeeld vaste rapportages, data-import- en export functies, opschonen van de gegevens, etc.

Andere overhead

Ga na wat er nog meer in de architectuur / infrastructuur is opgenomen behalve de client toepassing zelf (4 GL of C programma op PC, in UNIX of andere applicatieserver) en het RDBMS. Netwerkrouters of koppelingen (mogelijk loopt een deel van de communicatie via internet), TP-monitors of object brokers, gedistribueerde database management systemen, etc. leveren allemaal overhead. Dit kan in een gedistribueerde omgeving nodig zijn maar het kan ook de response tijden sterk nadelig beïnvloeden. Als deze overhead voor een systeem niet nodig is, laat deze dan weg.

Bekijk hier het eerste artikel over performance aspecten