Úložiště dotazů na flexibilním serveru Azure Database for PostgreSQL

Úložiště dotazů je funkce na flexibilním serveru Azure Database for PostgreSQL, která poskytuje způsob sledování výkonu dotazů v průběhu času. Úložiště dotazů zjednodušuje řešení potíží s výkonem tím, že vám pomůže rychle najít nejdéle běžící dotazy a dotazy náročné na prostředky. Úložiště dotazů automaticky zaznamenává historii dotazů a statistik modulu runtime a uchovává je pro vaši kontrolu. Data se rozkryjí podle času, abyste viděli vzory dočasného použití. Data pro všechny uživatele, databáze a dotazy se ukládají do databáze pojmenované azure_sys na flexibilním serveru Azure Database for PostgreSQL.

Povolení úložiště dotazů

Úložiště dotazů je k dispozici bez dalších poplatků. Jedná se o funkci výslovného souhlasu, takže na serveru není ve výchozím nastavení povolená. Úložiště dotazů můžete povolit nebo zakázat globálně pro všechny databáze na daném serveru. Nemůžete ho zapnout nebo vypnout pro každou databázi.

Důležité

Nepovolujte úložiště dotazů na cenové úrovni Burstable, protože způsobuje problémy s výkonem.

Povolení úložiště dotazů na webu Azure Portal

  1. Přihlaste se k portálu Azure a vyberte flexibilní server Azure Database for PostgreSQL.
  2. V části Nastavení nabídky vyberte Parametry.
  3. Vyhledejte pg_qs.query_capture_mode parametr.
  4. Nastavte hodnotu na top nebo all, v závislosti na tom, jestli chcete sledovat dotazy na nejvyšší úrovni nebo také vnořené dotazy (ty, které se spouštějí uvnitř funkce nebo procedury), a vyberte Uložit. Počkejte až 20 minut, než se první dávka dat bude uchovávat v azure_sys databázi.

Povolení vzorkování čekání v úložišti dotazů

  1. Vyhledejte pgms_wait_sampling.query_capture_mode parametr.
  2. Nastavte hodnotu na all a Uložte.

Informace v úložišti dotazů

Úložiště dotazů se skládá ze dvou úložišť:

  • Úložiště statistik modulu runtime pro zachování informací o statistikách provádění dotazů.
  • Úložiště statistik čekání pro trvalé ukládání informací o statistikách čekání.

Mezi běžné scénáře použití úložiště dotazů patří:

  • Určení počtu spuštění dotazu v daném časovém intervalu
  • Porovnáním průměrné doby provádění dotazu v časových oknech zobrazíte velké variace.
  • Identifikace nejdéle běžících dotazů za posledních několik hodin
  • Identifikace nejvýznamnějších N dotazů, které čekají na prostředky.
  • Pochopení povahy čekání na konkrétní dotaz

Aby se minimalizovalo využití místa, statistiky spouštění modulu runtime v úložišti statistik modulu runtime se agregují v rámci pevného konfigurovatelného časového intervalu. Na informace v těchto úložištích se můžete dotazovat pomocí zobrazení.

Přístup k informacím o úložišti dotazů

Azure Database for PostgreSQL – Flexibilní server ukládá data z úložiště dotazů do databáze azure_sys. Následující dotaz vrátí informace o dotazech, které úložiště dotazů zaznamenalo:

SELECT * FROM  query_store.qs_view;

A tento dotaz vrátí informace o statistikách čekání:

SELECT * FROM  query_store.pgms_wait_sampling_view;

Najít čekající dotazy

Typy událostí čekání seskupují různé události čekání do kontejnerů na základě podobnosti. Query Store poskytuje typ čekání, konkrétní název čekání a příslušný dotaz. Při korelaci těchto informací o čekání se statistikami modulu runtime dotazu získáte hlubší přehled o tom, co přispívá k charakteristikám výkonu dotazů.

Tady je několik příkladů, jak můžete získat další přehled o úlohách pomocí statistik čekání v Query Store:

