IBAnalyst: uw database begrijpen

Dmitri Kuzmenko, [email protected], laatste update 31-maart-2014

Ik werk sinds 1994 met InterBase. Destijds waren de meeste databases klein en hadden ze geen afstemming nodig. Natuurlijk waren er momenten waarop ik ibconfig op een server moest wijzigen en hardware of het besturingssysteem opnieuw moest configureren, maar dat was vrijwel alles wat ik kon doen om de prestaties af te stemmen.

Vier jaar geleden begon ons bedrijf met het leveren van technische ondersteuning en training aan InterBase-gebruikers. Het werken met veel productiedatabases leerde me ook veel verschillende dingen. Het meeste van wat ik leerde had echter betrekking op applicaties - het gebruik van transactieparameters, het optimaliseren van queries en resultaatsets.

Natuurlijk wist ik al geruime tijd van gstat - het hulpmiddel dat database-statistiekinformatie geeft. Als je ooit naar gstat-output hebt gekeken of erover hebt gelezen in opguide.pdf, dan weet je dat de statistische output eruitziet als een stel getallen en niets anders. Oké, je kunt fragmentatie-informatie voor een bepaalde tabel of index ontdekken, maar welke andere nuttige informatie kan worden verkregen?

Gelukkig was ik voordat ik met InterBase ging werken geïnteresseerd in verschillende datastructuren, hoe ze worden opgeslagen en welke algoritmen ze gebruiken. Dit hielp me om de output van gstat te interpreteren. Destijds besloot ik een tool te schrijven die gstat-output kon analyseren om te helpen bij het afstemmen van de database of op zijn minst om de oorzaak van prestatieproblemen te identificeren.

Lang verhaal, maar het resultaat was dat IBAnalyst werd gecreëerd. Ondanks mijn ervaring stelt het me nog steeds in staat om zeer interessante dingen of prestatieproblemen in verschillende databases te vinden.

Echte systemen hebben runtime-prestaties die fluctueren als een golf. De amplitude van dergelijke ‘golven’ kan laag of hoog zijn, dus je kunt zien hoe prestaties van dag tot dag (of uur tot uur) verschillen. De werkelijke prestaties hangen af van vele factoren, waaronder het ontwerp van de applicatie, serverconfiguratie, transactieconcurrentie, versievuil in de database enzovoort. Om te ontdekken wat er in een database gebeurt (zowel positieve als negatieve aspecten van prestaties), moet je op zijn minst af en toe een blik werpen op de database-statistieken.

Laten we eens kijken naar de mogelijkheden van IBAnalyst. IBAnalyst kan statistieken van gstat of de Services API nemen en deze compileren tot een rapport dat je volledige informatie geeft over de database, de tabellen en indices. Het heeft waarschuwingen ter plaatse die beschikbaar zijn tijdens het bekijken van statistieken; het bevat ook hintcommentaren en aanbevelingsrapporten.

Database-informatie

Figuur 1 Samenvatting van database-statistieken

De samenvatting in Figuur 1 geeft algemene informatie over je database. De getoonde waarschuwingen of opmerkingen zijn gebaseerd op zorgvuldig verzamelde kennis die is verkregen uit een groot aantal real-world productiedatabases.

Noot: Alle figuren in dit artikel bevatten gstat-statistieken die zijn genomen uit een real-world productiedatabase (met toestemming van de eigenaren).

Zoals ik al eerder zei, zien ruwe database-statistieken er cryptisch uit en zijn ze moeilijk te interpreteren. IBAnalyst markeert eventuele potentiële problemen duidelijk in geel of rood en het detail van het probleem kan eenvoudig worden gelezen door de cursor over het relevante item te plaatsen en de weergegeven hint te lezen.

Vervolgens kunnen we zien dat de parameter Forced Write is ingesteld op OFF en rood is gemarkeerd. InterBase 4.x en 5.x hadden deze parameter standaard op ON. Forced Writes zelf is een write-cache-methode: wanneer ON, schrijft het gewijzigde gegevens onmiddellijk naar schijf, maar OFF betekent dat schrijfbewerkingen voor onbekende tijd worden opgeslagen door het besturingssysteem in zijn bestandscache. InterBase 6 maakt databases met Forced Writes OFF.

Waarom is dit rood gemarkeerd in het IBAnalyst-rapport? Het antwoord is eenvoudig - het gebruik van asynchrone schrijfbewerkingen kan databasecorruptie veroorzaken in geval van stroom-, besturingssysteem- of serverstoringen.

