Rychlejší aplikace i bez změn dotazů - 1.díl - vliv častých Commitů

Pamatuji si, jak jsem před pár lety seděl na konferenci Oracle Develop a sledoval svého poněkud známějšího kolegu Marka Townsenda ukazovat prostinké demo - jak se jedna a tatáž operace dá udělat buď velmi pomalu a nebo velmi rychle. Nešlo o klasické ladění SQL, ale efektivní volání těchto dotazů. Jednotlivé techniky samy o sobě jsou přitom asi všem známé. Ohromilo mne ale hlavně to, jak velký rozdíl ve výkonu stejné operace prováděné stejným jednoduchým SQL dotazem může být. Říkám si, že kdyby ten rozdíl vidělo více lidí, možná by se častěji donutili napsat o ten jeden, dva řádky kódu více, aby byl kód třeba i o řád efektivnější a rychlejší. Pokusil jsem se udělat podobný příklad a na jeho základně vznikla trojice článků, které postupně vycházely na serveru Databázový svět. Z nějakého důvodu se ale poslední díl nikdy nedočkal publikace. Protože jsem od té doby párkrát narazil na potřebu odkázat někoho na celou trojici článků, rozhodl jsem se je re-publikovat na tomto blogu.

Problém a řešení budu ukazovat v Javě, ale ve skutečnosti jde o postupy, které jsou platné ať už voláte databázi odkudkoliv.
Úkol je jednoduchý - vložit 100.000 záznamů do jednoduché tabulky. A postupně si ukážeme že ze základního nejpomalejšího řešení se několika jednoduchými kroky dostaneme na řešení, které bude 80x rychlejší.



CREATE TABLE mojeTab(
datum date,
popis varchar2(100),
dalsidata varchar2(200));

Do sloupečku POPIS budeme vkládat měnící se text, ostatní sloupečky nám slouží pro dosažení reálnější velikosti záznamu, do DATUM budeme vkládat aktuální datum a čas, a DALSIDATA budeme plnit konstatním řetězcem 200 znaků.
Je zřejmé, že konkrétní zrychlení závisí na řadě faktorů a výsledky ve vašich aplikacích se mohou významně lišit. I tak doufám, že pro vás osmdesátinásobné zvýšení výkonu které v této konkrétní úloze postupně dosáhneme bude zajímavé. Ostatně ukážeme si, že zkrácení doby zpracování není jediným efektem. Postupně se nám významně podaří snížit jak počet čtecích a zápisových operací, které musí server provést, objem síťové komunikace i zátěž procesoru. Naše aplikace tak možná ve výsledku vystačí s levnějším diskovým polem a serverem s menším počtem procesorů.

1. Výchozí stav - Skládání SQL dotazů v textu, častý commit

Jako základ si vezmeme bohužel často používaný postup, kdy jsou hodnoty proměnných přímo vkládány do textu dotazu, objekt Statement je vytvářen pro každý příkaz znovu a po každém příkazu následuje Commit - ať již explicitní, nebo autocommit. Výchozí kód tedy vypadá takto:

void zaklad(Connection conn, String dalsidata) throws SQLException {
Statement stmt;
String sql, popis;
for(int recNo=0;recNo<100000;recNo++) {
stmt=conn.createStatement();
popis="Muj popis c." + recNo;
sql="INSERT INTO mojeTab (datum,popis,dalsidata) "
+ "VALUES(SYSDATE,'" + popis + "','"+dalsidata+"')";
stmt.execute(sql);
stmt.close();
conn.commit();
}
}

Vložení 100.000 záznamů trvalo 168 sekund. Nebyl přitom velký rozdíl mezi explicitním commitováním, jak je uvedeno v příkladu, nebo použitím autocommit. Ze sebraných provozních statistik sice vyplývá, že zapnutí autocommitu zmenší na polovinu množství zpráv předávaných z klienta na server, ale výsledný čas se zkrátil jen o jednotky procent. Rozdíl by nejspíš byl vyšší, pokud bychom měli mezi klientem a serverem síťové spojení s vyšší latencí.
Mimochodem ještě významně horší výsledek dosáhneme, pokud bychom rezignovali na opakované použití stejného spojení a vytvářeli si nové spojení pro každý Insert - v tom případě by doba zpracování vzrostla zhruba na desetinásobek. Vytvářet spojení ve smyčce nám všem určitě přijde jako nesmysl, ale kolik webových aplikací vytváří nové spojení na začátku každé stránky a na jejím konci zase spojení uzavírá místo aby používaly connection pool?

