IBAnalyst: İpuçları ve Püf Noktaları

This text was originally written in 2012, it is valid for version 1.0 - 2.5, in versions 3.0-5.0 there were many changes, wgcih could not be reflected. Please read documentation or contact us for support: [email protected].

IBAnalyst Önerileri ve/veya Yardım bölümünde yanıtlanmayan bazı sorular:

1. PRIMARY, FOREIGN veya UNIQUE kısıtlamalarındaki indeksler nasıl yeniden oluşturulur?

C: Firebird 1.0-2.5 sürümleri için. Evet, kısıtlama indekslerinde ALTER INDEX xxx INACTIVE/ACTIVE kullanamazsınız. Bu kısıtlamada derin veya parçalanmış bir indeks görürseniz, özel bir numara kullanabilirsiniz (gbak tarafından geri yüklemede kullanılır):

RDB$INDICES, indeks aktifse (CREATE INDEX veya ALTER INDEX ACTIVE sonrası) null veya 0 olan RDB$INDEX_INACTIVE bayrağına sahiptir. 1, indeksin inaktif olduğu anlamına gelir (ALTER INDEX INACTIVE sonrası). Ancak kısıtlama indekslerinde inaktif indeksleri belirtmek için 3 değeri de kullanılır. Bu nedenle, o indeks için RDB$INDEX_INACTIVE=3 değerini ayarlayabilir, COMMIT yapabilir ve ardından değeri 0’a döndürüp tekrar commit yapabilirsiniz - indeks yeniden oluşturulacaktır.

Firebird 3.0-5.0 için - basitçe ALTER INDEX indexname ACTIVE yapın.

2. Tüm IBAnalyst önerilerini kullandım ama bu sorguları hızlandırmaya yardımcı olmadı.

C: Bu, IBAnalyst’ın yardımcı olamayacağı ayrı bir konudur. Sorunun 2 nedeni olabilir:

1. İndekslerin istatistikleri güncel değil. İndeks istatistiklerini SET STATISTICS INDEX xxx komutuyla yenileyebilirsiniz (daha fazla ayrıntı için http://www.ibase.ru/proc_selectivity/ adresine bakın).

2. Sorguda kullanılan bazı koşullar için uygun bir indeks yok.

3. Sorgular çok karmaşık veya optimize edici sorguyu optimize edemiyor, bu nedenle sorguyu yeniden düzenlemek gerekir.

4. Bazı durumlarda geri yüklemeden hemen sonra “parçalanmış tablolar” görürsünüz.

Normalde Firebird ve Interbase (-use_all_space parametresi olmadan) veri sayfalarında gelecekteki eklemeler, güncellemeler veya silmeler için (kayıt sürümlerini yerleştirmek üzere) yaklaşık %25 alan ayırır. Ancak, herhangi bir veritabanı sayfa boyutunda (1, 2, 4 veya 8 k) küçük kayıt boyutuna sahip tablolar için (yaklaşık ~12-20 bayt, örneğin 2 tamsayı alanı olan bir tablonun ortalama kayıt boyutu = 12 bayt) yaklaşık %50 parçalanma görürsünüz.

Bu normaldir, bunu sihirli bir sunucu numarası (veya davranışı) olarak düşünün.

Bu nedenle, bu tür küçük kayıt tablolarınız varsa:

a) bu tablolar için “parçalanmış” uyarısını yok sayabilirsiniz

b) örneğin IBAnalyst Seçenekler iletişim kutusunda “parçalanma %” değerini %45’e düşürebilirsiniz.

4. Güncellenmemesi gereken tablo için kayıt sürümleri

Güncellenmemesi gereken bir tabloda (örneğin, bazı olay günlükleri olan bir tablo) kayıt sürümleri görürseniz - endişelenmeyin, bu sürümler silme işlemiyle oluşturulur.

Böylece tabloda kaç güncel kayıt olduğunu ve kaç kaydın silindiğini bileceksiniz.

