IBAnalyst: Tipps und Tricks

This text was originally written in 2012, it is valid for version 1.0 - 2.5, in versions 3.0-5.0 there were many changes, wgcih could not be reflected. Please read documentation or contact us for support: [email protected].

Einige Fragen, die in den IBAnalyst-Empfehlungen und/oder der Hilfe nicht beantwortet werden:

1. Wie kann ich Indizes für PRIMARY-, FOREIGN- oder UNIQUE-Constraints neu aufbauen?

A: Für Firebird-Versionen 1.0-2.5. Ja, Sie können ALTER INDEX xxx INACTIVE/ACTIVE nicht für Constraint-Indizes verwenden. Wenn Sie einen tiefen oder fragmentierten Index auf diesem Constraint sehen, können Sie einen speziellen Trick verwenden (der von gbak beim Wiederherstellen verwendet wird):

RDB$INDICES hat das RDB$INDEX_INACTIVE-Flag, das null oder 0 ist, wenn der Index aktiv ist (nach CREATE INDEX oder ALTER INDEX ACTIVE). 1 bedeutet, dass der Index inaktiv ist (nach ALTER INDEX INACTIVE). Es gibt aber auch den Wert 3, der verwendet wird, um inaktive Indizes auf Constraints zu kennzeichnen. Sie können also RDB$INDEX_INACTIVE=3 für diesen Index setzen, COMMIT ausführen und dann den Wert wieder auf 0 setzen und erneut committen - der Index wird neu aufgebaut.

Für Firebird 3.0-5.0 - führen Sie einfach ALTER INDEX indexname ACTIVE aus.

2. Ich habe alle IBAnalyst-Empfehlungen verwendet, aber das hilft nicht, Abfragen zu beschleunigen.

A: Dies ist ein separates Problem, bei dem IBAnalyst nicht helfen kann. Hier können 2 Ursachen für das Problem vorliegen:

1. Indizes haben veraltete Statistiken. Sie können die Indexstatistik mit dem Befehl SET STATISTICS INDEX xxx aktualisieren (weitere Details unter http://www.ibase.ru/proc_selectivity/).

2. Es gibt einfach keinen geeigneten Index für eine Bedingung, die in der Abfrage verwendet wird.

3. Abfragen sind sehr komplex, oder der Optimierer kann die Abfrage nicht optimieren, sodass es notwendig ist, die Abfrage umzustrukturieren.

4. In einigen Fällen sehen Sie “fragmentierte Tabellen” direkt nach der Wiederherstellung.

Normalerweise reserviert Firebird und InterBase (ohne Parameter -use_all_space) etwa 25% Speicherplatz auf Datenseiten für zukünftige Einfügungen, Aktualisierungen oder Löschungen (um Datensatzversionen zu platzieren). Aber bei jeder Datenbank-Seitengröße (1, 2, 4 oder 8 k) werden Sie etwa 50% Fragmentierung für Tabellen sehen, die eine kleine Datensatzgröße haben (etwa ~12-20 Bytes, zum Beispiel hat eine Tabelle mit 2 Integer-Feldern eine durchschnittliche Datensatzgröße von 12 Bytes).

Das ist in Ordnung, betrachten Sie dies als eine magische Servernummer (oder Verhalten).

Wenn Sie also solche Tabellen mit kleinen Datensätzen haben, können Sie:

a) die “fragmentierte” Warnung für diese Tabellen ignorieren

b) den “Fragmentierungs-%"-Wert zum Beispiel auf 45% im IBAnalyst-Optionsdialog senken.

4. Datensatzversionen für eine Tabelle, die nicht aktualisiert werden darf

Wenn Sie Datensatzversionen auf einer Tabelle sehen, die nicht aktualisiert werden darf (zum Beispiel eine Tabelle mit einem Ereignisprotokoll) - keine Sorge, diese Versionen werden durch Löschungen erzeugt.

So wissen Sie, wie viele aktuelle Datensätze in der Tabelle sind und wie viele Datensätze gelöscht wurden.