Pozorování Action
Dlouhé čekání na zámky Zkontrolujte texty dotazů pro ovlivněné dotazy a identifikujte cílové entity. Vyhledejte Query Store pro další dotazy, které se provádějí často a mají vysokou dobu trvání a upravují stejnou entitu. Po identifikaci těchto dotazů zvažte změnu logiky aplikace, aby se zlepšila souběžnost, nebo použijte méně omezující úroveň izolace.
Vysoká čekání na vstupně-výstupní operace způsobená vyrovnávací pamětí Najděte dotazy s vysokým počtem fyzických přístupů k datům v Úložišti dotazů. Pokud odpovídají dotazům s vysokými vstupně-výstupními čekáními, zvažte povolení funkce autonomního ladění , abyste zjistili, jestli může doporučit vytvoření některých indexů, které by mohly snížit počet fyzických čtení pro tyto dotazy.
Vysoké prodlevy způsobené pamětí Vyhledejte dotazy s nejvyšším využitím paměti v úložišti dotazů. Tyto dotazy pravděpodobně zpožďují další průběh ovlivněných dotazů.

Možnosti konfigurace

Když povolíte úložiště dotazů, uloží data v oknech agregace. Délka těchto oken je určena parametrem pg_qs.interval_length_minutes , který má výchozí hodnotu 15 minut. Pro každé okno ukládá úložiště dotazů až 500 různých dotazů. Atributy, které rozlišují jedinečnost každého dotazu, jsou user_id (identifikátor uživatele, který dotaz spouští), db_id (identifikátor databáze, v jehož kontextu se dotaz provede) a query_id (celočíselná hodnota jedinečně identifikující spuštěný dotaz). Pokud počet jedinečných dotazů během nakonfigurovaného intervalu dosáhne 500, uvolní úložiště dotazů 5% zaznamenaných dotazů, aby se uvolnilo místo pro další možnosti. Dotazy, které se uvolní jako první, jsou ty, které byly spuštěny nejméněkrát.

Ke konfiguraci parametrů Query Store použijte následující možnosti:

Parameter Description výchozí Rozsah
pg_qs.interval_length_minutes Interval zachytávání v minutách pro úložiště dotazů Definuje frekvenci trvalosti dat. 15 1 - 30
pg_qs.max_captured_queries Maximální počet dotazů, které úložiště dotazů uchovává, se uchovává ze všech dotazů zaznamenaných během každého intervalu zachycení. 500 100 - 500
pg_qs.max_plan_size Maximální počet bajtů, který úložiště dotazů uloží z textu plánu dotazu. Delší plány jsou zkráceny. 7500 100 - 10000
pg_qs.max_query_text_length Maximální délka dotazu, kterou úložiště dotazů může uložit. Delší dotazy jsou zkráceny. 6000 100 - 10000
pg_qs.parameters_capture_mode Určuje, jestli a kdy zachytit poziční parametry dotazu. capture_parameterless_only capture_parameterless_only, capture_first_sample
pg_qs.query_capture_mode Příkazy ke sledování none none, topall
pg_qs.retention_period_in_days Interval doby uchovávání ve dnech úložiště dotazů. Starší data se automaticky odstraní. 7 1 - 30
pg_qs.store_query_plans Zda má úložiště dotazů ukládat plány dotazů. off on, off
pg_qs.track_utility Určuje, jestli úložiště dotazů musí sledovat příkazy nástroje. on on, off

Poznámka:

Pokud změníte hodnotu parametru pg_qs.max_query_text_length , text všech dotazů zachycených úložištěm dotazů před provedením změny bude nadále používat stejné query_id a sql_query_text. Toto chování může mít dojem, že se nová hodnota neprojeví, ale u dotazů, které úložiště dotazů předtím nenahrálo, vidíte, že text dotazu používá nově nakonfigurovanou maximální délku. Toto chování je záměrně vysvětleno v zobrazeních a funkcích. Pokud spustíte query_store.qs_reset, odebere se všechny informace, které úložiště dotazů dosud zaznamenalo, včetně textu zachyceného pro každé ID dotazu. Pokud se některý z těchto dotazů spustí znovu, použije se nově nakonfigurovaná maximální délka na zachycený text.