Bu yalnızca MaxVer = 1 ise geçerlidir. > 1 ise, bu tablo bazı uygulamalar tarafından güncelleniyor demektir. Bu tablonun asla güncellenmemesi gerektiğinden gerçekten eminseniz, hangi uygulamanın güncelleme yaptığını bulmak için “before update” tetikleyicisine bir istisna koymak daha iyidir.

5. Blob’lar tablo parçalanmasına neden olabilir.

Motor blob’ları 3 farklı şekilde saklar:

1. Blob içeriği veri sayfasına sığarsa (yeterli boş alan varsa), kaydının (veya sürümünün) yanındaki veri sayfasında saklanır.

2. Blob içeriği veri sayfasına sığmazsa, ayrı bir sayfada saklanır.

3. 2. durumda blob tek bir veri sayfasına sığmazsa, uygun blob sayfalarını göstermek için bir işaretçi sayfası oluşturulur.

4. durum, saklanan blob boyutuna ve veritabanı sayfa boyutuna bağlı olarak gerçekleşir. Örneğin, sayfa boyutu 4K ve ortalama boyutu ~5K olan blob’larınız varsa, bunlar veri sayfalarında değil, ek blob sayfalarında saklanır.

Ancak veritabanınızı yedekleyip 8K sayfa boyutuyla geri yüklerseniz, blob’lar veri sayfasına sığar ve kayıtlarla birlikte saklanarak yüksek kayıt parçalanmasına neden olur.

IBAnalyst bu tabloları Pale (Kayıtlar sütunu) olarak işaretler ve ipucu, bu tablo için tahmini kayıt sayısını (veri sayfası sayısına göre) ve gerçek ortalama doluluk değerini (%) gösterir.

Sorgunuz bu tablodan blob’lar dışında herhangi bir alanı okuyorsa, doğal tarama, birleştirme veya toplama çok yavaş çalışır.

Bunu önlemenin tek çözümü: orijinal tabloya 1-1 bağlantılı ek bir tablo oluşturmak ve sayfa boyutundan küçük ortalama boyuta sahip tüm blob sütunlarını bu tabloya taşımaktır.

Bu durumda daha büyük sayfa boyutuyla yedekleme/geri yükleme yapmayı denemeyin! Bu, mevcut sayfa boyutunda veri sayfalarına sığamayan blob’ların, daha büyük sayfa boyutuyla geri yükleme sırasında veri sayfalarına yerleştirilmesine neden olur. Böylece blob’lu tablolarınız eskisinden daha fazla parçalanır.

Ayrıca daha küçük sayfa boyutuyla geri yükleme önerilmez, çünkü bu indekslerin ve blob olmayan tabloların performansını düşürebilir.

Ayrıca blob alanlarını varchar alanlarına değiştirmeyi denememelisiniz - varchar alanları her zaman kaydın bir parçası olarak saklanır, bu nedenle kayıt veri sayfasına sığmazsa 2 veya daha fazla parçaya sahip olabilir (2 veya daha fazla veri sayfasına yerleştirilebilir).

not: IBAnalyst bu tabloları “yanlışlıkla” raporlayabilir, örneğin tabloda veri içeren blob alanları vardı ancak tablo yapısından kaldırıldı. Ne yazık ki bu uyarı için yapılandırılabilir bir seçenek yoktur, çünkü bunu sunucu tarafından bildirilen verilerden (istatistikler) tam olarak hesaplıyoruz.

6. VerLen ve RecLength ilişkisi

a) VerLen >= RecLength’in %90’ı: Sürüm sütununda gördüğünüz sürümler çoğunlukla kayıt silmeleridir. Ne kadar çok kayıt silinirse, RecLength o kadar azalır (0 bayta kadar). Ayrıca, tablonuzu orijinal kayıtlarda saklanandan daha büyük dize verileriyle güncellerseniz VerLen, RecLen’den büyük olabilir.

b) VerLen <= RecLength’in %80’i: sürümler çoğunlukla kayıt güncellemeleridir.

