Benchmarks Archiv - credativ®

Einführung

Das Ausführen von ANALYZE (entweder explizit oder über Auto-Analyze) ist sehr wichtig, um aktuelle Datenstatistiken für den Postgres-Query-Planer zu haben. Insbesondere nach In-Place-Upgrades über muss ausgeführt werden, um überhaupt Abfragestatistiken zu erhalten. Da ANALYZE nur Teile der Blöcke in einer Tabelle abtastet, ähnelt das I/O-Muster eher einem Direktzugriff als einem sequenziellen Lesen. Version 14 von Postgres hat die Möglichkeit erhalten, Prefetching zu verwenden (falls verfügbar, was aber unter Linux der Fall ist), um dem Betriebssystemkernel mitzuteilen, welche Blöcke als Nächstes betrachtet werden. Dies wird über den Konfigurationsparameter maintenenance_io_concurrency gesteuert, der standardmäßig auf 10 gesetzt ist (im Gegensatz zu effective_io_concurrency, der standardmäßig auf 1 gesetzt ist).

Benchmark

Um die Änderungen zwischen Version 13 und 14 zu testen und zu demonstrieren, haben wir einige kurze Benchmarks mit den aktuellen Wartungsversionen (13.16 und 14.13) auf Debian 12 mit Paketen von https://apt.postgresql.org durchgeführt. Hardwareseitig wurde ein ThinkPad T14s Gen 3 mit einer Intel i7-1280P CPU mit 20 Kernen und 32 GB RAM verwendet. Die Basis ist eine pgbench-Datenbank, die mit einem Skalierungsfaktor von 1000 initialisiert wurde:

    $ pgbench -i -I dtg -s 1000 -d pgbench

Dadurch werden 100 Millionen Zeilen erstellt, was zu einer Datenbankgröße von etwa 15 GB führt. Um etwas mehr Arbeit zu geben, erhöhen wir von den standardmäßigen 100 auf den gleichen Wert wie den pgbench-Skalierungsfaktor (d. h. 1000). Dies führt dazu, dass etwa 20 % aller Blöcke scannt. Anschließend analysieren wir die pgbench-Haupttabelle, pgbench_accounts:

    $ vacuumdb -Z -v -d pgbench -t pgbench_accounts
    INFO:  analyzing "public.pgbench_accounts"
    INFO:  "pgbench_accounts": scanned 300000 of 1639345 pages,
           containing 18300000 live rows and 0 dead rows;
           300000 rows in sample, 100000045 estimated total rows

Zwischen den Durchläufen wird der Dateisystem-Seitencache über echo 3 | sudo tee /proc/sys/vm/drop_caches gelöscht und alle Durchläufe werden dreimal wiederholt. Die folgende Tabelle listet die Laufzeiten (in Sekunden) des obigen vacuumdb-Befehls für verschiedene Einstellungen von maintenance_io_concurrency auf:

Analyse

Zwei Dinge gehen aus diesen Zahlen deutlich hervor: Erstens ändern sich die Laufzeiten für Version 13 nicht, der Wert von maintenance_io_concurrency hat für diese Version keine Auswirkung. Zweitens, sobald das Prefetching für Version 14 einsetzt ( ist 5 oder mehr), wird um ein Vielfaches schneller, bis zu einem Faktor von 6-7x. Der Standardwert von von 10 ist bereits 3-4x schneller, und Werte größer als 50 zeigen nur geringfügige weitere Verbesserungen, zumindest für diesen Benchmark auf dieser Hardware. Bemerkenswert ist auch, dass die Laufzeiten, wenn Prefetching deaktiviert ist (maintenance_io_concurrency=0) oder nur auf 1 gesetzt ist, schlechter sind als bei Version 13, aber da der Standardwert für maintenance_io_concurrency 10 ist, sollte dies in der Praxis niemanden betreffen.

Fazit

Das Aktivieren von Prefetching für ANALYZE in Version 14 von PostgreSQL hat die Statistikabtastung erheblich beschleunigt. Der Standardwert von 10 für ist bereits recht gut, aber wir empfehlen, ihn auf 20-50 (oder höher) zu erhöhen, falls hochleistungsfähiger lokaler NVME-Speicher verwendet wird. In einem zukünftigen kurzen Benchmark planen wir, die -Leistung für die Hauptversionen seit 14 zu vergleichen. Insbesondere die kommende Version 17 verspricht aufgrund der neuen Streaming-I/O-Schnittstelle weitere Verbesserungen für ANALYZE.

Übersicht

Was ist das Hauptproblem bei der Aufblähung von Systemtabellen?

Beispiel für eine Berichtstabelle in einer Buchhaltungssoftware

CREATE TEMP TABLE pivot_temp_table (
  id serial PRIMARY KEY,
  inserted_at timestamp DEFAULT current_timestamp,
  client_id INTEGER,
  name text NOT NULL,
  address text NOT NULL,
  loyalty_program BOOLEAN DEFAULT false,
  loyalty_program_start TIMESTAMP,
  orders_202301_count_of_orders INTEGER DEFAULT 0,
  orders_202301_total_price NUMERIC DEFAULT 0,
  ...
  orders_202312_count_of_orders INTEGER DEFAULT 0,
  orders_202312_total_price NUMERIC DEFAULT 0);

CREATE INDEX pivot_temp_table_idx1 ON pivot_temp_table (client_id);
CREATE INDEX pivot_temp_table_idx2 ON pivot_temp_table (name);
CREATE INDEX pivot_temp_table_idx3 ON pivot_temp_table (loyalty_program);
CREATE INDEX pivot_temp_table_idx4 ON pivot_temp_table (loyalty_program_start);

Tabelle pg_attribute

SELECT
  c.relname as table_name,
  o.rolname as table_owner,
  c.relkind as table_type,
  a.attname as column_name,
  a.attnum as column_number,
  a.atttypid::regtype as column_data_type,
  pg_get_expr(adbin, adrelid) as sql_command
FROM pg_attrdef ad
JOIN pg_attribute a ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum
JOIN pg_class c ON c.oid = ad.adrelid
JOIN pg_authid o ON o.oid = c.relowner
WHERE c.relname = 'pivot_temp_table'
ORDER BY table_name, column_number;

  table_name | table_owner | table_type | column_name | column_number | column_data_type | sql_command
------------------+-------------+------------+-------------------------------+---------------+-----------------------------+----------------------------------------------
  pivot_temp_table | postgres | r | id | 1 | integer | nextval('pivot_temp_table_id_seq'::regclass)
  pivot_temp_table | postgres | r | inserted_at | 2 | timestamp without time zone | CURRENT_TIMESTAMP
  pivot_temp_table | postgres | r | loyalty_program | 6 | boolean | false
  pivot_temp_table | postgres | r | orders_202301_count_of_orders | 8 | integer | 0
  pivot_temp_table | postgres | r | orders_202301_total_price | 9 | numeric | 0
-->  bis zur Spalte "orders_202312_total_price"

Tabelle pg_class

Zusammenfassung dieses Beispiels

Beispiel für einen Online-Shop

Analyse des Datenverkehrs

WITH tablenames AS (SELECT tablename FROM (VALUES('pg_attribute'),('pg_attrdef'),('pg_class')) as t(tablename))
SELECT
  tablename,
  now() as checked_at,
  pg_relation_size(tablename) as relation_size,
  pg_relation_size(tablename) / (8*1024) as relation_pages,
  a.*,
  s.*
FROM tablenames t
JOIN LATERAL (SELECT * FROM pgstattuple(t.tablename)) s ON true
JOIN LATERAL (SELECT last_autovacuum, last_vacuum, last_autoanalyze, last_analyze, n_live_tup, n_dead_tup
FROM pg_stat_all_tables WHERE relname = t.tablename) a ON true
ORDER BY tablename

  tablename | pg_attribute
  checked_at | 2024-02-18 10:46:34.348105+00
  relation_size | 44949504
  relation_pages | 5487
  last_autovacuum | 2024-02-16 20:07:15.7767+00
  last_vacuum | 2024-02-16 20:55:50.685706+00
  last_autoanalyze | 2024-02-16 20:07:15.798466+00
  last_analyze | 2024-02-17 22:05:43.19133+00
  n_live_tup | 3401
  n_dead_tup | 188221
  table_len | 44949504
  tuple_count | 3401
  tuple_len | 476732
  tuple_percent | 1.06
  dead_tuple_count | 107576
  dead_tuple_len | 15060640
  dead_tuple_percent| 33.51
  free_space | 28038420
  free_percent | 62.38

WITH pages AS (
  SELECT * FROM generate_series(0, (SELECT pg_relation_size('pg_attribute') / 8192) -1) as pagenum),
tuples_per_page AS (
  SELECT pagenum, nullif(sum((t_xmin is not null)::int), 0) as tuples_per_page
  FROM pages JOIN LATERAL (SELECT * FROM heap_page_items(get_raw_page('pg_attribute',pagenum))) a ON true
  GROUP BY pagenum)
SELECT
  count(*) as pages_total,
  min(tuples_per_page) as min_tuples_per_page,
  max(tuples_per_page) as max_tuples_per_page,
  round(avg(tuples_per_page),0) as avg_tuples_per_page,
  mode() within group (order by tuples_per_page) as mode_tuples_per_page
