Kronester

… oder das ewige Leid mit dem Teilen.

Einleitung

Es gibt tatsächlich einen Grund warum ich mich jetzt doch noch entschlossen habe diesen Blog auch zu beruflichen Zwecken zu nutzen. Ich scheine nämlich in ein Alter gekommen zu sein in dem alle meine sozialen Kontakte annehmen dass es auch eine gewisse Weisheit mit sich bringt. Daraus folgend werde ich permanent mit Fragen zu allen möglichen Themen bombardiert und man erwartet eine sofortige Antwort und Beratung zu allen diesen Anliegen. Da aber auch meine Zeit auf Erden begrenzt ist, möchte ich die immer wieder auftauchenden Fragen einmalig hier beantworten und in Zukunft dann auf diese Artikel verweisen können.

In letzter Zeit häufen sich die Fragen zum Thema “wie behandle ich Tabellen die sehr groß werden können in SQL-Server?”. Leider lässt sich hier keine pauschale Antwort geben. Eine mögliche Alternative ist die Vermeidung großer Tabellen

Partitionieren (Aufteilen)

Eine weitere Möglichkeit ist die Aufteilung von Tabellen in mehrere einzelne physikalische Teile. Dies versetzt SQL-Server in die Lage mit seinen Ressourcen sparsamer umgehen zu können. In einem solchen Szenario müssen dann nur die Teildatenblöcke verarbeitet werden die die gesuchten Daten enthalten.

Die Partitionierung, von vielen als etwas Geheimnisvolles, technisch nicht Durchschaubares und daher potentiell Gefährliches wahrgenommen, kann uns doch einiges an Kopfschmerzen und Problemen abnehmen. Im Grunde bedeutet es nichts weiter als “eine Unterteilung eines Ganzen in mehrere Teile” (Die Wortwahl ist aus der Wikipedia).

Dieser Artikel soll nicht technisch in jedes Detail eintauchen sondern einen praxisorientierten Weg aufzeigen wie man dieses Thema im Umfeld von SQL-Server 2005 in den Griff bekommen kann. Wer hier eine wissenschaftliche Ausarbeitung recherchieren möchte dem sei die Online-Hilfe von Microsoft und noch mindestens 100 weitere Blogs im Netz empfohlen.

Die Arten der Partitionierung

Wenn man sich mit dem Thema Partitionierung beschäftigt, muss man sich erst einmal klarmachen was dies im Datenbankumfeld bedeutet. Wenn man den Begriff der Partitionierung in die Technik übersetzt, müsste man die “Problemkinder” einfach nur in diverse kleinere Blöcke aufteilen. Aus der Sicht von Programmen und Benutzern kann man aber eine Tabelle nicht einfach so zerstückeln. Vielmehr muss dies alles für die zugreifenden Systeme vollkommen transparent, d.h. nicht sichtbar, erfolgen. Im anderen Fall müsste bei jeder Änderung der Blöcke (neue Daten kommen hinzu, alte werden gelöscht, usw.) das abfragende System ebenfalls angepasst werden.

Ich möchte alle weiteren Beispiele mit Hilfe eines kleinen “virtuellen” Scenarios beschreiben. Einer Liste aller Mitarbeiter und Kunden der Firma “Adventure Works”. Diese Firma stellt sich als Datenbank dar, die mit den Beispielen von SQL-Server mitgeliefert wird.

Es gibt in der ADW (AdventureWorks) ein paar Tabellen die sich mit Personen beschäftigen. Stellen wir uns doch mal vor, wir müssten eine solche Personenliste manuell verarbeiten. Also jeden Tag kommen da ein einige Anfragen auf unseren Tisch die ein paar Informationen aus dieser Liste brauchen.

Hier die grundsätzliche Struktur der Liste:

Wie man leicht sehen kann haben wir hier alle Felder die man so braucht um eine Person zu beschreiben. Stellen wir uns nun vor wir verwalten in unserer Liste alle Personen die in Deutschlang gemeldet sind (dass müssten so ca. 88 Millionen Datensätze sein!!). Natürlich kann das eine einzelne menschliche Person nicht bewerkstelligen. Aber dies ist mein Blog und mein Computer ist auch nur ein Mensch … und der kann das!

Wir kriegen in unserem Scenario jede Stunde so ca. 10.000 Abfragen rein die Informationen aus dieser Liste (Tabelle) abrufen wollen. Das oben beschriebene Mengengerüst ist nicht allzu weit hergeholt also nicht vor den großen Zahlen erschrecken.

