MySQL performans optimizasyonu ve önbellekleme parametreleri

Çok vakit ayırıpta uzun uzadıya güzel bir makale hazırlayamadım fakat benim ihtiyaç duyduğum çalışma ve bilgileri not etmek, teknik bilgisi olan kişilere de fayda sağlamak istedim.

*Performans testini  mysqltuner.codeplex.com adresindeki küçük exe ile yaptım. Bunun perl scriptini linux makinalar için bulabilirsiniz.

(windows) My.ini / My.conf (linux) dosyası örneğini ekliyorum. Bu benim plesk panel 11.5 windows server 2008 üzerinde kullandığım ini dosyasıdır.

[highlight]

[client]
port=3306
[MySQLD]
port=3306
basedir=C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL51\
datadir=C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\Data
tmpdir=C:\Program Files (x86)\Parallels\\Plesk\Databases\MySQL\Data
character-set-server=latin1
default-storage-engine=INNODB
tmp_table_size=64M
max_heap_table_size=64M
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=2M
key_buffer_size=2M
read_buffer_size=1M
read_rnd_buffer_size=256K
sort_buffer_size=256K
### innodb varsa asagidaki ayarlari aciniz
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=256M #Innodb kullanimi cok ise 384M, 1024M e kadar arttirilabilir
innodb_log_file_size=10M
innodb_thread_concurrency=8
max_connections=500 # cok yogun sitelerde bu deger arttirilabilir
max_allowed_packet=1M
sort_buffer=256K
net_buffer_length=4K
old_passwords=0
connect_timeout=20
key_buffer=64M
table_cache=1000 # cok fazla tablolu veritabanlarinda bu deger arttirilabilir
thread_cache=128
thread_cache_size = 128
query_cache_type = 1
query_cache_size=33554432
query_cache_limit = 4M
low_priority_updates=1
log_slow_queries=C:\\Program Files (x86)\\Parallels\\Plesk\Databases\MySQL\\LowQuery\\lq.log
long_query_time=10
log-queries-not-using-indexes

[/highlight]

[highlight]

thread_cache_size = 128
key_buffer = 256M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
table_cache = 1000  

[/highlight]

 

Cok fazla tablolu siteleriniz varsa table_cache = 1000 kismindaki sayiyi artirmanizi oneririm yada az ise azaltmanizi..

Bu ayarlari kendinize gore duzenleyebilirsiniz. Genelde her sunucu ayni ozelliklerde olmuyor. Eger ram ve sunucu ozelliginiz cok dusuk ise verilen limitleri dusurebilirsiniz. Ancak oncesinde sql cachede etkili olan kisimlarin aciklamasini yapmamiz gere, bundan sonrasinda mantikli sonuclar ile sunucunuza goze duzenleme yapabilirsiniz.

“key_buffer = 256M” kismi, Myisam index blokları icin kullanilir. Gelen istekler disk yerine hafizadan saglanir.

“query_cache_type = 1″ kismi, cache’i açtığımızı belirtir. 0 yapınca kapatmis oluyoruz, ancak biz suanda 1 yaparak bu ozelligi aciyoruz.

“query_cache_limit = 4M” kismi, cachelenecek maximum sorgu limitini belirler, 4mb’a kadar olan sorgulari cache alarak hafizasinda tutar.

“query_cache_size = 128M” kismida toplamda cachelenecek dosyalar icin sunucuda kullanilacak maximum alani ifade eder. Artirabilirsiniz, yada dusurebilirsiniz..
Query cache 80MB kullanmak istediğimizde 10*1024*1024 = 83886080

“table_cache = 1000″ kismi, tablolarin acilmasini ve okunmasini hizlandirmak icin daha once acilan tablolar icin cache olusturmasidir. Myisam tablo yapısında tum tablolar sorgu seklinde sunucuda tutulmaktadir, bunlarin acilip okunmasi gibi islemler icin cacheyi acmak bu sureyi daha hizlandiracagi icin faydasini gosterecektir..

Kullandiginiz sisteme gore inceleme yaparak my.cnf ayarlarini ozellestirebilirsiniz. Bunun icin onerecegim bir yolda hatali ve sorunlu sql sorgularini ogrenmektir. Bunun icin my.cnf dosyasina asagidaki kodlari ekleyerek verilen yolda toplanan hata loglarini inceleyip sorunlu sorgulari ogrenebilirsiniz:

[highlight]

log_slow_queries=/var/log/slow-queries.log
long_query_time=10
log-queries-not-using-indexes  

[/highlight]

[highlight]

set global query_cache_size=83886080;
show variables like ‘query%’;

[/highlight]

Sonrasinda sorunlu sorgular icin scriptlerinizi optimizasyonudan gecirebilirsiniz.

* Değerler(Variables)

from mysql;
show variables;

veya komut satırından:

mysqladmin variables

yazmamız yeterli.
* Süreç ve Durum Bilgileri (Process/ Status)

from mysql;
show status;

veya komut satırından:

Alıntı:
Alıntı
mysqladmin -i10 processlist extended-status

*Ayrıca yararlı bir kaç bilgilendirme komutu
> top
> ps -axfu
> vmstat 1
* MySQL Optimizasyonu
Bu bölümde artık değerleri nasıl bulacağımızı öğrendiğimize göre optimizasyonu hangi parametrede nasıl yapacağız ona geçebiliriz.
1 – En önemli 2 değer: table_cache ve key_buffer_size
eğer opened_tables büyükse, büyük ihtimalle table_cache değeriniz çok küçük tanımlanmıştır.
table_cache 64
open_tables 64
opened_tables 544468
MySQL çok anahtarlı ve çok gösterimli bir sistemdir. Çalışırken aynı anda bir çok işlemi yapmasından dolayı tablolar kapanmadan tekrar tekrar açılabilir. Bundan dolayı bu değerlerin eşitliği iyi ayarlanmalı. Değerlerin küçük olması sistemin sıkışmasına, büyük olması da belleğin gereksiz yere boş tutulmasına sebep olur. Yani sonuçta ikisi de performans kaybına sebep olur. Değerler sürekli takip edilip aktif değere yakın tanımlanmalıdır.
Eğer tablolarımızda çok fazla gösterimimiz yoksa open_tables , eğer çok gösterimimiz varsa opened_tables değeri üzerinde ayarlamalar yapmamız gereklidir. table_cahe boyutunun doğru şekilde ayarlanmasını bu iki değerler yüksek oranda sağlayabiliriz.
Eğer key_reads değeri büyükse, muhtemelen key_buffer_size değeri küçüktür. 
cache hesaplama yöntemi: key_reads/key_read_requests
key_buffer_size 16M
key_read_requests 2973620399
key_reads 8490571
cache zamanı = 0.0028
buradaki cache zaman değerinin(key_reads/key_read_requests) 0.01 – 0.5 ararsında olması her zaman için iyidir. Bu değerin üzerinde olursa mutlaka MySQL sunucunuzu az kurcalayın ve bu değeri tutturmaya çalışın. key_buffer_size doğrudan indexi arabelleğe alma hızını etkiler.
2- Diğer önemli değerler: wait_timeout , max_connection, thread_cache
Genellikle MySQL süreçlerine baktığınızda uyku(sleeping) modunda birçok süreç görürsünüz. Bunun başlıca sebebi wait_timeout‘tur. wait_timeout ‘un görevi başlayan mysql sürecini tanımlanan süre dolduğunda sonlandırmaktır. Bu süreci ne kadar iyi şekilde ayarlarsanız o denli rahat edersiniz. Benim önerim 10-15 saniye arasında ayarlamanızdır.
MySQL iyileştirmesine max_connection ile devam edelim. Bu değer özellikle ufak ölçekli çalışan SQL sunucularında önemli. Çünkü ufak sunucularda fazla kişi bağlanmaz. Bundan dolayı bu değerin yüksek tutulmasına gerek yoktur. Tabii hosting işi yapan kişilerin bunu düşük tutması da düşünülemez. Hosting sunucu sahipleri bu değeri 200-300 civarında tutulabilir.
Eğer threads_created değeri büyükse, thread_cache_size değerini büyültme ihtiyacı hissedebilirsiniz. Bu önbellek sürecini ayarlamak için yine bir hesaplama yöntemimiz var. Bu da threads_created/connections ‘dır.
Thread_cache_size 0
Threads_created 150022
Connections 150023
Bu arada fixlediğimiz ikinci önemli ayarda burada. Buradaki 0 değeri normaldir. Fakat yoğun çalışan sunucularda bu değeri 8’e kadar yükseltebilirsiniz.
Formül: table_cache = opened_table / max_used_connection
3- Bakmamız gereken diğer birkaç değer: tmp_tables_size ve handler_read_rnd / handler_read_rnd_next
Eğer created_tmp_disk_tables büyükse, verilerinizin disk üzerinde önbelleklenmeyip ram üzerinde belleklenmesi için tmp_table_size değerini arttırmanız gerekir.
Tmp_table_size 32M
Created_tmp_disk_tables 3227
Created_tmp_tables 159832
Created_tmp_files 4444
Created_tmp_disk_tables: MySQL sürecinde disk üzerinde aktif olarak çalışan kesin tablo önbellek sayısıdır.
Created_tmp_tables: created_tmp_disk_tables ile aynı olmakla beraber tek farkı önbellekleme disk üzerinde değil ram üzerinde yapılır.
Bu noktada kesin olan bir şey var ki MySQL süreçlerinizi ram üzerinde değilde disk üzerine yapılandırırsanız yanlış bir seçim yapmış olursunuz. Mutlaka ram kapasiteniz yettikçe ram’i tercih edin.
Eğer handler_read_rnd değeri büyükse, bu MySQL sunucunuzun bütün tabloları üzerinde çokça sorgular yapıldığını veya işlemlerde bazı tablolara bağlanıp işlem sonunda tablodan çıkış yapılmadığını ve tablonun hâlâ açık olduğunu gösterir.
handler_read_rnd 27712353
handler_read_rnd_next 283536234
Burada kullandığım değerler tamamen örnek amaçlıdır. Herkes kendi sunucusu için uygun değerleri ilk başlıkta anlattığım yöntemlerle öğrenebilirler. Eğer ssh ile arası olmayanlar varsa phpmyadmin üzerinden de öğrenebilirler.


