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...

Mittwoch, März 22, 2017

Formatter-Einstellung für SQLcl

Dass die Häufigkeit meiner Beiträge zuletzt weiter abgenommen hat, mag dem regelmäßigen Leser aufgefallen sein und dafür gibt es - wie in solchen Fällen üblich - berufliche und private Ursachen (im weitesten Sinne steht Arbeit im Weg). Um aber nicht völlig zu verstummen hier mal wieder ein Link: Jeff Smith erläutert in seinem Blog, wie man eine SQL-Query in SQLcl mit dem Befehl "format buffer" automatisch formatieren lassen kann. Zusätzlich zeigt er, dass man die Regeln des Formatters im SQL Developer bearbeiten, in eine Datei exportieren und von SQLcl einlesen lassen kann: damit lässt sich die Formatierung dann den eigenen Wünschen entsprechend anpassen. Es wird wirklich Zeit, dass ich SQLcl zu meinem Standard CLI mache (und SQL*Plus nach wenigen Jahrzehnten hinter mir lasse).