MySql üzerinde bazı denetlemeleri kurulu olan mysqlcheck aracı ile yapabilirsiniz. Mysql veritabanı kontrolü için aşağıdaki komutu kullanabilirsiniz.
mysqlcheck -cA -u root -p
Mysql database optimizasyonu için;
mysqlcheck -oA -u root -p
Bozuk veritabanının onarımı için;
mysqlcheck -rA -u root -p
Bu komutları çalıştırmak için MySql>bin klasörü içinde olmalısınız.
MySql konfigürasyon dosyasında kullandığınız server özelliklerine göre bazı değişiklikler yaparak MySql in performanslı ve hızlı olmasını sağlayabilirsiniz. Bu ayarlar Windows ve Linux işletim sistemlerinin her ikisinde de uygulanabilir. Windows için my.ini dosyasını Linux için my.cfn dosyasını değiştirmelisiniz.
Dikkat: İlk olarak içinde değişiklik yapacağınız MySql konfigürasyon dosyasının bir yedeğini alın.
Aşağıdaki anlatımda 8GB Ram belleğe sahip bir server için yapılandırma yapacağım.
innodb_buffer_pool_size : Varsayılan değeri 128M'dir. InnoDB'nin belleğe yüklenen veri ve dizinleri için ne kadar bellek kullanacağını belirlediğinden, değiştirmek istediğiniz ana ayardır. Özel bir MySQL sunucusu için önerilen boyut, mevcut fiziksel belleğin % 50-80'idir. Bu değerin çok yüksek ayarlanmasının tehlikesi, işletim sistemi dosya sistemi önbelleğine ve InnoDB'nin işlem günlüklerine dayanan bazı MySQL alt sistemleri için bellek kalmamasıdır. InnoDB daha çok bellek içi veritabanı gibi davranır, verileri diskten bir kez okur ve ardından sonraki okumalar sırasında bellekten verilere erişir. Arabellek havuzu, ekleme ve güncelleme işlemleriyle değiştirilen verileri bile önbelleğe alır, böylece disk yazma işlemleri daha iyi performans için birlikte gruplanabilir.
innodb_buffer_pool_size = 5GB
innodb_log_file_size : Varsayılan olarak 48M'dir. Bir günlük grubundaki her günlük dosyasının bayt cinsinden max boyutudur. Yüksek yazma hızı gerektiren sistemler arka plan denetim noktası etkinliklerinin daha uzun süreler boyunca düzgün bir şekilde performans göstermesine olanak sağlamak için bunu artırmak isteyebilir. 4GB'a kadar olan değerler güvenlidir. Tarihsel olarak, büyük bir günlük dosyasıyla çalışmanın dezavantajı, çökme kurtarma sürelerinin artmasıydı. Ancak bu, MySql 5.5 ve 5.6'da büyük ölçüde iyileştirildi. MySQL 5.5 ve daha yüksek sürümlerde kurtarma performansında yapılan iyileştirmeler, günlük dosyası boyutunu daha az dikkate almasına rağmen, daha büyük günlük dosyaları da çökme kurtarmayı yavaşlatır.
innodb_log_file_size = 128M
innodb_file_per_table : Bu özellik devre dışıysa (varsayılan) InnoDB paylaşılan tablo alanında tablolar oluşturur. Innodb_file_per_table etkinleştirilirse (= 1) InnoDB, paylaşılan tablo alanı yerine veri ve dizinleri depolamak için kendi .ibd dosyasını kullanarak her yeni tabloyu oluşturur.
innodb_file_per_table=1
innodb_flush_method : O_DIRECT olarak ayarlandığında, işletim sisteminin önbelleğe alma işlemi atlanacaktır. Bir donanım RAID denetleyicisi kullanıyorsanız, bunu O_DIRECT olarak ayarlamak isteyebilirsiniz. Bu InnoDB arabellek havuzu sayfalarını okurken hem InnoDB'de hem de işletim sisteminin dosya sistemi önbelleğinde bir kopyasının bulunmasının "çift arabelleğe alma" etkisini önler. Bu innodb_buffer_pool_size de düzgün bir şekilde ayarlandığından, çift arabelleğe alma ek yükü olmadığını garanti eder. Bir donanım RAID denetleyiciniz yoksa veya bazı SAN'larda O_DIRECT daha kötü performansa neden olabilir, bu durumda kullanmamanız önerilir.
innodb_flush_method = O_DIRECT
key_buffer_size : Varsayılan değeri 64M'dir. MyISAM tabloları için MySQL'e bellek (RAM) ayırır. MyISAM tabloları için index blokları arabelleğe alınır ve tüm evreler tarafından paylaşılır. key_buffer_size index blokları için kullanılan arabellek boyutudur. Anahtar arabelleği, anahtar önbelleği olarak da bilinir. Key_buffer_size, MyISAM motorunda ince ayar yapmak için muhtemelen en kullanışlı tek değişkendir. MyISAM'ın kendisinin verileri değil, yalnızca indexleri önbelleğe aldığını unutmayın. Dolayısıyla, mümkünse, bu ayarın değeri tüm dizinlerinizin boyutunu kapsamalıdır. Sunucunuzda kullanılabilir bellek varsa, key_buffer_size değerini artırmak veritabanı hızını önemli ölçüde artırabilir. MySQL belgeleri, 256MB'den daha büyük RAM'e sahip sunucular ve birçok tablo içeren bir veritabanı için 64M'lik bir ayar önerir. MyISAM tabloları için, sunucunuzdaki RAM'in % 25'inden büyük olmayan bir değer önerilir.
key_buffer_size = 256M
max_connections : Maksimum eş zamanlı bağlantı miktarını ayarlar (varsayılan olarak 151). Daha yüksek bir ayar, daha fazla eşzamanlı isteklere izin verir. “too many connections” hatası görürseniz, daha fazla bağlantıya izin vermek için bu değeri artırmak isteyebilirsiniz. Bunun dezavantajı, daha fazla eşzamanlı bağlantı için daha fazla donanım gerektirmesidir.
max_connections = 250
innodb_log_buffer_size : Günlük dosyaları için arabellek boyutunu belirler. Verilerinize ve trafiğinize bağlı olarak, arabellek hızla dolabilir ve bu nedenle bu değeri yükseltmek performansa yardımcı olabilir.
innodb_log_buffer_size = 8M
query_cache_size : Siteniz ve veritabanınız arasındaki etkileşim daha açık yöndeyse ve siteniz veritabanından yazmaktan daha fazla okuma eğilimi gösteriyorsa, query_cache_type'ı açık tutmak ve query_cache_size değerini 128M veya 256M gibi bir değere ayarlayabilirsiniz.
query_cache_size = 256M
tmp_table_size : Dahili bellek içi geçici tabloların maksimum boyutu. (Gerçek sınır, minimum tmp_table_size ve max_heap_table_size olarak belirlenir.) Bellek içi bir geçici tablo sınırı aşarsa, MySQL bunu otomatik olarak disk üzerindeki MyISAM tablosuna dönüştürür. Çok sayıda gelişmiş GROUP BY sorgusu yapıyorsanız ve çok fazla belleğiniz varsa tmp_table_size değerini (ve gerekirse max_heap_table_size) artırın. Bu değişken, kullanıcı tarafından oluşturulan BELLEK tabloları için geçerli değildir.
tmp_table_size = 32M
thread_cache_size : Sunucunun yeniden kullanım için kaç tane evreyi önbelleğe alması gerektiği. Bir istemcinin bağlantısı kesildiğinde, orada thread_cache_size'den daha az evre varsa istemcinin evreleri önbelleğe alınır. İş parçacığı istekleri, mümkünse önbellekten alınan iş parçacıklarının yeniden kullanılmasıyla karşılanır ve yalnızca önbellek boş olduğunda yeni bir iş parçacığı oluşturulur. Çok sayıda yeni bağlantınız varsa, performansı artırmak için bu değişken artırılabilir. Normalde, eğer iyi bir iş parçacığı uygulamasına sahipseniz, bu önemli bir performans artışı sağlamaz. Ancak, sunucunuz saniyede yüzlerce bağlantı görürse, yeni bağlantıların çoğunun önbelleğe alınmış evreleri kullanması için normalde thread_cache_size'yi yeterince yüksek ayarlamanız gerekir.
thread_cache_size = 100
table_open_cache : Tüm iş parçacıkları için açık tablo sayısı. Bu değeri artırmak, mysqld'nin gerektirdiği dosya tanımlayıcı sayısını artırır. Tablo önbelleğini artırmanız gerekip gerekmediğini kontrol ediniz. (Komut: SHOW GLOBAL STATUS LIKE 'open_tables';)
table_open_cache = 600
innodb_flush_log_at_trx_commit : Veritabanını tamamen ACID uyumlu hale getiren 1 varsayılan değerine sahiptir. Değeri 0 veya 2 olarak değiştirebilirsiniz, her ikisi de daha az güvenilirlik pahasına performans kazançları sunar.
innodb_flush_log_at_trx_commit = 1
Join_buffer_size, sort_buffer_size, read_buffer_size ve read_rnd_buffer_size gibi tamponlar, bağlantı başına tahsis edilir. Bu nedenle, read_buffer_size = 1M ve max_connections = 150 ayarı, MySQL'i başlangıçtan itibaren bağlantı başına 1MB x 150 bağlantı tahsis edecek şekilde yapılandırır. Varsayılan read_buffer_sizere değeri 128KB'dir. Varsayılanı artırmak yalnızca sunucu belleğinin boşa harcanması anlamına gelmez, çoğu zaman performansa yardımcı olmaz. Neredeyse tüm durumlarda, bu dört tampon yapılandırma satırını kaldırarak veya yorumlayarak varsayılanları kullanmak en iyisidir. Daha kademeli bir yaklaşım için, boşa harcanan RAM'i boşaltmak için mevcut büyük değerlerinizi azaltın, zamanla bunları varsayılan değerlere düşürmeye devam edin. Bunları keyfi olarak artırmaktan kaçının!
Bir sorgu paketinin maksimum boyutunu büyütün.
max_allowed_packet = 256M
innodb_additional_mem_pool_size : Bir bellek havuzunun bayt cinsinden boyutu InnoDB, veri sözlüğü bilgilerini ve diğer dahili veri yapılarını depolamak için kullanır. Uygulamanızda ne kadar çok tablo varsa, burada o kadar fazla bellek ayırırsınız. InnoDB bu havuzda bellek kalmazsa, işletim sisteminden bellek ayırmaya başlar ve MySQL hata günlüğüne uyarı mesajları yazar. Varsayılan değer 8MB'dir.
innodb_additional_mem_pool_size= 8M
innodb_lock_wait_timeout : Bir InnoDB işleminin pes etmeden önce bir satır kilidi için beklediği saniye cinsinden süre. Varsayılan değer 50 saniyedir. Başka bir InnoDB işlemi tarafından kilitlenen bir satıra erişmeye çalışan bir işlem, aşağıdaki hatayı vermeden önce satıra yazma erişimi için en fazla bu saniye kadar bekler: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
innodb_lock_wait_timeout = 60
Konfigürasyon:
-------------------
[mysqld]
key_buffer = 256M
max_allowed_packet = 256M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
innodb_buffer_pool_size = 5G
#innodb_additional_mem_pool_size = 8M
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 60
query_cache_type = 1
query_cache_limit = 16M
query_cache_min_res_unit = 2k
query_cache_size = 256M
[mysqldump]
max_allowed_packet = 256M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 20M
read_buffer = 4M
write_buffer = 4M
-------------------------------------
MySQL'de yaşanan en büyük performans sorunlarından biride gereksiz kaynak kullanımı, açılan bağlantıların kod içinden kapatılmamasıdır. Açılan tüm bağlantıları kapatmazsanız Mysql performansı zamanla düşer. PHP'de aşağıdaki komutu kullanarak bağlantıyı kapatabilirsiniz.
<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
// ....some PHP code...
$mysqli -> close();
?>
Tüm değişikliklerin server üzerinde nekadar kaynak RAM tüketeceğini görmek için aşağıdaki web sayfasını kullanabilirsiniz.
https://www.mysqlcalculator.com/
