Performance aspecten in een (R)DBMS – Deel 1

Performance aspecten in een (R)DBMS.  Deel 1: Introductie 

Er is waarschijnlijk geen onderwerp waarover door DBA’s zoveel wordt gepraat. In dit artikel worden de belangrijkste punten beschreven waarmee bij het database ontwerp rekening gehouden moet worden om tenminste een basis voor een goede performance te leggen.

Veel performance aspecten zijn echter product specifiek. Hiervoor is een cursus (of handboek) “Performance en tuning” van de betreffende leverancier noodzakelijk. Maar er zijn veel aspecten of tips die productonafhankelijk zijn en die ook zeker toegepast moeten worden voordat naar de productspecifieke problemen wordt gekeken. Dit artikel beschrijft alleen deze productonafhankelijke aspecten en tips.

Wat is een goede performance?

Een goede performance is in eerste instantie wat de gebruiker als zodanig zal ervaren. We moeten hierbij rekening houden met verschillende omstandigheden:

• Het intoetsen van een letter of cijfer in een veld op het scherm moet direct zichtbaar zijn op het scherm, dus zonder waarneembare vertraging.
• Het opvragen van eenvoudige gegevens, bijvoorbeeld van 1 record (artikel gegevens) moet binnen 1 seconde op het scherm staan.
• Het opslaan van nieuwe gegevens of wijzigingen op bestaande gegevens moet binnen 1 tot 3 seconden worden uitgevoerd, dit afhankelijk van de hoeveelheid gegevens (updates op 1 record in 1 seconde, een order met een aantal orderregels en mogelijk nog andere gerelateerde gegevens zoals een afleveradres) mag 2 tot 3 seconden duren.
• Het opvragen van een eenvoudig lijstje, een factuur, etc. mag (afhankelijk van de complexiteit en de hoeveelheid te benaderen gegevens) van enkele seconden tot een minuut duren voordat het lijstje op het scherm gepresenteerd wordt of op de printer wordt afgedrukt.
• Grote batchverwerkingen of overzichten waarin veel gegevens moeten worden doorlopen en/of berekeningen nodig zijn, mogen langer duren. Hierbij is het vaak noodzakelijk enkele uren als doorloop te accepteren of zelfs te accepteren dat de verwerking alleen in de nacht of in het weekend kan worden uitgevoerd.

Het bovenstaande zijn algemeen aanvaarde eisen, maar toch zien we dat web pagina’s op internet regelmatig tot een minuut nodig hebben voordat ze op het scherm staan. Normen zijn blijkbaar aan verandering onderhevig.
Soms zullen aan functies heel specifieke performance eisen gesteld worden. Dit moet dan in het ontwerp van de betreffende functie worden vermeld terwijl voor de andere functies de (default) eisen uit de standaards gelden. Deze kunnen globaal overeenkomen met bovenstaande eisen.

De responstijden zijn hier nog beschreven zoals een gebruiker deze ervaart in zijn toepassing. Binnen een functie kunnen vaak een groot aantal queries naar een database gaan voor dat de taken van de functie zijn uitgevoerd, bijvoorbeeld een aantal validaties (bestaat deze en gene code in de referentietabellen) en een aantal inserts en updates (bijvoorbeeld het inserten van order en orderregel en het bijwerken van de voorraad bij het artikel en het uitstaand saldo bij de klant). Om de gebruiker bovenstaande performance te garanderen zullen de select, insert, update en delete statements een fractie van deze tijd (vaak minder dan een tiende van een seconde) mogen duren.

In de volgende hoofdstukken geven we een toelichting op de performance aspecten waarmee (in elke omgeving, dus product onafhankelijk) rekening gehouden moet worden.

Het logisch gegevensmodel en de vertaling naar het fysieke model

Het logisch gegevensmodel moet in eerste instantie zuiver en volgens de regels (voor normaliseren, etc. [zie ook 2, 3, 4, 5 en 7]) worden opgesteld. Performance aspecten komen pas aan de orde bij het vertalen van het logisch naar het fysieke model. Vaak, als de 2 modellen erg veel op elkaar lijken, dus als er weinig afwijkingen zijn tussen logisch en fysiek model, kan men besluiten om, na deze vertaling, 1 model te onderhouden. In dit geval wordt in praktijk het fysieke model gepromoveerd tot het te onderhouden model. In het gecombineerde model zijn dan wel alle performance aspecten verwerkt.

