🥇PostgreSQL ichki statistikasiga chuqur kirib boring. Aleksey Lesovskiy

Aleksey Lesovskiyning 2015 yildagi "PostgreSQL ichki statistikasiga chuqur kirib borish" hisobotining transkripti

Hisobot muallifidan voz kechish: Shuni ta'kidlaymanki, bu hisobot 2015 yil noyabr oyida - 4 yildan ortiq vaqt o'tdi va ko'p vaqt o'tdi. Hisobotda muhokama qilingan 9.4 versiyasi endi qo'llab-quvvatlanmaydi. So'nggi 4 yil ichida 5 ta yangi nashr chiqdi, ularda statistikaga oid ko'plab yangiliklar, yaxshilanishlar va o'zgarishlar mavjud va ba'zi materiallar eskirgan va ahamiyatsiz. Ko‘zdan kechirar ekanman, o‘quvchini chalg‘itmaslik uchun shu joylarni belgilashga harakat qildim. Men bu parchalarni qayta yozmadim, ular juda ko'p va natijada butunlay boshqacha hisobot bo'ladi.

PostgreSQL ma'lumotlar bazasi juda katta mexanizm bo'lib, bu mexanizm ko'plab quyi tizimlardan iborat bo'lib, ularning muvofiqlashtirilgan ishlashi DBMS ishlashiga bevosita ta'sir qiladi. Ishlash jarayonida statistik ma'lumotlar va komponentlarning ishlashi to'g'risidagi ma'lumotlar to'planadi, bu PostgreSQL samaradorligini baholash va ish faoliyatini yaxshilash choralarini ko'rish imkonini beradi. Biroq, bu ma'lumotlar juda ko'p va ular juda soddalashtirilgan shaklda taqdim etilgan. Ushbu ma'lumotni qayta ishlash va uni sharhlash ba'zan mutlaqo ahamiyatsiz ish bo'lib, asboblar va yordamchi dasturlarning "hayvonot bog'i" hatto ilg'or DBA-ni osongina chalkashtirib yuborishi mumkin.

Hayrli kun! Mening ismim Aleksey. Ilya aytganidek, men PostgreSQL statistikasi haqida gapiraman.

PostgreSQL faoliyati statistikasi. PostgreSQL ikkita statistikaga ega. Muhokama qilinadigan faoliyat statistikasi. Va ma'lumotlarni taqsimlash bo'yicha rejalashtiruvchi statistikasi. Men PostgreSQL faoliyati statistikasi haqida alohida gaplashaman, bu bizga ishlashni baholash va qandaydir tarzda yaxshilash imkonini beradi.

Men sizga statistik ma'lumotlardan qanday qilib sizda mavjud bo'lgan yoki bo'lishi mumkin bo'lgan turli muammolarni hal qilish uchun samarali foydalanishni aytib beraman.

Hisobotda nima bo'lmaydi? Hisobotda men rejalashtiruvchi statistikasiga tegmayman, chunki... Bu ma'lumotlar bazasida ma'lumotlar qanday saqlanishi va so'rovni rejalashtiruvchi ushbu ma'lumotlarning sifat va miqdoriy xarakteristikalari haqida qanday tasavvurga ega ekanligi haqida alohida hisobot uchun alohida mavzu.

Va hech qanday vosita sharhlari bo'lmaydi, men bir mahsulotni boshqasiga solishtirmayman. Hech qanday reklama bo'lmaydi. Keling, buni bir chetga surib qo'yaylik.

Men sizga statistikadan foydalanish foydali ekanligini ko'rsatmoqchiman. Bu zarur. Foydalanish xavfsiz. Bizga kerak bo'lgan yagona narsa - oddiy SQL va SQL bo'yicha asosiy bilim.

Va keling, muammolarni hal qilish uchun qanday statistikani tanlash kerakligi haqida gapiraylik.

Agar PostgreSQL-ga qarasak va jarayonlarni ko'rish uchun operatsion tizimda buyruqni ishga tushirsak, biz "qora quti" ni ko'ramiz. Biz nimadir qilayotgan ba'zi jarayonlarni ko'ramiz va nomidan ular u erda nima qilayotganlarini, nima qilayotganlarini taxminan tasavvur qilishimiz mumkin. Ammo, aslida, bu qora quti, biz ichkariga qaray olmaymiz.

Biz CPU yukini ko'rishimiz mumkin top, biz ba'zi tizim yordam dasturlari tomonidan xotiradan foydalanishni ko'rib chiqishimiz mumkin, ammo PostgreSQL-ni ko'rib chiqa olmaymiz. Buning uchun bizga boshqa vositalar kerak.

Va davom ettirib, sizga vaqt qayerda o'tkazilishini aytaman. Agar PostgreSQL-ni shunday diagramma ko'rinishida tasavvur qilsak, unda vaqt qayerga sarflanganiga javob bera olamiz. Bu ikkita narsa: u ilovalardan kelgan mijoz so'rovlarini va PostgreSQL o'zini ishlashini ta'minlash uchun bajaradigan fon vazifalarini qayta ishlaydi.

Agar biz yuqori chap burchakka qarashni boshlasak, mijoz so'rovlari qanday qayta ishlanishini ko'rishimiz mumkin. So'rov ilovadan keladi va keyingi ish uchun mijoz sessiyasi ochiladi. So'rov rejalashtiruvchiga yuboriladi. Rejalashtiruvchi so'rovlar rejasini tuzadi. Uni bajarish uchun qo'shimcha yuboradi. Jadvallar va indekslar bilan bog'liq bo'lgan ma'lumotlarni kiritish/chiqarish blokining bir turi mavjud. Kerakli ma'lumotlar disklardan xotiraga maxsus "birgalikda buferlar" maydoniga o'qiladi. So'rov natijalari, agar ular yangilanishlar, o'chirishlar bo'lsa, WAL-dagi tranzaktsiyalar jurnalida qayd etiladi. Ba'zi statistik ma'lumotlar jurnal yoki statistik kollektorda tugaydi. Va so'rov natijasi mijozga qaytariladi. Shundan so'ng mijoz yangi so'rov bilan hamma narsani yana takrorlashi mumkin.

Fon vazifalari va fon jarayonlari haqida nima deyish mumkin? Bizda ma'lumotlar bazasini normal ish rejimida ushlab turadigan bir nechta jarayonlar mavjud. Hisobotda ushbu jarayonlarga ham to'xtalib o'tadi: avtovakuum, nazorat nuqtasi, replikatsiya bilan bog'liq jarayonlar, fon yozuvchisi. Ularning har biriga hisobot berish jarayonida to‘xtalib o‘taman.

Statistikada qanday muammolar mavjud?

