Theoriegrundlage: Berechnung einer Datenbank-Größe

[gurbelunder]

Hallo an alle,
ich habe gerade ein Problem aus meinem DB Unterricht von der Uni. Wir haben heute das Berechnen einer Datenbankgröße gemacht. Ich selber war leider nicht anwesend wegen Krankheit. Und wie es üblich ist: keiner meiner Kommilitonen kann es mir vernünftig erläutern. Also hoffe ich auf euch. Folgendermaßen mal zu einer Aufgabe:

"Eine DB hat 4 Tabellen. Jede Tabelle hat eine Spalte smallint, 3 Spalten varchar(30), 1 x datetime, 1x money.
Die erste Tabelle hat 200.000 Datensätze. Die anderen Tabellen sind folgendermaßen gefüllt:
2. Tabelle 85% der 1. Tabelle
3. Tabelle 55% der 1. Tabelle
4. Tabelle 35% der 1. Tabelle
VarcharSpalten sind alle gefüllt.
Berechnen Sie: Gesamtzeilenlänge und die Zeilen pro Seite, anfängliche Größe der Datenbank."

Folgende Sachen sind mir bekannt:
smallint = 2 Byte, money und datetime = 8 Byte, varchar ist hier 10 Byte wegen der Angabe in ().
Eine Seite kann 8KByte fassen.
Meine feste Datengröße müssten alle Datentypen mit festgelegten Bytegrößen sein, hier also smallint, money und datetime. Pro Zeile sind das also: 18 Byte/Zeile
Die variable Datengröße bezieht sich hier auf den varchar Datentyp, dazu habe ich folgende Formel:

variable_datasize = 2 + (num_var_col * 2) + max_var_size

In dieser Betrachtung müssten "num_var_col" und "max_var_size" ja beide 10 Byte groß sein, oder? Das heißt dann: variable Datengröße = 32 Byte. Das klingt nur irgendwie "viel", denn der nächste Schritt wäre nun die Datengröße für die Zeile:
Nullbitmap und Seitenheader ist hier auf 4 Byte festgelegt

Zeilengröße = feste Datengröße + variable Datengröße + Nullbitmap + Seitenheader
= 18 + 32 + 4 + 4 = 58 Byte / Zeile

Wenn ich also nun schauen will, wie viele Seiten mit meinen 200.000 Datensätzen belegt werden, rechne ich das ins Verhältnis:

von der Gesamtgröße der Seite (8KByte) gehen 96 Byte(weis nicht, was die sind) weg, somit 8096 Byte Fassung pro Seite:

Zeilen pro Seite = 8096 / (58Byte + 2 Byte (irgendwie Anfang und Ende jeder Zeile))
= 134,93333333333333 = 135

Bei 200.000 Datensätzen / Zeilen sind das dann:
200.000 / 134,93333333 = 1482,213439
= 1483 Seiten, (sollen nicht geteilt werden)

Somit weis ich also, die Datenbank würde (theoretisch) 1483 Seiten gebrauchen. Nun meine Frage: Stimmt das so, was ich da gemacht habe?
Wäre super, wenn ihr mir dazu was sagen und evtl. sogar erläutern könntet.
Vielen Dank schonmal an euch und entschuldigt, wenn was nicht verständlich ist. Mir raucht auch die Rübe. Sonst fragt nochmal. Vielleicht kann ich dann nochmal was zu meinen Aufzeichnungen sagen.

Danke euch im vorraus

David

 

[gurbelunder]

Nach einer Nacht guten Schlafes und erneutem Drüberschauen und Hilfe eines guten Mitkomilitonen hat es dann endlich seinen Weg ins Stammhin gefunden. Es sind doch einige Sachen anders. Sollte einer mal Interesse zeigen, werd ich das gerne posten.

 

[PLSQL_SQL]

JA bitte poste mal den genauen Rechengang nach nochmaliger anführung der Aufgabenstellung.
so Step by Step!

Danke und Lg

 

[gurbelunder]

so hatte ich das vor, gebt mir ein zwei tage zeit, bin grad in den Abschlussprüfungen

 

[gurbelunder]

Also, da wollen wir mal!
Erstmal grundlegend: Es ist eine reine Theoriebetrachtung, in der Realität gibt es sicher Abweichungen!

Um das besser zu erklären, haben wir mal folgende Betrachtung:
Wir haben eine Datenbank mit einer Tabelle. Diese Tabelle hat 6 Spalten mit folgenden Datentypen: 1 x int, 1 x datetime, 1 x money und 3 x varchar(30). In die Tabelle kommen 200.000 Datensätze. Jede Spalte wird gefüllt.
Dabei soll die Gesamtgröße der Datenbank berechnet werden und angegeben werden, wie viel Datenbankseiten die Daten benötigen.

Zuerst mal folgende Einteilung:

• int, datetime und money werden bei der Betrachtung als Festdatengröße gesehen: ein int Feld hat 4 Byte, datetime und money 8 Byte. Jedesmal, wenn eine dieser Spalten gefüllt wird, ist sie immer so groß bei der Betrachtung.
• varchar ist ein variabler Datentyp. Man kann ihn von 1-30 Byte füllen. Er soll in der Rechnung grundsätzlich komplett gefüllt werden.

Nun zu den Berechnungen. Zuerst müssen wir die Größe berechnen, die ein Datensatz einnimmt:

• als erstes Rechnen wir die Festdatengröße aus. Dazu werden einfach alle feste Datenspalten zusammenadiert:
  • (1 x 4 Byte(int)) + (2 x 8 Byte (datetime und money)) = 20 Byte
• nun folgen die variablen Datengrößen. Hierbei ist zu beachten, dass diese Größen nicht nur den Inhalt, sondern auch Informationen zum Inhalt speichern:
  • 2 Byte + (Anzahl variabler Spalten * 2) + maximale Datengröße ALLER variablen Spalten
  • 2 Byte + (3 * 2) + (3 * 30 Byte) = 2 Byte + 6 Byte + 90 Byte = 98 Byte
• Als nächstes folgt die Nullbitmap. Soweit ich das verstanden hab, ist hierbei die Betrachtung aller Spalten, da keine eine NOT NULL Zuweisung bekommt.
  • 2 Byte + ((Anzahl der Spalten, die NULL sein können + 7) / 8)
  • 2 Byte + ((6 + 7) / 8) = 2 Byte + 1,625 = 3,625 Byte
• Nun kommt die Zeile, also ein einzelner Datensatz:
  • feste Datengröße + variable Datengröße + Nullbitmap + 4 (Seitenheader, hier einfacht festgelegt)
  • 20 Byte + 98 Byte + 3,625 Byte + 4 Byte = 125,625 Byte

Somit wissen wir, das eine einzige Zeile insgesamt 125,625 Byte groß sein kann. Um nun zu sehen, wie viel Datensätze auf eine Seite passen, wird wie folgt rangegangen. Wichtig hierbei ist zu wissen: eine Seite fasst 8192 Byte. Davon abziehen: Header und Footer, somit bleiben: 8096 Byte. Zusätzlich kommen 2 Byte zum Datensatz, 1 Byte Anfang, 1 Byte Ende sozusagegen. Wie bei einer CD LeadIn und LeadOut:

• 8096 / (Zeilengröße + 2 Byte)
• 8096 / (125,625 Byte + 2 Byte) = 63,435847208619 Zeilen

Man rundet dabei IMMER ab, damit man auch sicher gehen kann, das alles passt, also 63 Zeilen passen in dem Moment auf eine Seite. Nun wollen wir wissen, wie viele Seiten die Tabelle füllen wird mit ihren 200.000 Datensätzen:

• 200.000 / 63 = 3174,603174603175

