MySQL için Azure Veritabanı - Esnek Sunucu’da sorgu performansı sorunlarını giderme

ŞUNLAR IÇIN GEÇERLIDIR: MySQL için Azure Veritabanı - Tek Sunucu MySQL için Azure Veritabanı - Esnek Sunucu

Önemli

MySQL için Azure Veritabanı tek sunucu kullanımdan kaldırma yolundadır. Esnek MySQL için Azure Veritabanı sunucuya yükseltmenizi kesinlikle öneririz. MySQL için Azure Veritabanı esnek sunucuya geçiş hakkında daha fazla bilgi için bkz. MySQL için Azure Veritabanı Tek Sunucu'ya neler oluyor?

Sorgu performansı birden çok faktörden etkilenebilir, bu nedenle ilk olarak MySQL için Azure Veritabanı esnek sunucu örneğinizde karşılaştığınız belirtilerin kapsamına bakmak önemlidir. Örneğin, sorgu performansı aşağıdakiler için yavaştır:

• MySQL için Azure Veritabanı esnek sunucu örneğinde çalışan tüm sorgular?
• Belirli bir sorgu kümesi mi?
• Belirli bir sorgu mu?

Ayrıca, sorguladığınız tabloların yapısında veya temel alınan verilerinde yapılan son değişikliklerin performansı etkileyebileceğini unutmayın.

Günlüğe kaydetme işlevini etkinleştirme

Tek tek sorguları analiz etmeden önce sorgu karşılaştırmalarını tanımlamanız gerekir. Bu bilgilerle, uygulamanın gereksinimlerine göre belirttiğiniz eşiği aşan sorguları izlemek için veritabanı sunucusunda günlüğe kaydetme işlevini uygulayabilirsiniz.

Esnek sunucu MySQL için Azure Veritabanı, daha uzun sürecek sorguları tanımlamak için yavaş sorgu günlüğü özelliğinin kullanılması önerilirÇalıştırılacak N saniye. Yavaş sorgu günlüğündeki sorguları belirledikten sonra, bu sorgularla ilgili sorunları gidermek için MySQL tanılamasını kullanabilirsiniz.

Uzun süre çalışan sorguları takip etmeye başlamadan önce Azure portalını slow_query_log veya Azure CLI'yı kullanarak parametresini etkinleştirmeniz gerekir. Bu parametre etkinleştirildiğinde, "yavaş çalışan" sorgular olarak tanımlanmadan önce sorguların çalıştırabileceği saniye sayısını belirtmek için parametresinin değerini long_query_time de yapılandırmanız gerekir. Parametrenin varsayılan değeri 10 saniyedir, ancak değeri uygulamanızın SLA gereksinimlerini karşılayacak şekilde ayarlayabilirsiniz.

Yavaş sorgu günlüğü, uzun süre çalışan sorguları izlemeye yönelik harika bir araç olsa da, etkili olmayabileceği bazı senaryolar vardır. Örneğin, yavaş sorgu günlüğü:

• Sorgu sayısı çok yüksekse veya sorgu deyimi çok büyükse performansı olumsuz etkiler. Parametresinin long_query_time değerini buna göre ayarlayın.
• Tüm satırları alması beklenen sorguları günlüğe kaydetmeyi log_queries_not_using_index belirten parametresini de etkinleştirdiyseniz yararlı olmayabilir. Tam dizin taraması yapan sorgular dizinden yararlanırlar, ancak dizin döndürülen satır sayısını sınırlamadığından günlüğe kaydedilirler.

Günlüklerden bilgi alma

Günlükler oluşturuldukları günden itibaren yedi güne kadar kullanılabilir. Azure portalı veya Azure CLI aracılığıyla yavaş sorgu günlüklerini listeleyebilir ve indirebilirsiniz. Azure portalında sunucunuza gidin, İzleme'nin altında Sunucu günlükleri'ni seçin ve ardından bir girişin yanındaki aşağı oku seçerek araştırdığınız tarih ve saatle ilişkili günlükleri indirin.

Buna ek olarak, yavaş sorgu günlükleriniz Tanılama günlükleri aracılığıyla Azure İzleyici günlükleriyle tümleştirilmişse, daha fazla analiz etmek için bir düzenleyicide sorgu çalıştırabilirsiniz:

AzureDiagnostics
| where Resource == '<your server name>'
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, Resource , event_class_s, start_time_t , query_time_d, sql_text_s
| where query_time_d > 10

Dekont

Tanılama günlükleri aracılığıyla yavaş sorgu günlüklerini tanılamaya başlamanıza yönelik diğer örnekler için bkz . Azure İzleyici Günlüklerinde günlükleri analiz etme.

Aşağıdaki anlık görüntüde örnek bir yavaş sorgu verilmiştir.

# Time: 2021-11-13T10:07:52.610719Z
# User@Host: root[root] @  [172.30.209.6]  Id: 735026
# Query_time: 25.314811  Lock_time: 0.000000 Rows_sent: 126  Rows_examined: 443308
use employees;
SET timestamp=1596448847;
select * from titles where DATE(from_date) > DATE('1994-04-05') AND title like '%senior%';;

Sorgunun 26 saniye içinde çalıştırıldığına, 443k satır üzerinde incelendiğine ve 126 sonuç satırı döndürdüğünü dikkat edin.

Genellikle, Query_time ve Rows_examined için yüksek değerlere sahip sorgulara odaklanmalısınız. Ancak, yüksek Query_time ancak yalnızca birkaç Rows_examined olan sorgular fark ederseniz, bu durum genellikle bir kaynak performans sorunu olduğunu gösterir. Bu gibi durumlarda GÇ kısıtlaması veya CPU kullanımı olup olmadığını denetlemeniz gerekir.

Sorgu profili oluşturma

Belirli bir yavaş çalışan sorguyu belirledikten sonra, daha fazla ayrıntı toplamak için EXPLAIN komutunu ve profil oluşturmayı kullanabilirsiniz.

Sorgu planını denetlemek için aşağıdaki komutu çalıştırın:

EXPLAIN <QUERY>

Dekont

EXPLAIN deyimlerini kullanma hakkında daha fazla bilgi için bkz. Esnek MySQL için Azure Veritabanı sunucuda sorgu performansının profilini almak için EXPLAIN'ı kullanma.

Sorgu için BIR EXPLAIN planı oluşturmaya ek olarak, geçerli oturumda çalıştırılan deyimlerin yürütülmesini tanılamanıza olanak tanıyan SHOW PROFILE komutunu kullanabilirsiniz.

Oturumda profil oluşturmayı ve belirli bir sorgunun profilini oluşturmayı etkinleştirmek için aşağıdaki komut kümesini çalıştırın:

SET profiling = 1;
<QUERY>;
SHOW PROFILES;
SHOW PROFILE FOR QUERY <X>;

Dekont

Tek tek sorguların profilini oluşturma yalnızca bir oturumda kullanılabilir ve geçmiş deyimlerinin profili oluşturulamaz.

Şimdi sorgu profilini oluşturmak için bu komutları kullanma konusuna daha yakından bakalım. İlk olarak, geçerli oturum için profil oluşturmayı etkinleştirin, komutunu SET PROFILING = 1 çalıştırın:

mysql> SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Ardından, tam tablo taraması gerçekleştiren bir alt çalışma sorgusu yürütebilirsiniz:

mysql> select * from sbtest8 where c like '%99098187165%';
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k       | c                                                                                                                       | pad                                                         |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 10 | 5035785 | 81674956652-89815953173-84507133182-62502329576-99098187165-62672357237-37910808188-52047270287-89115790749-78840418590 | 91637025586-81807791530-84338237594-90990131533-07427691758 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (27.60 sec)

Ardından, komutunu çalıştırarak SHOW PROFILES tüm kullanılabilir sorgu profillerinin listesini görüntüleyin:

mysql> SHOW PROFILES;
+----------+-------------+----------------------------------------------------+
| Query_ID | Duration    | Query                                              |
+----------+-------------+----------------------------------------------------+
|        1 | 27.59450000 | select * from sbtest8 where c like '%99098187165%' |
+----------+-------------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Son olarak, 1. sorgunun profilini görüntülemek için komutunu çalıştırın SHOW PROFILE FOR QUERY 1 .

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000102 |
| checking permissions |  0.000028 |
| Opening tables       |  0.000033 |
| init                 |  0.000035 |
| System lock          |  0.000018 |
| optimizing           |  0.000017 |
| statistics           |  0.000025 |
| preparing            |  0.000019 |
| executing            |  0.000011 |
| Sending data         | 27.594038 |
| end                  |  0.000041 |
| query end            |  0.000014 |
| closing tables       |  0.000013 |
| freeing items        |  0.000088 |
| cleaning up          |  0.000020 |
+----------------------+-----------+
15 rows in set, 1 warning (0.00 sec)

Veritabanı sunucusunda en çok kullanılan sorguları listeleme

Sorgu performansı sorunlarını her giderdiğinizde, MySQL için Azure Veritabanı esnek sunucu örneğinizde en sık çalıştırılan sorguları anlamanız yararlı olur. Bu bilgileri, en çok kullanılan sorgulardan herhangi birinin çalıştırılmasının normalden uzun sürdüğünü ölçmek için kullanabilirsiniz. Buna ek olarak, bir geliştirici veya DBA herhangi bir sorguda sorgu yürütme sayısında ve süresinde ani bir artış olup olmadığını belirlemek için bu bilgileri kullanabilir.

MySQL için Azure Veritabanı esnek sunucu örneğinize karşı en çok yürütülen ilk 10 sorguyu listelemek için aşağıdaki sorguyu çalıştırın:

SELECT digest_text AS normalized_query,
 count_star AS all_occurrences,
 Concat(Round(sum_timer_wait / 1000000000000, 3), ' s') AS total_time,
 Concat(Round(min_timer_wait / 1000000000000, 3), ' s') AS min_time,
 Concat(Round(max_timer_wait / 1000000000000, 3), ' s') AS max_time,
 Concat(Round(avg_timer_wait / 1000000000000, 3), ' s') AS avg_time,
 Concat(Round(sum_lock_time / 1000000000000, 3), ' s') AS total_locktime,
 sum_rows_affected AS sum_rows_changed,
 sum_rows_sent AS sum_rows_selected,
 sum_rows_examined AS sum_rows_scanned,
 sum_created_tmp_tables,
 sum_select_scan,
 sum_no_index_used,
 sum_no_good_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

Dekont

Veritabanı sunucunuzda en çok yürütülen sorguları karşılaştırmak ve en üstteki sorgularda bir değişiklik olup olmadığını veya ilk karşılaştırmada mevcut sorguların çalıştırma süresinde artıp artmadığını belirlemek için bu sorguyu kullanın.

Toplam yürütme süresine göre en pahalı 10 sorguyu listeleme

Aşağıdaki sorgunun çıktısı, veritabanı sunucusunda çalışan ilk 10 sorgu ve veritabanı sunucusundaki yürütme sayısı hakkında bilgi sağlar. Ayrıca sorgu gecikme süreleri, kilit süreleri, sorgu çalışma zamanı kapsamında oluşturulan geçici tabloların sayısı gibi diğer yararlı bilgileri de sağlar. Veritabanındaki en çok kullanılan sorguları ve gecikme süreleri gibi faktörlerdeki değişiklikleri izlemek için bu sorgu çıkışını kullanın. Bu, gelecekteki risklerden kaçınmaya yardımcı olmak için sorguyu daha fazla ayarlama şansına işaret edebilir.

SELECT REPLACE(event_name, 'statement/sql/', '') AS statement, 
 count_star AS all_occurrences , 
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency, 
 Concat(Round(avg_timer_wait / 1000000000000, 2), ' s') AS avg_latency, 
 Concat(Round(sum_lock_time / 1000000000000, 2), ' s') AS total_lock_time  , 
 sum_rows_affected AS sum_rows_changed, 
 sum_rows_sent AS  sum_rows_selected, 
 sum_rows_examined AS  sum_rows_scanned, 
 sum_created_tmp_tables,  sum_created_tmp_disk_tables, 
 IF(sum_created_tmp_tables = 0, 0, Concat( Truncate(sum_created_tmp_disk_tables / 
 sum_created_tmp_tables * 100, 0))) AS 
 tmp_disk_tables_percent, 
 sum_select_scan, 
 sum_no_index_used, 
 sum_no_good_index_used 
FROM performance_schema.events_statements_summary_global_by_event_name 
WHERE event_name LIKE 'statement/sql/%' 
 AND count_star > 0 
ORDER BY sum_timer_wait DESC 
LIMIT 10;

InnoDB atık toplamayı izleme

InnoDB çöp toplama işlemi engellendiğinde veya geciktirildiğinde, veritabanı depolama kullanımını ve sorgu performansını olumsuz etkileyebilecek önemli bir temizleme gecikmesi geliştirebilir.

InnoDB geri alma segment geçmişi listesi uzunluğu (HLL), geri alma günlüğünde depolanan değişiklik kayıtlarının sayısını ölçer. Artan HLL değeri, InnoDB'nin çöp toplama iş parçacıklarının (iş parçacıklarını temizleme) yazma iş yüküne ayak uydurmadığını veya temizleme işleminin uzun süre çalışan bir sorgu veya işlem tarafından engellendiğini gösterir.

Çöp toplamadaki aşırı gecikmelerin ciddi, olumsuz sonuçları olabilir:

• InnoDB sistem tablo alanı genişleyerek temel depolama biriminin büyümesini hızlandırır. Bazen sistem tablo alanı, engellenen temizlemenin bir sonucu olarak birkaç terabayt kadar kabarabilir.
• Silme işaretli kayıtlar zamanında kaldırılmaz. Bu, InnoDB tablo boşluklarının büyümesine neden olabilir ve altyapının bu kayıtların kapladığı depolama alanını yeniden kullanmasını engelleyebilir.
• InnoDB depolama yapılarının büyümesi nedeniyle tüm sorguların performansı düşebilir ve CPU kullanımı artabilir.

Sonuç olarak HLL değerlerini, desenlerini ve eğilimlerini izlemek önemlidir.

HLL değerlerini bulma

