V ČMIS jsme se v nedávné době střetli s problémem spočívajícím v dramatickém zpomalení dotazů v souvislosti s migrací databázového systému Microsoft SQL Server. Bezprostředně po migraci jsme od klienta obdrželi zprávu, že se potýká se zdržením dotazů, za normálních okolností trvajícím v řádu minut, až na několik hodin. Rád bych vám představil, co za problémem stálo a jak jsme ho řešili.
Zákazník provozuje aplikaci Microsoft CRM 2011, jejíž databáze je hostována v Microsoft SQL Serveru 2012, edici Enterprise. Objem dat v databázi se pohybuje kolem 1 TB včetně indexů. Vzhledem k plánovanému přechodu na novější verzi CRM provedl zákazník migraci databázového systému. Aplikační databáze přesunul na nový server s výkonnějšími procesory, kam nainstaloval Microsoft SQL Server 2014, edici Standard. Kromě přesunu databáze zmigroval i veškerou konfiguraci SQL Serveru.
Bezprostředně po migraci však nastal problém. Uživatelé totiž začali hlásit, že celá řada dotazů trvá výrazně déle a některé dotazy nejsou dokončeny ani po několika hodinách. Společným rysem těchto dotazů je, že provádějí analytické výpočty a slouží jako podklad k reportům. Pracují tedy s velkým objemem databázových dat.
V rámci analýzy problému byla provedena kontrola celé řady výkonnostních čítačů. Žádný však nijak výrazně nepřevyšoval doporučené hodnoty, a tím tedy nevysvětloval zdroj problému.
U konkrétních problematických dotazů jsme analyzovali exekuční plán a porovnali jej s plánem, který byl použit ve starší verzi SQL Serveru. Kvalita plánů však byla na obou verzích SQL Serveru stejná, nebo velmi podobná. Při použití plánu SQL Server vykonal přibližně stejné množství IO operací na obou verzích. Migrací aplikace došlo k navýšení objemu dat v některých tabulkách, proto některé dotazy na novější verzi SQL Serveru provádějí více IO operací. To však není důvod k tomu, aby z původní délky trvání pět minut dotaz dobíhal v řádu hodin.
Hlavní viník: Buffer Pool
Při analýze zdrojů, na které dotazy SQL Serveru čekají při svém zpracování, jsme zaznamenali méně častý zdroj, který se běžně nevyskytuje. Jedná se o zdroj s názvem EC. Tento zdroj sice nebyl nejčastějším a nejdéle nedostupným zdrojem, neměl dokonce ani nijak zásadní průměrnou dobu čekání, přesto se ukázalo, že identifikátor představuje hlavní příčinu problému. Abych vás déle nenapínal, zdroj EC poukazuje na čtení dat z Buffer Pool Extension.
Co to je Buffer Pool Extension (BPE)? SQL Server disponuje vlastním memory managementem. Paměť, kterou jako proces alokuje, rozdělí na celou řadu částí, do kterých ukládá konkrétní objekty dle typu. Zpravidla největší část paměti je určena pro uložení dat tabulek a indexů, které jsou použity jako zdroj pro vykonání dotazů, a nazývá se Buffer Pool.
Velikost této části paměti je omezena edicí SQL Serveru. Ačkoli verze Enterprise paměťově omezena není, edice Standard ji omezuje na 128 GB, Express na 1,4 GB. SQL Server obsahuje volitelnou konfiguraci zvanou Buffer Pool Extension, která při nedostatku nebo omezení paměti RAM dokáže rozšířit Buffer Pool o prostor na disku. Jednoduše řekneme SQL Serveru: „Tady máš soubor na disku o dané velikosti a s ním můžeš pracovat jako s pamětí Buffer Pool.“
Pokud tedy máme omezenou paměť RAM nebo právě pracujeme s edicí Standard, která má stanovený limit 128 GB, můžeme prostor Buffer Pool významně rozšířit. Buffer Pool Extension má samozřejmě svá omezení, co se maximální velikosti týká, data v něm jsou pouze read-only (změny v datech se provádějí výhradně v paměti) a o tom, která data budou uložena v BPE, rozhoduje SQL Server.
BPE: dobrý sluha, špatný pán
BPE je jako zápalky. Může být velmi dobrým pomocníkem, při nesprávné konfiguraci však strůjcem hlavních výkonnostních problémů. SQL Server totiž nijak nezajímá, na jak rychlý disk jste soubor BPE umístili. Když má za standardních okolností SQL Server omezenou paměť pro Buffer Pool a má číst data tabulky, která v paměti nejsou, smaže z paměti jiná data, jež aktuálně nepotřebuje, a potřebná data načte do paměti z disku. To se projeví v celé řadě výkonnostních čítačů, například Page Life Expectancy, latenci IO operací u jednotlivých datových souborů, počtu IO čtecích operací z disku a tak dále.
V případě použití BPE se nic z toho stát nemusí. Namísto smazání nepotřebných dat z paměti pro uvolnění prostoru aktuálně požadovaným datům přesune data z Buffer Pool do BPE. Byť je BPE uložen jako soubor na disku, nejedná se o soubor žádné databáze, a proto pro něj standardně neměříme latenci. Díky BPE se tak do paměti může uložit podstatně větší objem dat, i když se vlastně nejedná o relativně rychlou RAM.
Nyní si vezměte, že se v BPE nachází obsah velké tabulky, nad níž pak uživatel spustí analytický dotaz, který musí, někdy i opakovaně, projít všechna data. Pokud máme BPE na standardním SSD disku, může latence jedné IO operace představovat například jednu až dvě milisekundy. Pokud dotaz provádí dva miliony IO operací a mimo to v něm použijeme skalární funkci, která donutí SQL Server procesovat jej jedním vláknem, bude trvat jen provedení příslušného množství IO operací 30 až 60 minut.
Říkáte si, že by ke stejné situaci došlo i bez BPE a načítání dat SQL Serverem z disku? Částečně máte pravdu. Je však třeba si uvědomit, že analytické dotazy mohou daný objekt používat opakovaně, takže opakované použití by již těžilo z přítomnosti dat v rychlé RAM. Jsou-li data používána častěji, díky vlastnímu memory managementu se SQL Server může rozhodnout je v paměti ponechat déle a dotazy na stejná data mohou z přítomnosti v RAM taktéž těžit. Pokud je však tabulka v BPE, opakované dotazy na její data vždy čtou každou stránku z disku.
Nevhodná konfigurace, ale i edice SQL Serveru
Samotná přítomnost BPE však nebyla jediným důvodem výkonnostního problému skupiny dotazů. BPE byl konfigurován i v předchozí verzi SQL Serveru a problém nezpůsoboval. Důležitá je také změna edice SQL Serveru. Klient používal verzi Enterprise, která nemá limit na velikost Buffer Poolu. Protože měl server dostatek paměti RAM a byl BPE nakonfigurován, SQL Server ho v podstatě nemusel použít, protože měl dostatek prostoru v samotné RAM. To se změnilo migrací na edici Standard, kdy Buffer Pool byl rázem omezen na 128 GB, a tím pádem byla podstatná část z datové velikosti databáze umístěna právě do BPE.
Po odstranění konfigurace BPE došlo k částečnému zlepšení výkonu některých dotazů právě proto, že část nejpoužívanějších dat držel v RAM, a tedy dotazy na tuto datovou množinu byly rychlejší. Datová velikost databáze je však kolem 1 TB, a proto se nadměrnému čtení z disku nebylo u analytických dotazů možné vyhnout. Aby byl problém vyřešen rychle, rozhodli jsme se okamžitě upgradovat edici Standard na Enterprise, což problém v kombinaci s 300 GB RAM zcela vyřešilo.
Upgrade na edici Enterprise je samozřejmě velmi finančně náročný. Při dostatku času existuje možnost nalezení jiného řešení, které by bylo levnější a dovolilo by setrvat na edici Standard. Za úvahu stojí použití např. ColumnStore indexů, techniky partitioning v kombinaci s kompresí.
