Salam.
Mənim adım Vanyadır və mən Java proqramçısıyam. Belə olur ki, mən PostgreSQL ilə çox işləyirəm - verilənlər bazasını qurmaq, strukturu, performansı optimallaşdırmaq və həftə sonları bir az DBA oynamaq.
Bu yaxınlarda mən mikroservislərimizdə bir neçə verilənlər bazasını səliqəyə saldım və java kitabxanası yazdım
Məsuliyyətdən imtina
Mənim işlədiyim PostgreSQL-in əsas versiyası 10-dur. İstifadə etdiyim bütün SQL sorğuları da 11-ci versiyada sınaqdan keçirilir. Minimum dəstəklənən versiya 9.6-dır.
Prehistorya
Hər şey, demək olar ki, bir il əvvəl mənim üçün qəribə olan bir vəziyyətlə başladı: mavi bir indeksin rəqabətli yaradılması səhvlə başa çatdı. İndeksin özü, həmişə olduğu kimi, etibarsız vəziyyətdə verilənlər bazasında qaldı. Log analizi çatışmazlıq göstərdi
Problem bir - standart konfiqurasiya
Yəqin ki, hər kəs Postgres haqqında metaforadan çox yorulub, hansı ki, qəhvədəmləyəndə işlədilə bilər, lakin... standart konfiqurasiya həqiqətən bir sıra suallar doğurur. Ən azı buna diqqət yetirməyə dəyər texniki_iş_mem, temp_fayl_limit, bəyanat_zaman aşımı и lock_timeout.
Bizim vəziyyətimizdə texniki_iş_mem standart 64 MB idi və temp_fayl_limit 2 GB ətrafında bir şey - sadəcə böyük bir masada indeks yaratmaq üçün kifayət qədər yaddaşımız yox idi.
Buna görə də pg-index-sağlamlıq Serial yığdım
Problem iki - dublikat indekslər
Verilənlər bazalarımız SSD disklərində yaşayır və biz istifadə edirik HA-birdən çox məlumat mərkəzləri ilə konfiqurasiya, master host və n- replikaların sayı. Disk sahəsi bizim üçün çox qiymətli resursdur; performans və CPU istehlakından az əhəmiyyət kəsb etmir. Buna görə də, bir tərəfdən sürətli oxumaq üçün indekslərə ehtiyacımız var, digər tərəfdən isə verilənlər bazasında lazımsız indeksləri görmək istəmirik, çünki onlar yer tutur və məlumatların yenilənməsini ləngidir.
İndi hər şeyi bərpa etdikdən sonra
Üçüncü məsələ - kəsişən indekslər
Əksər təcrübəsiz tərtibatçılar bir sütunda indekslər yaradırlar. Tədricən, bu işi hərtərəfli təcrübədən keçirərək, insanlar sorğularını optimallaşdırmağa və bir neçə sütundan ibarət daha mürəkkəb indekslər əlavə etməyə başlayırlar. Sütunlardakı indekslər belə görünür A, A + B, A + B + C və s. Bu indekslərin ilk ikisini təhlükəsiz şəkildə atmaq olar, çünki onlar üçüncünün prefiksləridir. Bu, həm də çox disk sahəsinə qənaət edir və bunun üçün diaqnostika var
Dördüncü problem - indeksləri olmayan xarici açarlar
Postgres sizə dəstək indeksi göstərmədən xarici açar məhdudiyyətləri yaratmağa imkan verir. Bir çox hallarda bu problem deyil, hətta özünü göstərməyə də bilər... Hələlik...
Bizimlə də eyni idi: sadəcə olaraq, müəyyən bir vaxtda qrafikə uyğun işləyən və test sifarişlərinin məlumat bazasını təmizləyən bir iş master host tərəfindən bizə "əlavə olunmağa" başladı. CPU və IO boşa çıxdı, sorğular yavaşladı və vaxtı keçdi, xidmət beş yüz idi. Sürətli analiz
delete from <table> where id in (…)
Bu halda, təbii ki, hədəf cədvəlində id-ə görə indeks var idi və şərtə uyğun olaraq çox az qeydlər silindi. Sanki hər şey işləməlidir, amma təəssüf ki, olmadı.
Gözəl olanı köməyə gəldi təhlilini izah edin və dedi ki, hədəf cədvəlindəki qeydləri silməklə yanaşı, istinad bütövlüyünün yoxlanılması da var və əlaqəli cədvəllərdən birində bu yoxlama uğursuz olur. ardıcıl tarama uyğun indeksin olmaması ilə əlaqədardır. Beləliklə, diaqnostika yarandı
Problem beş - indekslərdə sıfır dəyər
Varsayılan olaraq, Postgres btree indekslərində null dəyərləri ehtiva edir, lakin ümumiyyətlə orada lazım deyil. Buna görə də, səylə bu nullları atmağa çalışıram (diaqnostika where <A> is not null
. Bu yolla indekslərimizdən birinin ölçüsünü 1877 MB-dan 16 KB-a endirə bildim. Və xidmətlərdən birində verilənlər bazası ölçüsü indekslərdən null dəyərlərin xaric edilməsi səbəbindən ümumilikdə 16% (mütləq rəqəmlərlə 4.3 GB) azalıb. Çox sadə dəyişikliklərlə disk sahəsində böyük qənaət. 🙂
Problem altıncı - əsas açarların olmaması
Mexanizmin təbiətinə görə
Bir gün, bir gözəl miqrasiya böyük və fəal şəkildə istifadə olunan cədvəldəki bütün qeydləri götürdü və yenilədi. Biz mavi masa ölçüsünə +100 GB aldıq. Bu lənətə gəlmiş biabırçılıq idi, amma uğursuzluqlarımız bununla bitmədi. Bu masanın üzərindəki avtovakuum 15 saat sonra başa çatdıqdan sonra fiziki yerin geri dönməyəcəyi aydın oldu. Xidməti dayandırıb VACUUM FULL edə bilmədik, ona görə də istifadə etmək qərarına gəldik
Kitabxana versiyasında 0.1.5 Cədvəllərdən və indekslərdən məlumat toplamaq və onlara vaxtında cavab vermək imkanı əlavə edilmişdir.
Yeddi və səkkizinci problemlər - qeyri-kafi indekslər və istifadə olunmamış indekslər
Aşağıdakı iki diaqnostika:
Artıq yazdığım kimi, biz bir neçə replika ilə konfiqurasiyadan istifadə edirik və müxtəlif hostlarda oxu yükü əsaslı şəkildə fərqlidir. Nəticədə, vəziyyət belə çıxır ki, bəzi hostlarda bəzi cədvəllər və indekslər praktiki olaraq istifadə edilmir və təhlil üçün klasterdəki bütün hostlardan statistika toplamaq lazımdır.
Bu yanaşma bizə heç vaxt istifadə olunmayan indeksləri silməklə, həmçinin nadir hallarda istifadə olunan cədvəllərə çatışmayan indeksləri əlavə etməklə bir neçə onlarla giqabayta qənaət etməyə imkan verdi.
Nəticə olaraq
Əlbəttə ki, demək olar ki, bütün diaqnostika üçün konfiqurasiya edə bilərsiniz
Bəzi diaqnostikalar verilənlər bazası miqrasiyasını həyata keçirdikdən dərhal sonra funksional testlərdə həyata keçirilə bilər. Və bu, bəlkə də kitabxanamın ən güclü xüsusiyyətlərindən biridir. İstifadə nümunəsini tapa bilərsiniz
İstifadə edilməmiş və ya çatışmayan indeksləri, eləcə də şişkinliyi yalnız real verilənlər bazasında yoxlamaq məntiqlidir. Toplanmış dəyərləri qeyd etmək olar
Mən həqiqətən buna ümid edirəm pg-index-sağlamlıq faydalı və tələbatlı olacaq. Siz həmçinin tapdığınız problemləri bildirməklə və yeni diaqnostika təklif etməklə kitabxananın inkişafına töhfə verə bilərsiniz.
Mənbə: www.habr.com