Bu durumları daha kesin olarak ayırt edemeyiz çünkü istatistikler tüm tablo için ortalama kayıt ve sürüm boyutunu gösterirken, eşzamanlı işlemler için görünür sürüm sayısı değişebilir.

7. IBAnalyst neden bazı indeksleri “kötü” olarak adlandırıyor?

Seçicilik değeri 0.01’den düşük olan indeksler IBAnalyst’ta “kötü” olarak işaretlenir (İndeks görünümü yardımına bakın). Belirli bir indeksi kötü olarak adlandırmanın birkaç nedeni vardır:

1. Bu indeksin seçiciliği 0.01’den düşüktür. Teorik olarak optimize edici bu indeksi kullanmamalıdır, ancak başka indeks yoksa (where, order by veya join cümlesi için en azından) kullanır.

2. Böyle bir indeks çok yavaş çöp toplamaya neden olur. Bu sorun InterBase 7.1/7.5’te yoktur ve Firebird 2.0’da düzeltilecektir.

3. Bu indeks geri yükleme sürecini çok yavaşlatır ve çok yavaş oluşturulur (create/alter index active). Bunun nedeni, bir indeks anahtarı için kayıt numarası zincirinin büyük olmasıdır.

4. Bu indeks where cümlesinde kullanılırsa, bellek kullanımı aranan değere (bitmask boyutu) bağlı olacaktır. Kayıt zinciri büyük olabileceğinden (çok sayıda anahtar kopyası), bellek tüketimi de büyük olacaktır.

5. Bu indeks “order by” içinde kullanılırsa ve çoğunlukla düşük anahtar değerlerinde (indeks sıralama düzenine bağlı olarak) çok sayıda kopya varsa, sorguyu yavaşlatacak çok sayıda indeks sayfası okuması olacaktır.

IBAnalyst bu tür indekslerin varlığını yok sayamadığı için bu böyledir.

İndeks için en kötü durum, Uniques sütunu = 1 olduğunda, yani indekslenen sütun için tüm değerler aynı olduğunda ortaya çıkar. Bu indeksler Özet sayfasında “İşe yaramaz indeksler” altında listelenir.

Elbette, uygulamanız için böyle bir indeks “iyi” olabilir. Örneğin, kayıtların bazı sütunlarda “arşiv” bayrağı varsa ve uygulamanız bu sütundaki indeksi yalnızca arşivlenmemiş güncel veriler için kullanıyorsa. Bu nedenle, bu indeksi “kötü” olarak adlandırmamızın doğru olup olmadığı size kalmış.

8. “Kötü” indeks Foreign Key kısıtlaması tarafından oluşturulduysa ne olur?

Önceki paragraf, “kötü” indeksleri bırakmanın daha iyi olduğunu gösterir (diğer anahtarlardan daha az kopyası olan anahtarları aramak için kullanmıyorsanız). Ancak, böyle bir indeks yabancı anahtar tarafından oluşturulduysa, onu yalnızca yabancı anahtarı bırakarak kaldırabilirsiniz. Yabancı anahtarı bırakmak, ilişki kontrol kısıtlamasını devre dışı bırakır ve bu kabul edilemez olabilir.

FK’yı tetikleyicilerle değiştirebilirsiniz, ancak bazı kısıtlamalarla. FK, kayıt ilişkilerini indeks kullanarak kontrol eder ve indeks, işlem durumundan bağımsız olarak tüm kayıtlar için tüm anahtarları “görür”. Ancak tetikleyiciler yalnızca istemcinin işlem bağlamında çalışır. Bu nedenle, FK’yı tetikleyicilerle değiştirirken şunlardan emin olmalısınız:

• Kayıtlar ana tablodan silinmeyecek veya “snapshot table reserving” modunda silinmeyecek
• Ana tablodaki PK tarafından kullanılan sütun asla değiştirilmeyecek. Bunu before update tetikleyicisiyle kısıtlayabilirsiniz.

