g u t x .com.tr İpek yolu - Çin'i anlamaya götürürüm

Optimizasyon sistemi-mysql veritabanı sorgu önbellek özeti

Genel Bakış

mysql sorgu önbelleği, veritabanı optimizasyonunda çok önemli bir rol oynayabilir. Bugün bunu esas olarak özetleyeceğim. Şimdi birlikte bir göz atalım ~

1. Önbellek koşulları ve ilkeleri

MySQL Sorgu Önbelleği, yürüttüğümüz SELECT ifadesini ve ifadenin sonuç kümesini önbelleğe almak için kullanılır, MySql, Query Cache uygulamasının belirli teknik detaylarında tipik KV depolamasına benzer , SELECT ifadesinin ve sorgu ifadesinin sonuç kümesinin HASH eşlemesini yapmak ve belirli bir bellek alanına kaydetmektir. İstemci bir SQL sorgusu başlattığında, Sorgu Önbelleğinin arama mantığı, önce SQL'de karşılık gelen izinleri doğrulamak ve ardından sonuçları bulmak için Sorgu Önbelleğini kullanmaktır (bir boşluk daha olsa veya durum farklı olsa bile bunların tamamen aynı olması gerektiğini unutmayın, bunlar farklı kabul edilir. Aynı SQL kullanılsa bile farklı karakter setleri, farklı protokoller vb. Kullanırsanız farklı sorgular olarak değerlendirilecek ve ayrı olarak önbelleğe alınacaktır). Yürütme planını analiz etmek ve optimize etmek için Optimizer modülünden geçmesi gerekmez ve herhangi bir depolama motoruyla etkileşime girmesi gerekmez, bu da çok fazla disk GÇ ve CPU işlemlerini azaltır, bu nedenle bazen çok verimlidir.

Önbelleği sorgulamanın iş akışı aşağıdaki gibidir:

1: Vuruş koşulu

Önbellek bir karma tablosunda saklanır ve SQL sorgulama, veritabanını, istemci protokolünü vb. Sorgulayarak anahtar olarak kullanılır. Bir isabet olup olmadığına karar vermeden önce, MySQL SQL'i ayrıştırmaz, ancak önbelleği sorgulamak için doğrudan SQL'i kullanır, örneğin SQL karakterlerindeki herhangi bir fark Boşluklar ve yorumlar önbellek eksikliklerine neden olur.

Sorguda CURRENT_DATE () ve NOW () işlevleri gibi belirsiz veriler varsa, sorgu tamamlandıktan sonra önbelleğe alınmayacaktır.Bu nedenle, belirsiz veriler içeren bir sorgu kesinlikle kullanılabilir bir önbellek bulmayacaktır.

2: iş süreci

1. Sunucu SQL alır, önbellek tablosunu SQL ve anahtar olarak diğer bazı koşullarla arar (ekstra performans tüketimi)

2. Önbellek bulunursa, doğrudan önbelleğe dönün (performans artışı)

3. Önbellek bulunamazsa, orijinal SQL analizi, optimizasyonu vb. Dahil olmak üzere SQL sorgusunu yürütün.

4. SQL sorgu sonuçlarını yürüttükten sonra, SQL sorgu sonuçlarını önbellek tablosunda saklayın (ekstra performans tüketimi)

İlgili iki SQL ifadesi

2.1. SQL önbellek parametrelerini görüntüleyin: '% query_cache%' gibi değişkenleri gösterin;

Her parametrenin anlamı aşağıdaki gibidir:

Qcache_free_blocks: Önbellekteki bitişik bellek bloklarının sayısı. Büyük sayı, parçaların olabileceğini gösterir. FLUSH QUERY CACHE, ücretsiz bir blok elde etmek için önbellekteki parçaları sıralayacaktır.
Qcache_free_memory: Önbellekte boş bellek.
Qcache_hits: önbelleğe bir sorgu her geldiğinde artar
Qcache_inserts: Her sorgu eklendiğinde artırın. Ekleme sayısına bölünen isabet sayısı, ıskalama oranıdır.
Qcache_lowmem_prunes: Önbelleğin belleğinin kaç kez çalıştırıldığı ve daha fazla sorgu için alan sağlamak üzere temizlenmesi gerektiği sayısı. Bu sayı en iyi uzun bir süre boyunca izlenir; sayı artıyorsa, bu, parçalanmanın çok ciddi olabileceği veya hafızanın küçük olabileceği anlamına gelir. (Yukarıdaki free_blocks ve free_memory size hangi duruma ait olduğunu söyleyebilir)
Qcache_not_cached: Önbelleğe almaya uygun olmayan sorguların sayısıdır, çünkü genellikle bu sorgular SELECT deyimleri değildir veya now () gibi işlevleri kullanır.
Qcache_queries_in_cache: Şu anda önbelleğe alınan sorguların (ve yanıtların) sayısı.
Qcache_total_blocks: Önbellekteki blok sayısı.

