Bu yazı beginner seviyesinde acemiler için mysql optimizasyon hakkında temel bilgiler vermek için yazılmıştır.
Sunucu performansını etkileyen en önemli kriterin başında Mysql optimizasyonu geliyor. İşte bu sebebten ötürü mysql
kendi içinde 450’den fazla parametre ile sunucumuza özgü ayarlar yapma olanağını bizlere sunuyor.
Bu parametreleri kendimize özgü şekilde dogru ayarlarsak mysql performansını oldukça artırabiliriyoruz.
Mysql 5.5’den önce bu parametrelerin çok büyük bir bölümüne müdahale etmemize izin vermezken
Mysql 5.5+’den sonra parametrelere müdahaleyi serbest bıraktı.
Bu sebepten ötürü sunucunuzdaki mysql versiyonu en az 5.5+ olması çok iyi olur.
Bu kısa bilgiden sonra gelelim konumuza.
Bu 450 parametre içindeki en önemli ve temel 6-7 parametreyi dogru ayarlayabilirsek ayarlama kısmının büyük bir bölümünü halletmiş oluyoruz.
Alet Çantamızda olması gerekenler 2 ücretsiz program:
1- Putty
2- WinSCP
Önce root olarak sunucumuza putty ile bağlanalım. ve sunumuzdaki Ram miktarını tam olarak ögrenelim:
Komutumuz şu:
Kod:
free -m
MYSQL Optimizasyonun temeli RAM üzerine kuruluyor. Sunucumuzdaki Ram miktarına göre ayar yapıyoruz
Şimdi mysql configrasyon dosyamızın yerini bulalım. Varsayılan olarak /etc/my.cnf adresindedir ama yinede tam yerini bulmak için
şu komutu çalıştıralım:
Kod:
mysql –help | grep Default -A 1
Yerini bulduktan sonra WinSCP programı ile ilgili dizine gidip my.cnf dosyasını düzenlemeye başlıyoruz.
Önce yedek alalım
Daha iyi bir performans için DB yapınızın InnoDB yapıyı tercih edebilirsiniz.
Bilinmesi gerekenler:
- my.cnf dosyasındaki M=Megabyte G=Gigabyte K= Kbyte ifade eder.
- my.cnf dosyasındaki degişiklik sonrası” MySql server startup error ‘The server quit without updating PID file ‘” şeklinde hata alırsanız mysql versiyonlar arasında bazı parametrelerin yazımını degiştirdi.
Yazımı degişen parametreyi güncellerseniz sorun kalmaz parametre adını hatalı yazarsanızda mysql çalışmaz.
örnegin mysql versiyonunuz 5.1 ve siz nette bir yerde bulduğunuz config dosyasını kendi config dosyanıza kopyaladınız table_open_cache olarak geçen parametre yüzünden mysql çalışmaz. table_open_cache yazan parametreyi table_cache yaparsanız sorun çözülür mysql start çalışır. - my.cnf dosyanız bomboş olabilir.Bu durumda mysql default parametre degerlerini kabul eder yada NULL olarak işlem yapar.
- my.cnf dosyası içine yazacağınız parametre degerlerine göre işlem yapar ve öncelik verir.
- my.cnf dosyasındaki yeni parametre degerlerini mysql’in tanıması için restart mysql service yapmanız gerekmektedir.
- my.cnf dosyamızdaki aşağıdaki parametreler yoksa [mysqld] satırının altına bir yere ekleyelim.
Aşağıdaki mysql parametreleri zaten varsa yazıyı okumaya devam:
–INNODB—
max_connections=150 olarak default tanımlı geliyor. Ve 1 GB ram için 150 kişi öngörmüş mysql.
RAM (Gbyte olarak) x 150 olarak hesaplayabilirsiniz. Mesela 2GB ram bellek için 300 ideal rakamdır.
Rakamı abartı büyütmek gereksiz RAM tüketimi ve yavaş sql sorgusu olarak bize döner.
thread_cache_size: Optimum Formülü = 8 + (max_connections / 100). Bizim örnegimiz için 8+ (300/100) = 12
table_cache: Bunu hesaplamak için şu komutu çalıştırın:
mysql -u root -p ile giriş yapıp
SELECT COLUMN_NAME, TABLE_NAME,table_schema
FROM INFORMATION_SCHEMA.COLUMNS;
Toplam tablo sayısını elde edelim ve 100 tablo için 2M hesaplanıyor.
örnegin 453 tablo çıkarsa 8M yada 10M olarak ayarlamak yeterlidir. Daha büyük rakam vermek performans üzerinde bir etkisi bulunmuyor.
tmp_table_size: Bazı karmaşık SQL sorguların sonuçları RAM bellek üzerinde tutulur ve performansı dogrudan etkiliyor. Bu parametre ayarlı degilse yada az bir deger olarak ayarlı ise SQL sonucu disk üzerine yazılır.
Diskinizin yazma/okuma performansı olarak size döner.
Ancak session bazlı işliyor. Ayar yaparken bunu gözönünde bulundurun. Ne demek bu ki?
Session bazı parametreler= Tehlikeli parametredir ve dogru ayarlanmalıdır. max_connection rakamını çarpan olarak kullanan parametrelerdir.
max_connection=300 ayarlıysa
tmp_table_size=100M ayarladınız diyelim
300 x 100M =30000MB kadar bir belleği bu işe ayırmışsınız demek oluyor. Ram belleğinizin 30GB’ı bu ayar için SET edilmiş oldu.
Sunucunuzda 8gb RAM bellek varsa dogrudan CPU fırlar sunucu kilitlenir.
max_heap_table_size ile tmp_table_size aynı rakamları vermek uygun olur.
innodb_buffer_pool_size= Bu kesinlikle en önemli innodb parametresidir. Makinenin fiziksel bellek boyutunun %60 en ideal rakamdır. %80’i geçerse sıkıntı çıkıyor
InnoDB Performans icin en onemli parametredir. 2GB ram bellek için 1300M olarak ayarlayalım. Daha fazla ayarlarsak sunucudaki diger uygulama servislerine (apache, cpanel vs) ram kalmaz.
Daha düşük rakam verirsek mysql performansı alınamaz. 180beygir aracımız var ama biz 20beygir kullanalım demek gibi bişey
innodb_buffer_pool_instances: Bu parametre innodb ikinci en önemli parametredir. Bilgisayarımızdaki Anakart üzerindeki RAM bellek slot sayısı olarak düşünülebilir.
16GB Tek parça ram bellek yerine 4 Tane eş zamanlı 4GB ram bellek daha performanslı çalışır mantığı ile düşünebiliriz. innodb_buffer_pool_size eğer 1GB’den büyükse
işe yarıyor. ve çift sayı yapılması gerekir.
Kod:
scpu
şu komut ile sunucunuzdaki CPU sayısını ögrenebilirsiniz.
innodb_buffer_pool_instances= (innodb_buffer_pool_size in GB + number of CPUs)/2 formülü ile bulabilirsiniz.
örneğin 1.3GB innodb_buffer_pool_size ayarladık ve 8 çekirdekli işlemcimiz varsa (1,3+8)/2= 4 olarak ayarlamak gerekli.
innodb_log_file_size : Büyük veritabanlarında yazma işlemi yoğun ise çok önemli bir parametredir.
64M-512M arasında sunucu belleğine göre bir değer belirlenebilir ve performansı dogrudan etkiler.
innodb-file-per-table: Varsayılan olarak kapalı geliyor. 1 olarak verelim. Bu parametrenin peformansa dönüşmesi için bir kaç işlem yapmamız gerekiyor. Veribanını
yedekleyip. mysql’i kapatıp tek parça olan idb dosyasını silip. mysql’i çalıştırıp tekrar restore yapmak gerekli.
Bu işlemden sonra innodb her tabloyu ayrı bir ibdata dosyasına dönüştürerek performansda çok ciddi şekilde artış yaşanıyor.
Orta büyüklükte bir veritabanında 10gb dosyayı her seferinde açıp yazmanın
getirecegi yük ile her tablo için çok daha küçük boyutlara indirgenmiş dosyalara yazma okuma performansını göz önüne getirebilirsiniz.
innodb_flush_method : varsayılan olarak boş geliyor. onu O_DIRECT olarak degiştirelim. Disk üzerindeki okuma yazma peformansını verimli hale getiriyor.
innodb_write_io_threads :Varsayılan olarak 4 geliyor. innodb_buffer_pool_instances ile aynı rakamı ayarlayabiliriz 4’den küçük ise aynı kalsın hesaplamamıza göre.
innodb_read_io_threads :Varsayılan olarak 4 geliyor. innodb_buffer_pool_instances ile aynı rakamıayarlayabiliriz 4’den küçük ise aynı kalsın hesaplamamıza göre.
slow_query_log = 1 Varsayılan olarak kapalı gelir. Yavas sorgularin tespiti icin aktif edersek iyi olur. Darbogaza giren SQL sorgusunu tespit ettikten sonra silebiliriz.
long_query_time = 5 Açıklaması 5 sn’den uzun süren sorguları slow query kabul ediyoruz.
slow-query-log-file = /var/log/mysql/slow.log log dosyalarını yazılacağı dizin
—MYISAM AYARLARI—
Benim veritabanım myisam diyenler içinde en önemli parametreler bunlardır.
Bu rakamları artırmak performansınız coşturur ama aşırı büyük rakamlar sunucunuzu kilitleyebilir yada mysql LIKE ve COUNT
degerlerini boş dönrürür.
key_buffer_size= Bu myisam tablo yapısı için en önemli performans parametresidir. Bu rakam Ram miktarının %25 degerini kesinlikle geçmemeli. Örnegimiz için 2GB ram olan bir sunucuda 512M üst limit degeridir.
sort_buffer_size= Bu rakam key_buffer_size / 32 Örnegimiz için 512M üst limitli key_buffer_Size için 16M üst limit degeridir.
read_buffer_size = sort_buffer_size /16 ideal üst limit. yani örnegimiz için 1M
write_buffer_size = sort_buffer_size /16 ideal üst limit. yani örnegimiz için 1M
max_connections= Yukarıdaki parametrelere bağlı aşağıdaki formül ile bulunuyor.
Gerekli Optimizasyon Hesaplama Formülü:
max_connections= (RAM-key_buffer_size)/ (sort_buffer_size + read_buffer_size )
Çözümlü bir soru yapalım: 4GB ram belleğim var sunucumda en performanslı rakamları nasıl ayarlama yapmam gerekli?
Cevap: 2GB ram bellek için 150 max_connections ideal bir rakamdır. Ama makalenin başında 2GB ram için 300 max_connections yazmışsınız diyebilirsiniz. Bunun sebebi diger parametrelerin varsayılan şekilde kullanırsak şöyle ki mysql default olarak key_buffer_size=64M seçiyor. Default degerlerden daha iyi performans almak için optimizasyon yapıyoruz zaten. Amacımız default degerlerin dışına çıkıp sunucumuza özel mysql optimizasyon yapmak.
4GB ram bellek (4096M) için max_connections= 300 rakamını baz alarak optimimum degerlerimizi hesaplamaya çalışalım.
key_buffer_size= 512M seçtik.
sort_buffer_size= 16M
read_buffer_size=1M
max_connections = (4096-512)/(16+1)
max_connections= 210
Seçim yaptığım rakamlara göre anlık 210 kişiye kadar mysql too_many_connection hatası vermez ama 260 kişi bağlandığında mysql kilitlenir. key_buffer_size myisam için global performans parametresi olduğu için onu pek düşürmek istemiyorum. sort_buffer_size ve read_buffer_size session level çarpanı olduğu için biraz düşürmem gerekli olduğunu görüyorum.
key_buffer_size= 512M seçtik.
sort_buffer_size= 10M
read_buffer_size=1M
max_connections = (4096-512)/(10+1)
max_connections = 310
Seçim yaptığımız rakamlar bu sunucu için ideal rakamlardır. Benim siteme bu kadar kişi girmiyor 300 max_connection bana fazla derseniz benim sitem daha hızlı açılsın derseniz key_buffer_size rakamını yükseltin ve sort_buffer_size= 16M seçebilirsiniz.
Burada kararı siz vereceksiniz.
Aklımızda Bulunsun
sort_buffer_size
join_buffer_size
read_buffer_size
Bu 3 parametre SQL sorgusu (ORDER BY) parametreleridir. Session bazlı RAM tüketir.
Genel olarak gerekli olandan daha büyük olarak ayarlamak, ORDER BY yapan sorguların çoğunu çok ciddi olarak yavaşlatır ve bellek kilitlenmesine yol açıyor. Daha büyük degerler daha hızlı sql sonucu döndürmez.
SELECT * FROM table ORDER BY username
yaptığınızda veritabanınızda 1000 kişi varsa bu en fazla hafızada 10kb yer kaplar.
10.000 kişi varsa 100KB
100.000 kişi varsa 1mb yer yaplar.
Rakam verirken şu degerleri göz önünde bulundurun.
Mysql default olarak 1GB Ram için default degerler:
join_buffer_size= 0,128M
read_buffer_size=0,128M
sort_buffer_size = 2M
Bir çok sunucu için bu rakamları ideal kabul ediliyor mysql tarafından.
Bizim sunucumuz 4GB ram ise bu rakamları 4 ile çarpalım
join_buffer_size= 512K
read_buffer_size=512K
sort_buffer_size = 8M
Biraz performans alalım dersek çarpan olaran 2x-3x rakamlarını geçmeyin yazıyor kitaplarda.
join_buffer_size= 1M
read_buffer_size=1M
sort_buffer_size = 16M
thread_stack varsayılan miktarı 196K olarak ayarlıdır ve bu çoğu site için yeterlidir. Şu tarz bir hata alırsanız
Thread stack overrun:
Use ‘mysqld -O thread_stack=#’ to specify a bigger stack.
thread_stack=256K my.cnf dosyanıza eklerseniz veritabanınızın boyutu çok çok büyüyene kadar bir hata almazsınız.