FROM tuples_per_page

  pages_total | 5487
  min_tuples_per_page | 1
  max_tuples_per_page | 55
  avg_tuples_per_page | 23
  mode_tuples_per_page | 28

Die PostgreSQL 2024Q1 Back-Branch-Releases 16.2, 15.6, 14.11, 13.14 und 12.18 wurden am 8. Februar 2024 veröffentlicht. Neben der Behebung eines Sicherheitsproblems (CVE-2024-0985) und der üblichen Fehler sind sie insofern einzigartig, als sie zwei Leistungsprobleme durch das Backporting von Korrekturen beheben, die bereits zuvor in den Master-Branch eingeführt wurden. In diesem Blogbeitrag beschreiben wir zwei kurze Benchmarks, die zeigen, wie sich die neuen Punkt-Releases verbessert haben. Die Benchmarks wurden auf einem ThinkPad T14s Gen 3 durchgeführt, das über eine Intel i7-1280P CPU mit 20 Kernen und 32 GB RAM verfügt.

Skalierbarkeitsverbesserungen bei hoher Konkurrenz

Die Leistungsverbesserungen in den 2024Q1 Punkt-Releases betreffen Verbesserungen der Locking-Skalierbarkeit bei hohen Client-Zahlen, d.h. wenn das System unter starker Konkurrenz steht. Benchmarks hatten gezeigt, dass die Leistung bei einem pgbench-Lauf mit mehr als 128 Clients dramatisch abnahm. Der ursprüngliche Commit zum Master (der anschließend mit Version 16 veröffentlicht wurde) stammt vom November 2022. Er wurde nun in die Back-Branches eingeführt, da Version 16 bereits getestet wurde und die Ergebnisse vielversprechend waren.

Der von uns verwendete Benchmark ist an diesen Beitrag des Patch-Autors angepasst und besteht aus einem engen pgbench-Lauf, der einfach SELECT txid_current() für jeweils fünf Sekunden bei steigender Client-Anzahl ausführt und die Transaktionen pro Sekunde misst:

$ cat /tmp/txid.sql
SELECT txid_current();
$ for c in 1 2 4 8 16 32 64 96 128 192 256 384 512 768 1024 1536;
> do echo -n "$c ";pgbench -n -M prepared -f /tmp/txid.sql -c$c -j$c -T5 2>&1|grep '^tps'|awk '{print $3}';
> done

Die folgende Grafik zeigt die durchschnittlichen Transaktionen pro Sekunde (tps) über 3 Läufe mit steigender Client-Anzahl (1-1536 Clients), unter Verwendung der Debian 12 Pakete für Version 15, wobei das 2023Q4-Release (15.5, Paket postgresql-15_15.5-0+deb12u1) mit dem 2024Q1-Release (15.6, Paket postgresql-15_15.6-0+deb12u1) verglichen wird:

Die tps-Werte sind im Wesentlichen bis zu 128 Clients gleich, während danach die Transaktionszahlen von 15.5 vom Spitzenwert von 650.000 um das 10-fache auf 65.000 sinken. Das neue 15.6-Release hält die Transaktionszahl wesentlich besser und erreicht immer noch etwa 300.000 tps bei 1536 Clients, was eine 4,5-fache Steigerung des 2024Q1-Releases im Vergleich zu zuvor darstellt.

Dieser Benchmark ist natürlich ein Best-Case, ein künstliches Szenario, aber er zeigt, dass das neueste Punkt-Release von Postgres die Skalierbarkeit bei stark umkämpften Locking-Szenarien dramatisch verbessern kann.

JIT-Speicherverbrauchsverbesserungen

JIT (Just-in-Time-Kompilierung mit LLVM) wurde in Version 11 von Postgres eingeführt und in Version 13 zum Standard gemacht. Seit langem ist bekannt, dass lang laufende PostgreSQL-Sitzungen, die wiederholt JIT-Abfragen ausführen, Speicherlecks aufweisen. Es gab mehrere Fehlerberichte dazu, darunter einige weitere im Debian-Bugtracker und wahrscheinlich auch anderswo.

Dies wurde als auf JIT-Inlining zurückzuführen diagnostiziert, und ein Workaround besteht darin, jit_inline_above_cost von dem Standardwert 500.000 auf -1 zu setzen. Dies deaktiviert jedoch das JIT-Inlining vollständig. Die 2024Q1 Back-Branch-Releases enthalten einen Backport einer Änderung, die in Version 17 enthalten sein wird: Nach jeweils 100 Abfragen werden die LLVM-Caches gelöscht und neu erstellt, wodurch das Speicherleck behoben wird.