Performance aspecten: het RDBMS

Bij de vertaling naar het fysieke model heeft de Oracle, MySQL of SQL Server DBA de volgende mogelijkheden om de basis te leggen voor een goede performance:

1. Plaatsen van indexen.
2. Distributie van de fysieke database of tabellen over meer schijven.
3. Toevoegen van redundante gegevens.
4. Herindeling van tabellen.
5. Gebruik van de fysieke gegevenstypes.

Deze aspecten worden hierna eerst beschreven.
Daarna komen nog een aantal performance aspecten aan de orde waarmee tijdens de bouw van de applicatie rekening gehouden moet worden.

1. Mogelijkheden in het RDBMS

Indexen

In het algemeen gebruik ik een CASE tool voor het vastleggen van het gegevensmodel. Bij het genereren van de create table definities zal dit tool automatisch (unieke) indexen genereren op de primary key en de candidate keys en (niet unieke) indexen op de foreign keys. Dit is een goede default, maar er valt vaak aan deze keuzes nog wat te verbeteren door (in het CASE tool) op te geven waar nog meer indexen moeten komen of waar ze juist niet nodig zijn.

Een index op de foreign key kolommen is natuurlijk een default. Vaak worden deze kolommen gebruikt in een join of op een scherm als zoekargument opgegeven. In dit geval is een index inderdaad nodig. Maar het is zeker noodzakelijk om na te gaan of:

• deze foreign key inderdaad zo gebruikt wordt, indien niet dan is deze index niet nodig.
• of er nog andere kolommen zijn waar het systeem of de gebruiker gegevens op kan selecteren (of sorteren). Ook op die kolommen (of combinatie van kolommen) kan een index nodig zijn.

Op kleine tabellen zijn geen indexen nodig (behalve de indexen die ook de unieke waarde van een sleutel bewaken). Voor klein hanteer ik de volgende regel:

• Is de tabel kleiner dan 8 pages (van 2 Kb) dan geen indexen.
• Is de tabel groter dan 32 pages dan wel indexen.
• Daartussen ligt een grijs gebied: Als de tabel voornamelijk gelezen wordt, dan wel indexen, als er voornamelijk gewijzigd wordt dan geen indexen. Als de tabel veel indexen nodig zou hebben, dan maar liever helemaal geen indexen.

Sommige producten bieden verschillende soorten indexen:

• Voor maximaal 1 index kan worden aangegeven dat de gegevens in de tabel geheel op deze volgorde worden gehouden (de clustered index). Dit kan voordelig zijn als gegevens uit de tabel vaak op deze volgorde gesorteerd of gegroepeerd worden opgevraagd, bijvoorbeeld de orderregels van een order (met primary key: ordernummer, orderregelnummer).
• De meeste indexen bevatten een B-tree structuur. Het hoogste niveau van deze index heeft 1 pagina met een grove indeling van de index. Deze verwijst naar een 2e niveau met een verfijndere indeling, etc. tot, op het laagste niveau, de gegevens zelf worden gelezen. Bij een grote tabel kan dit snel in totaal 4 niveaus zijn en er zijn dus ook 4 fysieke IO’s nodig zijn om een record op te halen. Bij een index met grote kolommen (een naam van 40 posities of een index met een groot aantal kolommen) kunnen er minder rijen in de index pagina’s en is er dus eerder een extra niveau nodig. In dit geval kan het voordelig zijn om een gegenereerd nummer als primaire sleutel te introduceren en alle verwijzingen (foreign keys in andere tabellen) naar dit nummer te doen in plaats van naar de lange logische sleutel.
• Er zijn ook indexen die de pagina (page) waar de gegevens staan, berekenen door uit de sleutelgegevens een getal uit te rekenen. Dit getal (modulo het aantal pages van de tabel) geeft dan de page aan waar de gegevens gevonden kunnen worden. Dit is snel (er is voor de index geen ruimte en geen IO nodig) maar het nadeel is dat de sleutel volledig bekend moet zijn en de index kan (meestal) niet gebruikt worden om de gegevens meteen gesorteerd op te halen. Verder kan hierbij een pagina vol raken. De nieuwe gegevens moeten daarna naar een overflow gebied en dan is wel weer extra IO nodig. Bij deze indexen moet dan ook goed bijgehouden worden of er niet te veel gegevens in de overflow zitten. Deze wijze van indexering is niet bij alle producten beschikbaar.

