Redo-Struktur bei Row-Migration und Row-Chaining

Eine interessante Beobachtung von Kamil Stawiarski: wenn ein Update Row-Migration oder Row-Chaining hervorruft, dann entspricht die Struktur der zugehörigen redo-Informationen jener eines Delete mit folgendem Insert. Wenn man darüber nachdenkt, ist das völlig einleuchtend, denn der Datensatz muss aus dem einen Block gelöscht und in den anderen Block eingefügt werden. Aber dass man das so genau in den Redo-Einträgen nachvollziehen kann, war mir neu. Eine Konsequenz dieses Phänomens ist dann auch, dass Row-Migration oder Row-Chaining nicht nur auf Selects eine negative Wirkung haben, sondern auch das Redo-Aufkommen erhöhen.

Nachtrag 26.07.2017: Jonathan Lewis hat in Zusammenhang mit dem Artikel eine Liste der Redo-OP-Codes veröffentlicht, die ihm bekannt sind.

Unpivot mit Oracle

Vor einigen Wochen hat Jonathan Lewis einen Artikel zum Verhalten von UNPIVOT in Oracle veröffentlicht. Darin zeigt er mit Hilfe eines CBO-Traces, dass die UNPIVOT-Operation intern in eine Kombination mehrerer über UNION ALL verknüpfter Queries umgewandelt wird. Interessant ist dabei, dass die Zahl der Blockzugriffe (Buffers-Angabe in den rowsource-Statistiken) nicht höher ist als bei einem einzelnen Full Table Scan. Jonathan Lewis vermutet, dass der zugehörige code path dafür sorgt, dass die Blöcke im Cache Batch-weise gepinnt, dann fünf Mal gescannt und dann wieder freigegeben werden. Immerhin deutet das darauf hin, dass UNPIVOT die bessere Option als die Do-it-yourself-Transposition, die ich in der Regel noch immer verwende.

Analyse der Block-Struktur mit Postgres

Frits Hoogland, dessen Artikel zu Oracle zu den fundiertesten technischen Beiträgen gehören, die mein Blog-Reader mir liefert, hat zuletzt zwei Artikel veröffentlicht, in denen er sich mit der Analyse von Blocks in Postgres-Datenbanken beschäftigt. Da seine Analyse weit über das hinausgeht, was ich in der Vergangenheit in dieser Richtung herausgefunden hatte, erzähle ich hier mal wieder einen Teil der wichtigeren Inhalte nach:

• Postgresql block internals: zeigt die Verwendung der pageinspect Extension, mit der man Blockinhalte darstellen kann. Auch weist der Autor darauf hin, wie vorteilhaft es ist, den Sourcecode unmittelbar einsehen zu können, statt ausschließlich auf Induktion angewiesen zu sein: https://doxygen.postgresql.org/. Mit Hilfe der in der Extension verfügbaren Funktionen (page_header, heap_page_items, get_raw_page) werden der page header ausgelesen und die row entries dargestellt. Über das OS-Kommando xxd wird eine lesbarere Repräsentation der physikalischen Blockinhalte der ros erzeugt.
• Postgresql block internals, part 2: wiederum werden im Test postgres 9.6 und Oracle Linux 7u3 verwendet. Verwiesen wird auf die umfangreiche Dokumentation des Page Layouts unter https://www.postgresql.org/docs/9.6/static/storage-page-layout.htm. Auf Basis der Dokumentation erfolgt eine detaillierte Analyse der einzelnen Bestandteile der Einträge mit den Informationen zu Transaktionsnummern und internen Flags. Genauer untersucht werden die bit mask Informationen in den Feldern t_infomask (Attribut- und Transaktionsinformationen) und t_infomask2 (Update-Status-Angaben, Sichtbarkeit). Weiterhin wird die Block/Page-Struktur für einen Index untersucht, die den gleichen Header, aber andere Inhalte enthält. Zur Analyse gibt es weitere pageinspect-Funktionen, nämlich bt_metap (zur Darstellung der metapage, die auf den root-Block des Index verweist), bt_page_items (zur Darstellung der index entries) und bt_page_stats (zur Darstellung der Index Struktur). Im Fall der Struktur ist eine weitere Detailanalyse erforderlich, um aus den Daten klug zu werden und dabei zieht der Autor wiederum die Angaben des Source-Codes heran. Die zugehörigen Erläuterungen sind zu komplex, als das ich sie hier im Detail wiedergeben wollte. In der Zusammenfassung erscheinen dazu noch folgende Punkte: wenn ein Index sehr klein ist, können root und leaf Einträge in einer page zusammengefasst werden. Für größere Indizes werden die pages getrennt. Der default-Fill-Factor eines Index ist 90% (gegenüber 100% für eine Heap Tabelle). Erläutert werden auch das Split-Verhalten und die Verknüpfung der Leaf-Blocks, die nach meinem Verständnis recht ähnlich aussehen wie im Fall von Oracle (das auch der Herr Hogland als idealtypisches Muster bzw. Basisreferenz betrachtet, um die Unterschiede für den Postgres-Fall zu erläutern).
• Postgresql block internals, part 3: erläutert die Handhabung des Space-Managements im Block bzw. der Page im Fall von DML-Operationen. Im Test wird zunächst das Autovacuum deaktiviert, um die Effekte überhaupt exakt beobachten zu können, was für ein produktives System natürlich keine gute Idee wäre. Dann erfolgt ein Update, das im page header einen neuen Eintrag ergänzt, der über die Informationen in t_xmax und t_cid mit dem ursprünglichen Eintrag zur aktualisierten Id verknüpft ist - bekanntlich arbeitet die MVCC-Implementierung von postgres mit einer expliziten Speicherung unterschiedlicher Versionszustände in der gleichen Tabellenstruktur. Im zugehörigen Index gibt es keinen zusätzlichen Eintrag, da das geänderte Attribut nicht indiziert ist (es handelt sich um ein HOT - heap only tuple - Update): hier verweist in der Page dann ein Pointer vom initialen zum geänderten Datensatz. Erfolgen mehrere Änderungen auf dem selben Datensatz nacheinander, wird die Liste der Verknüpfungen verlängert - was im Fall des HOT Update die Performance beeinträchtigen kann. Aufgeräumt werden kann, wenn keine Transaktion mehr existiert, die noch Zugriff auf eine alte Version benötigt: dann kann vacuum den zugehörigen Datensatz löschen. Durch den vacuum-Befehl werden die Header-Einträge der gelöschten Datensätze aber nicht gelöscht, sondern mit entsprechenden Flags markiert, unter anderem, weil der zugehörige Index weiterhin auf den ersten Eintrag der verketteten Liste verweist. Die Offsets der gelöschten Einträge zeigen dabei, dass sie ans Ende der Page verschoben wurden, um den freien Platz in der Page zu maximieren.
  Um die Einträge vollständig zu beseitigen kann vacuum full verwendet werden, was allerdings ein table lock erfordert und daher in vielen Fällen keine Option darstellt. Im Fall der Aktualisierung eines indizierten Attributs ist ein HOT Backup nicht möglich: stattdessen muss in diesem Fall auch im Index ein neuer Eintrag ergänzt werden - dabei bleibt der alte Eintrag erhalten, aber die Informationen im Index enthalten keine Angaben zur Versionierung, so dass die Entscheidung über die Relevanz eines Ergebnisses erst in der data page getroffen werden kann. Durch ein erneutes vacuum kann in der data page jetzt der Eintrag als leer markiert werden, auf den der ursprüngliche Index-Eintrag verwies. Und im Index wird der zugehörige obsolete Eintrag entfernt.
  Ein weiterer relevanter Fall betrifft das Verhalten von Updates für einen vollständig gefüllten Block (Bzw. Page): ein Update erzeugt unter diesen Umständen eine neue Datensatzversion, die nicht mehr in der initialen Page untergebracht werden kann. Die Verknüpfung in die andere Page kann wiederum über den Pointer t_ctid verfolgt werden. In diesem Fall wird das Update nicht als HOT ausgeführt, sondern es existieren zwei Index-Einträge für den gleichen Wert, die in unterschiedliche Blöcke verweisen (wiederum, weil der Index allein nicht die Information liefert, welcher zugehörige Datensatz für eine bestimmte Transaktion relevant ist). Ein vacuum sorgt wiederum dafür, dass der ursprüngliche Datensatz in der data page als obsolet gekennzeichnet und der darauf verweisende (obsolete) Index-Eintrag entfernt werden.
  Nach dem Abschluss einer DML-Operation muss der nächste lesende Zugriff noch Anpassungen am "hint bit" in t_infomask durchführen, was Einträge im WAL file hervorruft. Dieser zusätzliche Aufwand ist nur für die erste folgende Leseoperation - und auch ein vacuum sorgt für das Setzen des "hint bist".

Wie üblich ist meine Zusammenfassung deutlich weniger aussagekräftig als das Original, das man sich im Zweifel lieber noch mal komplett durchlesen sollte. Eine derart gute Darstellung des Block Managements bei Postgres hatte ich bisher nicht gelesen.