Masa psql melalui VPN: memisahkan tunggu rangkaian daripada kerja pertanyaan

Laporan anda selesai dalam kira-kira satu saat pada hos pangkalan data dan empat melalui VPN, dan nombor yang dicetak psql menunjuk terus ke pangkalan data. Nombor itu ialah jam randik di pihak klien, bukan ukuran pelayan. Ia meliputi pernyataan dalam perjalanan keluar, tunggu balasan dan baris dalam perjalanan balik. Memisahkan ketiga-tiganya ialah satu-satunya cara mengetahui sisi mana yang perlu ditala.

Apa yang sebenarnya diukur oleh pemasa psql

\timing on memulakan jam randik sejurus sebelum klien menulis pernyataan ke soket dan berhenti apabila baris terakhir hasil itu dibaca. Pelaksanaan pelayan itu sendiri berada di suatu tempat dalam tetingkap itu, begitu juga setiap milisaat yang paket habiskan dalam terowong. Tiada apa pun dalam output memisahkan mereka. EXPLAIN juga mencetak nombor, tetapi nombor itu dihasilkan oleh pelayan dan mengecualikan tunggu klien. Dua nombor, dua soalan berbeza.

Mulakan satu sesi, biarkan pemasaan dihidupkan dan jalankan pernyataan yang anda pedulikan dari dalamnya. Menggunakan semula sesi menyimpan kos sambungan di luar ukuran 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';"

Nombor itu masih merangkumi pemindahan hasil, yang memang dikehendaki di sini. Ia tidak lagi merangkumi pertukaran sambungan dan pengesahan yang akan ditambah oleh panggilan baharu.

Bandingkan satu pernyataan merentas tiga laluan

Jalankan pernyataan yang sama melalui soket Unix, melalui alamat LAN dan melalui nama hos VPN, lalu bandingkan ketiga-tiganya. Jika hanya yang terakhir perlahan, pertanyaan bukan pemboleh ubah.

Setiap panggilan menyambung dahulu dan hanya kemudian mengukur pernyataan, jadi kos sambungan kekal di luar nombor manakala pemindahan kekal di dalam. Kekalkan klausa WHERE, bilangan baris dan mesin klien sama, atau anda membandingkan beban kerja dan bukan laluan.

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

Pada LAN perbezaannya biasanya cukup kecil untuk diabaikan; melalui VPN selalunya tidak. Menyusun ketiga-tiga hasil kemudian memberitahu anda tunggu mana yang perlu diserang.

Soket tempatan yang perlahan bermaksud kerja pelayan atau perancangan. Hasil LAN yang perlahan bermaksud pertanyaan atau isipadu yang dipindahkannya. Hanya hasil VPN yang perlahan bermaksud kependaman, MTU atau lebar jalur tersedia pada laluan itu, dan tiada perubahan indeks membaikinya.

Keluarkan masa pelaksanaan pelayan itu sendiri

Pelayan boleh melaporkan berapa lama ia mengambil masa, dan nombor itulah yang perlu dibandingkan dengan nombor klien. EXPLAIN dengan ANALYZE melaksanakan pernyataan dan mencetak masa perancangan dan pelaksanaan dalam pelan, manakala BUFFERS menambah tingkah laku cache yang sering menjelaskan larian hangat berbanding sejuk. Jangan halakannya kepada pernyataan tulis melainkan anda berniat untuk menulis. Pada replika baca sahaja, ia tempat paling selamat untuk bermula, dan rujukan psql mendokumenkan skop pemasa di pihak klien.

Bagi pernyataan yang sudah dijalankan oleh aplikasi, pg_stat_statements mengagregatkan pelaksanaan di pihak pelayan tanpa menjalankan semula apa-apa. Baris adalah bagi setiap pertanyaan ternormal, jadi satu entri boleh meliputi beberapa lokasi panggilan.

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 lajur berbeza mengikut keluaran: PostgreSQL 13 dan kemudiannya mendedahkan mean_exec_time, manakala versi lebih lama memanggilnya mean_time, jadi semak dengan \d pg_stat_statements jika pertanyaan itu memberikan ralat. Nilai-nilai ini mengecualikan perancangan dan mengecualikan perjalanan ke klien anda. Pengecualian itulah yang menjadikannya berguna untuk perbandingan.

Kira perjalanan pergi balik, bukan hanya milisaat

Laporan yang dibina daripada dua ratus pernyataan kecil bergantung menggandakan apa-apa kependaman yang ditambah oleh laluan dengan dua ratus. Satu pernyataan yang memulangkan sejuta baris membayar kependaman itu sekali dan kemudian membayar lebar jalur untuk masa yang lama. Kedua-duanya kelihatan sama dalam pemasa klien, dan kedua-duanya memerlukan pembetulan bertentangan.

Ukur bilangan pernyataan dan bukan menganggarkannya. Baca jumlahnya, jalankan laporan sekali, kemudian baca lagi dan tolak.

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 pada pelayan, pengumpulan kelompok ialah pembetulan yang berbaloi. Gabungkan pertanyaan bergantung, gunakan satu pernyataan berasaskan set atau pandu gelung daripada kursor. Dalam psql interaktif, \set FETCH_COUNT 1000 hasil besar tiba dalam kelompok dan mengubah tingkah laku pemindahan dan memori klien. Ia tidak mengurangkan kerja pelayan, jadi ia tidak akan menyelamatkan pertanyaan yang perlahan.

Periksa terowong untuk penghantaran semula dan genjatan MTU

Apabila pernyataan kecil pantas dan hasil besar tersekat di pertengahan jalan, pertanyaannya biasanya betul dan laluannya tidak. Terowong yang MTU-nya lebih rendah daripada pautan asas, digabungkan dengan ICMP yang disekat, menghasilkan corak itu tepat: paket pertama tiba, yang besar hilang, dan pemindahan tergantung sehingga ia mencuba semula. Pembilang penghantaran semula di pihak klien menunjukkannya.

Perhatikan soket dan pembilang kernel semasa pemindahan yang sengaja perlahan berjalan. Jumlah penghantaran semula adalah kumulatif, jadi catatkannya sebelum dan selepas dan bukan 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

Penghantaran semula yang naik semasa pemindahan, bersama ping yang berjaya pada 1400 bait tetapi gagal pada 1472, menunjukkan masalah MTU dan bukan masalah pangkalan data. Menurunkan MTU antara muka terowong atau mengetatkan MSS di pintu masuk ialah pembetulan biasa, dan tiga arahan yang sama mengesahkannya selepas itu. Jika penghantaran semula kekal rata manakala pemindahan masih perlahan, lihat lebar jalur yang dibenarkan untuk terowong dan bukan kehilangan.

Urutan pendek meliputi laporan perlahan seterusnya: bandingkan satu pernyataan secara tempatan, pada LAN dan melalui VPN; baca pelaksanaan pelayan daripada EXPLAIN atau pg_stat_statements; kira panggilan sebelum dan selepas satu larian; dan perhatikan penghantaran semula semasa hasil besar bergerak. Setiap langkah mengecualikan satu sisi laluan. Tiada satu pun memerlukan meneka komponen mana yang perlu dimulakan semula.