MySQL Optimizasyon Dersi [Geniş Anlatım]

Ece · 1 04-07-2008, 15:36:19

Merhabalar,
Bugün size ilk dersimi yazıyorum. Bu derste ne yapayım diye çok düşündüm aklıma bu konuda en çok ihtiyaç duyulan ve gerekli olan mysql optimizasyonu geldi.
Dersimizin içeriğinde ilk önce optimizasyonda gerekli olan bilgileri yazacağım. Daha sonra da size örnek bir my.cnf dosyası nasıl olmalı onu göstereceğim.
Ayar dosyamızın yeri ve adı: [root@ece ~] /etc/my.cnf
bu dosyayı nasıl düzenleyeceğiz: [root@ece ~] nano /etc/my.cnf
Derse başlamadan önce öneriler:
* my.cnf dosyanızın mutlaka bir yedeğini alın. Eğer dosyayı bozarsanız son çalışan hali lazım olacaktır.
* Emin olmadığınız değişiklikleri yapmayın.
* TÜM SORUMLULUK SİZE AİTTİR. BEN SADECE BİLGİMİ PAYLAŞIYORUM.
Önbilgi ve uyarılardan sonra dersimize geçebiliriz.
MySQL optimizasyonu net üzerinde çokça konuşuldu, çok yerde örnekler verildi fakat malesef ben hiç bu optimizasyonun nasıl yapıldığının anlatıldığı bir derse denk gelmedim. Onun için şimdi burada en önemli parametrelerden başlayarak bunları öğreneceğiz. Böylelikle sunucumuzdaki mysql yükünü hafifletecek ve Ram kullanımımızı düşüreceğiz.
MySQL ile ilgili bir çok parametre mevcut fakat biz öncelikle en önemlilerle başlayalım.
En yaygın kullanılan parametreler:

Alıntı:

- max_connections
- wait_timeout
- thread_cache_size
- table_cache
- key_buffer_size
- query_cache_size
- tmp_table_size

Bize gerekli olan değerleri nasıl öğreneceğiz?:
* Değerler(Variables)

Alıntı:

from mysql;
show variables;

veya komut satırından:

Alıntı:

mysqladmin variables

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

Alıntı:

from mysql;
show status;