2.2. SQL önbelleğini açın: global query_cache_type = 1 olarak ayarlayın;

2.3. SQL önbelleğini kapatın: global query_cache_type = 0 olarak ayarlayın;

2.4. Önbellek alanını ayarlayın: global query_cache_size = 1024 * 1024 * 64 (64M) olarak ayarlayın

2.5. Sabit SQL ifadesi ifadesi önbellek için geçerli değildir: tablo adından sql_no_cache * seçin

Not: SQL ifadelerinin durumunu değiştirirseniz veya veri tablosunda veri değişiklikleri varsa, önbellek çağrılmaz.

2.6, sorgu önbelleğini yapılandırın

vi /etc/my.cnf

query_cache_size = 300 milyon query_cache_type = 1

üç, önbelleği temizle

mysql FLUSH sözdizimi (önbelleği temizle)

FLUSH flush_option

MySQL tarafından kullanılan dahili önbelleğin bir kısmını temizlemek istiyorsanız, FLUSH komutunu kullanmalısınız. FLUSH işlemini gerçekleştirmek için yeniden yükleme iznine sahip olmanız gerekir.

flush_option aşağıdakilerden herhangi biri olabilir:

HOSTS en çok kullanılan ve ben sık sık karşılaşıyorum. Esas olarak ana bilgisayar önbellek tablosunu temizlemek için kullanılır. Bazı ana bilgisayarlarınız IP numarasını değiştirirse veya Ana Bilgisayar ... engellendi hata mesajını alırsanız, ana bilgisayar tablosunu temizlemelisiniz. MySQL sunucusuna bağlanırken, belirli bir ana bilgisayar için sürekli olarak max_connect_errors hatalarından daha fazlası meydana gelir.MySQL, güvenlik amacıyla ana bilgisayardan gelen diğer bağlantı isteklerini engeller. Ana bilgisayar tablosunun temizlenmesi, ana bilgisayarın yeniden bağlanmayı denemesine izin verir.
LOGS, geçerli ikili günlük dosyasını kapatır ve yeni bir dosya oluşturur Yeni ikili günlük dosyasının adı, geçerli ikili dosya numarasına 1 ekler.
PRIVILEGES de sıklıkla kullanılmaktadır.Yeniden yetkilendirmeden sonra yeni izinlerin hemen devreye girmesi için, her ihtimale karşı, genellikle çalıştırılır.Amaç, veritabanı yetkilendirme tablosundan izinleri önbelleğe yeniden yüklemektir.
TABLOLAR tüm açık tabloları kapatır ve bu işlem sorgu önbelleğinin içeriğini temizler.
OKUMA KİLİDİ İLE TABLALARI AKTAR, tüm açık tabloları kapatır ve kilit açma tabloları açıkça çalıştırılana kadar veritabanındaki tüm tablolara bir okuma kilidi ekler Bu işlem genellikle veri yedekleme için kullanılır.
STATUS, çoğu durum değişkenini 0'a sıfırlar.
MASTER, ikili günlük dizin dosyasındaki tüm ikili günlük dosyalarını siler, ikili günlük dosyasının dizin dosyasını boş olacak şekilde sıfırlar ve yeni bir ikili günlük dosyası oluşturur, ancak bu önerilmez ve ana verileri sıfırlamak için değiştirilir. Eskiden bu kadar dünyevi olduğum düşünülebilir.Bunu basit bir komutla idare edebilirdim, ancak birkaç komut aldı.Önceki yöntem, önce geçerli ikili günlük dosyası adını bulmak ve ardından temizleme işlemini kullanmaktı.
QUERY CACHE sorgu önbelleğini yeniden düzenler, parçaları ortadan kaldırır ve performansı iyileştirir, ancak sorgu önbelleğindeki mevcut verileri etkilemez. Bu, Tabloyu Temizle ve Sorgu Önbelleğini Sıfırla'dan (sorgu önbelleğinin içeriğini temizler) farklıdır.
KÖLE, çoğaltmanın sıfırlanmasına benzerdir. Bağımlı veri tabanının ana veri tabanının çoğaltma konumunu unutmasına izin verin ve ayrıca indirilen röle günlüğünü silin. Ana gibi, artık tavsiye edilmez. Bu da çok kullanışlıdır.

Genel olarak, Flush işlemleri ikili günlük dosyasına kaydedilir, ancak FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, FLUSH TABLES with READ LOCK kaydedilmez, bu nedenle yukarıdaki işlemler ikili günlük dosyasına kaydedilirse, slave veritabanına neden olur. etkiler. Not: Sıfırlama işlemi aslında Yıkama işleminin gelişmiş bir sürümü rolünü oynar.

