Mittwoch, Mai 17, 2017

Online Partitionierung einer existierenden Tabelle in 12.2

Eine sehr schöne Ergänzung der Partitionierungs-Optionen in 12.2 beschreibt Maria Colgan in ihrem Blog: die Möglichkeit, eine nicht partitionierte Tabelle ohne downtime - also online - in eine partitionierte Tabelle umzuwandeln. Die Syntax dazu sieht etwa folgendermaßen aus:

alter table t
partition by ...
(
   partition p1 ...,
   partition p2,
 ...
)
update indexes online

Das sieht für mich sehr intuitiv und vor allem kompakt aus. Dabei dient "update Indexes" wie üblich dazu, die Indizes während des Aktualisierungsvorgangs verfügbar zu halten. Die Default-Optionen bei der Umwandlung der Indizes sind folgende:
  • Indizes, die bereits als "global partitioniert" angelegt wurden, behalten ihr Partitionierungs-Schema
  • Indizes, die nicht mit dem "partition key" starten, werden "global non-partitioned indexes"
  • Indizes, die mit dem "partition key" starten, werden lokal partitionierte Indizes
  • Bitmap Indizes werden zu lokal partitionierte Indizes
Dass man diese Default-Optionen überschreiben kann, ist bei Frau Colgan nur implizit angedeutet (nämlich durch die Verwendung des Terminus "default"), aber Richard Foote hat dazu vor kurzem ein Beispiel veröffentlicht: auf das "update indexes" folgen dann in Klammern die Spezifikationen der Konvertierung.

Eine zweite nützliche Ergänzung zur Partitionierung in 12.2 ist die Möglichkeit, die Basis-Tabelle für eine "partition Exchange" Operation mit einem Befehl "create table ... for exchange with table ..." anzulegen. Allerdings scheint dieses Kommando nicht zur automatischen Generierung der passenden lokalen Indizes zu dienen, so dass hier weiterhin eine gewisse Sorgfalt bei der Vorbereitung des partition exchange erforderlich bleibt - was aber insofern kein größeres Problem darstellt, als der Austausch von Partitionen aus meiner Sicht ohnehin ein Task ist, der verskriptet werden sollte.

Darüber hinaus erwähnt die Autorin noch eine dritte nützliche Ergänzung: die Einführung von interval partitioning für List-partitionierte Tabellen. Dabei hoffe ich, dass die Intervall-Partitionierung in 12.2 stabiler geworden ist, als sie das in früheren Releases geworden ist, aber das ist ein Thema, das im Artikel nicht angesprochen wird - und dem im Detail nachzugehen mir aktuell die Zeit fehlt.

Freitag, April 21, 2017

postgres Statistiken: pg_stat_all_tables

Alexey Lesovsky hat bereits eine ganze Reihe interessanter Artikel im Rahmen einer Serie "Deep dive into postgres stats" veröffentlicht. Bisher hatte ich mich davor gedrückt, diese Ausführungen zusammenzufassen, aber heute ist ein ruhiger Tag und die pg_stat_all_tables scheint mir eine besonders interessante Quelle zu sein...

Die View pg_stat_all_tables enthält eine ganze Reihe interessanter Informationen zur Nutzung von Tabellen. Unter anderem weist sie hin auf:
  • Zugriffsinformationen: die View enthalt Informationen zur Anzahl sequentieller Scans (also Full Table Scans, wie man sie in anderen RDBMS nennt) und zur Anzahl von Index-Zugriffen sowie zur Anzahl der dabei zurückgelieferten Datensätze. Eine hohe Zahl sequentieller Scans auf großen Tabellen deutet häufig auf fehlende Indizes hin. Weiterhin könnten Index-Zugriffe mit hohen durchschnittlichen Satzanzahlen auf wenig selektive Index-Zugriffe hindeuten.
  • Schreiboperationen: zeigt die Menge der DML-Operationen auf den Tabellen. Dabei wird zwischen Standard- und HOT-(=Heap-only tuples) Updates unterschieden, wobei letztere wünschenswert sind, da sie Indizes nicht aktualisieren, wenn ein Update keine inidzierten Werte verändert. Allerdings funktionieren HOT-Updates nur, wenn die zugehörige page ausreichend Platz übrig hat. Für Tabellen, bei denen sich HOT-Updates kaum ergeben, könnte eine Anpassung des Fillfactors sinnvoll sein (der nur für neue Datensätze berücksichtigt wird, den Platzverbrauch des Objekts vergrößert und in diesem Zusammenhang nur dann relevant ist, wenn es tatsächlich updates ohne Bezug auf indizierte Spalten gibt).
  • die autovacuum queue: seit 9.6 kann man bestimmte Informationen zum autovacuum aus der View pg_stat_progress_vacuum bekommen. Was fehlt ist allerdings weiterhin eine Liste der Tabellen, für die aktuell ein vacuum-Lauf erforderlich wäre. Dazu hat der Autor eine komplexe Query (die unter anderem auf pg_stat_all_tables zugreift) bereitgestellt, mit deren Hilfe sich die Länge der autovacuum queue bestimmen lässt. Basierend auf den Aussagen der Query kann man dann über Maßnahmen zur Optimierung des autovacuum nachdenken (Erhöhung der autovacuum_max_workers oder Anpassung anderer autovacuum-Parameter).
Im Vergleich zu dem, was andere RDBMS an internen Statistiken liefern, sind die Informationen bei postgres überschaubar, erlauben aber durchaus interessante Analysen.

Dienstag, April 18, 2017

postgres Extensions

