Rechner für Datenbank-Speicher-Tuning

Wohin der Speicher einer Datenbank geht — und der Parameter, der sich still vervielfacht

Verwaltete Datenbanken kommen mit Speicherparametern, die aus der Instanzgröße abgeleitet sind, und die meisten davon sind vernünftige Standardwerte, die man in Ruhe lassen sollte. Einer nicht, denn er verhält sich anders: work_mem wird je Sortier- oder Hash-Knoten gewährt, seine reale Kosten multiplizieren sich also mit der Zahl der Verbindungen und der Zahl solcher Knoten je Abfrage. Dieses Werkzeug leitet die Standardparameter aus dem Instanzspeicher ab und zeigt dieses Produkt anschließend ausdrücklich.

So wird gerechnet

• Leitet shared_buffers, effective_cache_size und maintenance_work_mem aus dem Instanzspeicher ab, mit den Verhältnissen, die RDS selbst verwendet.
• Zeigt beim Wechsel der Engine die MySQL-Entsprechungen — innodb_buffer_pool_size und die Puffer je Verbindung.
• Multipliziert work_mem mit Ihrer Verbindungszahl und den Sortierungen je Abfrage — die Zahl, die tatsächlich hineinpassen muss.
• Warnt, wenn die Grundzuteilung plus dieser schlimmste Fall den Speicher der Instanz übersteigt.

PostgreSQL, wie RDS die Standardwerte setzt
  shared_buffers        = {DBInstanceClassMemory/32768}  -> 25% des RAM
  effective_cache_size  ~ 75% des RAM  (Planer-Hinweis, keine Zuteilung)
  maintenance_work_mem  ~ 5%, gedeckelt bei etwa 2 GiB

MySQL
  innodb_buffer_pool_size = {DBInstanceClassMemory*3/4} -> 75% des RAM

der, der sich vervielfacht
  schlimmster Fall = work_mem x Verbindungen x Sortierungen_je_Abfrage

Rechenbeispiel

Eine PostgreSQL-Instanz mit 32 GiB in der Standardkonfiguration — und was geschieht, wenn eine Einstellung steigt.

1. shared_buffers = 25% von 32 GiB = 8 GiB
2. effective_cache_size = 75% = 24 GiB, reserviert aber nichts — es sagt dem Planer nur, was das Betriebssystem vermutlich zwischenspeichert
3. maintenance_work_mem = 5% = 1,6 GiB, unter der 2-GiB-Deckelung
4. work_mem = 16 MiB, bei 200 Verbindungen und 2 Sortierknoten je Abfrage
5. schlimmster Fall = 16 MiB x 200 x 2 = 6,25 GiB
6. 8 GiB + 6,25 GiB = 14,25 GiB von 32 — komfortabel
7. nun work_mem auf 64 MiB anheben, um einen langsamen Report zu beschleunigen, und auf 1.000 Verbindungen mit 4 Sortierungen wachsen
8. schlimmster Fall = 64 MiB x 1.000 x 4 = 250 GiB auf einer 32-GiB-Instanz

Die letzten beiden Schritte sind der übliche Weg zum OOM-Kill. Nichts wirkte leichtsinnig: Jemand hat work_mem vervierfacht, um eine Abfrage zu beschleunigen, und der Verkehr ist gewachsen. Den Rest erledigte die Multiplikation — und zwar unter Last, nicht bei der Änderung des Parameters, weshalb es im Review selten auffällt.

Das Ergebnis lesen

• effective_cache_size teilt nichts zu. Es ist Ihre Schätzung für den Planer, wie viel Speicher das Betriebssystem vermutlich zum Zwischenspeichern Ihrer Daten nutzt, und es verändert, welche Pläne günstig aussehen. Hoch gesetzt verbraucht es keinen Speicher; falsch gesetzt erzeugt es schlechte Pläne.
• shared_buffers bei 25% ist der übliche Ausgangspunkt und der RDS-Standard. Anheben ist nicht kostenlos: PostgreSQL stützt sich auf den Seiten-Cache des Betriebssystems als zweite Ebene, Speicher in shared_buffers fehlt also dort. Sehr große Werte können alles langsamer machen.
• MySQL hat die umgekehrte Form. innodb_buffer_pool_size liegt standardmäßig bei 75%, weil InnoDB sich nicht wie PostgreSQL auf den OS-Cache stützt — es will die Daten im eigenen Pool. Übertragen Sie die PostgreSQL-Intuition nicht.
• Heben Sie work_mem für die Sitzung an statt für den Server, wenn nur ein Report es braucht. SET work_mem in dieser Verbindung liefert den gewünschten Plan, ohne allen anderen Verbindungen dieselbe Zuteilung zu gewähren.
• Eine Abfrage mit mehreren Sortier- oder Hash-Knoten kann work_mem mehrfach beanspruchen, innerhalb einer einzigen Verbindung. Parallele Worker vervielfachen es erneut. Den Regler für Sortierungen je Abfrage gibt es, weil die Annahme von einer der häufigste Grund für eine zu niedrige Schätzung ist.

Häufige Fragen

Ist der schlimmste Fall realistisch oder bloß Arithmetik?

Er ist eine Obergrenze, keine Prognose — jede Verbindung müsste gleichzeitig eine speicherhungrige Abfrage ausführen. Aber es ist die Obergrenze, auf die es ankommt, denn Datenbanken scheitern dort nicht sanft: Sie werden vom OOM-Killer beendet, und Lastspitzen sind genau die Momente, in denen alle Verbindungen gleichzeitig arbeiten.

Meine verwaltete Datenbank lässt shared_buffers nicht ändern. Warum?

Bei RDS und Azure wird es über eine Parametergruppe als Formel über DBInstanceClassMemory gesetzt, nicht als feste Zahl, und eine Änderung erfordert einen Neustart, weil das Shared-Memory-Segment beim Start zugeteilt wird. Cloud SQL sperrt manche Flags ganz. Der Standard passt meist; der Parameter, der Ihre Aufmerksamkeit verdient, ist work_mem.

Wie erkenne ich, wie viel work_mem eine Abfrage wirklich braucht?

Führen Sie EXPLAIN (ANALYZE, BUFFERS) aus und achten Sie auf externes Merge oder plattenbasiertes Hashing. Ist eine Sortierung auf die Platte ausgelagert, steht das im Plan samt Größe. Dieser Wert — etwas mehr als die Auslagerung — ist das, was die Abfrage möchte, und er sagt Ihnen, ob eine globale Anhebung lohnt oder die Abfrage repariert gehört.

Und die Puffer je Verbindung in MySQL?

Sie vervielfachen sich genauso. sort_buffer_size, join_buffer_size und read_rnd_buffer_size werden bei Bedarf je Verbindung zugeteilt, zusätzlich zum Buffer Pool. Es gilt dieselbe Disziplin: bescheiden halten und für die Sitzung anheben, die sie wirklich braucht.

Quellen

• PostgreSQL — Parameter zum Ressourcenverbrauch
• Amazon RDS — Best Practices
• MySQL — Konfiguration des InnoDB Buffer Pool