Následující možnosti se vztahují konkrétně na statistiky čekání:

Parameter Description výchozí Rozsah
pgms_wait_sampling.history_period Frekvence v milisekundách, ve kterých se vzorkují události čekání. 100 1 - 600000
pgms_wait_sampling.query_capture_mode Které výroky musí rozšíření pgms_wait_sampling sledovat. none none, all

Poznámka:

pg_qs.query_capture_mode nahrazuje pgms_wait_sampling.query_capture_mode. Pokud pg_qs.query_capture_mode je none, nastavení pgms_wait_sampling.query_capture_mode nemá žádný účinek.

Na webu Azure Portal můžete získat nebo nastavit jinou hodnotu parametru.

Zobrazení a funkce

Můžete dotazovat informace zaznamenané úložištěm dotazů a odstranit je pomocí zobrazení a funkcí dostupných ve query_store schématu azure_sys databáze. Tato zobrazení může zobrazit kdokoli ve veřejné roli PostgreSQL k zobrazení dat v úložišti dotazů. Tato zobrazení jsou k dispozici pouze v databázi azure_sys .

Dotazy jsou normalizovány zkoumáním jejich struktury a ignorováním čehokoli, co není sémanticky významné, jako jsou literály, konstanty, aliasy, nebo rozdíly ve velikosti písmen.

Pokud jsou dva dotazy sémanticky identické, i když pro stejné odkazované sloupce a tabulky používají různé aliasy, jsou identifikovány se stejným query_id. Pokud se dva dotazy liší pouze v hodnotách literálů použitých v nich, jsou také identifikovány se stejnými query_id. U dotazů identifikovaných se stejným query_id je jejich sql_query_text dotazem, který se spustil jako první od chvíle, kdy úložiště dotazů začalo zaznamenávat aktivitu, nebo od chvíle, kdy byla naposledy smazána načtená data z důvodu provedení funkce query_store.qs_reset.

Jak funguje normalizace dotazů

Následující příklady ukazují, jak funguje normalizace dotazů:

Předpokládejme, že vytvoříte tabulku pomocí následujícího příkazu:

create table tableOne (columnOne int, columnTwo int);

Povolíte shromažďování dat Query Store a jeden nebo více uživatelů spustí následující dotazy v tomto přesném pořadí:

select * from tableOne;
select columnOne, columnTwo from tableOne;
select columnOne as c1, columnTwo as c2 from tableOne as t1;
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one";

Všechny předchozí dotazy sdílejí stejné ID dotazu. Query Store zachová text prvního dotazu, který se spustí po povolení shromažďování dat. Text je tedy select * from tableOne;.

Následující sada dotazů po normalizaci neodpovídá předchozí sadě dotazů, protože klauzule WHERE je sémanticky liší:

select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
select * from tableOne where columnOne = -3 and columnTwo = -3;
select columnOne, columnTwo from tableOne where columnOne = '5' and columnTwo = '5';
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = 7 and columnTwo = 7;

Všechny dotazy v této poslední sadě ale sdílejí stejné ID dotazu. Text, který je identifikuje, je text prvního dotazu v dávce: select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;.

A nakonec následující dotazy nemají stejné ID dotazu jako dotazy v předchozí dávce. Důvod, proč se neshodují, je vysvětlený v následujícím seznamu:

Dotaz:

select columnTwo as c2, columnOne as c1 from tableOne as t1 where columnOne = 1 and columnTwo = 1;

Důvod, proč se neshoduje: Seznam sloupců odkazuje na stejné dva sloupce (columnOne a ColumnTwo), ale pořadí je obrácené. Pořadí se změní z columnOne, ColumnTwo v předchozí dávce na ColumnTwo, columnOne v tomto dotazu.

Dotaz:

