PolarDB for MySQL otomatik plan önbelleği özelliğini kullanarak SQL deyimleri için yürütme planlarını önbelleğe alabilirsiniz. Bu sayede sorgu optimizasyonu süresi kısalır ve sorgu performansı artar. Bu konuda, otomatik plan önbelleği özelliğine ilişkin arka plan bilgileri, ön koşullar, parametreler ve arayüzler açıklanmaktadır.
Arka plan bilgileri
Yürütme planı seçimi; istatistikler, farklı birleştirme sıralamaları ve sorgu dönüşümleri gibi birçok faktöre bağlıdır. Optimizasyon süresi sorgu deyimlerine göre değişir. Bazı SQL deyimlerinde sorgu optimizasyonu süresi, toplam çalışma süresinin büyük bir bölümünü oluşturabilir. Bu deyimler sık çalıştırıldığında uzun optimizasyon süreleri sistem yükünü artırır. Yürütme planlarını önbelleğe almak ve yeniden kullanmak her çalıştırmadaki optimizasyon süresini azaltır. Bu durum sorgu performansını iyileştirir, veritabanı yükünü düşürür ve aktarım hızı kapasitesini artırır.
Diğer birçok sorguda optimizasyon süresi kısa olsa da çalışma süresi büyük ölçüde yürütme planından etkilenir. Bir SQL deyimi içindeki farklı parametre değerleri farklı optimal yürütme planlarına karşılık gelebilir. Bazı senaryolarda MySQL daha fazla optimizasyon yapmak için parametre değerlerine göre altyapıdan gerçek verileri alır.
Bu sorgular için sabit bir yürütme planı kullanmak sorgu yanıt süresini iyileştirmeyebilir veya yük ek yükünü azaltmayabilir. Hatta bu yaklaşım performans gerilemesine neden olabilir.
PolarDB for MySQL uzun optimizasyon sürelerine sahip SQL deyimlerinin sorgu performansını iyileştirmek, sistem yükünü azaltmak ve sabit yürütme planlarından kaynaklanan performans gerilemesini önlemek için otomatik plan önbelleği özelliğini sunar. Otomatik plan önbelleği özelliğinin AUTO, DEMAND ve ENFORCE olmak üzere üç modu bulunur. Yürütme planlarını plan önbelleğinde önbelleğe almak için loose_plan_cache_type parametresini bu modlardan birine ayarlayabilirsiniz. Bu sayede optimizasyon süresi azalır ve sorgu performansı artar. Başvurulan bir tablonun istatistikleri değiştiğinde veya başvurulan bir tabloda Veri Tanımlama Dili (DDL) işlemi gerçekleştirildiğinde önbelleğe alınmış yürütme planı otomatik olarak geçersiz kılınır.
Ön koşullar
PolarDB kümeniz aşağıdaki sürüm gereksinimlerinden birini karşılamalıdır:
PolarDB for MySQL 8.0.1, revizyon sürümü 8.0.1.1.33 veya üzeri.
PolarDB for MySQL 8.0.2, revizyon sürümü 8.0.2.2.12 veya üzeri.
Parametreler
Aşağıdaki tabloda yer alan parametreleri PolarDB konsolu üzerinden ayarlayabilirsiniz. Daha fazla bilgi için bkz. Küme ve düğüm parametrelerini ayarlama.
|
Parametre |
Açıklama |
|
loose_plan_cache_type |
Otomatik plan önbelleği modu. Geçerli değerler:
|
|
loose_plan_cache_expire_time |
Plan önbelleğindeki bir yürütme planı bu süre içinde isabet almazsa belleği geri kazanılır. Birim saniyedir. Değer aralığı: 0 ile 4294967295. Varsayılan değer: 1800. |
|
loose_auto_plan_cache_pct_threshold |
Bir deyimin toplam çalışma süresindeki optimizasyon süresi yüzdesi için eşik değeri. Değer aralığı: 0 ile 100. Varsayılan değer: 20. |
|
loose_auto_plan_cache_time_threshold |
Bir SQL deyiminin toplam çalışma süresi için eşik değeri. Birim mikrosaniyedir. Değer aralığı: 0 ile 18446744073709551615. Varsayılan değer: 400. |
|
loose_auto_plan_cache_count_threshold |
Değer aralığı: 0 ile 18446744073709551615. Varsayılan değer: 512. Not
Önbelleğe alınmış yürütme planı, yalnızca önbelleğe alınma sayısı |
Arayüz açıklamaları
-
dbms_sql.add_plan_cache(schema, query): Belirtilen bir SQL deyiminin yürütme planını plan önbelleğinde önbelleğe alır.loose_plan_cache_typeparametresi DEMAND olarak ayarlandığında belirtilen bir SQL deyiminin yürütme planını önbelleğe almak için bu yerleşik saklı yordamı kullanabilirsiniz. Aşağıdaki örnekte bunun nasıl yapılacağı gösterilmektedir:CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");Bu deyim çalıştırıldıktan sonra
SELECT * FROM t_for_plan WHERE c1 > ? AND c1 < ?şablonuyla eşleşen herhangi bir SQL deyimi için yürütme planı önbelleğe alınır. -
dbms_sql.display_plan_cache_table(): Geçerli plan önbelleğinde başvurulan tablolar hakkındaki bilgileri görüntüler. Aşağıdaki örnekte bunun nasıl yapılacağı gösterilmektedir:CALL dbms_sql.display_plan_cache_table()\GAşağıdaki sonuç döndürülür:
*************************** 1. row *************************** SCHEMA_NAME: test TABLE_NAME: t_for_plan REF_COUNT: 1 VERSION: 0 VERSION_TIME: 2023-03-10 17:21:35.605264Aşağıdaki tabloda parametreler açıklanmaktadır.
SCHEMA_NAME: Başvurulan tablonun bulunduğu şemanın adı.
TABLE_NAME: Başvurulan tablonun adı.
REF_COUNT: Tablonun plan önbelleğinde kaç kez başvurulduğu sayısı.
VERSION: Plan önbelleğindeki başvurulan tablonun sürümü.
VERSION_TIME: Tablonun geçerli sürümünün başvurduğu zaman.
-
dbms_sql.delete_sharing_by_rowid(row_id): Belirtilen bir SQL deyiminin yürütme planını siler.row_iddeğerimysql.sql_sharingtablosunda depolanan yürütme planının satır kimliğidir.Example
-
Önbellekteki yürütme planı bilgilerini görüntülemek için aşağıdaki komutu çalıştırın.
SELECT Id, Schema_name, Type, Digest_text FROM mysql.sql_sharing WHERE Type = 'PLAN_CACHE'\GAşağıdaki sonuç döndürülür:
*************************** 1. row *************************** Id: 1 Schema_name: test Type: PLAN_CACHE Digest_text: SELECT * FROM `t_for_plan` WHERE `c1` > ? AND `c1` < ?Sorgu sonucunda
row_iddeğerinin 1 olduğu görülmektedir. -
Önceki sorgudaki yürütme planını silin.
CALL dbms_sql.delete_sharing_by_rowid(1);
-
Plan önbelleğinden önbelleğe alınan bilgileri alma
SQL deyimlerinin yürütme planları SQL Sharing modülünde depolanır. Plan önbelleğindeki önbelleğe alınmış bilgileri INFORMATION_SCHEMA.SQL_SHARING tablosundan sorgulamak için aşağıdaki SQL deyimini çalıştırabilirsiniz.
SELECT TYPE, REF_BY, SQL_ID, SCHEMA_NAME, DIGEST_TEXT, PLAN_ID, PLAN, PLAN_EXTRA, EXTRA FROM INFORMATION_SCHEMA.SQL_SHARING WHERE json_contains(REF_BY, '"PLAN_CACHE"') or json_contains(REF_BY, '"PLAN_CACHE(DEMAND)"')\G
Example
-
Verileri hazırlayın.
CREATE TABLE t_for_plan AS WITH RECURSIVE t(c1, c2, c3) AS (SELECT 1, 1, 1 UNION ALL SELECT c1+1, c1 % 50, c1 %200 FROM t WHERE c1 < 1000) SELECT c1, c2, c3 FROM t; CREATE INDEX i_c1_c2 on t_for_plan(c1, c2); -
Otomatik plan önbelleği modunu DEMAND olarak ayarlayın.
Otomatik plan önbelleği modunu aşağıdaki iki yoldan biriyle ayarlayabilirsiniz.
PolarDB konsolu içindeki Parameters sayfasında
loose_plan_cache_typeparametresini DEMAND olarak ayarlayın. Ayarlama tamamlandıktan sonra veritabanıyla bağlantınızı kesin ve ardından yeniden bağlanın.-
Geçerli veritabanı bağlantısında geçerli oturum için
plan_cache_typeparametresinidemandolarak ayarlamak üzere aşağıdaki komutu çalıştırın.SET plan_cache_type=demand;
-
Belirtilen SQL deyiminin yürütme planını plan önbelleğinde önbelleğe almak için aşağıdaki komutu çalıştırın.
CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10"); -
Sorgu deyimini çalıştırın.
SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10; -
Plan önbelleğindeki önbelleğe alınmış bilgileri sorgulayın.
SELECT TYPE, REF_BY, SQL_ID, SCHEMA_NAME, DIGEST_TEXT, PLAN_ID, PLAN, PLAN_EXTRA, EXTRA FROM INFORMATION_SCHEMA.SQL_SHARING WHERE json_contains(REF_BY, '"PLAN_CACHE"') or json_contains(REF_BY, '"PLAN_CACHE(DEMAND)"')\GAşağıdaki sonuç döndürülür:
*************************** 1. row *************************** TYPE: SQL REF_BY: ["PLAN_CACHE(DEMAND)"] SQL_ID: 9jrvksr3wjux6 SCHEMA_NAME: test DIGEST_TEXT: SELECT * FROM `t_for_plan` WHERE `c1` > ? AND `c1` < ? PLAN_ID: NULL PLAN: NULL PLAN_EXTRA: NULL EXTRA: {"TRACE_ROW_ID":1} *************************** 2. row *************************** TYPE: PLAN REF_BY: ["PLAN_CACHE"] SQL_ID: 9jrvksr3wjux6 SCHEMA_NAME: test DIGEST_TEXT: NULL PLAN_ID: 08xftakma6pm6 PLAN: /*+ INDEX(`t_for_plan`@`select#1` `i_c1_c2`) */ PLAN_EXTRA: {"access_type":["`t_for_plan`:range"]} EXTRA: {"PLAN_CACHE_INFO":{"tables":[`test`.`t_for_plan`], "versions":[0], "hits": 0}}EXTRAalanındaPLAN_CACHE_INFObaşvurulan tabloları, başvurulan tabloların sürümlerini ve yürütme planı isabet sayısını gösterir.
Sorgu performansı
8 çekirdekli ve 32 GB bellekli bir küme üzerinde stres testi gerçekleştirilmiştir. Veritabanında her biri 4 milyon satır veri içeren 25 tablo bulunmaktadır. Stres testinde IN listesinin uzunluğunun 20 olduğu SELECT id FROM sbtestN WHERE k IN(...) SQL deyimi kullanılmıştır. Hem Hazırlanmış Deyim (PS) hem de PS olmayan protokoller altında loose_plan_cache_type parametresi OFF, AUTO ve ENFORCE olarak ayarlanmışken performans test edilmiştir. Test sonuçları aşağıdadır:
PS protokolü altındaki performans testi sonuçları:

PS olmayan protokol altındaki performans testi sonuçları:

Test sonuçları otomatik plan önbelleği özelliğinin hem PS hem de PS olmayan protokoller altında performansı %50'den fazla artırdığını göstermektedir.