Ko'p ma'lumot mavjud. PostgreSQL 9.4 statistik ma'lumotlarni ko'rish uchun 109 ko'rsatkichni taqdim etadi. Biroq, agar ma'lumotlar bazasida ko'plab jadvallar, sxemalar, ma'lumotlar bazalari saqlansa, bu ko'rsatkichlarning barchasi jadvallar, ma'lumotlar bazalari soniga ko'paytirilishi kerak. Ya'ni, bundan ham ko'proq ma'lumot mavjud. Va unda cho'kish juda oson.
Keyingi muammo shundaki, statistika hisoblagichlar bilan ifodalanadi. Agar biz ushbu statistik ma'lumotlarni ko'rib chiqsak, biz doimiy ravishda o'sib borayotgan hisoblagichlarni ko'ramiz. Va agar statistika qayta tiklanganidan beri ko'p vaqt o'tgan bo'lsa, biz milliardlab qiymatlarni ko'ramiz. Va ular bizga hech narsa demaydilar.
Hikoya yo'q. Agar sizda qandaydir muvaffaqiyatsizlikka uchragan bo'lsangiz, 15-30 daqiqa oldin biror narsa tushib ketgan bo'lsa, siz statistikadan foydalana olmaysiz va 15-30 daqiqa oldin nima bo'lganini ko'ra olmaysiz. Bu muammo.
PostgreSQL-ga o'rnatilgan vositaning etishmasligi muammodir. Yadro ishlab chiquvchilari hech qanday yordamchi dasturni taqdim etmaydi. Ularda bunday narsa yo'q. Ular shunchaki ma'lumotlar bazasida statistik ma'lumotlarni taqdim etadilar. Undan foydalaning, unga so'rov yuboring, xohlaganingizni qiling.
PostgreSQL-ga o'rnatilgan vosita yo'qligi sababli, bu boshqa muammoni keltirib chiqaradi. Ko'plab uchinchi tomon vositalari. Ko'proq yoki kamroq to'g'ridan-to'g'ri qo'llari bo'lgan har bir kompaniya o'z dasturini yozishga harakat qilmoqda. Natijada, jamiyatda statistika bilan ishlashda foydalanish mumkin bo'lgan juda ko'p vositalar mavjud. Va ba'zi vositalar ma'lum imkoniyatlarga ega, boshqa vositalar boshqa imkoniyatlarga ega emas yoki ba'zi yangi imkoniyatlar mavjud. Va shunday vaziyat yuzaga keladiki, siz bir-birining ustiga chiqadigan va turli funktsiyalarga ega bo'lgan ikki, uch yoki to'rtta vositadan foydalanishingiz kerak. Bu juda yoqimsiz.

Bundan nima kelib chiqadi? Dasturlarga bog'liq bo'lmaslik yoki qandaydir tarzda ushbu dasturlarni o'zingiz yaxshilamaslik uchun statistik ma'lumotlarni to'g'ridan-to'g'ri olish imkoniyatiga ega bo'lish juda muhim: o'z foydangizga erishish uchun ba'zi funktsiyalarni qo'shing.

Va sizga SQL bo'yicha asosiy bilim kerak. Statistik ma'lumotlardan ba'zi ma'lumotlarni olish uchun siz SQL so'rovlarini yaratishingiz kerak, ya'ni tanlash va qo'shilish qanday tuzilganligini bilishingiz kerak.

Statistika bizga bir nechta narsalarni aytib beradi. Ularni toifalarga bo'lish mumkin.

Birinchi toifa - ma'lumotlar bazasida sodir bo'lgan voqealar. Bu ma'lumotlar bazasida biron bir hodisa sodir bo'lganda: so'rov, jadvalga kirish, avtovakuum, majburiyatlar, keyin bularning barchasi voqealar. Ushbu hodisalarga mos keladigan hisoblagichlar oshiriladi. Va biz bu voqealarni kuzatishimiz mumkin.
Ikkinchi toifa - jadvallar va ma'lumotlar bazalari kabi ob'ektlarning xususiyatlari. Ularning xususiyatlari bor. Bu jadvallarning o'lchami. Biz jadvallarning o'sishini va indekslarning o'sishini kuzatishimiz mumkin. Biz dinamikada o'zgarishlarni ko'rishimiz mumkin.
Uchinchi toifa esa tadbirga sarflangan vaqtdir. So'rov - bu hodisa. Uning o'ziga xos davomiyligi o'lchovi mavjud. Bu yerda boshlandi, shu yerda tugadi. Biz buni kuzatishimiz mumkin. Diskdan blokni o'qish yoki uni yozish uchun ketadigan vaqt. Bunday ishlar ham nazorat qilinadi.

Statistik ma'lumotlarning manbalari quyidagicha ko'rsatilgan:

Umumiy xotirada (umumiy buferlar) statik ma'lumotlarni saqlash uchun segment mavjud, shuningdek, ma'lum hodisalar sodir bo'lganda yoki ma'lumotlar bazasining ishlashida ba'zi lahzalar paydo bo'lganda doimiy ravishda oshirib boriladigan hisoblagichlar mavjud.
Ushbu hisoblagichlarning barchasi foydalanuvchi uchun va hatto administrator uchun ham mavjud emas. Bu past darajadagi narsalar. Ularga kirish uchun PostgreSQL SQL funktsiyalari ko'rinishidagi interfeysni taqdim etadi. Biz ushbu funksiyalar yordamida tanlab tashlashlarni amalga oshirishimiz va qandaydir metrikani (yoki ko'rsatkichlar to'plamini) olishimiz mumkin.
Biroq, bu funksiyalardan foydalanish har doim ham qulay emas, shuning uchun funksiyalar ko'rinishlar (VIEWs) uchun asosdir. Bu ma'lum bir quyi tizim yoki ma'lumotlar bazasidagi ma'lum voqealar to'plami bo'yicha statistik ma'lumotlarni taqdim etadigan virtual jadvallar.
Ushbu o'rnatilgan ko'rinishlar (VIEWs) statistika bilan ishlash uchun asosiy foydalanuvchi interfeysi hisoblanadi. Ular sukut bo'yicha qo'shimcha sozlamalarsiz mavjud, siz ularni darhol ishlatishingiz, ko'rishingiz va ulardan ma'lumot olishingiz mumkin. Va keyin hissalar bor. Hissalar rasmiy hisoblanadi. Siz postgresql-contrib paketini (masalan, postgresql94-contrib) o'rnatishingiz, kerakli modulni konfiguratsiyaga yuklashingiz, u uchun parametrlarni belgilashingiz, PostgreSQL-ni qayta ishga tushirishingiz va undan foydalanishingiz mumkin. (Eslatma. Tarqatishga qarab, oxirgi versiyalarda hissa to'plami asosiy paketning bir qismidir).
Va norasmiy hissa bor. Ular standart PostgreSQL taqsimotiga kiritilmagan. Ular kompilyatsiya qilinishi yoki kutubxona sifatida o'rnatilishi kerak. Variantlar juda boshqacha bo'lishi mumkin, bu norasmiy hissani ishlab chiquvchisi nimani o'ylab topganiga qarab.