select * from tableOne where columnTwo = 25 and columnOne = 25;

Důvod, proč se neshoduje: Pořadí, ve kterém se vyhodnocují výrazy v klauzuli WHERE, je obrácené. Pořadí se mění z columnOne = ? and ColumnTwo = ? v předchozí dávce na ColumnTwo = ? and columnOne = ? v tomto dotazu.

Dotaz:

select abs(columnOne), columnTwo from tableOne where columnOne = 12 and columnTwo = 21;

Důvod, proč se neshoduje: První výraz v seznamu sloupců už nenícolumnOne, ale funkce abs se vyhodnocuje (columnOneabs(columnOne)), což není sémanticky ekvivalentní.

Dotaz:

select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = ceiling(16) and columnTwo = 16;

Důvod, proč se neshoduje: První výraz v klauzuli WHERE už nevyhodnocuje rovnost columnOne s literálem, ale s výsledkem funkce ceiling vyhodnoceným přes literál, který není sémanticky ekvivalentní.

Views

query_store.qs_view

Toto zobrazení vrátí všechna data, která úložiště dotazů uchovává v podpůrných tabulkách. Data, která úložiště dotazů stále zaznamenává v paměti pro aktuálně aktivní časové období, se nezobrazují, dokud se časové okno nedokončí a data nestálé v paměti se shromažďují a uchovávají v tabulkách uložených na disku. Toto zobrazení vrátí jiný řádek pro každou odlišnou databázi (db_id), uživatele (user_id) a dotaz (query_id).

