psql-timing over een VPN: netwerkwacht van querywerk scheiden

Uw rapport is in ongeveer een seconde klaar op de databasehost en in vier over de VPN, en het getal dat psql afdrukt wijst recht naar de database. Dat getal is een stopwatch aan de clientkant, geen meting van de server. Het omvat het statement op de heenweg, het wachten op een antwoord en de rijen op de terugweg. Die drie uit elkaar halen is de enige manier om te weten welke kant u moet afstellen.

Wat de psql-timer echt meet

\timing on start een stopwatch vlak voordat de client het statement naar de socket schrijft en stopt wanneer de laatste rij van dat resultaat is gelezen. De uitvoering van de server zelf zit ergens in dat venster, en elke milliseconde die pakketten in de tunnel doorbrengen ook. Niets in de uitvoer scheidt ze. EXPLAIN drukt ook een getal af, maar dat wordt door de server gemaakt en sluit het wachten van de client uit. Twee getallen, twee verschillende vragen.

Start één sessie, laat de timing aanstaan en voer het statement dat u belangrijk vindt van binnenuit uit. Hergebruik van een sessie houdt de verbindingskosten buiten de eerste meting.

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

Het getal bevat nog steeds de overdracht van het resultaat, en dat is hier juist gewenst. Het bevat niet langer de verbindings- en authenticatie-uitwisseling die een nieuwe aanroep zou toevoegen.

Vergelijk één statement over drie paden

Voer hetzelfde statement uit via de Unix-socket, via het LAN-adres en via de VPN-hostnaam, en vergelijk de drie. Als alleen de laatste traag is, is de query niet de variabele.

Elke aanroep verbindt eerst en meet pas daarna het statement, dus de verbindingskosten blijven buiten het getal terwijl de overdracht erin blijft. Houd de WHERE-clausule, het aantal rijen en de clientmachine identiek, anders vergelijkt u werklasten in plaats van paden.

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

Op een LAN is het verschil meestal klein genoeg om te negeren; over een VPN vaak niet. De drie resultaten sorteren vertelt u dan welk wachten u moet aanpakken.

Een trage lokale socket betekent serverwerk of planning. Een traag LAN-resultaat betekent de query of het volume dat die verplaatst. Alleen een traag VPN-resultaat betekent latentie, MTU of beschikbare bandbreedte op het pad, en geen indexwijziging lost dat op.

Haal de eigen uitvoeringstijd van de server eruit

De server kan melden hoeveel tijd hij heeft besteed, en dat is het getal dat u tegen dat van de client moet houden. EXPLAIN met ANALYZE voert het statement uit en drukt planning- en uitvoeringstijd in het plan af, terwijl BUFFERS cachegedrag toevoegt dat vaak een warme run tegenover een koude verklaart. Richt het niet op schrijfstatements tenzij u van plan bent te schrijven. Op een alleen-lezen replica is het de veiligste plek om te beginnen, en de psql-referentie documenteert de reikwijdte van de timer aan de clientkant.

Voor statements die de applicatie al uitvoert, verzamelt pg_stat_statements de uitvoering aan de serverkant zonder iets opnieuw te draaien. De rijen zijn per genormaliseerde query, dus één vermelding kan meerdere aanroepplaatsen dekken.

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

Kolomnamen verschillen per release: PostgreSQL 13 en later tonen mean_exec_time, terwijl oudere versies het mean_time noemen, dus controleer met \d pg_stat_statements als die query een fout geeft. Deze waarden sluiten planning uit en sluiten de reis naar uw client uit. Juist die uitsluiting maakt ze nuttig voor de vergelijking.

Tel retouren, niet alleen milliseconden

Een rapport opgebouwd uit tweehonderd kleine afhankelijke statements vermenigvuldigt elke latentie die het pad toevoegt met tweehonderd. Eén statement dat een miljoen rijen teruggeeft, betaalt die latentie één keer en betaalt daarna lang bandbreedte. Beide zien er in een clienttimer identiek uit, en ze vragen om tegengestelde correcties.

Meet het aantal statements in plaats van het te schatten. Lees het totaal, voer het rapport één keer uit, lees het dan opnieuw en trek af.

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

Als de delta groot is en elk statement op de server goedkoop is, is batchen de correctie die loont. Combineer afhankelijke query's, gebruik één op sets gebaseerd statement of stuur de lus aan vanuit een cursor. In interactieve psql, \set FETCH_COUNT 1000 laat een groot resultaat in batches aankomen en verandert hoe de overdracht en het clientgeheugen zich gedragen. Het vermindert geen serverwerk, dus het redt een trage query niet.

Controleer de tunnel op hertransmissies en MTU-stops

Wanneer kleine statements snel zijn en grote resultaten halverwege vastlopen, is de query meestal in orde en het pad niet. Een tunnel waarvan de MTU lager is dan de onderliggende link, in combinatie met geblokkeerde ICMP, levert precies dat patroon: de eerste pakketten komen aan, de grote verdwijnen en de overdracht blijft hangen tot hij het opnieuw probeert. Hertransmissietellers aan de clientkant laten dat zien.

Houd de socket en de kerneltellers in het oog terwijl een bewust trage overdracht loopt. Hertransmissietotalen zijn cumulatief, dus noteer ze voor en na in plaats van één monster te lezen.

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

Hertransmissies die tijdens de overdracht stijgen, samen met een ping die bij 1400 bytes slaagt maar bij 1472 faalt, wijzen op een MTU-probleem en niet op een databaseprobleem. De MTU van de tunnelinterface verlagen of MSS bij de gateway knijpen is de gebruikelijke correctie, en dezelfde drie commando's bevestigen het daarna. Als de hertransmissies vlak blijven terwijl de overdracht nog traag is, kijk dan naar de bandbreedte die de tunnel mag gebruiken en niet naar verlies.

Een korte reeks dekt het volgende trage rapport: vergelijk één statement lokaal, op de LAN en over de VPN; lees de serveruitvoering uit EXPLAIN of pg_stat_statements; tel aanroepen voor en na één run; en houd hertransmissies in het oog terwijl het grote resultaat beweegt. Elke stap sluit één kant van het pad uit. Geen ervan vereist te raden welke component opnieuw moet worden gestart.