Als de primaire sleutel een gegenereerd volgnummer is en alle records worden toegevoegd aan de tabel op deze volgorde (de tabel ligt gesorteerd op deze sleutel), dan zal telkens de meeste activiteit op de laatste pagina van de tabel plaats vinden (Hot spots). Is het een tabel waarbij veel gebruikers tegelijk nieuwe gegevens invoeren dan kan dit tot vertragingen door locking en soms zelfs tot deadlocks leiden.

Indien de gegevens die het systeem opvraagt allen al in een index zitten zal het DBMS vaak alleen de index pages lezen en niet meer het laagste niveau, de data pages zelf. Dit versnelt het opvragen. Het kan daarom soms voordelig zijn om een extra veel gebruikte kolom toe te voegen aan een index of een index speciaal voor dit doel te definiëren.

Bedenk dat indexen ook voor overhead zorgen bij insert, deletes en soms bij updates. Vermijd dus indexen die toch bijna nooit gebruikt worden. Indien een index alleen voor een enkel rapport of (nachtelijke) batchverwerking nodig is, dan is het ook mogelijk om aan het begin van deze verwerking de index op te bouwen en aan het einde weer te verwijderen. Als vuistregel wordt wel eens aangegeven om niet meer dan 3 tot 5 indexen te definiëren, maar dit is natuurlijk erg afhankelijk van het gebruik van de tabel.

Distributie van de database of van tabellen over meer schijven.

De performance van een (zwaar gebruikt) systeem met een zeer grote database kan verder worden verbeterd door delen van de database over verschillende fysieke schijven te verdelen. Bijvoorbeeld een schijf voor de gewone gegevens en indexen, een tweede schijf voor de logging, een derde schijf voor werktabellen of tijdelijke tabellen en nog een vierde schijf voor de backups (mits dit niet direct naar tape gaat). Aldus zullen veel acties tegelijk van 2 schijven gebruik maken:

• een insert of update verdeelt de IO over de data en de log schijf.
• een select met order by optie verdeeld de IO over de data schijf en de schijf met werktabellen ten behoeve van de sortering.
• de backup zal lezen op de data schijf en schrijven op de backup schijf, etc.

Een stap verder is om ook grote en/of veel gebruikte tabellen te verdelen over meer schijven, bijvoorbeeld een schijf voor de data en een tweede schijf voor de indexen. Een andere methode is het splitsen van de gegevens van een grote tabel, bijvoorbeeld alle bankrekening records worden over 10 schijven verdeeld, gebaseerd op de laatste positie van het bankrekening nummer. Omdat dit een checkdigit (voor de 11 proef) is mag van dit nummer een gelijkmatige verdeling verwacht worden.

Als van dit soort distributies gebruik gemaakt wordt is het nodig dat regelmatig gecontroleerd wordt of de verdeling nog wel goed is. Eventueel kan later de verdeling worden bijgesteld aan de hand van de gevonden cijfers.

Herindeling van de logische entiteiten in fysieke tabellen

Bij het fysiek ontwerp kan ook gekeken worden of gegevens in de tabellen anders verdeeld worden dan in het logisch model:

• Verticale distributie op basis van de kolommen: Als een tabel een grote tekst kolom bevat of als een deel van de kolommen vaak leeg is of (in verhouding) weinig gelezen wordt, dan kan dit deel naar een tweede tabel worden afgesplitst. Er ontstaat dan een 1 op 1 (of 1 op 0,1) relatie tussen deze twee tabellen. Nadeel: als gegevens van beide tabellen nodig zijn, dan is wel extra IO nodig [zie ook 3].
• Horizontale distributie op basis van de rijen: Als een deel van de gegevens niet zo vaak meer nodig is, dan kan dat deel worden verplaatst naar een andere tabel (of naar een archief op CD-ROM). Te denken valt aan afgehandelde gegevens van meer dan een maand of jaar oud die zelden meer geraadpleegd worden. Bedenk dat het prikken op een kleine tabel via een index nauwelijks sneller is dan op een grote tabel maar het doorlopen van de hele tabel (table scan) zal wel significant sneller zijn. Het nadeel is dat als zowel de oude als de nieuwe gegevens nodig zijn beide tabellen gelezen moeten worden. Zo’n union zal, in combinatie met een join op andere tabellen, tot significant complexere coding leiden.

