Auswirkung der Indizes auf die Datenbankgröße

Alles, was den Programmierer beschäftigt

Moderator: SYNERPY

Antworten
miboe
Beiträge: 1295
Registriert: Fr Jul 28, 2006 9:13 am

Auswirkung der Indizes auf die Datenbankgröße

Beitrag von miboe » Mo Dez 05, 2011 7:57 pm

Hallo zusammen,

ich befasse mich ja gerade mit dem Thema Update von Datenbanken und im Rahmen dessen mache ich auch Versuche, mit dem Table Data Comparer von IBexpert Daten zwischen Datenbanken auszutauschen. Dabei bin ich auf einen Punkt gestoßen, den ich nicht sortiert kriege ...

Ich habe eine leere 2012er Datenbank und eine 2011er mit ca. 300-400.000 Datensätzen an Nutzdaten. Dabei sind ca. 60T Datensätze wie PLZ und ähnliches in der 2012er ja auch schon drin. Auf die 2011 lasse ich einen Strukturabgleich los und fülle die neuen NOT NULL Felder mit sinnvollen Inhalten, damit es keine Konsistenz-Fehler mehr gibt. Das zur Ausgangssituation. Beiden Datenbanken sind so eingestellt, daß kein Platz für Datensatzversionen reserviert wird, also gfix -use full ...

In diesem Zusand hat die 1012er Datenbank 34335 Seiten.

Nun mache ich die Tabelle BPLZ leer und mache ein Backup/Restore Zyklus (B/R-Zyklus). Danach sind wir bei dann bei 33261 Seiten

Zur Vorbereitung des Datenabgleichs mit TableDataCompare schalte ich nun die Trigger aus (per Befehl im IBexpert DB-Explorer) und deaktiviere per Update-Befehl auf RDB$... die Foreign Keys. Obwohl ich eigentlich nichts wirklich an den Daten inklusive Metadaten ändere sind wir dann plötzlich bei 33439 Seiten. Kleinkram ...

Nun importiere ich die Daten anhand des vom TableDataCompare erzeugten SQL-Script. Die Datenbank wächst dadurch auf 36644 Seiten an. Angesichts einer Seitengröße von 16384 Bytes sind das ca. 50Mbyte an Zuwachs, was ich für okay halte wenn ich zum Beispiel die 2011er mit einer leeren 2011 vergleiche.

Neuer B/R-Zyklus und die Datenbank landet bei 35947 Seiten. Nach dem Aufräumen sind also netto ca. 40Mb mehr an Volumen da, angesichts der Tatsache, daß viele Tabellen nur ganz wenige Datensätze haben und deswegen die dafür notwendigen Seiten quasi "verschwendet" sind, komme ich bis hierhin gedanklich noch mit.

Aber jetzt kommts: Ich schalte jetzt wieder die Foreign-Keys ein und der Testrechner nimmt sich erst mal ne Auszeit von einigen Minuten. Da kein wirklicher Dampfhammer noch erklärbar. ABER: die Datenbank hat hinterher 40414 Seiten und ist auf der Festplatte 630Mb groß, das ist ein Zuwachs von 70Mb und damit ja fast doppelt soviel wie die wirklichen Nutzdaten ...

Ein neuer B/R-Zyklus ändert daran auch nix mehr, ebenso wie das Einschalten der Trigger die Seitenzahl unverändert läßt ...

Kann mir das mal jemand erklären?

Übrigens: der Gegenversuch in der 2011er Datenbank aus der die Daten stammen verläuft so, wie man es sich vorstellen würde. Das Aus- und wieder Einschalten der Foreing-Keys (genauer: der Indizes dazu) ändert aber mal rein gar nichts an der Zahl der belegten Seiten.

HÜLFE, HÜLFE ... :shock:

Gruß
Michael
Nur wer das Unmögliche versucht, wird das Machbare erreichen!
--
Datenbank: 2012-A02
Programm: 4.2.5.65
OS: Win 7 Pro / Ubuntu 10.04.3

