زمان‌سنجی psql روی VPN: جدا کردن انتظار شبکه از کار پرس‌وجو

گزارش شما روی میزبان پایگاه داده حدود یک ثانیه و روی VPN چهار ثانیه تمام می‌شود، و عددی که psql چاپ می‌کند مستقیم به پایگاه داده اشاره می‌کند. آن عدد یک کرونومتر سمت کلاینت است، نه اندازه‌گیری سرور. دستور را در مسیر خروج، انتظار برای پاسخ و ردیف‌ها را در مسیر بازگشت پوشش می‌دهد. جدا کردن این سه، تنها راه دانستن این است که کدام طرف را تنظیم کنید.

تایمر psql واقعاً چه چیزی را می‌سنجد

\timing on یک کرونومتر را درست پیش از آنکه کلاینت دستور را در سوکت بنویسد شروع می‌کند و وقتی آخرین ردیف آن نتیجه خوانده شد متوقف می‌کند. اجرای خود سرور جایی درون همین پنجره است، و همچنین هر میلی‌ثانیه‌ای که بسته‌ها در تونل می‌گذرانند. هیچ چیز در خروجی آن‌ها را جدا نمی‌کند. EXPLAIN هم عددی چاپ می‌کند، اما آن را سرور تولید می‌کند و انتظار کلاینت را کنار می‌گذارد. دو عدد، دو پرسش متفاوت.

یک نشست آغاز کنید، زمان‌سنجی را روشن بگذارید و دستوری که برایتان مهم است را از داخل همان اجرا کنید. استفاده دوباره از نشست هزینه اتصال را از اندازه‌گیری اول بیرون نگه می‌دارد.

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

این عدد هنوز انتقال نتیجه را شامل می‌شود، که همین‌جا چیزی است که می‌خواهید. دیگر تبادل اتصال و احراز هویتی را که یک فراخوانی تازه اضافه می‌کرد شامل نمی‌شود.

یک دستور را روی سه مسیر مقایسه کنید

همان دستور را روی سوکت Unix، روی نشانی LAN و روی نام میزبان VPN اجرا کنید و سه را مقایسه کنید. اگر فقط آخری کند است، پرس‌وجو متغیر نیست.

هر فراخوانی اول متصل می‌شود و تنها بعد دستور را زمان‌سنجی می‌کند، پس هزینه اتصال بیرون از عدد می‌ماند در حالی که انتقال درون آن می‌ماند. بند WHERE، شمار ردیف‌ها و ماشین کلاینت را یکسان نگه دارید، وگرنه بار کاری را مقایسه می‌کنید نه مسیرها را.

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

در یک LAN تفاوت معمولاً آن‌قدر کوچک است که نادیده گرفته شود؛ روی VPN اغلب چنین نیست. مرتب کردن سه نتیجه بعداً می‌گوید به کدام انتظار حمله کنید.

سوکت محلی کند یعنی کار سرور یا برنامه‌ریزی. نتیجه کند LAN یعنی پرس‌وجو یا حجمی که جابه‌جا می‌کند. تنها کند بودن نتیجه VPN یعنی تأخیر، MTU یا پهنای باند موجود در مسیر، و هیچ تغییر ایندکسی آن را درست نمی‌کند.

زمان اجرای خود سرور را بیرون بکشید

سرور می‌تواند گزارش دهد چقدر زمان صرف کرده، و همان عدد چیزی است که باید کنار عدد کلاینت گذاشت. EXPLAIN همراه با ANALYZE دستور را اجرا می‌کند و زمان برنامه‌ریزی و اجرا را داخل طرح چاپ می‌کند، و BUFFERS رفتار حافظه نهان را می‌افزاید که اغلب اجرای گرم را در برابر سرد توضیح می‌دهد. مگر آنکه قصد نوشتن دارید، آن را به دستورهای نوشتن نشانه نگیرید. روی یک نسخه فقط-خواندنی امن‌ترین نقطه برای شروع است، و مرجع psql دامنه سمت کلاینت تایمر را مستند می‌کند.