Ushbu slaydda PostgreSQL 9.4 da mavjud bo'lgan barcha ko'rinishlar va ba'zi funktsiyalar taqdim etiladi. Ko'rib turganimizdek, ularning ko'pi bor. Va agar siz birinchi marta duch kelsangiz, chalkashib ketish juda oson.

Biroq, agar biz oldingi rasmni olsak Как тратится время на PostgreSQL va ushbu ro'yxatga mos kelsa, biz ushbu rasmni olamiz. PostgreSQL ishlayotgan vaqtda tegishli statistikani olish uchun har bir ko‘rinishdan (VIEWs) yoki har bir funksiyadan u yoki bu maqsadda foydalanishimiz mumkin. Va biz allaqachon quyi tizimning ishlashi haqida ba'zi ma'lumotlarni olishimiz mumkin.

Biz ko'rib chiqadigan birinchi narsa pg_stat_database. Ko'rib turganimizdek, bu spektakl. Unda juda ko'p ma'lumotlar mavjud. Eng xilma-xil ma'lumotlar. Va bu bizning ma'lumotlar bazasida nima sodir bo'layotgani haqida juda foydali ma'lumot beradi.

U erdan qanday foydali narsalarni olishimiz mumkin? Eng oddiy narsalardan boshlaylik.

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

Biz ko'rib chiqishimiz mumkin bo'lgan birinchi narsa - keshga kirish foizi. Keshni urish tezligi foydali ko'rsatkichdir. Bu umumiy bufer keshidan qancha ma'lumotlar olinganligini va diskdan qancha o'qilganligini taxmin qilish imkonini beradi.

Bu aniq qanchalik ko'p kesh xitlar bo'lsa, shuncha yaxshi. Biz ushbu ko'rsatkichni foiz sifatida o'lchaymiz. Va, masalan, bu kesh xitlarining foizi 90% dan ortiq bo'lsa, bu yaxshi. Agar u 90% dan pastga tushsa, bu bizda ma'lumotlarning issiq boshini xotirada saqlash uchun etarli xotira yo'qligini anglatadi. Va bu ma'lumotlardan foydalanish uchun PostgreSQL diskka kirishga majbur bo'ladi va bu ma'lumotlar xotiradan o'qilganidan ko'ra sekinroq. Va siz xotirani ko'paytirish haqida o'ylashingiz kerak: yoki umumiy buferlarni ko'paytirish yoki apparat xotirasini (RAM) oshirish.

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;

Ushbu spektakldan yana nimani olishingiz mumkin? Ma'lumotlar bazasida yuzaga kelgan anomaliyalarni ko'rishingiz mumkin. Bu erda nima ko'rsatilgan? Majburiyatlar, orqaga qaytishlar, vaqtinchalik fayllarni yaratish, ularning o'lchami, qulflar va nizolar mavjud.

Biz bu so'rovdan foydalanishimiz mumkin. Bu SQL juda oddiy. Va biz bu ma'lumotni bu erda ko'rishimiz mumkin.

Va bu erda chegara qiymatlari. Biz majburiyatlar va qaytarishlar nisbatiga qaraymiz. Majburiyatlar - tranzaktsiyaning muvaffaqiyatli tasdiqlanishi. Orqaga qaytarish - bu orqaga qaytarish, ya'ni tranzaktsiya ba'zi ishni bajardi, ma'lumotlar bazasini zo'riqtirdi, biror narsani hisobladi, keyin esa xatolik yuz berdi va tranzaksiya natijalari bekor qilinadi. Anavi doimiy ravishda ortib borayotgan orqaga qaytishlar soni yomon. Va qandaydir tarzda ulardan qochishingiz va bu sodir bo'lmasligi uchun kodni tahrirlashingiz kerak.

Mojarolar replikatsiya bilan bog'liq. Va ular ham oldini olish kerak. Agar sizda replikatsiyada bajarilgan ba'zi so'rovlar bo'lsa va nizolar yuzaga kelsa, siz ushbu ziddiyatlarni tartibga solib, nima sodir bo'layotganini ko'rishingiz kerak. Tafsilotlarni jurnallarda topish mumkin. Ilova so'rovlari xatosiz ishlashi uchun ziddiyatli vaziyatlarni bartaraf qiling.

O'lik qulflar ham yomon holat. So'rovlar resurslar uchun kurashayotganda, bitta so'rov bitta resursga kirdi va qulfni oldi, ikkinchi so'rov ikkinchi resursga kirdi va qulfni ham oldi, so'ngra ikkala so'rov ham bir-birining resurslariga kirishdi va qo'shni qulfni bo'shatishini kutayotganda bloklanadi. Bu ham muammoli holat. Ular ilovalarni qayta yozish va resurslarga kirishni ketma-ketlashtirish darajasida hal qilinishi kerak. Va agar siz o'liklaringiz doimiy ravishda oshib borayotganini ko'rsangiz, jurnallardagi tafsilotlarni ko'rib chiqishingiz, yuzaga keladigan vaziyatlarni tahlil qilishingiz va muammoning nima ekanligini ko'rishingiz kerak.

Vaqtinchalik fayllar (temp_files) ham yomon. Agar foydalanuvchi so'rovi operativ, vaqtinchalik ma'lumotlarni joylashtirish uchun etarli xotiraga ega bo'lmasa, u diskda fayl yaratadi. Va xotiradagi vaqtinchalik buferda bajarishi mumkin bo'lgan barcha operatsiyalar diskda bajarila boshlaydi. Bu sekin. Bu so'rovni bajarish vaqtini oshiradi. PostgreSQL-ga so'rov yuborgan mijoz birozdan keyin javob oladi. Agar ushbu operatsiyalarning barchasi xotirada bajarilsa, Postgres tezroq javob beradi va mijoz kamroq kutadi.

Pg_stat_bgwriter - Bu ko'rinish ikkita PostgreSQL fon quyi tizimlarining ishlashini tavsiflaydi: bu checkpointer и background writer.