2. Commitovat jen, když je to třeba

Jednou z možností další optimalizace je snížení počtu commitů. Otázka jak často používat Commit je stará jak lidstvo... ehm, vlastně... databáze samy. Častý Commit zajišťuje okamžitou viditelnost změn pro ostatní spojení. Navíc krátké transakce znamenají i krátkou dobu držení zámků a zkracují tak případné vzájemné blokování transakcí.
Jenže každý Commit na druhou stranu zvyšuje objem dat, která je třeba zapsat do redo logů a undo tablespace. Navíc vynucuje okamžitý zápis redo informací z log bufferu na disk do redo log souborů, aby byla zajištěna trvalost transakce. Jde tedy o poměrně drahou operaci.
Častý Commit sice zvyšuje objem redo a undo dat, které daná transakce generuje, což znamená více zápisových operací, málo častý Commit a tudíž dlouhé transakce však naopak prodlužují dobu, po kterou musí Oracle udržovat v undo tablespace informace pro případný rollback. Zápisových operací je tak méně, ale undo tablespace musí být obvykle větší.
Jak se tedy změní výkon naší aplikace, pokud operaci Commit vysuneme ven ze smyčky a provedeme ji pouze jedenkrát a to na konci procedury?

void commitJednou(Connection conn, String dalsidata) throws SQLException {
Statement stmt;
String sql, popis;
for(int recNo=0;recNo<100000;recNo++) {
stmt=conn.createStatement();
popis="Muj popis c." + recNo;
sql="INSERT INTO mojeTab (datum,popis,dalsidata) "
+ "VALUES(SYSDATE,'" + popis + "','"+dalsidata+"')";
stmt.execute(sql);
stmt.close();
}
conn.commit();
}

Nyní již ke vložení 100.000 záznamů stačilo pouhých 106 sekund. Vynecháním opakovaných Commitu jsme tedy uspořili zhruba třetinu času. Úspora se projevila i v objemu čtených a měněných dat.

Statistika1. Základ2. Commit 1xÚspora
Čas a CPU
Dosažený čas [s]16710537%
CPU used by this session (Spotřeba CPU) [s]1038616%
Čtení a zápis dat
Session logical reads (Logické čtení) [počet operací]715 358426 92840%
Db block changes (Změny datových bloků) [počet operací]421 819219 28248%
Redo size (Objem dat zapsaný do redo logů) [bytes]75 749 02648 221 63236%
Undo change vector size (Objem dat zapsaný do undo tblspc.) [bytes]12 669 4686 401 98449%
Optimalizace dotazů
Parse time elapsed (Doba parsování dotazů) [s]80748%

Tabulka ukazuje očekávané snížení objemu dat zapisovaných do redo logů (redo size) o třetinu a dat zapisovaných do undo tablespace na polovinu. Celkové množství čtecích a zápisových operací pokleslo také na polovinu. Můžeme si sice říkat, že čtení (session logical reads) bude ve většině případů probíhat z cache a jde tedy o relativně levnou operací. Avšak i málo náročná operace opakovaná tolikrát významně zatíží serveru. Mimochodem již snížení frekvence commitování na každých 100 či 1.000 záznamů vedlo k velmi podobným výsledkům.
Ze slibovaného osmdesátinásobného zrychlení jsme ušli jen kousek - zrychlili jsme operaci o 37%, v dalších dílech tedy budeme mít ještě hodně práce. Kde ušetřit další čas nám může napovědět poslední sekce z tabulky vybraných statistik. Z těch celkem 106 sekund běhu operace zabralo celých 74 sekund parsování dotazů, tedy rozbor a optimalizace dotazu (statistika parse time elapsed). Pouze zbylých 32 sekund zabralo vlastní uložení dat. Musí opravdu databázový server opakovaně parsovat každý z těch 100.000 příkazů, které se liší jen vkládanými hodnotami? V přístím dílu seriálu zkusíme serveru ušetřit tuto práci díky použití vázaných proměnných a uvidíte, že rázem budeme celkovou dobu zpracování měřit ve velmi malých desítkách vteřin.

Comments:

