Pengukuran waktu psql lewat VPN: memisahkan tunggu jaringan dari kerja kueri

Laporan Anda selesai dalam sekitar satu detik di host basis data dan empat detik lewat VPN, dan angka yang dicetak psql menunjuk langsung ke basis data. Angka itu adalah stopwatch sisi klien, bukan pengukuran server. Angka itu mencakup pernyataan saat keluar, penantian balasan, dan baris saat kembali. Memisahkan ketiganya adalah satu-satunya cara mengetahui sisi mana yang harus disetel.

Apa yang sebenarnya diukur pengatur waktu psql

\timing on memulai stopwatch tepat sebelum klien menulis pernyataan ke soket dan berhenti saat baris terakhir hasil itu dibaca. Eksekusi server sendiri berada di suatu tempat di dalam jendela itu, begitu pula setiap milidetik yang dihabiskan paket di terowongan. Tidak ada apa pun di keluaran yang memisahkannya. EXPLAIN juga mencetak angka, tetapi angka itu dihasilkan server dan mengecualikan penantian klien. Dua angka, dua pertanyaan berbeda.

Mulai satu sesi, biarkan pengukuran waktu menyala, dan jalankan pernyataan yang Anda pedulikan dari dalamnya. Menggunakan ulang sesi menjaga biaya koneksi keluar dari pengukuran pertama.

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

Angka itu masih mencakup transfer hasil, yang memang diinginkan di sini. Angka itu tidak lagi mencakup pertukaran koneksi dan autentikasi yang akan ditambahkan pemanggilan baru.

Bandingkan satu pernyataan di tiga jalur

Jalankan pernyataan yang sama lewat soket Unix, lewat alamat LAN, dan lewat nama host VPN, lalu bandingkan ketiganya. Jika hanya yang terakhir lambat, kueri bukan variabelnya.

Setiap pemanggilan terhubung dulu dan baru kemudian mengukur pernyataan, jadi biaya koneksi tetap di luar angka sementara transfer tetap di dalam. Jaga klausa WHERE, jumlah baris, dan mesin klien tetap sama, atau Anda membandingkan beban kerja, bukan jalur.

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

Di LAN perbedaannya biasanya cukup kecil untuk diabaikan; lewat VPN sering kali tidak. Mengurutkan ketiga hasil kemudian memberi tahu penantian mana yang harus diserang.

Soket lokal yang lambat berarti kerja server atau perencanaan. Hasil LAN yang lambat berarti kueri atau volume yang dipindahkannya. Hanya hasil VPN yang lambat berarti latensi, MTU, atau bandwidth tersedia di jalur, dan tidak ada perubahan indeks yang memperbaikinya.

Ambil waktu eksekusi server sendiri

Server dapat melaporkan berapa lama waktu yang dihabiskannya, dan angka itulah yang harus disandingkan dengan angka klien. EXPLAIN dengan ANALYZE menjalankan pernyataan dan mencetak waktu perencanaan dan eksekusi di dalam rencana, sedangkan BUFFERS menambahkan perilaku cache yang sering menjelaskan jalan hangat versus dingin. Jangan mengarahkannya ke pernyataan tulis kecuali Anda berniat menulis. Pada replika hanya-baca, itu tempat paling aman untuk mulai, dan referensi psql mendokumentasikan cakupan pengatur waktu di sisi klien.

Untuk pernyataan yang sudah dijalankan aplikasi, pg_stat_statements mengagregasi eksekusi sisi server tanpa menjalankan ulang apa pun. Barisnya per kueri ternormalisasi, jadi satu entri dapat mencakup beberapa lokasi pemanggilan.

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

Nama kolom berbeda menurut rilis: PostgreSQL 13 dan yang lebih baru mengekspos mean_exec_time, sedangkan versi lama menyebutnya mean_time, jadi periksa dengan \d pg_stat_statements jika kueri itu menghasilkan galat. Nilai-nilai ini mengecualikan perencanaan dan mengecualikan perjalanan ke klien Anda. Pengecualian itulah yang membuatnya berguna untuk perbandingan.

Hitung pulang-pergi, bukan hanya milidetik

Laporan yang dibangun dari dua ratus pernyataan kecil yang saling bergantung mengalikan latensi apa pun yang ditambahkan jalur dengan dua ratus. Satu pernyataan yang mengembalikan satu juta baris membayar latensi itu sekali lalu membayar bandwidth untuk waktu lama. Keduanya tampak sama di pengatur waktu klien, dan keduanya perlu perbaikan yang berlawanan.

Ukur jumlah pernyataan alih-alih menaksirnya. Baca totalnya, jalankan laporan sekali, lalu baca lagi dan kurangi.

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

Jika delta besar dan setiap pernyataan murah di server, pengelompokan adalah perbaikan yang membayar. Gabungkan kueri yang saling bergantung, gunakan satu pernyataan berbasis himpunan, atau jalankan loop dari kursor. Di psql interaktif, \set FETCH_COUNT 1000 membuat hasil besar datang dalam kelompok dan mengubah perilaku transfer serta memori klien. Itu tidak mengurangi kerja server, jadi tidak akan menyelamatkan kueri yang lambat.

Periksa terowongan untuk pengiriman ulang dan kemacetan MTU

Ketika pernyataan kecil cepat dan hasil besar berhenti di tengah jalan, kuerinya biasanya baik-baik saja dan jalurnya tidak. Terowongan yang MTU-nya lebih rendah dari tautan dasarnya, ditambah ICMP yang diblokir, menghasilkan pola persis seperti itu: paket pertama tiba, paket besar menghilang, dan transfer menggantung sampai mencoba lagi. Penghitung pengiriman ulang di sisi klien menunjukkannya.

Amati soket dan penghitung kernel selagi transfer yang sengaja dibuat lambat berjalan. Total pengiriman ulang bersifat kumulatif, jadi catat sebelum dan sesudah alih-alih membaca satu sampel.

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

Pengiriman ulang yang naik selama transfer, bersama ping yang berhasil pada 1400 byte tetapi gagal pada 1472, menunjuk ke masalah MTU, bukan masalah basis data. Menurunkan MTU antarmuka terowongan atau mengencangkan MSS di gateway adalah koreksi yang biasa, dan tiga perintah yang sama memastikannya sesudahnya. Jika pengiriman ulang tetap datar sementara transfer masih lambat, lihat bandwidth yang diizinkan untuk terowongan, bukan kehilangan paket.

Urutan singkat mencakup laporan lambat berikutnya: bandingkan satu pernyataan secara lokal, di LAN, dan lewat VPN; baca eksekusi server dari EXPLAIN atau pg_stat_statements; hitung pemanggilan sebelum dan sesudah satu kali jalan; dan amati pengiriman ulang selagi hasil besar bergerak. Setiap langkah menyingkirkan satu sisi jalur. Tidak satu pun menuntut menebak komponen mana yang harus dimulai ulang.