IBAnalyst: Tips en trucs

Dit artikel is oorspronkelijk geschreven in 2012, het is geldig voor versie 1.0 - 2.5, in versies 3.0-5.0 waren er veel wijzigingen, die niet konden worden weergegeven. Lees de documentatie of neem contact met ons op voor ondersteuning: [email protected].

Enkele vragen die niet worden beantwoord in IBAnalyst Recommendations en/of Help:

1. Hoe herbouw ik indexen op PRIMARY, FOREIGN of UNIQUE constraints?

A: Voor Firebird versies 1.0-2.5. Ja, je kunt ALTER INDEX xxx INACTIVE/ACTIVE niet gebruiken op constraint-indexen. Als je een diepe of gefragmenteerde index op deze constraint ziet, kun je een speciale truc gebruiken (gebruikt door gbak bij restore):

RDB$INDICES heeft een RDB$INDEX_INACTIVE vlag die null of 0 is als de index actief is (na CREATE INDEX of ALTER INDEX ACTIVE). 1 betekent dat de index inactief is (na ALTER INDEX INACTIVE). Maar er is ook de waarde 3 die wordt gebruikt om inactieve indexen op constraints aan te geven. Je kunt dus RDB$INDEX_INACTIVE=3 instellen voor die index, COMMIT, en daarna de waarde terugzetten naar 0 en opnieuw committen - de index zal worden herbouwd.

Voor Firebird 3.0-5.0 - doe gewoon ALTER INDEX indexnaam ACTIVE

2. Ik heb alle IBAnalyst aanbevelingen gebruikt, maar dit helpt niet om queries te versnellen.

A: Dit is een apart probleem waarbij IBAnalyst niet kan helpen. Er kunnen hier 2 oorzaken van het probleem zijn:

1. Indexen hebben verouderde statistieken. Je kunt de indexstatistieken verversen met het commando SET STATISTICS INDEX xxx (zie meer details http://www.ibase.ru/proc_selectivity/).

2. Er is simpelweg geen geschikte index voor een bepaalde voorwaarde die in de query wordt gebruikt.

3. Queries zijn zeer complex, of de optimizer kan de query niet optimaliseren, dus het is noodzakelijk om de query te herstructureren.

4. In sommige gevallen zie je “gefragmenteerde tabellen” direct na een restore.

Normaal gesproken reserveert Firebird en InterBase (zonder parameter -use_all_space) ongeveer 25% ruimte op datapagina’s voor toekomstige inserts, updates of deletes (om recordversies te plaatsen). Maar bij elke databasepagina-grootte (1, 2, 4 of 8 k) zie je ~50% fragmentatie voor tabellen met een kleine recordgrootte (ongeveer ~12-20 bytes, bijvoorbeeld een tabel met 2 integer-velden heeft een gemiddelde recordgrootte van 12 bytes).

Dit is normaal, beschouw dit als een magisch servernummer (of gedrag).

Dus als je zulke kleine recordtabellen hebt, kun je:

a) de “gefragmenteerde” waarschuwing voor die tabellen negeren

b) het “gefragmenteerde %” verlagen naar bijvoorbeeld 45% in het IBAnalyst Opties-dialoogvenster.

4. Recordversies voor een tabel die niet geüpdatet mag worden

Als je recordversies ziet op een tabel die niet geüpdatet mag worden (bijvoorbeeld een tabel met een gebeurtenissenlogboek) - maak je geen zorgen, deze versies worden gegenereerd door deletes.

Zo weet je hoeveel huidige records er in de tabel zijn en hoeveel records zijn verwijderd.

Dit is alleen waar als MaxVer = 1. Als het > 1 is, dan wordt deze tabel bijgewerkt door een applicatie. Als je er zeker van bent dat deze tabel nooit geüpdatet mag worden, is het beter om een “before update” trigger met een exceptie in te stellen om te ontdekken welke applicatie updates uitvoert.

5. Blobs kunnen tabelfragmentatie veroorzaken.

De engine slaat blobs op 3 verschillende manieren op:

1. Als de blobinhoud op de datapagina past (voldoende vrije ruimte), wordt deze op die datapagina nabij het record (of de versie) opgeslagen.

2. Als de blobinhoud niet op de datapagina past, wordt deze op een aparte pagina opgeslagen.

3. Als in geval 2 de blob niet op één datapagina past, wordt er een pointerpagina aangemaakt die naar de juiste blobpagina’s verwijst.

Geval 1 gebeurt afhankelijk van de opgeslagen blobgrootte en de databasepagina-grootte. Als je bijvoorbeeld een paginagrootte van 4K had en blobs met een gemiddelde grootte van ~5K, worden ze niet op datapagina’s opgeslagen, maar op extra blobpagina’s.

