Auna lokacin psql ta VPN: raba jiran cibiyar sadarwa daga aikin tambaya

Rahotonka yana ƙarewa cikin kusan daƙiƙa ɗaya a kan uwar garken bayanai kuma cikin huɗu ta VPN, kuma lambar da psql ke bugawa tana nuni kai tsaye ga bayanan. Wannan lambar agogon lokaci ne a gefen abokin ciniki, ba ma'aunin uwar garke ba. Tana rufe sanarwar a hanyar fita, jiran amsa, da layukan a hanyar dawowa. Raba waɗannan uku ita ce hanya ɗaya tilo ta sanin wane gefe za a daidaita.

Abin da ma'aunin lokaci na psql ke aunawa da gaske

\timing on yana kunna agogon lokaci kafin abokin ciniki ya rubuta sanarwar cikin soket kuma yana tsayawa lokacin da aka karanta layi na ƙarshe na wannan sakamakon. Aiƙatawar uwar garke kanta tana wani wuri cikin wannan taga, haka ma kowace millisekunde da fakitin ke kashewa cikin rami. Babu wani abu a cikin fitarwa da ke raba su. EXPLAIN ma yana buga lamba, amma uwar garke ce ke samar da ita kuma tana ware jiran abokin ciniki. Lambobi biyu, tambayoyi biyu daban-daban.

Fara zama ɗaya, bar auna lokaci a kunne, sannan ka gudanar da sanarwar da kake so daga cikinta. Sake amfani da zama yana ajiye kuɗin haɗi a wajen awo na farko.

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

Lambar har yanzu tana haɗe da canja wurin sakamakon, wanda shi ne abin da kake so a nan. Ba ta ƙara haɗe da musayar haɗi da tabbatarwa da sabon kira zai ƙara ba.

Kwatanta sanarwa ɗaya a kan hanyoyi uku

Gudanar da sanarwa ɗaya ta soket na Unix, ta adireshin LAN da ta sunan uwar garken VPN, sannan ka kwatanta su uku. Idan na ƙarshe ne kawai yake jinkiri, tambayar ba ita ce mai canzawa ba.

Kowace kira tana haɗawa da farko sannan ta auna sanarwar daga baya, don haka kuɗin haɗi yana waje da lambar yayin da canja wuri yake ciki. Ka riƙe sashen WHERE, adadin layuka da na'urar abokin ciniki daidai, in ba haka ba kana kwatanta nauyin aiki maimakon hanyoyi.

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

A kan LAN bambanci yawanci ƙanƙane ne da za a yi watsi da shi; ta VPN sau da yawa ba haka ba ne. Tsara sakamakon uku daga baya zai gaya maka wane jira za ka kai hari.

Soket na gida mai jinkiri yana nufin aikin uwar garke ko tsari. Sakamakon LAN mai jinkiri yana nufin tambayar ko girman da take motsawa. Sakamakon VPN ne kawai mai jinkiri yana nufin jinkiri, MTU ko bandwidth da ke akwai a kan hanya, kuma babu wani canjin index da ke gyara hakan.

Fito da lokacin aiwatarwa na uwar garke kanta

Uwar garke na iya bayar da rahoton lokacin da ta kashe, kuma wannan lambar ita ce za a riƙe da ta abokin ciniki. EXPLAIN tare da ANALYZE yana gudanar da sanarwar kuma yana buga lokacin tsari da aiwatarwa cikin shirin, yayin da BUFFERS ke ƙara halin ajiyar kuɗi wanda sau da yawa ke bayyana gudu mai dumi a kan mai sanyi. Kada ka nufe shi ga sanarwar rubutu sai dai idan kana da niyyar rubutu. A kan kwafin karatu kawai shi ne wuri mafi aminci don farawa, kuma bayanin psql yana rubuta iyakar ma'aunin lokaci a gefen abokin ciniki.