Dört, önbellek yönetimi

Önbellek, önbellek verilerini korumak için bellekte bir bellek parçası (query_cache_size) açacaktır; bunun yaklaşık 40K alanı önbelleğin meta verilerini, örneğin uzamsal bellek, veri tablolarının eşleştirilmesi ve sorgu sonuçları, SQL eşlemesi ve sorgu sonuçları Bekle.

MySQL, bu büyük bellek bloğunu küçük bellek bloklarına (query_cache_min_res_unit) böler ve her küçük blok, ön ve arka bellek bloklarına işaretçilerin yanı sıra kendi tür, boyut ve sorgu sonuç verilerini depolar.

MySQL'in tek bir küçük depolama bloğunun boyutunu ayarlaması ve SQL sorgusunun başlangıcında bir alan için başvurması gerekir (sonuç henüz elde edilmemiştir), bu nedenle önbellek verileriniz bu boyuta ulaşmasa bile, kaydetmek için bu boyutta bir veri bloğu kullanmanız gerekir (bu (Nokta, Linux dosya sisteminin Bloğu ile aynıdır) Sonuç bu bellek bloğunun boyutunu aşarsa, başka bir bellek bloğu için başvurmanız gerekir.Sorgu tamamlandığında ve istenen bellek bloğunun fazla olduğu tespit edildiğinde, fazla alan serbest bırakılacaktır. Bellek parçalanma sorunlarına neden olur, aşağıdaki şekle bakın

Sorgu sonuçlarını depoladıktan sonra kalan sorgu parçalarını önbelleğe alma

Burada, sorgu 1 ve sorgu 2 arasındaki boş kısım bellek parçalanmasıdır. Boş belleğin bu kısmı, sorgu 1 tamamlandıktan sonra serbest bırakılır. Bu alanın MySQL tarafından belirlenen bellek bloğu boyutundan daha küçük olduğu varsayılırsa, artık kullanılamaz ve sonuçta Parçalanma sorunu

Sorgunun başlangıcında bir bellek bloğunun tahsisine başvurmak için, tüm boş bellek alanını kilitlemeniz gerekir, bu nedenle bir bellek bloğunu ayırmak çok yoğun kaynak gerektirir Burada belirtilen ayrılmış belleğin MySQL başlatıldığında oluşturulan belleğe tahsis edildiğini unutmayın.

5. Önbelleğin zamanlaması ve performansı

Önbelleği açmanın sistemin performansını iyileştirip iyileştirmediğini ölçmek zor bir konudur

1. Önbellek isabet oranına, önbellek isabet oranına göre = önbellek isabetleri (Qcache_hits) / sorgular (Com_select)

2. Önbellek yazma oranı aracılığıyla, yazma hızı = önbellek yazma süreleri (Qcache_inserts) / sorgu süreleri (Qcache_inserts)

3. Hit-yazma oranına göre, oran = isabet sayısı (Qcache_hits) / yazma sayısı (Qcache_inserts), yüksek performanslı MySQL'de performans gelişimini yansıtan bir indeks olarak adlandırılır. Genel olarak konuşursak, 3: 1'e ulaşırsa sorgudur. Önbellek etkilidir ve 10: 1'e ulaşmak en iyisidir

Özellikle sık yazılan bazı sistemler için her şey çok fazladır Sorgu Önbelleği işlevini etkinleştirmek sistem performansını iyileştirmeyebilir ve bazen azalır. Bunun nedeni, MySql'in Sorgu Önbelleği tarafından önbelleğe alınan içeriğin gerçek verilerle kesinlikle tutarlı olmasını sağlamasıdır. Bir veri tablosu güncellendiğinde, silindiğinde veya eklendiğinde, MySql, tabloya başvuran tüm sorgu SQL'lerinin Sorgu Önbelleğini geçersiz kılınmaya zorlar. Yoğun yazma işlemleri için, sorgu önbelleğinin etkinleştirilmesi, önbelleğin sık sık geçersiz kılınmasına neden olabilir, bu da dolaylı olarak bellekte bir artışa ve CPU'da bir artışa neden olur , Bu zaten çok meşgul bir veritabanı sistemi için büyük bir yüktür.

Altı, sorgu önbellek problem analizi

Sorgu önbelleğini analiz edin ve yapılandırın

sonuç olarak

Burada, Sorgu Önbelleği uygulamasının MyISAM gibi MySql'in farklı depolama motorları nedeniyle biraz farklı olduğu, önbelleğe alınan sonuç kümesinin OS Önbelleğinde saklandığı ve en popüler InnoDB'nin Arabellek Havuzuna yerleştirildiği belirtilmelidir.

Daha sonra daha fazla devop ve DBA içeriği paylaşacağım ve ilgilenen arkadaşlar buna dikkat edebilir ~