Ukulinganisa isikhathi se-psql nge-VPN: ukuhlukanisa ukulinda kwenethiwekhi nomsebenzi wombuzo

Umbiko wakho uphela cishe ngomzuzwana owodwa kumsingathi wedathabhethi nangemizuzwana emine nge-VPN, futhi inombolo ephrintwa yi-psql ikhomba ngqo kudathabhethi. Leyo nombolo iyisibali-sikhathi sohlangothi lwekhasimende, hhayi isilinganiso seseva. Ihlanganisa umusho endleleni yokuphuma, ukulinda impendulo kanye nemigqa esendleleni yokubuya. Ukuhlukanisa lezi zinto ezintathu iyona ndlela kuphela yokwazi ukuthi yiluphi uhlangothi okufanele ululungise.

Isibali-sikhathi se-psql silinganisa ini ngempela

\timing on siqala isibali-sikhathi ngaphambi nje kokuba ikhasimende libhale umusho kusokhethi futhi siyeka lapho kufundwe umugqa wokugcina walowo mphumela. Ukusebenza kweseva uqobo kutholakala ndawana thize ngaphakathi kwalelo windi, kanye nayo yonke imilisekendi amaphakethe ayichitha kuthaneli. Akukho lutho kokuphuma okuhlukanisa lezi. I-EXPLAIN nayo iphrinta inombolo, kodwa leyo yenziwa iseva futhi ayifaki ukulinda kwekhasimende. Izinombolo ezimbili, imibuzo emibili ehlukene.

Qala isikhathi esisodwa, shiya ukulinganisa isikhathi kuvuliwe, futhi gijima umusho okhathazayo ngaphakathi kwaso. Ukusebenzisa kabusha isikhathi kugcina izindleko zokuxhumana ngaphandle kwesilinganiso sokuqala.

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

Inombolo isabalisa ukudluliswa komphumela, okuyikho okudingayo lapha. Ayisakubandakanyi ukushintshana kokuxhumana nokuqinisekisa okungezwa yikholi entsha.

Qhathanisa umusho owodwa emigwaqweni emithathu

Gijima umusho ofanayo ngesokhethi ye-Unix, ngekheli le-LAN nangegama lomsingathi we-VPN, bese uqhathanisa yomithathu. Uma kuphela okokugcina okuhamba kancane, umbuzo awuyona inguquko.

Ikhodi ngayinye ixhuma kuqala bese ilinganisa umusho ngemuva kwalokho, ngakho izindleko zokuxhumana zihlala ngaphandle kwenombolo kuyilapho ukudluliswa kuhlala ngaphakathi. Gcina isigaba se-WHERE, inani lemigqa kanye nomshini wekhasimende kufana, ngaphandle kwalokho uqhathanisa imithwalo yomsebenzi hhayi imigwaqo.

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

Ku-LAN umehluko ngokuvamile mncane ngokwanele ukuba ungawunaki; nge-VPN kaningi awunjalo. Ukuhlela imiphumela emithathu kuzokutshela ukuthi yikuphi ukulinda okufanele ukuhlasela.

Isokhethi yendawo ehamba kancane isho umsebenzi weseva noma ukuhlela. Umphumela we-LAN ohamba kancane usho umbuzo noma umthamo owuhambisayo. Umphumela we-VPN kuphela ohamba kancane usho ukubambezeleka, i-MTU noma umkhawulo wokudlulisa okutholakalayo endleleni, futhi akukho shintsho lwenkomba olulungisayo lokho.

Khipha isikhathi sokusebenza seseva uqobo

Iseva ingabika ukuthi ichithe isikhathi esingakanani, futhi leyo yinombolo okufanele iqhathaniswe neyakhasimende. I-EXPLAIN ne-ANALYZE igijima umusho futhi iphrinta isikhathi sokuhlela nokusebenza ngaphakathi kohlelo, kuyilapho i-BUFFERS yengeza ukuziphatha kwekhashi okuvame ukuchaza ukugijima okushisayo uma kuqhathaniswa nokubandayo. Ungayiqondisi emishweni yokubhala ngaphandle uma uhlose ukubhala. Kukhophi yokufunda kuphela yilapho okuphephe kakhulu ukuqala, futhi incwadi yokubhekisa ye-psql ibhala ububanzi besibali-sikhathi ohlangothini lwekhasimende.