Um zu zeigen, wie sich der Speicherverbrauch verbessert hat, verwenden wir den Testfall aus diesem Fehlerbericht. Der Benchmark wird wie folgt vorbereitet:

CREATE TABLE IF NOT EXISTS public.leak_test
(
   id integer NOT NULL,
   CONSTRAINT leak_test_pkey PRIMARY KEY (id)
);

INSERT INTO leak_test(id)
   SELECT id
   FROM generate_series(1,100000) id
ON CONFLICT DO NOTHING;

Anschließend wird die Prozess-ID des Backends notiert und die im Fehlerbericht erwähnte SQL-Abfrage 5000 Mal in einer Schleife ausgeführt:

=> SELECT pg_backend_pid();
 pg_backend_pid
----------------
         623404

=> DO $$DECLARE loop_cnt integer;
-> BEGIN
->   loop_cnt := 5000;
->   LOOP
->     PERFORM
->       id,
->       (SELECT count(*) FROM leak_test x WHERE x.id=l.id) as x_result,
->       (SELECT count(*) FROM leak_test y WHERE y.id=l.id) as y_result
->       /* Leaks memory around 80 kB on each query, but only if two sub-queries are used. */
->     FROM leak_test l;
->     loop_cnt := loop_cnt - 1;
->     EXIT WHEN loop_cnt = 0;
->   END LOOP;
-> END$$;

Dabei wird der Speicherverbrauch des Postgres-Backends über pidstat aufgezeichnet:

pidstat -r -hl -p 623404 2 | tee -a leak_test.log.15.6
Linux 6.1.0-18-amd64 (mbanck-lin-0.credativ.de)     15.02.2024  _x86_64_    (20 CPU)

# Time        UID       PID  minflt/s  majflt/s     VSZ     RSS   %MEM  Command
12:48:56      118    623404      0,00      0,00  381856   91504   0,28  postgres: 15/main: postgres postgres [local] SELECT
12:48:58      118    623404      0,00      0,00  381856   91504   0,28  postgres: 15/main: postgres postgres [local] SELECT
12:49:00      118    623404      0,00      0,00  381856   91504   0,28  postgres: 15/main: postgres postgres [local] SELECT
12:49:02      118    623404      0,00      0,00  381856   91504   0,28  postgres: 15/main: postgres postgres [local] SELECT
12:49:04      118    623404   7113,00      0,00  393632  109252   0,34  postgres: 15/main: postgres postgres [local] SELECT
12:49:06      118    623404  13219,00      0,00  394556  109508   0,34  postgres: 15/main: postgres postgres [local] SELECT
12:49:08      118    623404  14376,00      0,00  395384  108228   0,33  postgres: 15/main: postgres postgres [local] SELECT
[...]

Der Benchmark wird erneut für die Debian 12 Pakete 15.5 und 15.6 wiederholt (die beide gegen LLVM-14 gelinkt sind) und der RSS-Speicherverbrauch, wie von pidstat berichtet, wird gegen die Zeit aufgetragen:

Während der Speicherverbrauch der 15.5-Sitzung linear über die Zeit von 100 auf 600 MB ansteigt, bleibt er bei 15.6 mehr oder weniger konstant bei etwa 100 MB. Dies ist eine große Verbesserung, die JIT für größere Installationen mit lang laufenden Sitzungen, bei denen bisher die übliche Empfehlung darin bestand, JIT vollständig zu deaktivieren, wesentlich nutzbarer machen wird.

Fazit

Das 2024Q1 Patch-Release enthält wichtige Leistungsverbesserungen für die Lock-Skalierbarkeit und den JIT-Speicherverbrauch, die wir in diesem Blogbeitrag demonstriert haben. Darüber hinaus enthält das Patch-Release weitere wichtige Fehlerbehebungen und einen Sicherheitspatch für CVE-2024-0985. Dieses Sicherheitsproblem ist auf materialisierte Views beschränkt, und ein Administrator muss dazu verleitet werden, eine bösartige materialisierte View im Namen eines Angreifers neu zu erstellen. Es hat jedoch einige deutsche Presseberichterstattung erfahren, sodass einige unserer Kunden besonders darauf aufmerksam gemacht wurden und uns um Unterstützung bei ihren kleineren Upgrades baten. Im Allgemeinen sind Postgres-Patch-Releases risikoarm und unaufdringlich (einfach die aktualisierten Pakete installieren und die Postgres-Instanzen neu starten, falls das Paket dies nicht selbst getan hat), sodass sie immer so schnell wie möglich eingesetzt werden sollten.