Rubutun rahoton Alexey Lesovsky na 2015 " Zurfafa zurfafa cikin kididdigar ciki na PostgreSQL"
Sanarwa daga marubucin rahoton: Na lura cewa wannan rahoto yana kwanan watan Nuwamba 2015 - fiye da shekaru 4 sun shude kuma lokaci mai yawa ya wuce. An daina tallafawa sigar 9.4 da aka tattauna a cikin rahoton. A cikin shekaru 4 da suka gabata, an sake fitar da sabbin abubuwan 5 waɗanda ke da sabbin abubuwa da yawa, haɓakawa da canje-canje game da ƙididdiga, kuma wasu kayan sun tsufa kuma basu dace ba. Yayin da nake bita, na yi ƙoƙari na yi wa waɗannan wuraren alama don kada in batar da mai karatu. Ban sake rubuta wadannan nassosin ba, suna da yawa kuma sakamakon zai zama wani rahoto na daban.
PostgreSQL DBMS wata babbar hanya ce, kuma wannan tsarin ya ƙunshi tsarin ƙasa da yawa, haɗin gwiwar aikin wanda kai tsaye yana shafar aikin DBMS. A lokacin aiki, ana tattara ƙididdiga da bayanai game da ayyukan da aka haɗa, wanda ke ba ku damar kimanta tasirin PostgreSQL kuma ku ɗauki matakan inganta aikin. Koyaya, waɗannan bayanan suna da yawa kuma an gabatar da su a cikin siga mai sauƙi. Sarrafa wannan bayanin da fassara shi wani lokaci aikin gaba ɗaya ne mara nauyi, kuma “zoo” na kayan aiki da abubuwan amfani na iya rikitar da sauƙi ko da DBA mai ci gaba.
Barka da rana Sunana Aleksey. Kamar yadda Ilya ya ce, zan yi magana game da kididdigar PostgreSQL.
PostgreSQL kididdigar ayyuka. PostgreSQL yana da ƙididdiga guda biyu. Kididdigar ayyukan da za a tattauna. Da kuma ƙididdiga masu tsarawa game da rarraba bayanai. Zan yi magana musamman game da kididdigar ayyuka na PostgreSQL, wanda ke ba mu damar yin hukunci game da aiki kuma ta yaya inganta shi.
Zan gaya muku yadda ake amfani da ƙididdiga yadda ya kamata don magance matsaloli iri-iri da kuke da su ko ƙila.
Menene ba zai kasance a cikin rahoton ba? A cikin rahoton ba zan tabo kididdigar jadawalin ba, saboda... Wannan wani batu ne na daban don rahoto na daban kan yadda ake adana bayanai a cikin ma'ajin bayanai da kuma yadda mai tsarawa ke samun ra'ayi game da halaye masu inganci da ƙididdiga na wannan bayanan.
Kuma ba za a sami sake dubawa na kayan aiki ba, ba zan kwatanta samfurin ɗaya da wani ba. Ba za a yi talla ba. Mu ajiye wancan gefe.
Ina so in nuna muku cewa yin amfani da kididdiga yana da amfani. Wajibi ne. Yana da lafiya don amfani. Duk abin da muke buƙata shine SQL na yau da kullun da ilimin asali na SQL.
Kuma bari mu yi magana game da abin da statistics zabi don warware matsaloli.
Idan muka kalli PostgreSQL kuma muka gudanar da umarni akan tsarin aiki don duba matakai, zamu ga "akwatin baki". Za mu ga wasu matakai da suke yin wani abu, kuma daga sunan za mu iya tunanin abin da suke yi a can, abin da suke yi. Amma, a zahiri, akwatin baki ne, ba za mu iya duba ciki ba.
Muna iya ganin nauyin CPU a ciki top, za mu iya kallon amfani da ƙwaƙwalwar ajiya ta wasu kayan aikin tsarin, amma ba za mu iya duba cikin PostgreSQL ba. Don wannan muna buƙatar wasu kayan aikin.
Kuma in ci gaba, zan gaya muku inda lokaci ya ƙare. Idan muka yi tunanin PostgreSQL a cikin irin wannan zane, za mu iya amsa inda aka kashe lokaci. Waɗannan abubuwa biyu ne: yana sarrafa buƙatun abokin ciniki daga aikace-aikace da kuma ayyukan baya da PostgreSQL ke yi don ci gaba da gudana.
Idan muka fara kallon kusurwar hagu ta sama, za mu iya ganin yadda ake sarrafa buƙatun abokin ciniki. Buƙatar ta fito daga aikace-aikacen kuma an buɗe taron abokin ciniki don ƙarin aiki. Ana aika buƙatar zuwa ga mai tsarawa. Mai tsarawa yana gina tsarin tambaya. Aika shi gaba don aiwatarwa. Akwai wani nau'i na toshe bayanan shigarwa/fitarwa mai alaƙa da tebur da fihirisa. Ana karanta bayanan da suka wajaba daga faifai zuwa ƙwaƙwalwar ajiya zuwa wani yanki na musamman "masu buffers". Sakamakon buƙatun, idan sabuntawa ne, sharewa, ana yin rikodin su a cikin log ɗin ciniki a cikin WAL. Wasu bayanan ƙididdiga suna ƙarewa a cikin log ko mai tara ƙididdiga. Kuma sakamakon buƙatar an aika zuwa ga abokin ciniki. Bayan haka abokin ciniki na iya sake maimaita komai tare da sabon buƙatar.
Me game da ayyuka na baya da tsarin baya? Muna da matakai da yawa waɗanda ke kiyaye rumbun adana bayanai sama da aiki a yanayin aiki na yau da kullun. Hakanan za'a taɓa waɗannan hanyoyin a cikin rahoton: autovacuum, checkpointer, hanyoyin da suka danganci maimaitawa, marubucin baya. Zan tabo kowannensu yayin da nake rahoto.
Wadanne matsaloli ne ake samu game da kididdiga?
- Akwai bayanai da yawa. PostgreSQL 9.4 yana ba da ma'auni 109 don duba bayanan ƙididdiga. Duk da haka, idan rumbun adana bayanai ya adana tebur da yawa, tsare-tsare, ma'ajin bayanai, to duk waɗannan ma'auni dole ne a ninka su ta hanyar adadin tebur, bayanan bayanai. Wato akwai ma ƙarin bayani. Kuma abu ne mai sauqi a nutse a cikinsa.
- Matsala ta gaba ita ce ƙididdiga tana wakiltar ƙididdiga. Idan muka dubi waɗannan ƙididdiga, za mu ga kullun ƙara yawan ƙididdiga. Kuma idan lokaci mai yawa ya wuce tun lokacin da aka sake saita kididdigar, za mu ga dabi'u a cikin biliyoyin. Kuma ba sa gaya mana komai.
- Babu labari. Idan kuna da wani nau'i na gazawar, wani abu ya fadi 15-30 mintuna da suka wuce, ba za ku iya amfani da kididdiga ba kuma ku ga abin da ya faru 15-30 mintuna da suka wuce. Wannan matsala ce.
- Rashin kayan aikin da aka gina a cikin PostgreSQL matsala ce. Masu haɓaka kernel ba su samar da wani abin amfani ba. Ba su da wani abu makamancin haka. Suna ba da ƙididdiga kawai a cikin ma'ajin bayanai. Yi amfani da shi, yi buƙatunsa, yi duk abin da kuke so.
- Tun da babu kayan aiki da aka gina a cikin PostgreSQL, wannan yana haifar da wata matsala. Yawancin kayan aikin ɓangare na uku. Duk kamfani da ke da hannu kai tsaye ko sama da haka yana ƙoƙarin rubuta nasa shirin. Kuma a sakamakon haka, al'umma suna da kayan aiki da yawa waɗanda za a iya amfani da su don yin aiki tare da kididdiga. Kuma wasu kayan aikin suna da wasu iyakoki, wasu kayan aikin ba su da wasu iyawa, ko kuma akwai wasu sabbin iyakoki. Kuma wani yanayi ya taso cewa kuna buƙatar amfani da kayan aiki biyu, uku ko huɗu waɗanda ke mamaye juna kuma suna da ayyuka daban-daban. Wannan ba shi da daɗi sosai.
Menene ya biyo baya daga wannan? Yana da mahimmanci don samun damar ɗaukar kididdiga kai tsaye, don kada ku dogara da shirye-shirye, ko ta yaya inganta waɗannan shirye-shiryen da kanku: ƙara wasu ayyuka don samun amfanin kanku.
Kuma kuna buƙatar ilimin asali na SQL. Don samun wasu bayanai daga kididdiga, kuna buƙatar ƙirƙirar tambayoyin SQL, watau kuna buƙatar sanin yadda ake haɗa zaɓi da haɗawa.
Kididdiga ta gaya mana abubuwa da yawa. Ana iya raba su zuwa rukuni.
- Rukunin farko shine abubuwan da ke faruwa a cikin ma'ajin bayanai. Wannan shine lokacin da wasu al'amura suka faru a cikin ma'ajin bayanai: buƙatu, samun dama ga tebur, autovacuum, aikatawa, to waɗannan duk abubuwan ne. Ana ƙara ƙididdiga masu dacewa da waɗannan abubuwan da suka faru. Kuma za mu iya bin diddigin waɗannan abubuwan.
- Kashi na biyu shine kaddarorin abubuwa kamar teburi da ma'ajin bayanai. Suna da kaddarorin. Wannan shine girman teburin. Za mu iya bin diddigin ci gaban tebura da haɓakar fihirisa. Muna iya ganin canje-canje a cikin kuzari.
- Kuma nau'i na uku shine lokacin da aka kashe akan taron. Roƙon lamari ne. Yana da nasa ƙayyadaddun ma'auni na tsawon lokaci. An fara nan, ya ƙare a nan. Za mu iya bin sa. Ko dai lokacin da ake ɗauka don karanta block daga faifai ko rubuta shi. Irin waɗannan abubuwa kuma ana bin su.
An gabatar da tushen ƙididdiga kamar haka:
- A cikin ƙwaƙwalwar ajiyar da aka raba (shared buffers) akwai wani yanki don adana bayanan da ba daidai ba, akwai kuma waɗancan ƙididdiga waɗanda ake ƙara haɓakawa koyaushe lokacin da wasu abubuwan suka faru, ko kuma wasu lokuta suka taso a cikin aiki na bayanan.
- Duk waɗannan ƙididdiga ba su da isa ga mai amfani kuma ba ma isa ga mai gudanarwa ba. Waɗannan ƙananan abubuwa ne. Don samun dama gare su, PostgreSQL yana ba da hanyar sadarwa ta hanyar ayyukan SQL. Za mu iya yin zaɓin jifa ta amfani da waɗannan ayyuka kuma mu sami wani nau'in awo (ko saitin ma'auni).
- Koyaya, yin amfani da waɗannan ayyukan ba koyaushe bane dacewa, don haka ayyuka sune tushen ra'ayoyi (RA'ayoyi). Waɗannan teburi ne na kama-da-wane waɗanda ke ba da ƙididdiga akan takamaiman tsarin tsarin ƙasa, ko kan wasu saitin abubuwan da suka faru a cikin bayanan.
- Waɗannan ra'ayoyin da aka haɗa (VIEWs) sune babban haɗin mai amfani don aiki tare da ƙididdiga. Ana samun su ta tsohuwa ba tare da ƙarin saitunan ba, zaku iya amfani da su nan da nan, duba su, kuma ku karɓi bayanai daga gare su. Sannan akwai gudunmawa. Taimako na hukuma ne. Kuna iya shigar da kunshin postgresql-contrib (misali, postgresql94-contrib), loda tsarin da ake buƙata a cikin tsarin, saka sigogi don shi, sake kunna PostgreSQL kuma zaku iya amfani dashi. (A kula. Dangane da rarrabawa, a cikin sigar kwanan nan kunshin gudummawar wani ɓangare ne na babban fakitin).
- Kuma akwai gudummawar da ba na hukuma ba. Ba a haɗa su cikin daidaitaccen rarraba PostgreSQL ba. Dole ne a haɗa su ko a sanya su azaman ɗakin karatu. Zaɓuɓɓukan na iya zama daban-daban, ya danganta da abin da mai haɓaka wannan tallafin da ba na hukuma ba ya fito da shi.
Wannan zane-zane yana gabatar da duk waɗannan RA'ayoyi da wasu ayyuka waɗanda ke cikin PostgreSQL 9.4. Kamar yadda muke gani, suna da yawa. Kuma yana da sauƙi a ruɗe idan kun haɗu da shi a karon farko.
Koyaya, idan muka ɗauki hoton da ya gabata Как тратится время на PostgreSQL kuma masu dacewa da wannan jerin, muna samun wannan hoton. Za mu iya amfani da kowane ra'ayi (VIEWs) ko kowane aiki don dalili ɗaya ko wani don samun ƙididdiga masu dacewa lokacin da PostgreSQL ke gudana. Kuma za mu iya riga samun wasu bayanai game da aiki na subsystem.
Abu na farko da zamu duba shine pg_stat_database. Kamar yadda muke iya gani, wannan wasan kwaikwayo ne. Akwai bayanai da yawa a cikinsa. Mafi bambance-bambancen bayanai. Kuma yana ba da ilimi mai amfani sosai ga abubuwan da ke faruwa a cikin ma'ajin mu.
Wadanne abubuwa masu amfani za mu iya dauka daga can? Bari mu fara da abubuwa mafi sauƙi.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Abu na farko da za mu iya dubawa shine yawan bugun cache. Adadin bugun cache ma'auni ne mai amfani. Yana ba ku damar ƙididdige adadin bayanai da aka karɓa daga ma'ajiyar buffers da aka raba da nawa ake karantawa daga faifai.
A fili yake cewa da yawan cache ɗin da muke da shi, mafi kyau. Muna auna wannan ma'aunin a matsayin kashi. Kuma, alal misali, idan adadin mu na waɗannan cache hits ya wuce 90%, to wannan yana da kyau. Idan ya faɗi ƙasa da 90%, yana nufin ba mu da isassun ƙwaƙwalwar ajiyar da za ta iya riƙe hot shugaban bayanai a ƙwaƙwalwar ajiya. Kuma don amfani da wannan bayanan, an tilasta PostgreSQL don shiga diski kuma wannan yana da hankali fiye da idan an karanta bayanan daga ƙwaƙwalwar ajiya. Kuma kuna buƙatar yin tunani game da haɓaka ƙwaƙwalwar ajiya: ko dai ƙara abubuwan haɗin gwiwa, ko haɓaka ƙwaƙwalwar ajiyar hardware (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;Me kuma za ku iya ɗauka daga wannan wasan kwaikwayon? Kuna iya ganin abubuwan da ke faruwa a cikin ma'ajin bayanai. Menene aka nuna a nan? Akwai aikatawa, jujjuyawa, ƙirƙirar fayilolin wucin gadi, girman su, maƙasudin maƙiya da rikice-rikice.
Za mu iya amfani da wannan buƙatar. Wannan SQL kyakkyawa ne mai sauƙi. Kuma za mu iya duba wannan bayanai a nan.
Kuma a nan ne ƙimar ƙima. Muna duban rabon aikatawa da sake dawowa. Ƙaddamarwa shine nasarar tabbatar da ciniki. Rollbacks shine juzu'i, watau ciniki ya yi wani aiki, ya takura ma'ajin bayanai, ya ƙididdige wani abu, sannan aka yi rashin nasara kuma an watsar da sakamakon cinikin. Wato yawan sake dawowa kullum yana karuwa yana da kyau. Kuma ya kamata ka ko ta yaya ka guje su, kuma ka gyara lambar don kada hakan ya faru.
Rikici yana da alaƙa da maimaitawa. Kuma suma a guji su. Idan kuna da wasu tambayoyin da aka aiwatar akan kwafi kuma rikice-rikice sun taso, to kuna buƙatar warware waɗannan rikice-rikice kuma ku ga abin da ke faruwa. Ana iya samun cikakkun bayanai a cikin rajistan ayyukan. Kuma kawar da yanayin rikici domin buƙatun aikace-aikacen suyi aiki ba tare da kurakurai ba.
Makullin ma mummunan yanayi ne. Lokacin da buƙatun ke yaƙi don albarkatu, buƙatun ɗaya ya isa ga albarkatu ɗaya kuma ya ɗauki kulle, buƙatu na biyu ya sami damar amfani da albarkatu na biyu kuma ya ɗauki kulle, sannan buƙatun biyu sun isa albarkatun juna kuma an toshe su yayin jiran maƙwabcin ya saki makullin. Wannan kuma lamari ne mai matsala. Suna buƙatar a magance su a matakin sake rubuta aikace-aikacen da jera damar samun albarkatu. Kuma idan kun ga kullun ku na karuwa kullum, kuna buƙatar duba cikakkun bayanai a cikin rajistan ayyukan, bincika yanayin da suka taso kuma ku ga menene matsalar.
Fayilolin wucin gadi (temp_files) suma mara kyau ne. Lokacin da buƙatar mai amfani ba ta da isasshen ƙwaƙwalwar ajiya don ɗaukar aiki, bayanan wucin gadi, yana ƙirƙirar fayil akan faifai. Kuma duk ayyukan da zai iya yi a cikin buffer na wucin gadi a ƙwaƙwalwar ajiya sun fara aiwatar da su akan faifan. A hankali. Wannan yana ƙara lokacin aiwatar da tambaya. Kuma abokin ciniki wanda ya aika buƙatun zuwa PostgreSQL zai sami amsa kaɗan daga baya. Idan duk waɗannan ayyukan an yi su a cikin ƙwaƙwalwar ajiya, Postgres zai amsa da sauri kuma abokin ciniki zai jira ƙasa.
Pg_stat_bgwriter - Wannan ra'ayi yana kwatanta aikin tsarin bayanan baya na PostgreSQL guda biyu: wannan. checkpointer и background writer.
Na farko, bari mu dubi wuraren sarrafawa, abin da ake kira. checkpoints. Menene wuraren sarrafawa? Wurin bincike matsayi ne a cikin ma'amalar ma'amala da ke nuna cewa duk canje-canjen bayanan da aka rubuta a cikin log ɗin an yi nasarar aiki tare da bayanan da ke kan faifai. Tsarin, ya danganta da nauyin aiki da saituna, na iya yin tsayi kuma galibi ya ƙunshi aiki tare da ƙazantattun shafuka a cikin buffers ɗin da aka raba tare da fayilolin bayanai akan faifai. Menene don me? Idan PostgreSQL ya kasance koyaushe yana shiga faifai yana karɓar bayanai daga can, da rubuta bayanai akan kowane damar, zai kasance a hankali. Saboda haka, PostgreSQL yana da ɓangaren ƙwaƙwalwar ajiya wanda girmansa ya dogara da saitunan da ke cikin saitin. Postgres yana adana bayanan kai tsaye a cikin wannan ƙwaƙwalwar don aiki ko tambaya daga baya. Idan akwai buƙatun canza bayanan, an canza shi. Kuma muna samun nau'ikan bayanan biyu. Ɗaya yana cikin ƙwaƙwalwar ajiyarmu, ɗayan yana kan faifai. Kuma lokaci-lokaci kuna buƙatar daidaita wannan bayanan. Muna buƙatar daidaita abin da aka canza a ƙwaƙwalwar ajiya zuwa faifai. Don wannan kuna buƙatar wuraren bincike.
Matsakaicin abin dubawa yana shiga ta cikin maɓallan da aka raba, suna nuna ƙazantattun shafuka waɗanda ake buƙatar su don wurin bincike. Sannan yana ƙaddamar da wucewa ta biyu ta cikin maɓallan da aka raba. Kuma shafukan da aka yiwa alamar bincike, ya riga ya daidaita su. Ta wannan hanyar bayanan suna aiki tare da faifai.
Akwai wuraren bincike iri biyu. Ana aiwatar da wurin bincike ɗaya tare da ƙarewar lokaci. Wannan wurin bincike yana da amfani kuma yana da kyau - checkpoint_timed. Kuma akwai wuraren bincike akan buƙata - checkpoint required. Wannan wurin binciken yana faruwa ne lokacin da muke da babban rikodin bayanai. Mun rubuta rajistan ayyukan ma'amala da yawa. Kuma PostgreSQL ya yi imanin cewa yana buƙatar daidaita duk wannan da sauri da sauri, sanya wurin bincike kuma ci gaba.
Kuma idan ka duba kididdiga pg_stat_bgwriter kuma ga abin da kuke da shi checkpoint_req ya fi girma fiye da checkpoint_timed, to wannan ba shi da kyau. Me yasa mara kyau? Wannan yana nufin cewa PostgreSQL yana cikin damuwa akai-akai lokacin da yake buƙatar rubuta bayanai zuwa faifai. Wurin bincikar lokaci ba shi da ɗan damuwa kuma ana yin shi bisa ga jadawalin ciki kuma ana yada shi akan lokaci. PostgreSQL yana da ikon dakatar da aiki kuma kada ya lalata tsarin faifai. Wannan yana da amfani ga PostgreSQL. Kuma tambayoyin da ake aiwatarwa a lokacin bincike ba za su fuskanci damuwa ba saboda gaskiyar cewa tsarin faifan yana aiki.
Kuma don daidaita wurin bincike akwai sigogi guda uku:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Suna ba ka damar tsara aikin wuraren sarrafawa. Amma ba zan zauna a kansu ba. Tasirinsu wani batu ne daban.
Gargadi: Sigar 9.4 da aka tattauna a cikin rahoton ba ta da dacewa. A cikin sigar zamani na PostgreSQL ma'aunin checkpoint_segments maye gurbinsu da sigogi min_wal_size и max_wal_size.
Tsarin ƙasa na gaba shine marubucin bango - background writer. Me yake yi? Yana gudana akai-akai a cikin madauki mara iyaka. Yana bincika shafuka a cikin maɓallan da aka raba kuma yana zubar da ƙazantattun shafuka waɗanda ya samo su zuwa faifai. Don haka, yana taimakawa ma'aunin bincike don yin ƙarancin aiki yayin aiwatar da wuraren binciken.
Me kuma ake bukata? Yana ba da buƙatar buƙatun da ba komai a cikin maɓallan da aka raba idan ana buƙatar su ba zato ba tsammani (a cikin adadi mai yawa kuma nan da nan) don ɗaukar bayanai. A ce wani yanayi ya taso lokacin da ake buƙatar shafukan da ba su da tushe don kammala buƙatu kuma sun riga sun kasance a cikin buffers ɗin da aka raba. Postgresive backend kawai ya dauke su ya yi amfani da su, ba sai ya wanke komai da kansa ba. Amma idan babu irin waɗannan shafuka ba zato ba tsammani, mai ba da baya ya dakatar da aiki kuma ya fara neman shafuka don zubar da su a kan faifai da ɗaukar su don bukatun kansa - wanda ke yin mummunan tasiri ga lokacin aiwatar da buƙatar a halin yanzu. Idan ka ga kana da siga maxwritten_clean babba, wannan yana nufin cewa marubucin baya baya yin aikinsa kuma kana buƙatar ƙara sigogi bgwriter_lru_maxpages, don ya iya yin ƙarin aiki a cikin zagaye ɗaya, share ƙarin shafuka.
Kuma wata alama mai matukar amfani ita ce buffers_backend_fsync. Backends ba sa fsync saboda yana da hankali. Suna wuce fsync sama da ma'aunin bincike na IO. Mai duba yana da nasa jerin gwano, lokaci-lokaci yana aiwatar da fsync kuma yana daidaita shafuka a cikin ƙwaƙwalwar ajiya tare da fayiloli akan faifai. Idan jerin gwano a wurin dubawa yana da girma kuma ya cika, to, an tilasta wa baya don yin fsync da kansa kuma wannan yana rage aikin na baya., watau abokin ciniki zai sami amsa daga baya fiye da yadda zai iya. Idan kun ga cewa darajar ku ta fi sifili, to wannan ya riga ya zama matsala kuma kuna buƙatar kula da saitunan marubucin baya kuma ku kimanta aikin tsarin faifai.
Gargadi: _Rubutu mai zuwa yana bayyana ra'ayoyin ƙididdiga masu alaƙa da maimaitawa. Yawancin ra'ayi da sunayen ayyuka an sake sanya su a cikin Postgres 10. Ma'anar sake suna shine maye gurbin. xlog a kan wal и location a kan lsn a cikin aiki/duba sunaye, da sauransu. Misali na musamman, aiki pg_xlog_location_diff() aka sake masa suna pg_wal_lsn_diff()._
Muna da abubuwa da yawa a nan kuma. Amma muna buƙatar abubuwa masu alaƙa da wuri kawai.
Idan muka ga cewa duk dabi'u daidai suke, to wannan zaɓi ne mai kyau kuma kwafin ba ya jinkirin maigidan.
Wannan matsayi na hexadecimal anan shine matsayi a cikin log ɗin ciniki. Yana ƙaruwa koyaushe idan akwai wani aiki a cikin bayanan: sakawa, sharewa, da sauransu.
сколько записано 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());Idan waɗannan abubuwan sun bambanta, to akwai wani nau'i na lag. Lag shine lag tsakanin kwafi da maigida, watau bayanan sun bambanta tsakanin uwar garken.
Akwai dalilai guda uku da ke haifar da koma baya:
- Wannan tsarin faifai ba zai iya jure aiki tare da rikodin rikodi ba.
- Waɗannan kurakurai ne masu yuwuwa na hanyar sadarwa, ko yin kifin cibiyar sadarwa, lokacin da bayanan ba su da lokacin isa ga kwafi kuma ba za ta iya sake yin su ba.
- Da kuma processor. Mai sarrafa masarrafa lamari ne da ba kasafai ba. Kuma na ga wannan sau biyu ko uku, amma wannan kuma yana iya faruwa.
Kuma a nan akwai tambayoyi guda uku waɗanda ke ba mu damar amfani da ƙididdiga. Za mu iya ƙididdige adadin da muka yi rikodin a cikin ma'amala. Akwai irin wannan aikin pg_xlog_location_diff kuma za mu iya ƙididdige koma baya a cikin bytes da daƙiƙa. Hakanan muna amfani da ƙimar daga wannan ra'ayi (VIEWs) don wannan.
Note: _Maimakon pg_xlog_locationAyyukan diff() na iya amfani da afaretan ragi kuma cire wuri ɗaya daga wani. Dadi.
Akwai maki ɗaya tare da lag, wanda ke cikin daƙiƙa. Idan babu wani aiki a kan maigidan, cinikin ya kasance a wurin kusan mintuna 15 da suka gabata kuma babu wani aiki, kuma idan muka kalli wannan lag akan kwafin, za mu ga jinkirin mintuna 15. Wannan ya cancanci tunawa. Kuma wannan na iya zama ruɗani lokacin da kuke kallon wannan lagon.
Pg_stat_all_tables wani kallo ne mai amfani. Yana nuna ƙididdiga akan tebur. Lokacin da muke da tebur a cikin bayanan, akwai wasu ayyuka tare da shi, wasu ayyuka, za mu iya samun wannan bayanin daga wannan ra'ayi.
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;Abu na farko da za mu iya dubawa shine jerin sikanin da aka yi a kan tebur. Lambar da kanta bayan waɗannan wucewar ba lallai ba ne mummuna kuma ba alama ce cewa muna buƙatar yin wani abu ba.
Koyaya, akwai awo na biyu - seq_tup_read. Wannan shine adadin layuka da aka dawo daga sikanin jeri. Idan matsakaicin adadin ya wuce 1, 000, 10, 000, to wannan ya riga ya zama mai nuna alama cewa watakila kana buƙatar gina fihirisa a wani wuri domin tambayoyin sun dogara ne akan maƙasudin, ko kuma yana yiwuwa a inganta tambayoyin da suke amfani da irin wannan binciken na jere don haka. cewa hakan bai faru ba.
Misali mai sauƙi - bari mu faɗi buƙatu tare da babban OFFSET da IYAKA farashin. Misali, ana duba layuka 100 da ke cikin tebur sannan a dauki layuka 000 da ake bukata, sannan a jefar da layuka na baya. Wannan kuma mummunan lamari ne. Kuma irin waɗannan tambayoyin suna buƙatar inganta su. Kuma ga tambaya mai sauƙi ta SQL inda zaku iya duba wannan kuma ku kimanta sakamakon lambobi.
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;Hakanan za'a iya samun girman tebur ta amfani da wannan tebur da amfani da ƙarin ayyuka pg_total_relation_size(), pg_relation_size().
Gabaɗaya, akwai metacommands dt и di, wanda za'a iya amfani dashi a cikin PSQL kuma yana duba girman tebur da fihirisa.
Duk da haka, yin amfani da ayyuka yana taimaka mana mu dubi girman tebur, har ma da yin la'akari da ma'auni, ko ba tare da la'akari da ma'auni ba, kuma mun riga mun yi wasu ƙididdiga dangane da haɓakar bayanai, watau yadda yake girma, da wane ƙarfi, da kuma yadda yake girma. zana wasu yanke shawara game da inganta girman girman.
Ayyukan rikodi. Menene rikodi? Bari mu dubi aikin UPDATE - aikin sabunta layuka a cikin tebur. A zahiri, sabuntawa shine ayyuka biyu (ko ma fiye). Wannan yana shigar da sabon juzu'in layin da kuma yiwa tsohon sigar jeri alama mara amfani. Daga baya, autovacuum zai zo ya share waɗannan tsoffin juzu'an layukan, yana yiwa wannan wuri alama don sake amfani da su.
Bugu da kari, sabuntawa ba kawai game da sabunta tebur ba ne. Wannan kuma sabuntawar fihirisa ne. Idan kuna da fihirisa da yawa akan tebur, to yayin ɗaukaka duk fihirisar da suka haɗa da filayen da aka sabunta a cikin tambaya kuma za a buƙaci a sabunta su. Waɗannan firikwensin kuma za su sami tsayayyen juzu'in layuka waɗanda za su buƙaci a tsaftace su.
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;Kuma saboda sabon ƙirar sa, UPDATE aiki ne mai nauyi. Amma ana iya sauƙaƙe su. Ku ci hot updates. Sun bayyana a cikin PostgreSQL version 8.3. Kuma menene wannan? Wannan sabuntawa ne mara nauyi wanda baya sa a sake gina fihirisa. Wato, mun sabunta rikodin, amma kawai rikodin a cikin shafin (wanda ke cikin tebur) an sabunta shi, kuma alamun har yanzu suna nuna rikodin iri ɗaya a cikin shafin. Akwai ɗan dabaru na aiki mai ban sha'awa: lokacin da injin ya zo, yana ƙirƙirar waɗannan sarƙoƙi hot sake ginawa kuma komai yana ci gaba da aiki ba tare da sabunta ma'auni ba, kuma duk abin da ke faruwa tare da ƙarancin ɓata albarkatu.
Kuma yaushe kuke n_tup_hot_upd babba, to yana da kyau sosai. Wannan yana nufin cewa sabuntawa masu nauyi sun mamaye kuma wannan yana da arha a gare mu dangane da albarkatu kuma komai yana da kyau.
ALTER TABLE table_name SET (fillfactor = 70);Yadda ake ƙara girma hot updateov? Za mu iya amfani fillfactor. Yana ƙayyade girman da aka tanada kyauta lokacin da ake cika shafi a cikin tebur ta amfani da INSERTs. Lokacin da aka ƙara abubuwan da aka saka a tebur, suna cika shafin gaba ɗaya kuma ba su bar komai ba. Sannan ana haskaka sabon shafi. An sake cika bayanan. Kuma wannan shine tsohuwar hali, fillfactor = 100%.
Za mu iya yin fillfactor 70%. Wato, yayin sakawa, an haskaka sabon shafi, amma kashi 70% kawai na shafin ya cika. Kuma muna da kashi 30% a matsayin tanadi. Lokacin da kuke buƙatar yin sabuntawa, zai fi yiwuwa ya faru akan shafi ɗaya, kuma sabon sigar layin zai dace akan shafi ɗaya. Kuma hot_update za a yi. Wannan yana ba da sauƙin rubutu akan tebur.
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));Motsa linzamin kwamfuta. Autovacuum tsarin ƙasa ne wanda ke da ƙididdiga kaɗan a cikin PostgreSQL. Zamu iya gani kawai a cikin allunan a cikin pg_stat_activity nawa vacuum da muke da shi a yanzu. Koyaya, yana da matukar wahala a fahimci tebur nawa ke cikin jerin gwano nan da nan.
Note: An fara da Postgres 10, halin da ake ciki tare da bin diddigin Vatovac ya inganta sosai - ra'ayin pg_stat_progress ya bayyana.vacuum, wanda ke sauƙaƙa mahimmancin batun sa ido kan injin motar.
Za mu iya amfani da wannan sauƙaƙan tambayar. Kuma za mu iya ganin lokacin da za a yi vacuum. Amma ta yaya kuma yaushe ya kamata a fara injin? Waɗannan su ne sigogin gado na layukan da na yi magana akai a baya. Sabuntawa ya faru, an saka sabon sigar layin. Wani tsohon sigar kirtani ya bayyana. A cikin tebur pg_stat_user_tables akwai irin wannan siga n_dead_tup. Yana nuna adadin layukan "matattu". Kuma da zaran adadin layuka da suka mutu ya zama mafi girma fiye da wani kofa, autovacuum zai zo kan tebur.
Kuma ta yaya ake lissafin wannan bakin kofa? Wannan ƙayyadaddun kaso ne na jimlar adadin layuka a cikin tebur. Akwai siga autovacuum_vacuum_scale_factor. Yana ƙayyade kashi. Bari mu ce 10% + akwai ƙarin ainihin madaidaicin layi na 50. Kuma me ya faru? Lokacin da muke da layuka da yawa da suka mutu fiye da "10% + 50" na duk layuka a cikin tebur, sannan mu sanya tebur akan 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));Duk da haka, akwai batu guda. Matsakaicin tushe don sigogi av_base_thresh и av_scale_factor za a iya sanyawa akayi daban-daban. Kuma, bisa ga haka, kofa ba zai zama duniya ba, amma mutum don tebur. Don haka, don yin lissafi, kuna buƙatar amfani da dabaru da dabaru. Kuma idan kuna sha'awar, to, zaku iya duba kwarewar abokan aikinmu daga Avito (haɗin da ke kan nunin ba shi da inganci kuma an sabunta shi a cikin rubutu).
Sun rubuta don , wanda ke la'akari da waɗannan abubuwa. Akwai rigar ƙafar zani biyu a wurin. Amma yana ƙididdigewa daidai kuma yadda ya kamata ya ba mu damar tantance inda muke buƙatar ƙarancin sarari don tebur inda akwai kaɗan.
Me za mu iya yi game da shi? Idan muna da babban layi kuma autovacuum ba zai iya jurewa ba, to zamu iya ƙara yawan ma'aikatan injin, ko kuma kawai sanya injin ya zama mai ƙarfi., don haka yana haifar da farko, aiwatar da tebur a cikin ƙananan guda. Kuma ta haka layin zai ragu. - Babban abu a nan shi ne kula da nauyin da ke kan faifai, saboda ... vacuum ba abu ne na kyauta ba, ko da yake tare da zuwan na'urorin SSD/NVMe matsalar ta zama ƙasa da sananne.
Pg_stat_all_indexes kididdiga ce akan fihirisa. Ita ba babba bace. Kuma za mu iya amfani da shi don samun bayanai game da amfani da fihirisa. Kuma alal misali, za mu iya ƙayyade waɗanne fihirisa muke da ƙari.
Kamar yadda na fada a baya. sabuntawa ba kawai sabuntawar tebur ba ne, har ila yau sabuntawa ne na fihirisa. Saboda haka, idan muna da fihirisa da yawa a kan tebur, to, lokacin da ake sabunta layuka a cikin tebur, ana buƙatar sabunta filaye na filayen da aka ƙididdige su, kuma idan muna da fihirisar da ba a yi amfani da su ba waɗanda babu sikanin fihirisa, to sun rataye a matsayin ballast. Kuma muna bukatar mu kawar da su. Don wannan muna buƙatar filin idx_scan. Muna kawai duba yawan adadin sikanin fihirisa. Idan fihirisa ba su da sifili sikanin a kan in mun gwada da dogon lokaci na statistics ajiya (akalla 2-3 makonni), to, mafi m wadannan su ne mummuna fihirisa, muna bukatar mu rabu da su.
Note: Lokacin nemo fihirisar da ba a yi amfani da su ba a cikin yanayin gungu na kwafi, kuna buƙatar bincika duk nodes ɗin gungu, saboda statistics ba na duniya ba ne, kuma idan ba a yi amfani da index a kan maigidan ba, to ana iya amfani da shi akan kwafi (idan akwai kaya a can).
Hanyoyi guda biyu:
Waɗannan su ne ƙarin ƙarin misalan tambaya kan yadda ake duba fihirisa da ba a yi amfani da su ba.
Hanya ta biyu buƙatu ce mai ban sha'awa. Akwai dabaru maras muhimmanci a wurin. Ina ba da shawarar shi don tunani.
Menene kuma darajar taƙaitawa ta amfani da fihirisa?
-
Fihirisar da ba a amfani da su ba su da kyau.
-
Suna ɗaukar sarari.
-
Rage ayyukan sabuntawa.
-
Ƙarin aiki don vacuum.
Idan muka cire firikwensin da ba a yi amfani da su ba, za mu inganta bayanan bayanai ne kawai.
Gabatarwa ta gaba ita ce pg_stat_activity. Wannan shi ne analogue na mai amfani ps, kawai a cikin PostgreSQL. Idan ps"Om ku duba hanyoyin da ke cikin tsarin aiki, to pg_stat_activity Zai nuna muku ayyukan da ke cikin PostgreSQL.
Wadanne abubuwa masu amfani za mu iya dauka daga can?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Za mu iya ganin aikin gabaɗaya, abin da ke faruwa a cikin bayanan. Za mu iya yin sabon turawa. Duk abin da ke nan ya fashe, ba a karɓi sabbin hanyoyin sadarwa ba, kurakurai suna ta kwarara cikin aikace-aikacen.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Za mu iya gudanar da tambaya kamar wannan kuma mu ga jimillar adadin haɗin kai dangane da matsakaicin iyakar haɗin yanar gizo kuma mu ga wanda ya fi haɗin kai. Kuma a cikin wannan yanayin muna ganin mai amfani cron_role bude 508 haɗi. Kuma wani abu ya same shi a can. Muna bukatar mu magance shi kuma mu duba. Kuma yana yiwuwa wannan wani nau'in nau'in nau'in haɗin kai ne.
Idan muna da nauyin aikin OLTP, tambayoyin yakamata su kasance cikin sauri, da sauri sosai kuma bai kamata a sami dogon tambayoyi ba. Duk da haka, idan dogon tambayoyi sun taso, to a cikin gajeren lokaci babu wani abin damuwa, amma A cikin dogon lokaci, dogayen tambayoyin suna cutar da ma'ajin bayanai; suna haɓaka tasirin tebur lokacin da rarrabuwar tebur ta faru. Kuna buƙatar kawar da duka kumbura da dogon tambayoyi.
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;Lura: tare da wannan buƙatar za mu iya gano dogayen tambayoyi da ma'amaloli. Muna amfani da aikin clock_timestamp() don ƙayyade lokacin aiki. Dogayen tambayoyin da muka samo, za mu iya tunawa da su, mu cika su explain, duba tsare-tsaren kuma ko ta yaya inganta. Muna harba dogon buƙatun na yanzu kuma muna ci gaba da rayuwarmu.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Mummunan ma'amaloli su ne ma'amaloli a cikin marasa aiki a cikin ma'amala da kuma marasa aiki a cikin ma'amala (wacce aka soke) jihohi.
Me ake nufi? Ma'amaloli suna da jihohi da yawa. Kuma ana iya ɗauka ɗaya daga cikin waɗannan jihohin a kowane lokaci. Akwai filin da za a ayyana jihohi state a cikin wannan gabatarwa. Kuma muna amfani da shi don sanin jihar.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Kuma, kamar yadda na fada a sama, wadannan jihohi biyu marasa aiki a cikin ma'amala da rago a cikin ciniki (wanda aka zubar) ba su da kyau. Menene shi? Wannan shine lokacin da aikace-aikacen ya buɗe ciniki, ya yi wasu ayyuka kuma ya ci gaba da kasuwancinsa. Kasuwancin ya kasance a buɗe. Yana rataye, babu abin da ke faruwa a cikinsa, yana ɗaukar haɗin gwiwa, yana kulle a kan layukan da aka canza kuma yana iya ƙara kumburin sauran tebur, saboda gine-ginen injin ma'amala na Postrges. Kuma irin wannan ciniki ya kamata a yi watsi da su, saboda gabaɗaya suna da illa, a kowane hali.
Idan kun ga cewa kuna da fiye da 5-10-20 daga cikinsu a cikin bayananku, to kuna buƙatar damuwa kuma ku fara yin wani abu tare da su.
Anan kuma muna amfani da lokacin lissafin clock_timestamp(). Muna harba ma'amaloli kuma muna haɓaka aikace-aikacen.
Kamar yadda na fada a sama, toshewa shine lokacin da ma'amaloli biyu ko fiye suka yi yaƙi don ɗaya ko rukuni na albarkatu. Don wannan muna da filin waiting tare da darajar boolean true ko false.
Gaskiya - wannan yana nufin cewa tsari yana jiran, wani abu yana buƙatar yin aiki. Lokacin da tsari ke jira, yana nufin cewa abokin ciniki wanda ya fara wannan tsari shima yana jira. Abokin ciniki yana zaune a cikin mai binciken kuma yana jira.
Gargadi: _Farawa daga filin Postgres 9.6 waiting cire kuma an ƙara ƙarin filayen bayanai guda biyu maimakon wait_event_type и wait_event._
Abin da ya yi? Idan kun ga gaskiya na dogon lokaci, yana nufin kuna buƙatar kawar da irin waɗannan buƙatun. Mu kawai harba saukar da irin wannan ma'amaloli. Muna rubuta wa masu haɓakawa cewa suna buƙatar haɓaka ko ta yaya don kada a sami tseren albarkatu. Sannan masu haɓakawa sun inganta aikace-aikacen don kada hakan ya faru.
Kuma matsananci, amma mai yuwuwar shari'ar da ba ta mutu ba ita ce faruwar matattu. Ma'amaloli biyu sun sabunta albarkatun guda biyu, sannan an sake samun dama gare su, wannan lokacin zuwa wasu albarkatu. A wannan yanayin, PostgreSQL yana kashe ma'amala da kanta don wani ya ci gaba da aiki. Wannan mummunan yanayi ne kuma ba za ta iya gane shi da kanta ba. Saboda haka, an tilasta PostgreSQL don ɗaukar tsauraran matakai.
Kuma a nan akwai tambayoyi guda biyu waɗanda ke ba ku damar bin hanyar toshewa. Muna amfani da view pg_locks, wanda ke ba ku damar bin diddigin makullai masu nauyi.
Kuma mahaɗin farko shine rubutun buƙatar da kansa. Yana da tsayi sosai.
Kuma mahaɗin na biyu shine labarin akan makullai. Yana da amfani don karantawa, yana da ban sha'awa sosai.
To me muke gani? Muna ganin buƙatun guda biyu. Ma'amala tare da ALTER TABLE ciniki ne mai toshewa. Ya fara, amma bai cika ba, kuma aikace-aikacen da ya rubuta wannan ma'amala yana yin wasu abubuwa a wani wuri. Kuma buƙatun na biyu shine sabuntawa. Yana jiran table din ya kare kafin ya cigaba da aikinsa.
Ta haka ne za mu iya gano wanda ya kulle wa, ya rike wa, kuma za mu iya magance shi.
Module na gaba shine pg_stat_statements. Kamar yadda na ce, wannan module ne. Don amfani da shi, kuna buƙatar loda ɗakin karatu a cikin tsarin, sake kunna PostgreSQL, shigar da tsarin (tare da umarni ɗaya) sannan za mu sami sabon ra'ayi.
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;Me za mu iya dauka daga can? Idan muka yi magana game da abubuwa masu sauƙi, za mu iya ɗaukar matsakaicin lokacin aiwatar da tambaya. Lokaci yana girma, wanda ke nufin cewa PostgreSQL yana amsawa a hankali kuma muna buƙatar yin wani abu.
Za mu iya duba mafi yawan ma'amalar rubuce-rubuce a cikin ma'ajin bayanai waɗanda ke canza bayanai a cikin ma'ajin da aka raba. Duba wanda ke ɗaukaka ko share bayanai a wurin.
Kuma za mu iya kawai duba ƙididdiga daban-daban na waɗannan buƙatun.
Mu pg_stat_statements Muna amfani da shi don gina rahotanni. Muna sake saita kididdigar sau ɗaya a rana. Mu tara shi. Kafin sake saita kididdigar lokaci na gaba, bari mu gina rahoto. Ga hanyar haɗi zuwa rahoton. Kuna iya kallon shi.
Me muke yi? Muna ƙididdige ƙididdiga na gaba ɗaya don duk buƙatun. Bayan haka, ga kowane buƙatu, muna ƙidaya gudummawar mutum ɗaya ga waɗannan ƙididdiga gabaɗayan.
Kuma me za mu iya kallo? Za mu iya duba jimlar lokacin aiwatar da duk buƙatun wani nau'i na musamman dangane da duk sauran buƙatun. Zamu iya duba amfani da albarkatun CPU da I/O dangane da cikakken hoto. Kuma riga inganta waɗannan tambayoyin. Muna gina manyan tambayoyin dangane da wannan rahoto kuma muna samun abinci don tunani akan abin da zamu inganta.
Me muka bari a bayan fage? Har yanzu akwai sauran ‘yan gabatarwa da ban yi la’akari da su ba saboda lokaci yana da iyaka.
Akwai pgstattuple Hakanan ƙarin samfuri ne daga daidaitattun fakitin ba da gudummawa. Yana ba ku damar kimantawa bloat Tables, abin da ake kira tebur gutsuttsura. Kuma idan akwai rarrabuwa da yawa, kuna buƙatar cire shi kuma kuyi amfani da kayan aiki daban-daban. Kuma aiki pgstattuple yana aiki na dogon lokaci. Kuma yawancin teburin akwai, tsayin daka zai yi aiki.
Gudunmawar ta gaba ita ce pg_buffercache. Yana ba ku damar bincika buffers ɗin da aka raba: yadda sosai da kuma waɗanne shafukan buffer tebur ake amfani da su. Kuma kawai yana ba ku damar bincika abubuwan buffer da aka raba kuma ku kimanta abin da ke faruwa a wurin.
Module na gaba shine pgfincore. Yana ba da damar ayyukan tebur ƙananan matakan ta hanyar kiran tsarin mincore(), watau yana ba ka damar ɗora tebur a cikin abubuwan da aka raba, ko sauke shi. Kuma yana ba da damar, a tsakanin sauran abubuwa, don bincika cache na tsarin aiki, watau, yawan sarari da tebur ke cikin cache na shafi, a cikin buffers masu raba, kuma kawai yana ba mu damar kimanta aikin tebur.
Module na gaba - pg_stat_kcache. Hakanan yana amfani da tsarin kira getrusage(). Kuma yana aiwatar da shi kafin da kuma bayan an zartar da bukatar. Kuma a cikin kididdigar da aka samu, yana ba mu damar kimanta nawa buƙatarmu ta kashe akan faifai I / O, watau, aiki tare da tsarin fayil kuma yana duban amfanin mai sarrafawa. Koyaya, tsarin yana matashi (tari tari) kuma don aikinsa yana buƙatar PostgreSQL 9.4 da pg_stat_statements, waɗanda na ambata a baya.
-
Sanin yadda ake amfani da kididdiga yana da amfani. Ba kwa buƙatar shirye-shiryen ɓangare na uku. Kuna iya shiga, gani, yin wani abu, cim ma wani abu.
-
Amfani da ƙididdiga ba shi da wahala, SQL ne na yau da kullun. Kun tattara buqatar, kun tattara ta, ku aika, ku duba.
-
Ƙididdiga na taimakawa amsa tambayoyi. Idan kuna da tambayoyi, kun juya zuwa ƙididdiga - duba, zana ƙarshe, bincika sakamakon.
-
Kuma gwaji. Akwai buƙatu da yawa, bayanai da yawa. Kuna iya haɓaka tambayar da ke akwai koyaushe. Kuna iya yin nau'in buƙatun ku wanda ya dace da ku fiye da na asali kuma kuyi amfani da shi.
nassoshi
Hanyoyin haɗi masu dacewa waɗanda aka samo a cikin labarin, bisa ga kayan aiki, sun kasance a cikin rahoton.
Mawallafi rubuta ƙarin
( Eng)
Mai Tarin Ƙididdiga
Ayyukan Gudanar da Tsarin
Abubuwan ba da gudummawa
SQL utils da sql code misalai
Na gode duka saboda kulawar ku!
source: www.habr.com