Entwickleralltag bei uns: Livehacking DB Performance

08.07.2019

Jürgen hat nach Hilfe gerufen und laut in den Wald geschrien. Durch bilateralen Austausch saßen dann am Ende zwei Klugscheißer Experten neben Jürgen. Durch eine Datenanalyse auf der Produktivdatenbank war die Ursache für die furchtbar langsame Abfrage schnell gefunden. Jürgen hat das 1×1 der DB Analyse nicht beachtet und keinen Index angelegt. Jürgen beruft sich dabei darauf, dass KEIN Full Table Scan beim Ausführungsplan zu sehen war. Antwort von Klugscheißer 1: „Schau mal ob Seq Scan drin steht. Das ist der verschlüsselte Code bei Postgres für Full Table Scan.“

Ergebnis von „EXPLAIN ANALYZE“:

explain analyze
select id, name, details, creationdate
from ticket 
where state = 'PENDING'
and category = 'STORY'
order by creationdate desc
;
---------------------------------------------------------------------------------------------
Sort  (cost=4180322.11..4180322.11 rows=1 width=156) (actual time=83977.611..83977.696 rows=54 loops=1)
  Sort Key: creationdate DESC
  Sort Method: quicksort  Memory: 39kB
  ->  Seq Scan on ticket  (cost=0.00..4180322.10 rows=1 width=156) (actual time=15074.457..83977.313 rows=54 loops=1)
        Filter: ((state = 'PENDING'::text) AND (category = 'STORY'::text))
        Rows Removed by Filter: 30745002
Planning time: 0.272 ms
Execution time: 83977.821 ms

Nach gemeinsamer Überprüfung die Bestätigung: Es fehlte ein Index auf die Spalten der WHERE-Bedingung.
Daraufhin wurde Jürgen von Klugscheißer 2 gemobbt, dass er doch einen Index auf diese Spalte(n) anlegen sollte.
Bevor zur Tat geschritten wurde wollte Klugscheißer 1 jedoch noch überprüfen, ob der Index für die Abfrage überhaupt sinnvoll ist, und wenn ja, für welche Spalte der WHERE-Bedingung. Wenn bpsw. 80% der Tabelleneinträge auf die WHERE-Bedingung zutrifft, wird weiterhin direkt auf die Tabelle gegangen statt den Umweg über den Index zu nehmen.

select state, count(*)
from ticket
group by state
-----------------------------------
 
state     |count  |
----------|-------|
DONE      |3733049|
OPEN      |2759168|
PENDING   |    196|
BLOCKED   |     27|

Ergebnis: Nur 0,003% der Daten besitzen den Status ‚PENDING‘. Somit würde ein Index auf diese Spalte herangezogen werden.
Das haben wir dann auch ganz ordentlich auf der Test-Umgebung getestet und haben gesehen, dass tatsächlich der Index herangezogen wird. Klugscheißer 1 wollte dann voller Tatendrang den Index auf der Produktivumgebung anlegen (bei 40 Mio. Daten). Daraufhin hatte Jürgen einen Geistesblitz und brachte die Frage auf, ob dies währenddessen die Tabelle sperren könnte … immerhin werden dort ständig INSERTs getätigt. Klugscheißer 2 meinte daraufhin: „Der tut doch nichts, der liest doch nur ….“

Google brachte dabei folgendes Ergebnis:
„Creating an index can interfere with regular operation of a database. Normally PostgreSQL locks the table to be indexed against writes and performs the entire index build with a single scan of the table.“ [1]

Jürgen wurde bei dem Wort „Normally“ hellhörig: „Da muss es doch eine Alternative für geben.“ Diese Alternative bietet Postgres bei vielen Statements (Index, View, etc.) via dem Zusatzwort „CONCURRENTLY“:
„PostgreSQL supports building indexes without locking out writes. This method is invoked by specifying the CONCURRENTLY option of CREATE INDEX. When this option is used, PostgreSQL must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate. Thus this method requires more total work than a standard index build and takes significantly longer to complete.“ [1]

CREATE INDEX CONCURRENTLY ticket_state_idx ON ticket USING btree (state);

Der „CREATE INDEX …“ ist nach 123 Minuten und 44 Sekunden erfolgreich durchgelaufen. Das Select-Statement dauert jetzt noch 214ms (zuvor lag die Zeit bei knapp einer Minute). Damit war das wohlverdiente Wochenende am Freitagnachmittag gerettet. So erfolgreich kann Teamwork sein :-)

Viele Grüße von


Quellen:

  1. https://www.postgresql.org/docs/current/sql-createindex.html

 

Zurück zur Übersicht

Ein Kommentar zur “Entwickleralltag bei uns: Livehacking DB Performance

Kommentar verfassen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

*Pflichtfelder

*