Tip: Het is interessant dat moderne HDD-interfaces (ATA, SATA, SCSI) geen groot verschil in prestaties laten zien met Forced Write ingesteld op On of Off(1).

Volgende op het rapport is het mysterieuze “sweep-interval”. Als het positief is, stelt het de grootte in van de kloof tussen de oudste (2) en oudste snapshot-transactie, waarbij de engine wordt gealarmeerd over de noodzaak om automatische garbagecollection te starten. Op sommige systemen kan het bereiken van deze drempel een “plotseling prestatieverlies”-effect veroorzaken, en als gevolg daarvan wordt soms aanbevolen om het sweep-interval op 0 te zetten (automatisch vegen volledig uitschakelen). Hier is het sweep-interval geel gemarkeerd, omdat de waarde van de sweep-kloof negatief is, wat het kan zijn in InterBase 6.0, Firebird en Yaffil-statistieken, maar niet in InterBase 7.x. Wanneer de waarde van de sweep-kloof groter is dan het sweep-interval (als het sweep-interval niet 0 is), wordt het rapportitem voor het sweep-interval rood gemarkeerd met een passende hint.

We zullen de volgende 8 rijen als een groep bekijken, omdat ze allemaal aspecten van de transactiestatus van de database weergeven:

De oudste transactie is de oudste niet-gecommitte transactie. Lagere transactienummers zijn voor gecommitte transacties, en er zijn geen recordversies beschikbaar voor dergelijke transacties. Transactienummers hoger dan de oudste transactie zijn voor transacties die in elke staat kunnen zijn. Dit wordt ook wel de “oudste interessante transactie” genoemd, omdat het bevriest wanneer een transactie wordt beëindigd met rollback, en de server de wijzigingen op dat moment niet ongedaan kan maken.
De oudste snapshot - de oudste actieve (d.w.z. nog niet gecommitte) transactie die bestond aan het begin van de transactie die momenteel de oudste “interessante” transactie is. Geeft het laagste snapshot-transactienummer aan dat geïnteresseerd is in recordversies.
De oudste actieve - de oudste momenteel actieve transactie (3).
De volgende transactie - het transactienummer dat aan een nieuwe transactie zal worden toegewezen.
Actieve transacties - IBAnalyst geeft een waarschuwing als het oudste actieve transactienummer 30% lager is dan het dagelijkse transactieaantal. De statistieken vertellen niet of er andere actieve transacties zijn tussen de oudste actieve en de volgende transactie, maar dergelijke transacties kunnen er zijn. Meestal zijn er twee mogelijke oorzaken als de oudste actieve vastloopt: a) dat een transactie lange tijd actief is of b) dat het applicatieontwerp transacties toestaat om lange tijd te draaien. Beide oorzaken voorkomen garbagecollection en verbruiken serverbronnen.
Transacties per dag - dit wordt berekend uit de volgende transactie, gedeeld door het aantal dagen dat is verstreken sinds de creatie van de database tot het punt waarop de statistieken worden opgehaald. Dit kan alleen correct zijn voor productiedatabases, of voor databases die periodiek vanuit een back-up worden hersteld, waardoor de transactienummering wordt gereset.

Zoals je al hebt geleerd, als er waarschuwingen zijn, worden ze weergegeven als gekleurde regels, met duidelijke, beschrijvende hints over hoe het probleem op te lossen of te voorkomen.

Het moet worden opgemerkt dat database-statistieken niet altijd nuttig zijn. Statistieken die worden verzameld tijdens werk- en onderhoudsbewerkingen kunnen betekenisloos zijn.

Verzamel geen statistieken als je:

Je database net hebt hersteld
Een back-up hebt uitgevoerd (gbak -b db.gdb) zonder de -g-schakelaar
Onlangs een handmatige sweep hebt uitgevoerd (gfix -sweep)

Statistieken die je bij dergelijke gelegenheden krijgt, zullen praktisch nutteloos zijn. Het is ook correct dat er tijdens normaal werk momenten kunnen zijn waarop de database in perfecte staat is, bijvoorbeeld wanneer applicaties minder databasebelasting veroorzaken dan normaal (gebruikers zijn aan de lunch of het is een rustige tijd in de werkdag).

Hoe kun je zien wanneer er iets mis is met de database?

Je applicaties kunnen zo goed zijn ontworpen dat ze altijd correct met transacties en gegevens werken, geen sweep-kloof creëren, niet veel actieve transacties accumuleren, geen langlopende snapshots behouden, enzovoort. Meestal gebeurt dat niet (sorry, collega’s).

