Sunucudaki (Hosting/LAMP vb…) farketmeksizin mysql kullanan tüm sistemleri Optimize etmek için düzenlemeniz gereken dosya “my.cnf” dosyasıdır /etc/my.cnf dizininde bulunur. Yaptığınız ayarlarla mysql in şişmesinin saldırılarda mysql kaynaklı düşmelerin önüne geçebilir, performans arttırabilirsiniz.
Düzenlemeye geçelim,
1
$ nano /etc/my.cnf
/etc/my.cnf dosyasını nano editörü ile açıyoruz ardından verdiğim hazır cnf leri veya makaleden aldığınız bilgiyle kendinize özel ayarlarla güncelleyebilirsiniz.
Small/Küçük (500MB – 1GB – 2GB) sunucular için hazır my.cnf;
[mysqld]
performance_schema=off
skip-networking
innodb_file_per_table=1
tmpdir=/tmp
max_connections=200
max_user_connections=200
key_buffer_size=256M
myisam_sort_buffer_size=128M
join_buffer_size=1M
read_buffer_size=128K
sort_buffer_size=128K
table_open_cache=3072
thread_cache_size=1024
table_definition_cache=2048
open_files_limit=52000
wait_timeout=120
connect_timeout=120
tmp_table_size=128M
connect_timeout=120
tmp_table_size=128M
max_heap_table_size=128M
max_allowed_packet=268435456
query_cache_limit=4M
query_cache_size=128M
query_cache_type=1
interactive_timeout=120
max_connect_errors=15
local-infile=0
innodb_buffer_pool_size=128M
innodb_additional_mem_pool_size=64M
innodb_thread_concurrency=4
innodb_flush_method=O_DIRECT
default-storage-engine=MyISAM
[isamchk]
key_buffer=512M
sort_buffer_size=2M
read_buffer=2M
write_buffer=2M
[myisamchk]
tmpdir=/tmp
key_buffer=512M
sort_buffer=2M
read_buffer=2M
write_buffer=2M
Middle/Orta seviviye ve yüksek (4GB,8GB…) sunucular için: https://gist.github.com/ertugrulturan/1ad2f954b16d9d042cb6bca27f733c2b
Peki bu parametreler ne işe yarıyor?
sync_binlog = ikili günlüğe yapılan tüm yazmaların derhal olmasını sağlar
back_log = MySQL bağlantı yöneticisi iş parçacığı işlenmeden önce işletim sisteminin dinleme kuyruğunda tutabileceği bağlantıların sayısını belirtir. Çok yüksek bir bağlantı oranına sahipseniz ve “bağlantı reddedildi” hatası yaşarsanız, bu değeri artırmanız gerekebilir. Bu parametrenin maksimum değeri için işletim sisteminizin belgelerine bakın. Back_log’u işletim sistemi sınırından daha yüksek olarak ayarlamaya çalışmanın hiçbir etkisi olmaz.
max_connections = değeri 1 gb ram için varsayılan 150 değeridir. Ram oranınıza göre connections değerini değiştirebilirsiniz. Ancak çoğu durumda düşük sayılar ile mysqlin çok fazla bağlantı ile şişmesini önlemek için değerleri daha düşük tutarız
max_connect_errors = MySQL’in neden max_connect_errors denetimini devre dışı bırakmadığını bilmiyorum, ancak bunu yapmıyorsa, özellikle de bir yük dengeleyici kullanıyorsanız, MySQL’in renksiz bir sunucudaki bağlantıları kabul etmeyi reddetmesini engellemek için yüksek bir değere ayarlayın!
table_cache = Tüm iş parçacıkları için açık tabloların sayısı, mysqld_safe bölümünde açık dosya sınırının en az iki katı olduğundan emin olun
max_allowed_packet = Sunucunun üstesinden gelebilecek bir sorgu paketinin maksimum boyutu.
binlog_cache_size = İşlem sırasında ikili günlük için SQL deyimlerini tutacak önbellek boyutu. Sık sık büyük, çok beyanlı işlemler kullanırsanız, daha fazla performans elde etmek için bu değeri artırabilirsiniz. İşlemlerden gelen tüm ifadeler ikili günlük önbelleğinde arabelleğe alınır ve COMMIT’den hemen sonra ikili günlüğe yazılır. İşlem bu değerden büyükse, bunun yerine diskte geçici dosya kullanılır. Bu arabellek, işlemdeki ilk güncelleme deyimine bağlantı başına ayrılır
max_heap_table_size = Tek bir HEAP (bellekte) tabloda izin verilen maksimum boyut. Bu seçenek, aksi halde tüm bellek kaynaklarını tüketen çok büyük bir HEAP tablosunun yanlışlıkla oluşturulmasına karşı koruma sağlar.
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 (64M).
sort_buffer_size = Sıralama arabelleği, bazı ORDER BY ve GROUP BY sorgularını sıralamak için kullanılır. Sıralama verileri sıralama arabelleğine uymuyorsa, bunun yerine bir disk tabanlı birleştirme sırası kullanılır – “Sort_merge_passes” durum değişkenine bakın. Sıralama gerekiyorsa iplik başına tahsis edilir.
join_buffer_size = Bu arabellek, tam JOIN’lerin optimizasyonu için kullanılır (dizin içermeyen JOIN’ler). Bu gibi JOIN’ler çoğu durumda performans için çok kötüdür, ancak bu değişkeni büyük bir değere ayarlamak performans etkisini azaltır. Tam JOIN’lerin sayısı için “Select_full_join” durum değişkenine bakın.
thread_cache_size = Kaç tane iş parçacığı, bir yeniden önbellekte saklanmalıyız. Bir istemci bağlantısı kesildiğinde, istemcinin iş parçacığı öncekinden çok fazla iş parçacığı_ekliğinden farklı değilse önbelleğe konur. Bu, çok sayıda yeni bağlantınız varsa, gereken iplik oluşturma sayısını büyük ölçüde azaltır. (Normalde, iyi bir iplik uygulamanız varsa, bu önemli bir performans gelişimi sağlamaz.)
query_cache_size = Sorgu önbellek, SELECT sonuçlarını önbelleğe almak ve daha sonra aynı sorguyu bir kez daha gerçekleştirmeden tekrar iade etmek için kullanılır. Sorgu önbelleğinin etkinleştirilmesi, benzersiz sorgulara ve seyrek olarak değişen tablolara sahip olmanız durumunda önemli ölçüde hızlanma geliştirmesine neden olabilir. Mevcut değerinizin yükünüz için yeterince yüksek olup olmadığını kontrol etmek için durum değişkeni “Qcache_lowmem_prunes” e bakın. Not: Tablolarınızın çok sık değiştiği veya sorgularınızın her seferinde metinden farklı olması durumunda, sorgu önbellek performans iyileştirmesi yerine yavaşlamaya neden olabilir.
query_cache_limit = Yalnızca bu sınırdan küçük sonuç kümeleri önbellekle. Bu, diğer tüm sorgu sonuçlarının üzerine yazılmış çok büyük sonuç kümesinin sorgu önbelleğini korumak içindir.
query_alloc_block_size = query_alloc_block_size, SQL deyimlerini ve diğer önemsiz bellekleri ayrıştırmak için ne kadar bellek ayırdığını denetler. Olası bellek parçalanmasını azaltmak için karmaşık sorgular çalıştıran kutularda artırıyorum. YMMV varsayılanı 8 k
default_table_type = CREATE TABLE deyimi sırasında farklı şekilde belirtilmemişse, yeni tablolar oluştururken varsayılan olarak kullanılan tablo türü.
thread_stack = Kullanılacak iplik yığını boyutu. Bu hafıza miktarı her zaman bağlantı zamanında saklıdır. Kendi yığın aç UDF işlevlerini kullanırsanız veya işletim sisteminiz bazı işlemler için daha fazla yığın gerektiriyorsa, MySQL’in kendisi genellikle 64 K’den fazla belleğe ihtiyaç duymaz, bunu daha yüksek bir değere ayarlamanız gerekebilir.
long_query_time = Bu süreyi aşan (saniyeler içinde) tüm sorgular yavaş çalışılacaktır. Bir değer olarak “1” i kullanmayın, çünkü bu, zaman zaman çok hızlı sorguların güncellenmesine neden olacaktır (şu an MySQL yalnızca şu an ikinci doğrulukla zamanı ölçmektedir).
key_buffer_size = MyISAM tablolar için dizin bloklarını önbelleğe almak için kullanılan Anahtar Arabelleğinin boyutu. Bazı bellek OS tarafından önbellek satırları için de gerektiği için, mevcut belleğinizin% 30’undan daha büyük bir değere ayarlanmayın. MyISAM tablolarını kullanmasanız bile dahili geçici disk tabloları için de kullanılacağı için 8-64M olarak ayarlamanız gerekir.
read_buffer_size = MyISAM tablolarının tam tablo taramaları için kullanılan arabellek boyutu. Tam tarama gerekiyorsa, iplik başına ayrılır.
read_rnd_buffer_size = Bir sıralama sonrasında sıralanmış sıralarla satır okurken, disk aramalarından kaçınmak için satırlar bu arabelleğe okunur. Bunu yüksek bir değere ayarlarsanız, ORDER BY performansını çok artırabilirsiniz. Her iş parçacığı için gerektiğinde tahsis edilir.
bulk_insert_buffer_size = MyISAM toplu eklemeler yapmak için (yani, INSERT … SELECT, INSERT … VALUES (…), (…), … ve LOAD DATA INFILE) özel ağaç benzeri önbellek kullanır. Bu değişken, önbellek ağacının boyutunu iş parçacığı başına bayt olarak sınırlar. 0 olarak ayarlamak bu optimizasyonu devre dışı bırakır. En iyi performans için “key_buffer_size” değerinden büyük ayarlamayın. Bu arabellek, bir toplu ek algılandığında tahsis edilir.
myisam_sort_buffer_size = Bu arabellek, MySQL’in, TAMİR, OPTİMİZE, ALTER tablo tablolarında ve YÜK VERİLERİNDEN KAYNAKLARDA boş bir tabloda dizini yeniden oluşturması gerektiğinde tahsis edilir. Her iş parçacığı için ayrılır, bu nedenle büyük ayarlara dikkat edin.
myisam_max_sort_file_size = MySQL’in geçici dosyanın maksimum boyutu, dizini yeniden oluştururken (REPAIR, ALTER TABLE veya LOAD DATA INFILE sırasında kullanmaya izin verir.) Dosya boyutu bundan daha büyük olursa, dizin anahtar önbelleği üzerinden oluşturulur Yavaş).
myisam_max_extra_sort_file_size = Hızlı dizin oluşturma için kullanılan geçici dosya, burada belirtilen miktara göre anahtar önbelleğini kullanmaktan daha büyük olursa, anahtar önbellek yöntemini tercih edin. Bu, ağırlıklı olarak büyük tablolardaki uzun karakter anahtarlarını yavaş anahtar önbellek yöntemini kullanarak dizin oluşturmak için zorlar.
myisam_repair_threads = Bir tabloda birden fazla dizin varsa, MyISAM bunları paralel sıralamayla onarmak için birden fazla iş parçacığı kullanabilir. Birden çok işlemciye ve bol miktarda belleğe sahipseniz bu mantıklıdır.
innodb_additional_mem_pool_size = InnoDB tarafından meta veri bilgisini depolamak için kullanılan ek bellek havuzu. InnoDB, bu amaçla daha fazla bellek gerektiriyorsa, işletim sisteminden ayrılmaya başlayacaktır. Bu, en yeni işletim sistemlerinde yeterince hızlı olduğundan normalde bu değeri değiştirmeniz gerekmez. SHOW INNODB STATUS kullanılan geçerli miktarı görüntüler.
innodb_buffer_pool_size = Bu yapılandırma dosyası en az 8G’lık bir ana bellek varsayar
innodb_thread_concurrency = InnoDB çekirdeğinde izin verilen iş parçacığı sayısı. En iyi değer, uygulamaya, donanıma ve OS zamanlayıcı özelliklerine bağlıdır. Çok yüksek bir değer, iplik parçalanmasına neden olabilir.
innodb_flush_log_at_trx_commit = 1 olarak ayarlanırsa, InnoDB işlem günlüğünü ACID davranışı sunan her bir taahhütteki diske boşaltacaktır (fsync). Bu güvenliği tehlikeye atmaya hazır iseniz ve küçük işlemler yürütüyorsanız, disk G / Ç’yi günlüklere azaltmak için bunu 0 veya 2 olarak ayarlayabilirsiniz. Değer 0, günlüğün yalnızca günlük dosyasına yazıldığı ve günlük dosyası saniyede yaklaşık bir kere diske boşaltıldığı anlamına gelir. Değer 2, günlüğün her taahhütte günlük dosyasına yazıldığı anlamına gelir, ancak günlük dosyası yalnızca yaklaşık saniyede bir kez diske boşaltılır.
innodb_log_buffer_size = InnoDB’nin günlük verisini arabelleğe almak için kullandığı arabellek boyutu. Dolduğunda, InnoDB diske boşaltmak zorunda kalacaktır. Her neyse saniyede bir kez boşaltıldığı için, çok büyük olması mantıklı değil (hatta uzun işlemlerde).
innodb_log_file_size = günlük dosyalarının, günlükler döndüğünde kontrol noktalarını tutmaması için yeterince büyük olduğundan emin olun!
innodb_log_files_in_group = Günlük grubundaki toplam dosya sayısı. 2-3 değeri genellikle yeterlidir.
innodb_max_dirty_pages_pct = InnoDB arabellek havuzundaki kirli sayfaların izin verilen maksimum yüzdesi. Buna ulaşılırsa, InnoDB onları temiz sayfalardan hiç kaçınmamak için agresif bir şekilde yıkamaya başlayacaktır. Bu, yumuşak bir sınır olup, tutulması garanti edilmez.
innodb_lock_wait_timeout = Ne kadar bir InnoDB işlemi, geri alınmadan önce bir kilidin verilmesi için beklenmelidir. InnoDB, kendi kilit tablosundaki işlem kilitlenmelerini otomatik olarak algılar ve işlemi geri alır. LOCK TABLES komutunu veya aynı işlemde InnoDB’den başka işlemle güvenli depolama motorlarını kullanırsanız, InnoDB’nin fark edemediği bir kilitlenme ortaya çıkabilir. Böyle durumlarda zaman aşımı durumu çözmek için yararlıdır.
innodb_commit_concurrency= Bu yapılandırmayla, muhtemelen yine de önemi kalmaz, çünkü ikili günlüğü etkinleştirilir; bu, izolasyon seviyesi seri hale getirilemese bile seri hale getirilmiş taahhütleri zorlar.
open-files-limit = İşlem başına izin verilen açık dosya sayısını artırın. Uyarı: Global sistem limitini yeterince yüksek ayarladığınızdan emin olun! Çok sayıda açılmış tablo için yüksek değer gereklidir