MySQL için 10 temel performans ipucu

Tüm ilişkisel veritabanlarında olduğu gibi, MySQL karmaşık bir canavar olduğunu kanıtlayabilir, uygulamalarınızı bir anda kesintiye uğrayarak ve işinizi tehlikeye atarak bir anda durma noktasına gelebilir.

Gerçek şu ki, MySQL performans sorunlarının çoğunun temelinde yaygın hatalar yatmaktadır. MySQL sunucunuzun en yüksek hızda çalışmasını sağlamak, istikrarlı ve tutarlı performans sağlamak için, genellikle iş yükünüzdeki bazı inceliklerin veya bir yapılandırma tuzağının gizlediği bu hataları ortadan kaldırmak önemlidir.

Neyse ki, birçok MySQL performans sorununun benzer çözümlere sahip olduğu ortaya çıktı, bu da sorun gidermeyi ve MySQL'i ayarlamayı yönetilebilir bir görev haline getiriyor.

İşte MySQL'den harika performans elde etmek için 10 ipucu.

MySQL performans ipucu No. 1: İş yükünüzün profilini çıkarın

Sunucunuzun zamanını nasıl harcadığını anlamanın en iyi yolu, sunucunun iş yükünün profilini çıkarmaktır. İş yükünüzün profilini çıkararak, daha fazla ayarlama için en pahalı sorguları ortaya çıkarabilirsiniz. Burada, zaman en önemli ölçüdür, çünkü sunucuya karşı bir sorgu gönderdiğinizde, ne kadar hızlı tamamlandığı dışında hiçbir şeyi çok az önemsersiniz.

İş yükünüzün profilini çıkarmanın en iyi yolu, MySQL Enterprise Monitor'un sorgu analizcisi veya Percona Toolkit'ten pt-query-abstract gibi bir araç kullanmaktır. Bu araçlar, sunucunun yürüttüğü sorguları yakalar ve azalan yanıt süresi sırasına göre sıralanmış bir görev tablosu döndürür, çabalarınızı nereye odaklayacağınızı görebilmeniz için en pahalı ve zaman alan görevleri anında en üste çıkarır.

İş yükü profili oluşturma araçları, benzer sorguları bir arada gruplandırarak, yavaş olan sorguların yanı sıra hızlı ancak birçok kez yürütülen sorguları görmenize olanak tanır.

MySQL performans ipucu No. 2: Dört temel kaynağı anlayın

Bir veritabanı sunucusunun çalışması için dört temel kaynağa ihtiyacı vardır: CPU, bellek, disk ve ağ. Bunlardan herhangi biri zayıf, düzensiz veya aşırı yüklenmişse, veritabanı sunucusunun kötü performans gösterme olasılığı çok yüksektir.

Temel kaynakları anlamak iki belirli alanda önemlidir: donanım seçimi ve sorunları giderme.

MySQL için donanım seçerken, her yerde iyi performans gösteren bileşenlerden emin olun. Aynı derecede önemli olan, onları birbirlerine karşı oldukça iyi dengeleyin. Çoğu zaman, kuruluşlar hızlı CPU'lara ve disklere sahip ancak belleğe ihtiyaç duyan sunucuları seçeceklerdir. Bazı durumlarda bellek eklemek, özellikle diske bağlı iş yüklerinde, büyüklük sıralarına göre performansı artırmanın ucuz bir yoludur. Bu mantığa aykırı görünebilir, ancak çoğu durumda, sunucunun çalışan veri kümesini tutmak için yeterli bellek olmadığından diskler aşırı kullanılır.

Bu dengenin bir başka güzel örneği CPU'larla ilgilidir. Çoğu durumda, MySQL hızlı CPU'larla iyi performans gösterir çünkü her sorgu tek bir iş parçacığında çalışır ve CPU'lar arasında paralelleştirilemez.

Sorun giderme söz konusu olduğunda, düşük performans gösterip göstermediklerini veya sadece çok fazla iş yapmaları istenip istenmediğini belirlerken dikkatli bir şekilde dört kaynağın da performansını ve kullanımını kontrol edin. Bu bilgi, sorunları hızlı bir şekilde çözmeye yardımcı olabilir.