veya komut satırından:

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.
MySQL ile ilgili birkaç önemli hesaplama yöntemi:
S. MySQL için ne kadar Ram’e ihtiyacım var ?
C. Bunu hesaplamak için, key_buffer + max_connections * ( join_buffer + record_buffer + sorf_buffer + thread_stack + tmp_tables_size ) formülünü kullanabilirsiniz. Bağlantı sayınız arttıkça bu değerler artar ve dolayısıyla ram kullanımızda artmış olur.
S. Bu değerleri kafama göre değiştirip tutturabilir miyim?
C. Deneme yanılma ile gerçek optimizasyonu yapmak çok zordur. Amiyane tabirle çok çok ballı olmanız gerekir. Bunu da bence kimse göze almasın. Peki nasıl yapmalıyım diyebilirsiniz. Ona da cevap verelim.
Optimizasyon yapmaya karar verdiğiniz ilk gün her saat başı mysql sunucunuzdaki değerlerin sonuçlarını not edin(eğer phpmyadminden bakıyorsanız kırmızı değerleri not etmeniz yeterlidir.). İlk gün yaptığınız kayıtları inceleyip ortalama değerleri tespit edince ikinci gün bu değerlere göre sunucunuzu ayarlayın. Ayarlamayı yapınca gün boyu sunucuyu takip edin ve değerlerde tekrar yükseklikler görürseniz tekrar ayar yapın. Ne zaman buradaki değerler çok yüksek çıkmazsa ayarınız tamam demektir. Fakat bu durumda aklınıza “Optimizasyon yaptım işim bitti artık sunucuya bir daha optimizasyon yapmama gerek yok.” Diye bir düşünce gelmesin. Çünkü sunucudaki siteler sürekli geliştiği için sizin buradaki değerlerinizde değişir. Sürekli takip etmeniz gereklidir.
Önemli Öneri: MySQL sunucunuz orta yoğunluk ve üzerinde çalışacaksa kesinlikle en az 2 GB RAM kapasiteniz olsun. En iyi performansı o zaman almaya başlarsınız. Yoksa 1 GB ve altında çok zorluklar çekersiniz.
Sunucu sahibi,Kodlamacı ve Site sahiplerine de önerilerim var onları da yazıp birkaç örnek MySQL conf dosyası yazıp dersi bitireceğim.
Öneriler:
Sunucu sahipleri;
- MySQL süreçlerini MyTOP ile sürekli takip edin. Herhangi bir süreçten şüphelendiğinizde mutlaka müdahale edin. Ertelemeyin…
- Slow query log sistemini ve mysqldumpslow komutunu ciddi problem tespitlerinde kullanabilirsiniz. Fakat bu logları belirli aralıklarla temizlemezseniz disk kapasitenizi doldurabilirler. Bunu mutlaka takip ederek bu özellikleri kullanın.
- Sunucudaki tabloları sürekli onarma(repair),kontrol(check) ve optimize özellikleriyle elden geçirin.
- Query_cache özelliğini aktif ettiğinizden emin olun. Önbellekleme(cache) sunucunuzun rahatlamasında çok önemli.
Kodlamacılar ;
- MySQL indexlerine mutlaka dikkat etsin.
- sayfa sonunda mysql_close kullansınlar
- verileri çekerken SELECT * şeklinde değil de, SELECT id, name, title gibi gerekli olanları çağırsınlar.
- mysql dışında kalsa da veri çekme sürecinde mysql'i etkileyen bir durum olan php_include komutunu mümkün olduğu kadar az kullansınlar.
Site Sahipleri;
- Bu işlere merakınız vardır ve kodlamacınıza yazdırdığınız veya hazır kurduğunuz scripti kurcalarken mutlaka o dosyasının sağlam bir yedeğini saklayın. Bozarsanız tekrar geri yükleyin.
- Ya da eğer çok bilginiz yoksa scripti bozmamak için kurcalamayın.
ÖRNEK MySQL Conf dosyası:
Aşağıdaki örne MySQL ayar dosyası(configuration) için genel bir örnek yazdım. Bu ayar dosyasındaki x'lerin yerine siz kendi değerlerinizi yazacaksınız. Bazı değerleri ben yazdım. Onlar genelde değişmeyen değerlerdir. İhtiyacınıza göre gerekirse değiştirebilirsiniz.

Alıntı:

[mysqld]
datadir=/var/lib/mysql
skip-locking
skip-innodb
safe-show-database
query_cache_limit=xM
query_cache_size=xxxM ## 1 GB Ram için 32MB
query_cache_type=1
max_user_connections=200
max_connections=300
interactive_timeout=10
wait_timeout=15
connect_timeout=20
thread_cache_size=xxx
key_buffer=xxxxM ## 1 GB Ram için 128MB
join_buffer=xM
max_connect_errors=15
max_allowed_packet=xxM
table_cache=xxxx
record_buffer=xM
sort_buffer_size=xM ## 1 GB Ram için 1MB
read_buffer_size=xM ## 1 GB Ram için 1MB
read_rnd_buffer_size=xM ## 1 GB Ram için 1MB
thread_concurrency=x ## Çekirdek sayısı x 2
myisam_sort_buffer_size=xxM
[mysql.server]
user=mysql
basedir=/var/lib

[mysqldump]
quick
max_allowed_packet=xxM
[mysql]
no-auto-rehash

[isamchk]
key_buffer=xxM
sort_buffer=xxM
read_buffer=xxM
write_buffer=xxM

[myisamchk]
key_buffer=xxM
sort_buffer=xxM
read_buffer=xxM
write_buffer=xxM
[mysqlhotcopy]
interactive-timeout

Dipnot: Takıldığınız ve anlayamadığınız yerlerde sorularınızı bu konu altına yazabilirsiniz. Vakit buldukça cevaplamaya çalışırım.
Umarım işinize yarar. Kolay gelsin.