Zestien locks zouden voor iedereen genoeg moeten zijn

Dit is meestal ongevaarlijk. De locks zijn AccessShareLock, de zwakste lock die er is; ze conflicteren met bijna niets en zijn goedkoop om te verkrijgen. Maar "goedkoop" is niet "gratis". Bij een bepaalde combinatie van het aantal indexen en de query-snelheid verandert dit gedrag in een CPU-slurpend lock-contention probleem, zelfs bij queries die zo onschuldig zijn als een primary key-lookup van een enkele rij.

De planner vergrendelt alles waar hij naar kijkt

Wanneer PostgreSQL een query plant, neemt hij een AccessShareLock op elke relatie die de query zou kunnen gebruiken. Dat betekent de tabel en elke index op die tabel, omdat de planner elke index moet openen om te bepalen of deze nuttig is. Het maakt niet uit dat het uiteindelijke plan er precies één van gebruikt. De overweging vereist een lock.

Een tabel met een primary key en 20 secundaire indexen kost dus 22 relation locks per query: de tabel, de primary key index en de 20 andere indexen die de planner heeft onderzocht en verworpen. Bij elke enkele uitvoering.

Het 'fast path' en wanneer je er vanaf valt

Het verkrijgen van een lock betekent normaal gesproken een vermelding in de shared lock table, die in het gedeelde geheugen leeft en wordt beschermd door lightweight locks (verdeeld over 16 partities, elk met een eigen LWLock). Bij hoge query-snelheden op een machine met veel cores worden die 16 LWLocks op zichzelf een punt van contention.

PostgreSQL heeft hier een optimalisatie voor, toegevoegd in versie 9.2: fast-path locking. Elke backend krijgt een kleine private array waarin hij zwakke relation locks (AccessShareLock, RowShareLock, RowExclusiveLock) kan registreren zonder de shared lock table aan te raken. Tot en met PostgreSQL 17 heeft die array precies 16 slots.

Zestien. Tel je indexen.

Als een query meer dan 16 relation locks nodig heeft, gaat de overloop via de shared lock table, inclusief de LWLocks. Eén backend die dit doet is prima. Enkele honderden backends die dit duizenden keren per seconde doen, leiden tot een muur van LWLock:LockManager waits in pgstatactivity (voor PostgreSQL 13 heette dit lock_manager), waarbij de CPU maximaal wordt belast en de doorvoer keldert, terwijl de queries zelf triviaal simpel blijven. Als je op RDS of Aurora draait, is dit het LWLock:LockManager wait-event dat Performance Insights graag in alarmerende bruintinten aan je laat zien.

De nadelige eigenschap van deze foutmodus is dat hij zich concentreert op precies de verkeerde tabellen. De tabellen met te veel indexen zijn je kernstabellen, de tabellen die elke query aanraakt. Hierdoor betaalt elke query de tol, en de contention schaalt mee met je totale query-snelheid. Bij gepartitioneerde tabellen gebeurt dit nog sneller, aangezien de planner mogelijk elke partitie en de indexen van elke partitie moet vergrendelen.

Het probleem in de praktijk

Dit is eenvoudig aan te tonen. Maak een users tabel met een primary key en twintig single-column indexen (ik heb ergere gevallen gezien in productie, en jij waarschijnlijk ook), en voer de meest saaie query uit die je kunt bedenken:

BEGIN;
SELECT * FROM users WHERE id = 42;

SELECT fastpath, count(*)
  FROM pg_locks
 WHERE pid = pg_backend_pid()
   AND locktype = 'relation'
   AND relation <> 'pg_locks'::regclass
 GROUP BY 1;

Resultaat:

fastpathcount
f6
t16

Dit is PostgreSQL 16: een primary key-lookup van een enkele rij neemt 22 relation locks, vult alle 16 fast-path slots en duwt 6 locks naar de shared lock table. Bij elke uitvoering, voor altijd.

Prepared statements: de onbedoelde oplossing

Dit is het deel dat niet direct duidelijk is: prepared statements doen dit probleem verdwijnen, en niet vanwege de lock-regels.

Een prepared statement wordt voor de eerste vijf uitvoeringen gepland met een custom plan. Bij de zesde uitvoering bouwt PostgreSQL een generic plan, cached dit, en (als de kostenvergelijking uitvalt, wat bij een primary key-lookup het geval is) hergebruikt hij dit vanaf dat moment. Het hergebruiken van een gecached plan slaat de planner volledig over, en het uitvoeren van een gecached plan vergrendelt alleen de relaties die daadwerkelijk in het plan staan, niet alles wat de planner zou hebben overwogen.

