Eine kurze Geschichte über die "schwere" Anfrage und die elegante Lösung des Problems
Vor kurzem, nachts, weckten uns Warnungen: Es gab nicht genügend Speicherplatz. Wir haben schnell herausgefunden, dass das Problem bei ETL-Aufgaben liegt.
Die ETL-Aufgabe wurde in einer Tabelle ausgeführt, in der Binärdatensätze und Speicherauszüge gespeichert sind. Diese Aufgabe bestand jede Nacht darin, doppelte Speicherauszüge zu entfernen und Speicherplatz freizugeben.
Um nach doppelten Dumps zu suchen, haben wir diese Abfrage verwendet:
id, MIN(id) OVER (PARTITION BY blob ORDER BY id) FROM dumps
Die Abfrage kombiniert dieselben Speicherauszüge durch das BLOB-Feld. Mit der Fensterfunktion erhalten wir die Kennung des ersten Auftretens jedes Speicherauszugs. Mit dieser Anfrage löschen wir dann alle doppelten Dumps.
Die Anforderung wurde einige Zeit ausgeführt und hat, wie aus den Protokollen hervorgeht, viel Speicherplatz beansprucht. Die Grafik zeigt, wie er jede Nacht freien Speicherplatz erzielte:
Mit der Zeit benötigte die Anforderung mehr Speicher, und die Fehler vertieften sich. Und als wir auf den Ausführungsplan blickten, sahen wir sofort, wohin alles geht:
Buffers: shared hit=3916, temp read=3807 written=3816 -> Sort (cost=69547.50..70790.83 rows=497332 width=36) (actual time=107.607..127.485 rows=39160) Sort Key: blob, id Sort Method: external merge Disk: 30456kB Buffers: shared hit=3916, temp read=3807 written=3816 -> Seq Scan on dumps (cost=0..8889.32 rows=497332 width=36) (actual time=0.022..8.747 rows=39160) Buffers: shared hit=3916 Execution time: 159.960 ms
Das Sortieren nimmt viel Speicherplatz in Anspruch. In Bezug auf die Ausführung erfordert das Sortieren ungefähr 30 MB Speicher aus einem Testdatensatz.
Warum so?
PostgreSQL reserviert Speicher zum Hashing und Sortieren. Die Speichermenge wird durch den Parameter work_mem gesteuert. Die Standardgröße von work_mem beträgt 4 MB. Wenn mehr als 4 MB zum Hashing oder Sortieren benötigt werden, belegt PostgreSQL vorübergehend Speicherplatz.
Unsere Abfrage belegt deutlich mehr als 4 MB, sodass die Datenbank so viel Speicher benötigt. Wir haben uns entschieden: Wir werden uns nicht beeilen und haben den Parameter nicht erhöht und den Speicher nicht erweitert. Es ist besser, nach einer anderen Möglichkeit zu suchen, um den Speicher für die Sortierung zu kürzen .
Wirtschaftliche Sortierung
„Wie viel Sortierung verbraucht, hängt von der Größe des Datensatzes und des Sortierschlüssels ab. Sie können den Datensatz nicht reduzieren, aber die Größe des Schlüssels ist möglich .
Für den Referenzpunkt nehmen wir die durchschnittliche Größe des Sortierschlüssels:
avg ---------- 780
Jeder Schlüssel wiegt 780. Um den Binärschlüssel zu reduzieren, kann er gehasht werden. In PostgreSQL gibt es dafür md5 (ja, keine Sicherheit, aber für unseren Zweck wird es reichen). Mal sehen, wie viel der mit md5 gehashte BLOB wiegt:
avg ----------- 36
Die Größe des durch md5 gehashten Schlüssels beträgt 36 Bytes. Ein Hash-Schlüssel wiegt nur 4% der ursprünglichen Option .
Als Nächstes haben wir die ursprüngliche Anforderung mit einem Hash-Schlüssel gestartet:
id, MIN(id) OVER ( PARTITION BY md5(array_to_string(blob, '') ) ORDER BY id) FROM dumps;
Und der Umsetzungsplan:
Buffers: shared hit=3916 -> Sort (cost=7490.74..7588.64 rows=39160 width=36) (actual time=349.383..353.045 rows=39160) Sort Key: (md5(array_to_string(blob, ''::text))), id Sort Method: quicksort Memory: 4005kB Buffers: shared hit=3916 -> Seq Scan on dumps (cost=0..4503.40 rows=39160 width=36) (actual time=0.055..292.070 rows=39160) Buffers: shared hit=3916 Execution time: 374.125 ms
Mit einem Hash-Schlüssel verbraucht die Anforderung nur 4 zusätzliche Megabyte, dh etwas mehr als 10% der vorherigen 30 MB. Die Größe des Sortierschlüssels hat also großen Einfluss darauf, wie viel Speicher die Sortierung benötigt .
Weiter mehr
In diesem Beispiel haben wir das BLOB mit md5 . Mit MD5 erstellte Hashes müssen 16 Byte wiegen. Und wir haben mehr:
md5_size ------------- 32
Unser Hash war genau doppelt so groß, weil md5 einen Hash in Form von hexadezimalem Text erzeugt.
In PostgreSQL können Sie MD5 zum Hashing mit der Erweiterung pgcrypto . pgcrypto erstellt MD5 vom Typ bytea (binär) :
select pg_column_size( digest('foo', 'md5') ) as crypto_md5_size crypto_md5_size --------------- 20
Der Hash ist immer noch 4 Bytes mehr als er sein sollte. Es ist nur so, dass der bytea Typ diese 4 Bytes verwendet, um die Länge des Werts zu speichern, aber wir werden es nicht so lassen.
Es stellt sich heraus, dass der uuid Typ in PostgreSQL genau 16 Bytes wiegt und jeden beliebigen Wert unterstützt, sodass wir die verbleibenden vier Bytes entfernen:
uuid_size --------------- 16
Das ist alles. 32 Bytes mit md5 mit md5 zu 16 Bytes.
Ich habe die Auswirkungen der Änderung anhand eines größeren Datensatzes überprüft. Die Daten selbst können nicht angezeigt werden, aber ich werde die Ergebnisse teilen:
Wie Sie der Tabelle entnehmen können, wog die ursprüngliche problematische Anfrage 300 MB (und weckte uns mitten in der Nacht). Mit dem uuid Schlüssel dauerte das Sortieren nur 7 MB.
Follow-up-Überlegungen
Eine Anforderung mit einem Hash-Speicher-Sortierschlüssel verbraucht weniger, funktioniert jedoch viel langsamer:
Hashing benötigt mehr CPU, daher ist eine Anforderung mit einem Hash langsamer. Wir haben jedoch versucht, das Problem mit dem Speicherplatz zu lösen. Außerdem wird die Aufgabe nachts ausgeführt, sodass die Zeit kein Problem darstellt. Wir haben Kompromisse geschlossen, um Speicherplatz zu sparen.
Dies ist ein gutes Beispiel dafür, dass Sie nicht immer versuchen müssen, Datenbankabfragen zu beschleunigen . Es ist besser, das zu verwenden, was ausgeglichen ist, und das Maximum aus einem Minimum an Ressourcen herauszuholen.