MySQL performans ipucu No. 3: MySQL'i kuyruk olarak kullanmayın

Kuyruklar ve kuyruk benzeri erişim modelleri, siz bilmeden uygulamanıza gizlice girebilir. Örneğin, belirli bir çalışan işlemin üzerinde işlem yapmadan önce hak iddia edebilmesi için bir öğenin durumunu ayarlarsanız, farkında olmadan bir kuyruk oluşturursunuz. E-postaları gönderilmedi olarak işaretlemek, göndermek ve ardından gönderildi olarak işaretlemek yaygın bir örnektir.

Kuyruklar iki ana nedenden dolayı sorunlara neden olur: İş yükünüzü seri hale getirirler, görevlerin paralel olarak yapılmasını engellerler ve genellikle süreçteki işleri ve uzun zaman önce işlenmiş işlerin geçmiş verilerini içeren bir tabloyla sonuçlanırlar. Her ikisi de uygulamaya gecikme ekler ve MySQL'e yükler.

MySQL performans ipucu No. 4: Sonuçları önce en ucuza göre filtreleyin

MySQL'i optimize etmenin harika bir yolu, önce ucuz, kesin olmayan işler, ardından daha küçük, sonuçta ortaya çıkan veri kümesi üzerinde zor ve hassas çalışma yapmaktır.

Örneğin, coğrafi bir noktanın belirli bir yarıçapı içinde bir şey aradığınızı varsayalım. Birçok programcının araç kutusundaki ilk araç, bir kürenin yüzeyi boyunca mesafeyi hesaplamak için büyük daire (Haversine) formülüdür. Bu teknikle ilgili sorun, formülün çok CPU yoğun olan çok sayıda trigonometrik işlem gerektirmesidir. Büyük daire hesaplamaları yavaş çalışma eğilimindedir ve makinenin CPU kullanımını fırlatır.

Büyük daire formülünü uygulamadan önce kayıtlarınızı toplamın küçük bir alt kümesine ayırın ve ortaya çıkan kümeyi kesin bir daireye bölün. Daireyi içeren bir kare (kesin veya kesin olarak) bunu yapmanın kolay bir yoludur. Bu şekilde, meydanın dışındaki dünya, tüm bu maliyetli trigonometrik işlevlerle asla çarpılmaz.

MySQL performans ipucu No. 5: Ölçeklenebilir iki ölüm tuzağını öğrenin

Ölçeklenebilirlik, sandığınız kadar belirsiz değildir. Aslında, ölçeklenebilirliğin denklemler olarak ifade edilen kesin matematiksel tanımları vardır. Bu denklemler, sistemlerin neden olması gerektiği kadar iyi ölçeklenmediğini vurgular.

Bir sistemin ölçeklenebilirlik özelliklerini ifade etmek ve ölçmek için kullanışlı bir tanım olan Evrensel Ölçeklenebilirlik Yasasını ele alalım. Ölçeklendirme problemlerini iki temel maliyet açısından açıklar: serileştirme ve çapraz konuşma.

Serileştirilmiş bir şeyin gerçekleşmesi için durması gereken paralel süreçler, ölçeklenebilirlikleri açısından doğaları gereği sınırlıdır. Aynı şekilde, paralel süreçlerin çalışmalarını koordine etmek için her zaman birbirleriyle sohbet etmeleri gerekiyorsa, birbirlerini sınırlarlar.

Serileştirme ve çapraz karışıklıktan kaçının, uygulamanız çok daha iyi ölçeklenir. Bu MySQL'in içinde ne anlama geliyor? Değişir, ancak bazı örnekler satırlarda özel kilitlerden kaçınmak olabilir. Yukarıdaki 3. nokta olan kuyruklar, bu nedenle yetersiz ölçeklenme eğilimindedir.

MySQL performans ipucu No. 6: Yapılandırmaya çok fazla odaklanmayın

DBA'lar yapılandırma ayarlarında çok fazla zaman harcama eğilimindedir. Sonuç genellikle büyük bir gelişme değildir ve hatta bazen çok zararlı olabilir. Sürekli çöken, hafızası biten ve iş yükü biraz daha yoğunlaştığında kötü performans gösteren birçok "optimize edilmiş" sunucu gördüm.