Emishweni esetshenziswa ngohlelo kakade, i-pg_stat_statements iqoqa ukusebenza kohlangothi lweseva ngaphandle kokuphinda igijime noma yini. Imigqa ingeyombuzo ojwayelekile, ngakho ukufaka okukodwa kungase kuhlanganise izindawo eziningi zokushaya ucingo.

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

Amagama amakholomu ayehluka ngenguqulo: i-PostgreSQL 13 nangemuva iveza i-mean_exec_time, kuyilapho izinguqulo ezindala ziyibiza nge-mean_time, ngakho hlola nge-\d pg_stat_statements uma lowo mbuzo unikeza iphutha. Lawa manani awafaki ukuhlela futhi awafaki uhambo oluya kukhasimende lakho. Ukukhishwa kwalokho kuyikho okubenza babe usizo ekuqhathaniseni.

Bala ukuhamba nokubuya, hhayi amamilisekendi kuphela

Umbiko owakhiwe ngemisho emincane engamakhulu amabili encike komunye nomunye uphinda phindeke noma yikuphi ukubambezeleka okwengezwa indlela ngamakhulu amabili. Umusho owodwa obuyisela isigidi semigqa ukhokha lokho kubambezeleka kanye bese ukhokha umkhawulo wokudlulisa isikhathi eside. Kokubili kubonakala kufana esibalini-sikhathi sekhasimende, futhi kudinga izilungiso eziphambene.

Linganisa inani lemisho esikhundleni sokuliqagela. Funda isamba, gijima umbiko kanye, bese uwufunda futhi bese ukhipha.

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

Uma i-delta inkulu futhi umusho ngamunye ushibhile kuseva, ukubhampa ngamaqembu yisilungiso esizuzayo. Hlanganisa imibuzo encikile, sebenzisa umusho owodwa osuselwe kusethi, noma qondisa iluphu kusukela kusikhombisi. Ku-psql yokuxhumana, \set FETCH_COUNT 1000 umphumela omkhulu ufika ngamaqembu futhi kushintsha ukuziphatha kokudluliswa nenkumbulo yekhasimende. Akunciphisi umsebenzi weseva, ngakho ngeke kusindise umbuzo ohamba kancane.

Hlola ithaneli ngokuthumela kabusha nokuma kwe-MTU

Lapho imisho emincane ishesha kodwa imiphumela emikhulu ima phakathi nendlela, umbuzo uvamise ukuba kahle kanti indlela ayikho. Ithaneli eline-MTU ephansi kunesixhumanisi esingaphansi, kanye ne-ICMP evinjiwe, likhiqiza impela lelo phethini: amaphakethe okuqala ayafika, amakhulu ayanyamalala, futhi ukudluliswa kulenga kuze kube yilapho lizama futhi. Izibali zokuthumela kabusha ohlangothini lwekhasimende zikubonisa lokho.

Buka isokhethi nezibali zekhaneli ngesikhathi ukudluliswa okuhamba kancane ngamabomu kuqhubeka. Izamba zokuthumela kabusha ziyanyuka, ngakho zibhale ngaphambi nangemuva esikhundleni sokufunda isampula elilodwa.

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

Ukuthumela kabusha okukhulayo ngesikhathi sokudluliswa, kanye ne-ping ephumelelayo kumabhayithi angu-1400 kodwa ehluleka kwangu-1472, kukhombisa inkinga ye-MTU hhayi inkinga yedathabhethi. Ukwehlisa i-MTU yesixhumi sothaneli noma ukucindezela i-MSS esangweni yisilungiso esivamile, futhi imiyalo emithathu efanayo iyaqinisekisa ngemuva kwalokho. Uma ukuthumela kabusha kuhlala kufana kuyilapho ukudluliswa kusenjalo kuhamba kancane, bheka umkhawulo wokudlulisa ovunyelwe ithaneli hhayi ukulahleka.

Ukulandelana okufushane kuhlanganisa umbiko ohamba kancane olandelayo: qhathanisa umusho owodwa endaweni, ku-LAN nange-VPN; funda ukusebenza kweseva ku-EXPLAIN noma ku-pg_stat_statements; bala izingcingo ngaphambi nangemuva kokugijima kanye; futhi ubuke ukuthumela kabusha ngesikhathi umphumela omkhulu unyakaza. Isinyathelo ngasinye sikhipha uhlangothi olulodwa lwendlela. Akukho nokukodwa okudinga ukuqagela ukuthi iyiphi ingxenye okufanele iqalwe kabusha.