Nabízí se použití vázaných proměnných (prepared statement) - předpokládám, že takto by (měla) postupovala většina programátorů, kteří mají alespoň základní zkušenost s vývojem rozsáhlejší aplikace. Další volba je podle mě "batch" (kdy se sestaví kolekce insertů a pošle se jedním voláním databáze). Velmi by se mi líbí přístup, kdy aplikační vrstva předá kolekci záznamů proceduře/funkci (pomocí PL/SQL table), která si zajistí práci s daty sama. Ideálně udělá bulkový zápis (FORALL) do databáze. Logika vlastního zápisu je v databázi a v případě potřeby můžeme ladit datovou vrstvu nezávisle na zbytku aplikace (prostřednictvím kvalitních ladících nástrojů jako STATSPACK apod.). Mohlo by dojít i k experimentováním s asynchronním commitem (COMMIT NOWAIT) případně dávkovým zápisem do souborů (COMMIT BATCH). Zde mě napadají dvě nevýhody: přenáším commit do databáze (ideálně by měla commit řídit aplikační vrstva) a není to úplně "bezpečné" (není potvrzeno, že k zápisu dat do logů skutečně došlo - pokud dojde při zápisu k chybě, aplikace se to nedozví). Na okraj stojí za zmínku, jestli se skutečně jedná o transakční zpracování (100 000 tis. záznamů je mnoho) a ne o synchronizaci nebo loadování dat, které se provádí nárazově. Pokud se nejedná o transakční systém, bylo by nejefektivnější data natáhnout do databáze pomocí SQL*Loaderu nebo skrz tzv. "externí tabulku".

Posted by stepan on květen 06, 2010 at 06:03 dop. CEST #

Díky za komentář (vlastně je to první komentář na tomhle blogu - takže vám večer připiju na zdraví). Máte pravdu, použití vázaných proměnných a batchování/posílání dat mnoha záznamů v poli... to jsou přesně ty kroky, ktere budou v dalších dílech, jen co je zformátuju do blogu. Byl bych rád, kdyby platila vaše věta "předpokládám, že takto by postupovala většina programátorů" pak by tyto články neměly smysl. Zkušenosti bohužel ale ukazují, že je pořád dost těch, kteří raději navkládají hodnoty jako literály přímo do textu SQL a (ideálně se zapnutým autocommitem - pod heslem "žádné transakce, ty zdržují") vkládají data do databáze záznam po záznamu. Tak jsem si říkal, že snad někoho přesvědčí ukázka o kolik se mohou operace zrychlit, aniž by se musela zásadně změnit stávající architektura. Osobně si myslím, že už asi nebude takový rozdíl mezi hromadnou operací z aplikace (batch/pole), nebo v PL/SQL (FORALL, BULK COLLECT), důležité je, aby pokud je dat víc, to byla nějaká hromadná operace. Jinak ale samozřejmě přístup "zapouzdření logiky do PL/SQL" podporuji a souhlasím se všemi vašimi argumenty. COMMIT BATCH, resp. NOWAIT - mohou určitě také pomoct. Ale vidím to spíše jako řešení v druhém sledu - tj. když buď musím data zpřístupnit ostatním transakcím rychleji a proto musím často commitovat, nebo pokud už aplikace prostě je napsaná tak, že často commituje a není v současnosti možné to přepsat. Jinak opět souhlasím s výhradami (NOWAIT z definice nezaručuje okamžitou perzistenci dat) i s tím, že by COMMIT většinou měla řídit aplikace. Souhlasím i pokud jde o to, že 100.000 záznamů už není úplně typická úloha pro OLTP. Někdy ale stovky i tisíce záznamů najednou vkládáte. S menšími objemy dat jsem se ale při použití jednoho spojení dostal na těžko měřitelné časy. Paralelní práce více spojení typická pro OLTP by pak zase už trošku komplikovala ukázky kódu. Ale možná se k tomu také jednou dokopu. Šlo mi vlastně o to říct "zamyslete se, jestli vaší logiku nejde převést na něco takového. Pokud ano, tady vidíte možné přínosy, pokud ne, uvědomte si, co za to platíte". Uznávám, že jde o trošku "školní" příklad.

Posted by david.krch on květen 06, 2010 at 06:33 dop. CEST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Česky o všem co se točí kolem Oracle Database.

Autoři:

Patrik Plachý
Technology Sales Consultant

David Krch
Principal Consultant
Oracle Expert Services

Oracle Czech

Search

Archives
« duben 2014
PoÚtStČtSoNe
 
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
    
       
Today