برای دستورهایی که برنامه از پیش اجرا می‌کند، pg_stat_statements اجرای سمت سرور را بدون دوباره اجرا کردن چیزی جمع می‌کند. ردیف‌ها برای هر پرس‌وجوی نرمال‌شده هستند، پس یک ورودی می‌تواند چند نقطه فراخوانی را بپوشاند.

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

نام ستون‌ها با نسخه فرق می‌کند: PostgreSQL 13 و پس از آن mean_exec_time را نشان می‌دهند، در حالی که نسخه‌های قدیمی‌تر آن را mean_time می‌نامند، پس با \d pg_stat_statements بررسی کنید اگر آن پرس‌وجو خطا داد. این مقادیر برنامه‌ریزی را کنار می‌گذارند و سفر به کلاینت شما را هم کنار می‌گذارند. همین کنار گذاشتن است که آن‌ها را برای مقایسه سودمند می‌کند.

رفت‌وبرگشت‌ها را بشمارید، نه فقط میلی‌ثانیه‌ها را

گزارشی که از دویست دستور کوچک وابسته ساخته شده، هر تأخیری را که مسیر می‌افزاید در دویست ضرب می‌کند. یک دستور که یک میلیون ردیف برمی‌گرداند آن تأخیر را یک بار می‌پردازد و سپس مدت طولانی پهنای باند می‌پردازد. هر دو در تایمر کلاینت یکسان به نظر می‌رسند و به اصلاح‌های متضاد نیاز دارند.

شمار دستورها را اندازه بگیرید نه تخمین بزنید. جمع را بخوانید، گزارش را یک بار اجرا کنید، بعد دوباره بخوانید و کم کنید.

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

اگر دلتا بزرگ است و هر دستور روی سرور ارزان است، دسته‌بندی همان اصلاحی است که می‌ارزد. پرس‌وجوهای وابسته را ترکیب کنید، از یک دستور مجموعه‌محور استفاده کنید یا حلقه را از یک نشانگر بگردانید. در psql تعاملی، \set FETCH_COUNT 1000 نتیجه بزرگ را دسته‌دسته می‌رساند و رفتار انتقال و حافظه کلاینت را تغییر می‌دهد. کار سرور را کم نمی‌کند، پس پرس‌وجوی کند را نجات نمی‌دهد.

تونل را برای بازفرست‌ها و توقف‌های MTU بررسی کنید

وقتی دستورهای کوچک سریع‌اند و نتیجه‌های بزرگ در نیمه راه متوقف می‌شوند، پرس‌وجو معمولاً سالم است و مسیر نه. تونلی که MTU آن از پیوند زیرین کمتر است، همراه با ICMP مسدود، دقیقاً همین الگو را می‌سازد: بسته‌های اول می‌رسند، بسته‌های بزرگ ناپدید می‌شوند و انتقال تا تلاش دوباره معلق می‌ماند. شمارنده‌های بازفرست سمت کلاینت این را نشان می‌دهند.

هنگام اجرای یک انتقال عمداً کند، سوکت و شمارنده‌های هسته را بپایید. جمع‌های بازفرست تجمعی‌اند، پس آن‌ها را پیش و پس یادداشت کنید نه اینکه یک نمونه بخوانید.

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

بازفرست‌هایی که در طول انتقال بالا می‌روند، همراه با ping که در 1400 بایت موفق و در 1472 ناموفق است، به مشکل MTU اشاره دارند نه مشکل پایگاه داده. کاهش MTU واسط تونل یا بستن MSS در دروازه اصلاح معمول است و همان سه فرمان بعداً آن را تأیید می‌کند. اگر بازفرست‌ها مسطح می‌مانند در حالی که انتقال هنوز کند است، به پهنای باند مجاز تونل نگاه کنید نه به افت.

یک دنباله کوتاه گزارش کند بعدی را می‌پوشاند: یک دستور را محلی، روی LAN و روی VPN مقایسه کنید؛ اجرای سرور را از EXPLAIN یا pg_stat_statements بخوانید؛ فراخوانی‌ها را پیش و پس از یک اجرا بشمارید؛ و هنگام حرکت نتیجه بزرگ بازفرست‌ها را بپایید. هر گام یک طرف مسیر را کنار می‌گذارد. هیچ‌کدام نیاز ندارد حدس بزنید کدام مؤلفه را دوباره راه بیندازید.