cpr
Beiträge: 137
Registriert: Mi Sep 01, 2010 9:31 pm

Beitrag von cpr » Do Dez 08, 2011 2:23 pm

Ich hab keine wirkliche Ahnung (das voraus).

Du schreibst nix von der garbage collection, hast Du bei den backup/restore- Zyklen in der neuen DB die gc "angehakt"?

Du könntest mal für beide Datenbanken gstat http://www.firebirdsql.org/manual/gstat ... eader.html aufrufen, evtl. gibt es dort Hinweise. Oder hast Du das bereits verwendet, um den pages count zu ermitteln?
fast doppelt soviel
riecht irgendwie nach "shadow" oder so...
--
Client: 4.2.5.42
Datenbank AVERP2011-A.02

cpr
Beiträge: 137
Registriert: Mi Sep 01, 2010 9:31 pm

Beitrag von cpr » Do Dez 08, 2011 9:03 pm

Ich hab hier ad hoc eine 2011.A03-DEMO und eine 2012.02 Datenbankdatei. Mit meinen noch beschränkten Firebird-Kenntnissen fiel mir auf:

Die 2012er Version ist ohne "no reserve" konfiguriert.
http://www.firebirdsql.org/manual/gfix-pagespace.html
http://www.ib-aid.com/articles/item113
Würde rein intuitiv zwar keine 50% Abweichungen erklären.
--
Client: 4.2.5.42
Datenbank AVERP2011-A.02

cpr
Beiträge: 137
Registriert: Mi Sep 01, 2010 9:31 pm

Beitrag von cpr » Do Dez 08, 2011 9:36 pm

Weiter im gstat-Output gewühlt, danke für den Hinweis auf die Postleitzahlen!

In der 2011a02 hab ich iirc glaub zwei oder drei Einträge angepasst/ergänzt. Kann also leicht von pristinen Dateien abweichen:

Code: Alles auswählen

BPLZ (259)
    Primary pointer page: 499, Index root page: 500
    Data pages: 308, data page slots: 308, average fill: 98%
    Fill distribution:
	 0 - 19% = 0
	20 - 39% = 0
	40 - 59% = 0
	60 - 79% = 0
	80 - 99% = 308
In der 2012.2 dann:

Code: Alles auswählen

BPLZ (261)
    Primary pointer page: 536, Index root page: 537
    Data pages: 385, data page slots: 385, average fill: 78%
    Fill distribution:
	 0 - 19% = 0
	20 - 39% = 0
	40 - 59% = 0
	60 - 79% = 373
	80 - 99% = 12
Das deckt sich mit dem Unterschied in der "no reserve" Konfiguration.

DARÜBER hinaus hab ich als Nievielgeldgehabter immer Hardware-mäßig den Kopf in den Sand gesteckt, bin daher bis hin zu Dateisystemen unbewandert -- ABER was bedeutet denn doch gleich auch auf unfragmentierten Platten eine page-Zunahme hinsichtlich des verwendeten Dateisystems? Wie wirken sich die 20% no-reserve/reserve http://www.firebirdsql.org/manual/gfix-pagespace.html dann auf eine verhältnismäßg große Datei aus?
Zumal ja NTFS irgendwie von Partitionsgröße abhängig seine Clustergröße bestimmt http://de.wikipedia.org/wiki/Slack_(Dateisystem) .


Du könntest ja mal a) beide Datenbank-Dateien auf der gleichen Maschine speichern/erstellen und b) vor dem Messen miz gfix -sweep drüberbügeln.
Ich weiss aber gar hat, ob der Messzeitpunkt unmitttelbar nach einem backup/restore überhaupt geeignet ist?

Die Sache mit der Clustergröße hätte den charmanten Aspekt, dass man beruhigt eine spürbare Performance-Strafe ausschliessen könnte...
--
Client: 4.2.5.42
Datenbank AVERP2011-A.02

Antworten