Google ile gelen arama sonuçları:

  • mysql performans ayarları
  • nbellek iin linux da hngi parametreler kullanilir
  • önbellek boyutları
  • mysql disk yerine ram
  • mysql conf düzenleme r10
  • toplu saklama modu
  • lozan hezimet mi zafer mi deneme
  • innodb_flush_log_at_trx_commit nedir
  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1831 iBSTmxk7S3dbXt7DI1Ejj0hVXec-VXZR16S84B3t2Gbz1RUqotwOopEMSYKhYBgv 12a092334a471adf8886b705d1be9759e98e13c6&uuid=&state=_BLhILn4SxNIvvL0W45KSic66uCIg23qh8iRG98qeIXme
  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1825 a9xFpXWpcpqRFJiTIoA-jofP3iESapwh8jqW34bAfIIQhTCGCXnHSUTh_pgvYZhi b974da9d451fff66870cbf214a18cbbe10c9de42&uuid=&state=_BLhILn4SxNIvvL0W45KSic66uCIg23qh8iRG98qeIXme

4 thoughts on “MySQL performans optimizasyonu ve önbellekleme parametreleri

  1. Barış says:

    Çok faydalı bir makale, kutlarım.
    Biz firma olarak wamp2.4.4 ü kullanıyoruz. Daha önce win 7 üzerinden kullandığım DB yi server 2012 r2 ye yükledikten sonra aynı anda 2-3 kullanıcı girmesi halinde yanıt vermiyor(herhangi bir uyarı vermiyor). Sorunun çözümü için hangi dosyadaki değerleri değiştirmem gerekli ?

  2. @Barış merhabalar, öncelikle hangi kaynağınızın tükendiğini tespit etmelisiniz 2-3 kullanıcıyı sisteme tekrar bağlayıp o sırada izleyerek, cpu dan mı ram den mi yavaşlama oluyor bunu tespit etmelisiniz.
    Ondan sonra yukarıda bahsettiğim mysqltuner.codeplex.com yazılımı ile analiz yapabilirsiniz.

    Yukarıda anlatılanları detaylıca inceleyerek istediğiniz gibi optimize edebilirsiniz

  3. Selim says:

    ellerine sağlık hocam,

    aynı şekilde sizden apache içinde bir optimizasyon çalışması bekliyoruz 🙂

    Tekrar teşekkürler

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir