Freitag, Oktober 23, 2020

Postgres Performance seit Version 8.3

Ja, es ist eine Weile her, seit ich hier zuletzt etwas geschrieben habe. Und so richtig viel Neues kommt auch jetzt nicht. Nur der Link auf eine interessante Untersuchung von Tomas Vondra von 2ndQuadrant, der die Performance von Postgres beim TPC-H Benchmark für die Versionen 8.3 bis 13 untersucht. Die Ergebnisse sind in vielen Details sehr interessant und zeigen unter anderem die Rolle, die die Parallelisierung bei den neueren Releases spielt. Aus den Graphen würde ich ableiten, dass die letzten Releases seit Version 10 keine extrem spektakulären Performance-Verbesserungen mehr gebracht haben, aber durchaus eine positive Entwicklung. Das mag aber auch an der Skalierung liegen, da die Verbesserungen davor sehr deutlich sichtbar waren.

Donnerstag, Juli 09, 2020

Join-Performance in relationalen Datenbanken

Damit ich den Artikel in Zukunft wiederfinde, um darauf verweisen zu können: Franck Pachot beschäftigt sich in seinem jüngsten Artikel mit dem Mythos der langsamen Joins in relationalen Datenbanken. Die dahinter stehende Vorstellung, die in den Kreisen der reinen NoSQL Enthusiasten häufig geäußert wird, ist, dass Join-Operationen in relationalen Datenbanken langsam sein müssen, da hier große Datenmengen verknüpft werden müssen, was zu einer hohen Komplexität, CPU-Last etc. führen muss, die sich abhängig von der Größe der Tabellen erhöhen.

Franck Pachot erklärt, dass diese Annahme übersieht, dass alle relationalen Datenbanken über B*Tree Indizes verfügen und die den key-values Stores vergleichbaren kleineren Suchoperationen in aller Regel mit Nested Loop Joins abbilden, wodurch die Größe der Objekte keine Rolle spielt. Er zeigt auch - am Beispiel von Postgres -, dass der Optimizer des RDBMS hier nicht als Black Box agiert (auch das ein üblicher Einwand gegen die relationalen Datenbanken), sondern Pläne über Explain Kommandos darstellen kann.

Sehr schön am Artikel ist auch, dass er sehr sachlich bleibt - und Alex DeBrie, der Autor des Artikels aus der NoSQL-Gemeinde, auf den Franck Pachot sich bezieht (und in dem sich der Abschnitt "Why relational databases don't scale" findet) in Twitter den schönen Satz "really great post by Franck Pachot, and I'm grateful for the conversation" schreiben kann. Wobei ich insgesamt den Eindruck habe, dass heute solche technischen Auseinandersetzungen in vielen Fällen etwas zivilisierter ausgetragen werden, als das in früheren Zeiten (sagen wir: vor 10, 15 Jahren) der Fall war.

Freitag, Mai 15, 2020

Plan Prädikate in AWR mit Oracle 20

Nur damit der Blog nicht völlig einschläft: Franck Pachot weist in seinem Blog darauf hin, dass Oracle 20 nun endlich die "predicate information" in AWR abspeichert, wie das seit Generationen gewünscht worden war - insbesondere vom Herrn Pachot, der dazu im Jahr des Herrn 2014 eine Idee im Oracle Developer Forum untergebracht hatte; wie ich sehe, sind die von mir dort vorgeschlagenen Verbesserungen alle noch nicht umgesetzt... Leider komme ich heute kaum noch ins Forum und schon gar nicht, um dort etwas zu schreiben. Oder auch: Tempora mutantur et nos mutamur in illis.

Montag, April 06, 2020

dense_rank zur Bestimmung von Maximalwerten pro Gruppe

Wie man nicht nur am Alter des letzten Eintrags, sondern auch am Titel des vorliegenden erkennen kann, komme ich derzeit selten dazu, mir Gedanken über technische Fragen und ihre schriftliche Zusammenfassung zu machen: und da werden die Titel dann offenbar sperriger...