Ga sanarwar da manhajar ke gudanarwa tuni, pg_stat_statements yana tattara aiwatarwa a gefen uwar garke ba tare da sake gudanar da komai ba. Layukan na kowace tambaya da aka daidaita, don haka shigarwa ɗaya na iya rufe wuraren kira da yawa.

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

Sunayen ginshiƙi sun bambanta bisa bugu: PostgreSQL 13 da na baya suna nuna mean_exec_time, yayin da tsofaffin sigar suna kiransa mean_time, don haka duba da \d pg_stat_statements idan wannan tambayar ta ba da kuskure. Waɗannan ƙididdiga sun ware tsari kuma sun ware tafiya zuwa abokin cinikinka. Wannan warewa ce ta sa suke da amfani ga kwatancen.

Ƙididdige tafiye-tafiyen da-da-dawowa, ba millisekunde kawai ba

Rahoton da aka gina daga sanarwa ƙanana ɗari biyu masu dogaro yana ninka duk wani jinkiri da hanya ke ƙarawa da ɗari biyu. Sanarwa ɗaya da ke dawo da miliyan ɗaya layuka tana biyan wannan jinkirin sau ɗaya sannan tana biyan bandwidth na dogon lokaci. Dukansu suna kama da juna a agogon abokin ciniki, kuma suna buƙatar gyare-gyare masu sabani.

Auna adadin sanarwa maimakon ƙiyasta shi. Karanta jimillar, gudanar da rahoton sau ɗaya, sannan ka sake karantawa ka cire.

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

Idan bambancin yana da girma kuma kowace sanarwa tana da arha a uwar garke, tattarawa ita ce gyaran da ke biya. Haɗa tambayoyi masu dogaro, yi amfani da sanarwa ɗaya ta tushen saiti, ko ka jagoranci madauki daga cursor. A cikin psql mai mu'amala, \set FETCH_COUNT 1000 yana sa babban sakamako ya zo cikin batches kuma yana canza yadda canja wuri da ƙwaƙwalwar abokin ciniki ke kasancewa. Ba ya rage aikin uwar garke, don haka ba zai ceci tambaya mai jinkiri ba.

Duba ramin don sake aikawa da tsayawar MTU

Lokacin da ƙananan sanarwa suke da sauri kuma manyan sakamako suna tsayawa rabin hanya, tambayar yawanci tana lafiya kuma hanya ba. Ramin da MTU ɗinsa ya fi ƙasa da hanyar da ke ƙarƙashinsa, tare da ICMP da aka toshe, yana haifar da irin wannan tsarin daidai: fakiti na farko suna zuwa, manyan suna ɓacewa, kuma canja wurin yana rataye har sai ya sake gwadawa. Ƙididdigar sake aikawa a gefen abokin ciniki suna nuna hakan.

Kula da soket da ƙididdigar kernel yayin da canja wuri da gangan mai jinkiri ke gudana. Jimillar sake aikawa tana taruwa, don haka rubuta su kafin da bayan maimakon karanta samfurin guda ɗaya.

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

Sake aikawa da ke tashi yayin canja wuri, tare da ping da ke yin nasara a 1400 byte amma ya kasa a 1472, suna nuna matsalar MTU maimakon matsalar bayanai. Rage MTU na fuskar ramin ko matsawa MSS a ƙofar ita ce gyaran da aka saba, kuma waɗannan umarni uku ɗin suna tabbatar da shi daga baya. Idan sake aikawa ya tsaya cak yayin da canja wuri har yanzu yana jinkiri, duba bandwidth da aka ba wa ramin maimakon asara.

Gajeren jerin yana rufe rahoton jinkiri na gaba: kwatanta sanarwa ɗaya a gida, a kan LAN da ta VPN; karanta aiwatarwar uwar garke daga EXPLAIN ko pg_stat_statements; ƙididdige kira kafin da bayan gudu ɗaya; kuma ka kula da sake aikawa yayin da babban sakamakon ke motsi. Kowace mataki tana ware gefe ɗaya na hanyar. Babu ɗayansu da ke buƙatar hasashen wace ɓangare za a sake farawa.