Kuv xav kom koj nyeem cov ntawv sau tseg ntawm tsab ntawv ceeb toom los ntawm Alexey Lesovsky los ntawm Cov Ntaub Ntawv Egret "Fundamentals of PostgreSQL saib xyuas"
Hauv tsab ntawv tshaj tawm no, Alexey Lesovsky yuav tham txog cov ntsiab lus tseem ceeb ntawm kev txheeb cais tom qab, lawv txhais li cas, thiab vim li cas lawv yuav tsum muaj nyob rau hauv kev saib xyuas; hais txog dab tsi graphs yuav tsum nyob rau hauv kev saib xyuas, yuav ua li cas ntxiv lawv thiab txhais li cas. Daim ntawv tshaj tawm yuav muaj txiaj ntsig zoo rau cov thawj coj hauv chaw khaws ntaub ntawv, cov thawj coj ua haujlwm thiab cov tsim tawm uas txaus siab rau kev daws teeb meem Postgres.
Kuv lub npe yog Alexey Lesovsky, Kuv sawv cev rau lub tuam txhab Data Egret.
Ob peb lo lus hais txog kuv tus kheej. Kuv pib tawm ntev dhau los ua tus tswj hwm qhov system.
Kuv tau tswj hwm txhua hom kev sib txawv ntawm Linux, ua haujlwm ntau yam ntsig txog Linux, piv txwv li virtualization, saib xyuas, ua haujlwm nrog cov neeg sawv cev, thiab lwm yam. Tab sis qee lub sijhawm kuv pib ua haujlwm ntau dua nrog cov databases, PostgreSQL. Kuv nyiam nws heev. Thiab ntawm qee lub sijhawm kuv pib ua haujlwm ntawm PostgreSQL feem ntau ntawm kuv lub sijhawm ua haujlwm. Thiab yog li maj kuv dhau los ua PostgreSQL DBA.
Thiab thoob plaws hauv kuv txoj haujlwm, kuv ib txwm nyiam cov ncauj lus ntawm kev txheeb cais, saib xyuas, thiab telemetry. Thiab thaum kuv yog tus thawj tswj hwm, kuv tau ua haujlwm zoo nrog Zabbix. Thiab kuv tau sau cov ntawv me me xws li . Nws tau nrov heev hauv nws lub sijhawm. Thiab nyob ntawd nws muaj peev xwm saib xyuas ntau yam tseem ceeb, tsis yog Linux nkaus xwb, tab sis kuj muaj ntau yam khoom.
Tam sim no kuv tab tom ua haujlwm ntawm PostgreSQL. Kuv twb tau sau lwm yam uas tso cai rau koj ua haujlwm nrog PostgreSQL txheeb cais. Nws yog hu ua (txoj lus ntawm Habre - ).
Ib daim ntawv qhia me ntsis. Peb cov neeg siv khoom, peb cov neeg siv khoom muaj xwm txheej dab tsi? Muaj qee yam kev huam yuaj ntsig txog cov ntaub ntawv. Thiab thaum cov ntaub ntawv tau rov qab los lawm, tus thawj coj lossis tus thawj coj ntawm kev txhim kho tuaj thiab hais tias: "Cov phooj ywg, peb yuav tsum tau saib xyuas cov ntaub ntawv, vim tias muaj qee yam tsis zoo tshwm sim thiab peb yuav tsum tiv thaiv qhov no los ntawm kev tshwm sim yav tom ntej." Thiab ntawm no pib cov txheej txheem nthuav dav ntawm kev xaiv qhov kev saib xyuas lossis hloov kho qhov kev saib xyuas uas twb muaj lawm kom koj tuaj yeem saib xyuas koj cov ntaub ntawv - PostgreSQL, MySQL lossis qee qhov lwm tus. Thiab cov npoj yaig pib tawm tswv yim: "Kuv hnov ββββtias muaj cov ntaub ntawv zoo li no. Cia peb siv nws." Cov npoj yaig pib sib cav sib ceg. Thiab thaum kawg nws hloov tawm tias peb xaiv qee hom database, tab sis PostgreSQL saib xyuas tau nthuav tawm hauv nws tsis zoo thiab peb ib txwm yuav tsum tau ntxiv qee yam. Siv qee qhov chaw cia khoom los ntawm GitHub, clone lawv, hloov cov ntawv sau, thiab qee yam kho lawv. Thiab thaum kawg nws xaus ua qee yam ntawm phau ntawv ua haujlwm.
Yog li ntawd, nyob rau hauv no hais lus kuv yuav sim muab koj ib co kev paub txog yuav ua li cas xaiv kev saib xyuas tsis yog rau PostgreSQL xwb, tab sis kuj rau database. Thiab muab koj cov kev paub uas yuav tso cai rau koj ua kom tiav koj qhov kev saib xyuas kom tau txais qee qhov txiaj ntsig los ntawm nws, kom koj tuaj yeem saib xyuas koj cov ntaub ntawv nrog cov txiaj ntsig, txhawm rau tiv thaiv tam sim ntawd thaum muaj xwm txheej ceev uas yuav tshwm sim.
Thiab cov tswv yim uas yuav muaj nyob rau hauv daim ntawv tshaj tawm no tuaj yeem hloov kho ncaj qha rau txhua qhov chaw khaws ntaub ntawv, tsis yog DBMS lossis noSQL. Yog li ntawd, tsis yog PostgreSQL nkaus xwb, tab sis yuav muaj ntau yam zaub mov txawv ntawm yuav ua li cas hauv PostgreSQL. Yuav muaj cov piv txwv ntawm cov lus nug, piv txwv ntawm cov chaw uas PostgreSQL muaj rau kev saib xyuas. Thiab yog tias koj DBMS muaj tib yam uas tso cai rau koj los saib xyuas lawv, koj tuaj yeem hloov kho lawv, ntxiv rau lawv thiab nws yuav zoo.
Kuv yuav tsis nyob hauv tsab ntawv ceeb toom
tham txog yuav ua li cas xa thiab khaws cov ntsuas. Kuv yuav tsis hais dab tsi txog tom qab ua cov ntaub ntawv thiab nthuav tawm rau tus neeg siv. Thiab kuv yuav tsis hais dab tsi txog kev ceeb toom.
Tab sis raws li zaj dab neeg mus ntxiv, kuv yuav qhia cov screenshots sib txawv ntawm kev saib xyuas uas twb muaj lawm thiab qee yam thuam lawv. Tab sis txawm li cas los xij, kuv yuav sim tsis sau npe cov npe kom tsis txhob tsim kev tshaj tawm lossis tawm tsam kev tshaj tawm rau cov khoom lag luam no. Yog li ntawd, tag nrho cov coincidences yog random thiab tshuav rau koj lub tswv yim.
Ua ntej, cia peb saib seb qhov kev saib xyuas yog dab tsi. Kev saib xyuas yog ib qho tseem ceeb heev uas yuav tsum muaj. Sawv daws to taub qhov no. Tab sis tib lub sijhawm, kev saib xyuas tsis cuam tshuam nrog cov khoom lag luam thiab tsis cuam tshuam ncaj qha rau lub tuam txhab cov nyiaj tau los, yog li lub sijhawm ib txwm muab faib rau kev soj ntsuam ntawm qhov seem. Yog tias peb muaj sijhawm, ces peb saib xyuas; yog tias peb tsis muaj sijhawm, ces OK, peb yuav muab tso rau hauv qhov rov qab thiab muaj ib hnub peb yuav rov qab mus rau cov haujlwm no.
Yog li ntawd, los ntawm peb qhov kev coj ua, thaum peb tuaj rau cov neeg siv khoom, kev saib xyuas feem ntau tsis tiav thiab tsis muaj qhov nthuav dav uas yuav pab peb ua haujlwm zoo dua nrog cov ntaub ntawv. Thiab yog li kev saib xyuas ib txwm yuav tsum ua kom tiav.
Databases yog tej yam nyuaj uas yuav tsum tau saib xyuas, vim hais tias databases yog ib tug repository ntawm cov ntaub ntawv. Thiab cov ntaub ntawv tseem ceeb heev rau lub tuam txhab; nws tsis tuaj yeem ploj hauv txhua txoj kev. Tab sis tib lub sij hawm, databases yog heev complex daim ntawm software. Lawv muaj ib tug loj tus naj npawb ntawm cov khoom. Thiab ntau yam ntawm cov khoom no yuav tsum tau saib xyuas.
Yog tias peb tab tom tham tshwj xeeb txog PostgreSQL, ces nws tuaj yeem sawv cev rau hauv daim ntawv ntawm lub tswv yim uas muaj ntau yam khoom. Cov khoom no cuam tshuam nrog ib leeg. Thiab nyob rau tib lub sijhawm, PostgreSQL muaj lub npe hu ua Stats Collector subsystem, uas tso cai rau koj los sau cov txheeb cais txog kev ua haujlwm ntawm cov subsystems thiab muab qee yam kev cuam tshuam rau tus thawj coj lossis cov neeg siv kom nws tuaj yeem saib cov txheeb cais no.
Cov txheeb cais no tau nthuav tawm nyob rau hauv daim ntawv ntawm ib co kev ua haujlwm thiab kev pom. Lawv tuaj yeem hu ua rooj. Ntawd yog, siv tus neeg siv psql li niaj zaus, koj tuaj yeem txuas mus rau cov ntaub ntawv, xaiv cov haujlwm no thiab saib, thiab tau txais qee tus lej tshwj xeeb txog kev ua haujlwm ntawm PostgreSQL subsystems.
Koj tuaj yeem ntxiv cov lej no rau koj txoj kev saib xyuas koj nyiam, kos duab, ntxiv cov haujlwm thiab tau txais kev txheeb xyuas nyob rau lub sijhawm ntev.
Tab sis hauv daim ntawv tshaj tawm no kuv yuav tsis npog tag nrho cov haujlwm no, vim tias nws tuaj yeem siv sijhawm ntev. Kuv yuav hais ob, peb lossis plaub yam thiab qhia koj tias lawv pab saib xyuas zoo li cas.
Thiab yog tias peb tham txog kev saib xyuas cov ntaub ntawv, ces dab tsi yuav tsum tau saib xyuas? Ua ntej tshaj plaws, peb yuav tsum tau saib xyuas qhov muaj, vim tias cov ntaub ntawv khaws cia yog qhov kev pabcuam uas muab kev nkag mus rau cov ntaub ntawv rau cov neeg siv khoom thiab peb yuav tsum tau saib xyuas qhov muaj, thiab tseem muab qee yam ntawm nws cov yam ntxwv zoo thiab ntau yam.
Peb kuj yuav tsum tau saib xyuas cov neeg siv khoom sib txuas rau peb cov ntaub ntawv, vim tias lawv tuaj yeem yog ob qho tib si cov neeg siv khoom thiab cov neeg siv khoom tsis zoo uas tuaj yeem ua rau cov ntaub ntawv raug mob. Lawv kuj yuav tsum tau saib xyuas thiab lawv cov dej num taug qab.
Thaum cov neeg siv khoom txuas mus rau cov ntaub ntawv, nws pom tseeb tias lawv pib ua haujlwm nrog peb cov ntaub ntawv, yog li peb yuav tsum tau saib xyuas seb cov neeg siv khoom ua haujlwm li cas nrog cov ntaub ntawv: nrog cov ntxhuav twg, thiab tsawg dua, nrog cov ntsuas twg. Ntawd yog, peb yuav tsum ntsuas cov haujlwm ua haujlwm uas tsim los ntawm peb cov neeg siv khoom.
Tab sis kev ua haujlwm kuj muaj, tau kawg, ntawm kev thov. Cov ntawv thov txuas rau cov ntaub ntawv, nkag mus rau cov ntaub ntawv siv cov lus nug, yog li nws yog ib qho tseem ceeb uas yuav tsum tau soj ntsuam cov lus nug uas peb muaj nyob rau hauv cov ntaub ntawv, saib xyuas lawv qhov tsim nyog, uas lawv tsis yog sau ntawv crookedly, uas qee qhov kev xaiv yuav tsum tau sau thiab ua kom lawv ua haujlwm sai dua. thiab nrog kev ua tau zoo dua.
Thiab txij li thaum peb tab tom tham txog lub database, lub database yeej ib txwm ua keeb kwm yav dhau. Cov txheej txheem keeb kwm yav dhau los pab tswj cov ntaub ntawv kev ua tau zoo ntawm qib zoo, yog li lawv xav tau qee qhov peev txheej rau lawv tus kheej ua haujlwm. Thiab nyob rau tib lub sijhawm, lawv tuaj yeem sib tshooj nrog cov neeg thov kev pabcuam, yog li cov txheej txheem kev ntshaw tom qab tuaj yeem cuam tshuam ncaj qha rau kev ua haujlwm ntawm cov neeg thov. Yog li ntawd, lawv kuj yuav tsum tau saib xyuas thiab taug qab kom tsis txhob muaj kev cuam tshuam ntawm cov txheej txheem keeb kwm yav dhau.
Thiab tag nrho cov no nyob rau hauv cov nqe lus ntawm kev soj ntsuam database tseem nyob rau hauv qhov system metric. Tab sis txiav txim siab tias feem ntau ntawm peb cov kev tsim kho vaj tse tau tsiv mus rau huab, qhov kev ntsuas qhov system ntawm tus tswv tsev ib txwm ploj mus rau hauv keeb kwm yav dhau. Tab sis hauv cov ntaub ntawv lawv tseem muaj feem cuam tshuam thiab, tau kawg, nws tseem yuav tsum tau saib xyuas cov kev ntsuas kev ntsuas.
Txhua yam zoo dua los yog tsawg dua nrog kev ntsuas kev ntsuas, tag nrho cov kev soj ntsuam niaj hnub no twb tau txhawb nqa cov kev ntsuas no, tab sis feem ntau, qee cov khoom tseem tsis txaus thiab qee yam yuav tsum tau ntxiv. Kuv kuj yuav kov rau lawv, yuav muaj ob peb slides txog lawv.
Thawj lub ntsiab lus ntawm txoj kev npaj yog kev nkag tau yooj yim. Accessibility yog dab tsi? Muaj nyob hauv kuv qhov kev nkag siab yog lub peev xwm ntawm lub hauv paus rau kev pabcuam kev sib txuas, piv txwv li lub hauv paus tau tsa, nws, raws li kev pabcuam, lees txais kev sib txuas ntawm cov neeg siv khoom. Thiab qhov kev nkag tau yooj yim no tuaj yeem ntsuas tau los ntawm qee tus yam ntxwv. Nws yog qhov yooj yim heev los tso saib cov yam ntxwv ntawm dashboards.
Txhua tus paub tias dashboards yog dab tsi. Qhov no yog thaum koj coj ib qho saib ntawm qhov screen uas cov ntaub ntawv tsim nyog tau sau tseg. Thiab koj tuaj yeem txiav txim siab tam sim ntawd seb puas muaj teeb meem hauv database lossis tsis.
Yog li, qhov muaj nyob ntawm cov ntaub ntawv thiab lwm yam ntxwv tseem ceeb yuav tsum tau muab tso rau hauv dashboards kom cov ntaub ntawv no nyob ntawm tes thiab ib txwm muaj rau koj. Qee cov ntsiab lus ntxiv uas twb tau pab hauv kev tshawb nrhiav qhov xwm txheej, thaum tshawb xyuas qee qhov xwm txheej kub ntxhov, lawv twb yuav tsum tau muab tso rau hauv cov dashboards thib ob, lossis muab zais rau hauv cov kev sib txuas uas ua rau cov neeg thib peb saib xyuas.
Ib qho piv txwv ntawm ib qho kev saib xyuas zoo. Qhov no yog ib qho kev saib xyuas txias heev. Nws sau ntau cov ntaub ntawv, tab sis los ntawm kuv qhov kev xav, nws muaj lub tswv yim coj txawv txawv ntawm dashboards. Muaj qhov txuas rau "tsim lub dashboard". Tab sis thaum koj tsim lub dashboard, koj tsim cov npe ntawm ob kab, ib daim ntawv teev cov duab. Thiab thaum koj xav tau saib ib yam dab tsi, koj pib nyem nrog tus nas, scrolling, nrhiav daim ntawv xav tau. Thiab qhov no yuav siv sijhawm, piv txwv li tsis muaj dashboards li ntawd. Tsuas muaj cov npe ntawm cov kab kos.
Koj yuav tsum ntxiv dab tsi rau cov dashboards no? Koj tuaj yeem pib nrog tus yam ntxwv zoo li lub sijhawm teb. PostgreSQL muaj pg_stat_statements saib. Nws yog neeg xiam oob khab los ntawm lub neej ntawd, tab sis nws yog ib qho tseem ceeb ntawm cov kev pom zoo uas yuav tsum tau qhib thiab siv. Nws khaws cov ntaub ntawv hais txog txhua cov lus nug uas tau ua tiav hauv cov ntaub ntawv.
Raws li, peb tuaj yeem pib los ntawm qhov tseeb tias peb tuaj yeem siv lub sijhawm ua tiav tag nrho ntawm txhua qhov kev thov thiab faib nws los ntawm tus lej ntawm kev thov siv cov teb saum toj no. Tab sis qhov no yog qhov nruab nrab kub hauv tsev kho mob. Peb tuaj yeem pib los ntawm lwm qhov chaw - qhov tsawg kawg nkaus query execution lub sij hawm, siab tshaj plaws thiab nruab nrab. Thiab peb tuaj yeem tsim cov feem pua; PostgreSQL muaj cov haujlwm sib xws rau qhov no. Thiab peb tuaj yeem tau txais qee tus lej uas qhia lub sijhawm teb ntawm peb cov ntaub ntawv rau kev thov ua tiav, piv txwv li peb tsis ua tiav qhov kev thov cuav 'xaiv 1' thiab saib lub sijhawm teb, tab sis peb txheeb xyuas lub sijhawm teb rau cov lus thov ua tiav thiab kos. yog ib daim duab cais, los yog peb tsim ib daim duab raws li nws.
Nws tseem yog ib qho tseem ceeb los saib xyuas cov lej ntawm qhov tsis raug uas tam sim no tsim los ntawm qhov system. Thiab rau qhov no koj tuaj yeem siv pg_stat_database saib. Peb tsom mus rau xact_rollback teb. Daim teb no qhia tsis tau tsuas yog tus naj npawb ntawm rollbacks uas tshwm sim nyob rau hauv lub database, tab sis kuj yuav siv sij hawm mus rau hauv tus account tus naj npawb ntawm qhov yuam kev. Hais txog kev hais lus, peb tuaj yeem tso saib daim duab no hauv peb lub dashboard thiab pom pes tsawg qhov yuam kev peb tam sim no muaj. Yog tias muaj ntau qhov yuam kev, ces qhov no yog qhov laj thawj zoo los saib rau hauv cov cav thiab pom tias lawv yog qhov yuam kev thiab vim li cas lawv tshwm sim, thiab tom qab ntawd nqis peev thiab daws lawv.
Koj tuaj yeem ntxiv cov khoom xws li Tachometer. Cov no yog tus naj npawb ntawm kev hloov pauv ib ob thiab tus naj npawb ntawm kev thov ib ob. Piv txwv li, koj tuaj yeem siv cov lej no raws li kev ua haujlwm tam sim no ntawm koj cov ntaub ntawv thiab saib seb puas muaj qhov siab tshaj plaws hauv kev thov, nce siab hauv kev lag luam, lossis, qhov sib txawv, txawm tias cov ntaub ntawv poob qis vim qee qhov backend ua tsis tiav. Nws yog ib qho tseem ceeb uas yuav tsum nco ntsoov saib daim duab no thiab nco ntsoov tias rau peb qhov project no kev ua tau zoo yog qhov qub, tab sis cov txiaj ntsig saum toj no thiab hauv qab no twb muaj qee yam teeb meem thiab tsis nkag siab, uas txhais tau tias peb yuav tsum tau saib vim li cas cov lej no. siab heev.
Txhawm rau kwv yees tus lej ntawm kev hloov pauv, peb tuaj yeem rov xa mus rau pg_stat_database saib. Peb tuaj yeem ntxiv tus lej ntawm kev cog lus thiab tus lej ntawm rollbacks thiab tau txais tus lej ntawm kev hloov pauv ib ob.
Puas yog txhua tus nkag siab tias ntau qhov kev thov tuaj yeem haum rau hauv ib qho kev lag luam? Yog li TPS thiab QPS txawv me ntsis.
Tus naj npawb ntawm kev thov ib ob tuaj yeem tau txais los ntawm pg_stat_statements thiab tsuas yog xam cov lej ntawm txhua qhov kev thov ua tiav. Nws yog qhov tseeb tias peb sib piv tus nqi tam sim no nrog rau yav dhau los, rho tawm, tau txais qhov delta, thiab tau txais qhov ntau.
Koj tuaj yeem ntxiv cov metrics ntxiv yog tias xav tau, uas tseem pab ntsuas qhov muaj nyob ntawm peb cov ntaub ntawv thiab saib xyuas seb puas muaj kev poob qis.
Ib qho ntawm cov kev ntsuas no yog lub sijhawm ua haujlwm. Tab sis uptime hauv PostgreSQL yog qhov nyuaj me ntsis. Kuv mam qhia koj vim li cas. Thaum PostgreSQL tau pib, uptime pib qhia. Tab sis yog tias qee lub sijhawm, piv txwv li, qee qhov haujlwm tau ua haujlwm thaum hmo ntuj, OOM-killer tuaj thiab yuam kom kaw PostgreSQL cov txheej txheem menyuam yaus, tom qab ntawd qhov no PostgreSQL txiav tawm kev sib txuas ntawm txhua tus neeg siv khoom, rov pib dua thaj chaw nco thiab pib rov qab los ntawm qhov chaw kuaj zaum kawg. Thiab thaum qhov kev rov qab los ntawm qhov chaw kuaj xyuas no kav ntev, cov ntaub ntawv tsis lees txais kev sib txuas, piv txwv li qhov xwm txheej no tuaj yeem ntsuas raws li lub sijhawm poob. Tab sis lub sijhawm uptime txee yuav tsis rov pib dua, vim tias nws yuav siv sij hawm rau hauv tus account postmaster pib lub sij hawm los ntawm thawj lub sijhawm. Yog li ntawd, cov xwm txheej zoo li no tuaj yeem hla.
Koj yuav tsum tau saib xyuas tus naj npawb ntawm cov neeg ua haujlwm nqus tsev. Puas yog txhua tus paub tias autovacuum yog dab tsi hauv PostgreSQL? Qhov no yog qhov nthuav subsystem hauv PostgreSQL. Ntau cov ntawv tau sau txog nws, ntau cov ntawv ceeb toom tau ua. Muaj ntau qhov kev sib tham txog lub tshuab nqus tsev thiab nws yuav tsum ua haujlwm li cas. Ntau tus xav tias nws yog qhov phem tsim nyog. Tab sis yog li ntawd nws yog. Qhov no yog ib hom analogue ntawm cov khoom khib nyiab uas ntxuav cov kab uas tsis muaj hnub nyoog uas tsis xav tau los ntawm kev hloov pauv thiab tso chaw nyob hauv cov ntxhuav thiab cov ntsuas rau kab tshiab.
Vim li cas koj yuav tsum tau saib xyuas nws? Vim tias lub tshuab nqus tsev qee zaum mob hnyav heev. Nws siv ntau cov peev txheej thiab cov neeg thov kev thov pib raug kev txom nyem los ntawm qhov tshwm sim.
Thiab nws yuav tsum tau saib xyuas los ntawm pg_stat_activity saib, uas kuv yuav tham txog hauv ntu tom ntej. Qhov kev pom no qhia txog cov haujlwm tam sim no hauv cov ntaub ntawv. Thiab los ntawm txoj haujlwm no peb tuaj yeem taug qab cov naj npawb ntawm lub tshuab nqus tsev uas ua haujlwm tam sim no. Peb tuaj yeem taug qab lub tshuab nqus tsev thiab pom tias yog tias peb tau dhau qhov kev txwv, ces qhov no yog qhov laj thawj los saib rau hauv PostgreSQL chaw thiab qee qhov kev ua haujlwm zoo ntawm lub tshuab nqus tsev.
Lwm qhov hais txog PostgreSQL yog PostgreSQL mob heev ntawm kev lag luam ntev. Tshwj xeeb tshaj yog los ntawm kev lag luam uas nyob ib puag ncig ntev thiab tsis ua dab tsi. Qhov no yog qhov hu ua stat idle-in-transaction. Xws li kev lag luam tuav cov xauv thiab tiv thaiv lub tshuab nqus tsev los ntawm kev ua haujlwm. Thiab yog li ntawd, cov ntxhuav swell thiab nce loj. Thiab cov lus nug uas ua hauj lwm nrog cov rooj no pib ua hauj lwm qeeb, vim hais tias koj yuav tsum shovel tag nrho cov qub versions ntawm kab los ntawm lub cim xeeb mus rau disk thiab rov qab. Yog li ntawd, lub sij hawm, lub sij hawm ntawm kev ua lag luam ntev tshaj plaws, qhov kev thov lub tshuab nqus tsev ntev tshaj plaws kuj yuav tsum tau saib xyuas. Thiab yog tias peb pom qee cov txheej txheem uas tau ua haujlwm ntev heev, twb tau ntau dua 10-20-30 feeb rau kev thauj khoom OLTP, ces peb yuav tsum tau xyuam xim rau lawv thiab quab yuam txiav lawv, lossis txhim kho daim ntawv thov kom lawv tsis hu thiab tsis txhob dai ntev. Rau kev ntsuas kev ua haujlwm, 10-20-30 feeb yog qhov qub; kuj tseem muaj qhov ntev dua.
Tom ntej no peb muaj kev xaiv nrog cov neeg siv khoom sib txuas. Thaum peb twb tau tsim ib lub dashboard thiab tshaj tawm cov ntsiab lus muaj nyob rau ntawm nws, peb tuaj yeem ntxiv cov ntaub ntawv ntxiv txog cov neeg siv khoom sib txuas rau ntawd.
Cov ntaub ntawv hais txog cov neeg siv khoom sib txuas yog qhov tseem ceeb vim tias, los ntawm PostgreSQL qhov kev xav, cov neeg siv khoom sib txawv. Muaj cov neeg zoo thiab muaj cov neeg phem.
Ib qho piv txwv yooj yim. Los ntawm tus neeg siv khoom kuv nkag siab txog daim ntawv thov. Daim ntawv thov tau txuas nrog lub database thiab tam sim ntawd pib xa nws cov lus thov rau ntawd, cov txheej txheem database thiab ua tiav lawv, thiab xa cov txiaj ntsig rov qab rau tus neeg siv khoom. Cov no yog cov neeg siv khoom zoo thiab raug.
Muaj cov xwm txheej thaum tus neeg siv khoom tau txuas nrog, nws tuav qhov kev sib txuas, tab sis tsis muaj dab tsi. Nws nyob hauv lub xeev tsis muaj zog.
Tab sis muaj cov neeg siv khoom phem. Piv txwv li, tib tus neeg siv khoom txuas nrog, qhib kev lag luam, ua ib yam dab tsi hauv cov ntaub ntawv thiab tom qab ntawd nkag mus rau hauv cov lej, piv txwv li, nkag mus rau lwm qhov chaw lossis ua cov ntaub ntawv tau txais nyob ntawd. Tab sis nws tsis tau kaw qhov kev lag luam. Thiab qhov kev lag luam dai rau hauv cov ntaub ntawv thiab tau tuav hauv lub xauv ntawm kab. Qhov no yog ib qho mob phem. Thiab yog tias dheev ib daim ntawv thov nyob rau hauv nws tus kheej tsis ua hauj lwm nrog ib qho kev zam, ces qhov kev pauv yuav nyob twj ywm qhib rau lub sij hawm ntev heev. Thiab qhov no ncaj qha cuam tshuam rau PostgreSQL kev ua tau zoo. PostgreSQL yuav qeeb dua. Yog li ntawd, nws yog ib qho tseem ceeb uas yuav tau taug qab cov neeg siv khoom raws sij hawm thiab quab yuam txiav lawv txoj haujlwm. Thiab koj yuav tsum optimize koj daim ntawv thov kom cov xwm txheej zoo li no tsis tshwm sim.
Lwm cov neeg tsis zoo yog tos cov neeg siv khoom. Tab sis lawv ua phem vim muaj xwm txheej. Piv txwv li, kev lag luam yooj yim tsis ua haujlwm: nws tuaj yeem qhib kev lag luam, nqa cov xauv ntawm qee cov kab, tom qab ntawd qhov chaw hauv qhov chaws nws yuav poob, tawm hauv kev lag luam dai. Lwm tus neeg siv yuav tuaj thiab thov cov ntaub ntawv tib yam, tab sis nws yuav ntsib lub xauv, vim tias qhov kev sib pauv ntawd tau tuav cov xauv ntawm qee cov kab uas xav tau. Thiab qhov kev hloov pauv thib ob yuav nyob ib puag ncig tos thawj qhov kev hloov pauv kom tiav lossis yuam kom kaw los ntawm tus thawj tswj hwm. Yog li ntawd, cov kev lag luam tseem tos tuaj yeem sib sau thiab sau cov ntaub ntawv sib txuas txwv. Thiab thaum qhov txwv tsis pub dhau, daim ntawv thov tsis tuaj yeem ua haujlwm nrog cov ntaub ntawv ntxiv lawm. Qhov no twb yog ib qho xwm txheej ceev rau qhov project. Yog li ntawd, cov neeg siv khoom tsis zoo yuav tsum tau taug qab thiab teb rau lub sijhawm.
Lwm qhov piv txwv ntawm kev saib xyuas. Thiab twb muaj ib tug dlej dashboard ntawm no. Muaj cov ntaub ntawv hais txog kev sib txuas saum toj no. DB kev twb kev txuas - 8 pieces. Thiab nws yog tag nrho. Peb tsis muaj cov ntaub ntawv hais txog cov neeg siv khoom twg yog cov neeg siv khoom, cov neeg siv khoom tsis ua haujlwm, tsis ua dab tsi. Tsis muaj cov ntaub ntawv hais txog kev ua lag luam tseem tos thiab kev sib txuas tsis tu ncua, piv txwv li qhov no yog ib daim duab uas qhia cov naj npawb ntawm kev sib txuas thiab qhov ntawd yog nws. Thiab ces twv rau koj tus kheej.
Yog li, txhawm rau ntxiv cov ntaub ntawv no rau kev saib xyuas, koj yuav tsum nkag mus rau pg_stat_activity system saib. Yog tias koj siv sijhawm ntau hauv PostgreSQL, ces qhov no yog qhov pom zoo heev uas yuav tsum tau los ua koj tus phooj ywg, vim nws qhia txog cov haujlwm tam sim no hauv PostgreSQL, piv txwv li dab tsi tshwm sim hauv nws. Rau txhua tus txheej txheem muaj ib txoj kab sib cais uas qhia cov ntaub ntawv hais txog cov txheej txheem no: los ntawm tus tswv kev sib txuas tau tsim, raws li tus neeg siv, nyob rau hauv lub npe dab tsi, thaum pib qhov kev hloov pauv, qhov kev thov tam sim no tau ua, qhov kev thov twg tau raug ua tiav. Thiab, raws li, peb tuaj yeem ntsuas tus neeg siv khoom lub xeev siv qhov chaw stat. Hais lus sib piv, peb tuaj yeem pab pawg los ntawm daim teb no thiab tau txais cov stats uas tam sim no nyob rau hauv cov ntaub ntawv thiab cov naj npawb ntawm cov kev sib txuas uas muaj cov xwm txheej no hauv cov ntaub ntawv. Thiab peb tuaj yeem xa cov lej uas twb tau txais lawm rau peb saib thiab kos duab raws li lawv.
Nws tseem yog ib qho tseem ceeb rau kev ntsuam xyuas lub sij hawm ntawm kev ua lag luam. Kuv twb tau hais tias nws yog ib qho tseem ceeb rau kev ntsuam xyuas lub sijhawm ntawm lub tshuab nqus tsev, tab sis kev lag luam raug ntsuas tib yam. Muaj xact_start thiab query_start teb. Lawv, kuj hais lus, qhia lub sijhawm pib ntawm kev sib pauv thiab lub sijhawm pib ntawm qhov kev thov. Peb siv tam sim no () muaj nuj nqi, uas qhia lub sij hawm tam sim no, thiab rho tawm cov kev sib pauv thiab thov timestamp. Thiab peb tau txais lub sijhawm ntawm kev hloov pauv, lub sijhawm ntawm qhov kev thov.
Yog tias peb pom kev lag luam ntev, peb yuav tsum ua kom tiav lawv lawm. Rau kev thauj khoom OLTP, kev ua lag luam ntev twb tau ntau dua 1-2-3 feeb. Rau ib qho kev ua haujlwm ntawm OLAP, kev ua haujlwm ntev yog qhov qub, tab sis yog tias lawv siv sijhawm ntau tshaj li ob teev kom tiav, qhov no kuj yog ib qho cim qhia tias peb muaj skew qhov chaw.
Thaum cov neeg siv tau txuas nrog lub database, lawv pib ua haujlwm nrog peb cov ntaub ntawv. Lawv nkag mus rau cov ntxhuav, lawv nkag mus rau cov indexes kom tau cov ntaub ntawv los ntawm lub rooj. Thiab nws yog ib qho tseem ceeb los ntsuas seb cov neeg siv khoom cuam tshuam nrog cov ntaub ntawv no li cas.
Qhov no yog qhov tsim nyog txhawm rau txhawm rau ntsuas peb cov haujlwm ua haujlwm thiab nkag siab tob txog cov ntxhuav twg yog "kub tshaj" rau peb. Piv txwv li, qhov no yog qhov xav tau nyob rau hauv cov xwm txheej uas peb xav tso cov ntxhuav "kub" ntawm qee yam ceev SSD cia. Piv txwv li, qee lub rooj archive uas peb tsis tau siv ntev tuaj yeem hloov mus rau qee yam "txias" archive, rau SATA drives thiab cia lawv nyob ntawd, lawv yuav nkag tau raws li xav tau.
Qhov no kuj tseem muaj txiaj ntsig zoo rau kev txheeb xyuas qhov tsis txaus ntseeg tom qab tso tawm thiab xa tawm. Wb hais tias qhov project tau tso tawm ib co tshiab feature. Piv txwv li, peb ntxiv cov haujlwm tshiab rau kev ua haujlwm nrog cov ntaub ntawv. Thiab yog tias peb npaj cov duab siv cov duab, peb tuaj yeem pom cov kev tsis txaus ntseeg ntawm cov duab no. Piv txwv li, hloov tshiab bursts los yog rho tawm bursts. Nws yuav pom heev.
Koj tuaj yeem tshawb xyuas qhov tsis txaus ntseeg hauv "floating" txheeb cais. Nws txhais li cas? PostgreSQL muaj cov lus nug zoo heev thiab zoo heev. Thiab cov neeg tsim khoom siv sijhawm ntau rau nws txoj kev loj hlob. Nws ua haujlwm li cas? Txhawm rau ua cov phiaj xwm zoo, PostgreSQL sau cov txheeb cais ntawm kev faib cov ntaub ntawv hauv cov ntxhuav ntawm lub sijhawm qee lub sijhawm thiab nrog qee zaus. Cov no yog qhov tseem ceeb tshaj plaws: tus naj npawb ntawm cov txiaj ntsig tshwj xeeb, cov ntaub ntawv hais txog NULL hauv cov lus, ntau cov ntaub ntawv.
Raws li cov txheeb cais no, tus neeg npaj tsim ntau cov lus nug, xaiv qhov zoo tshaj plaws, thiab siv cov lus nug no los ua cov lus nug nws tus kheej thiab xa cov ntaub ntawv rov qab.
Thiab nws tshwm sim tias cov txheeb cais "float". Cov ntaub ntawv zoo thiab ntau npaum li cas hloov pauv hauv cov lus, tab sis cov txheeb cais tsis tau sau. Thiab cov phiaj xwm tsim yuav tsis zoo. Thiab yog tias peb cov phiaj xwm hloov mus ua qhov tsis zoo raws li kev soj ntsuam sau tseg, raws li cov lus, peb yuav tuaj yeem pom cov kev tsis txaus ntseeg no. Piv txwv li, qee qhov chaw cov ntaub ntawv hloov pauv tau zoo thiab hloov pauv qhov ntsuas, ib ntu dhau los ntawm lub rooj pib siv, i.e. yog tias ib qho lus nug xav tau rov qab tsuas yog 100 kab (muaj qhov txwv ntawm 100), ces qhov kev tshawb nrhiav tiav yuav ua rau cov lus nug no. Thiab qhov no ib txwm muaj kev cuam tshuam tsis zoo rau kev ua haujlwm.
Thiab peb tuaj yeem pom qhov no hauv kev saib xyuas. Thiab twb saib cov lus nug no, khiav ib qho kev piav qhia rau nws, sau cov txheeb cais, tsim qhov ntsuas tshiab ntxiv. Thiab twb teb rau qhov teeb meem no. Yog vim li cas nws tseem ceeb.
Lwm qhov piv txwv ntawm kev saib xyuas. Kuv xav tias muaj coob tus neeg paub nws vim nws nrov heev. Leej twg siv nws nyob rau hauv lawv tej yaam num ? Leej twg siv cov khoom no nrog Prometheus? Qhov tseeb yog tias nyob rau hauv tus txheej txheem repository ntawm qhov kev soj ntsuam no muaj lub dashboard rau kev ua hauj lwm nrog PostgreSQL - Prometheus. Tab sis muaj ib qho kev nthuav dav tsis zoo.
Muaj ob peb daim duab. Thiab bytes tau qhia tias kev sib sau, piv txwv li muaj 5 daim duab. Cov no yog Insert data, Update data, delete data, Fetch data and Return data. Qhov ntsuas ntsuas yog bytes. Tab sis qhov tshaj plaws yog tias kev txheeb cais hauv PostgreSQL rov qab cov ntaub ntawv hauv tuple (kab). Thiab, raws li, cov duab no yog ib txoj hauv kev zoo heev rau kev kwv yees koj cov haujlwm ntau zaus, kaum zaus, vim hais tias ib tug tuple tsis yog ib byte, ib tug tuple yog ib txoj hlua, nws yog ntau bytes thiab nws yog ib txwm sib txawv ntawm qhov ntev. Ntawd yog, suav cov haujlwm hauv bytes siv tuples yog ib txoj haujlwm tsis muaj tseeb lossis nyuaj heev. Yog li ntawd, thaum koj siv lub dashboard los yog built-in xyuas, nws yog ib qho tseem ceeb kom nkag siab tias nws ua hauj lwm kom raug thiab xa rov qab koj cov ntaub ntawv raug soj ntsuam.
Yuav ua li cas kom tau txais txheeb cais ntawm cov rooj no? Rau lub hom phiaj no, PostgreSQL muaj qee tsev neeg ntawm kev pom. Thiab lub ntsiab saib yog . User_tables - qhov no txhais tau tias cov ntxhuav tsim los sawv cev ntawm tus neeg siv. Hauv qhov sib piv, muaj cov kev pom zoo uas siv los ntawm PostgreSQL nws tus kheej. Thiab muaj cov lus qhia txog Alltables, uas suav nrog ob qho tib si thiab cov neeg siv. Koj tuaj yeem pib los ntawm ib qho ntawm lawv uas koj nyiam tshaj plaws.
Siv cov teb saum toj no koj tuaj yeem kwv yees tus naj npawb ntawm cov ntxig, hloov tshiab thiab tshem tawm. Piv txwv ntawm lub dashboard uas kuv tau siv siv cov teb no los ntsuas cov yam ntxwv ntawm kev ua haujlwm. Yog li ntawd, peb tuaj yeem txhim kho lawv. Tab sis nws tsim nyog nco ntsoov tias cov no yog tuples, tsis yog bytes, yog li peb tsis tuaj yeem ua nws hauv bytes xwb.
Raws li cov ntaub ntawv no, peb tuaj yeem tsim cov lus hu ua TopN. Piv txwv li, Top-5, Top-10. Thiab koj tuaj yeem taug qab cov ntxhuav kub uas tau siv dua tshiab dua li lwm tus. Piv txwv li, 5 lub rooj "kub" rau kev ntxig. Thiab siv cov rooj TopN no peb ntsuas peb cov haujlwm ua haujlwm thiab tuaj yeem ntsuas qhov tawg ntawm kev ua haujlwm tom qab kev tshaj tawm, hloov tshiab, thiab xa tawm.
Nws tseem yog ib qho tseem ceeb rau kev ntsuam xyuas qhov loj ntawm lub rooj, vim hais tias qee zaum cov neeg tsim tawm yob tawm qhov tshiab, thiab peb cov ntxhuav pib o ntawm lawv qhov loj me, vim tias lawv txiav txim siab ntxiv cov ntaub ntawv ntxiv, tab sis tsis tau twv seb qhov no yuav ua li cas. cuam ββtshuam qhov loj ntawm lub database. Cov xwm txheej zoo li no kuj ua rau peb xav tsis thoob.
Thiab tam sim no ib lo lus nug me me rau koj. Cov lus nug dab tsi tshwm sim thaum koj pom cov khoom thauj ntawm koj lub server server? Cov lus nug tom ntej koj muaj dab tsi?
Tab sis qhov tseeb lo lus nug tshwm sim raws li hauv qab no. Qhov kev thov dab tsi ua rau lub nra hnyav? Ntawd yog, nws tsis txaus siab los saib cov txheej txheem uas tshwm sim los ntawm kev thauj khoom. Nws yog qhov tseeb tias yog tias tus tswv tsev muaj cov ntaub ntawv, ces cov ntaub ntawv tau khiav ntawm qhov ntawd thiab nws pom tseeb tias tsuas yog cov ntaub ntawv pov tseg yuav raug pov tseg ntawm qhov ntawd. Yog tias peb qhib Sab saum toj, peb yuav pom muaj cov npe ntawm cov txheej txheem hauv PostgreSQL uas tab tom ua qee yam. Nws yuav tsis meej los ntawm Top lawv ua dab tsi.
Raws li, koj yuav tsum nrhiav cov lus nug uas ua rau lub siab tshaj plaws load, vim hais tias cov lus nug tuning, raws li txoj cai, muab cov txiaj ntsig ntau dua li kho PostgreSQL lossis kev teeb tsa kev ua haujlwm, lossis txawm tias kho kho vajtse. Raws li kuv kwv yees, qhov no yog kwv yees li 80-85-90%. Thiab qhov no ua tiav sai dua. Nws yog nrawm dua los kho qhov kev thov dua li kho qhov teeb tsa, teem caij rov pib dua, tshwj xeeb tshaj yog tias cov ntaub ntawv tsis tuaj yeem rov pib dua, lossis ntxiv kho vajtse. Nws yooj yim dua los sau cov lus nug nyob qhov twg lossis ntxiv qhov ntsuas kom tau txais txiaj ntsig zoo dua los ntawm cov lus nug no.
Yog li ntawd, nws yog ib qho tsim nyog los saib xyuas cov kev thov thiab lawv qhov txaus. Cia peb ua lwm yam piv txwv ntawm kev saib xyuas. Thiab ntawm no, ib yam nkaus, zoo li muaj kev saib xyuas zoo heev. Muaj cov ntaub ntawv ntawm replication, muaj cov ntaub ntawv ntawm throughput, thaiv, kev siv peev txheej. Txhua yam zoo, tab sis tsis muaj ntaub ntawv ntawm kev thov. Nws tsis paub meej tias cov lus nug tau khiav hauv peb cov ntaub ntawv, lawv tau khiav ntev npaum li cas, pes tsawg ntawm cov lus nug no. Peb ib txwm yuav tsum muaj cov ntaub ntawv no hauv peb txoj kev saib xyuas.
Thiab kom tau txais cov ntaub ntawv no peb tuaj yeem siv pg_stat_statements module. Raws li nws, koj tuaj yeem tsim ntau hom duab. Piv txwv li, koj tuaj yeem tau txais cov ntaub ntawv ntawm cov lus nug nquag tshaj plaws, uas yog, ntawm cov lus nug uas tau ua ntau zaus. Yog lawm, tom qab xa tawm nws kuj tseem ceeb heev los saib nws thiab nkag siab yog tias muaj kev thov nce ntxiv.
Koj tuaj yeem saib xyuas cov lus nug ntev tshaj plaws, uas yog, cov lus nug uas siv sijhawm ntev tshaj plaws kom tiav. Lawv khiav ntawm lub processor, lawv haus I / O. Peb kuj tuaj yeem ntsuas qhov no siv cov teb total_time, mean_time, blk_write_time thiab blk_read_time.
Peb tuaj yeem soj ntsuam thiab saib xyuas qhov kev thov hnyav tshaj plaws ntawm kev siv cov peev txheej, cov uas nyeem los ntawm disk, uas ua haujlwm nrog kev nco, lossis, hloov pauv, tsim qee yam kev sau ntawv.
Peb tuaj yeem ntsuas qhov kev thov dav dav tshaj plaws. Cov no yog cov lus nug uas xa rov qab ntau cov kab. Piv txwv li, qhov no tuaj yeem yog qee qhov kev thov uas lawv tsis nco qab teeb tsa. Thiab nws tsuas yog rov qab tag nrho cov ntsiab lus ntawm lub rooj lossis cov lus nug hla cov lus nug.
Thiab koj tuaj yeem saib xyuas cov lus nug uas siv cov ntaub ntawv ib ntus lossis cov ntxhuav ib ntus.
Thiab peb tseem muaj cov txheej txheem keeb kwm yav dhau. Cov txheej txheem keeb kwm yav dhau yog qhov chaw kuaj xyuas lossis lawv tseem hu ua checkpoints, cov no yog autovacuum thiab replication.
Lwm qhov piv txwv ntawm kev saib xyuas. Muaj ib tug Maintenance tab nyob rau sab laug, mus rau nws thiab vam tias yuav pom tej yam muaj txiaj ntsig. Tab sis ntawm no tsuas yog lub sijhawm ntawm lub tshuab nqus tsev ua haujlwm thiab kev txheeb cais, tsis muaj dab tsi ntxiv. Qhov no yog cov ntaub ntawv tsis zoo, yog li peb ib txwm yuav tsum muaj cov ntaub ntawv hais txog yuav ua li cas cov txheej txheem keeb kwm yav dhau ua haujlwm hauv peb cov ntaub ntawv thiab seb puas muaj teeb meem los ntawm lawv txoj haujlwm.
Thaum peb saib cov chaw kuaj xyuas, peb yuav tsum nco ntsoov tias cov chaw kuaj xyuas cov nplooj ntawv qias neeg los ntawm thaj chaw nco mus rau disk, tom qab ntawd tsim qhov chaw kuaj xyuas. Thiab qhov chaw kuaj xyuas no tuaj yeem siv los ua qhov chaw rov qab yog tias PostgreSQL tau txiav tam sim ntawd thaum muaj xwm txheej ceev.
Yog li, txhawm rau txhawm rau tshem tawm tag nrho cov nplooj ntawv "qhuav" rau disk, koj yuav tsum ua qee qhov kev sau ntawv. Thiab, raws li txoj cai, ntawm cov kab ke nrog ntau lub cim xeeb, qhov no yog ntau heev. Thiab yog tias peb ua cov chaw kuaj xyuas ntau zaus hauv lub sijhawm luv, ces kev ua haujlwm disk yuav poob qis heev. Thiab cov neeg thov kev thov yuav raug kev txom nyem los ntawm qhov tsis muaj peev txheej. Lawv yuav sib tw rau cov peev txheej thiab tsis muaj kev tsim khoom.
Raws li, dhau ntawm pg_stat_bgwriter siv cov lus teev tseg peb tuaj yeem saib xyuas tus naj npawb ntawm cov chaw kuaj mob uas tshwm sim. Thiab yog tias peb muaj ntau qhov chaw kuaj xyuas ntau lub sijhawm (hauv 10-15-20 feeb, hauv ib nrab teev), piv txwv li, 3-4-5, qhov no tuaj yeem ua teeb meem. Thiab koj twb yuav tsum tau saib nyob rau hauv lub database, saib nyob rau hauv lub configuration, dab tsi ua rau xws li ib tug abundance ntawm checkpoints. Tej zaum yuav muaj qee yam kev kaw tseg loj mus. Peb tuaj yeem ntsuas qhov ua haujlwm, vim tias peb twb tau ntxiv cov duab ua haujlwm. Peb tuaj yeem hloov kho qhov ntsuas qhov ntsuas qhov ntsuas thiab xyuas kom meej tias lawv tsis cuam tshuam rau cov lus nug kev ua tau zoo.
Kuv rov qab los rau autovacuum dua vim tias nws yog ib yam, raws li kuv tau hais, uas tuaj yeem yooj yim ntxiv ob qho tib si disk thiab nug kev ua haujlwm, yog li nws yog ib qho tseem ceeb rau kwv yees tus nqi ntawm autovacuum.
Tus naj npawb ntawm cov neeg ua haujlwm autovacuum hauv cov ntaub ntawv tsuas yog txwv. Los ntawm lub neej ntawd, muaj peb ntawm lawv, yog li yog tias peb ib txwm muaj peb tus neeg ua haujlwm hauv cov ntaub ntawv, qhov no txhais tau tias peb lub autovacuum tsis tau teeb tsa, peb yuav tsum tau tsa cov kev txwv, kho qhov chaw autovacuum thiab nkag mus rau hauv kev teeb tsa.
Nws yog ib qho tseem ceeb los soj ntsuam cov neeg ua haujlwm lub tshuab nqus tsev uas peb muaj. Txawm hais tias nws tau pib los ntawm cov neeg siv, DBA tuaj thiab manually launched qee yam ntawm lub tshuab nqus tsev, thiab qhov no tsim ib qho load. Peb muaj tej yam teeb meem. Los yog qhov no yog tus naj npawb ntawm lub tshuab nqus tsev uas unscrew lub lag luam txee. Rau qee qhov versions ntawm PostgreSQL cov no yog lub tshuab nqus tsev hnyav heev. Thiab lawv tuaj yeem yooj yim ntxiv qhov kev ua tau zoo vim lawv nyeem tag nrho cov lus, luam theej duab tag nrho cov blocks hauv lub rooj.
Thiab, ntawm chav kawm, lub sijhawm ntawm lub tshuab nqus tsev. Yog tias peb muaj lub tshuab nqus tsev ntev ntev uas khiav mus ntev heev, qhov no txhais tau hais tias peb yuav tsum tau ua tib zoo saib xyuas lub tshuab nqus tsev thiab tej zaum rov xav txog nws cov chaw. Vim tias qhov xwm txheej yuav tshwm sim thaum lub tshuab nqus tsev ua haujlwm ntawm lub rooj ntev ntev (3-4 teev), tab sis thaum lub sijhawm lub tshuab nqus tsev ua haujlwm, ntau cov kab tuag tau tswj kom khaws cia hauv lub rooj dua. Thiab sai li sai tau thaum lub tshuab nqus tsev ua tiav, nws yuav tsum nqus lub rooj no dua. Thiab peb tuaj rau qhov xwm txheej - lub tshuab nqus tsev tsis muaj qhov kawg. Thiab nyob rau hauv cov ntaub ntawv no, lub tshuab nqus tsev tsis tiv nrog nws cov hauj lwm, thiab cov ntxhuav maj mam pib o nyob rau hauv loj, txawm hais tias lub ntim ntawm pab tau cov ntaub ntawv nyob rau hauv nws tseem zoo li qub. Yog li ntawd, thaum lub sij hawm nqus tsev vacuums ntev, peb ib txwm saib cov configuration thiab sim optimize nws, tab sis nyob rau tib lub sij hawm kom cov kev ua tau zoo ntawm cov neeg thov tsis raug kev txom nyem.
Niaj hnub no muaj kev xyaum tsis muaj PostgreSQL installation uas tsis muaj streaming replication. Replication yog tus txheej txheem ntawm kev hloov cov ntaub ntawv los ntawm tus tswv mus rau ib qho replica.
Replication hauv PostgreSQL yog ua tiav los ntawm kev sib pauv log. Tus wizard tsim ib daim ntawv teev kev sib pauv. Daim ntawv teev kev sib pauv mus dhau lub network txuas mus rau qhov hloov pauv, thiab tom qab ntawd nws rov ua dua tshiab ntawm qhov hloov pauv. Nws yog qhov yooj yim.
Raws li, pg_stat_replication saib yog siv los saib xyuas qhov kev lag luam rov ua dua. Tab sis tsis yog txhua yam yooj yim nrog nws. Hauv version 10, qhov pom tau dhau los ua ntau qhov kev hloov pauv. Ua ntej, qee qhov chaw tau hloov npe. Thiab qee qhov chaw tau ntxiv lawm. Hauv version 10, cov teb tau tshwm sim uas tso cai rau koj los kwv yees qhov rov ua lag luam hauv vib nas this. Nws yooj yim heev. Ua ntej version 10, nws muaj peev xwm kwv yees qhov rov ua lag luam hauv bytes. Qhov kev xaiv no tseem nyob hauv version 10, piv txwv li koj tuaj yeem xaiv qhov yooj yim dua rau koj - kwv yees qhov lag luam hauv bytes lossis kwv yees qhov lag hauv vib nas this. Ntau tus neeg ua ob qho tib si.
Tab sis txawm li cas los xij, txhawm rau ntsuas qhov rov ua dua lag luam, koj yuav tsum paub txog txoj haujlwm ntawm lub cav hauv kev sib pauv. Thiab cov kev hloov pauv no yog raws nraim hauv pg_stat_replication saib. Hais txog kev hais lus, peb tuaj yeem nqa ob lub ntsiab lus hauv kev sib pauv log siv pg_xlog_location_diff() muaj nuj nqi. Xam cov delta nruab nrab ntawm lawv thiab tau txais qhov rov ua lag luam hauv bytes. Nws yooj yim heev thiab yooj yim.
Hauv version 10, qhov haujlwm no tau hloov npe rau pg_wal_lsn_diff(). Feem ntau, hauv txhua txoj haujlwm, kev pom, thiab kev siv hluav taws xob uas lo lus "xlog" tshwm sim, nws tau hloov nrog tus nqi "wal". Qhov no siv rau ob qho tib si saib thiab ua haujlwm. Qhov no yog ib qho innovation.
Ntxiv rau, hauv version 10, kab tau ntxiv uas tshwj xeeb qhia qhov lag. Cov no yog sau lag, flush lag, replay lag. Qhov ntawd yog, nws yog ib qho tseem ceeb los saib xyuas tej yam no. Yog tias peb pom tias peb muaj qhov rov ua dua tshiab, peb yuav tsum tshawb xyuas vim li cas nws tshwm sim, qhov twg los ntawm thiab kho qhov teeb meem.
Yuav luag txhua yam yog nyob rau hauv kev txiav txim nrog system metrics. Thaum twg kev soj ntsuam pib, nws pib nrog kev ntsuas qhov system. Qhov no yog pov tseg ntawm processors, nco, swap, network thiab disk. Txawm li cas los xij, ntau qhov ntsuas tsis nyob ntawd los ntawm lub neej ntawd.
Yog tias txhua yam nyob rau hauv kev txiav txim nrog cov txheej txheem rov ua dua, ces muaj teeb meem nrog kev rov ua dua disk. Raws li txoj cai, saib xyuas cov neeg tsim khoom ntxiv cov ntaub ntawv hais txog kev nkag mus. Nws tuaj yeem ua hauv iops lossis bytes. Tab sis lawv tsis nco qab txog latency thiab kev siv cov khoom siv disk. Cov no yog cov tsis tseem ceeb dua uas tso cai rau peb los ntsuas seb peb cov disks thauj khoom li cas thiab lawv qeeb npaum li cas. Yog tias peb muaj latency siab, qhov no txhais tau hais tias muaj qee qhov teeb meem nrog cov disks. Yog tias peb muaj kev siv siab, nws txhais tau hais tias cov disks tsis daws teeb meem. Cov no yog cov yam ntxwv zoo dua li kev hla dhau.
Ntxiv mus, cov txheeb cais no tseem tuaj yeem tau txais los ntawm /proc cov ntaub ntawv, raws li tau ua tiav rau cov txheej txheem rov ua dua tshiab. Kuv tsis paub vim li cas cov ntaub ntawv no tsis ntxiv rau kev saib xyuas. Tab sis txawm li cas los xij, nws tseem ceeb heev uas yuav tsum muaj qhov no hauv koj qhov kev saib xyuas.
Tib yam siv rau network interfaces. Muaj cov ntaub ntawv hais txog kev sib txuas hauv network hauv pob ntawv, hauv bytes, tab sis txawm li cas los xij tsis muaj cov ntaub ntawv hais txog latency thiab tsis muaj ntaub ntawv hais txog kev siv, txawm hais tias qhov no kuj yog cov ntaub ntawv tseem ceeb.
Txhua qhov kev soj ntsuam muaj qhov tsis zoo. Thiab tsis muaj teeb meem dab tsi ntawm kev saib xyuas koj ua, nws yuav tsis ua raws li qee yam kev cai. Tab sis txawm li cas los xij, lawv tab tom txhim kho, cov yam ntxwv tshiab thiab cov khoom tshiab tau ntxiv, yog li xaiv ib yam dab tsi thiab ua kom tiav.
Thiab txhawm rau ua kom tiav, koj yuav tsum muaj lub tswv yim ntawm qhov kev txheeb cais tau muab txhais li cas thiab koj tuaj yeem siv lawv li cas los daws cov teeb meem.
Thiab ob peb lub ntsiab lus tseem ceeb:
- Koj yuav tsum tau saib xyuas qhov muaj thiab muaj cov dashboards kom koj tuaj yeem soj ntsuam sai sai tias txhua yam nyob rau hauv kev txiav txim nrog cov ntaub ntawv.
- Koj ib txwm yuav tsum muaj lub tswv yim ntawm cov neeg siv khoom ua haujlwm nrog koj cov ntaub ntawv txhawm rau txhawm rau tshem tawm cov neeg siv khoom phem thiab tua lawv.
- Nws yog ib qho tseem ceeb los ntsuas seb cov neeg siv khoom no ua haujlwm li cas nrog cov ntaub ntawv. Koj yuav tsum muaj lub tswv yim txog koj cov haujlwm.
- Nws yog ib qho tseem ceeb los ntsuas seb qhov kev ua haujlwm no raug tsim li cas, nrog kev pab ntawm cov lus nug dab tsi. Koj tuaj yeem ntsuas cov lus nug, koj tuaj yeem ua kom zoo rau lawv, rov ua dua lawv, tsim cov ntsuas rau lawv. Nws tseem ceeb heev.
- Cov txheej txheem keeb kwm yav dhau tuaj yeem cuam tshuam tsis zoo rau cov neeg thov kev thov, yog li nws tseem ceeb heev uas yuav tsum tau saib xyuas tias lawv tsis siv ntau cov peev txheej.
- Kev ntsuas qhov system tso cai rau koj los tsim cov phiaj xwm rau kev ntsuas thiab ua kom muaj peev xwm ntawm koj cov servers, yog li nws tseem ceeb heev rau kev taug qab thiab ntsuas lawv ib yam nkaus.
Yog tias koj txaus siab rau lub ntsiab lus no, koj tuaj yeem ua raws li cov kev txuas no.
- Qhov no yog cov ntaub ntawv raug cai los ntawm tus sau txheeb cais. Muaj ib qho kev piav qhia ntawm txhua qhov kev txheeb xyuas thiab kev piav qhia ntawm txhua qhov chaw. Koj tuaj yeem nyeem, nkag siab thiab txheeb xyuas lawv. Thiab raws li lawv, tsim koj cov duab kos thiab ntxiv rau koj qhov kev saib xyuas.
Piv txwv thov:
Nov yog peb lub chaw khaws ntaub ntawv thiab kuv tus kheej. Lawv muaj cov lus nug piv txwv. Tsis muaj lus nug los ntawm kev xaiv * los ntawm koob muaj. Muaj cov lus nug npaj txhij nrog kev koom nrog, siv cov haujlwm nthuav dav uas tso cai rau koj tig cov lej nyoos rau hauv cov ntawv nyeem tau yooj yim, piv txwv li cov no yog bytes, sijhawm. Koj tuaj yeem khaws lawv, saib lawv, txheeb xyuas lawv, ntxiv rau koj qhov kev saib xyuas, tsim koj qhov kev saib xyuas raws li lawv.
Lus nug
Lo lus nug: Koj tau hais tias koj yuav tsis tshaj tawm cov npe, tab sis kuv tseem xav paub - dab tsi ntawm dashboards koj siv hauv koj cov haujlwm?
Teb: Nws txawv. Nws tshwm sim tias peb tuaj rau ib tus neeg siv khoom thiab nws twb muaj nws tus kheej saib xyuas. Thiab peb qhia rau cov neeg siv khoom txog qhov yuav tsum tau ntxiv rau lawv cov kev saib xyuas. Qhov xwm txheej phem tshaj plaws yog nrog Zabbix. Vim tias nws tsis muaj peev xwm tsim cov duab TopN. Peb tus kheej siv , vim peb tau sab laj nrog cov neeg no ntawm kev saib xyuas. Lawv tau saib xyuas PostgreSQL raws li peb cov kev qhia tshwj xeeb. Kuv tabtom sau kuv tus kheej tsiaj-project, uas sau cov ntaub ntawv ntawm Prometheus thiab muab nws rau hauv . Kuv txoj haujlwm yog los tsim kuv tus kheej exporter hauv Prometheus thiab tom qab ntawd muab txhua yam hauv Grafana.
Nqe Lus Nug: Puas muaj cov analogues ntawm AWR cov ntaub ntawv lossis ... kev sib sau ua ke? Koj puas paub txog tej yam zoo li no?
Teb: Yog, Kuv paub tias AWR yog dab tsi, nws yog qhov txias. Tam sim no muaj ntau lub tsheb kauj vab uas siv kwv yees li cov qauv hauv qab no. Nyob rau qee lub sijhawm, qee qhov kev qhia hauv qab tau sau rau tib lub PostgreSQL lossis rau ib qho chaw cia. Koj tuaj yeem google lawv hauv Internet, lawv nyob ntawd. Ib qho ntawm cov neeg tsim khoom zoo li no yog zaum ntawm lub rooj sib tham sql.ru hauv PostgreSQL xov. Koj tuaj yeem ntes nws nyob ntawd. Yog, muaj cov khoom zoo li no, lawv tuaj yeem siv tau. Ntxiv rau hauv nws Kuv kuj sau ib yam uas tso cai rau koj ua ib yam.
PS1 Yog tias koj siv postgres_exporter, dab tsi dashboard koj siv? Muaj ob peb ntawm lawv. Lawv twb dhau lawm. Tej zaum cov zej zog yuav tsim cov qauv tshiab?
PS2 Tshem tawm pganalyze vim nws yog tus tswv SaaS muab uas tsom rau kev saib xyuas kev ua tau zoo thiab cov lus qhia kho qhov muag.
Tsuas yog cov neeg siv sau npe tuaj yeem koom nrog hauv daim ntawv ntsuam xyuas. thov.
Qhov twg tus kheej-hosted postgresql saib xyuas (nrog dashboard) koj puas xav txog qhov zoo tshaj plaws?
-
30,0%Zabbix + ntxiv los ntawm Alexey Lesovsky lossis zabbix 4.4 lossis libzbxpgsql + zabbix libzbxpgsql + zabbix3
-
0,0%https://github.com/lesovsky/pgcenter0
-
0,0%https://github.com/pg-monz/pg_monz0
-
20,0%https://github.com/cybertec-postgresql/pgwatch22
-
20,0%https://github.com/postgrespro/mamonsu2
-
0,0%https://www.percona.com/doc/percona-monitoring-and-management/conf-postgres.html0
-
10,0%pganalyze yog tus tswv SaaS - Kuv tsis tuaj yeem rho tawm nws 1
-
10,0%https://github.com/powa-team/powa1
-
0,0%https://github.com/darold/pgbadger0
-
0,0%https://github.com/darold/pgcluu0
-
0,0%https://github.com/zalando/PGObserver0
-
10,0%https://github.com/spotify/postgresql-metrics1
10 cov neeg siv pov npav. 26 cov neeg siv txwv tsis pub siv.
Tau qhov twg los: www.hab.com