MySQL ile birlikte gönderilen varsayılanlar tek bedene uymaz ve çok eskidir, ancak her şeyi yapılandırmanıza gerek yoktur. Temel bilgileri doğru bir şekilde almak ve diğer ayarları yalnızca gerekirse değiştirmek daha iyidir. Çoğu durumda, yaklaşık 10 seçeneği doğru ayarlayarak sunucunun en yüksek performansının yüzde 95'ini elde edebilirsiniz. Bunun geçerli olmadığı birkaç durum, sizin koşullarınıza özgü uç durumlar olacaktır.

Çoğu durumda, sunucu "ayarlama" araçları, belirli durumlar için mantıklı olmayan yönergeler verme eğiliminde olduklarından önerilmez. Hatta bazılarının, önbellek isabet oranları ve bellek tüketimi formülleri gibi kendilerine kodlanmış tehlikeli ve yanlış tavsiyeleri bile vardır. Bunlar hiçbir zaman doğru olmadı ve zaman geçtikçe daha da az doğru hale geldi.

MySQL performans ipucu No. 7: Sayfalama sorgularına dikkat edin

Sayfalandıran uygulamalar, sunucuyu diz çöktürme eğilimindedir. Bir sonraki sayfaya gitmek için bir bağlantıyla size bir sonuç sayfası gösterirken, bu uygulamalar genellikle dizinleri kullanamayacak şekilde gruplandırır ve sıralar ve sunucunun çok fazla iş üretmesine neden olan bir LIMITve kullanırlar offset, sonra satırlar atılır.

Optimizasyonlar genellikle kullanıcı arayüzünün kendisinde bulunabilir. Sonuçlarda tam sayfa sayısını ve her sayfaya olan bağlantıları ayrı ayrı göstermek yerine, bir sonraki sayfaya bir bağlantı gösterebilirsiniz. İnsanların ilk sayfadan çok uzaktaki sayfalara gitmesini de engelleyebilirsiniz.

Sorgu tarafında, LIMITwith kullanmak yerine offsetihtiyacınız olandan bir satır daha seçebilir ve kullanıcı "sonraki sayfa" bağlantısını tıkladığında, bu son satırı bir sonraki sonuç kümesi için başlangıç ​​noktası olarak atayabilirsiniz. Örneğin, kullanıcı 101'den 120'ye kadar satırları olan bir sayfayı görüntülerse, 121 numaralı satırı da seçersiniz; sonraki sayfayı oluşturmak için, sunucudan 121'e eşit veya daha büyük satırlar için sorgu yaparsınız, sınır 21.

MySQL performans ipucu No. 8: İstatistikleri isteyerek kaydedin, isteksizce uyarın

İzleme ve uyarı önemlidir, ancak tipik izleme sistemine ne olur? Yanlış pozitifler göndermeye başlar ve sistem yöneticileri gürültüyü durdurmak için e-posta filtreleme kuralları belirler. Yakında izleme sisteminiz tamamen işe yaramaz hale gelir.

İzlemeyi iki şekilde düşünmeyi seviyorum: ölçümleri yakalama ve uyarı verme. Olası tüm ölçümleri yakalayıp kaydetmek çok önemlidir çünkü sistemde nelerin değiştiğini anlamaya çalışırken bunlara sahip olmaktan memnun olacaksınız. Bir gün, tuhaf bir sorun ortaya çıkacak ve bir grafiğe işaret etme ve sunucunun iş yükünde bir değişiklik gösterme yeteneğini seveceksiniz.

Aksine, çok fazla uyarı yapma eğilimi var. İnsanlar genellikle tampon isabet oranı veya saniyede oluşturulan geçici tablo sayısı gibi konularda uyarıda bulunur. Sorun, böyle bir oran için iyi bir eşik olmamasıdır. Doğru eşik yalnızca sunucudan sunucuya değil, iş yükünüz değiştikçe saatten saate de farklılık gösterir.

