Cov ntawv sau tseg ntawm Alexey Lesovsky's 2015 tsab ntawv ceeb toom "Dub tob rau hauv PostgreSQL cov txheeb cais sab hauv"
Disclaimer los ntawm tus sau tsab ntawv ceeb toom: Kuv nco ntsoov tias daim ntawv tshaj tawm no yog hnub tim Kaum Ib Hlis 2015 - ntau dua 4 xyoo tau dhau los thiab ntau lub sijhawm tau dhau mus. Lub version 9.4 tau tham hauv tsab ntawv ceeb toom tsis tau txais kev txhawb nqa lawm. Hauv 4 xyoo dhau los, 5 qhov kev tshaj tawm tshiab tau tshaj tawm nyob rau hauv uas muaj ntau qhov kev tsim kho tshiab, kev txhim kho thiab kev hloov pauv ntawm kev txheeb cais, thiab qee qhov ntawm cov khoom siv tau dhau los thiab tsis cuam tshuam. Raws li kuv tshuaj xyuas, kuv sim kos cov chaw no kom tsis txhob yuam kev nyeem ntawv. Kuv tsis tau sau dua cov nqe lus no, muaj ntau ntawm lawv thiab qhov tshwm sim yuav yog ib daim ntawv qhia txawv kiag li.
Lub PostgreSQL DBMS yog lub tshuab loj loj, thiab cov txheej txheem no muaj ntau lub subsystems, kev sib koom ua haujlwm uas cuam tshuam ncaj qha rau kev ua haujlwm ntawm DBMS. Thaum lub sijhawm ua haujlwm, kev txheeb cais thiab cov ntaub ntawv hais txog kev ua haujlwm ntawm cov khoom raug sau, uas tso cai rau koj los ntsuas qhov ua tau zoo ntawm PostgreSQL thiab ntsuas kev ua haujlwm. Txawm li cas los xij, muaj ntau cov ntaub ntawv no thiab nws tau nthuav tawm hauv daim ntawv yooj yim. Kev ua cov ntaub ntawv no thiab txhais nws yog qee zaum ua haujlwm tsis tseem ceeb, thiab "zoo" ntawm cov cuab yeej thiab cov khoom siv tau yooj yim tuaj yeem cuam tshuam txawm tias DBA siab heev.
Nyob zoo tav su Kuv lub npe yog Aleksey. Raws li Ilya hais, Kuv yuav tham txog PostgreSQL txheeb cais.
PostgreSQL kev txheeb cais. PostgreSQL muaj ob qhov kev txheeb cais. Cov ntaub ntawv txheeb cais uas yuav tau tham. Thiab teem caij txheeb cais txog kev faib cov ntaub ntawv. Kuv yuav tham tshwj xeeb txog PostgreSQL kev txheeb cais, uas tso cai rau peb los txiav txim qhov kev ua tau zoo thiab qee yam txhim kho nws.
Kuv mam li qhia koj yuav ua li cas siv cov txheeb cais kom daws tau ntau yam teeb meem uas koj muaj lossis tej zaum yuav muaj.
Dab tsi yuav tsis nyob hauv daim ntawv qhia? Nyob rau hauv tsab ntawv ceeb toom kuv yuav tsis kov ntawm lub sijhawm teem sijhawm, vim tias ... Qhov no yog ib lub ntsiab lus cais rau ib daim ntawv qhia cais txog yuav ua li cas cov ntaub ntawv khaws cia hauv cov ntaub ntawv thiab yuav ua li cas cov lus nug npaj tau txais lub tswv yim ntawm cov yam ntxwv zoo thiab ntau npaum li cas ntawm cov ntaub ntawv no.
Thiab yuav tsis muaj cov cuab yeej tshuaj xyuas, Kuv yuav tsis piv ib yam khoom rau lwm tus. Yuav tsis muaj kev tshaj tawm. Cia peb muab tso tseg.
Kuv xav qhia koj tias kev siv cov txheeb cais muaj txiaj ntsig. Nws yog qhov tsim nyog. Nws muaj kev nyab xeeb siv. Txhua yam peb xav tau yog SQL tsis tu ncua thiab kev paub txog SQL.
Thiab cia peb tham txog dab tsi txheeb cais los xaiv los daws teeb meem.
Yog tias peb saib PostgreSQL thiab khiav cov lus txib ntawm lub operating system los saib cov txheej txheem, peb yuav pom "black box". Peb yuav pom qee cov txheej txheem uas ua ib yam dab tsi, thiab los ntawm lub npe peb tuaj yeem xav txog qhov lawv ua nyob rau ntawd, lawv ua dab tsi. Tab sis, hauv qhov tseeb, nws yog lub thawv dub; peb tsis tuaj yeem saib sab hauv.
Peb tuaj yeem pom CPU thauj khoom hauv top, peb tuaj yeem saib kev siv lub cim xeeb los ntawm qee qhov kev siv hluav taws xob, tab sis peb yuav tsis tuaj yeem saib hauv PostgreSQL. Rau qhov no peb xav tau lwm yam cuab yeej.
Thiab txuas ntxiv mus, kuv yuav qhia koj tias lub sijhawm siv sijhawm. Yog tias peb xav txog PostgreSQL hauv daim duab zoo li no, peb tuaj yeem teb qhov twg siv sijhawm. Cov no yog ob yam: nws yog ua cov neeg thov kev thov los ntawm cov ntawv thov thiab cov haujlwm tom qab uas PostgreSQL ua kom nws tus kheej khiav.
Yog tias peb pib saib ntawm kaum sab laug sab saum toj, peb tuaj yeem pom tias cov neeg thov kev thov raug ua li cas. Qhov kev thov yog los ntawm daim ntawv thov thiab qhib kev sib ntsib rau cov neeg siv khoom ntxiv. Qhov kev thov raug xa mus rau tus teem sijhawm. Tus teem sij hawm tsim ib qho kev npaj nug. Xa nws ntxiv rau kev ua tiav. Muaj qee yam kev thaiv cov ntaub ntawv tawm tswv yim / tso tawm cuam tshuam nrog cov ntxhuav thiab qhov ntsuas. Cov ntaub ntawv tsim nyog tau nyeem los ntawm cov disks rau hauv lub cim xeeb rau hauv thaj chaw tshwj xeeb "sib koom buffers". Cov txiaj ntsig ntawm qhov kev thov, yog tias lawv hloov kho, tshem tawm, raug kaw hauv kev sib pauv hauv WAL. Qee cov ntaub ntawv txheeb cais xaus rau hauv lub cav lossis tus sau cov ntaub ntawv txheeb cais. Thiab qhov tshwm sim ntawm qhov kev thov raug xa rov qab mus rau tus neeg siv khoom. Tom qab ntawd tus neeg siv khoom tuaj yeem rov ua txhua yam ntxiv nrog kev thov tshiab.
Yuav ua li cas txog cov hauj lwm tom qab thiab cov txheej txheem keeb kwm yav dhau? Peb muaj ob peb txheej txheem uas ua kom cov ntaub ntawv nce thiab khiav hauv kev ua haujlwm ib txwm muaj. Cov txheej txheem no tseem yuav raug cuam tshuam rau hauv daim ntawv tshaj tawm: autovacuum, checkpointer, replication-related processes, backgroundwriter. Kuv yuav kov rau lawv txhua tus raws li kuv qhia.
Muaj teeb meem dab tsi nrog kev txheeb cais?
- Muaj ntau cov ntaub ntawv. PostgreSQL 9.4 muab 109 metrics rau saib cov ntaub ntawv txheeb cais. Txawm li cas los xij, yog tias cov ntaub ntawv khaws ntau lub rooj, schemas, databases, ces tag nrho cov kev ntsuas no yuav tsum tau muab sib npaug los ntawm tus lej ntawm cov ntxhuav, databases. Ntawd yog, muaj cov ntaub ntawv ntau ntxiv. Thiab nws yog ib qho yooj yim heev rau poob rau hauv nws.
- Qhov teeb meem tom ntej no yog cov txheeb cais sawv cev los ntawm cov txee. Yog tias peb saib cov txheeb cais no, peb yuav pom cov txee nce tas li. Thiab yog tias ntau lub sijhawm tau dhau los txij li cov txheeb cais tau rov pib dua, peb yuav pom qhov muaj nuj nqis hauv billions. Thiab lawv tsis qhia peb dab tsi.
- Tsis muaj dab neeg. Yog tias koj muaj qee yam tsis ua haujlwm, qee yam poob 15-30 feeb dhau los, koj yuav tsis tuaj yeem siv cov txheeb cais thiab pom dab tsi tshwm sim 15-30 feeb dhau los. Qhov no yog teeb meem.
- Qhov tsis muaj lub cuab yeej ua rau hauv PostgreSQL yog qhov teeb meem. Cov neeg tsim tawm kernel tsis muab kev siv hluav taws xob. Lawv tsis muaj dab tsi li ntawd. Lawv tsuas yog muab txheeb cais hauv cov ntaub ntawv. Siv nws, thov rau nws, ua txhua yam koj xav tau.
- Txij li thaum tsis muaj cov cuab yeej ua rau hauv PostgreSQL, qhov no ua rau muaj teeb meem ntxiv. Ntau yam cuab yeej thib peb. Txhua lub tuam txhab uas muaj ntau dua los yog tsawg dua txhais tes yog sim sau nws tus kheej qhov kev pab cuam. Thiab yog li ntawd, lub zej zog muaj ntau yam cuab yeej siv los ua haujlwm nrog kev txheeb cais. Thiab qee cov cuab yeej muaj qee qhov peev xwm, lwm yam cuab yeej tsis muaj lwm yam peev xwm, lossis muaj qee qhov peev xwm tshiab. Thiab qhov xwm txheej tshwm sim uas koj yuav tsum siv ob, peb lossis plaub lub cuab yeej uas sib tshooj thiab muaj cov haujlwm sib txawv. Qhov no tsis kaj siab heev.
Dab tsi ntxiv los ntawm qhov no? Nws yog ib qho tseem ceeb kom muaj peev xwm coj cov txheeb cais ncaj qha, yog li tsis yog nyob ntawm cov kev pab cuam, lossis qee yam txhim kho cov kev pab cuam koj tus kheej: ntxiv qee qhov haujlwm kom tau txais txiaj ntsig ntawm koj tus kheej.
Thiab koj xav tau kev paub yooj yim ntawm SQL. Txhawm rau kom tau txais qee cov ntaub ntawv los ntawm kev txheeb cais, koj yuav tsum tsim cov lus nug SQL, piv txwv li koj yuav tsum paub yuav ua li cas xaiv thiab koom ua ke.
Kev txheeb cais qhia peb ntau yam. Lawv tuaj yeem muab faib ua pawg.
- Thawj qeb yog cov xwm txheej tshwm sim hauv cov ntaub ntawv. Qhov no yog thaum qee qhov xwm txheej tshwm sim hauv cov ntaub ntawv: qhov kev thov, nkag mus rau lub rooj, autovacuum, commits, ces cov no yog tag nrho cov xwm txheej. Cov suav suav nrog cov xwm txheej no tau nce ntxiv. Thiab peb tuaj yeem taug qab cov xwm txheej no.
- Cov qeb thib ob yog cov khoom ntawm cov khoom xws li cov ntxhuav thiab cov ntaub ntawv. Lawv muaj cov khoom. Qhov no yog qhov loj ntawm cov ntxhuav. Peb tuaj yeem taug qab kev loj hlob ntawm cov ntxhuav thiab kev loj hlob ntawm indexes. Peb tuaj yeem pom cov kev hloov pauv hauv dynamics.
- Thiab qeb thib peb yog lub sijhawm siv rau ntawm qhov kev tshwm sim. Ib qho kev thov yog ib qho kev tshwm sim. Nws muaj nws tus kheej qhov tshwj xeeb ntsuas ntawm lub sijhawm. Pib ntawm no, xaus ntawm no. Peb tuaj yeem taug qab nws. Txawm hais tias lub sijhawm nws yuav siv los nyeem ib qho thaiv ntawm disk lossis sau nws. Tej yam zoo li no kuj tau taug qab.
Cov peev txheej ntawm kev txheeb cais tau nthuav tawm raws li hauv qab no:
- Hauv kev sib koom nco (sib koom buffers) muaj ib ntu rau khaws cia cov ntaub ntawv zoo li qub, kuj tseem muaj cov txee uas tau nce ntxiv thaum qee yam xwm txheej tshwm sim, lossis qee lub sijhawm tshwm sim hauv kev ua haujlwm ntawm cov ntaub ntawv.
- Tag nrho cov txee no tsis siv tau rau tus neeg siv thiab tsis tuaj yeem nkag mus rau tus thawj tswj hwm. Cov no yog cov khoom qis. Txhawm rau nkag mus rau lawv, PostgreSQL muab qhov sib txuas hauv daim ntawv ntawm SQL ua haujlwm. Peb tuaj yeem tsim cov kev xaiv pov tseg uas siv cov haujlwm no thiab tau txais qee yam kev ntsuas (lossis cov ntsuas ntsuas).
- Txawm li cas los xij, kev siv cov haujlwm no tsis yog ib txwm yooj yim, yog li kev ua haujlwm yog lub hauv paus rau kev pom (VIEWs). Cov no yog cov rooj virtual uas muab kev txheeb cais ntawm ib lub subsystem tshwj xeeb, lossis ntawm qee yam txheej xwm hauv database.
- Cov embedded views (VIEWs) yog thawj tus neeg siv interface rau kev ua haujlwm nrog kev txheeb cais. Lawv muaj nyob rau hauv lub neej ntawd yam tsis muaj kev teeb tsa ntxiv, koj tuaj yeem siv tam sim ntawd, saib lawv, thiab nqa cov ntaub ntawv los ntawm lawv. Thiab tom qab ntawd muaj kev sib koom ua ke. Kev koom tes yog official. Koj tuaj yeem nruab lub pob postgresql-contrib (piv txwv li, postgresql94-contrib), thauj cov qauv uas yuav tsum tau ua hauv kev teeb tsa, qhia qhov tsis haum rau nws, rov pib PostgreSQL thiab koj tuaj yeem siv nws. (Nco tseg. Nyob ntawm qhov kev faib tawm, nyob rau hauv cov versions tsis ntev los no cov pob khoom sib koom ua ke yog ib feem ntawm pob tseem ceeb).
- Thiab muaj kev koom tes tsis raug cai. Lawv tsis suav nrog tus qauv PostgreSQL faib. Lawv yuav tsum tau muab tso ua ke los yog ntsia raws li lub tsev qiv ntawv. Cov kev xaiv tuaj yeem sib txawv heev, nyob ntawm seb tus tsim tawm ntawm qhov kev koom tes tsis raug cai no tuaj nrog.
Cov swb no nthuav tawm tag nrho cov VIEWs thiab qee qhov haujlwm uas muaj nyob hauv PostgreSQL 9.4. Raws li peb pom, muaj ntau ntawm lawv. Thiab nws yooj yim heev kom tsis meej pem yog tias koj ntsib nws thawj zaug.
Txawm li cas los xij, yog tias peb muab daim duab dhau los ΠΠ°ΠΊ ΡΡΠ°ΡΠΈΡΡΡ Π²ΡΠ΅ΠΌΡ Π½Π° PostgreSQL thiab sib xws nrog cov npe no, peb tau txais daim duab no. Peb tuaj yeem siv txhua qhov saib (VIEWs) lossis txhua qhov haujlwm rau ib lub hom phiaj lossis lwm qhov kom tau txais cov txheeb cais sib raug thaum PostgreSQL tab tom khiav. Thiab peb tuaj yeem tau txais qee cov ntaub ntawv hais txog kev ua haujlwm ntawm subsystem.
Thawj qhov peb yuav saib yog pg_stat_database. Raws li peb tau pom, qhov no yog kev ua yeeb yam. Muaj ntau cov ntaub ntawv nyob rau hauv nws. Cov ntaub ntawv sib txawv tshaj plaws. Thiab nws muab kev paub zoo heev ntawm qhov tshwm sim hauv peb cov ntaub ntawv.
Dab tsi muaj txiaj ntsig peb tuaj yeem nqa los ntawm qhov ntawd? Cia peb pib nrog tej yam yooj yim.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Thawj qhov peb tuaj yeem saib yog qhov cache ntaus feem pua. Cache ntaus tus nqi yog qhov ntsuas tau zoo. Nws tso cai rau koj los kwv yees ntau npaum li cas cov ntaub ntawv raug coj los ntawm kev sib koom buffers cache thiab nyeem ntau npaum li cas los ntawm disk.
Nws yog qhov tseeb tias qhov ntau cache hits peb muaj, qhov zoo. Peb ntsuas qhov ntsuas no raws li feem pua. Thiab, piv txwv li, yog tias peb feem pua ββββntawm cov cache hits ntau dua 90%, ces qhov no yog qhov zoo. Yog tias nws poob qis dua 90%, nws txhais tau tias peb tsis muaj lub cim xeeb txaus los tuav lub taub hau kub ntawm cov ntaub ntawv hauv lub cim xeeb. Thiab txhawm rau siv cov ntaub ntawv no, PostgreSQL raug yuam kom nkag mus rau lub disk thiab qhov no qeeb dua yog tias cov ntaub ntawv tau nyeem los ntawm lub cim xeeb. Thiab koj yuav tsum xav txog kev nce lub cim xeeb: nce kev sib koom buffers, lossis nce hardware nco (RAM).
select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;Dab tsi ntxiv koj tuaj yeem nqa los ntawm qhov kev ua yeeb yam no? Koj tuaj yeem pom qhov tsis txaus ntseeg tshwm sim hauv cov ntaub ntawv. Dab tsi yog qhia ntawm no? Muaj commits, rollbacks, creation ntawm ib ntus cov ntaub ntawv, lawv loj, deadlocks thiab tsis sib haum xeeb.
Peb tuaj yeem siv qhov kev thov no. Qhov no SQL yog yooj yim heev. Thiab peb tuaj yeem saib cov ntaub ntawv no ntawm no.
Thiab ntawm no yog cov nqi pib. Peb saib qhov piv ntawm kev cog lus thiab rollbacks. Kev cog lus yog qhov kev lees paub ua tiav ntawm kev lag luam. Rollbacks yog rollback, piv txwv li kev lag luam tau ua qee yam haujlwm, lim cov ntaub ntawv, suav ib yam dab tsi, thiab tom qab ntawd qhov ua tsis tiav tau tshwm sim thiab cov txiaj ntsig ntawm kev lag luam raug pov tseg. Qhov ntawd yog tus naj npawb ntawm rollbacks tas li nce yog qhov tsis zoo. Thiab koj yuav tsum ua li cas zam lawv, thiab kho cov cai kom qhov no tsis tshwm sim.
Kev tsis sib haum xeeb muaj feem xyuam nrog kev rov ua dua tshiab. Thiab lawv kuj yuav tsum zam. Yog tias koj muaj qee cov lus nug uas tau ua tiav ntawm qhov kev hloov pauv thiab tsis sib haum xeeb tshwm sim, ces koj yuav tsum txheeb xyuas cov kev tsis sib haum xeeb no thiab pom tias muaj dab tsi tshwm sim. Cov ntsiab lus tuaj yeem pom hauv cov cav. Thiab tshem tawm cov xwm txheej tsis sib haum xeeb kom cov ntawv thov ua haujlwm yam tsis muaj qhov yuam kev.
Deadlocks kuj yog qhov xwm txheej phem. Thaum kev thov sib ntaus sib tua rau cov peev txheej, ib qho kev thov nkag mus rau ib qho peev txheej thiab muab lub xauv, qhov kev thov thib ob nkag mus rau qhov chaw thib ob thiab tseem muab lub xauv, thiab tom qab ntawd ob qhov kev thov nkag mus rau ib leeg cov peev txheej thiab thaiv thaum tos cov neeg nyob ze tso lub xauv. Qhov no kuj yog ib qho teeb meem. Lawv yuav tsum tau hais nyob rau theem ntawm rewriting daim ntawv thov thiab serializing nkag mus rau cov peev txheej. Thiab yog tias koj pom tias koj qhov kev tuag tsis tu ncua, koj yuav tsum tau saib cov ntsiab lus hauv cov cav, txheeb xyuas cov xwm txheej uas tshwm sim thiab pom tias qhov teeb meem yog dab tsi.
Cov ntaub ntawv ib ntus (temp_files) kuj tsis zoo. Thaum tus neeg siv thov tsis muaj lub cim xeeb txaus kom haum rau kev ua haujlwm, cov ntaub ntawv ib ntus, nws tsim cov ntaub ntawv ntawm disk. Thiab tag nrho cov haujlwm uas nws tuaj yeem ua tau nyob rau hauv ib ntus tsis nyob hauv lub cim xeeb pib ua rau ntawm disk. Nws qeeb. Qhov no ua rau kom lub sij hawm ua lus nug. Thiab tus neeg siv khoom uas xa daim ntawv thov rau PostgreSQL yuav tau txais cov lus teb me ntsis tom qab. Yog tias tag nrho cov haujlwm no tau ua tiav hauv kev nco, Postgres yuav teb sai dua thiab tus neeg siv yuav tos tsawg dua.
Pg_stat_bgwriter - Qhov kev pom no piav qhia txog kev ua haujlwm ntawm ob lub PostgreSQL keeb kwm yav dhau los: qhov no checkpointer ΠΈ background writer.
Ua ntej, cia saib cov ntsiab lus tswj, lub npe hu ua. checkpoints. Cov ntsiab lus tswj hwm yog dab tsi? Lub checkpoint yog ib txoj hauj lwm nyob rau hauv cov kev sib pauv log qhia tias tag nrho cov ntaub ntawv hloov nyob rau hauv lub cav tau ua tiav synchronized nrog cov ntaub ntawv ntawm disk. Cov txheej txheem, nyob ntawm qhov chaw ua haujlwm thiab kev teeb tsa, tuaj yeem ntev thiab feem ntau yog synchronizing cov nplooj ntawv qias neeg hauv kev sib koom buffers nrog cov ntaub ntawv ntawm disk. Nws yog dab tsi? Yog tias PostgreSQL tau txuas ntxiv mus rau disk thiab nqa cov ntaub ntawv los ntawm qhov ntawd, thiab sau cov ntaub ntawv ntawm txhua qhov nkag, nws yuav qeeb. Yog li ntawd, PostgreSQL muaj ib ntu nco uas nws qhov loj me nyob ntawm qhov chaw hauv kev teeb tsa. Postgres khaws cov ntaub ntawv nyob hauv lub cim xeeb no rau kev ua tom qab lossis nug. Thaum thov hloov cov ntaub ntawv, nws raug hloov. Thiab peb tau txais ob versions ntawm cov ntaub ntawv. Ib qho yog nyob rau hauv peb lub cim xeeb, lwm qhov yog nyob rau hauv disk. Thiab ib ntus koj yuav tsum tau synchronize cov ntaub ntawv no. Peb yuav tsum synchronize dab tsi hloov hauv nco rau disk. Rau qhov no koj xav tau checkpoints.
Checkpoint mus los ntawm kev sib koom buffers, kos cov nplooj ntawv qias neeg uas lawv xav tau rau qhov chaw kuaj xyuas. Tom qab ntawd nws pib qhov thib ob dhau los ntawm kev sib koom buffers. Thiab cov nplooj ntawv uas tau cim rau checkpoint, nws twb synchronizes lawv. Li no cov ntaub ntawv yog synchronized nrog lub disk.
Muaj ob hom kev kuaj xyuas. Ib qhov chaw kuaj xyuas tau ua tiav nrog lub sijhawm. Qhov chaw kuaj xyuas no muaj txiaj ntsig thiab zoo - checkpoint_timed. Thiab muaj cov chaw kuaj xyuas ntawm kev thov - checkpoint required. Qhov chaw kuaj xyuas no tshwm sim thaum peb muaj cov ntaub ntawv loj heev. Peb tau sau ntau daim ntawv teev kev lag luam. Thiab PostgreSQL ntseeg tias nws yuav tsum tau synchronize tag nrho cov no kom sai li sai tau, ua qhov chaw kuaj xyuas thiab txav mus.
Thiab yog tias koj saib cov txheeb cais pg_stat_bgwriter thiab pom yam koj muaj checkpoint_req ntau loj dua checkpoint_timed, ces qhov no yog phem. Ua cas phem? Qhov no txhais tau hais tias PostgreSQL nyob rau hauv qhov kev ntxhov siab tas li thaum nws xav tau sau cov ntaub ntawv rau disk. Lub sijhawm kuaj xyuas tsis tshua muaj kev ntxhov siab thiab ua raws li lub sijhawm sab hauv thiab yog hom kev sib kis raws sijhawm. PostgreSQL muaj lub peev xwm los ncua kev ua haujlwm thiab tsis cuam tshuam rau disk subsystem. Qhov no pab tau rau PostgreSQL. Thiab cov lus nug uas raug tua thaum lub sijhawm kuaj xyuas yuav tsis muaj kev ntxhov siab los ntawm qhov tseeb tias lub disk subsystem tsis khoom.
Thiab txhawm rau kho qhov chaw kuaj xyuas muaj peb yam tsis muaj:
-
Ρheckpoint_segments. -
Ρheckpoint_timeout. -
Ρheckpoint_competion_target.
Lawv tso cai rau koj los tswj cov haujlwm ntawm kev tswj cov ntsiab lus. Tab sis kuv yuav tsis nyob ntawm lawv. Lawv lub hwj chim yog ib lub ntsiab lus txawv.
Ceeb toom: Cov version 9.4 tau tham hauv tsab ntawv ceeb toom tsis cuam tshuam ntxiv lawm. Nyob rau hauv niaj hnub versions ntawm PostgreSQL tus parameter checkpoint_segments hloov los ntawm tsis min_wal_size ΠΈ max_wal_size.
Lub subsystem tom ntej yog tus sau keeb kwm yav dhau - background writer. Nws ua dab tsi? Nws khiav tas li nyob rau hauv lub voj tsis kawg. Tshawb cov nplooj ntawv hauv cov buffers sib koom thiab pov tseg cov nplooj ntawv qias neeg uas nws pom rau disk. Yog li, nws pab tus checkpointer ua haujlwm tsawg dua thaum lub sijhawm ua haujlwm checkpoint.
Dab tsi ntxiv yog nws xav tau rau? Nws muab cov kev xav tau rau nplooj ntawv dawb paug hauv kev sib koom ua ke yog tias lawv xav tau dheev (ntau ntau thiab tam sim) kom haum rau cov ntaub ntawv. Xav tias muaj xwm txheej tshwm sim thaum nplooj ntawv dawb yuav tsum tau ua kom tiav qhov kev thov thiab lawv twb tau nyob rau hauv qhov sib koom buffers. Postgresive backend nws tsuas khaws lawv thiab siv xwb, nws tsis tas yuav ntxuav dab tsi ntawm nws tus kheej. Tab sis yog tias tam sim ntawd tsis muaj nplooj ntawv zoo li no, lub backend nres ua haujlwm thiab pib tshawb cov nplooj ntawv pov tseg rau hauv disk thiab coj lawv rau nws tus kheej xav tau - uas cuam tshuam tsis zoo rau lub sijhawm ntawm qhov kev thov tam sim no. Yog tias koj pom tias koj muaj parameter maxwritten_clean loj, qhov no txhais tau hais tias tus kws sau keeb kwm yav dhau los tsis ua nws txoj haujlwm thiab koj yuav tsum tau nce cov kev txwv bgwriter_lru_maxpages, kom nws tuaj yeem ua haujlwm ntau dua hauv ib lub voj voog, tshem ntau nplooj ntawv.
Thiab lwm qhov qhia tau zoo heev yog buffers_backend_fsync. Backends tsis fsync vim nws qeeb. Lawv dhau fsync li IO pawg checkpointer. Tus checkpointer muaj nws tus kheej queue, nws ib ntus txheej txheem fsync thiab synchronizes nplooj ntawv hauv nco nrog cov ntaub ntawv ntawm disk. Yog hais tias lub queue ntawm lub checkpointer yog loj thiab tag nrho, ces lub backend yog yuam kom ua fsync nws tus kheej thiab qhov no slows down txoj hauj lwm ntawm backend., i.e. tus neeg siv yuav tau txais cov lus teb tom qab nws ua tau. Yog tias koj pom tias koj tus nqi ntau dua li xoom, ces qhov no yog qhov teeb meem thiab Koj yuav tsum tau them sai sai rau tus sau keeb kwm yav dhau los qhov chaw thiab tseem ntsuas qhov kev ua tau zoo ntawm disk subsystem.
Ceeb toom: _Cov ntawv hauv qab no piav qhia txog cov kev txheeb xyuas txheeb xyuas cuam tshuam nrog kev rov ua dua. Feem ntau ntawm cov saib thiab cov npe ua haujlwm tau hloov npe hauv Postgres 10. Lub ntsiab lus ntawm kev hloov npe yog hloov pauv. xlog rau wal ΠΈ location rau lsn hauv kev ua haujlwm / saib cov npe, thiab lwm yam. Piv txwv, muaj nuj nqi pg_xlog_location_diff() tau hloov npe rau pg_wal_lsn_diff()._
Peb muaj ntau yam ntawm no thiab. Tab sis peb tsuas yog xav tau cov khoom ntsig txog qhov chaw.
Yog tias peb pom tias tag nrho cov txiaj ntsig sib npaug, ces qhov no yog qhov kev xaiv zoo tshaj plaws thiab cov khoom siv rov ua dua tsis poob qab tus tswv.
Qhov no hexadecimal txoj hauj lwm ntawm no yog txoj hauj lwm nyob rau hauv kev sib pauv log. Nws pheej nce ntxiv yog tias muaj ib qho haujlwm hauv cov ntaub ntawv: ntxig, tshem tawm, thiab lwm yam.
ΡΠΊΠΎΠ»ΡΠΊΠΎ Π·Π°ΠΏΠΈΡΠ°Π½ΠΎ xlog Π² Π±Π°ΠΉΡΠ°Ρ
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
Π»Π°Π³ ΡΠ΅ΠΏΠ»ΠΈΠΊΠ°ΡΠΈΠΈ Π² Π±Π°ΠΉΡΠ°Ρ
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
Π»Π°Π³ ΡΠ΅ΠΏΠ»ΠΈΠΊΠ°ΡΠΈΠΈ Π² ΡΠ΅ΠΊΡΠ½Π΄Π°Ρ
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());Yog tias tej yam no txawv, ces muaj qee yam lag luam. Lag yog qhov lag luam ntawm tus qauv thiab tus tswv, piv txwv li cov ntaub ntawv sib txawv ntawm cov servers.
Muaj peb lub laj thawj rau kev lag luam:
- Qhov no disk subsystem tsis tuaj yeem tiv nrog kev kaw cov ntaub ntawv synchronization.
- Cov no yog qhov ua tau network yuam kev, lossis network overload, thaum cov ntaub ntawv tsis muaj sij hawm mus txog qhov replica thiab nws tsis tuaj yeem rov tsim dua.
- Thiab lub processor. Lub processor yog qhov tsawg heev. Thiab kuv tau pom qhov no ob lossis peb zaug, tab sis qhov no kuj tuaj yeem tshwm sim.
Thiab ntawm no yog peb cov lus nug uas tso cai rau peb siv cov txheeb cais. Peb tuaj yeem kwv yees ntau npaum li cas peb tau sau tseg hauv daim ntawv teev kev sib pauv. Muaj ib txoj haujlwm zoo li no pg_xlog_location_diff thiab peb tuaj yeem kwv yees qhov rov ua lag luam hauv bytes thiab vib nas this. Peb kuj siv tus nqi los ntawm qhov kev pom no (VIEWs) rau qhov no.
Nco ntsoov: _Stead pg_xlog_locationLub diff() muaj nuj nqi tuaj yeem siv tus neeg teb xov tooj rho tawm thiab rho tawm ib qho chaw los ntawm lwm qhov. Yooj yim.
Muaj ib qho taw tes nrog lub lag, uas yog nyob rau hauv vib nas this. Yog tias tsis muaj kev ua ub no ntawm tus tswv, kev hloov pauv tau muaj li ntawm 15 feeb dhau los thiab tsis muaj kev ua si, thiab yog tias peb saib qhov lag luam no ntawm tus qauv, peb yuav pom qhov lag luam ntawm 15 feeb. Qhov no tsim nyog nco ntsoov. Thiab qhov no tuaj yeem tsis meej pem thaum koj saib qhov lag luam no.
Pg_stat_all_tables yog lwm qhov kev pom zoo. Nws qhia cov txheeb cais ntawm cov rooj. Thaum peb muaj cov ntxhuav hauv cov ntaub ntawv, muaj qee yam haujlwm nrog nws, qee qhov kev ua, peb tuaj yeem tau txais cov ntaub ntawv no los ntawm qhov kev pom no.
select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;Thawj qhov uas peb tuaj yeem saib yog qhov sib txuas ua ke hla lub rooj. Tus lej nws tus kheej tom qab cov ntawv hla no tsis tas yuav phem thiab tsis yog qhov qhia tias peb yuav tsum ua qee yam.
Txawm li cas los xij, muaj qhov ntsuas thib ob - seq_tup_read. Qhov no yog tus naj npawb ntawm cov kab rov qab los ntawm kev sib txuas ua ke. Yog tias tus naj npawb nruab nrab tshaj 1, 000, 10, 000, ces qhov no yog qhov qhia tau tias tej zaum koj yuav tsum tsim qhov ntsuas qhov chaw kom cov lus nug raws li qhov ntsuas, lossis nws muaj peev xwm ua kom zoo dua cov lus nug uas siv cov kev soj ntsuam ua ntu zus. tias qhov no tsis tshwm sim.
Ib qho piv txwv yooj yim - cia peb hais ib qho kev thov nrog OFFSET loj thiab LIMIT cov nqi. Piv txwv li, 100 kab hauv ib lub rooj raug luam tawm thiab tom qab ntawd 000 cov kab uas xav tau raug coj mus, thiab cov kab scanned yav dhau los raug muab pov tseg. Qhov no kuj yog ib rooj plaub phem. Thiab cov lus nug zoo li no yuav tsum tau ua kom zoo. Thiab ntawm no yog cov lus nug SQL yooj yim uas koj tuaj yeem saib qhov no thiab ntsuas cov lej tshwm sim.
select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;Qhov loj ntawm lub rooj kuj tuaj yeem tau siv lub rooj no thiab siv cov haujlwm ntxiv pg_total_relation_size(), pg_relation_size().
Feem ntau, muaj cov metacommands dt ΠΈ di, uas tuaj yeem siv rau hauv PSQL thiab tseem saib qhov ntau thiab tsawg ntawm cov ntxhuav thiab indexes.
Txawm li cas los xij, kev siv cov haujlwm pab peb saib qhov ntau thiab tsawg ntawm cov ntxhuav, txawm tias suav nrog cov lej ntsuas, lossis tsis suav nrog cov lej ntsuas, thiab twb tau ua qee qhov kev kwv yees raws li kev loj hlob ntawm cov ntaub ntawv, piv txwv li nws loj hlob li cas, nrog qhov siv, thiab. kos ib co lus xaus txog qhov sizing optimization.
Kev kaw haujlwm. Kev kaw lus yog dab tsi? Cia peb saib ntawm kev ua haujlwm UPDATE - kev ua haujlwm ntawm kev hloov kho kab hauv ib lub rooj. Qhov tseeb, hloov tshiab yog ob txoj haujlwm (lossis ntau dua). Qhov no yog inserting ib tug tshiab version ntawm kab thiab kos lub qub version ntawm kab li obsolete. Tom qab ntawd, lub autovacuum yuav tuaj thiab ntxuav tawm cov qauv qub ntawm cov kab no, cim qhov chaw no kom rov siv tau.
Tsis tas li ntawd, kev hloov tshiab tsis yog hais txog kev hloov kho lub rooj xwb. Qhov no kuj yog qhov hloov tshiab index. Yog tias koj muaj ntau qhov ntsuas ntawm lub rooj, tom qab ntawd thaum hloov kho txhua qhov ntsuas ntsuas uas suav nrog cov teb hloov kho hauv cov lus nug kuj yuav tsum tau hloov kho. Cov indexes no tseem yuav muaj cov stale versions ntawm kab uas yuav tsum tau muab ntxuav.
select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;Thiab vim nws qhov kev tsim tshiab, UPDATE yog kev ua haujlwm hnyav. Tab sis lawv tuaj yeem ua tau yooj yim dua. Noj hot updates. Lawv tshwm sim hauv PostgreSQL version 8.3. Thiab qhov no yog dab tsi? Qhov no yog ib lub teeb yuag hloov tshiab uas tsis ua indexes yuav tsum tau rebuilt. Qhov ntawd yog, peb hloov kho cov ntaub ntawv, tab sis tsuas yog cov ntaub ntawv nyob rau hauv nplooj ntawv (uas yog lub rooj) tau hloov kho, thiab cov indexes tseem taw tes rau tib cov ntaub ntawv hauv nplooj ntawv. Muaj me ntsis ntawm kev nthuav dav kev khiav hauj lwm logic: thaum lub tshuab nqus tsev los, nws tsim cov chains hot rov tsim dua thiab txhua yam ua haujlwm txuas ntxiv yam tsis muaj kev hloov kho indexes, thiab txhua yam tshwm sim nrog cov khoom siv tsawg tsawg.
Thiab thaum twg koj ua n_tup_hot_upd loj, ces nws zoo heev. Qhov no txhais tau hais tias lub teeb yuag hloov tshiab predominate thiab qhov no yog pheej yig dua rau peb ntawm cov peev txheej thiab txhua yam zoo.
ALTER TABLE table_name SET (fillfactor = 70);Yuav ua li cas nce ntim hot updateov? Peb tuaj yeem siv tau fillfactor. Nws txiav txim siab qhov loj ntawm qhov chaw pub dawb thaum sau ib nplooj ntawv hauv lub rooj siv INSERTs. Thaum cov ntawv ntxig ntxiv rau hauv lub rooj, lawv ua tiav cov nplooj ntawv thiab tsis muaj qhov chaw khoob. Tom qab ntawd ib nplooj ntawv tshiab tseem ceeb. Cov ntaub ntawv tau sau dua. Thiab qhov no yog tus cwj pwm tsis zoo, fillfactor = 100%.
Peb tuaj yeem ua tus fillfactor 70%. Ntawd yog, thaum lub sij hawm ntxig, nplooj ntawv tshiab tau qhia meej, tab sis tsuas yog 70% ntawm nplooj ntawv tau sau. Thiab peb muaj 30% tshuav raws li cia. Thaum koj xav tau kev hloov tshiab, nws yuav tshwm sim nyob rau tib nplooj ntawv, thiab cov kab tshiab ntawm kab yuav haum rau tib nplooj ntawv. Thiab hot_update yuav ua tiav. Qhov no ua rau nws yooj yim sau rau ntawm cov rooj.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Autovacuum kab. Autovacuum yog lub subsystem uas muaj cov txheeb cais tsawg heev hauv PostgreSQL. Peb tsuas tuaj yeem pom hauv cov rooj hauv pg_stat_activity muaj pes tsawg lub tshuab nqus tsev peb muaj tam sim no. Txawm li cas los xij, nws nyuaj heev kom nkag siab tias muaj pes tsawg lub rooj nyob hauv cov kab tam sim ntawd.
Nco ntsoov: _Pib nrog Postgres 10, qhov xwm txheej nrog Vatovac taug qab tau txhim kho zoo heev - pg_stat_progress pom tau tshwm simlub tshuab nqus tsev, uas ua kom yooj yim rau qhov teeb meem ntawm kev saib xyuas lub tshuab nqus tsev.
Peb tuaj yeem siv cov lus nug yooj yim no. Thiab peb tuaj yeem pom thaum lub tshuab nqus tsev yuav tsum tau ua. Tab sis yuav ua li cas thiab thaum twg lub tshuab nqus tsev yuav tsum pib? Cov no yog cov keeb kwm ntawm cov kab uas kuv tau tham txog ua ntej. Kev hloov tshiab tshwm sim, ib qho tshiab ntawm kab tau muab tso rau. Ib tug outdated version ntawm txoj hlua tau tshwm sim. Hauv lub rooj pg_stat_user_tables muaj xws li ib tug parameter n_dead_tup. Nws qhia cov naj npawb ntawm "tuag" kab. Thiab sai li sai tau raws li tus naj npawb ntawm cov kab tuag tau ntau dua li ib qho chaw pib, ib qho autovacuum yuav tuaj rau ntawm lub rooj.
Thiab qhov ntsuas no suav tau li cas? Qhov no yog qhov feem pua ββββntawm tag nrho cov kab hauv lub rooj. Muaj ib qho parameter autovacuum_vacuum_scale_factor. Nws txiav txim qhov feem pua. Cia peb hais tias 10% + muaj qhov pib pib ntxiv ntawm 50 kab. Thiab ua li cas? Thaum peb muaj kab tuag ntau dua "10% + 50" ntawm txhua kab hauv lub rooj, ces peb muab lub rooj rau ntawm autovacuum.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Txawm li cas los xij, muaj ib lub ntsiab lus. Cov theem pib rau parameters av_base_thresh ΠΈ av_scale_factor tuaj yeem muab rau tus kheej. Thiab, raws li, qhov pib yuav tsis yog ntiaj teb, tab sis tus kheej rau lub rooj. Yog li ntawd, nyob rau hauv thiaj li yuav xam, koj yuav tsum tau siv tricks thiab tricks. Thiab yog tias koj txaus siab, koj tuaj yeem saib cov kev paub dhau los ntawm peb cov npoj yaig los ntawm Avito (qhov txuas ntawm tus swb tsis raug thiab tau hloov kho hauv cov ntawv nyeem).
Lawv sau rau , uas coj tej yam no mus rau hauv tus account. Muaj ob daim ntaub ko taw nyob ntawd. Tab sis nws xam kom raug thiab ua tau zoo heev tso cai rau peb los soj ntsuam qhov twg peb xav tau ntau lub tshuab nqus tsev rau cov rooj uas muaj tsawg.
Peb yuav ua li cas txog nws? Yog tias peb muaj cov kab loj thiab autovacuum tsis tuaj yeem tiv taus, ces peb tuaj yeem ua kom cov neeg ua haujlwm nqus tsev vacuum nce ntxiv, lossis tsuas yog ua kom lub tshuab nqus tsev ua kom nruj dua., yog li ntawd nws tshwm sim ua ntej, txheej txheem lub rooj nyob rau hauv me me pieces. Thiab yog li cov kab yuav txo qis. - Qhov tseem ceeb ntawm no yog saib xyuas cov load ntawm cov disks, vim ... Lub tshuab nqus tsev tsis yog ib qho khoom pub dawb, txawm hais tias nrog kev tshwm sim ntawm SSD / NVMe cov cuab yeej, qhov teeb meem tau dhau los ua tsis tshua pom.
Pg_stat_all_indexes yog cov txheeb cais ntawm cov indexes. Nws tsis loj. Thiab peb tuaj yeem siv nws kom tau txais cov ntaub ntawv ntawm kev siv indexes. Thiab piv txwv li, peb tuaj yeem txiav txim siab seb qhov ntsuas twg peb muaj ntxiv.
Raws li kuv twb hais lawm, hloov tshiab tsis yog ib qho kev hloov tshiab ntawm cov ntxhuav, nws tseem yog qhov hloov tshiab ntawm indexes. Raws li, yog tias peb muaj ntau qhov ntsuas ntawm lub rooj, ces thaum hloov kho cov kab hauv lub rooj, cov indexes ntawm cov indexed teb kuj yuav tsum tau hloov kho, thiab Yog tias peb muaj cov indexes tsis siv uas tsis muaj kev ntsuas ntsuas, ces lawv dai li ballast. Thiab peb yuav tsum tau tshem ntawm lawv. Rau qhov no peb xav tau ib daim teb idx_scan. Peb tsuas yog saib tus lej ntawm kev ntsuas ntsuas. Yog tias indexes muaj xoom scans nyob rau lub sij hawm ntev ntawm kev txheeb cais cia (tsawg kawg 2-3 lub lis piam), ces feem ntau cov no yog cov indexes phem, peb yuav tsum tau tshem ntawm lawv.
Nco ntsoov: Thaum tshawb nrhiav qhov ntsuas tsis tau siv nyob rau hauv cov ntaub ntawv ntawm streaming replication pawg, koj yuav tsum xyuas tag nrho cov pawg nodes, vim kev txheeb cais tsis yog thoob ntiaj teb, thiab yog tias qhov ntsuas tsis tau siv rau ntawm tus tswv, ces nws tuaj yeem siv rau ntawm cov khoom siv rov ua dua (yog tias muaj cov khoom thauj ntawd).
Ob qhov txuas:
Cov no yog cov piv txwv cov lus nug siab dua ntawm yuav ua li cas saib cov indexes tsis siv.
Qhov txuas thib ob yog qhov kev thov txaus nyiam. Muaj ib qho tsis tseem ceeb heev logic nyob ntawd. Kuv xav kom nws siv.
Dab tsi ntxiv yog tsim nyog suav nrog kev siv indices?
-
Cov indexes tsis siv yog phem.
-
Lawv siv qhov chaw.
-
Ua haujlwm hloov tshiab qeeb.
-
Ua haujlwm ntxiv rau lub tshuab nqus tsev.
Yog tias peb tshem tawm cov indexes tsis siv, peb tsuas yog ua kom cov ntaub ntawv zoo dua.
Qhov kev nthuav qhia tom ntej yog pg_stat_activity. Qhov no yog ib qho analogue ntawm kev siv hluav taws xob ps, tsuas yog hauv PostgreSQL. Yog ps'om koj saib cov txheej txheem hauv operating system, tom qab ntawd pg_stat_activity Nws yuav qhia koj cov haujlwm hauv PostgreSQL.
Dab tsi muaj txiaj ntsig peb tuaj yeem nqa los ntawm qhov ntawd?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Peb tuaj yeem pom cov haujlwm dav dav, dab tsi tshwm sim hauv cov ntaub ntawv. Peb tuaj yeem ua qhov kev xa tawm tshiab. Txhua yam ntawm no tau tawg, kev sib txuas tshiab tsis tau txais, qhov tsis raug yog nchuav rau hauv daim ntawv thov.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Peb tuaj yeem khiav cov lus nug zoo li no thiab pom tag nrho cov feem pua ββββntawm kev sib txuas nrog rau qhov kev sib txuas siab tshaj plaws thiab pom leej twg muaj kev sib txuas ntau tshaj. Thiab nyob rau hauv cov ntaub ntawv no peb pom tus neeg siv cron_role qhib 508 kev sib txuas. Thiab ib yam dab tsi tshwm sim rau nws nyob ntawd. Peb yuav tsum tau nrog nws thiab saib nws. Thiab nws yog qhov ua tau heev uas qhov no yog qee yam tsis zoo ntawm kev sib txuas.
Yog tias peb muaj OLTP ua haujlwm, cov lus nug yuav tsum ceev ceev, ceev heev thiab yuav tsum tsis txhob muaj lus nug ntev. Txawm li cas los xij, yog tias cov lus nug ntev tshwm sim, ces nyob rau lub sijhawm luv tsis muaj dab tsi txhawj txog, tab sis Nyob rau hauv lub sij hawm ntev, cov lus nug ntev ua mob rau lub database; lawv ua rau cov nyhuv ntawm cov ntxhuav thaum lub rooj fragmentation tshwm sim. Koj yuav tsum tau tshem ntawm ob qho tib si bloat thiab cov lus nug ntev.
select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;Thov nco ntsoov: nrog rau qhov kev thov no peb tuaj yeem txheeb xyuas cov lus nug ntev thiab kev lag luam. Peb siv txoj haujlwm clock_timestamp() los txiav txim lub sijhawm ua haujlwm. Cov lus nug ntev uas peb pom, peb tuaj yeem nco tau lawv, ua tiav lawv explain, saib cov phiaj xwm thiab ua li cas optimize. Peb tua cov kev thov ntev tam sim no thiab mus nrog peb lub neej.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Kev lag luam tsis zoo yog kev lag luam hauv kev lag luam hauv kev lag luam thiab tsis ua haujlwm hauv kev lag luam (tso tseg) xeev.
Nws txhais li cas? Kev lag luam muaj ntau lub xeev. Thiab ib qho ntawm cov xeev no tuaj yeem xav tau txhua lub sijhawm. Muaj ib daim teb los txhais cov xeev state hauv qhov kev nthuav qhia no. Thiab peb siv nws los txiav txim lub xeev.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Thiab, raws li kuv tau hais saum toj no, ob lub xeev no tsis ua haujlwm hauv kev lag luam thiab tsis ua haujlwm hauv kev lag luam ( rho tawm) tsis zoo. Nws yog dab tsi? Qhov no yog thaum daim ntawv thov qhib kev lag luam, tau ua qee yam thiab mus txog nws txoj kev lag luam. Qhov kev lag luam tseem qhib. Nws hangs, tsis muaj dab tsi tshwm sim nyob rau hauv nws, nws yuav siv sij hawm qhov kev twb kev txuas, locks ntawm hloov kab thiab tej zaum yuav tsub kom lub bloat ntawm lwm cov ntxhuav, vim lub architecture ntawm lub Postrges muas cav. Thiab xws li kev lag luam yuav tsum raug tua, vim tias lawv feem ntau muaj kev phom sij, nyob rau hauv txhua rooj plaub.
Yog tias koj pom tias koj muaj ntau tshaj 5-10-20 ntawm lawv hauv koj cov ntaub ntawv, ces koj yuav tsum txhawj xeeb thiab pib ua ib yam dab tsi nrog lawv.
Ntawm no peb kuj siv rau lub sij hawm xam clock_timestamp(). Peb tua kev lag luam thiab optimize daim ntawv thov.
Raws li kuv tau hais saum toj no, kev thaiv yog thaum ob lossis ntau qhov kev sib tw sib tw rau ib lossis ib pab pawg. Rau qhov no peb muaj ib daim teb waiting nrog tus nqi boolean true los yog false.
Tseeb - qhov no txhais tau hais tias cov txheej txheem tseem tab tom tos, qee yam yuav tsum tau ua. Thaum tus txheej txheem tab tom tos, nws txhais tau tias tus neeg siv khoom uas pib txheej txheem no tseem tos. Tus neeg siv zaum hauv qhov browser thiab tseem tos.
Ceeb toom: _Pib los ntawm Postgres version 9.6 teb waiting tshem tawm thiab ob qho kev qhia ntxiv ntxiv ntxiv wait_event_type ΠΈ wait_event._
Kuv yuav ua li cas? Yog tias koj pom qhov tseeb rau lub sijhawm ntev, nws txhais tau tias koj yuav tsum tshem tawm cov kev thov no. Peb tsuas tua cov kev pauv no xwb. Peb sau rau cov neeg tsim tawm tias lawv yuav tsum tau ua kom zoo dua kom tsis muaj kev sib tw rau cov peev txheej. Thiab ces cov developers optimize daim ntawv thov kom qhov no tsis tshwm sim.
Thiab qhov kawg, tab sis tej zaum yuav tsis mob tuag yog tshwm sim ntawm deadlocks. Ob qhov kev pauv hloov kho ob qhov peev txheej, tom qab ntawd nkag mus rau lawv dua, lub sijhawm no rau cov peev txheej sib txawv. Hauv qhov no, PostgreSQL tua qhov kev hloov pauv nws tus kheej kom lwm tus tuaj yeem ua haujlwm txuas ntxiv. Qhov no yog qhov xwm txheej kawg thiab nws tsis tuaj yeem txiav txim siab nws tus kheej. Yog li ntawd, PostgreSQL raug yuam kom ntsuas huab cua.
Thiab ntawm no yog ob lo lus nug uas tso cai rau koj taug qab kev thaiv. Peb siv saib pg_locks, uas tso cai rau koj taug qab cov xauv hnyav.
Thiab thawj qhov txuas yog cov ntawv thov nws tus kheej. Nws ntev heev.
Thiab qhov txuas thib ob yog ib tsab xov xwm ntawm cov xauv. Nws yog ib qho tseem ceeb los nyeem, nws yog qhov nthuav heev.
Yog li peb pom dab tsi? Peb pom ob qhov kev thov. Kev lag luam nrog ALTER TABLE yog kev thaiv kev lag luam. Nws pib, tab sis tsis ua tiav, thiab daim ntawv thov uas tau sau tseg qhov kev hloov pauv no yog ua lwm qhov chaw. Thiab qhov kev thov thib ob yog hloov tshiab. Nws tos kom lub rooj hloov pauv tas ua ntej nws tuaj yeem ua nws txoj haujlwm txuas ntxiv.
Qhov no yog qhov peb tuaj yeem tshawb pom tias leej twg kaw leej twg, tuav leej twg, thiab peb tuaj yeem nrog nws ntxiv.
Tom ntej no module yog pg_stat_statements. Raws li kuv tau hais, qhov no yog ib qho module. Txhawm rau siv nws, koj yuav tsum thauj nws lub tsev qiv ntawv hauv kev teeb tsa, rov pib PostgreSQL, teeb tsa lub module (nrog ib qho lus txib) thiab tom qab ntawd peb yuav pom qhov tshiab.
CΡΠ΅Π΄Π½Π΅Π΅ Π²ΡΠ΅ΠΌΡ Π·Π°ΠΏΡΠΎΡΠ° Π² ΠΌΠΈΠ»ΠΈΡΠ΅ΠΊΡΠ½Π΄Π°Ρ
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Π‘Π°ΠΌΡΠ΅ Π°ΠΊΡΠΈΠ²Π½ΠΎ ΠΏΠΈΡΡΡΠΈΠ΅ (Π² shared_buffers) Π·Π°ΠΏΡΠΎΡΡ
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;Peb tuaj yeem nqa dab tsi los ntawm qhov ntawd? Yog tias peb tham txog tej yam yooj yim, peb tuaj yeem siv lub sijhawm nruab nrab ntawm cov lus nug. Lub sij hawm loj hlob, uas txhais tau hais tias PostgreSQL teb qeeb thiab peb yuav tsum ua qee yam.
Peb tuaj yeem saib cov ntawv sau ntau tshaj plaws hauv cov ntaub ntawv uas hloov cov ntaub ntawv hauv kev sib koom buffers. Saib leej twg hloov kho lossis tshem tawm cov ntaub ntawv nyob ntawd.
Thiab peb tuaj yeem saib cov txheeb cais sib txawv rau cov kev thov no.
Peb pg_stat_statements Peb siv nws los tsim cov ntawv ceeb toom. Peb rov kho cov txheeb cais ib zaug ib hnub. Cia peb khaws cia. Ua ntej rov pib dua cov txheeb cais lwm zaus, cia peb tsim ib daim ntawv qhia. Ntawm no yog qhov txuas rau daim ntawv tshaj tawm. Koj tuaj yeem saib nws.
Peb ua dab tsi? Peb suav cov txheeb cais dav dav rau txhua qhov kev thov. Tom qab ntawd, rau txhua qhov kev thov, peb suav nws tus kheej cov nyiaj pab rau cov kev txheeb cais tag nrho.
Thiab peb tuaj yeem saib tau dab tsi? Peb tuaj yeem saib tag nrho lub sijhawm ua tiav ntawm txhua qhov kev thov ntawm ib hom tshwj xeeb tiv thaiv keeb kwm yav dhau ntawm tag nrho lwm qhov kev thov. Peb tuaj yeem saib CPU thiab I / O cov peev txheej siv txheeb ze rau tag nrho cov duab. Thiab twb optimize cov lus nug. Peb tab tom tsim cov lus nug saum toj kawg nkaus raws li daim ntawv tshaj tawm no thiab twb tau txais zaub mov rau kev xav txog dab tsi los ua kom zoo dua.
Dab tsi peb tau tso tseg tom qab qhov xwm txheej? Tseem muaj qee qhov kev xa tawm uas kuv tsis tau txiav txim siab vim tias lub sijhawm txwv.
muaj pgstattuple kuj yog ib qho ntxiv module los ntawm cov txheej txheem contribs pob. Nws tso cai rau koj los ntsuas bloat rooj, thiaj li hu rooj fragmentation. Thiab yog tias muaj ntau qhov tawg, koj yuav tsum tau tshem nws thiab siv cov cuab yeej sib txawv. Thiab muaj nuj nqi pgstattuple ua haujlwm ntev. Thiab ntau lub rooj muaj, qhov ntev nws yuav ua haujlwm.
Qhov kev koom tes tom ntej yog pg_buffercache. Nws tso cai rau koj los tshuaj xyuas cov kev sib koom ua ke: yuav siv ntau npaum li cas thiab siv cov nplooj ntawv twg. Thiab nws tsuas tso cai rau koj saib mus rau hauv kev sib koom buffers thiab ntsuas qhov tshwm sim nyob rau ntawd.
Tom ntej no module yog pgfincore. Nws tso cai rau lub rooj ua haujlwm qis ntawm kev hu xov tooj mincore(), i.e. nws tso cai rau koj thauj lub rooj rau hauv kev sib koom buffers, lossis tshem tawm nws. Thiab nws tso cai, ntawm lwm yam, los tshuaj xyuas lub operating system nplooj ntawv cache, piv txwv li, qhov chaw ntau npaum li cas lub rooj nyob hauv nplooj ntawv cache, hauv kev sib koom buffers, thiab tsuas yog tso cai rau peb los ntsuas qhov ua haujlwm ntawm lub rooj.
Tom ntej no module - pg_stat_kcache. Nws kuj siv lub kaw lus hu getrusage(). Thiab nws ua nws ua ntej thiab tom qab qhov kev thov raug tua. Thiab nyob rau hauv cov txiaj ntsig kev txheeb cais, nws tso cai rau peb kwv yees ntau npaum li cas peb qhov kev thov siv rau disk I / O, piv txwv li, kev ua haujlwm nrog cov ntaub ntawv kaw lus thiab saib ntawm kev siv processor. Txawm li cas los xij, cov qauv no yog cov hluas ( hnoos hnoos) thiab rau nws txoj haujlwm nws xav tau PostgreSQL 9.4 thiab pg_stat_statements, uas kuv tau hais ua ntej.
-
Kev paub txog kev siv cov ntaub ntawv txheeb cais muaj txiaj ntsig. Koj tsis xav tau cov kev pab cuam thib peb. Koj tuaj yeem tuaj, pom, ua ib yam dab tsi, ua tiav ib yam dab tsi.
-
Kev siv cov txheeb cais tsis yog qhov nyuaj, nws tsuas yog SQL li niaj zaus. Koj sau qhov kev thov, sau nws, xa nws, saib nws.
-
Kev txheeb cais pab teb cov lus nug. Yog tias koj muaj lus nug, koj tig mus rau kev txheeb cais - saib, kos cov lus xaus, txheeb xyuas cov txiaj ntsig.
-
Thiab sim. Muaj ntau qhov kev thov, ntau cov ntaub ntawv. Koj tuaj yeem ua kom zoo rau cov lus nug uas twb muaj lawm. Koj tuaj yeem ua koj tus kheej version ntawm qhov kev thov uas haum rau koj ntau tshaj li qhov qub thiab siv nws.
ua tim khawv
Cov kev sib txuas tsim nyog uas tau pom hauv tsab xov xwm, raws li cov ntaub ntawv, tau nyob hauv daim ntawv tshaj tawm.
Tus sau sau ntxiv
(eng)
Tus sau Statistics
System Administration Functions
Sib koom modules
SQL utils thiab sql code piv txwv
Ua tsaug rau koj mloog!
Tau qhov twg los: www.hab.com