Bu koşulları sağlarsanız, belirli Foreign Key’i bırakabilirsiniz. Elbette, bu sütunda manuel olarak indeks oluşturmayın.

9. Veri sürümü yüzde satırında neden yalnızca 12 megabayt veri var, ancak 140 megabaytlık veritabanım var?

1. IBAnalyst burada diğer veritabanı yapılarını (indeksler, meta veriler…) ve sayfa parçalanmasını saymadan “saf” veri hacmini gösterir.

2. Geri yüklemeden sonra InterBase ve Firebird, gelecekteki güncellemeleri/silmeleri hızlandırmak için veri sayfalarında biraz boş alan (%15-25) bırakır.

3. Tablo kayıt boyutu düşükse (~11-22 bayt), sunucunun veri sayfalarını ~%50 oranında parçalanmış bıraktığı belirli bir sunucu davranışı vardır.

10. Sık güncellemeler durumunda optimize edici performansı nasıl iyileştirilir

İndeks istatistikleri RDB$INDICES.RDB$STATISTICS sütununda saklanır ve 3 şekilde güncellenir:

1. SET STATISTICS INDEX

2. ALTER INDEX ACTIVE veya CREATE INDEX …

3. geri yükleme işlemi (tüm indeksler “ALTER INDEX ACTIVE” ile birlikte yeniden oluşturulur)

Optimize edici, sorguları hazırlamak için bu istatistik bilgilerini kullanır. İstatistik değerlerini kullanarak optimize edici, indeksin kayıtları almak için “yeterince iyi” veya “kullanışsız” olduğuna karar verebilir.

İstatistikler uzun süre güncellenmediyse, optimize edici kötü bir plan üretebilir çünkü mevcut istatistik değerleri gerçek duruma karşılık gelmez, çünkü tablo verileri önemli ölçüde değişmiş olabilir (örneğin, kayıt sayısı 5-10 kat arttı veya tam tersi, tüm kayıtlar silindi).

Belirli bir sorgu için kötü otomatik sorgu planını açık PLAN ile değiştirebilirsiniz, ancak bu iyi bir yaklaşım değildir, çünkü plan geliştirildikten sonra veriler önemli ölçüde değişebilir.

Alternatif (ve doğru) yol, tüm indeksler için SET STATISTICS ifadesini uygulayarak istatistikleri periyodik olarak yenilemektir. ISQL kullanarak istatistikleri yenilemek için bir SQL betiği çalıştırmayı veya hazır bir araç olan gidx’i (yalnızca Windows) kullanmayı planlayabilirsiniz.

Periyodik olarak farklı kayıtlar yüklenen bazı tablolarınız varsa bu yaklaşım yardımcı olmaz. Örneği ele alalım:

• A tablosu günde 4-5 kez veriyle yüklenir.
• Yüklenen verilerin işlenmesinden sonra A tablosundaki tüm kayıtlar silinir.

Bu durumda, A tablosundaki indeksler için 2 doğru istatistik değeri görebiliriz - veriyle yüklendiğinde ve boş olduğunda. Bu nedenle, yüklü tabloda yeniden hesaplanan istatistikler tablo boşken işe yaramaz ve bunun tersi de geçerlidir.

Bunu önlemek için, A tablosundaki indekslerin istatistiklerini yalnızca tablo veriyle doldurulduğunda yeniden hesaplamanız gerekir. En iyisi, bu tablodaki sorgular çalıştırılmadan önce yapılmasıdır.

1.91 sürümünden bu yana, IBAnalyst indeks istatistik farkını gösterir ve istediğiniz zaman yeniden hesaplamanıza olanak tanır. Önce tablo kayıt bilgisine bakmanız gerekir - bu normal ortalama kayıt sayısı mı değil mi? Evet ise, indeks seçiciliğini kesin olarak yeniden hesaplayabilirsiniz. Değilse - indeks istatistiklerine dokunmamak daha iyi olabilir, çünkü bu optimize edicinin daha da kötü sorgu planları üretmesine neden olabilir.