Zwei interessante Hinweise findet man im neusten postgres-Artikel von Daniel Westermann:
  • das data dicitionary von postgres liefert zahlreiche Informationen zu den verfügbaren und den installierten Extensions:
    •  pg_available_extensions: zeigt die verfügbaren und die installierten Extensions inklusive eines Kommentars zu ihrer Funktion.
    • pg_available_extension_versions: liefert weitere Detailinformationen zu den Extensions, unter anderem zu den Abhängigkeiten, die zwischen den Erweiterungen bestehen.
    • pg_extension: liefert Informationen zu den installierten Extensions. Dabei weichen die Informationen von denen ab, die der psql-Shortcut \px liefert.
  • beinahe ebenso interessant ist der zweite Hinweis, den der Herr Westermann liefert: mit dem Switch: "\set ECHO_HIDDEN on" kann man die den psql-Shortcuts zugrunde liegenden Befehle anzeigen lassen. Mir war zwar klar, dass das irgendwie möglich sein sollte, aber bisher hatte ich mir nie die Mühe gemacht, nach dieser Option zu suchen.

Donnerstag, April 06, 2017

Skript zur Bestimmung von Index Fragmentation

In den OTN Foren gibt es bei diversen Beiträgern eine gewisse Tendenz dazu, auf bestimmte Schlüsselwörter allergisch zu reagieren. Eines dieser Schlüsselwörter ist: Fragmentation. Im ungünstigen Fall bekommt man dann zur Antwort, dass es so etwas wie Fragmentation nicht gäbe - was ich für ausgemachten Blödsinn halte -, im günstigeren Fall, dass man den Terminus bitte erst mal definieren sollte, was durchaus eine sinnvolle Reaktion darstellt. Aber da der Fall von Index Fragmentation eher weniger kontrovers ist, belasse ich es hier bei der harmlosen Einordnung: ein Index ist dann fragmentiert, wenn er deutlich mehr Leaf Blocks beinhaltet als zur Speicherung der enthaltenen Einträge eigentlich erforderlich wären. Bei Kellyn Pot’Vin-Gorman habe ich heute einen Verweis auf ein nützliches Skript von Franck Pachot gefunden, das Folgendes leistet: "shows the number of rows per block, as well as used and free space per block, and aggregates that by range of values". Für die Detail-Analyse einzelner Indizes kann dieses Skript extrem nützlich sein - und die manuelle Sammlung und Verknüpfung der entsprechenden Informationen vermeiden.

Mittwoch, März 29, 2017

Index Statistiken und Column Group Statistiken (Extended Statistics)

Nach einiger Zeit widme ich mich mal wieder einem meiner Hobbies: der Zusammenfassung des aktuellen Artikels von Jonathan Lewis. Diesmal geht es darum, dass der Herr Lewis gerne den - von mir häufig wiederholten - Vorschlag macht, bei der Löschung eines aus Zugriffssicht überflüssigen Index entsprechende "extended statistics" für die fragliche column group anzulegen, da der Optimizer die distinct key Angabe eines Index zur Durchführung eines Sanity Checks bei der cardinality-Bestimmung verwenden kann - was die Beurteilung von Index-Löschungen grundsätzlich schwieriger macht als die der Neuanlage von Indizes. Außerdem hat der Autor gelegentlich darauf hingewiesen, dass column group Statistiken dann Probleme bereiten (so heißen: nicht berücksichtigt werden), wenn die Einschränkung für eine der fraglichen Spalten außerhalb des Bereichs der für diese Spalte bekannten Werte liegt (also außerhalb des Korridors von low_value und high_value in user_tab_columns). Im aktuellen Artikel bringt er diese beiden Beobachtungen zusammen und stellt die Frage: kann die Löschung eines Index und Anlage von entsprechenden column group Statistiken im Fall derartiger Prädikate zu einer Veränderung der cardinality Schätzung führen. Dazu gibt es wie üblich ein Test-Beispiel: diesmal mit einer Tabelle mit zwei Spalten mit vollständig korrelierenden Werten, was ohne den Index oder die column groups in jedem Fall zu massiven Fehleinschätzungen des Optimizers führen würde. Auf dieser Basis werden drei Queries ausgeführt:
  • eine Query mit Index und zwei Prädikaten innerhalb des Ranges der bekannten Werte
  • eine Query mit Index und zwei Prädikaten außerhalb des Ranges der bekannten Werte
  • eine Query ohne Index - aber mit column group Statistiken - und zwei Prädikaten außerhalb des Ranges der bekannten Werte
 Die Ergebnisse zeigen für die drei Fälle:
  • für die erste Query werden die distinct keys des Index als Sanity Check bei der Kalkulation der Cardinality verwendet: dadurch ergibt sich ein Full Table Scan.
  • für die zweite Query werden die individuellen Selektivitäten der Spalten verwendet und jenseits des bekannten Wertebereichs setzt die übliche Abnahme der erwarteten Trefferwahrscheinlichkeit ein (linear decay): dadurch ergibt sich ein Index Zugriff.
  • für die dritte Query ergibt sich wieder der Full Table Scan.
Für den Herrn Lewis ergibt sich daraus: "So my concern that substituting column groups for indexes was unfounded – the optimizer was being silly (legal disclaimer: that’s just my opinion) with indexes, and the silly (ditto) behaviour with column groups hasn’t changed anything." Mir ist diese Antwort noch nicht ganz klar, da mein Eindruck ist, dass sich column group und der Index hier nicht zu den gleichen Ergebnissen führen - aber möglicherweise entgeht mir hier etwas Entscheidendes. Möglicherweise bekomme ich dazu noch eine Erklärung, die ich dann hier ergänzen würde.

Nachtrag vom gleichen Tag: Jonathan Lewis hat's erklärt: "The second and third plans both have a cardinality of 79 (which is the only thing the note was about). Were you looking at the cost, perhaps?" Tatsächlich hatte ich auf die cost-Angaben geschaut...