Dies gilt nur, wenn MaxVer = 1 ist. Wenn es > 1 ist, wird diese Tabelle von einer Anwendung aktualisiert. Wenn Sie sicher sind, dass diese Tabelle niemals aktualisiert werden darf, ist es besser, einen “before update”-Trigger mit einer Exception zu setzen, um herauszufinden, welche Anwendung Aktualisierungen vornimmt.

5. Blobs können Tabellenfragmentierung verursachen.

Die Engine speichert Blobs auf 3 verschiedene Arten:

1. Wenn der Blob-Inhalt auf die Datenseite passt (genügend freier Speicherplatz), wird er auf dieser Datenseite in der Nähe seines Datensatzes (oder seiner Version) gespeichert.

2. Wenn der Blob-Inhalt nicht auf die Datenseite passt, wird er auf einer separaten Seite gespeichert.

3. Wenn im Fall 2 der Blob nicht auf eine Datenseite passt, wird eine Zeigerseite erstellt, die auf die entsprechenden Blob-Seiten verweist.

Fall 1 tritt abhängig von der gespeicherten Blob-Größe und der Datenbank-Seitengröße auf. Wenn Sie zum Beispiel eine Seitengröße von 4K und Blobs mit einer durchschnittlichen Größe von ~5K haben, werden diese nicht auf Datenseiten, sondern auf zusätzlichen Blob-Seiten gespeichert.

Wenn Sie jedoch Ihre Datenbank sichern und mit einer Seitengröße von 8K wiederherstellen, passen die Blobs auf die Datenseite und werden mit den Datensätzen gespeichert, was eine hohe Datensatzfragmentierung verursacht.

IBAnalyst markiert diese Tabellen als Pale (Spalte Records), und der Hinweis zeigt die geschätzte Anzahl der Datensätze für diese Tabelle (basierend auf der Anzahl der Datenseiten) und den tatsächlichen durchschnittlichen Füllwert (%).

Wenn Ihre Abfrage außer Blobs auch andere Felder aus dieser Tabelle liest, wird ein natürlicher Scan, Join oder eine Aggregation sehr langsam ausgeführt.

Die einzige Lösung, dies zu vermeiden: Erstellen Sie eine zusätzliche Tabelle (1-1-verknüpft mit der Originaltabelle) und verschieben Sie alle Blob-Spalten, deren durchschnittliche Größe kleiner als die Seitengröße ist, dorthin.

Versuchen Sie in diesem Fall nicht, mit einer größeren Seitengröße zu sichern/wiederherzustellen! Dies führt dazu, dass Blobs, die bei der aktuellen Seitengröße nicht auf Datenseiten passen, bei der Wiederherstellung mit größerer Seitengröße auf Datenseiten platziert werden. Ihre Tabellen mit Blobs werden also stärker fragmentiert als zuvor.

Es wird auch nicht empfohlen, mit einer kleineren Seitengröße wiederherzustellen, da dies die Leistung für Indizes und Nicht-Blob-Tabellen verringern kann.

Sie sollten auch nicht versuchen, Blob-Felder in VARCHAR-Felder zu ändern - VARCHAR-Felder werden immer als Teil eines Datensatzes gespeichert, sodass ein Datensatz 2 oder mehr Fragmente haben kann (auf 2 oder mehr Datenseiten platziert wird), wenn er nicht auf eine Datenseite passt.

p.s. IBAnalyst kann diese Tabellen “versehentlich” melden, zum Beispiel hatte eine Tabelle Blob-Felder mit Daten, die jedoch aus der Tabellenstruktur entfernt wurden. Leider gibt es keine konfigurierbare Option für diese Warnung, da wir sie genau aus den vom Server gemeldeten Daten (Statistiken) berechnen.

6. VerLen- und RecLength-Beziehung

a) VerLen >= 90% von RecLength: Die Versionen, die Sie in der Spalte Version sehen, sind hauptsächlich Datensatzlöschungen. Je mehr Datensätze gelöscht werden, desto kleiner wird RecLength (bis zu 0 Bytes). VerLen kann auch größer als RecLen sein, wenn Sie Ihre Tabelle mit größeren String-Daten aktualisieren, als in den ursprünglichen Datensätzen gespeichert waren.

b) VerLen <= 80% von RecLength: Die Versionen sind hauptsächlich Datensatzaktualisierungen.