De meest voorkomende reden is dat ontwikkelaars hun applicaties testen met slechts twee of drie gelijktijdige gebruikers. Wanneer de applicatie vervolgens in een productieomgeving wordt gebruikt met vijftien of meer gelijktijdige gebruikers, kan de database zich onvoorspelbaar gedragen. Natuurlijk kan multi-user-modus goed werken omdat de meeste multi-user-conflicten kunnen worden getest met twee of drie gelijktijdig draaiende applicaties. Met grotere aantallen gebruikers kunnen echter garbagecollection-problemen ontstaan. Dergelijke potentiële problemen kunnen worden opgemerkt als je database-statistieken op de juiste momenten verzamelt.

Tabelinformatie

Laten we eens kijken naar een ander voorbeeld van IBAnalyst-output.

![](/images/article_IBAnalyst (1).jpg)

Figuur 2 Tabelstatistieken

De tabelstatistiekenweergave van IBAnalyst is ook zeer nuttig. Het kan laten zien welke tabellen veel recordversies hebben, waar een groot aantal updates/deletes zijn gemaakt, gefragmenteerde tabellen, met fragmentatie veroorzaakt door update/delete of door blobs, enzovoort. Je kunt zien welke tabellen vaak worden bijgewerkt en wat de tabelgrootte in megabytes is. De meeste van deze waarschuwingen zijn aanpasbaar.

In dit databasevoorbeeld zijn er verschillende problemen. Ten eerste waarschuwt de gele kleur in de VerLen-kolom dat de ruimte die door recordversies wordt ingenomen groter is dan die van de records zelf. Dit kan het gevolg zijn van het bijwerken van veel velden in een record of van bulk-deletes. Zie de rijen waarin de MaxVers-kolom blauw is gemarkeerd. Dit toont aan dat er slechts één versie per record wordt opgeslagen en dat het probleem dus te wijten is aan bulk-deletes. De waarde in de Versions-kolom toont hoeveel records zijn verwijderd.

Langlevende actieve transacties die garbagecollection voorkomen, zijn de belangrijkste reden voor prestatievermindering. Voor sommige tabellen kunnen er veel versies zijn die nog “in gebruik” zijn. De server kan niet beslissen of ze echt in gebruik zijn, omdat actieve transacties potentieel een of al deze versies nodig hebben. Dienovereenkomstig beschouwt de server deze versies niet als vuil, en het duurt steeds langer om een correct record uit veel versies te construeren wanneer een transactie het toevallig leest. In Figuur 2 kun je twee tabellen zien die een versieaantal hebben dat drie keer hoger is dan het recordaantal. Met deze informatie kun je ook controleren of het feit dat je applicaties deze tabellen zo vaak bijwerken, door ontwerp is of vanwege een fout.

De indexweergave

Indices worden door de database-engine gebruikt om primary key-, foreign key- en unique-beperkingen af te dwingen. Ze versnellen ook het ophalen van gegevens. Unieke indices zijn het beste voor het ophalen van gegevens, maar het voordeel van niet-unieke indices hangt af van de diversiteit van de geïndexeerde gegevens.

Kijk bijvoorbeeld naar ADDR_ADDRESS_IDX6. Ten eerste suggereert de indexnaam zelf dat deze handmatig is gemaakt. Als statistieken zijn genomen via de Services API met metadata-informatie, kun je zien welke kolommen zijn geïndexeerd (in IBAnalyst 1.83 en hoger). Voor de onderzochte index kun je zien dat deze 34999 keys heeft, TotalDup is 34995 en MaxDup is 25056. Beide duplicaatkolommen zijn rood gemarkeerd. Dit komt omdat er slechts 4 unieke key-waarden zijn onder alle keys in deze index, zoals te zien is in de Uniques-kolom. Bovendien is de grootste duplicaatketen (key die naar records met dezelfde kolomwaarde wijst) 25056 - d.w.z. bijna alle keys slaan een van vier unieke waarden op. Als gevolg hiervan zou deze index kunnen:

Verminder de snelheid van het herstelproces. Oké, vijfendertigduizend sleutels is geen groot probleem voor moderne databases en hardware, maar de impact moet toch worden opgemerkt.
Vertraag garbage collection. Indexen met een laag aantal unieke waarden kunnen garbage collection tot tien keer vertragen in vergelijking met een volledig unieke index. Dit probleem is opgelost in InterBase 7.1/7.5 en Firebird 2.0.
Veroorzaak onnodige paginalezingen wanneer de optimizer de index leest. Dit hangt af van de waarde die in een specifieke query wordt gezocht - zoeken via een index met een grotere MaxDup-waarde zal langzamer zijn. Zoeken op een waarde in een kolom met minder dubbele waarden zal sneller zijn, maar alleen jij weet dat de kolom geïndexeerd is.