název Type Odkazy Description
runtime_stats_entry_id bigint ID z tabulky runtime_stats_entries.
user_id OID pg_authid.oid Identifikátor uživatele, který příkaz spustil.
db_id OID pg_database.oid Identifikátor databáze, ve které byl příkaz proveden.
query_id bigint Interní hashovací kód vypočítaný ze stromu analýzy příkazu
query_sql_text varchar(10000) Text reprezentativního prohlášení Různé dotazy se stejnou strukturou jsou seskupené dohromady. Tento text je text pro první z dotazů v clusteru. Výchozí hodnota maximální délky textu dotazu je 6 000 a můžete ji upravit pomocí parametru pg_qs.max_query_text_lengthúložiště dotazů . Pokud text dotazu překročí tuto maximální hodnotu, zkrátí se na první pg_qs.max_query_text_length bajty.
plan_id bigint ID plánu odpovídající tomuto dotazu.
start_time časové razítko Dotazy se agregují podle časových intervalů. Parametr pg_qs.interval_length_minutes definuje časové rozpětí těchto oken (výchozí hodnota je 15 minut). Tento sloupec odpovídá počátečnímu času okna, ve kterém byla tato položka zaznamenána.
end_time časové razítko Koncový čas odpovídající časovému intervalu pro tuto položku.
calls bigint Počet spuštění dotazu v tomto časovém intervalu U paralelních dotazů připadá na každé spuštění 1 volání pro backendový proces, který řídí provádění dotazu, plus jedna další jednotka pro každý pracovní proces backendu spuštěný ke spolupráci na provádění paralelních větví stromu provádění.
total_time dvojitá přesnost Celková doba provádění dotazů v milisekundách
min_time dvojitá přesnost Minimální doba provádění dotazů v milisekundách
max_time dvojitá přesnost Maximální doba provádění dotazů v milisekundách
mean_time dvojitá přesnost Střední doba provádění dotazů v milisekundách
stddev_time dvojitá přesnost Směrodatná odchylka doby provádění dotazu v milisekundách
rows bigint Celkový počet řádků načtených nebo ovlivněných výrokem. U paralelních dotazů počet řádků pro každé spuštění odpovídá počtu řádků vrácených klientovi back-endovým procesem, který řídí provádění dotazu, a součet všech řádků, které jednotlivé back-endové pracovní procesy spustily, aby spolupracovaly s prováděním paralelních větví stromu spuštění, se vrátí do back-endového procesu, který řídí provádění dotazu.
shared_blks_hit bigint Celkový počet zásahů do sdílené mezipaměti bloku příkazem.
shared_blks_read bigint Celkový počet sdílených bloků přečtených příkazem
shared_blks_dirtied bigint Celkový počet sdílených bloků označených příkazem.
shared_blks_written bigint Celkový počet sdílených bloků zapsaných příkazem
local_blks_hit bigint Celkový počet zásahů do místní mezipaměti bloků vyvolaných příkazem.
local_blks_read bigint Celkový počet bloků místně přečtených příkazem.
local_blks_dirtied bigint Celkový počet místních bloků, které příkaz pošpinil.
local_blks_written bigint Celkový počet místních bloků zapsaných příkazem
temp_blks_read bigint Celkový počet dočasných bloků přečtených příkazem.
temp_blks_written bigint Celkový počet dočasných bloků zapsaných příkazem
blk_read_time dvojitá přesnost Celkový čas, který výrok strávil čtením bloků v milisekundách (pokud je povolen track_io_timing, jinak nula).
blk_write_time dvojitá přesnost Celkový čas, který výkaz strávil zápisem bloků, v milisekundách (pokud je povolen track_io_timing, jinak nula).
is_system_query Boolean Určuje, zda role s user_id = 10 (azuresu) spustila dotaz. Tento uživatel má oprávnění superuživatele a slouží k provádění operací řídicí roviny. Vzhledem k tomu, že je tato služba spravovanou službou PaaS, je součástí této role superuživatele pouze Microsoft.
query_type poslat SMS Typ operace reprezentovaný dotazem Možné hodnoty jsou unknown, , select, update, insertdeletemergeutilitynothing. undefined
search_path poslat SMS Hodnota search_path nastavená v době zachycení dotazu.
query_parameters poslat SMS Textové znázornění objektu JSON s hodnotami předanými pozičním parametrům parametrizovaného dotazu. Tento sloupec naplní hodnotu pouze ve dvou případech: 1) pro neparametrizované dotazy. 2) Pro parametrizované dotazy, pokud pg_qs.parameters_capture_mode je nastavena na capture_first_sample, a pokud úložiště dotazů může načíst hodnoty parametrů dotazu v době provádění.
parameters_capture_status poslat SMS Typ operace reprezentovaný dotazem Možné hodnoty jsou succeeded (dotaz nebyl parametrizován nebo se jedná o parametrizovaný dotaz a hodnoty byly úspěšně zachyceny), disabled (dotaz byl parametrizován, ale parametry nebyly zachyceny, protože pg_qs.parameters_capture_mode byly nastaveny na capture_parameterless_only), too_long_to_capture (dotaz byl parametrizován, ale parametry nebyly zachyceny, protože délka výsledného JSON, která by se zobrazila ve query_parameters sloupci tohoto zobrazení, byla považována za příliš dlouhou dobu, než se úložiště dotazů zachová). too_many_to_capture (dotaz byl parametrizován, ale parametry nebyly zachyceny, protože celkový počet parametrů, byly považovány za nadměrné, aby úložiště dotazů trvalo), serialization_failed (dotaz byl parametrizován, ale alespoň jedna z hodnot předaných jako parametr nemohl být serializována na text).

query_store.query_text_view

Toto zobrazení vrátí textová data dotazu v úložišti dotazů. Každý samostatný query_sql_text má jeden řádek.

název Type Description
query_text_id bigint ID pro tabulku query_texts
query_sql_text varchar(10000) Text reprezentativního prohlášení Různé dotazy se stejnou strukturou jsou seskupené dohromady. Tento text je text pro první z dotazů v clusteru.
query_type smallint Typ operace reprezentovaný dotazem Ve verzích PostgreSQL <= 14 jsou možné tyto hodnoty: 0 (neznámá), 1 (select), 2 (update), 3 (insert), 4 (delete), 5 (utility), 6 (nothing). Ve verzích PostgreSQL >= 15 jsou možné hodnoty 0 (neznámá), 1 (select), 2 (update), 3 (insert), 4 (delete), 5 (merge), 6 (utility), 7 (nothing).

