Misurare i tempi di psql su una VPN: separare l’attesa di rete dal lavoro della query

Il tuo report termina in circa un secondo sull’host del database e in quattro sulla VPN, e il numero che psql stampa punta dritto al database. Quel numero è un cronometro lato client, non una misura del server. Copre l’istruzione in uscita, l’attesa di una risposta e le righe al ritorno. Separare queste tre cose è l’unico modo per sapere quale lato regolare.

Che cosa misura davvero il timer di psql

\timing on avvia un cronometro appena prima che il client scriva l’istruzione nel socket e lo ferma quando è stata letta l’ultima riga di quel risultato. L’esecuzione del server stesso si trova da qualche parte dentro quella finestra, e così ogni millisecondo che i pacchetti trascorrono nel tunnel. Nulla nell’output li separa. EXPLAIN stampa anch’esso un numero, ma quello lo produce il server ed esclude l’attesa del client. Due numeri, due domande diverse.

Avvia una sessione, lascia attiva la misurazione e esegui l’istruzione che ti interessa dall’interno. Riusare una sessione tiene il costo di connessione fuori dalla prima misurazione.

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

Il numero include ancora il trasferimento del risultato, che è ciò che vuoi qui. Non include più lo scambio di connessione e autenticazione che una nuova invocazione aggiungerebbe.

Confronta un’istruzione su tre percorsi

Esegui la stessa istruzione sul socket Unix, sull’indirizzo LAN e sul nome host VPN, e confronta i tre. Se solo l’ultimo è lento, la query non è la variabile.

Ogni invocazione si connette prima e solo dopo misura l’istruzione, quindi il costo di connessione resta fuori dal numero mentre il trasferimento resta dentro. Mantieni identici la clausola WHERE, il numero di righe e la macchina client, altrimenti stai confrontando carichi di lavoro invece di percorsi.

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

Su una LAN la differenza di solito è abbastanza piccola da ignorarla; su una VPN spesso non lo è. Ordinare i tre risultati ti dice poi quale attesa attaccare.

Un socket locale lento significa lavoro del server o pianificazione. Un risultato LAN lento significa la query o il volume che muove. Solo un risultato VPN lento significa latenza, MTU o larghezza di banda disponibile sul percorso, e nessuna modifica di indice lo risolve.

Estrai il tempo di esecuzione del server stesso

Il server può riportare quanto tempo ha impiegato, e quella è la cifra da tenere contro quella del client. EXPLAIN con ANALYZE esegue l’istruzione e stampa il tempo di pianificazione ed esecuzione dentro il piano, mentre BUFFERS aggiunge il comportamento della cache che spesso spiega un’esecuzione calda rispetto a una fredda. Non puntarlo su istruzioni di scrittura se non intendi scrivere. Su una replica di sola lettura è il posto più sicuro per iniziare, e la documentazione di psql documenta l’ambito del timer lato client.

Per le istruzioni che l’applicazione esegue già, pg_stat_statements aggrega l’esecuzione lato server senza rieseguire nulla. Le righe sono per query normalizzata, quindi una voce può coprire più punti di chiamata.

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

I nomi delle colonne cambiano con la versione: PostgreSQL 13 e successivi espongono mean_exec_time, mentre le versioni precedenti lo chiamano mean_time, quindi controlla con \d pg_stat_statements se quella query dà errore. Questi valori escludono la pianificazione ed escludono il viaggio verso il tuo client. Proprio questa esclusione li rende utili per il confronto.

Conta i viaggi di andata e ritorno, non solo i millisecondi

Un report costruito con duecento piccole istruzioni dipendenti moltiplica per duecento qualsiasi latenza aggiunta dal percorso. Una sola istruzione che restituisce un milione di righe paga quella latenza una volta e poi paga larghezza di banda a lungo. Entrambi sembrano identici in un timer client, e richiedono correzioni opposte.

Misura il numero di istruzioni invece di stimarlo. Leggi il totale, esegui il report una volta, poi rileggilo e sottrai.

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

Se il delta è grande e ogni istruzione è economica sul server, il raggruppamento è la correzione che paga. Combina query dipendenti, usa una sola istruzione basata su insiemi o guida il ciclo da un cursore. In psql interattivo, \set FETCH_COUNT 1000 fa arrivare un risultato grande a lotti e cambia il comportamento del trasferimento e della memoria del client. Non riduce il lavoro del server, quindi non salverà una query lenta.

Controlla il tunnel per ritrasmissioni e blocchi di MTU

Quando le istruzioni piccole sono veloci e i risultati grandi si bloccano a metà strada, la query di solito va bene e il percorso no. Un tunnel il cui MTU è inferiore al collegamento sottostante, combinato con ICMP bloccato, produce esattamente quel modello: i primi pacchetti arrivano, quelli grandi svaniscono e il trasferimento resta appeso finché non riprova. I contatori di ritrasmissione lato client lo mostrano.

Osserva il socket e i contatori del kernel mentre è in corso un trasferimento volutamente lento. I totali di ritrasmissione sono cumulativi, quindi annotali prima e dopo invece di leggere un solo campione.

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

Le ritrasmissioni che crescono durante il trasferimento, insieme a un ping che riesce a 1400 byte ma fallisce a 1472, indicano un problema di MTU e non di database. Abbassare l’MTU dell’interfaccia del tunnel o limitare l’MSS sul gateway è la correzione abituale, e gli stessi tre comandi lo confermano dopo. Se le ritrasmissioni restano piatte mentre il trasferimento è ancora lento, guarda la larghezza di banda consentita al tunnel e non le perdite.

Una sequenza breve copre il prossimo report lento: confronta un’istruzione in locale, sulla LAN e sulla VPN; leggi l’esecuzione del server da EXPLAIN o pg_stat_statements; conta le chiamate prima e dopo una singola esecuzione; e osserva le ritrasmissioni mentre il risultato grande si muove. Ogni passo esclude un lato del percorso. Nessuno richiede di indovinare quale componente riavviare.