psql-tidsmätning över en VPN: skilja nätverksväntan från frågans arbete

Din rapport blir klar på ungefär en sekund på databashosten och på fyra över VPN:n, och talet som psql skriver ut pekar rakt på databasen. Det talet är ett stoppur på klientsidan, inte en mätning av servern. Det omfattar satsen på väg ut, väntan på ett svar och raderna på väg tillbaka. Att dela upp dessa tre är det enda sättet att veta vilken sida som ska justeras.

Vad psql-timern egentligen mäter

\timing on startar ett stoppur strax innan klienten skriver satsen till socketen och stannar det när den sista raden i resultatet har lästs. Serverns egen körning ligger någonstans inom det fönstret, och det gör också varje millisekund som paketen tillbringar i tunneln. Ingenting i utdata skiljer dem. EXPLAIN skriver också ut ett tal, men det produceras av servern och utesluter klientens väntan. Två tal, två olika frågor.

Starta en session, låt tidsmätningen vara på och kör satsen du bryr dig om inifrån den. Återanvändning av en session håller anslutningskostnaden utanför den första mätningen.

psql "host=db.internal port=5432 dbname=appdb user=app" \
  -c "\timing on" \
  -c "select count(*) from orders where created_at > now() - interval '1 day';"

Talet inkluderar fortfarande överföringen av resultatet, vilket är det du vill här. Det inkluderar inte längre anslutnings- och autentiseringsutbytet som ett nytt anrop skulle lägga till.

Jämför en sats över tre vägar

Kör samma sats över Unix-socketen, över LAN-adressen och över VPN-värdnamnet, och jämför de tre. Om bara den sista är långsam är frågan inte variabeln.

Varje anrop ansluter först och mäter först därefter satsen, så anslutningskostnaden blir utanför talet medan överföringen blir innanför. Håll WHERE-villkoret, radantalet och klientmaskinen identiska, annars jämför du arbetsbelastningar i stället för vägar.

psql -h /var/run/postgresql -U app -d appdb -c "\timing on" -c "select count(*) from orders;"
psql -h 10.40.0.12 -U app -d appdb -c "\timing on" -c "select count(*) from orders;"
psql -h db.vpn.internal -U app -d appdb -c "\timing on" -c "select count(*) from orders;"

I ett LAN är skillnaden oftast liten nog att ignorera; över en VPN är den ofta inte det. Att sortera de tre resultaten berättar sedan vilken väntan du ska angripa.

En långsam lokal socket betyder serverarbete eller planering. Ett långsamt LAN-resultat betyder frågan eller den volym den flyttar. Bara ett långsamt VPN-resultat betyder latens, MTU eller tillgänglig bandbredd på vägen, och inga indexändringar rättar det.

Hämta serverns egen körtid

Servern kan rapportera hur lång tid den spenderade, och det är det talet som ska hållas mot klientens. EXPLAIN med ANALYZE kör satsen och skriver ut planerings- och körtid inne i planen, medan BUFFERS lägger till cachebeteende som ofta förklarar en varm körning mot en kall. Rikta det inte mot skrivsatser om du inte tänker skriva. På en skrivskyddad replik är det den säkraste platsen att börja, och den psql-referensen dokumenterar timerns omfattning på klientsidan.

För satser som applikationen redan kör samlar pg_stat_statements in körningen på serversidan utan att köra något igen. Raderna är per normaliserad fråga, så en post kan täcka flera anropsplatser.

select calls, round(mean_exec_time::numeric, 2) as mean_ms, rows
from pg_stat_statements
order by mean_exec_time desc
limit 5;

Kolumnnamnen varierar mellan versioner: PostgreSQL 13 och senare exponerar mean_exec_time, medan äldre versioner kallar det mean_time, så kontrollera med \d pg_stat_statements om den frågan ger ett fel. Dessa värden utesluter planering och utesluter resan till din klient. Just det uteslutandet gör dem användbara för jämförelsen.

Räkna tur-och-retur-resor, inte bara millisekunder

En rapport byggd av två hundra små beroende satser multiplicerar varje latens som vägen lägger till med två hundra. En enda sats som returnerar en miljon rader betalar den latensen en gång och betalar sedan bandbredd länge. Båda ser identiska ut i en klienttimer, och de behöver motsatta rättelser.

Mät antalet satser i stället för att uppskatta det. Läs totalen, kör rapporten en gång, läs den sedan igen och subtrahera.

select sum(calls) as calls from pg_stat_statements;
-- run the report here
select sum(calls) as calls from pg_stat_statements;

Om deltat är stort och varje sats är billig på servern är batchning den rättelse som lönar sig. Kombinera beroende frågor, använd en enda mängdbaserad sats eller driv loopen från en markör. I interaktiv psql, \set FETCH_COUNT 1000 får ett stort resultat att komma i portioner och ändrar hur överföringen och klientminnet beter sig. Det minskar inte serverarbetet, så det räddar inte en långsam fråga.

Kontrollera tunneln för omsändningar och MTU-stopp

När små satser är snabba och stora resultat stannar halvvägs är frågan oftast i ordning och vägen inte. En tunnel vars MTU är lägre än den underliggande länken, i kombination med blockerad ICMP, ger exakt det mönstret: de första paketen kommer fram, de stora försvinner och överföringen hänger tills den försöker igen. Omsändningsräknare på klientsidan visar det.

Håll koll på socketen och kärnräknarna medan en avsiktligt långsam överföring pågår. Omsändningssummorna är kumulativa, så notera dem före och efter i stället för att läsa ett enda prov.

ss -tin dst 10.40.0.12
netstat -s | grep -iE "retransmit|segments retransmitted"
ping -M do -s 1400 -c 3 10.40.0.12

Omsändningar som stiger under överföringen, tillsammans med en ping som lyckas vid 1400 byte men misslyckas vid 1472, pekar på ett MTU-problem snarare än ett databasproblem. Att sänka tunnelns gränssnitts-MTU eller klämma MSS vid gatewayen är den vanliga rättelsen, och samma tre kommandon bekräftar det efteråt. Om omsändningarna ligger stilla medan överföringen fortfarande är långsam, titta på bandbredden tunneln får använda i stället för på förluster.

En kort sekvens täcker nästa långsamma rapport: jämför en sats lokalt, i LAN och över VPN; läs serverns körning från EXPLAIN eller pg_stat_statements; räkna anrop före och efter en körning; och håll koll på omsändningar medan det stora resultatet rör sig. Varje steg utesluter en sida av vägen. Ingen av dem kräver att du gissar vilken komponent som ska startas om.