Birinchidan, nazorat nuqtalari deb ataladigan narsalarni ko'rib chiqaylik. checkpoints. Nazorat nuqtalari nima? Tekshirish punkti - bu jurnalda qayd etilgan barcha ma'lumotlar o'zgarishlari diskdagi ma'lumotlar bilan muvaffaqiyatli sinxronlashtirilganligini ko'rsatadigan tranzaktsiyalar jurnalidagi joy. Jarayon, ish yuki va sozlamalarga qarab, uzoq davom etishi mumkin va asosan umumiy buferlardagi iflos sahifalarni diskdagi ma'lumotlar fayllari bilan sinxronlashtirishdan iborat. Bu nima uchun? Agar PostgreSQL doimiy ravishda diskka kirsa va u yerdan ma'lumotlarni olib tursa va har bir kirishda ma'lumotlarni yozsa, u sekin bo'lardi. Shuning uchun PostgreSQL xotira segmentiga ega, uning hajmi konfiguratsiyadagi sozlamalarga bog'liq. Postgres keyinchalik qayta ishlash yoki so'rov qilish uchun ushbu xotirada jonli ma'lumotlarni saqlaydi. Ma'lumotlarni o'zgartirish so'rovlari bo'lsa, ular o'zgartiriladi. Va biz ma'lumotlarning ikkita versiyasini olamiz. Biri xotiramizda, ikkinchisi diskda. Va vaqti-vaqti bilan siz ushbu ma'lumotlarni sinxronlashtirishingiz kerak. Xotirada o'zgartirilgan narsalarni disk bilan sinxronlashtirishimiz kerak. Buning uchun sizga nazorat punktlari kerak.

Tekshirish punkti umumiy buferlardan o'tadi, tekshirish punkti uchun kerak bo'lgan iflos sahifalarni belgilaydi. Keyin u umumiy buferlar orqali ikkinchi o'tishni boshlaydi. Va nazorat nuqtasi uchun belgilangan sahifalar, u allaqachon ularni sinxronlashtiradi. Shunday qilib, ma'lumotlar disk bilan sinxronlashtiriladi.

Tekshirish punktlarining ikki turi mavjud. Bitta nazorat punkti vaqt tugashi bilan bajariladi. Ushbu nazorat punkti foydali va yaxshi - checkpoint_timed. Va talab bo'yicha nazorat punktlari mavjud - checkpoint required. Ushbu nazorat nuqtasi bizda juda katta ma'lumotlar yozuviga ega bo'lganda sodir bo'ladi. Biz ko'plab tranzaksiya jurnallarini qayd etdik. Va PostgreSQL bularning barchasini imkon qadar tezroq sinxronlashtirishi, nazorat nuqtasini yaratishi va davom etishi kerak deb hisoblaydi.

Va agar siz statistikaga qarasangiz pg_stat_bgwriter va sizda nima borligini ko'rdim checkpoint_req checkpoint_timed dan ancha katta, bu yomon. Nega yomon? Bu PostgreSQL diskka ma'lumotlarni yozish kerak bo'lganda doimiy stress ostida ekanligini anglatadi. Vaqt tugashi nazorat punkti kamroq stressli va ichki jadvalga muvofiq amalga oshiriladi va vaqt o'tishi bilan tarqaladi. PostgreSQL ishni to'xtatib turish va diskning quyi tizimini zo'riqtirmaslik qobiliyatiga ega. Bu PostgreSQL uchun foydalidir. Tekshirish punktida bajariladigan so'rovlar diskning quyi tizimi band bo'lganligi sababli stressni boshdan kechirmaydi.

Va nazorat nuqtasini sozlash uchun uchta parametr mavjud:

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

Ular nazorat punktlarining ishini tartibga solish imkonini beradi. Lekin men ular haqida to'xtalmayman. Ularning ta'siri alohida mavzu.

Ogohlantirish: Hisobotda muhokama qilingan 9.4 versiyasi endi tegishli emas. PostgreSQL ning zamonaviy versiyalarida parametr checkpoint_segments parametrlar bilan almashtiriladi min_wal_size и max_wal_size.

Keyingi quyi tizim fon yozuvchisi - background writer. U nima qilyapti? U doimiy ravishda cheksiz tsiklda ishlaydi. Umumiy buferlardagi sahifalarni skanerlaydi va topilgan iflos sahifalarni diskka tashlaydi. Shunday qilib, nazorat punktini bajarish vaqtida nazoratchiga kamroq ish bajarishga yordam beradi.

Yana nimaga kerak? Agar ular to'satdan ma'lumotlarni joylashtirish uchun (ko'p miqdorda va darhol) talab etilsa, umumiy buferlarda bo'sh sahifalarga bo'lgan ehtiyojni ta'minlaydi. Aytaylik, so'rovni bajarish uchun bo'sh sahifalar talab qilingan va ular allaqachon umumiy buferlarda bo'lgan vaziyat yuzaga keldi. Postgresiv backend u faqat ularni oladi va ulardan foydalanadi, u hech narsani o'zi tozalashi shart emas. Ammo to'satdan bunday sahifalar bo'lmasa, backend ishlay boshlaydi va ularni diskka tashlash va o'z ehtiyojlari uchun olish uchun sahifalarni qidirishni boshlaydi - bu hozir bajarilayotgan so'rov vaqtiga salbiy ta'sir qiladi. Agar sizda parametr borligini ko'rsangiz maxwritten_clean katta, bu fon yozuvchisi o'z ishini qilmayotganligini anglatadi va siz parametrlarni oshirishingiz kerak bgwriter_lru_maxpages, shuning uchun u bir tsiklda ko'proq ish qila oladi, ko'proq sahifalarni tozalaydi.

Va yana bir juda foydali ko'rsatkich buffers_backend_fsync. Backendlar fsinxronlashtirilmaydi, chunki u sekin. Ular fsync ni IO stekini tekshirish nuqtasidan o'tkazadilar. Tekshirish nuqtasi o'z navbatiga ega, u vaqti-vaqti bilan fsync-ni qayta ishlaydi va xotiradagi sahifalarni diskdagi fayllar bilan sinxronlashtiradi. Agar nazorat punktidagi navbat katta va to'la bo'lsa, u holda backend fsync ni o'zi bajarishga majbur bo'ladi va bu backend ishini sekinlashtiradi., ya'ni mijoz mumkin bo'lganidan kechroq javob oladi. Agar sizning qiymatingiz noldan katta ekanligini ko'rsangiz, bu allaqachon muammo va fon yozuvchisi sozlamalariga e'tibor berishingiz va shuningdek, disk quyi tizimining ishlashini baholashingiz kerak.

Ogohlantirish: _Quyidagi matn replikatsiya bilan bog'liq statistik ko'rinishlarni tavsiflaydi. Postgres 10 da koʻpgina koʻrinish va funksiya nomlari qayta nomlandi. Qayta nomlashning mohiyati oʻzgartirishdan iborat edi. xlog haqida wal и location haqida lsn funktsiya/ko'rish nomlarida va hokazo. Maxsus misol, funktsiya pg_xlog_location_diff() deb qayta nomlandi pg_wal_lsn_diff()._

Bu yerda bizda ham ko'p narsa bor. Lekin bizga faqat joylashuv bilan bog'liq narsalar kerak.

Agar biz barcha qiymatlar teng ekanligini ko'rsak, bu ideal variant va replika ustadan orqada qolmaydi.

Bu o'n oltilik pozitsiyasi tranzaktsiyalar jurnalidagi pozitsiyadir. Ma'lumotlar bazasida biron bir faoliyat mavjud bo'lsa, u doimiy ravishda oshib boradi: qo'shish, o'chirish va hk.

сколько записано 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());

