VPN-এর মাধ্যমে psql টাইমিং: নেটওয়ার্কের অপেক্ষা ও কুয়েরির কাজ আলাদা করা

আপনার রিপোর্ট ডেটাবেস হোস্টে প্রায় এক সেকেন্ডে শেষ হয় আর 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 বা প্রাপ্য ব্যান্ডউইথ, এবং কোনো ইনডেক্স পরিবর্তন তা সারায় না।

সার্ভারের নিজের সম্পাদন সময় বের করুন

সার্ভার জানাতে পারে সে কত সময় কাটিয়েছে, এবং ক্লায়েন্টের সংখ্যার সঙ্গে তুলনীয় সংখ্যা সেটিই। ANALYZE সহ EXPLAIN স্টেটমেন্টটি চালায় এবং পরিকল্পনার ভেতরে পরিকল্পনা ও সম্পাদন সময় ছাপে, আর 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

স্থানান্তরের সময় যে পুনঃপ্রেরণ বাড়ে, সাথে 1400 বাইটে সফল কিন্তু 1472-এ ব্যর্থ ping, তা ডেটাবেস সমস্যা নয় বরং MTU সমস্যার দিকে ইশারা করে। টানেল ইন্টারফেসের MTU কমানো বা গেটওয়েতে MSS চেপে ধরা স্বাভাবিক সংশোধন, এবং একই তিনটি কমান্ড পরে তা নিশ্চিত করে। স্থানান্তর এখনও ধীর থাকা অবস্থায় পুনঃপ্রেরণ সমান থাকলে, ক্ষতির দিকে নয় বরং টানেলকে যে ব্যান্ডউইথ দেওয়া হয়েছে তার দিকে তাকান।

একটি ছোট ক্রম পরের ধীর রিপোর্ট ঢেকে দেয়: একটি স্টেটমেন্ট স্থানীয়ভাবে, LAN-এ ও VPN-এর মাধ্যমে তুলনা করুন; EXPLAIN বা pg_stat_statements থেকে সার্ভার সম্পাদন পড়ুন; একবার চালানোর আগে ও পরে আহ্বান গণনা করুন; এবং বড় ফলাফল চলার সময় পুনঃপ্রেরণ দেখুন। প্রতিটি ধাপ পথের এক পক্ষ বাদ দেয়। কোনওটিই কোন উপাদান রিস্টার্ট করবেন তা অনুমান করতে বলে না।