Wir können diese Fälle nicht genauer unterscheiden, da die Statistiken die durchschnittliche Datensatz- und Versionsgröße für die gesamte Tabelle zeigen, während die sichtbare Anzahl der Versionen für gleichzeitige Transaktionen variieren kann.

7. Warum bezeichnet IBAnalyst einige Indizes als “schlecht”?

Indizes mit einem Selektivitätswert unter 0,01 werden in IBAnalyst als “schlecht” markiert (siehe Hilfe zur Index-Ansicht). Es gibt mehrere Ursachen, einen bestimmten Index als schlecht zu bezeichnen:

1. Die Selektivität dieses Index liegt unter 0,01. Theoretisch sollte der Optimierer diesen Index nicht verwenden, aber er tut es, wenn keine anderen Indizes existieren (für where, order by oder join-Klausel, zumindest).

2. Ein solcher Index verursacht eine sehr langsame Garbage Collection. Dieses Problem existiert nicht in InterBase 7.1/7.5 und wird in Firebird 2.0 behoben.

3. Dieser Index macht den Wiederherstellungsprozess sehr langsam, und er wird sehr langsam erstellt (create/alter index active). Dies liegt daran, dass die Datensatznummernkette für einen Indexschlüssel groß ist.

4. Wenn dieser Index in einer where-Klausel verwendet wird, hängt der Speicherverbrauch vom gesuchten Wert ab (Bitmaskengröße). Da die Datensatzkette groß sein kann (viele Schlüsselduplikate), ist auch der Speicherverbrauch groß.

5. Wenn dieser Index in “order by” verwendet wird und viele Duplikate hauptsächlich bei niedrigeren Schlüsselwerten vorhanden sind (abhängig von der Index-Sortierreihenfolge), gibt es viele Indexseiten-Lesevorgänge, die die Abfrage verlangsamen.

Das liegt daran, dass IBAnalyst die Existenz solcher Indizes nicht ignorieren kann.

Der schlimmste Fall für einen Index ist, wenn er die Spalte Uniques = 1 hat, d.h. alle Werte für die indizierte Spalte sind gleich. Diese Indizes werden auf der Zusammenfassungsseite unter “Useless indices” aufgelistet.

Natürlich kann ein solcher Index für Ihre Anwendung “gut” sein. Zum Beispiel, wenn Datensätze ein “Archiv”-Flag in einer Spalte haben und Ihre Anwendung nur nach aktuellen, nicht archivierten Daten über den Index auf dieser Spalte sucht. Es liegt also an Ihnen, ob wir richtig liegen, diesen Index als “schlecht” zu bezeichnen oder nicht.

8. Was ist, wenn ein “schlechter” Index durch einen Foreign-Key-Constraint erstellt wurde?

Nun, der vorherige Absatz zeigt, dass es besser ist, “schlechte” Indizes zu entfernen (wenn Sie sie nicht verwenden, um Schlüssel mit weniger Duplikaten als andere Schlüssel zu suchen). Wenn ein solcher Index jedoch durch einen Fremdschlüssel erstellt wurde, können Sie ihn nur durch Entfernen des Fremdschlüssels löschen. Das Entfernen des Fremdschlüssels deaktiviert die Beziehungsprüfungs-Constraint, was möglicherweise nicht akzeptabel ist.

Sie können den FK durch Trigger ersetzen, jedoch mit einigen Einschränkungen. FK kontrolliert Datensatzbeziehungen mithilfe eines Index, und der Index “sieht” alle Schlüssel für alle Datensätze unabhängig vom Transaktionsstatus. Trigger arbeiten jedoch nur im Kontext der Client-Transaktion. Wenn Sie also FK durch Trigger ersetzen, müssen Sie sicherstellen, dass:

• Datensätze nicht aus der Master-Tabelle gelöscht werden oder im Modus “Snapshot-Tabelle reservierend” gelöscht werden
• Die Spalte, die vom PK in der Master-Tabelle verwendet wird, niemals geändert wird. Sie können dies durch einen before-update-Trigger einschränken.

Wenn Sie diese Bedingungen einhalten, können Sie einen bestimmten Fremdschlüssel entfernen. Erstellen Sie natürlich keinen Index manuell auf dieser Spalte.

