Mysql hızlandırmak için öneriler aşağıda kısaca anlatmaya çalıştım. umarım faydalı olur.
Mysql Slow Queries
Mysql sunucu üzerindeki problemli (Yüksek I/O ‘ya ve CPU utilizasyonuna neden olan) ve index kullanmayan veya yanlış index kullanan sorgular belirlenebilir.
Ayarların kalıcı olması için/etc/my.cnf dosyasına aşağıdaki satırlar ekleyebiliriz.
1 2 3 4 5 6 7 8 9 |
# Slow Query Log # Indeks kullanmayan sorgularin tespiti icin kullanilir.Varsayilan deger 0'dir.1 yaparsak slow.log dosyasina yazar. log_queries_not_using_indexes = 0 # Yavas sorgularin tespiti icin aktif ediyoruz.Varsayilan deger 0'dir. slow_query_log = 1 # 3 sn'den cok suren sorgulari slow query kabul ediyoruz. long_query_time = 3 # Slow query'lerin yazilacagi dosya. slow-query-log-file = /var/lib/mysql/logs/slow.log |
Mevcut session’da gerekli ayarları düzenleyebiliriz.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
[root@dbtemplate ~]$mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.26-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show global variables like "log_queries_not_using_indexes"; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | OFF | +-------------------------------+-------+ 1 row in set (0.00 sec) mysql> set global log_queries_not_using_indexes=1; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like "log_queries_not_using_indexes"; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | +-------------------------------+-------+ 1 row in set (0.00 sec) mysql> show global variables like "slow_query_log"; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | OFF | +----------------+-------+ 1 row in set (0.00 sec) mysql> set global slow_query_log=1; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like "slow_query_log"; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | ON | +----------------+-------+ 1 row in set (0.00 sec) mysql> set global slow_query_log=1; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like "slow_query_log_file"; +---------------------+------------------------------+ | Variable_name | Value | +---------------------+------------------------------+ | slow_query_log_file | /var/lib/mysql/logs/slow.log | +---------------------+------------------------------+ 1 row in set (0.01 sec) mysql> set global slow_query_log_file="/var/lib/mysql/logs/slowquery.log"; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like "slow_query_log_file"; +---------------------+-----------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------+ | slow_query_log_file | /var/lib/mysql/logs/slowquery.log | +---------------------+-----------------------------------+ 1 row in set (0.00 sec) mysql> |
Cache Queries
Mysql veritabanı sunucusuna sql sorgusu her geldiğinde mysql sunucusu bu sorgular için herdefasında bir grup işlem yapar. Bu işlemler sorgunun analiz edilmesi ve parçalara bölünmesi, çalıştırılma şeklinin belirlenmesi, sonucun diske yazılması veya diskten yüklenmesi ve cevabın (result) client ‘a döndürülmesi olarak sıralanabilir.
Mysql sunucusu,tekrar edilen sorgular için sonuçları memory’de tutularak bu süreçlerin hızlanmasını sağlar. Bunu query cache kullanarak yapabiliriz.
1 2 3 4 5 |
# Query Cache # Query Cache'i aktif ediyoruz. Varsayilan deger 0'dir. query_cache_type = 1 # Query Cache icin kullanilacak bellek alani. query_cache_size = 16MB |
Query Cache değişkenlerinin monitör edilmesi;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> SHOW STATUS LIKE "qcache%"; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 22 | | Qcache_free_memory | 66906184 | | Qcache_hits | 1703257 | | Qcache_inserts | 1461824 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 7278 | | Qcache_queries_in_cache | 72 | | Qcache_total_blocks | 183 | +-------------------------+----------+ 8 rows in set (0.00 sec) |
Qcache_free_blocks : Bitişik memory bloklarının sayısını verir. Bu sayının yüksek olması datanın dağılmış (dağıtık) şekilde memory’de barındığını gösterir. Bu durumda FLUSH QUERY CACHE komutu ile boş bloklar oluşturulabilir.
Qcache_free_memory: Query cache için ayırdığımız memory’deki kullanılmayan cache miktarını gösterir.
Qcache_hits: Gelen sorgulardan kaçının query cache kullanılarak cevaplandığı sayısını gösterir.
Qcache_inserts: Gelen sorgulardan query cache ‘e eklenenlerin sayısını gösterir.
Önemli : (1 – (Qcache_inserts / Qcache_hits ) ) formulü query cahce hit oranını vermektedir. Bu örnekte sorguların %15 ‘i query cache den cevaplanmaktadır.Eğer oran düşükse problem query_cache_limit değişkeninden kaynaklanıyor olabilir. query_cache_limit query cache’ e eklenecek sorgu sonçlarının mb cinsinden maksimum değeridir. Bu değerin 512 ‘den daha yüksek tutulması önerilmez.
Qcache_lowmem_prunes : Query cache ‘in kaç kere mevcut memory’den taştığını göstermektedir. (Out of memory). Bu durumda sunucumuzda memory kaynağı problemi bulunduğu söyelenebilir. Eğer bu değer sürekli olarak artıyorsa ciddi bir fragmantasyon sorunuda oluşabilir.
Qcache_not_cached: Cache işlemine uygun olmayan sorguların sayısını gösterir. Yüksek oranla bu sorgular “select” sorgusu dışında kalan sorgulardır.
Qcache_queries_in_cache: Sunucuda şu anda cache ‘lenmiş sorgu sayısını gösterir.
Qcache_total_blocks: Cache üzerindeki toplam blok sayısını gösterir.
Limitlemeler
1 2 3 4 5 6 7 |
# Connection variables # Sunucuya aynı anda açılabilecek en fazla bağlantı sayısını gösteremektedir max_connections = 250 # TCP/IP baglanti kuyrugunun boyutudur. 50 + (max_connections / 5) back_log = 110 # idle connection'ların otomatik olarak kapatılması için geçmesi gereken süre. wait_timeout = 300 |
max_connections değişkeni sunucuya aynı anda açılabilecek en fazla bağlantı sayısını gösteremektedir. Bu değişkeni belirlemek için aşağıdaki komut kullanılabilir.
1 2 3 4 5 6 7 |
mysql> SHOW STATUS LIKE "max_used_connections"; +----------------------+-----------+ | Variable_name | Value | +----------------------+-----------+ | Max_used_connections | 192 | +----------------------+-----------+ 1 row in set (0.00 sec) |
wait _timeout idle(Boş bağlantı) olarak kalan mysql bağlantılarının otomatik olarak kapatılması için geçmesi gereken süredir. 300 saniye optimum değer olarak belirlenebilir.
Table Cache
1 2 |
# Varsayilan deger 2000'dir. table_open_cache = 512 |
Mysql dosya okuma işlemini hızlandırmak için açılan tablolalar için bir cache oluşturur.
1 2 3 4 5 6 7 8 |
mysql> SHOW STATUS LIKE "open%tables"; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | Open_tables | 748 | | Opened_tables | 253 | +---------------+----------+ 2 rows in set (0.00 sec) |
Yukarıdaki örnekte Open_tables değişkeni sistemde 748 tablonun şu anda açık olduğunu ve 253 tanesininde kapatıldığını, sistemde ihtiyaç olan file descripter sayısının geçildini göstermektedir.
Eğer open tables değişkeni table_open_cache değişkeninden çok düşükse table_open_cache değişkeni düşürülebilir.
Thread Cache
1 2 3 4 5 6 7 8 9 10 |
mysql> SHOW STATUS LIKE "threads%"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 59 | | Threads_connected | 158 | | Threads_created | 335 | | Threads_running | 8 | +-------------------+-------+ 4 rows in set (0.00 sec) |
Mysql table cache ‘de olduğu gibi açılan thread ‘ler içinde cache oluşturmaktadır. Mysql oluşan bağlantılar için yeni thread’ler oluşturmaktadır. Özellikle yoğun sistemlerde Threadlerin oluşması sırasında geçen sürenin kısaltılabilmesi için thread_cache kullanılmaktadır.
Önemli:Bu bölümde önemli olan değişken Threads_created değişkenidir. Eğer bu değer hızlı bir şekilde artıyorsa thread_cache değişkenin değeri arttırılmalıdır.
Key Buffer
1 2 3 4 5 6 7 8 |
mysql> show status like "%key_read%"; +-------------------+----------------+ | Variable_name | Value | +-------------------+----------------+ | Key_read_requests | 15271982 | | Key_reads | 88 | +-------------------+----------------+ 2 rows in set (0.01 sec) |
Key buffer Myisam index bloklarını barındırmaktadır. Bu bloklardan yapılacak olan taleplerin disk yerine memory ‘den gelmesi gerekmektedir.
Key_reads diskten okunan talepleri gösteremektedir. key_reads / key_read_requests formulü miss rate değşikenini vermektedir. Bu durumda bu formül sonucunda 1000 talepte 1 den fazla talep diskten okunuyorsa key_buffer değeri arttırılmalıdır.
Temporary Tables
Groub By gibi Komplex sorgular için mysql memory üzerinde geçici tablolar oluşturmaktadır. Ancak bu geçici tablo boyutu çok büyük olursa mysql tabloları disk üzerinde oluşturmaya başlar. Bu nedenle temporary tables değişkenleri kontrol edilmelidir.
1 2 3 4 5 6 7 8 9 |
mysql> SHOW STATUS LIKE "created_tmp%"; +————————-+——----------------+ | Variable_name | Value | +————————-+——----------------+ | Created_tmp_disk_tables | 30660 | | Created_tmp_files | 2 | | Created_tmp_tables | 32912 | +————————-+——----------------+ 3 rows in set (0.00 sec) |
Temp tables her kullanıldığında Created_tmp_tables değikeni artar. Disk üzerinde oluşan temp. tables ise created_tmp_disk_tables değişkeninde ifade edilmektedir. tmp_table_size ve max_heap_table_size değişkenlerinin değerleri değiştirilerek disk üzerinde oluşan temp. tables değeri düşürülebilir.
1 2 3 4 |
# Gecici tablolarin maksimum boyutu. Varsayilan deger 16777216'dir. tmp_table_size = 16MB # Bellekte olusan en buyuk tablolarin boyutu max_heap_table_size sistem degisken ile sinirlidir. max_heap_table_size = 16MB |
Sorting
1 2 3 4 5 6 7 8 9 10 |
mysql> SHOW STATUS LIKE "sort%"; +——————----------+——---------—+ | Variable_name | Value | +——————----------+——---------—+ | Sort_merge_passes | 1 | | Sort_range | 79192 | | Sort_rows | 2066532 | | Sort_scan | 44006 | +——————----------+———---------+ 4 rows in set (0.00 sec) |
Mysql sort işlemi gerçekleştirirken diskten okuduğu satır değerlerini bir sorf buffer alanı içerisinde tutar. Eğer data boyutu çok yüksek ise data için disk üzerinde bir temp. tablo oluşturulur ve sort işlemi bu tablo üzerinde gerçekleştirilir.
Eğer sort_merge_passes değişken değeri yüksek ise sort_buffer_size değeri arttırılmalıdır. Ancak bu bölümde gerçekleştirilecek olan arttırmalar küçük değerler ile olmalıdır. Çünkü sorting session base bir değişkendir.
Sort_range: Aralık belirtilerek gerçekleştirilen sort işlemleri
Sort_rows: Sort edilmiş satır sayısı.
Sort_scan: Sort işlemini gerçekleştirmek için taranmış olan tablo sayısı.
Scan Ratio
Mysql tabloları okumak için memory’deki alanı kullanmaktadır. Index’ler satırları okumak için gerekli bilgiyi barındırmaktadır. Ancak bazen daha büyük data parçarlarınıda tablolardan okumak gerekir. Bu durumda sistemde kaç adet select sorgusunun çalıştırıldığını ve kaç kere bir sonraki satırın okunduğunu bulmamız gerekir. (Index kullanılmadığı yani direk erişim bulunmadığı için)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> SHOW STATUS LIKE "com_select"; +—————+——--------------–+ | Variable_name | Value | +—————+——--------------–+ | Com_select | 318243| +—————+——--------------–+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE "handler_read_rnd_next"; +———————-------–+------------------—–+ | Variable_name | Value | +—————-------——–+—------------------–+ | Handler_read_rnd_next | 165959471 | +———————–---------------+——--------—–+ 1 row in set (0.00 sec) |
Handler_read_rnd_next / Com_select formülü scan oranını bize verecektir. Bu oran ‘ın 4000 de 1 den az olması beklenir. Eğer yüksek ise read_buffer_size değişkenini arttırmak gerekecektir.
So, what do you think ?