Agar bu narsalar boshqacha bo'lsa, unda qandaydir kechikish bor. Kechikish - replika va master o'rtasidagi kechikish, ya'ni ma'lumotlar serverlar o'rtasida farq qiladi.

Kechikishning uchta sababi bor:

Ushbu disk quyi tizimi yozib olish faylini sinxronlashtirishga dosh bera olmaydi.
Bular mumkin bo'lgan tarmoq xatolari yoki tarmoqning haddan tashqari yuklanishi, agar ma'lumotlar replikaga etib borishga ulgurmasa va uni qayta ishlab chiqara olmasa.
Va protsessor. Protsessor juda kam uchraydigan holat. Va men buni ikki yoki uch marta ko'rganman, lekin bu ham sodir bo'lishi mumkin.

Va bu erda statistikadan foydalanishga imkon beradigan uchta so'rov mavjud. Biz tranzaktsiyalar jurnalida qancha qayd etganimizni taxmin qilishimiz mumkin. Bunday funktsiya mavjud pg_xlog_location_diff va replikatsiya kechikishini bayt va soniyalarda baholashimiz mumkin. Buning uchun biz ushbu ko'rinishdagi qiymatdan (VIEWs) ham foydalanamiz.

Eslatma: _pg_xlog_location o'rnigadiff() funksiyasi ayirish operatoridan foydalanishi va bir joyni boshqasidan ayirishi mumkin. Qulay.

Kechikish bilan bir nuqta bor, bu soniyalarda. Agar masterda hech qanday faollik bo'lmasa, tranzaktsiya taxminan 15 daqiqa oldin bo'lgan va hech qanday faollik yo'q va agar biz replikatsiyadagi bu kechikishni ko'rib chiqsak, biz 15 daqiqalik kechikishni ko'ramiz. Buni eslash kerak. Va bu kechikishni ko'rganingizda chalkash bo'lishi mumkin.

Pg_stat_all_tables - yana bir foydali ko'rinish. Jadvallardagi statistik ma'lumotlarni ko'rsatadi. Ma'lumotlar bazasida jadvallar mavjud bo'lganda, u bilan qandaydir faollik, ba'zi harakatlar mavjud, biz ushbu ma'lumotni ushbu ko'rinishdan olishimiz mumkin.

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;

Biz ko'rib chiqishimiz mumkin bo'lgan birinchi narsa - jadval bo'ylab ketma-ket skanerlash. Ushbu o'tishlardan keyin raqamning o'zi yomon emas va biz biror narsa qilishimiz kerakligini ko'rsatmaydi.

Biroq, ikkinchi ko'rsatkich mavjud - seq_tup_read. Bu ketma-ket skanerdan qaytarilgan qatorlar soni. Agar o'rtacha raqam 1, 000 10, 000 50, 000 100 dan oshsa, bu allaqachon ko'rsatkich bo'lib, so'rovlar indeksga asoslangan bo'lishi uchun biror joyda indeks yaratishingiz kerak yoki shunday ketma-ket skanerlashdan foydalanadigan so'rovlarni optimallashtirish mumkin. bu sodir bo'lmasligi edi.

Oddiy misol - katta OFFSET va LIMIT xarajatlari bo'lgan so'rovni aytaylik. Masalan, jadvaldagi 100 000 ta satr skanerdan o'tkaziladi va shundan so'ng 50 000 ta kerakli satr olinadi va oldingi skanerlangan qatorlar o'chiriladi. Bu ham yomon holat. Va bunday so'rovlarni optimallashtirish kerak. Va bu erda oddiy SQL so'rovi mavjud, unda siz bunga qarashingiz va olingan raqamlarni baholashingiz mumkin.

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;

Jadval o'lchamlarini ushbu jadval yordamida va qo'shimcha funktsiyalar yordamida ham olish mumkin pg_total_relation_size(), pg_relation_size().

Umuman olganda, metabuyruqlar mavjud dt и di, bu PSQL da ishlatilishi mumkin, shuningdek, jadvallar va indekslarning o'lchamlarini ko'rish mumkin.

Shu bilan birga, funktsiyalardan foydalanish bizga indekslarni hisobga olgan holda yoki indekslarni hisobga olmagan holda jadvallarning o'lchamlarini ko'rib chiqishga yordam beradi va allaqachon ma'lumotlar bazasi o'sishiga, ya'ni u qanday o'sayotganiga, qanday intensivlikda va qanday o'sayotganiga qarab ba'zi taxminlarni amalga oshirishga yordam beradi. o'lchamlarni optimallashtirish bo'yicha ba'zi xulosalar chiqaring.

Yozib olish faoliyati. Yozuv nima? Keling, operatsiyani ko'rib chiqaylik UPDATE – jadvaldagi qatorlarni yangilash operatsiyasi. Aslida, yangilash ikkita operatsiya (yoki undan ham ko'proq). Bu qatorning yangi versiyasini kiritish va qatorning eski versiyasini eskirgan deb belgilash. Keyinchalik, avtovakuum kelib, ushbu eskirgan chiziqlarni tozalaydi va bu joyni qayta foydalanish mumkin deb belgilaydi.

Bundan tashqari, yangilash nafaqat jadvalni yangilash bilan bog'liq. Bu ham indeks yangilanishi. Agar jadvalda ko'plab indekslar mavjud bo'lsa, yangilash paytida so'rovda yangilangan maydonlarni o'z ichiga olgan barcha indekslar ham yangilanishi kerak bo'ladi. Ushbu indekslar, shuningdek, tozalanishi kerak bo'lgan satrlarning eskirgan versiyalariga ega bo'ladi.

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;