Maar als je een back-up van je database maakt en deze herstelt met een paginagrootte van 8K, passen de blobs op de datapagina en worden ze met de records opgeslagen, wat een hoge recordfragmentatie veroorzaakt.

IBAnalyst markeert deze tabellen als Pale (Records-kolom) en de hint toont de geschatte records voor die tabel (op basis van het aantal datapagina’s) en de werkelijke gemiddelde vulwaarde (%).

Als je query alle velden behalve blobs uit die tabel leest, zal een natuurlijke scan, join of aggregatie erg langzaam verlopen.

De enige oplossing om dit te voorkomen: maak een extra tabel aan (1-op-1 gekoppeld aan de oorspronkelijke tabel) en verplaats alle blob-kolommen met een gemiddelde grootte kleiner dan de paginagrootte naar deze tabel.

Probeer in dat geval niet te back-uppen/te herstellen met een grotere paginagrootte! Dit zorgt ervoor dat blobs die niet op datapagina’s passen met de huidige paginagrootte, tijdens de restore met een grotere paginagrootte op datapagina’s worden geplaatst. Je tabellen met blobs zullen dus meer gefragmenteerd zijn dan voorheen.

Het wordt ook afgeraden om te herstellen met een kleinere paginagrootte, omdat dit de prestaties voor indexen en niet-blob-tabellen kan verminderen.

Je moet ook niet proberen blob-velden te wijzigen naar varchar-velden - varchar-velden worden altijd als onderdeel van een record opgeslagen, dus een record kan 2 of meer fragmenten hebben (op 2 of meer datapagina’s worden geplaatst) als het niet op een datapagina past.

p.s. IBAnalyst kan deze tabellen “per ongeluk” melden, bijvoorbeeld als een tabel blob-velden met gegevens had, maar deze uit de tabelstructuur zijn verwijderd. Helaas is er geen configureerbare optie voor deze waarschuwing, omdat we deze exact berekenen op basis van gegevens die door de server worden gerapporteerd (statistieken).

6. VerLen en RecLength relatie

a) VerLen >= 90% van RecLength: de versies die je in de Versie-kolom ziet, zijn meestal record-deletes. Hoe meer records worden verwijderd, hoe kleiner RecLength wordt (tot 0 bytes). VerLen kan ook groter zijn dan RecLen als je je tabel bijwerkt met grotere stringgegevens dan in de oorspronkelijke records waren opgeslagen.

b) VerLen <= 80% van RecLength: versies zijn meestal record-updates.

We kunnen deze gevallen niet nauwkeuriger onderscheiden omdat de statistieken de gemiddelde record- en versiegrootte voor de hele tabel tonen, terwijl het zichtbare aantal versies voor gelijktijdige transacties kan variëren.

7. Waarom noemt IBAnalyst sommige indexen “slecht”?

Indexen met een selectiviteitswaarde lager dan 0,01 worden in IBAnalyst als “slecht” gemarkeerd (zie Index-view help). Er zijn verschillende oorzaken om een bepaalde index als slecht te bestempelen:

1. De selectiviteit van die index is lager dan 0,01. Theoretisch zou de optimizer die index niet moeten gebruiken, maar hij doet dat wel als er geen andere indexen bestaan (voor where, order by of join-clausule, tenminste).

2. Zo’n index veroorzaakt zeer trage garbage collection. Dit probleem bestaat niet in InterBase 7.1/7.5 en zal worden opgelost in Firebird 2.0.

3. Deze index maakt het restore-proces zeer traag en wordt zeer langzaam aangemaakt (create/alter index active). Dit komt doordat de recordnummerketen groot is voor één indexsleutel.

4. Als deze index in een where-clausule wordt gebruikt, hangt het geheugengebruik af van de waarde die wordt doorzocht (bitmaskergrootte). Aangezien de recordketen groot kan zijn (veel sleutelduplicaten), zal het geheugenverbruik ook groot zijn.

5. Als die index wordt gebruikt in “order by” en er veel duplicaten zijn, meestal in lagere sleutelwaarden (afhankelijk van de indexsorteervolgorde), zullen er veel indexpaginametingen zijn die de query vertragen.

Dat komt omdat IBAnalyst het bestaan van dergelijke indexen niet kan negeren.

Het slechtste geval voor een index is wanneer deze Uniques-kolom = 1 heeft, d.w.z. alle waarden voor de geïndexeerde kolom zijn hetzelfde. Deze indexen worden vermeld onder “Nutteloze indexen” op de Samenvattingspagina.

Natuurlijk kan zo’n index voor jouw applicatie “goed” zijn. Bijvoorbeeld als records een “archief”-vlag in een kolom hebben en jouw applicatie zoekt via een index op die kolom alleen voor huidige, niet gearchiveerde gegevens. Het is dus aan jou of wij gelijk hebben om die index “slecht” te noemen of niet.