Einen eindeutigen Bezeichner (Primärschlüssel) für jeden Datensatz finden wir in der PersonenID. Diese ID bezeichnet jede Person in der Tabelle mit einer eindeutigen Kennzahl.

(Die E-Mail-Adresse stellt zwar auch einen eindeutigen Bezeichner dar, wird aber im Datenbankumfeld wegen des Textformats aus Performancegründen nicht so gerne gesehen.)

OK, bis jetzt stellen wir uns noch die Frage … was hat das alles mit Partitionierung zu tun? Na ja, schon eine ganze Menge. Hierzu mal ein kleines Rechenbeispiel.

Angenommen unser Datensatz ist ca. 300 Zeichen lang. Das bedeutet dass in einer Speicherseite im SQL-Server 13 Datensätze (unicode) gespeichert werden können. Unsere ganze Tabelle besteht also aus ca. 6,8 Millionen Speicherseiten.

SQL-Server kann Informationen immer nur in ganzen Speicherseiten lesen und schreiben. Weiterhin muss jede Seite in den Hauptspeicher unseres Rechners geladen werden um dort entsprechend weiterverarbeitet werden zu können. Nehmen wir weiterhin an, dass in 80% der Fälle nur die Telefonnummer gesucht wird.

Daraus ergibt sich folgende Rechnung wenn wir immer nur mit dem Primärschlüssel zugreifen:10.000 Abfragen (Datensätze) * 8000 Bytes (=4.000 Zeichen / Seite in Unicode) = 80.000.000 Byte = 80 MB / Std.Brauchen würden wir aber nur 10.000 mal 175 Zeichen = 350 Bytes * 10000 = 3.500.000 Byte = 3,5 MB / Std

Die Rechnung zeigt jetzt bereits wohin die Reise gehen sollte! Wir transportieren 95% Daten von der Festplatte über den Hauptspeicher die wir gar nicht brauchen!

Wenn wir nach einer Gruppe suchen (z.B. alle Namen die 1961 Geburtstag haben) sieht die Rechnung schon ganz anders aus. Hier muss SQL-Server ggf. die gesamte Tabelle durchsuchen um den richtigen Datensatz zu finden. In der Praxis würden wir hier ebenfalls über einen Index zugreifen. Bei sehr großen Tabellen wird dieser Index aber eventuell nicht von SQL-Server verwendet. Dies hängt mit dem Indexhandling von SQL-Server zusammen und ist nicht Thema dieses Artikels. (Vielleicht später mal …)

Bei einer Abfrage die nicht über den Index läuft ergibt sich dann die nachfolgende Rechnung:Tabellengröße:
88 Mio. Datensätze / 13 DS pro Seite = 6,8 Mio Seiten = 54 GByte
10.000 Abfragen * 54 GB = ca. 540 TB / Stunde !!!

Ok, ok die Freaks sagen jetzt 1 MB = 1024 Byte oder das könnte man aber mit den folgenden 20 Methoden anders regeln.

Aber nochmal … dies ist mein Blog.

Natürlich ist das Beispiel ein bisschen konstruiert, aber ich denke man kann sehr gut erkennen wo bei großen Tabellen die Probleme entstehen können.

Flach oder Steil? … horizontal oder vertikal

Bei einer vertikalen Partitionierung teilt man die Tabelle in der Senkrechten (eben vertikal!).

Konkret bedeutet dies, dass danach mindestens zwei Tabellen existieren. Eine Haupttabelle die die Felder enthält die in ca. 80 % aller Abfragen benötigt werden und einer Nebentabelle die die Felder für die restlichen 20 % der Abfragen enthält.

Diese beiden vertikalen Teile (Partitionen) kann man dann über einen 1:1-Schlüssel (ein Satz in der Haupttabelle zeigt immer nur auf einen Satz in der Nebentabelle) und eine View wieder vereinigen. Der Optimierer von SQL-Server erkennt bei einer Abfrage ob Felder aus der Nebentabelle verwendet werden und lässt dann, wenn möglich, die komplette Tabelle bei der Selektion außer Acht. Alle Benutzer und Anwender verwenden nur die View für den Datenzugriff.

Wenn man die oben beschriebene Liste z.B. in zwei Tabellen aufteilen würde um bei 80 % der Fälle nicht soviele Daten bewegen zu müssen, könnte die Struktur demnach wie folgt aussehen:

Wie man leicht erkennen kann, wird zwar Speicherplatz für den Verbindungsschüssel (VPart_Key) verwendet, aber dies sind auch bei großen Tabellen selten mehr als 4 Byte (ca. 2,4 Milliarden Datenzeilen). Wenn der Primärschlüssel der Tabelle aus nur einem Feld besteht kann man dieses Feld auch als Verbindungsschlüssel verwenden. Wie in unserem Fall benötigen wir dann nur die PersonenID auch in der 20%-Tabelle.