Hierbei muss beachtet werden, dass IMMER AUFgerundet wird, wieder sichergehen, dass genug da ist. Somit braucht unsere Tabelle 3175 Seiten.
Um nun die Größe der Tabelle und, in diesem Fall, die Größe der Datenbank zu betrachten, wird nun noch die Anzahl mit der maximalen Größe einer Seite multipliziert. Auch, wenn die letzte Seite sicher nicht voll beschrieben wird, kann man, der leichteren Rechnung entgegenkommend, davon ausgehen, dass alle Seiten voll werden. In dem Moment machen die paar Kilobyte sowie so nicht mehr viel

• 3175 * 8192 Byte/Seite = 26009600 Byte = 25400 KByte = 24,8046875 MByte

Nun wissens wir: Planen wir eine Datenbank in diesem Format, sollten wir mit einer Tabellengröße von ca. 25 MB rechnen.

 

[akretschmer]

Also, da wollen wir mal!
Erstmal grundlegend: Es ist eine reine Theoriebetrachtung, in der Realität gibt es sicher Abweichungen!

Ich möchte anmerken, daß oft zusätzliche Spalten vorhanden sind, bei PostgreSQL z.B. xmin/xmax für die Transaktions-Sichtbarkeit der Zeile, ctid für interne Zwecke, evtl. noch die oid. Dazu kommt, daß oft Seiten nur zu einem definierten Prozentsatz gefüllt werden. Außerdem ist zu beachten, daß u.U. durch Updates/Deletes noch dead tuple vorhanden sind.

Aber ansonsten: gut erklärt!

 

[gurbelunder]

Ich möchte anmerken, daß oft zusätzliche Spalten vorhanden sind, bei PostgreSQL z.B. xmin/xmax für die Transaktions-Sichtbarkeit der Zeile, ctid für interne Zwecke, evtl. noch die oid. Dazu kommt, daß oft Seiten nur zu einem definierten Prozentsatz gefüllt werden. Außerdem ist zu beachten, daß u.U. durch Updates/Deletes noch dead tuple vorhanden sind.

Aber ansonsten: gut erklärt!

Danke erstmal für die Blumen !

Du kennt wohl die Rechnung? Bis auf einige Kleinigkeiten?

 

[akretschmer]

Danke erstmal für die Blumen !

Du kennt wohl die Rechnung? Bis auf einige Kleinigkeiten?

Nun ja, das ist natürlich alles auch von der konkreten Implementierung abhängig, auch vom OS, ob 32 oder 64 Bit, evtl. weitere Compileoptionen und so fort.
Aber das prinzipielle Vorgehen, das zu berechnen, ist IMHO so korrekt. Man sollte aber vielleicht es eingrenzen und sagen: gilt für Datenbank XYZ, kann je Datenbank und Wetterlage auch mehr oder weniger abweichen ...


Andreas

 

[gurbelunder]

Nun ja, das ist natürlich alles auch von der konkreten Implementierung abhängig, auch vom OS, ob 32 oder 64 Bit, evtl. weitere Compileoptionen und so fort.
Aber das prinzipielle Vorgehen, das zu berechnen, ist IMHO so korrekt. Man sollte aber vielleicht es eingrenzen und sagen: gilt für Datenbank XYZ, kann je Datenbank und Wetterlage auch mehr oder weniger abweichen ...


Andreas

Das auf jeden Fall, darum sollte das auch eine rein theoretische Grundlage sein. Praxisbezogen muss man ja schon den Unterschied zwischen varchar und varchar2 bei Oracle beachten. Das wirkt sich dann schon anders aus. Aber normalerweise sollen die Abweichungen nicht unbedingt so groß sein. Unser Dozent hat damals gesagt: "Wenn ihr sicher gehen wollt, nehmt die errechnete Größe einfach mal 2.....". Ich glaube, damit kommt man dann sicher weit .