Jonathan Lewis behandelt in seinem Blog eine Einsatzmöglichkeit der analytischen dense_rank Funktion, der mir gelegentlich auch schon geholfen hätte. Die Fragestellung ist dabei die der jüngsten Bestellung, die in einer Transaktionstabelle für jeden einzelnen Kunden vorliegt. Dazu werden diverse Varianten mit korrelierten und nicht korrelierten Subqueries überprüft, die jeweils das Problem mit sich bringen, dass die Transaktionstabelle zweimal gelesen werden muss. Vermeidbar ist das mit analytischen Funktionen, wobei zunächst eine inline-View mit einem analytischen MAX verwendet wird, bei dem dann in der rahmenden Query eine Filterung auf diese maximalen Werte erfolgt - und das wäre wahrscheinlich auch die Lösung gewesen, die mir in diesem Fall eingefallen wäre. Noch effizienter ist hier aber ein "max (...) keep (dense_rank last order by ...)". Voraussetzung ist dabei, dass es möglich ist, einen unique constraint über die Gruppierungsspalte und die order by Spalte zu legen, da es sonst zu falschen Ergebnissen kommen kann. Grundsätzlich kann die Strategie aber sehr nützlich sein, wenn die Zahl der Datensätze in der Transaktionstabelle im Verhältnis zur Anzahl der (distinkten) Werte in der gruppierten Ergebnismenge der Query hoch ist.

Wenn ich das noch mal überfliege, wird mir klar, dass nicht nur die Titel unter der selteneren Übung beim Zusammenfassen leiden...

Montag, Februar 10, 2020

MVCC Verhalten bei Oracle, Postgres und dem SQL Server

Franck Pachot zeigt in einem aktuellen Artikel, dass der SQL Server in Sachen MVCC immer noch deutlich anders agiert als Oracle und Postgres. Zur Erinnerung: Microsofts Interpretation des Isolation Levels "read committed" hat in der Vergangenheit immer wieder zu Diskussionen geführt, da hier nicht wie bei Postgres und Oracle ein für den Startzeitpunkt der Query konsistenter Zustand gelesen wird, sondern zum Zeitpunkt des Zugriffs abgerufen wird, was per commit festgeschrieben wurde. Dadurch kann eine lang laufende Query inkonsistente Ergebnisse liefern. Es gibt zwar immer wieder Interpreten, die das für eine sehr plausible Implementierungs-Variante halten, aber zu diesen gehöre ich nicht. Noch problematischer ist wahrscheinlich, dass sich trotzdem im SQL Server mit diesem Isolation Level immer wieder Situationen ergaben, in denen writer durch reader und reader durch writer blockiert wurden, was dazu führte, dass manche SQL Server Applikation auf das Isolation Level "read uncommitted" (aka dirty read) umstellte, was zwar Sperren vermied, aber die Frage der Konsistenz der Daten noch weiter in den Hintergrund drängte. Aus diesem Grund hat Microsoft schließlich das Isolation Level "read committed snapshot" eingeführt, das die Bereitstellung lesekonsistenter Ergebnisse sicherstellt (und aus meiner Sicht stärker der Postgres-Implementierung von MVCC ähnelt als der von Oracle, da hier explizit historische Daten vorgehalten werden, so lange sie von Transaktionen benötigt werden).

Frank Pachot zeigt nun in seinem Artikel, dass selbst der Isolation Level "read committed snapshot" stärkere Locking-Effekte hervorrufen kann als die MVCC-Implementierungen von Oracle und Postgres. Vermeiden kann man diese Effekte nur durch ergänzende Indizes (und unter Umständen auch Hints). Die Ursache dafür fasst der Herr Pachot so schön zusammen, dass ich sie hier einfach zitiere:
A DML statement has two phases: one to find the rows and the second one to modify them. A DELETE or UPDATE in Oracle and Postgres runs the first in snapshot mode: non-blocking MVCC. The second must, of course, modify the current version. This is a very complex mechanism because it may require a retry (restart) when the current version does not match the consistent snapshot that was used for filtering. Both PostgreSQL and Oracle can ensure this write consistency without the need to block the reads. SQL Server has implemented MVCC more recently and provides non-blocking reads only for the SELECT reads. But a read can still be in blocking situation for the query phase of an update statement.
Mir zumindest war bisher nicht aufgefallen, dass "read committed snapshot" hier weiterhin ein anderes Verhalten zeigt, als "read committed" in Postgres und Oracle.

Freitag, Januar 17, 2020

Falsche Ergebnisse bei Group By Elimination in Oracle 12.2

