Medir o tempo de psql por VPN: separar a espera de rede do traballo da consulta

O seu informe remata en aproximadamente un segundo no host da base de datos e en catro pola VPN, e o número que imprime psql apunta directamente á base de datos. Ese número é un cronómetro do lado do cliente, non unha medición do servidor. Cobre a instrución de saída, a espera dunha resposta e as filas de volta. Separar esas tres cousas é a única forma de saber que lado axustar.

Que mide realmente o temporizador de psql

\timing on inicia un cronómetro xusto antes de que o cliente escriba a instrución no socket e deténeo cando se leu a última fila dese resultado. A execución do propio servidor está nalgún lugar dentro desa xanela, e tamén cada milisegundo que os paquetes pasan no túnel. Nada na saída os separa. EXPLAIN tamén imprime un número, pero ese prodúceo o servidor e exclúe a espera do cliente. Dous números, dúas preguntas distintas.

Inicie unha sesión, deixe a medición activada e execute a instrución que lle interesa desde dentro. Reutilizar unha sesión mantén o custo de conexión fóra da primeira medición.

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

A cifra segue incluíndo a transferencia do resultado, que é o que se quere aquí. Xa non inclúe o intercambio de conexión e autenticación que engadiría unha invocación nova.

Compare unha instrución por tres camiños

Execute a mesma instrución polo socket Unix, polo enderezo LAN e polo nome de host VPN, e compare as tres. Se só a última é lenta, a consulta non é a variable.

Cada invocación conéctase primeiro e só despois mide a instrución, así que o custo de conexión queda fóra do número mentres a transferencia queda dentro. Manteña a cláusula WHERE, o reconto de filas e a máquina cliente idénticos, ou estará comparando cargas de traballo en lugar de camiños.

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

Nunha LAN a diferenza adoita ser o bastante pequena para ignorala; por VPN a miúdo non o é. Ordenar os tres resultados dille despois contra que espera atacar.

Un socket local lento significa traballo do servidor ou planificación. Un resultado LAN lento significa a consulta ou o volume que move. Só un resultado VPN lento significa latencia, MTU ou ancho de banda dispoñible no camiño, e ningún cambio de índice o arranxa.

Extraia o tempo de execución do propio servidor

O servidor pode informar de canto tempo pasou, e esa cifra é a que hai que comparar coa do cliente. EXPLAIN con ANALYZE executa a instrución e imprime o tempo de planificación e execución dentro do plan, mentres que BUFFERS engade o comportamento da caché que a miúdo explica unha execución en quente fronte a unha en frío. Non o apunte a instrucións de escritura salvo que pretenda escribir. Nunha réplica de só lectura é o lugar máis seguro para comezar, e a referencia de psql documenta o alcance do temporizador no lado do cliente.

Para as instrucións que a aplicación xa executa, pg_stat_statements agrega a execución do lado do servidor sen volver executar nada. As filas son por consulta normalizada, así que unha entrada pode cubrir varios puntos de chamada.

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

Os nomes de columna varían segundo a versión: PostgreSQL 13 e posteriores expoñen mean_exec_time, mentres que as versións anteriores chámano mean_time, así que comprobe con \d pg_stat_statements se esa consulta dá erro. Estes valores exclúen a planificación e exclúen a viaxe ata o seu cliente. Esa exclusión é precisamente por que son útiles para a comparación.

Conte as viaxes de ida e volta, non só os milisegundos

Un informe construído con duascentas instrucións pequenas dependentes multiplica por duascentas calquera latencia que engada o camiño. Unha soa instrución que devolve un millón de filas paga esa latencia unha vez e logo paga ancho de banda durante moito tempo. Ambas se ven iguais nun temporizador de cliente, e necesitan correccións opostas.

Mida o reconto de instrucións en lugar de estimarlo. Lea o total, execute o informe unha vez, volva lelo e reste.

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

Se o delta é grande e cada instrución é barata no servidor, o agrupamento é a corrección que compensa. Combine consultas dependentes, use unha soa instrución baseada en conxuntos ou leve o bucle desde un cursor. En psql interactivo, \set FETCH_COUNT 1000 fai que un resultado grande chegue por lotes e cambia como se comportan a transferencia e a memoria do cliente. Non reduce o traballo do servidor, así que non rescatará unha consulta lenta.

Revise o túnel por retransmisións e paradas de MTU

Cando as instrucións pequenas son rápidas e os resultados grandes se atascan a metade de camiño, a consulta adoita estar ben e o camiño non. Un túnel cuxo MTU é inferior á ligazón subxacente, combinado con ICMP bloqueado, produce exactamente ese patrón: chegan os primeiros paquetes, desaparecen os grandes e a transferencia colga ata que reintenta. Os contadores de retransmisión do lado do cliente móstrano.

Vixíe o socket e os contadores do núcleo mentres se executa unha transferencia deliberadamente lenta. Os totais de retransmisión son acumulativos, así que anóteos antes e despois en lugar de ler unha soa mostra.

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

As retransmisións que soben durante a transferencia, xunto cun ping que ten éxito a 1400 bytes pero falla a 1472, apuntan a un problema de MTU e non de base de datos. Baixar o MTU da interface do túnel ou limitar o MSS na pasarela é a corrección habitual, e os mesmos tres comandos confírmano despois. Se as retransmisións se manteñen planas mentres a transferencia segue lenta, mire o ancho de banda permitido ao túnel e non as perdas.

Unha secuencia curta cobre o seguinte informe lento: compare unha instrución en local, na LAN e pola VPN; lea a execución do servidor desde EXPLAIN ou pg_stat_statements; conte as chamadas antes e despois dunha soa execución; e vixíe as retransmisións mentres se move o resultado grande. Cada paso descarta un lado do camiño. Ningún esixe adiviñar que compoñente reiniciar.