9. Warum gibt es in der Zeile Datenversion-Prozent nur 12 Megabyte Daten, aber ich habe eine 140-Megabyte-Datenbank?

1. IBAnalyst zeigt hier das “reine” Datenvolumen, ohne Berücksichtigung anderer Datenbankstrukturen (Indizes, Metadaten…) und Seitenfragmentierung.

2. Nach der Wiederherstellung lassen InterBase und Firebird etwas freien Speicherplatz (15-25%) auf Datenseiten, um zukünftige Aktualisierungen/Löschungen schneller zu machen.

3. Es gibt ein spezifisches Serververhalten, bei dem Datenseiten um etwa 50% fragmentiert bleiben, wenn die Datensatzgröße der Tabelle gering ist, etwa 11-22 Bytes.

10. Wie kann die Optimiererleistung bei häufigen Aktualisierungen verbessert werden?

Die Indexstatistik wird in der Spalte RDB$INDICES.RDB$STATISTICS gespeichert und auf 3 Arten aktualisiert:

1. SET STATISTICS INDEX

2. ALTER INDEX ACTIVE oder CREATE INDEX …

3. Wiederherstellungsprozess (alle Indizes werden neu aufgebaut sowie “ALTER INDEX ACTIVE”)

Der Optimierer verwendet diese Statistikinformationen, um Abfragen vorzubereiten. Mithilfe der Statistikwerte kann der Optimierer entscheiden, ob ein Index “gut genug” oder “nicht nützlich” zum Abrufen von Datensätzen ist.

Wenn die Statistik über einen langen Zeitraum nicht aktualisiert wurde, kann der Optimierer einen schlechten Plan erzeugen, da die vorhandenen Statistikwerte nicht dem tatsächlichen Zustand entsprechen, weil sich die Tabellendaten erheblich geändert haben können (zum Beispiel wurde die Anzahl der Datensätze um das 5-10-fache erhöht oder umgekehrt wurden alle Datensätze gelöscht).

Sie können den schlechten automatischen Abfrageplan durch einen expliziten PLAN für eine bestimmte Abfrage ersetzen, aber dies ist kein guter Ansatz, da sich die Daten nach der Entwicklung des Plans erheblich ändern können.

Ein alternativer (und richtiger) Weg ist, die Statistik regelmäßig durch Anwenden der SET STATISTICS-Anweisung für alle Indizes zu aktualisieren. Sie können die Ausführung eines SQL-Skripts zur Aktualisierung der Statistik mit ISQL oder dem fertigen Tool gidx (nur Windows) planen.

Wenn Sie einige Tabellen haben, die regelmäßig mit unterschiedlichen Datensätzen neu geladen werden, hilft dieser Ansatz nicht. Betrachten wir das Beispiel:

• Tabelle A wird 4-5 Mal pro Tag mit Daten geladen.
• Nach der Verarbeitung der geladenen Daten werden alle Datensätze in Tabelle A gelöscht.

In diesem Fall können wir 2 korrekte Statistikwerte für Indizes auf Tabelle A sehen - wenn sie mit Daten geladen ist und wenn sie leer ist. Eine auf der geladenen Tabelle neu berechnete Statistik ist also nutzlos, wenn die Tabelle leer ist, und umgekehrt.

Um dies zu vermeiden, müssen Sie die Statistik für Indizes auf Tabelle A nur dann neu berechnen, wenn die Tabelle mit Daten gefüllt ist. Am besten ist es, bevor Abfragen auf dieser Tabelle ausgeführt werden.

Seit Version 1.91 zeigt IBAnalyst die Indexstatistik-Differenz an und ermöglicht es Ihnen, sie jederzeit neu zu berechnen. Zuerst müssen Sie sich die Datensatzinformationen der Tabelle ansehen - ist dies die übliche durchschnittliche Datensatzanzahl oder nicht. Wenn ja, können Sie die Indexselektivität sicher neu berechnen. Wenn nicht - ist es vielleicht besser, die Indexstatistik nicht anzufassen, da dies dazu führen kann, dass der Optimierer noch schlechtere Abfragepläne erstellt.