query_store.pgms_wait_sampling_view

Toto zobrazení vrátí data událostí čekání v úložišti dotazů. Toto zobrazení vrátí jiný řádek pro každou odlišnou databázi (db_id), uživatele (user_id), dotaz (query_id) a událost (událost).

název Type Odkazy Description
start_time časové razítko Dotazy se agregují podle časových intervalů. Parametr pg_qs.interval_length_minutes definuje časové rozpětí těchto oken (výchozí hodnota je 15 minut). Tento sloupec odpovídá počátečnímu času okna, ve kterém byla tato položka zaznamenána.
end_time časové razítko Koncový čas odpovídající časovému intervalu pro tuto položku.
user_id OID pg_authid.oid Identifikátor objektu uživatele, který příkaz spustil.
db_id OID pg_database.oid Identifikátor objektu databáze, ve které byl příkaz proveden.
query_id bigint Interní hashovací kód vypočítaný ze stromu analýzy příkazu
event_type poslat SMS Typ události, pro kterou back-end čeká.
event poslat SMS Název události čekání, pokud backend právě čeká.
calls integer Kolikrát byla zaznamenána stejná událost.

Poznámka:

Seznam možných hodnot v event_type zobrazení a event sloupcích query_store.pgms_wait_sampling_view najdete v oficiální dokumentaci pg_stat_activity a vyhledejte informace odkazující na sloupce se stejnými názvy.

query_store.query_plans_view

Toto zobrazení vrátí plán dotazu, který se použil k provedení dotazu. Existuje jeden řádek pro každý jedinečný ID databáze a identifikátor dotazu. Úložiště dotazů zaznamenává pouze plány dotazů pro dotazy, které nejsou využitelné.

název Type Odkazy Description
plan_id bigint Hodnota hash z normalizovaného plánu dotazu vytvořeného aplikací EXPLAIN. Je v normalizované podobě, protože vylučuje odhadované náklady na uzly plánu a využití vyrovnávacích pamětí.
db_id OID pg_database.oid Identifikátor databáze, ve které byl příkaz proveden.
query_id bigint Interní hashovací kód vypočítaný ze stromu analýzy příkazu
plan_text varchar(10000) Plán provádění příkazu s nastavením costs=false, buffers=false a format=text. Identický výstup jako výstup vytvořený pomocí funkce EXPLAIN.

Functions

query_store.qs_reset

Tato funkce zahodí všechny statistiky, které úložiště dotazů shromažďuje. Zahodí statistiky pro již uzavřená časová období, která jsou již zachována v tabulkách na disku. Zahodí také statistiku aktuálního časového intervalu, který existuje pouze v paměti. Tuto funkci můžou spustit jenom členové role správce serveru (azure_pg_admin).

query_store.staging_data_reset

Tato funkce odstraní všechny statistiky shromážděné v paměti úložištěm dotazů. Tato data ještě nejsou vyprázdněna do tabulek na disku, které podporují trvalost shromážděných dat pro úložiště dotazů. Tuto funkci můžou spustit jenom členové role správce serveru (azure_pg_admin).

Režim jen pro čtení

Pokud je flexibilní server Azure Database for PostgreSQL v režimu jen pro čtení, například když default_transaction_read_only je parametr nastavený na on, nebo pokud je režim jen pro čtení automaticky povolený kvůli dosažení kapacity úložiště, úložiště dotazů nezachytává žádná data.

Povolení úložiště dotazů na serveru s replikami pro čtení automaticky nepovoluje úložiště dotazů na žádné z replik pro čtení. I když ho povolíte na některé z replik pro čtení, úložiště dotazů nezaznamená dotazy spuštěné u žádné repliky pro čtení. Repliky pro čtení pracují v režimu pouze pro čtení, dokud je nepovýšíte na primární repliku.

Související obsah

  • Last updated on