Va yangi dizayni tufayli UPDATE og'ir operatsiya hisoblanadi. Ammo ularni osonroq qilish mumkin. Yemoq hot updates. Ular PostgreSQL 8.3 versiyasida paydo bo'ldi. Va bu nima? Bu indekslarni qayta tiklashga olib kelmaydigan engil yangilanish. Ya'ni, biz rekordni yangiladik, lekin faqat sahifadagi yozuv (jadvalga tegishli) yangilandi va indekslar hali ham sahifadagi bir xil yozuvga ishora qiladi. Biroz qiziqarli operatsion mantiq bor: vakuum kelganda, u bu zanjirlarni yaratadi hot qayta quradi va hamma narsa indekslarni yangilamasdan ishlashda davom etadi va hamma narsa resurslarni kamroq sarflash bilan sodir bo'ladi.

Va qachon n_tup_hot_upd katta, keyin bu juda yaxshi. Bu shuni anglatadiki, engil yangilanishlar ustunlik qiladi va bu biz uchun resurslar nuqtai nazaridan arzonroq va hamma narsa yaxshi.

ALTER TABLE table_name SET (fillfactor = 70);

Ovozni qanday oshirish mumkin hot updateov? Biz foydalanishimiz mumkin fillfactor. U INSERTlar yordamida jadvaldagi sahifani to'ldirishda ajratilgan bo'sh joy hajmini aniqlaydi. Jadvalga qo'shimchalar qo'shilsa, ular sahifani to'liq to'ldiradi va bo'sh joy qoldirmaydi. Keyin yangi sahifa ajratib ko'rsatiladi. Ma'lumotlar yana to'ldiriladi. Va bu standart xatti-harakat, to'ldiruvchi = 100%.

Biz to'ldiruvchini 70% qilishimiz mumkin. Ya'ni, qo'shimchalar paytida yangi sahifa ajratib ko'rsatilgan, ammo sahifaning atigi 70% to'ldirilgan. Bizda esa zaxira sifatida 30% qoldi. Yangilash kerak bo'lganda, u xuddi shu sahifada sodir bo'ladi va chiziqning yangi versiyasi bir xil sahifaga mos keladi. Va hot_update amalga oshiriladi. Bu jadvallarga yozishni osonlashtiradi.

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

Avtovakuum navbati. Autovacuum - bu PostgreSQL-da juda kam statistik ma'lumotlar mavjud bo'lgan quyi tizim. Ayni paytda bizda qancha vakuum borligini faqat pg_stat_activity jadvallarida ko'rishimiz mumkin. Biroq, darhol navbatda qancha stol borligini tushunish juda qiyin.

Eslatma: _Postgres 10 dan boshlab, Vatovac kuzatuvi bilan bog'liq vaziyat sezilarli darajada yaxshilandi - pg_stat_progress ko'rinishi paydo bo'ldivakuum, bu avtomobil vakuumini kuzatish masalasini sezilarli darajada soddalashtiradi.

Ushbu soddalashtirilgan so'rovdan foydalanishimiz mumkin. Va biz vakuumni qachon qilish kerakligini ko'rishimiz mumkin. Ammo vakuumni qanday va qachon boshlash kerak? Bular men ilgari gapirgan satrlarning eski versiyalari. Yangilanish yuz berdi, qatorning yangi versiyasi kiritildi. Stringning eskirgan versiyasi paydo bo'ldi. Jadvalda pg_stat_user_tables Bunday parametr mavjud n_dead_tup. U "o'lik" chiziqlar sonini ko'rsatadi. Va o'lik qatorlar soni ma'lum bir chegaradan oshib ketishi bilanoq, stolga avtovakuum keladi.

Va bu chegara qanday hisoblanadi? Bu jadvaldagi qatorlar umumiy sonining juda aniq foizi. Parametr mavjud autovacuum_vacuum_scale_factor. U foizni belgilaydi. Aytaylik, 10% + 50 ta chiziqdan iborat qo'shimcha asosiy chegara mavjud. Va nima bo'ladi? Jadvaldagi barcha qatorlardan "10% + 50" dan ko'proq o'lik qatorlar bo'lsa, biz stolni avtovakuumga qo'yamiz.

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

Biroq, bitta nuqta bor. Parametrlar uchun asosiy chegaralar av_base_thresh и av_scale_factor alohida tayinlanishi mumkin. Va shunga ko'ra, pol global emas, balki jadval uchun individual bo'ladi. Shuning uchun, hisoblash uchun siz hiyla-nayranglardan foydalanishingiz kerak. Va agar siz qiziqsangiz, Avito'dagi hamkasblarimizning tajribasini ko'rib chiqishingiz mumkin (slayddagi havola haqiqiy emas va matnda yangilangan).

Ular uchun yozishgan munin plagini, bu narsalarni hisobga oladi. U yerda ikki choyshabli oyoq kiyimi bor. Ammo u to'g'ri hisoblab chiqadi va juda samarali bo'lgan jadvallar uchun juda ko'p vakuum kerak bo'lgan joyni baholashga imkon beradi.

Bu haqda nima qilishimiz mumkin? Agar bizda katta navbat bo'lsa va avtovakuum bardosh bera olmasa, biz vakuumchilar sonini ko'paytirishimiz yoki shunchaki vakuumni yanada tajovuzkor qilishimiz mumkin., u avvalroq ishga tushishi uchun jadvalni kichik qismlarga bo'lib qayta ishlaydi. Shunday qilib, navbat kamayadi. — Bu yerda asosiy narsa disklardagi yukni kuzatish, chunki... vakuum - bu bepul narsa emas, garchi SSD/NVMe qurilmalari paydo bo'lishi bilan muammo kamroq sezilib qoldi.

Pg_stat_all_indexes - indekslar bo'yicha statistika. U katta emas. Va biz indekslardan foydalanish bo'yicha ma'lumot olish uchun foydalanishimiz mumkin. Va, masalan, bizda qo'shimcha indekslar borligini aniqlashimiz mumkin.

Men allaqachon aytganimdek, yangilash nafaqat jadvallarni yangilash, balki indekslarni ham yangilashdir. Shunga ko'ra, agar bizda jadvalda ko'plab indekslar mavjud bo'lsa, unda jadvaldagi qatorlarni yangilashda indekslangan maydonlarning indekslari ham yangilanishi kerak va agar bizda indeks skanerlari bo'lmagan foydalanilmagan indekslar bo'lsa, ular balast sifatida osilib turadi. Va biz ulardan qutulishimiz kerak. Buning uchun bizga maydon kerak idx_scan. Biz shunchaki indekslarni skanerlash soniga qaraymiz. Agar indekslar statistik ma'lumotlarni saqlashning nisbatan uzoq vaqt davomida (kamida 2-3 hafta) nol skanerdan o'tkazilsa, unda bu yomon indekslar bo'lishi mumkin, biz ulardan xalos bo'lishimiz kerak.