Sonuç olarak, tedbirli bir şekilde ve yalnızca kesin, eyleme geçirilebilir bir sorunu gösteren koşullarda uyarın. Düşük bir arabellek isabet oranı eyleme geçirilemez ve gerçek bir soruna işaret etmez, ancak bir bağlantı girişimine yanıt vermeyen bir sunucu çözülmesi gereken gerçek bir sorundur.

MySQL performans ipucu No. 9: İndekslemenin üç kuralını öğrenin

Dizin oluşturma, veritabanlarında muhtemelen en yanlış anlaşılan konudur çünkü dizinlerin nasıl çalıştığı ve sunucunun bunları nasıl kullandığı konusunda kafa karıştırmanın pek çok yolu vardır. Neler olduğunu gerçekten anlamak çok çaba gerektirir.

Dizinler, uygun şekilde tasarlandıklarında, bir veritabanı sunucusunda üç önemli amaca hizmet eder:

  1. Dizinler, sunucunun tek satırlar yerine bitişik satır gruplarını bulmasını sağlar. Pek çok insan, bir dizinin amacının tek tek satırları bulmak olduğunu düşünür, ancak tek satırları bulmak, yavaş olan rastgele disk işlemlerine yol açar. Sıraları teker teker bulmaktansa tümü veya çoğu ilginç olan satır grupları bulmak çok daha iyidir.
  2. Dizinler, sunucunun satırları istenen sırada okuyarak sıralamayı engellemesini sağlar. Sıralama maliyetlidir. Satırları istenen sırada okumak çok daha hızlıdır.
  3. Dizinler, sunucunun tüm sorguları yalnızca dizinden karşılamasını sağlayarak tabloya erişme ihtiyacını ortadan kaldırır. Bu, çeşitli şekillerde kapsama dizini veya yalnızca dizin sorgusu olarak bilinir.

Dizinlerinizi ve sorgularınızı bu üç fırsattan yararlanacak şekilde tasarlayabilirseniz, sorgularınızı birkaç kat daha hızlı yapabilirsiniz.

MySQL performans ipucu No. 10: Meslektaşlarınızın uzmanlığından yararlanın

Tek başına gitmeye çalışma. Bir problem üzerinde kafa yoruyorsanız ve size mantıklı ve mantıklı görünen şeyi yapıyorsanız, bu harika. Bu, 20'de yaklaşık 19 kez işe yarayacaktır. Diğer sefer, çok maliyetli ve zaman alıcı bir tavşan deliğinden aşağı ineceksiniz, çünkü tam da denediğiniz çözüm çok mantıklı görünüyor.

MySQL ile ilgili kaynaklardan oluşan bir ağ oluşturun - bu, araç setlerinin ve sorun giderme kılavuzlarının ötesine geçer. Posta listelerinde, forumlarda, Soru-Cevap web sitelerinde vb. Gizlenen son derece bilgili insanlar var. Konferanslar, ticari gösteriler ve yerel kullanıcı grubu etkinlikleri, size yardımcı olabilecek meslektaşlarla içgörü kazanmak ve ilişkiler kurmak için değerli fırsatlar sunar.

Bu ipuçlarını tamamlayacak araçlar arayanlar için, MySQL için Percona Yapılandırma Sihirbazı, MySQL için Percona Sorgu Danışmanı ve Percona İzleme Eklentilerine göz atabilirsiniz. (Not: Bu ilk iki bağlantıya erişmek için bir Percona hesabı oluşturmanız gerekir. Ücretsizdir.) Yapılandırma sihirbazı, yeni bir sunucu için, birlikte gönderilen örnek dosyalardan daha üstün bir temel my.cnf dosyası oluşturmanıza yardımcı olabilir. sunucu. Sorgu danışmanı, sayfalandırma sorguları (No. 7) gibi potansiyel olarak kötü kalıpları tespit etmeye yardımcı olmak için SQL'inizi analiz eder. Percona İzleme Eklentileri, isteyerek istatistikleri kaydetmenize ve isteksizce uyarı vermenize yardımcı olan bir dizi izleme ve grafik eklentisidir (No. 8). Tüm bu araçlar ücretsiz olarak temin edilebilir.