8. Wat als een “slechte” index is aangemaakt door een Foreign Key constraint?

De vorige paragraaf toont aan dat het beter is om “slechte” indexen te verwijderen (als je ze niet gebruikt om sleutels te zoeken met minder duplicaten dan andere sleutels). Maar als zo’n index door een foreign key is aangemaakt, kun je deze alleen verwijderen door de foreign key te verwijderen. Het verwijderen van de foreign key schakelt de relatiecontrole-constraint uit, wat onacceptabel kan zijn.

Je kunt FK vervangen door triggers, maar met enkele beperkingen. FK controleert recordrelaties met behulp van een index, en de index “ziet” alle sleutels voor alle records onafhankelijk van de transactiestatus. Maar triggers werken alleen in de transactiecontext van de client. Dus als je FK vervangt door triggers, moet je ervoor zorgen dat:

• Records niet worden verwijderd uit de mastertabel, of worden verwijderd in de “snapshot table reserving” modus
• De kolom die door PK in de mastertabel wordt gebruikt, nooit wordt gewijzigd. Je kunt dit beperken met een before update trigger.

Als je aan deze voorwaarden voldoet, kun je een specifieke Foreign Key verwijderen. Maak natuurlijk geen index handmatig op die kolom aan.

9. Waarom staat er in de rij Data-versiepercentage slechts 12 megabytes aan gegevens, terwijl ik een database van 140 megabytes heb?

1. IBAnalyst toont hier het “pure” gegevensvolume, zonder telling van andere databasestructuren (indexen, metadata…) en paginafragmentatie.

2. Na een restore laten InterBase en Firebird wat vrije ruimte (15-25%) op datapagina’s achter om toekomstige updates/deletes sneller te maken.

3. Er is specifiek servergedrag waarbij datapagina’s voor ongeveer 50% gefragmenteerd blijven als de recordgrootte van die tabel laag is, ongeveer 11-22 bytes.

10. Hoe de optimizerprestaties verbeteren bij frequente updates

Indexstatistieken worden opgeslagen in de RDB$INDICES.RDB$STATISTICS-kolom en worden op 3 manieren bijgewerkt:

1. SET STATISTICS INDEX

2. ALTER INDEX ACTIVE, of CREATE INDEX …

3. restore-proces (alle indexen worden herbouwd evenals “ALTER INDEX ACTIVE”)

De optimizer gebruikt deze statistische informatie om queries voor te bereiden. Met behulp van statistiekwaarden kan de optimizer beslissen dat een index “goed genoeg” of “niet nuttig” is voor het ophalen van records.

Als statistieken gedurende lange tijd niet zijn bijgewerkt, kan de optimizer een slecht plan produceren omdat de bestaande statistiekwaarden niet overeenkomen met de werkelijke stand van zaken, omdat tabelgegevens aanzienlijk kunnen zijn gewijzigd (bijvoorbeeld het aantal records is 5-10 keer toegenomen, of omgekeerd, alle records zijn verwijderd).

Je kunt het slechte automatische queryplan vervangen door een expliciet PLAN voor een specifieke query, maar dit is geen goede aanpak, omdat gegevens aanzienlijk kunnen veranderen nadat het plan is ontwikkeld.

Een alternatieve (en juiste) manier is om statistieken periodiek te verversen door de SET STATISTICS-instructie toe te passen voor alle indexen. Je kunt een SQL-script plannen om statistieken te verversen met ISQL of het kant-en-klare hulpmiddel gidx (alleen Windows).

Als je tabellen hebt met periodiek opnieuw geladen verschillende records, zal deze aanpak niet helpen. Laten we een voorbeeld bekijken:

• Tabel A wordt 4-5 keer per dag met gegevens geladen.
• Na verwerking van de geladen gegevens worden alle records in tabel A verwijderd.

In dit geval kunnen we 2 correcte statistiekwaarden zien voor indexen op tabel A - wanneer deze met gegevens is geladen en wanneer deze leeg is. Statistieken die op een geladen tabel opnieuw worden berekend, zijn dus nutteloos wanneer de tabel leeg is, en vice versa.

Om dit te voorkomen, moet je statistieken voor indexen op tabel A alleen opnieuw berekenen wanneer de tabel met gegevens is gevuld. Het beste is vóórdat queries op die tabel worden uitgevoerd.

Sinds versie 1.91 toont IBAnalyst het verschil in indexstatistieken en kun je deze op elk moment opnieuw berekenen. Eerst moet je naar de recordinformatie van de tabel kijken - is dit het gebruikelijke gemiddelde aantal records of niet. Zo ja, kun je de indexselectiviteit zeker opnieuw berekenen. Zo niet - dan is het misschien beter om de indexstatistieken niet aan te raken, omdat dit ertoe kan leiden dat de optimizer nog slechtere queryplannen produceert.