PREPARE u(bigint) AS SELECT * FROM users WHERE id = $1;
-- voer het zes keer uit om voorbij de custom-plan fase te komen, en dan:

BEGIN;
EXECUTE u(42);

SELECT relation::regclass AS relation, fastpath
  FROM pg_locks
 WHERE pid = pg_backend_pid()
   AND locktype = 'relation'
   AND relation <> 'pg_locks'::regclass;

Resultaat:

relationfastpath
users_pkeyt
userst

Tweeëntwintig locks werden er twee, beide comfortabel op het fast path, en de shared lock manager hoort nooit meer iets van deze query. Als bonus stop je ook met het betalen voor het opnieuw plannen van de query bij elke uitvoering, wat op een over-geïndexeerde tabel op zichzelf al een aanzienlijke hoeveelheid CPU kost.

Er zijn twee kanttekeningen. Ten eerste hebben generic plans een bekende foutmodus: parameters met een scheve distributie kunnen plannen krijgen die catastrofaal fout zijn voor specifieke waarden. plancachemode bestaat voor wanneer dit een probleem vormt, maar het eerlijke antwoord is om je meest gebruikte queries te testen.

Ten tweede, en irritanter: prepared statements en connection poolers hebben een gecompliceerde relatie. PgBouncer in transaction pooling mode ondersteunt pas sinds versie 1.21 protocol-level prepared statements via maxpreparedstatements; als je een oudere versie gebruikt, upgrade dan. RDS Proxy is erger: een prepared statement koppelt de clientsessie aan een backend-verbinding, wat de multiplexing (de reden waarom RDS Proxy bestaat) tenietdoet. Als je architectuur afhankelijk is van RDS Proxy, is deze oplossing effectief niet mogelijk.

PostgreSQL 18 verwijdert het magische getal

In PostgreSQL 18 is de fast-path array niet langer vastgesteld op 16 slots, dankzij werk van Tomas Vondra. De grootte wordt bij het opstarten van de server bepaald op basis van maxlocksper_transaction, in groepen van 16 slots. De standaardwaarde van 64 geeft elke backend dus 64 fast-path slots. Dezelfde tabel, dezelfde query, geen prepared statement:

SHOW max_locks_per_transaction;
-- Resultaat: 64

BEGIN;
SELECT * FROM users WHERE id = 42;

SELECT fastpath, count(*)
  FROM pg_locks
 WHERE pid = pg_backend_pid()
   AND locktype = 'relation'
   AND relation <> 'pg_locks'::regclass
 GROUP BY 1;

Resultaat:

fastpathcount
t22

Alle 22 locks bevinden zich direct op het fast path. (Stel maxlocksper_transaction = 16 in op versie 18 en je krijgt de oude 16/6 split terug, mocht je historische rampen willen naspelen.)

Let op dat er geen specifieke knop hiervoor is; de fast-path capaciteit is gekoppeld aan maxlocksper_transaction, wiens primaire taak het dimensioneren van de shared lock table is. Als je schema zwaar gepartitioneerd of zwaar geïndexeerd is, verhoog dit dan boven je worst-case aantal relaties per query. De fast-path arrays zelf kosten bijna niets; de entries in de shared lock table zijn groter, maar op elke machine waar dit probleem voorkomt, heb je voldoende geheugen.

Of, anders gezegd: minder indexen

Beide bovenstaande oplossingen behandelen het symptoom. De ziekte is een tabel met 21 indexen.

Je kent de standaardkosten van over-indexing al: elke INSERT en non-HOT UPDATE moet elke index bijhouden, VACUUM moet elke index verwerken, en elke index neemt opslagruimte en buffer cache in beslag. Voeg deze aan de lijst toe: tot en met PostgreSQL 17 duwt elke index voorbij de vijftiende op een actieve tabel elke query tegen die tabel van het lock fast path af. De boete wordt betaald in shared-memory contention over het hele systeem, niet alleen door de query die het veroorzaakte.

Kijk naar idxscan in pgstatallindexes. Als deze over een representatieve periode nul is en de index geen constraint afdwingt, verwijder hem dan. Je lock manager zal je dankbaar zijn, stilzwijgend, zestien locks per keer.