psql-tidsmåling over ein VPN: skilje nettverksventing frå spørjearbeid

Rapporten din blir ferdig på omtrent eitt sekund på databaseverten og på fire over VPN-en, og talet psql skriv ut peikar rett på databasen. Det talet er ei stoppeklokke på klientsida, ikkje ei måling av serveren. Det dekkjer setninga på veg ut, ventinga på eit svar og radene på veg tilbake. Å dele desse tre er den einaste måten å vite kva side som skal justerast.

Kva psql-timaren eigentleg måler

\timing on startar ei stoppeklokke rett før klienten skriv setninga til sockelen og stoppar når den siste rada i resultatet er lesen. Sjølve køyringa på serveren ligg ein stad inne i det vindauget, og det gjer også kvar millisekund pakkane tilbringar i tunnelen. Ingenting i utdataa skil dei. EXPLAIN skriv også ut eit tal, men det blir produsert av serveren og utelèt ventinga til klienten. To tal, to ulike spørsmål.

Start éi økt, la tidsmålinga stå på og køyr setninga du bryr deg om inne frå henne. Gjenbruk av ei økt held tilkoplingskostnaden utanfor den første målinga.

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 inkluderer framleis overføringa av resultatet, som er det du vil her. Det inkluderer ikkje lenger tilkoplings- og godkjenningsutvekslinga som eit nytt kall ville leggje til.

Samanlikn éi setning over tre vegar

Køyr den same setninga over Unix-sockelen, over LAN-adressa og over VPN-vertsnamnet, og samanlikn dei tre. Viss berre den siste er treg, er ikkje spørjinga variabelen.

Kvart kall koplar til først og måler først deretter setninga, så tilkoplingskostnaden blir utanfor talet medan overføringa blir innanfor. Hald WHERE-klausulen, radtalet og klientmaskina identiske, elles samanliknar du arbeidsbelastningar i staden for vegar.

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å eit LAN er skilnaden vanlegvis liten nok til å ignorere; over eit VPN er han ofte ikkje det. Å sortere dei tre resultata fortel deg deretter kva venting du skal angripe.

Ein treg lokal sockel betyr serverarbeid eller planlegging. Eit tregt LAN-resultat betyr spørjinga eller volumet ho flyttar. Berre eit tregt VPN-resultat betyr latens, MTU eller tilgjengeleg bandbreidd på vegen, og ingen indeksendringar rettar det.

Hent den eigentlege køyretida til serveren

Serveren kan rapportere kor lang tid han brukte, og det talet er det som skal haldast mot klienten sitt. EXPLAIN med ANALYZE køyrer setninga og skriv ut planleggings- og køyretid inne i planen, medan BUFFERS legg til cacheåtferd som ofte forklarer ein varm køyring mot ein kald. Ikkje rett det mot skrivesetningar med mindre du har tenkt å skrive. På ein skriveverna replika er det den tryggaste staden å byrje, og den psql-referansen dokumenterer omfanget til timaren på klientsida.

For setningar som applikasjonen allereie køyrer, samlar pg_stat_statements køyringa på serversida utan å køyre noko på nytt. Radene er per normalisert spørjing, så ein oppføring kan dekkje fleire kallstader.

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

Kolonnenamn varierer etter versjon: PostgreSQL 13 og nyare viser mean_exec_time, medan eldre versjonar kallar det mean_time, så sjekk med \d pg_stat_statements, viss den spørjinga gir ein feil. Desse verdiane utelèt planlegging og utelèt reisa til klienten din. Nettopp den utelatinga gjer dei nyttige for samanlikninga.

Tel rundturar, ikkje berre millisekund

Ein rapport bygd av to hundre små avhengige setningar multipliserer kvar latens vegen legg til med to hundre. Éi setning som returnerer ein million rader betaler den latenstida éin gong og betaler deretter bandbreidd lenge. Begge ser identiske ut i ein klienttimer, og dei treng motsette rettingar.

Mål talet på setningar i staden for å anslå det. Les totalen, køyr rapporten éin gong, les han så igjen og trekk frå.

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

Viss deltaet er stort og kvar setning er billeg på serveren, er batching rettinga som betaler seg. Kombiner avhengige spørjingar, bruk éi setningsbasert spørjing eller driv løkka frå ein markør. I interaktiv psql, \set FETCH_COUNT 1000 får eit stort resultat til å kome i porsjonar og endrar korleis overføringa og klientminnet oppfører seg. Det reduserer ikkje serverarbeid, så det reddar ikkje ei treg spørjing.

Sjekk tunnelen for gjensendingar og MTU-stopp

Når små setningar er raske og store resultat stoppar halvvegs, er spørjinga vanlegvis i orden og vegen ikkje. Ein tunnel med lågare MTU enn den underliggande lenkja, kombinert med blokkert ICMP, gir nøyaktig det mønsteret: dei første pakkane kjem fram, dei store forsvinn, og overføringa heng til han prøver igjen. Gjensendingsteljarar på klientsida viser det.

Følg med på sockelen og kjernetaljarane medan ei medvite treg overføring køyrer. Gjensendingstala er kumulative, så noter dei før og etter i staden for å lese éin 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

Gjensendingar som stig under overføringa, saman med ein ping som lukkast på 1400 byte men feilar på 1472, peikar på eit MTU-problem snarare enn eit databaseproblem. Å senke MTU-en på tunnelgrensesnittet eller klemme MSS ved gatewayen er den vanlege rettinga, og dei same tre kommandoane stadfester det etterpå. Viss gjensendingane held seg flate medan overføringa framleis er treg, sjå på bandbreidda tunnelen får bruke i staden for tap.

Ein kort sekvens dekkjer den neste trege rapporten: samanlikn éi setning lokalt, på LAN og over VPN; les serverkøyringa frå EXPLAIN eller pg_stat_statements; tel kall før og etter éi køyring; og følg med på gjensendingar medan det store resultatet bevegar seg. Kvart steg utelèt den eine sida av vegen. Ingen av dei krev å gjette kva komponent som skal startast på nytt.