psql-tidsmåling over en VPN: skille nettverksventing fra spørringsarbeid

Rapporten din blir ferdig på omtrent ett sekund på databaseverten og på fire over VPN-en, og tallet psql skriver ut peker rett på databasen. Det tallet er en stoppeklokke på klientsiden, ikke en måling av serveren. Det dekker setningen på vei ut, ventingen på et svar og radene på vei tilbake. Å dele disse tre er den eneste måten å vite hvilken side som skal justeres.

Hva psql-timeren egentlig måler

\timing on starter en stoppeklokke rett før klienten skriver setningen til sockelen og stopper når den siste raden i resultatet er lest. Selve kjøringen på serveren ligger et sted inne i det vinduet, og det gjør også hver millisekund pakkene tilbringer i tunnelen. Ingenting i utdataene skiller dem. EXPLAIN skriver også ut et tall, men det produseres av serveren og utelukker klientens venting. To tall, to forskjellige spørsmål.

Start én økt, la tidsmålingen stå på og kjør setningen du bryr deg om inne fra den. Gjenbruk av en økt holder tilkoblingskostnaden utenfor den første målingen.

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';"

Tallet inkluderer fortsatt overføringen av resultatet, som er det du vil her. Det inkluderer ikke lenger tilkoblings- og godkjenningsutvekslingen som et nytt kall ville legge til.

Sammenlign én setning over tre veier

Kjør den samme setningen over Unix-sockelen, over LAN-adressen og over VPN-vertsnavnet, og sammenlign de tre. Hvis bare den siste er treg, er ikke spørringen variabelen.

Hvert kall kobler til først og måler først deretter setningen, så tilkoblingskostnaden blir utenfor tallet mens overføringen blir innenfor. Hold WHERE-klausulen, radantallet og klientmaskinen identiske, ellers sammenligner du arbeidsbelastninger i stedet for veier.

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;"

På et LAN er forskjellen vanligvis liten nok til å ignorere; over en VPN er den ofte ikke det. Å sortere de tre resultatene forteller deg deretter hvilken venting du skal angripe.

En treg lokal sockel betyr serverarbeid eller planlegging. Et tregt LAN-resultat betyr spørringen eller volumet den flytter. Bare et tregt VPN-resultat betyr latens, MTU eller tilgjengelig båndbredde på veien, og ingen indeksendringer retter det.

Hent serverens egen kjøretid

Serveren kan rapportere hvor lang tid den brukte, og det tallet er det som skal holdes mot klientens. EXPLAIN med ANALYZE kjører setningen og skriver ut planleggings- og kjøretid inne i planen, mens BUFFERS legger til cacheatferd som ofte forklarer en varm kjøring mot en kald. Ikke rett det mot skrivesetninger med mindre du har tenkt å skrive. På en skrivebeskyttet replika er det det tryggeste stedet å begynne, og den psql-referansen dokumenterer timerens omfang på klientsiden.

For setninger som applikasjonen allerede kjører, samler pg_stat_statements kjøringen på serversiden uten å kjøre noe på nytt. Radene er per normalisert spørring, så en oppføring kan dekke flere kallsteder.

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

Kolonnenavn varierer etter versjon: PostgreSQL 13 og nyere viser mean_exec_time, mens eldre versjoner kaller det mean_time, så sjekk med \d pg_stat_statements, hvis den spørringen gir en feil. Disse verdiene utelukker planlegging og utelukker reisen til klienten din. Nettopp den utelukkelsen gjør dem nyttige for sammenligningen.

Tell rundturer, ikke bare millisekunder

En rapport bygget av to hundre små avhengige setninger multipliserer enhver latens veien legger til med to hundre. En enkelt setning som returnerer en million rader betaler den latenstiden én gang og betaler deretter båndbredde lenge. Begge ser identiske ut i en klienttimer, og de trenger motsatte rettelser.

Mål antallet setninger i stedet for å anslå det. Les totalen, kjør rapporten én gang, les den så igjen og trekk fra.

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

Hvis deltaet er stort og hver setning er billig på serveren, er batching rettelsen som betaler seg. Kombiner avhengige spørringer, bruk én setningsbasert spørring eller driv løkken fra en markør. I interaktiv psql, \set FETCH_COUNT 1000 får et stort resultat til å komme i porsjoner og endrer hvordan overføringen og klientminnet oppfører seg. Det reduserer ikke serverarbeid, så det redder ikke en treg spørring.

Sjekk tunnelen for gjensendinger og MTU-stopp

Når små setninger er raske og store resultater stopper halvveis, er spørringen vanligvis i orden og veien ikke. En tunnel med lavere MTU enn den underliggende linken, kombinert med blokkert ICMP, gir nøyaktig det mønsteret: de første pakkene kommer frem, de store forsvinner, og overføringen henger til den prøver igjen. Gjensendingstellere på klientsiden viser det.

Følg med på sockelen og kjernetailjerne mens en bevisst treg overføring kjører. Gjensendingstallene er kumulative, så noter dem før og etter i stedet for å lese én prøve.

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

Gjensendinger som stiger under overføringen, sammen med en ping som lykkes på 1400 byte men feiler på 1472, peker på et MTU-problem snarere enn et databaseproblem. Å senke MTU-en på tunnelgrensesnittet eller klemme MSS ved gatewayen er den vanlige rettelsen, og de samme tre kommandoene bekrefter det etterpå. Hvis gjensendingene holder seg flate mens overføringen fortsatt er treg, se på båndbredden tunnelen får bruke i stedet for tap.

En kort sekvens dekker den neste trege rapporten: sammenlign én setning lokalt, på LAN og over VPN; les serverkjøringen fra EXPLAIN eller pg_stat_statements; tell kall før og etter én kjøring; og følg med på gjensendinger mens det store resultatet beveger seg. Hvert steg utelukker den ene siden av veien. Ingen av dem krever å gjette hvilken komponent som skal startes på nytt.