PostgreSQL-Performance

Ihr solltet nicht mit MVCC – Multiversion Concurrency Control beginnen, wenn ihr eure PostgreSQL-Datenbank optimieren wollt: viele Verbesserungen können sehr viel einfacher vorgenommen werden da weder Transaktionslogs noch große Linux Kernel Page Sizes verantwortlich sein dürften. Üblicherweise beginnen wir mit zwei Metriken, die sehr gut die Performance eurer Datenbanken anzeigen können:

Cache- und Index-Trefferquote

Cache-Trefferquote (engl.: cache hit ratio)

Prozentsatz der Zeit, in der die Daten aus dem Arbeitsspeicher statt aus dem Festplattenspeicher bereitgestellt werden können. Für eine Web-Anweundung mit vielen kleinen Anfragen empfehle ich ca. 99%.

SELECT
  'index hit rate' AS name,
  (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio
FROM pg_statio_user_indexes
UNION ALL
SELECT
 'table hit rate' AS name,
  sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio
FROM pg_statio_user_tables;

Falls die Cache-Trefferquote zu gering sein sollte, könnt ihr einfach den Arbeitsspeicher erhöhen.

Index-Trefferquote (engl.: index hit ratio)

Häufigkeit der Verwendung der Indizes.

 SELECT relname,
   CASE idx_scan
     WHEN 0 THEN 'Insufficient data'
     ELSE (100 * idx_scan / (seq_scan + idx_scan))::text
   END percent_of_times_index_used,
   n_live_tup rows_in_table
 FROM
   pg_stat_user_tables
 ORDER BY
   n_live_tup DESC;
        relname        | percent_of_times_index_used | rows_in_table
-----------------------+-----------------------------+---------------
 account               | 11                          |          5409
 activity              | 69                          |         58276
 application           | 93                          |          5345
 …

Üblicherweise sollten bei uns nicht mehr als 10.000 Datensätze in einer Tabelle und der Prozentsatz des verwendeten Index größer als 90% sein.

In unserem Beispiel sehen wir, dass der account-Tabelle relevante Indizes fehlen, da nur in 11% der Anfragen ein Index genutzt wird. Auch der activity-Tabelle fehlen einige passende Indizes, sie hat jedoch auch sehr viele Datensätze, sodass es sinnvoll sein dürfte, sie in mehrere Tabellen aufzuteilen.

Nicht-verwendete Indizes bereinigen

Nicht-verwendete Indizes führen beim Schreiben der Datensätze zu einem langsameren Durchsatz ohne dass dadurch Abfragen schneller würden.

SELECT
  schemaname || '.' || relname AS table,
  indexrelname AS index,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
  idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
pg_relation_size(i.indexrelid) DESC;

Indizes ohne Verwendung können einfach entfernt werden. Schwieriger wird die Entscheidung hingegen bei Indizes, die nur sehr selten verwendet werden: hier muss eine Abwägung zwischen der Schreib- und Abfragegeschwindigkeit getroffen werden.

Nicht-verwendete Daten bereinigen

Auch wenn PostgreSQL sehr vielfältige Daten aufnehmen kann, ist das jedoch nicht immer sinnvoll. Tabellen wie messages, logs und events haben eine gute Chance, den Großteil des Speichers zu beanspruchen ohne dass die Datenbankanwendung unmittelbar davon profitiert: wenn diese Daten vielmehr der Fehleranalyse dienen, sollten sie außerhalb der Datenbank gespeichert und regelmäßig rotiert werden.

Abfrageleistung mit pg_stat_statements analysieren

pg_stat_statements zeichnet Abfragen auf und führt eine Reihe von Statistiken dazu. So lassen wir uns in regelmäßigen Abständen anzeigen, welche Abfragen im Durchschnitt am langsamsten sind und welche das System am stärksten belasten:

SELECT
  (total_time / 1000 / 60) as total_minutes,
  (total_time/calls) as average_time,
  query
FROM pg_stat_statements
ORDER BY 1 DESC
LIMIT 50;
total_time        |     avg_time      |                           query
------------------+-------------------+------------------------------------------------------------
 295.761165833319 | 10.1374053278061  | SELECT id FROM account WHERE email LIKE ?
 219.138564283326 | 80.24530822355305 | SELECT * FROM account WHERE user_id = ? AND current = True
…

Üblich sollten Antwortzeiten von ~1ms und in wenigen Fällen ~4–5ms sein. Um mit der Performance-Optimierung zu beginnen, wägen wir meist zwischen der Gesamtzeit und der Durchschnittszeit ab, sodass wir in obigem Beispiel vermutlich mit der zweiten Zeile beginnen würden da wir hier die größeren Einsparmöglichkeiten sehen. Um eine genauere Vorstellung von der Abfrage zu erhalten, analysieren wir sie genauer mit:

EXPLAIN ANALYZE
SELECT *
FROM account
WHERE user_id = 123
  AND current = True
                                                                   QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4690.88..4690.88 rows=1 width=0) (actual time=519.288..519.289 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..4690.66 rows=433 width=0) (actual time=15.302..519.076 rows=213 loops=1)
         ->  Index Scan using idx_account_userid on account  (cost=0.00..232.52 rows=23 width=4) (actual time=10.143..62.822 rows=1 loops=8)
               Index Cond: (user_id = 123)
               Filter: current
               Rows Removed by Filter: 14
 Total runtime: 219.428 ms
(1 rows)

Wir sehen also, dass zwar ein Index verwendet wird, jedoch werden 15 verschiedene Zeilen daraus abgerufen von denen dann 14 wieder verworfen werden. Um dies zu optimieren, würden wir einen bedingten oder zusammengesetzten Index erstellen. Im ersten Fall müsste current = true erfüllt sein, im zweiten Fall würde ein Composite-Index mit beiden Werten erstellt werden. Ein bedingter Index ist in der Regel sinnvoller bei einem kleinen Satz von Werten, während der Composite-Index bei größeren Sätzen von Werten vorteilhafter ist. In unserem Beispiel dürfte klar ein bedingter Index sinnvoller sein. Diesen können wir erstellen mit:

CREATE INDEX CONCURRENTLY idx_account_userid_current ON account(user_id) WHERE current = True;

Nun müsste sich auch der Query-Plan verbessern:

EXPLAIN ANALYZE
SELECT *
FROM account
WHERE user_id = 123
  AND current = True

                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4690.88..4690.88 rows=1 width=0) (actual time=519.288..519.289 rows=1 loops=1)
     ->  Index Scan using idx_account_userid_current on account  (cost=0.00..232.52 rows=23 width=4) (actual time=10.143..62.822 rows=1 loops=8)
           Index Cond: ((user_id = 123) AND (current = True))
 Total runtime: .728 ms
(1 rows)