HLL değerini bulmak için innodb durum komutunu gösterme altyapısını çalıştırabilirsiniz. Değer çıkışta, İşlemLER başlığı altında listelenir:

mysql> show engine innodb status\G 
*************************** 1. row *************************** 
 
(...) 
 
------------ 
TRANSACTIONS 
------------ 
Trx id counter 52685768 
Purge done for trx's n:o < 52680802 undo n:o < 0 state: running but idle 
History list length 2964300 
 
(...) 

ayrıca information_schema.innodb_metrics tablosunu sorgulayarak da HLL değerini belirleyebilirsiniz:

mysql> select count from information_schema.innodb_metrics  
    -> where name = 'trx_rseg_history_len'; 
+---------+ 
|  count  | 
+---------+ 
| 2964300 | 
+---------+ 
1 row in set (0.00 sec)

HLL değerlerini yorumlama

HLL değerlerini yorumlarken aşağıdaki tabloda listelenen yönergeleri göz önünde bulundurun:

Değer	Notlar
Yaklaşık 10.000'den az	Çöp toplamanın geride bırakılmadığını gösteren normal değerler.
Yaklaşık 10.000 ile ~1.000.000 arasında	Bu değerler çöp toplamada küçük bir gecikme olduğunu gösterir. Bu değerler sabit kalır ve artmazsa kabul edilebilir olabilir.
Yaklaşık 1.000.000'den büyük	Bu değerler araştırılmalıdır ve düzeltici eylemler gerektirebilir

Aşırı HLL değerlerini ele alma

HLL büyük ani artışlar gösteriyorsa veya düzenli büyüme deseni sergilerse, MySQL için Azure Veritabanı esnek sunucu örneğinizde çalışan sorguları ve işlemleri hemen araştırın. Ardından, çöp toplama işleminin ilerlemesini engelleyebilecek iş yükü sorunlarını çözebilirsiniz. Veritabanının temizleme gecikmesi olmaması beklenmese de, gecikmenin kontrolsüz bir şekilde büyümesine izin vermemelisiniz.

Tablodan information_schema.innodb_trx işlem bilgilerini almak için aşağıdaki komutları çalıştırın:

select * from information_schema.innodb_trx  
order by trx_started asc\G

Sütundaki trx_started ayrıntı, işlem yaşını hesaplamanıza yardımcı olur.

mysql> select * from information_schema.innodb_trx  
    -> order by trx_started asc\G 
*************************** 1. row *************************** 
                    trx_id: 8150550 
                 trx_state: RUNNING 
               trx_started: 2021-11-13 20:50:11 
     trx_requested_lock_id: NULL 
          trx_wait_started: NULL 
                trx_weight: 0 
       trx_mysql_thread_id: 19 
                 trx_query: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(…) 

Oturumun geçerli durumunda harcanan süre de dahil olmak üzere geçerli veritabanı oturumları hakkında bilgi için tabloya information_schema.processlist bakın. Örneğin aşağıdaki çıktıda, son 1462 saniye boyunca etkin bir şekilde sorgu yürüten bir oturum gösterilmektedir:

mysql> select user, host, db, command, time, info  
    -> from information_schema.processlist  
    -> order by time desc\G 
*************************** 1. row *************************** 
   user: test 
   host: 172.31.19.159:38004 
     db: employees 
command: Query 
   time: 1462 
   info: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
 
(...) 

Öneriler

• Veritabanınızda sorgularınızı çalıştırmak için yeterli kaynak ayrıldığınızdan emin olun. Bazen, iş yükünüz için daha fazla CPU çekirdeği ve ek bellek elde etmek için örnek boyutunu artırmanız gerekebilir.

• Büyük veya uzun süre çalışan işlemleri daha küçük işlemlere ayırarak önleyin.

• Arka plan temizleme işlemlerinin verimliliğini artırmak için iş yükünüz uyarınca innodb_purge_threads yapılandırın.

  Dekont

  Altyapı davranışındaki değişikliği ölçmek için her ortam için bu sunucu değişkeninde yapılan değişiklikleri test edin.

• Sistem belirtilen eşiklerden herhangi birini aşarsa bildirim almak için "Konak CPU Yüzdesi", "Konak Bellek Yüzdesi" ve "Toplam Bağlan ions" uyarılarını kullanın.

• Sorunlu veya yavaş çalışan sorguları belirlemek ve ardından bunları iyileştirmek için Sorgu Performansı Analizler veya Azure Çalışma Kitapları'nı kullanın.

• Üretim veritabanı sunucuları için, her şeyin sorunsuz çalıştığından emin olmak için düzenli aralıklarla tanılamaları toplayın. Aksi takdirde, tanımladığınız sorunları giderin ve çözün.

Sonraki adımlar

En önemli sorularınızın eş yanıtlarını bulmak veya soru göndermek veya yanıtlamak için Stack Overflow'u ziyaret edin.