Daarom vestigt IBAnalyst je aandacht op dergelijke indexen, markeert ze rood en geel en neemt ze op in het aanbevelingenrapport. Helaas worden de meeste “slechte” indexen automatisch aangemaakt om foreign key-beperkingen af te dwingen. In sommige gevallen kan dit probleem worden opgelost door, met behulp van triggers, verwijderingen of updates van de primaire sleutel in opzoektabellen te voorkomen. Maar als het niet mogelijk is om dergelijke wijzigingen door te voeren, toont IBAnalyst je elke keer dat je statistieken bekijkt “slechte” indexen op foreign keys.

Rapporten

Er is geen noodzaak om elke keer het hele rapport door te nemen, celkleuren te spotten en hints voor nieuwe waarschuwingen te lezen. Directere en gedetailleerdere informatie kan worden verkregen door de aanbevelingenfunctie van IBAnalyst te gebruiken. Laad gewoon de statistieken en ga naar het menu Rapporten/Aanbevelingen bekijken. Dit rapport biedt een stapsgewijze analyse, inclusief meer gedetailleerde beschrijvende waarschuwingen over geforceerde schrijfbewerkingen, sweep-interval, database-activiteit, transactiestatus, database-paginagrootte, sweeping, transactie-inventarispagina’s, gefragmenteerde tabellen, tabellen met veel recordversies, massale verwijderingen/updates, diepe indexen, optimizer-onvriendelijke indexen, nutteloze indexen en zelfs lege tabellen. Al deze informatie en de bijbehorende suggesties worden dynamisch gegenereerd op basis van de geladen statistieken.

Als voorbeeld van de rapportuitvoer bekijken we een rapport dat is gegenereerd voor de databasestatistieken die je eerder in dit artikel zag:

“De totale omvang van transactie-inventarispagina’s (TIP) is groot - 94 kilobytes of 23 pagina’s. Read_committed-transacties gebruiken de globale TIP, maar snapshot-transacties maken eigen kopieën van de TIP in het geheugen. Een grote TIP-omvang kan de prestaties vertragen. Probeer handmatig een sweep uit te voeren (gfix -sweep) om de TIP-omvang te verkleinen.”

Hier is nog een citaat uit het tabel/indexen-gedeelte van het rapport:

“Aantal versietabellen: 8. Een grote hoeveelheid recordversies vertraagt meestal de prestaties. Als er veel recordversies in een tabel zijn, werkt garbage collection niet, of worden records niet gelezen door een select-instructie. Je kunt proberen select count(*) op die tabellen uit te voeren om garbage collection af te dwingen, maar dit kan lang duren (als er veel versies en niet-unieke indexen zijn) en kan mislukken als er minstens één transactie is die in deze versies geïnteresseerd is.

Hier is de lijst met tabellen met een versie/record-verhouding groter dan 3:


Tabel	Records	Versies	Rec/Vers-omvang
CLIENTS_PR	3388	10944	92%
DICT_PRICE	30	1992	45%
DOCS	9	2225	64%
N_PART	13835	72594	83%
REGISTR_NC	241	4085	56%
SKL_NC	1640	7736	170%
STAT_QUICK	17649	85062	110%
UO_LOCK	283	8490	144%

Samenvatting

IBAnalyst is een onmisbaar hulpmiddel dat een gebruiker helpt bij het uitvoeren van gedetailleerde analyses van Firebird- of InterBase-databasestatistieken en het identificeren van mogelijke problemen met een database op het gebied van prestaties, onderhoud en hoe een applicatie met de database interageert. Het neemt cryptische databasestatistieken en toont ze op een gemakkelijk te begrijpen, grafische manier en doet automatisch verstandige suggesties over het verbeteren van databaseprestaties en het vergemakkelijken van databaseonderhoud.

1 InterBase 7.5 en Firebird 1.5 hebben speciale functies die periodiek niet-opgeslagen pagina’s kunnen doorspoelen als Forced Writes is uitgeschakeld.

2 De oudste transactie is dezelfde oudste interessante transactie, die overal wordt genoemd. Gstat-uitvoer toont deze transactie niet als “interessant”.

3 Ann Harrison zegt dat de oudste actieve transactie de oudste transactie is die actief was toen de huidige oudste actieve transactie begon. Voor applicaties is dit hier geen groot verschil.