Der Vorteil der vertikalen Partitionierung liegt einfach in der niedrigeren Datenmenge je Datensatz und die Übertragung von mehr Datensätzen je Speicherseite. Jetzt passen ja deutlich mehr als doppelt so viele Zeilen auf eine Seite der Haupttabelle.

Hier nochmal die Rechnung für die vertikale Partitionierung:Tabellengröße der 80% Haupttabelle:

88 Mio. Datensätze / 33 DS pro Seite = 2,6 Mio Seiten = 20 GByte 10.000 Abfragen * 20 GB = ca. 200 TB / Stunde !!!

Immer noch viel, aber jetzt schon weniger als die Hälfte an Daten die bewegt werden müssen!

Wo es eine vertikale Aufteilung gibt, gibt es auch eine horizontale Variante.

Bei der horizontalen Partitionierung kommen wir dann auch endlich zum eigentlich interessanten Thema dieses Artikels.

Immer dann, wenn man bei einer Abfrage einen Filter definieren kann der in allen Abfragen (oder zumindest 80% aller Abfragen) auftaucht, kann man sich mit der Frage beschäftigen ob eine horizontale Partitionierung sinnvoll sein könnte.

Anmerkung: (Wer mehr zu dem 80/20 Prinzip (Pareto-Verteilung) erfahren möchte, kann sich das hier mal ansehen. Es funktioniert so fast überall im Leben)

Wie oben bereits erwähnt, brauchen wir für eine Partitionierung auf der horizontalen Ebene einen Filter bzw. ein Gruppierungsmerkmal. Dieses Kritierium kann eine Zahl, ein String oder auch ein Datum sein. In den meisten mir bekannten Fällen wird eine Zahl verwendet die ein Datum repräsentiert (z.B. 200805 für Mai 2008).

Die aufzuteilende Tabelle wird mit Hilfe dieses Filterkriteriums in viele kleine Blöcke zerlegt. In jedem dieser Blöcke hat das Filterfeld immer den gleichen Wert. Also sind in einem Teilblock nur Datensätze vom Mai 2008 enthalten, in einem anderen Block nur Datensätze vom April 2008 usw..

In unserem Beispiel möchte ich die Tabelle allerdings nach den Anfangsbuchstaben der Nachnamen aufteilen. Also für jeden Buchstaben im Alphabet eine Teiltabelle. Dies ergibt dann max. 26 Partitionen. Wir benötigen immer eine Partition für sogenannte Überlaufdaten die hier noch dazugerechnet werden muss.

Wir werdem im Laufe dieser Serie auch noch ein nicht ganz so theoretischen Beispiel ansprechen und dort die Partitionierung über die Zeitachse durchführen.

Schematisch sieht das Adressbeispiel mit den Anfangsbuchstaben dann so aus:

Die Zahlen unter den Buchstaben stellen hierbei den ASCII-Code des jeweiligen Zeichens dar. Unser Computer kann eben nur mit Zahlen umgehen.

Wenn wir jetzt nochmal unsere Rechnung aufstellen sollten die zu transportierenden Daten durch den SQL-Server signifikant sinken wenn wir annehmen das wir als Bereichsverarbeitung nur die Namen von A bis D bearbeiten sollen.

Tabellengröße der 80% Haupttabelle je Anfangsbuchstabe: 88 Mio. Datensätze / 33 DS pro Seite = 2,6 Mio Seiten = 20 GByte / 27 Partitionen = 0,74 GB / Partition!
10.000 Abfragen * 0,74 GB = ca. 7,4 TB / Stunde und Anfangsbuchstabe !!!

Die Datenmenge ist zwar immer noch enorm (hat mich auch Erstaunt … hat jemand einen Rechenfehler entdeckt?) und müsste durch weitere Maßnahmen noch weiter reduziert werden. Es zeigt aber deutlich wo der Sinn der Partitionierung liegt.

….

Zum Abschluss hier nochmal ein Überblick über die partitionierte Tabelle:

Nachdem wir jetzt wissen, was für ein Potential hinter dem Konzept der Partitionierung steckt, werde ich in den nächstem Folgen zu diesem Thema auf die technische Realisierung mit Hilfe von SQL-Server 2005 eingehen.

bis dahin …

1. Allgmein
2. vertikale Partitionierung
3. horizontale Partitionierung
4. Sliding Windows
5. Switch Partition