Eslatma: Oqimli replikatsiya klasterlari holatida foydalanilmagan indekslarni qidirishda siz barcha klaster tugunlarini tekshirishingiz kerak, chunki statistika global emas va agar indeks masterda ishlatilmasa, uni replikalarda ishlatish mumkin (agar u erda yuk bo'lsa).

Ikki havola:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Bu foydalanilmagan indekslarni qanday qidirish bo'yicha yanada rivojlangan so'rovlar misollari.

Ikkinchi havola juda qiziq so'rov. U erda juda oddiy bo'lmagan mantiq bor. Malumot uchun tavsiya qilaman.

Indekslar yordamida yana nimani umumlashtirish kerak?

Foydalanilmayotgan indekslar yomon.
Ular joy egallaydi.
Yangilash operatsiyalarini sekinlashtiring.
Vakuum uchun qo'shimcha ish.

Agar foydalanilmagan indekslarni olib tashlasak, biz faqat ma'lumotlar bazasini yaxshilaymiz.

Keyingi taqdimot pg_stat_activity. Bu yordamchi dasturning analogidir ps, faqat PostgreSQL da. Agar ps'Om, siz operatsion tizimdagi jarayonlarga qaraysiz pg_stat_activity U sizga PostgreSQL ichidagi faoliyatni ko'rsatadi.

U erdan qanday foydali narsalarni olishimiz mumkin?

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

Biz umumiy faoliyatni, ma'lumotlar bazasida nima sodir bo'layotganini ko'rishimiz mumkin. Biz yangi joylashtirishni amalga oshirishimiz mumkin. Bu erda hamma narsa portladi, yangi ulanishlar qabul qilinmaydi, xatolar ilovaga quyiladi.

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

Biz shunga o'xshash so'rovni bajarishimiz va maksimal ulanish chegarasiga nisbatan ulanishlarning umumiy foizini ko'rishimiz va kimning eng ko'p ulanishini ko'rishimiz mumkin. Va bu berilgan holatda biz o'sha foydalanuvchini ko'ramiz cron_role 508 ta ulanish ochildi. Va u erda unga nimadir yuz berdi. Biz u bilan shug'ullanishimiz va unga qarashimiz kerak. Va bu qandaydir anomal ulanishlar soni bo'lishi mumkin.

Agar bizda OLTP ish yuki bo'lsa, so'rovlar tez, juda tez bo'lishi va uzoq so'rovlar bo'lmasligi kerak. Biroq, agar uzoq so'rovlar paydo bo'lsa, unda qisqa muddatda tashvishlanadigan hech narsa yo'q, lekin Uzoq muddatda uzoq so'rovlar ma'lumotlar bazasiga zarar etkazadi; ular jadvalning parchalanishi sodir bo'lganda jadvallarning shishish effektini oshiradi. Siz ham shishgan, ham uzoq so'rovlardan xalos bo'lishingiz kerak.

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;

E'tibor bering: ushbu so'rov orqali biz uzoq so'rovlar va tranzaksiyalarni aniqlashimiz mumkin. Biz funktsiyadan foydalanamiz clock_timestamp() ish vaqtini aniqlash uchun. Biz topgan uzoq so'rovlar, biz ularni eslay olamiz, bajara olamiz explain, rejalarga qarang va qandaydir tarzda optimallashtiring. Biz hozirgi uzoq so'rovlarni o'qqa tutamiz va hayotimizni davom ettiramiz.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Noto'g'ri tranzaksiyalar - bu tranzaksiya bo'yicha bo'sh turgan va tranzaksiya (to'xtatilgan) holatlaridagi operatsiyalar.

Bu nima degani? Tranzaksiyalar bir nechta holatga ega. Va bu holatlardan biri istalgan vaqtda qabul qilinishi mumkin. Davlatlarni aniqlash uchun maydon mavjud state ushbu taqdimotda. Va biz davlatni aniqlash uchun foydalanamiz.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Va yuqorida aytganimdek, bu ikki davlat tranzaksiyada idle va tranzaksiyada bo'sh (bekor qilingan) yomon. Bu nima? Ilova tranzaksiyani ochib, ba'zi harakatlarni amalga oshirdi va o'z biznesini davom ettirdi. Tranzaksiya ochiq qoladi. Postrges tranzaksiya mexanizmi arxitekturasi tufayli u osilib turadi, unda hech narsa bo'lmaydi, ulanishni oladi, o'zgartirilgan qatorlarda qulflanadi va boshqa jadvallarning shishishini oshirishi mumkin. Va bunday operatsiyalarni ham otib tashlash kerak, chunki ular odatda zararli, har holda.

Agar sizning ma'lumotlar bazangizda ularning 5-10-20 dan ortig'i borligini ko'rsangiz, tashvishlanishingiz va ular bilan biror narsa qilishni boshlashingiz kerak.

Bu erda biz hisoblash vaqtini ham ishlatamiz clock_timestamp(). Biz tranzaktsiyalarni o'tkazamiz va dasturni optimallashtiramiz.

Yuqorida aytib o'tganimdek, blokirovkalash - bu ikki yoki undan ortiq tranzaktsiyalar bir yoki bir guruh resurslar uchun kurashganda. Buning uchun bizda maydon bor waiting boolean qiymati bilan true yoki false.

To'g'ri - bu jarayon kutilayotganligini anglatadi, biror narsa qilish kerak. Agar jarayon kutayotgan bo'lsa, bu jarayonni boshlagan mijoz ham kutayotganligini anglatadi. Mijoz brauzerda o'tiradi va kutadi.

Ogohlantirish: _Postgres versiyasi 9.6 maydonidan boshlab waiting o'chirildi va uning o'rniga yana ikkita ma'lumotli maydon qo'shildi wait_event_type и wait_event._

Nima qilish kerak? Agar siz uzoq vaqt davomida haqiqatni ko'rsangiz, demak, bunday so'rovlardan xalos bo'lishingiz kerak. Biz shunchaki bunday operatsiyalarni o'chirib tashlaymiz. Biz ishlab chiquvchilarga resurslar uchun poyga bo'lmasligi uchun ular qandaydir tarzda optimallashtirishlari kerakligini yozamiz. Va keyin ishlab chiquvchilar bunday bo'lmasligi uchun dasturni optimallashtiradi.

Va ekstremal, ammo halokatli bo'lmagan holat o'liklarning paydo bo'lishi. Ikkita tranzaksiya ikkita resursni yangiladi, keyin ularga yana, bu safar qarama-qarshi resurslarga kirdi. Bunday holda, PostgreSQL tranzaksiyaning o'zini o'ldiradi, shunda boshqasi ishlashni davom ettiradi. Bu boshi berk ko'chadagi holat va u buni o'zi hal qila olmaydi. Shuning uchun PostgreSQL ekstremal choralar ko'rishga majbur.

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

Va bu erda blokirovkani kuzatish imkonini beruvchi ikkita so'rov mavjud. Biz view dan foydalanamiz pg_locks, bu sizga og'ir qulflarni kuzatish imkonini beradi.

Va birinchi havola so'rov matnining o'zi. Bu ancha uzoq.

Va ikkinchi havola - qulflar haqidagi maqola. O'qish foydali, juda qiziq.

Xo'sh, biz nimani ko'ramiz? Biz ikkita so'rovni ko'ramiz. bilan tranzaksiya ALTER TABLE blokirovka qiluvchi tranzaksiya hisoblanadi. U boshlandi, lekin tugallanmadi va ushbu tranzaksiyani qayd etgan dastur biron bir joyda boshqa narsalarni qilmoqda. Va ikkinchi so'rov - yangilash. U ishini davom ettirishdan oldin o'zgartirish jadvali tugashini kutadi.

Shunday qilib, kim kimni qulflaganini, kimni ushlab turganini bilib olamiz va biz bu bilan yanada ko'proq shug'ullanishimiz mumkin.

Keyingi modul pg_stat_statements. Aytganimdek, bu modul. Uni ishlatish uchun siz uning kutubxonasini konfiguratsiyaga yuklashingiz, PostgreSQL-ni qayta ishga tushirishingiz, modulni o'rnatishingiz kerak (bitta buyruq bilan) va keyin biz yangi ko'rinishga ega bo'lamiz.

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;

U erdan nimani olsak bo'ladi? Agar oddiy narsalar haqida gapiradigan bo'lsak, so'rovni bajarish uchun o'rtacha vaqtni olishimiz mumkin. Vaqt o'sib bormoqda, ya'ni PostgreSQL sekin javob beradi va biz nimadir qilishimiz kerak.

Biz umumiy buferlardagi ma'lumotlarni o'zgartiradigan ma'lumotlar bazasidagi eng faol yozish operatsiyalarini ko'rib chiqishimiz mumkin. U erda ma'lumotlarni kim yangilashi yoki o'chirishini ko'ring.

Va biz bu so'rovlar uchun turli statistik ma'lumotlarni ko'rib chiqishimiz mumkin.

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

Biz shundaymiz pg_stat_statements Biz undan hisobotlarni yaratish uchun foydalanamiz. Biz kuniga bir marta statistikani tiklaymiz. Keling, uni yig'amiz. Statistikani keyingi safar tiklashdan oldin, keling, hisobot tuzamiz. Bu yerda hisobotga havola. Siz tomosha qilishingiz mumkin.

Biz nima qilyapmiz? Biz barcha so'rovlar uchun umumiy statistikani hisoblaymiz. Keyin, har bir so'rov uchun biz ushbu umumiy statistikaga uning individual hissasini hisoblaymiz.

Va biz nimani tomosha qilishimiz mumkin? Biz boshqa barcha so'rovlar fonida ma'lum turdagi barcha so'rovlarning umumiy bajarilish vaqtini ko'rishimiz mumkin. Biz umumiy rasmga nisbatan protsessor va kirish/chiqish resurslaridan foydalanishni ko'rib chiqishimiz mumkin. Va bu so'rovlarni allaqachon optimallashtiring. Biz ushbu hisobot asosida eng yaxshi so'rovlarni tuzmoqdamiz va nimani optimallashtirish haqida o'ylash uchun allaqachon oziq-ovqat olamiz.

Sahna ortida nima qoldirdik? Hali vaqt cheklanganligi sababli ko'rib chiqmagan bir nechta taqdimotlar qoldi.

bor pgstattuple standart hissa to'plamidan qo'shimcha modul hamdir. Bu sizga baholash imkonini beradi bloat jadvallar, deb ataladi jadvalning parchalanishi. Va agar parchalanish juda ko'p bo'lsa, uni olib tashlashingiz va turli vositalardan foydalanishingiz kerak. Va funktsiya pgstattuple uzoq vaqt ishlaydi. Va qancha jadvallar mavjud bo'lsa, u uzoqroq ishlaydi.

Keyingi hissa pg_buffercache. Bu sizga umumiy buferlarni tekshirish imkonini beradi: bufer sahifalari qanchalik intensiv va qaysi jadvallar uchun ishlatilishini. Va bu oddiygina umumiy buferlarni ko'rib chiqish va u erda nima sodir bo'layotganini baholash imkonini beradi.

Keyingi modul pgfincore. Bu tizim qo'ng'irog'i orqali past darajadagi jadval operatsiyalarini bajarishga imkon beradi mincore(), ya'ni jadvalni umumiy buferlarga yuklash yoki uni tushirish imkonini beradi. Va bu, boshqa narsalar qatorida, operatsion tizimning sahifa keshini tekshirish imkonini beradi, ya'ni jadval sahifa keshida, umumiy buferlarda qancha joy egallaydi va shunchaki jadvalning ish yukini baholashga imkon beradi.

Keyingi modul - pg_stat_kcache. Shuningdek, u tizim chaqiruvidan foydalanadi getrusage(). Va uni so'rov bajarilishidan oldin va keyin bajaradi. Va natijada olingan statistik ma'lumotlarda bu bizning so'rovimiz diskni kiritish-chiqarish, ya'ni fayl tizimi bilan operatsiyalarga qancha sarflanganini va protsessordan foydalanishni ko'rib chiqishga imkon beradi. Biroq, modul yosh (yo'tal yo'tal) va uning ishlashi uchun PostgreSQL 9.4 va yuqorida aytib o'tganim pg_stat_statements talab qilinadi.

Statistikani qanday ishlatishni bilish foydalidir. Sizga uchinchi tomon dasturlari kerak emas. Siz kirishingiz, ko'rishingiz, biror narsa qilishingiz, biror narsa qilishingiz mumkin.
Statistikani ishlatish qiyin emas, bu oddiy SQL. Siz so'rovni to'pladingiz, tuzdingiz, yubordingiz, ko'rib chiqdingiz.
Statistikalar savollarga javob berishga yordam beradi. Savollaringiz bo'lsa, siz statistikaga murojaat qilasiz - qarang, xulosalar chiqaring, natijalarni tahlil qiling.
Va tajriba. Ko'p so'rovlar, juda ko'p ma'lumotlar bor. Siz har doim mavjud so'rovni optimallashtirishingiz mumkin. Siz so'rovning asl nusxadan ko'ra o'zingizga mos keladigan versiyasini yaratishingiz va undan foydalanishingiz mumkin.