Jonathan Lewis weist in seinem Blog auf einen Fehler bei der in Oracle 12c eingeführten Group By Elimination hin. Diese dient eigentlich dazu, überflüssige Sortierungen zu vermeiden, wenn ein eindeutiger Schlüssel sicherstellt, dass die Gruppierung nicht erforderlich ist. Leider existiert in 12.2 ein Bug, der dazu führt, dass etwa für ein to_char(business_date, 'YYYY') die Group By Elimination wirksam wird, da die Spalte "business_date" der PK der Tabelle ist. Vom Ergebnis her wäre hier aber eine Gruppierung erforderlich, da die to_char Funktion Duplikate liefert. Letztlich liefert die Operation somit falsche Ergbenisse. Für to_char ist der Bug in Oracle 18 behoben, aber die Funktion extract liefert weiterhin falsche Ergebnisse.

Freitag, Dezember 20, 2019

Caching von PL/SQL Funktionsaufrufen

Mohamed Houri zeigt in seinem Blog einen nützlichen Trick: er stellt den Fall einer Query vor, in der ein Wert mit dem Ergebnis eines Funktionsaufrufs verglichen wird:
a.xy_bat_id = f_get_id('BJOBD176')
Dieser Zugriff ruft beim Abrufen von 18605 Datensätzen aus der zugehörigen Tabelle 18605 recursive calls hervor - und darüber hinaus sehr viele consistent gets. Eine Untersuchung mit SQL Trace zeigt, dass fast die gesamte Laufzeit auf diesen wiederholt ausgeführten Funktionsaufruf entfällt. Offenbar wird hier für jeden Datensatz rekursiv die vollständige Query ausgeführt, die in der Funktion gekapselt ist. Um das Verhalten zu ändern, genügt es, den Aufruf in ein "select from dual" zu integrieren, also:
a.xy_bat_id = (select f_get_id('BJOBD176') from dual)
Damit wird ein "scalar subquery caching" hervorgerufen: dadurch dass die Funktion für den gegebenen Eingabewert im Rahmen der gleichen Query immer den gleichen Wert zurückgeben muss, kann der Optimizer das Ergebnis durch einmalige Ausführung bestimmen und für alle folgenden Datensätze wiederverwenden. Dieser Trick ist nicht neu - Mohamed verweist in diesem Zusammenhang auf Tom Kyte -, aber unter den entsprechenden Umständen kann er extrem nützlich sein.

Donnerstag, November 28, 2019

B*Tree Index Optimierungen in Postgres 12

Laurenz Albe erläutert in seinem Artikel im Cybertec Blog ein paar interessante Optimierungen für B*Tree Indizes, die mit Postgres 12 eingeführt wurden. Ein erster Punkt ist, dass Indizes, die als non-unique definiert sind, in Postgres 12 deutlich kompakter erstellt werden. Ursache dafür ist, dass die TID (also die tuple Id, sprich: die physikalische Satzadresse) in den Schlüssel aufgenommen wurde, was eine bessere interne Sortierung der Einträge mit sich bringt und dafür sorgt, dass page splits bei der Erweiterung des Index seltener als 50:50 Splits in der Mitte der Struktur und häufiger als 90:10 Splits am rechten Ende der Index-Struktur erfolgen. Eine weitere Verbesserung betrifft auch die eindeutigen Indizes: die interne Speicherung in den Index Blocks wurde durch eine komprimierte Ablage der Informationen optimiert, was eine Reduzierung der Anzahl der Ebenen in der Index-Struktur mit sich bringt und damit die Zahl der Lesezugriffe beim Zugriff auf einen einzelnen Index-Eintrag reduzieren kann. Die Änderung der internen Index-Struktur ist als Version 4 der B*Tree Index-Implementierung definiert und bedeutet auch, dass Indizes nach einer Migration via pg_upgrade neu aufgebaut werden müssen, um optimiert abgelegt werden zu können. Neben diesen beiden detailliert ausgeführten Verbesserungen werden noch folgende Änderungen erwähnt, die Postgres 12 mit sich bringt:
  • Reduzierung des locking overhead bei Inserts
  • Einführung einer "redindex concurrently" Option (was aus meiner Sicht ein extrem nützliches Feature ist)
  • Performance-Verbesserungen für index-only scans bei Indizes mit vielen Attributen
  • Ergänzung einer dictionary view pg_stat_progress_create_index, mit der sich der Fortschritt beim Index-(Neu-)Aufbau überprüfen lässt
Insgesamt sind das diverse hochinteressante Features, die Postgres 12 interessant machen können.