Toevoegen van redundante gegevens

Vaak zijn gegevens nodig die berekend moet worden uit een veelheid van andere gegevens. Bijvoorbeeld het uitstaande saldo van een klant dat berekend kan worden door de gegevens van alle facturen, orders en betalingen te combineren [zie ook 2]. Als dit gegeven vaak nodig is kan het voordelig zijn om dit saldo redundant bij de klant op te slaan. Het systeem kan dan, bij het opvragen van de klantgegevens inclusief saldo, volstaan met het opvragen van 1 klantrecord in plaats van alle gegevens waarop het saldo gebaseerd is. Nadeel is dat er extra IO nodig is als een factuur, order of betaling in het systeem wordt ingevoerd of gewijzigd. Tevens moet deze coding zo geschreven worden dat de kans op fouten in het bijwerken van het saldo minimaal (= nul?!) is. Ik zelf heb hierbij voorkeur voor (insert, update en delete) triggers op tabellen waarop het betreffende saldo gebaseerd is.

Gebruik van de fysieke gegevenstypen

Overweeg goed bij het opstellen van het fysieke model in wat voor typen de gegevens worden opgeslagen. Een getal (aantal, volgnummer, etc.) kan in een integer, float of numeric/decimal type worden opgeslagen. Een integer zal een betere performance geven, zeker als dit attribuut ook in een index staat of als hierop gesorteerd wordt.

Voor langere character gegevens (bijvoorbeeld een persoonsnaam) die maximaal 24 of 36 posities gedefinieerd is kan beter een varchar type dan een char type gebruikt worden. Omdat een naam meestal niet langer is dan 10 posities bespaart dit extra ruimte en er kunnen dus meer records op een page waardoor de performance verbetert. Een varchar heeft in het DBMS echter enige overhead ten opzichte van een char, zeker als bij een update de lengte wijzigt. Gebruik de varchar daarom niet als de kolom meestal volledig of bijna volledig gevuld is, bijvoorbeeld bij een postcode.

Gebruik de NULL / NOT NULL optie correct. NULL (toegestaan) heeft enige overhead ten opzichte van NOT NULL Gebruik dus NULL alleen als de kolom echt optioneel gevuld kan worden.

Bedenk bij dit alles eerst wat conceptueel correct is en documenteer dit in het logisch model. Documenteer goed de overwegingen als er bij het opstellen van het fysieke model wordt afgeweken van het logische model, zodat dit later geen vragen gaat oproepen en mogelijk zelfs wordt teruggedraaid.

Gedistribueerde systemen

Als in een organisatie meer systemen gebruikt worden die onderling gegevens uitwisselen, dan zal een systeem A vaak gegevens nodig hebben van systeem B, bijvoorbeeld het factureringssysteem heeft gegevens nodig van het klant-beheer systeem. Deze gegevens kunnen bij de huidige stand van techniek on-line worden opgevraagd, zelfs al staan deze gegevens op een andere computer, mogelijk op een RDBMS van een andere leverancier (of zelfs in een ERP pakket) [zie ook 1]. Zo’n opvraging duurt echter altijd langer dan wanneer deze gegevens lokaal staan. Mocht de tijd die voor het ophalen onaanvaardbaar lang zijn dan kan men een kopie (replicatie) van de gegevens van het andere systeem in het eigen systeem plaatsen. Ook als het risico te groot is dat het andere systeem of het tussenliggende netwerk down is en daardoor de gegevens niet opgehaald kunnen worden kan men een kopie van deze gegevens in het eigen systeem plaatsen. Nadeel hiervan is natuurlijk weer dat deze redundante gegevens bijgewerkt moeten worden als de gegevens in het andere systeem worden gewijzigd. De meest betrouwbare manier om deze gegevens bij te werken is gebruik te maken van de replicatie functionaliteit van het gebruikte DBMS.

In het het tweede deel ga ik verder in op de mogelijkheden binnen de applicatie.