Automatická analýza výkonnosti databázových serverů. Jiří Rudolf

České vysoké učení technické v Praze Fakulta elektrotechnická Katedra počítačů

Bakalářská práce

Automatická analýza výkonnosti databázových serverů Jiří Rudolf

Vedoucí práce: Ing. Tomáš Siegl

Studijní program: Softwarové technologie a management, Bakalářský Obor: Softwarové inženýrství 25. května 2010

iv

v

Poděkování Děkuji své ženě Janě za trpělivost a pochopení, které při tvorbě této práce i během celého studia se mnou měla. Děkuji také vedoucímu práce Ing. Tomáši Sieglovi za vedení práce a především pak za nápad a možnost odborně zpracovat téma, které je mi blízké a v mé profesi konzultanta databázových serverů Microsoft velice přínosné.

vi

vii

Prohlášení Prohlašuji, že jsem práci vypracoval samostatně a použil jsem pouze podklady uvedené v přiloženém seznamu. Nemám závažný důvod proti užití tohoto školního díla ve smyslu §60 Zákona č. 121/2000 Sb., o právu autorském, o právech souvisejících s právem autorským a o změně některých zákonů (autorský zákon).

V Hradci Králové dne 25. 5. 2010

.............................................................

viii

Abstract The subject of this study is to find the way, how to analyze database server in order to determine whether the server’s performace is sufficient to workload generated by applications. The study is related only to database servers with IBM PC architecture and operating system Microsoft Windows Server and database system Microsoft SQL Server 2000 and later. The goal is to find proved methodology of measure server’s performance, which help determine whether the server satisfies the performance application’s requirements and identifies bottle in hardware configuration of the server. Based on this methology the study decribes design an application, which will measure and analyze performance automatically. Analysis will be supported by data measured on the real server workload. The primary objective of the analysis is practical usage in troubleshooting long time application response on which database server performance can have negative impact.

Abstrakt Cílem této práce je najít vhodný způsob analýzy výkonnosti databázového serveru tak, aby bylo možné určit, zda databázový server dostačuje svými hardwarovými parametry zpracovat zátěž, kterou na něj aplikace generují. Práce se omezuje na databázové servery s architekturou IBM PC, s operačním systémem typu Microsoft Windows Server a databázovým serverem verze Microsoft SQL 2000 Server a vyšší. Cílem je tedy najít spolehlivou metodiku měření zatížení serveru, ze které bude možné určit, zda server vyhovuje potřebám aplikací a která případně odhalí slabé místo v hardwarové konfiguraci serveru. Na základě této metodiky práce popisuje návrh aplikace, která bude měření a analýzu výkonnosti provádět automatizovaně. Analýza se bude opírat o reálná data naměřená z provozu SQL serveru a jejím hlavním přínosem je praktické využití při řešení problémů s pomalou odezvou aplikací, na kterou může mít slabý výkon databázového serveru vliv.

ix

x

Obsah 1 Úvod

1

2 Specifikace cíle a požadavků na návrh

3

3 Popis nástrojů pro měření zatížení serveru 3.1 Popis System Monitor . . . . . . . . . . . . . . . . . . . . . . . . . . . 3.1.1 Popis vybraných čítačů System Monitor . . . . . . . . . . . . . 3.2 Popis SQL Profiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3.2.1 Popis vybraných Event Class v SQL Profiler . . . . . . . . . . . 3.2.2 Popis vybraných atributů událostí Data Column v SQL Profiler

. . . . .

. . . . .

. . . . .

. . . . .

4 Návrh metodiky měření a analýzy dat zatížení serveru 4.1 Návrh způsobu měření . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.1.1 Časový interval měření . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.1.2 Nastavení měření System Monitor . . . . . . . . . . . . . . . . . . . . 4.1.3 Nastavení měření SQL Profiler . . . . . . . . . . . . . . . . . . . . . . 4.2 Návrh analýzy naměřených dat . . . . . . . . . . . . . . . . . . . . . . . . . . 4.2.1 Návrh analýzy zatížení hardware serveru . . . . . . . . . . . . . . . . . 4.2.2 Analýza zatížení hardware serverů JUPITER a SQL . . . . . . . . . . 4.2.3 Návrh analýzy hledání závislostí měřených veličin . . . . . . . . . . . . 4.2.4 Analýza vzájemné závislosti atributů měření serverů JUPITER a SQL pomocí korelačního koeficientu . . . . . . . . . . . . . . . . . . . . . . 4.2.4.1 Závěr . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.2.5 Návrh analýzy hledání závislostí měřených veličin pomocí SOM sítě . . 4.2.6 Analýza vzájemné závislosti atributů měření serverů JUPITER a SQL pomocí SOM sítě . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.2.6.1 Vyhodnocení měření serveru JUPITER pomocí SOM sítě . . 4.2.6.2 Vyhodnocení měření serveru SQL pomocí SOM sítě . . . . . 4.2.7 Hledání problematických SQL transakcí . . . . . . . . . . . . . . . . . 4.2.7.1 Vyhodnocení problematických SQL transakcí na serveru Jupiter 4.2.7.2 Vyhodnocení problematických SQL transakcí na serveru SQL

5 5 5 8 9 9 11 11 12 12 14 15 15 16 18 19 20 20 21 21 26 29 29 32

5 Návrh nástroje pro automatické měření a analýzu zatížení serveru 37 5.1 Seznam funkčních a nefunkčních požadavků . . . . . . . . . . . . . . . . . . . 37 5.1.1 Funkční požadavky . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 5.1.2 Nefunkční požadavky . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40

xi

xii

OBSAH

5.2 5.3 5.4 5.5

Návrh Návrh Návrh Návrh 5.5.1 5.5.2

modelu jednání . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . architektury aplikace . . . . . . . . . . . . . . . . . . . . . . . . . . . . automatizovaného vyhodnocení na základě zadaných expertních pravidel použitých technologií . . . . . . . . . . . . . . . . . . . . . . . . . . . . Programový přístup k výkonnostním čítačům . . . . . . . . . . . . . . Programovým přístup k trasování SQL serveru . . . . . . . . . . . . .

6 Ověření funkčnosti nástroje 6.1 Nastavení aplikace . . . . . . . . . . . . 6.2 Měření čítačů . . . . . . . . . . . . . . . 6.3 Trasování transakcí . . . . . . . . . . . . 6.4 Automatizované vyhodnocení ze souboru 6.5 Ověření funkcionality měření dat . . . . 6.6 Závěr . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . expertních pravidel . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

40 41 41 42 42 43 45 45 45 47 50 51 51

7 Závěr

53

Literatura

55

A Transformační SQL skripty A.1 SQL skript pro vytvoření souhrnu průměrných hodnot čítačů System Monitor A.2 Skript na transpozici čítačů z řádků do sloupců . . . . . . . . . . . . . . . . . A.3 Skript sloučení tabulek SQL Profiler a System Monitor. . . . . . . . . . . . . A.4 Skript na výpis seznamu transakcí s celkovou nejvyšší dobou zpracování . . .

57 57 58 59 60

B Tabulky B.1 Matice korelačních koeficientů JUPITER . . . . . . . . . . . B.2 Matice korelačních koeficientů SQL . . . . . . . . . . . . . .

61 dat SQL Profiler a System Monitor pro server . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 dat SQL Profiler a System Monitor pro server . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

C Matlab skripty 65 C.1 Matlab skript pro vytvoření a vizualizaci SOM sítě. . . . . . . . . . . . . . . . 65 D XML soubory 69 D.1 Příklad souboru expertních znalostí pro databázové servery . . . . . . . . . . 69 E Seznam použitých zkratek

75

F UML diagramy 77 F.1 Model jednání . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 G Obsah přiloženého CD

85

Seznam obrázků 4.1 4.2 4.3

Vyhodnocení průměrných hodnot čítačů na serveru JUPITER . . . . . . . . . Vyhodnocení průměrných hodnot čítačů na serveru SQL . . . . . . . . . . . . Nalezená podobnost SOM map atributů SQL Profiler Duration, Reads, Writes na serveru Jupiter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.4 Nalezená podobnost SOM map čítačů serveru Jupiter zpoždění čtení na disky 0 C: a 2:F a volné paměti. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.5 Nalezená podobnost SOM map čítačů počtu operací čtení a zápisu na disku 0 C: se stránkováním do paměti. . . . . . . . . . . . . . . . . . . . . . . . . . 4.6 Nalezená podobnost SOM map čítače počtu operací zápisu na disku 2 F: se zatížením CPU. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.7 U matice a mapa vzdálenosti ukazuje na podobnost s vytížením IO operacemi na disku 0 C: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.8 SOM mapy atributů SQL Profiler Duration, Reads, Writes na serveru SQL. . 4.9 SOM mapy atributů čítačů volné paměti a stránkování serveru SQL. . . . . . 4.10 Nalezená podobnost SOM map čítačů disků 0 C: D: a 1 E: na serveru SQL. . 4.11 Nalezená podobnost SOM map čítačů počtu a zpoždění operací čtění na disk 1 E: na serveru SQL a SOM mapa čítače zatížení CPU. . . . . . . . . . . . . 4.12 U-matice a mapy vzdáleností ukazuje na podobnost výskytu hodnot čítačů disků 0 C: D: a 1 E:, viz. obrázek 4.10 . . . . . . . . . . . . . . . . . . . . . .

17 18 22 23 24 25 25 26 27 27 28 28

5.1 5.2

Prostředí aplikace SqlMonitor, serverové role. . . . . . . . . . . . . . . . . . . 42 Přehled všech funkčních požadavků . . . . . . . . . . . . . . . . . . . . . . . . 44

6.1 6.2 6.3 6.4 6.5

Konfigurace a nastavení serverových rolí aplikace SqlMonitor. . . . . . . . . . Nastavení a měření čítačů. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Měření čítačů. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Trasování SQL transakcí bez nastavení filtru Duration. . . . . . . . . . . . . . Výpočet průměrných hodnot naměřených čítačů a automatizované vyhodnocení.

46 47 48 49 51

F.1 F.2 F.3 F.4 F.5 F.6 F.7

Model Model Model Model Model Model Model

78 79 80 81 82 83 84

jednání jednání jednání jednání jednání jednání jednání

-

Sestavení souboru expertních znalostí. Inicializace připojení. . . . . . . . . . Nastavení měření čítačů. . . . . . . . . Nastavení měření SQL transakcí. . . . Ovládání měření. . . . . . . . . . . . . Vyhodnocení čítačů. . . . . . . . . . . Vyhodnocení SQL transakcí. . . . . .

xiii

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

xiv

SEZNAM OBRÁZKŮ

G.1 Seznam přiloženého CD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85

Seznam tabulek 3.1 3.2

Přehled IOPS výkonů vybraných disků . . . . . . . . . . . . . . . . . . . . . . Výpočet zátěže IOPS na jeden disk. [1] . . . . . . . . . . . . . . . . . . . . . .

7 8

4.1 4.2

Čítače System Monitor vhodné pro analýzu databázového serveru . . . . . . . Seznam SQL transakcí serveru Jupiter s celkovou nejdelší dobou zpracování Duration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Seznam SQL transakcí serveru Jupiter s nejvyšším zpožděním operací čtení z disku F: - čítač Avg \\JUPITER\PhysicalDisk: Avg. Disk sec/Read: 2 F:, označený jako Avg Disk Delay Read F: . . . . . . . . . . . . . . . . . . . . . Seznam SQL transakcí serveru SQL s celkovou nejdelší dobou zpracování Duration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Seznam SQL transakcí serveru SQL s nejvyšším stránkováním mezi paměti a pevným diskem - čítač \\SQL \Memory: Pages/sec, označený jako Avg Pages/sec: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

13

4.3

4.4 4.5

xv

30

31 33

34

xvi

SEZNAM TABULEK

Kapitola 1

Úvod Rychlost odezvy databázových aplikací je dána mnoha faktory, jedním z nich je výkon databázových serverů. Jako konzultant na databázové systémy společnosti Microsoft se často u zákazníků setkávám s potřebou určit příčinu pomalé odezvy aplikace, především posoudit vliv výkonnosti databázového serveru. Prokázání případně vyloučení tohoto vlivu je třeba provést způsobem, který je důvěryhodný a nezpochybnitelný. Pokud se prokáže, že databázový server negativně ovlivňuje rychlosti aplikace, je nutné vyvodit závěr a doporučení, které vedou ke nápravě problému.

Nejčastější problémy na straně databázového serveru jsou:

Neoptimalizované SQL transakce. Příkazy, jejichž doba zpracování je příliš dlouhá, protože zbytečně načítají velká množství dat nebo nevhodně spojují data nebo nevhodně nastavují zámky nad databázovými objekty.

Nevyužité indexy. Doba zpracování transakcí je výrazně delší pokud chybějí nebo jsou nevhodně navrženy indexy.

Nedostatečná výkonnost databázového serveru. Zjistí se úzké hrdlo v hardwarové konfiguraci serveru.

Při hledání příčin pomalé odezvy databázových aplikací se provádí hloubková analýza databázového serveru, kde se provádí měření zatížení serveru a zaznamenávají se informace o SQL transakcích. Většina problémů se právě určuje z těchto měření. Zjištěné závěry musejí být absolutně správné, protože na jejich základě zpravidla zákazník vydává nemalé investice do úpravy aplikace nebo do posílení hardware či nákupu úplně nového databázového serveru. Některá řešení problémů vyžadují pořízení drahých a vysoce výkonných diskových systémů.

1

2

KAPITOLA 1. ÚVOD

Cílem této bakalářské práce je proto stanovit vhodnou metodiku pro měření zatížení a zaznamenání SQL transakcí a prozkoumat možnosti analýzy dat z těchto měření, tak aby závěry analýzy byly přesné a nezpochybnitelné. Dalším cílem je provést návrh softwarového nástroje, který bude automatizovaně provádět tato měření a analýzy. Použití takového nástroje v praxi významně zkrátí dobu a pracnost analýzy serveru a umožní provést analýzu i méně zkušeným správcům databázových systémů.

Kapitola 2

Specifikace cíle a požadavků na návrh Cíle jsou definovány a strukturovány v této práce takto: Kapitola 3 - Popis nástrojů pro měření zatížení serveru • Prozkoumat a popsat standardní nástroje pro měření databázových sytémů Microsoft SQL Server verze 2000, 2005 a 2008. Jedná se o nástroje System Monitor (dříve označovaný jako Performance Monitor) a SQL Profiler, které jsou součástí databázového serveru. • Popsat čítače operačního systému Windows, které se pro výkonnostní analýzu nejvíce používají a na základě praktických znalostí a doporučení společnosti Microsoft určit jejich mezní hodnoty. • Určit a popsat události a jejich atributy, které je vhodné v SQL Profileru zaznamenávat. Kapitola 4 - Návrh metodiky měření a analýzy dat zatížení serveru • Návrh konečného seznamu čítačů System Monitoru a událostí SQL Profileru, které je nutné pro výkonnosntí audit zaznamenávat. • Stanovit způsob měření a zaznamenání hodnot měření. Určit intervaly vzorkování měření. • Provést zkušební měření z reálného provozu více databázových serverů. Na základě dat z těchto měření následně budou zkoumány a porovnávány různé metody analýzy dat. • Určit správný způsob transformace a spojení dat z měření System Monitor a SQL Profiler, tak aby bylo možné provést jejich analýzu. • Na naměřených hodnotách čítačů operačního systému najít nevyhovující, popřípadě nejvíce zatěžované komponenty hardware serveru. • Na naměřených datech testovat a porovnat dvě vybrané metody vytěžování dat. Smyslem těchto analýz je hledat závislosti mezi vytěžováním komponent hardware serveru a dobou trvání zpracování SQL transakcí.

3

4

KAPITOLA 2. SPECIFIKACE CÍLE A POŽADAVKŮ NA NÁVRH

• Na naměřených datech navrhnout spolehlivou statistickou metodiku hledání konkrétních SQL transakcí, které souvisí s nadměrným vytěžováním komponent hardware serveru. Kapitola 5 - Návrh nástroje pro automatické měření a analýzu zatížení serveru • Na základě zjištěných skutečností provést návrh softwarového nástroje pro automatizované měření a analýzu zatížení Microsoft SQL Serveru. Tento nástroj bude mít možnost v otevřeném formátu importovat expertní pravidla, na jejichž základě vyhledá výkonově nevyhovující hardwarové komponenty serveru. V dalším kroku podle uživatelských předvoleb nabídne možnost hledání problematických SQL transakcí. Kapitola 6 - Ověření funkčnosti nástroje • Podle návrhu vytvořit aplikaci, na které budou otestovány základní funkce aplikace měření čítačů a zaznamenávání probíhajících transakcí. Kapitola 7 - Závěr • Shrnutí zkoumaných metod měření a analýzy dat. • Zhodnocení nástroje pro automatizované měření a analýzu. • Náměty na další rozvoj analýz.

Kapitola 3

Popis nástrojů pro měření zatížení serveru Základní podmínkou pro zkoumání zatížení databázového serveru je dobrá znalost monitorovacích nástrojů a především znalost měřených parametrů System Monitor a SQL událostí v SQL Profiler. Pomocí těchto expertních znalostí je možné z mnoha hodnot měření vyvodit závěry o výkonu databázového serveru. Podmínkou pro takovou analýzu je to, že zkoumaný server musí být výlučně vyhrazen jako databázový. Pokud na serveru běží další aplikace nebo procesy, které generují další zátěž, není možné hledat a určovat problematické SQL transakce.

3.1

Popis System Monitor

Výkon Windows Serveru je možno monitorovat nástrojem, který je součástí operačního systému - System Monitor, dříve Performance Monitor. Tento nástroj zaznamenává hodnoty tzv. čítačů v reálném čase. Některé čítače jsou součástí operačního systému a mimo jiné slouží pro monitorování vytěžování hardwarových komponent a některé jsou součástí instalovaných aplikací. Čítače jsou tedy volně rozšiřitelné [11]. U většiny čítačů jsou stanoveny doporučené a mezní hodnoty, díky kterým je možné určit problematické místo serveru, či aplikace. Měření ze System Monitor je možné zaznamenávat do souboru vlastního formátu nebo do SQL databáze. Interval měření je možné nastavit od jedné sekundy a výše. Měřit lze bezobslužně pomocí plánovače, který měření v předepsaném čase spustí a ukončí. Monitorovat lze lokální i vzdálené servery. Měření zaznamenaná do vlastního souboru lze v System Monitor později opětovně načíst a prozkoumat. Nástroj nabízí základní vyhodnocení čítačů - jejich minimální, maximální a průměrné hodnoty.

3.1.1

Popis vybraných čítačů System Monitor

Čítače System Monitoru jsou kategorizovány do aplikačních nebo komponentových celků. Nejznámější kategorie jsou například Memory, Physical Disk, Logical Disk, Processor, SQL Server. Každá kategorie obsahuje samotné čítače dané kategorie, například Memory: Page/sec, PhysicalDisk: % Disk Time a každý čítač může mít několik instancí, to je například konkrétní

5

6

KAPITOLA 3. POPIS NÁSTROJŮ PRO MĚŘENÍ ZATÍŽENÍ SERVERU

hardware komponenta, například čítač PhysicalDisk: Disk Writes/sec: 0 C: D: měří počet operací zápisu na disk 0, který má obsahuje logické disky C: a D:. Kategorie, čítač a instance se v této práci slučuji do jediného označení, kde jsou odděleny znakem dvojtečky. U popisu konkrétního měření je před tímto složeným názvem uveden ještě název serveru s dvojitým zpětným lomítkem před názvem a jedním za názvem serveru. Například čítač \\SQL \PhysicalDisk: Disk Writes/sec: 0 C: D: označuje čítač naměřený na serveru SQL.

Memory: Available Bytes. Pokud nemá databázový server nastaveno omezení využití paměti pomocí parametru Maximum Server Memory, pak se snaží dostupnou fyzickou paměť serveru využívat co nejvíce. Zpravidla přitom ponechává rezervu cca 140MB. Této rezervě odpovídá hodnota čítače Memory: Available Bytes. Pokud hodnota tohoto čítače klesne pod 50MB [1] znamená to, že server má nedostatek fyzické paměti.

Memory: Pages/sec. Čítač udává počet I/O operací, které musely být přeneseny mezi operační pamětí a pevným diskem. Pokud je hodnota vyšší než 20 stránek za sekundu [4], pak toto stránkování negativně ovlivňuje výkonnost databázového server a může ukazovat na nedostatek paměti. Tyto operace jsou označovány jako hard page faults.

Memory: Page Faults/sec značí počet pamětových stránek, které nebyly přístupné v pracovním virtuálním prostoru fyzické paměti a musely být do něj načteny. Jedná se o součet tzv. soft page faults, což jsou stránky umístěné v jiné části paměti a jejich přečtení je rychlé a hard page faults, což jsou stránky, které musely být načteny z pomalého pevného disku. Při vysokých hodnotách stránkování hard page faults se zjišťuje, zda tyto hodnoty nesouvisí s Memory: Page Faults/sec, což ukazuje, že běžící aplikace trpí nedostatkem paměti.

Network Interface: Bytes Total/sec je čítač, který může ukázat nedostatečnou průchodnost dat přes síťový adaptér. Mezní hodnota se určuje podle přenosové rychlosti rozhranní. Pro adaptér 100 Mb/s by měla být hodnota čítače menší než 7 MB/s, pro 1000 Mb/s menší než 70 MB/s.

PhysicalDisk: % Disk Time určuje zatížení pevného disku. Jeho hodnota by trvale neměla překračovat 90% [6]. U některých speciálních diskových systémů jako SAN pole s velkou vyrovnávací cache pamětí má čítač hodnoty vyšší než maximum 100% [6]. V takovém případě nelze čítač posuzovat.

PhysicalDisk: Avg. Disk Queue Length znamená kolik průměrně I/O operací za sekundu čeká v systémové frontě na uložení na pevný disk. Při vyšším zatížení jeho hodnota roste. Neměla by ale překračovat hodnotu 2 [6]. Pokud je měřený disk tvořen několika disky v RAID uspořádání, pak se jako mezní hodnota bere násobek počtu disků a hodnoty 2. Například RAID-1 složený ze dvou disků by neměl mít vyšší hodnotu než 4. PhysicalDisk: Avg. Disk sec/Read měří průměrné zpoždění I/O operace čtení z pevného disku v sekundách. U databázového serveru by tato hodnota neměla být trvale vyšší než 17 ms.

3.1. POPIS SYSTEM MONITOR

Disk Seagate Seagate Seagate Seagate Seagate Seagate

Cheetah 15K.7 SAS Savvio 15K.2 SAS Cheetah NS 10K.2 SAS Savvio 10K.4 SAS Barracuda 36ES2 7200K SCSI EE25 5400.2

7

Read IOPS 8kb 380 [8] 410 [14] 320 [7] 240 [13] 140 [5] 70 [18]

Použití servery servery servery servery servery notebooky

Tabulka 3.1: Přehled IOPS výkonů vybraných disků

PhysicalDisk: Avg. Disk sec/Write podobně jako v předchozím případě určuje průměrné zpoždění diskové I/O operace, v tomto případě se jedná o operace zápisu. Podobně by neměla být hodnota čítače trvale vyšší než 17 ms.

PhysicalDisk: Disk Reads/sec čítač monitoruje počet I/O operací načtených z pevného disku za sekundu.

PhysicalDisk: Disk Write/sec jako v předchozím případě uvádí počet I/O operací, ale zapsaných na pevný disk za sekundu. Čítače PhysicalDisk: Disk Reads/sec a PhysicalDisk: Disk Write/sec přímo odráží výkonnost disku nebo diskového pole běžně uváděnou jako IOPS - v překladu Input Output Operations per Second. Pro databázové servery, které produkují velký počet malých operací je toto nejdůležitější výkonnostní diskový parametr. U diskových operací se rozlišuje, zda jsou typu náhodného, tzv. random - blok operací je proveden na různá místa na disku nebo sekvenčního přístupu, tzv. sequential, kdy jsou data ukládána nebo načtena za sebou. Microsoft SQL Server generuje na soubory databází operace náhodného přístupu a na soubory transakčních logů operace sekvenčního typu. [2] Určení zda je z čítačů PhysicalDisk: Disk Reads/sec a PhysicalDisk: Disk Write/sec disková zátěž nadměrná, je u diskových polí závislé na IOPS výkonu použitých disků a jejich RAID uspořádání. Kromě toho u diskového pole vybaveného vlastní vyrovnávací pamětí cache dochází k vylepšení IOPS výkonu rychlým přístupem k datům do této paměti. V tabulce 3.1 je uveden dosažitelný výkon IOPS pro nejnovější disky společnosti Seagate. Nové specializované disky Cheetah [8] a Savvio [14] právě určené pro databázové systémy nabízí nejvyšší výkon až 400 IOPS. Nicméně pro starší disky SAS, SCSI se tento výkon pohybuje v rozmezí 110 - 140 [5] podle rychlosti otáček a přístupové doby. Disky SATA, které nabízejí vyšší kapacitu a vyšší průchodnost jsou pro dabázové systémy nevhodné, protože poskytují výkon pouze okolo 60 - 80 IOPS [18]. Výpočet mezních hodnot čítačů PhysicalDisk: Disk Reads/sec a PhysicalDisk: Disk Write/sec se provádí podle typu RAID uspořádání a určuje jaké je zatížení IOPS na jeden disk. V tabulce 3.2 jsou uvedeny vzorce výpočtu zatížení na jeden disk u nejznámnějších RAID uspořádání [1]. Tyto vzorce reflektují tzv. penalizaci IO operací, která je daná nutnosti zapsat

8


Typ RAID RAID-0 RAID-1 RAID-5 RAID-10

Read IOPS 8kb (reads + writes) / počet disků [reads + (2 ∗ writes)]/2 [reads + (4 ∗ writes)]/ počet disků [reads + (2 ∗ writes)]/ počet disků

Tabulka 3.2: Výpočet zátěže IOPS na jeden disk. [1] paritní bloky dat. Výpočet pak porovnáváme s dosažitelným IOPS výkonem použitého disku. Pokud jej neznáme, pak zpravidla porovnáváme s výkonem průměrného disku 110 IOPS.

Processor: % Processor Time čítač určuje zatížení procesoru serveru. Hodnota by se neměla trvale pohybovat nad 75%.

System: Processor Queue Length čítač indikuje součet vláken procesů, které musí čekat na zpracování CPU. Pro každý procesor by neměla být hodnota vyšší než 2.

SQLServer:Buffer Manager: Buffer cache hit ratio je čítač SQL serveru, který pomáhá indikovat nedostatek paměti RAM. Ukazuje s jakou úspěšností SQL Server nachází požadovaná data v cache paměti SQL Serveru. U standardních instalací by neměla trvale přesahovat 99%.

SQLServer:General Statistics: User Connections je pouze informační čítač, který detekuje počet uživatelů připojených do SQL relací databázového serveru.

SQLServer:Locks: Number of Deadlocks/sec je pomocný ukazatel, který udává počet vzájemných zámků na SQL objekty, tzv. Dead Lock. Tímto se jednoduše monitoruje problémy s odezvou SQL aplikace, které nejsou způsobeny výkonem serveru, ale těmito zámky.

3.2

Popis SQL Profiler

Tento nástroj slouží především pro tzv. trasování SQL serveru, to je zaznamenávání různých událostí, jako je zpracování DDL, DML příkazů, spuštění uložených procedur, audit přístupu uživatelů, přihlašování uživatelů a další. SQL Profiler je součástí SQL Serveru edicí Standard Edition a vyšších. Trasování SQL událostí u nižších edicí je možné spuštěním uložené procedury SQL sp_trace_create, která ukládá data z trasování do souboru. Při trasování SQL Profiler je možné data ukládat do souboru nebo databáze.

3.2. POPIS SQL PROFILER

9

SQL Profiler používá následující terminologii: • Event je konkrétní akce, která na databázovém serveru probíhá. Jedná se o SQL příkazy, uložené procedury, zamykání objektů a další. • Event Class jedná se o množinu událostí, které jsou podobného typu. Příkladem je TSQL: SQL:BatchCompleted nebo Stored Procedures: RPC: Completed, které zaznamenávají událostí SQL DML příkazů a spouštění uložených procedur. • Event Category je seskupení Event Class do skupin podle typu objektů. Příkladem jsou SQL, Session, Stored Procedures. • Data Column je atribut zachyceného záznamu. Například TextData je konkrétní příkaz, StartTime je čas začátku události, Duration je čas trvání zpracování události. • Filter je omezení pro konkrétní atribut měření Data Column, kde lze nastavit typ podmínky numerického porovnání jako větší, menší nebo hledání znakových řetězců. Po nastaveném Filter se zachytí pouze události, které splňují podmínku. Každá Event Class má své vlastní specifické Data Column. Například Security Audit: Audit Login nemá atribut CPU, který se používá pro určení využití CPU SQL událostí zachycenou jako TSQL: SQL:BatchCompleted.

3.2.1

Popis vybraných Event Class v SQL Profiler

TSQL: SQL:BatchCompleted je Event Class, který zachycuje události všech DML a DDL příkazů, které byly na serveru zpracovány a dokončeny.

Stored Procedures: RPC: Completed je skupina událostí zpracování všech typů uložených procedur.

Security Audit je Event Category, který zachycuje veškeré změny v oprávnění přístupu uživatelů.

3.2.2

Popis vybraných atributů událostí Data Column v SQL Profiler

TextData je konkrétní SQL příkaz, tak jak jej odeslala na server aplikace. StartTime a EndTime je datum a čas začátku a konce zpracování události. Application Name je název aplikace, která příkaz generovala. Duration čas v milisekundách, který označuje dobu trvání zpracování příkazu. CPU čas v milisekundách, po který daná událost využívala procesoru.

10


Reads je počet paměťových stránek, které bylo pro daný příkaz nutno načíst. Tyto stránky mohly být načteny z pevného disku nebo z cache paměti serveru.

Writes je počet paměťových stránek, který zaznamenaný příkaz potřebuje zapsat. Zápis se nejprve provede do paměti serveru a souboru transakčního logu a později do databáze.

Kapitola 4

Návrh metodiky měření a analýzy dat zatížení serveru Pro stanovení a ověření navržené metodiky měření a analýzy zatížení databázových serverů byla provedena dvě nezávislá měření reálných SQL Serverů. Jedno měření bylo provedeno na serveru s názvem JUPITER, což je SQL Server 2005, který hostuje warehouse databázi pro generování reportů aplikace Microsoft System Center Operation Manager 2007 R2 [15]. Tato aplikace monitoruje, zaznamenává a proaktivně upozorňuje administrátora Windows Serverů o problémech v operačním systému, aplikacích a síťové infrastruktuře. Do databáze OperationManagerDW se generuje obrovské množství informací o každém monitorovaném serveru, jeho čítače výkonu, výpadky aplikací, vybrané záznamy z logu událostí operačního systému. Velikost databáze v době měření byla 15GB. Tento server běží jako virtuální a v době měření měl velké problémy s výkonem, dlouhou odezvou a vysokým zatížením disků. Cílem jeho měření a analýzy je prokazatelně určit souvislost mezi jeho vysokým zatížením hardware a běžícím SQL serverem a popřípadě najít konkrétní SQL transakce, které vysokou zátěž způsobují. Druhé měření bylo provedeno na centrálním SQL 2005 Serveru s názvem SQL, který hostuje databáze pro 5 aplikací a využívá jej okolo 50 uživatelů. Tento server běží jako fyzický a v době měření nevykazoval žádné velké výkonnostní problémy. Cílem jeho měření a analýzy je snaha zjistit, zda server svým výkonem skutečně vyhovuje potřebám aplikací, dále nalezení dlouhých transakcí a určení spojitosti mezi dobou jejich zpracování a zatížením hardware serveru.

4.1

Návrh způsobu měření

Měření je nutné provádět ze vzdáleného počítače, nejlépe jiného databázového serveru, měřit přes síť a záznamy z měření ukládat do databáze tohoto vzdáleného serveru. Díky tomu se předejde zkreslení zatížení měřeného serveru. Jak System Monitor tak SQL Profiler umožňují měřit vzdáleně.

11

12KAPITOLA 4. NÁVRH METODIKY MĚŘENÍ A ANALÝZY DAT ZATÍŽENÍ SERVERU

4.1.1

Časový interval měření

Pro každý analyzovaný databázový server je třeba určit interval měření zvlášt především s ohledem na předpokládanou dobu jeho zatížení. Pokud to není záměrem analýzy, tak je vhodné vynechat měření v čase zálohování nebo jiné údržby databáze, kdy dochází k velikému zkreslení zatížení databázového serveru, především na diskovém systému. V praxi se měření provádí v době zpracování účetních uzávěrek nebo kdy dochází k velkému zpracování dat, a kdy je odezva databázového serveru příliš dlouhá. Pro analýzu je vhodné provést měření alespoň po dobu 3 hodin. Měření serveru JUPITER probíhalo 16. 3. 2010 od 5:00 do 16:00. Měření serveru SQL probíhalo 17. 3. 2010 od 10:00 do 16:00.

4.1.2

Nastavení měření System Monitor

Pro pozdější analýzu je nutné záznamy z měření System Monitor uložit v SQL databázi. Nejprve je nutné databázi založit a na měřícím serveru nakonfigurovat pro tuto databázi datový zdroj ODBC typu DSN. Nejvhodnější je tuto databázi připravit na měřícím serveru. Potom je nutné v sekci Performance Logs and Alerts, Counter Logs vytvořit nastavení logování. Tady je nutné zadat jméno měřeného serveru a vybrat jeho konkrétní čítače. Dále se dá nastavit čas automatického spuštění a čas ukončení měření a zadat účet pod kterým proces měření poběží. Měření je možné spustit i ukončit ručně. Důležitým parametrem měření je vzorkování měření. Z důvodu největší přesnosti při hledání problematických transakcí je výhodné zvolit nejmenší možný 1 sekundu. Tím se v každé sekundě zaznamená hodnota všech nastavených čítačů. Nakonec je důležité změnit cíl logování na SQL databázi a zde nastavit název ODBC DSN datového zdroje. Po spuštění měření se v předdefinované databázi vytvoří tyto tabulky: • CounterData, kde je v každém řádku uložena čas a hodnota jednoho čítače. Dále je zde jako CounterID uložen cizí klíč identifikující typ čítače a konkrétní instanci. • CounterDetails je tabulka s popisem čítačů a jejich instancí. Určit slabou komponentu v hardware nelze podle jediného čítače, ale z kombinace čítačů. Například nadměrné hodnoty čítače Processor: % Processor Time nemusí indikovat nevyhovující výkon procesoru, ale můžou být zapříčiněny například nadměrným stránkováním a nedostatkem paměti. Proto je nutné posuzovat hodnoty čítačů jako celek. V tabulce 4.1 je uložen souhrnný přehled čítačů, které je pro výkonnostní analýzu databázového serveru vhodné zaznamenat. Jejich podrobný popis je uveden v kapitole 3.1.1.

4.1. NÁVRH ZPŮSOBU MĚŘENÍ

Čítač Memory: Available Bytes Memory: Page/sec Memory: Network Interface: Bytes Total/sec PhysicalDisk: % Disk Time PhysicalDisk: Avg. Disk Queue Length PhysicalDisk: Avg. Disk sec/Read PhysicalDisk: Avg. Disk sec/Write PhysicalDisk: Disk Reads/sec PhysicalDisk: Disk Write/sec Processor: % Processor Time System: Processor Queue Length SQLServer:Buffer Manager: Buffer cache hit ratio SQLServer:Locks: Number of Deadlocks/sec

13

Doporučené hodnoty větší než 140MB menší než 20 pro adaptér 100 Mb/s menší než 7 MB/s pro 1000 Mb/s menší než 70 MB/s menší než 90 menší než počet disků * 2

Instance

menší než 17 ms

Všechny využívané disky

menší než 17 ms

Všechny využívané disky

menší než 75 pro každý procesor menší než 2 větší než 99

Všechny využívané disky Všechny využívané disky

Všechny využívané disky Všechny využívané disky Total Processors

Tabulka 4.1: Čítače System Monitor vhodné pro analýzu databázového serveru


4.1.3

Nastavení měření SQL Profiler

Trasování SQL událostí pomocí SQL profiler musí probíhat souběžně s měřením čítačů System Monitor. Obě měření je vhodné provádět na vzdáleném měřícím serveru a data z obou měření ukládat do stejné databáze. Trasování SQL událostí je vhodné omezit pouze na ty, které mají dlouhou dobu odezvy. Proto se nastavuje Filter na atribut Duration. Stanovení hodnoty tohoto filtru by mělo odpovídat potřebám zákazníka, ten by měl určit, jaká doba odezvy je akceptovatelná a jaká nikoliv. Na serveru JUPITER byly zaznamenány SQL události, které měly dobu zpracování delší než 5 sekund a na serveru SQL události s dobou odezvy delší než 2 sekundy. Pro analýzu databázového serveru je vhodné nastavit tyto Event Class: • Stored Procedures–RPC:Completed zachytí události zpracování uložených procedur. • TSQL–SQL:Batch Completed zachytí události všech DDL a DML SQL příkazů.

Dále je vhodné zaznamenat tyto atributy - Data Column: • Duration - doba trvání zpracování příkazu nebo procedury. • DatabaseName - jméno databáze, nad kterou se příkaz provádí. • TextData - konkrétní podoba příkazu nebo uložené procedury, který databázový server zpracovává. • CPU - čas v milisekundách, po který daná událost využívala procesoru. • Writes - počet paměťových stránek, který zaznamenaný příkaz potřebuje zapsat. • Read - počet paměťových stránek, které bylo pro daný příkaz nutno načíst. Tyto stránky mohly být načteny z pevného disku nebo z cache paměti serveru. • StartTime - čas začátku zpracování transakce. • EndTime - čas dokončení transakce. • ApplicationName - název aplikace, která příkaz volá. • LoginName - název SQL účtu, pod kterým daná aplikace spouští. • SPID - číselný identifikátor transakce.

4.2. NÁVRH ANALÝZY NAMĚŘENÝCH DAT

4.2

15

Návrh analýzy naměřených dat

Prvním krokem při analýze výkonnosti databázového serveru je určení, zda komponenty hardware serveru vyhovují nebo nevyhovují kladené zátěži na serveru. Pokud nevyhovují, je nutné určit slabá místa v hardwarové konfiguraci serveru. Tento krok se provádí vyhodnocením čítačů System Monitoru. V druhém kroku se provede seskupení dat z měření System Monitor a SQL Profiler. Potom je možné následnou analýzou zjišťovat vzájemné závislosti jednotlivých atributů měření. Nejsledovanějším atributy by měly být atributy System Monitor, které překračují limitní hodnoty a především SQL Profiler atribut doby zpracování transakce Duration. K tomu lze použít různé techniky vytěžování dat. Jedna ze základních metod je statistická metoda hledání lineálrní závislosti. Druhou technikou, kde budeme hledat nelineární závislosti je neuronová samoorganizující síť SOM. K těmto operacím bude využit nástroj pro modelování dat Matlab R2009B [10]. Posledním krokem výkonnostní analýzy je hledání problematických SQL transakcí. Pokud se prokáže souvislost mezi dobou zpracování SQL transakcí a čítači System Monitor, potom je možné provést hledání konkrétních SQL transakcí, které souvisí s přetěžováním slabé komponenty serveru. V takovém případě se hledají transakce, které pobíhaly v době nejvyššího zatížení nevyhovující hardware komponenty. Použitou metodou pro hledání problematických transakcí je dotazování do tabulek měření pomocí příkazů SQL.

4.2.1

Návrh analýzy zatížení hardware serveru

V této analýze se určí čítače System Montor, které překračují přípustné hodnoty. Na základě expertních znalostí, kdy se čítače vyhodnocují komplexně, se určí nevyhovující hardwarové komponenty serveru. Před vyhodnocením je nutné provést úpravu formátu dat. Data z měření System Monitor jsou uložena v tabulce CounterData. Sloupec CounterDateTime tabulky CounterData obsahuje datum v nevhodném datovém typu CHAR(24). Pomocí skriptu 4.2.1 se provede jeho převod do formátu DATETIME.

UPDATE [dbo].[CounterData] SET [CounterDateTime] = CONVERT(CHAR(19), CounterDateTime); ALTER TABLE dbo.CounterData ALTER COLUMN CounterDateTime DATETIME; SQL Skript pro změnu tabulky CounterData Dalším krokem je sestavení přehledu souhrnných hodnot čítačů. Tento souhrn je proveden na základě průměrných hodnot. SQL Skript pro vytvoření souhrnu průměrných hodnot System Monitor je uvedený v příloze A.1.Tento skript uloží průměrné hodnoty čítačů do tabulky SqlAvgCounter. V dalším kroku se musí provést analýza vypočtených hodnot a označit čítače, které limitní hodnoty překročili. Tento krok je možné výrazně urychlit pomocí tabulkového procesoru Microsoft Excel, kde je možné pro analýzu čítače nastavit vzorový list,


který má nastavenou funkci Podmíněné formátování na limitní hodnoty čítačů. V tabulkách 4.1 a 4.2 jsou uvedeny výstupy z Excel listu.

4.2.2

Analýza zatížení hardware serverů JUPITER a SQL

V tabulce 4.1 je uveden přehled průměrných hodnot čítačů System Monitor na serveru JUPITER. Z ní je patrné, že u čítačů PhysicalDisk: Avg. Disk sec/Read na disku F: a C: došlo k výraznému překročení doporučené hodnoty 17ms. Průměrná hodnota pro disk F: je namísto toho 144ms a u disku C: 20ms. To znamená, že především na disku F: dochází k obrovským prodlevám při čtení z disku. Posouzení čítače PhysicalDisk: Disk Reads/sec není v tomto případě možné, protože disk je součástí velkého diskového pole sdíleného dalšími virtuálními servery a není známa jejich zátěžová charakteristika. Nicméně podle poměrně vysoké průměrné hodnoty 72 IOPS čtení a 136 IOPS zápisu lze přisoudit vliv nadměrného zatěžování diskového pole právě serveru JUPITER.


Obrázek 4.1: Vyhodnocení průměrných hodnot čítačů na serveru JUPITER

17


V tabulce 4.2 je uveden přehled průměrných hodnot čítačů serveru SQL. Hodnoty všech čítačů se pohybují v doporučených limitech. Jedinou vyjímkou je čítač Memory: Page/sec, který velikostí 168 stránek za sekundu výrazně překračuje doporučený limit 20 stránek za sekundu. Při podrobném zkoumání vyšlo najevo, že příčinou tohoto chování může být zálohování transakčních logů, které se na serveru provádí cyklicky denně každé 2 hodiny.

Obrázek 4.2: Vyhodnocení průměrných hodnot čítačů na serveru SQL

4.2.3

Návrh analýzy hledání závislostí měřených veličin

V tomto návrhu se pokusíme sestavit základní metodiku, která bude pomocí korelační analýzy hledat vzájemné závislosti mezi významnými atributy měření. Především půjde o


19

nalezení závislosti mezi dobou zpracování transakce Duration a zatížení hardware komponent serveru. K tomu je potřeba provést transformaci naměřených dat System Monitor, kde je v každém řádku tabulky CounterData uložena jedna hodnota čítače. Tuto tabulku je nejprve nutné transponovat tak, že jeden řádek tabulky bude obsahovat hodnoty všech čítačů naměřených v jedné sekundě. Každý sloupec transponované tabulky bude odpovídat jednomu čítači. Tento úkon lze automatizovaně provést SQL skriptem uvedeným v příloze A.2. Skript nejprve vygeneruje jako řetězec znaků komplexní příkaz T-SQL na transpozici všech čítačů z řádkové podoby do sloupců. V závěru skriptu je tento vygenerový příkaz aktivován procedurou EXEC (). Dalším krokem je sloučení nově vzniklé tabulky CounterDataPivot s tabulkou SQL Profiler. Tímto krokem dostaneme do jedné tabulky data z měření čítačů System Monitor a transakce z SQL Profiler. Spojení se musí provést na základě atributu času, v tabulce CounterDataPivot je pojmenován jako CounterDateTime a v tabulce SQL Profileru jsou atributy času dva - StartTime a EndTime. Spojení je tedy nutné provést na základě podmínky, že čas čítače CounterDateTime musí patřit do intervalu StartTime a EndTime. Záznamy SQL Profileru při této transformaci musí být zachovány a uvedeny pouze jednou. Při tomto spojení vychází pro jeden záznam SQL transakce několik záznamů čítačů a proto je v transformačním skriptu provedeno seskupení GROUP BY hodnot čítačů System Monitor podle jejich průměrných hodnot z doby trvání transakce. Skript uvedený v příloze A.3 provede popsanou transformaci do nové tabulky CounterDataProfiler. Lze očekávat, že hledaná vzájemná závislost všech číselných atributů uložených v tabulce CounterDataProfiler bude lineárního typu, proto pro její určení je možné provést statistickou metodou - korelační analýzou. Mezi dvěma veličinami se vyhodnocuje hodnota tzv. Paersonova korelačního koeficientu. Tento koeficient nabývá hodnot -1 až 1. V případě hodnot blízkých 1 nebo -1 určuje, že mezi dvěma náhodnými veličinami existuje přímá nebo nepřímá lineární závislost. Pokud se hodnota blíží nule, potom lze tvrdit, že mezi dvěma veličinami není lineální závislost, nicméně může mezi nimi existovat jiný typ závislosti. Síla korelace, tedy hodnota korelačního koeficientu se posuzuje individuálně.

4.2.4

Analýza vzájemné závislosti atributů měření serverů JUPITER a SQL pomocí korelačního koeficientu

V analýze těchto dvou serverů byla zaměřena pozornost na hledání závislosti atributu Duration. Číselná data jednotně sloučená v tabulce CounterDataProfiler byla hromadně importována do aplikace Matlab do matice, kde v každém sloupci byly uloženy hodnoty jednoho čítače. Vyhodnocení korelačního koeficientu bylo provedeno funkcí: corrcoef(Matice_Dat) Výstupem této funkce je čtvercová matice složená s hodnot korelačního koeficientu o rozměru odpovídajícímu počtu atributů. Každý řádek nebo sloupec matice vystihuje hodnoty korelačních koeficientů jednoho atributu ve vztahu k ostatním. Z uvedených hodnot matice korelačních koeficientů B.1 dat serveru JUPITER vyplývá, že nebyla zjištěna lineární závislost mezi daty ze SQL Profileru a System Monitoru. Existuje zde vzájemná vazba mezi atributy SQL Profiler - Duration, CPU, Reads a Writes, koeficient


vzájemné závislosti dosahuje hodnot 0,75 až 0,98. Další vztah exituje mezi čítači JUPITER PhysicalDisk: Disk Writes/sec: 0 C: a JUPITER PhysicalDisk: Disk Reads/sec: 0 C:. Výsledkem této analýzy je tedy zjištění, že atributy měření nejsou vzájemně lineárně závislé. Příčinou tohoto poznatku může být to, že vazby v atributech, ač by měly existovat, mohou být zkresleny vyrovnávací pamětí SQL Memory Buffer a Disk Queue, nebo jejich projev nastal s určitým časovým posuvem a sestavená data System Monitor a SQL Profiler tento časový posuv nereflektují. V matici koeficientů korelace dat B.2 na serveru SQL je možné objevit několik vztahů mezi atributy. SQL Profiler Writes - počet zapsaných operací má středně silnou závislost vyjádřenou koeficientem 0,72 s čítačem PhysicalDisk: Disk Writes/sec: 0 C: D:. To odpovídá skutečnosti, protože na zmíněném disku zapisují transakce do transakčních logů. Další vztahy jsou mezi čítači disků, a vyjadřují přirozený jev. Zatížení disku 0, kde jsou logické disky C: a D: s diskem 1 s logickým diskem E: souvisí - na prvním disku jsou uloženy transakční logy a na druhém databáze. Změny v datech databázový server aplikuje na oba disky.

4.2.4.1

Závěr

Metodu hledání vzájemných lineárních závislostí pomocí koeficientu korelace je možné použít jako nenáročnou metodu, která může poměrně rychle poskytnout pohled na to, který konkrétní čítač a SQL Profiler atribut jsou na sobě závislé. Nicméně předpoklad, že server JUPITER je zcela jistě nadměrně přetížen v operacích na disk a tento negativní jev by měl mít vliv na rychlost zpracování Duration, se touto metodou nepodařilo určit. Proto se na její výsledky nelze spoléhat jako úplné.

4.2.5

Návrh analýzy hledání závislostí měřených veličin pomocí SOM sítě

V této kapitole je popsáno, jak lze provést analýzu závislostí atributů měření System Monitor a SQL Profiler pomocí samoorganizující sítě SOM. Touto metodou je možné najít vztahy mezi atributy měření, které nemusejí být lineárně závislé, tak jako v případě Pearsonova korelačního koeficientu. Analýza byla provedena v programu Matlab s použitím doplňku SOM Toolbox 2.0 [3]. SOM síť je mapa složená z dat, kde se vyšší výskyt dat podobného charakteru projevuje větší hustotou sítě a naopak. Jedná se o typ neuronové sítě, která může být použita k vizualizaci vícerozměrných dat, hledání shluků nebo klasifikaci dat a pro další datové analýzy. Formování této sítě probíhá způsobem učení bez učitele, ke každému vektoru vstupních dat je přiřazen nejvíce podobný vektor sítě a jeho pozice včetně pozice okolních bodů se vychyluje směrem ke vstupnímu vektoru. Tím dochází ke shlukování v místech s podobnými datovými vektory a naopak k roztažení, kde se data objevují řídce. Kvalita sestavené SOM sítě je dána chybovosti modelu sítě SOM. Při ní se pro každý vektor vstupních dat určuje tzv. BMU - Best-Matching Unit, což je vektor sítě s nejpodobnějšími parametry a na základě jejich vzájemných odchylek se definuje tzv. Final quantization error. Kromě toho se ještě hodnotí tzv. Final topographic error, což je procento datových vektorů, jejichž první ani druhý BMU nejsou k datovým vektorům přilehlé.


4.2.6

21

Analýza vzájemné závislosti atributů měření serverů JUPITER a SQL pomocí SOM sítě

Sestavení a vizualizaci SOM sítě v Matlab SOM Toolboxu 2.0 [3] je možné provést podle uvedeného příkladu Matlab skriptu C.1. Procedura se provádí v několika krocích. Nejprve je z matice InputData, v níž jsou uložena spojená data System Monitor a SQL Profiler z tabulky CounterDataProfiler, vygenerována SOM datová struktura se vstupními daty, označená jako SomData. Do této struktury jsou naimportovány názvy atributů dat. V dalším kroku se provádí normalizace dat, tak aby se hodnoty atributů pohybovaly ve stejných intervalech číselných hodnot. Pomocí funkce som_make(SomData) se nakonec SOM síť vytvoří a natrénuje. V závěru skriptu jsou sestaveny grafické přehledy SOM sítě. Jedná se především o rozmístění vektorů vstupních dat v hexagonální struktuře SOM sítě. Na základě výskytu vyšších nebo v případě nepřímé závislosti nižších hodnot jednotlivých atributů ve stejné pozici SOM sítě, je možné najít existující vztah závislosti mezi atributy. Kromě toho je sestavena tzv. U-matice, což je zobrazení vzdálenosti jednotlivých mezi sebou přilehlých bodů SOM sítě. Pokud se v této matici objeví ohraničené úseky vyšších hodnot, pak se s největší pravděpodobností jedná o oblast datových vektorů, které spolu nějak souvisí. Tato oblast zpravidla definuje jakýsi shluk atributů, který se ostře liší od ostatních dat. Interpretace může být různá, například v klasifikaci dat může ukazovat na data se společným klasifikátorem dat. V analýze databázového serveru je hlavním cílem hledání závislosti atributu Duration doby zpracování SQL transakce na ostatních atributech dat. Tuto závislost můžeme hledat pomocí grafických přehledů SOM sítě tak, že určíme místo v SOM síti, kde se objevují nejvyšší hodnoty atributu Duration a v tom samém místě hledáme vysoký, popřípadě nízký výskyt hodnot jiného atributu. 4.2.6.1

Vyhodnocení měření serveru JUPITER pomocí SOM sítě

Pro server Jupiter bylo z vyhodnocení čítačů System Monitor 4.1 zjištěno, že nejvíce přetěžovanými komponentami serveru jsou disk F: a disk C:, které nezvládají zátěž generovanou operacemi čtení. Zároveň na tomto serveru byly detekovány velké problémy s odezvou zpracování SQL transakcí. Při hledání závislostí z mapy SOM sítě hledáme shodné barevné uspořádání dvou nebo více různých sítí jednotlivých atributů. U každé mapy s hexagovální strukturou je uveden sloupec, jehož zabarvení popisuje skutečné hodnoty atributu. Při pohledu na komponenty SOM mapy 4.3 zjistíme, že rozložení dat s dlouhou dobou odezvy - Duration ve spodní části mapy je shodné s rozložením vysokých hodnot atributů CPU, Read a Writes. Všechny tyto atributy jsou data z měření SQL Profiler. Naopak při průzkumu ostatních atributů - dat čítačů z měření System Monitor se nepodařilo najít takový, který by měl v uvedené pozici zaznamenány vysoké hodnoty. Přesto z toho lze vyvodit cenný závěr, že vysoká doba odezvy přímo souvisí s vytěžováním serveru skutečnými operacemi Read, Write a CPU a ne dlouhodobým uzamykáním SQL objektů zámky, což bývá častý projev neoptimálního řešení souběžného přístupu k databázi. Atributy Read, Write a CPU představují operace prováděné v rámci procesu SQL serveru a je při nich využívána rychlá vyrovnávací cache paměti RAM. Některé z těchto operací pak generují


Obrázek 4.3: Nalezená podobnost SOM map atributů SQL Profiler Duration, Reads, Writes na serveru Jupiter

zátěž na disky serveru, ale zde se nepodařilo prokázat přímou souvislost s kritickým zatížením disků F: a C: ani zatížením dalších komponent. Při zkoumání kritických čítačů JUPITER PhysicalDisk: Avg. Disk sec/Read: 2 F: a JUPITER PhysicalDisk: Avg. Disk sec/Read: 0 C: na obrázku 4.4, což jsou doby zpoždění při čtení z disku, je patrné, že tyto čítače mají podobný výskyt dat s vysokými hodnotami. Kromě toho je vidět, že ve stejném místě - v pravém horním rohu sítě 4.4 je u čítače Jupiter Memory: Available: Bytes naopak zjištěn pokles hodnot. Tento čítač ukazuje velikost dostupné paměti serveru a v tomto místě klesá na hodnotu 105MB, což je méně než doporučená hodnota 140MB.


23

Z toho lze vyvodit velmi důležitý závěr, že vysoká zátěž na disků F: a zejména C: je způsobena nedostatkem paměti RAM. Podobné uspořádání SOM sítě a tedy vzájemnou závislost mají atributy JUPITER PhysicalDisk: Avg. Disk sec/Write: 2 F: a JUPITER PhysicalDisk: Avg. Disk sec/Write: 0 C, což je doba zpoždění pro operace zápisu. Zobrazení SOM sítí všech těchto atributů je uvedeno na obrázku 4.4.

Obrázek 4.4: Nalezená podobnost SOM map čítačů serveru Jupiter zpoždění čtení na disky 0 C: a 2:F a volné paměti. V dalším rozboru na obrázku 4.5 působí poněkud rozporuplným dojmem výskyt vysokých hodnot čítačů JUPITER PhysicalDisk: Disk Reads/sec: 0 C:, JUPITER PhysicalDisk: Disk Writes/sec: 2 F: a JUPITER Memory: Pages/sec: vlevo dole, na opačné straně sítě než kde byl zaznamenán výskyt kritických hodnot zpoždění čtení na tyto disky. První dva čítače představují počet IO operací čtení a zápisu na disku C: a posledně uvedený JUPITER Memory: Pages/sec: počet paměťových stránek uložených nebo načtených mezi paměti RAM a


disky. To ukazuje na projev stránkování na disku C:. Opět se tím potvrzuje domněnka, že server trpí nedostatkem paměti RAM.

Obrázek 4.5: Nalezená podobnost SOM map čítačů počtu operací čtení a zápisu na disku 0 C: se stránkováním do paměti.

V mapě U-matice a mapy vzdáleností 4.7 je nalezen v pravé dolní části map jediný shluk dat. Jedná se o oblast dat s nejvýraznějším nárůstem hodnot všech atributů. Je to stejné místo jako jsou výskyty vysokých hodnot SOM mapy čítačů počtu operací čtení a zápisu na disku C: a stránkování do paměti 4.5. Z tohoto pohledu vyplývá, že nejvýraznějším výkonnostním projevem serveru Jupiter jsou IO operace na na disku C:. Nicméně při srovnání maximálních hodnot operací read/write 76/41 4.5 na disku C: a na disku F: read/write 305/275 4.6 je zjevné, že disková jednotka F: je zatížená výrazně více. Sestavení SOM sítě serveru Jupiter bylo provedeno s chybami final quantization error = 1.309 a final topographic error = 0.023.


25

Obrázek 4.6: Nalezená podobnost SOM map čítače počtu operací zápisu na disku 2 F: se zatížením CPU.

Obrázek 4.7: U matice a mapa vzdálenosti ukazuje na podobnost s vytížením IO operacemi na disku 0 C:


4.2.6.2

Vyhodnocení měření serveru SQL pomocí SOM sítě

Server SQL oproti serveru Jupiter nevykazoval velké výkonnostní problémy, ani vysoké doby zpoždění zpracování SQL transakcí - Duration. Při hodnocení SOM sítě zpoždění Duration 4.8 s sítěmi ostatních atributů se nepodařilo objevit žádnou závislost.

Obrázek 4.8: SOM mapy atributů SQL Profiler Duration, Reads, Writes na serveru SQL.

Mírnou závislost se podařilo na obrázku 4.9 objevit mezi čítači volné paměti SQL Memory: Available Bytes: a stránkování SQL Memory: Pages/sec:. Jedná se o přirozený jev, kdy nedostatek paměti vede k vyššímu stránkování, nicméně výskyt vysokého stránkování na SOM mapě je velmi malý a proto nemá zásadní vliv na výkonnost serveru. Při hledání jiných závislostí je v levém spodním rohu SOM sítě 4.10 patrný stejný výskyt vysokých hodnot čítačů SQL PhysicalDisk: Avg. Disk sec/Read: 0 C: D:, SQL PhysicalDisk: Avg. Disk sec/Write: 0 C: D:, SQL PhysicalDisk: Avg. Disk sec/Write: 1 E: a SQL PhysicalDisk: Disk Writes/sec: 1 E, což jsou hodnoty zpoždění čtení a zápisu na disku s logickými disky C: D: a zpoždění zápisu a počet operací zápisu na disk E:. Na disku E: jsou uloženy soubory databází a na disku D: soubory transakčních logů. Tento jev je přirozený a souvisí s transakčním zpracování SQL operací. Nejprve jsou změny zapsány do souborů transakčních logů na disku D:, s malým zpožděním z tohoto disku načteny a zapsány na disk E: do souborů databází.


27

Obrázek 4.9: SOM mapy atributů čítačů volné paměti a stránkování serveru SQL.

Obrázek 4.10: Nalezená podobnost SOM map čítačů disků 0 C: D: a 1 E: na serveru SQL.

Na obrázku 4.11 je kromě výskytu atributu zatížení CPU zobrazena podobnost map čítačů zpoždění a počtu operací čtení na disku 1 E:. Toto je přirozený jev, kdy počet provedených IO operací souvisí se zpožděním těchto operací. Z mapy U-matice a mapy vzdáleností na obrázku 4.12 je patrné, že nejvýraznějším projevem z měřených atributů jsou čítače disků 0 C: D: a 1 E:. U těchto čítačů je na obrázku 4.10 nalezena podobnost s mapou U matice a mapou vzdálenosti. Sestavení SOM sítě serveru SQL bylo provedeno s chybami final quantization error: 1.169 a final topographic error = 0.011.


Obrázek 4.11: Nalezená podobnost SOM map čítačů počtu a zpoždění operací čtění na disk 1 E: na serveru SQL a SOM mapa čítače zatížení CPU.

Obrázek 4.12: U-matice a mapy vzdáleností ukazuje na podobnost výskytu hodnot čítačů disků 0 C: D: a 1 E:, viz. obrázek 4.10


4.2.7

29

Hledání problematických SQL transakcí

Tato kapitola se věnuje hledání transakcí, které negativně ovlivňují zatížení databázového serveru. Hlavní zdrojem dat pro hledání je tabulka CounterDateProfiler, která obsahuje spojená data obou měření - System Monitor a SQL Profiler. Vytvoření této tabulky je popsáno v kapitole 4.2.3. Nad touto tabulkou je nutné provést seskupení podle atributů Textdata a Database, které obsahují název SQL příkazu nebo uložené procedury a název databáze. U ostatních atributů této tabulky, jako Reads, Writes, CPU a čítače System Monitor je nutné provést agregaci jejich hodnot podle aritmeckého průměru. Výsledkem je tedy tabulka, která obsahuje jedinečné záznamy SQL příkazů a číselné průměrné hodnoty ostatních atributů. Kromě toho by tabulka vyhodnocení SQL transakcí měla obsahovat četnost jednotlivých transakcí, která by měla mít vliv na určení problematických transakcí. Při sestavení seznamu problematických SQL transakcí je nutné vybrat kritérium, podle kterého se bude seznam řadit. Toto kritérium by mělo obsahovat čítače System Monitor, jejichž hodnoty překračují doporučené limity. Pokud se chceme zaměřit na dlouhou odezvu zpracování SQL transakcí, pak je vhodné sestavit kritérium, které bude obsahovat atribut Duration - dobu zpracování SQL transakce. Pokud chceme posoudit celkový vliv dané transakce na server, pak je vhodné do řadícího kritéria přidat četnost opakování transakce. S ohledem na potřeby zadavatele analýzy, může být vhodnější vytipovat transakce, které mají vysoké hodnoty daného atributu a často se opakují, než transakce, které mají hodnoty sice vyšší, ale jejich výskyt je příliš nízký, a investice na jejich optimalizaci se nevyplatí. Nejobjektivnějším způsobem sestavení řadícího kritéria je násobek četnosti transakce a průměrné hodnoty atributu, tím se určí celkový součet hodnot atributů zaznamenaných v průběhu celého měření. Ve skriptu SQL uvedeném v příloze A.4 je uveden příklad vytvoření seznamu deseti SQL transakcí serveru Jupiter, které měli vysokou dobu odezvy a vysoký počet opakování. Parametr Duration je přepočítán na sekundy.

4.2.7.1

Vyhodnocení problematických SQL transakcí na serveru Jupiter

Podle skriptu A.4 byly vygenerovány dva seznamy problematických transakcí. První uvedený v tabulce 4.2 představuje deset SQL transakcí s nejdelší celkovou dobou zpracování. Druhý seznam uvedený v tabulce 4.3 se zaměřuje na SQL transakce řazené podle kritického čítače \\JUPITER\PhysicalDisk: Avg. Disk sec/Read: 2 F: a četnosti. V uvedených tabulkách 4.2 4.3 jsou SQL příkazy pro přehlednost ořezány na 80 znaků. Server Jupiter hostuje warehouse databázi OperationManagerDW pro generování reportů aplikace Microsoft System Center Operation Manager 2007 R2 [15]. Tato aplikace monitoruje, zaznamenává a proaktivně upozorňuje administrátora Windows Serverů o problémech v operačním systému, aplikacích a síťové infrastruktuře. Do databáze OperationManagerDW se generuje obrovské množství informací o každém monitorovaném serveru, jeho čítače výkonu, výpadky aplikací, vybrané záznamy z logu událostí operačního systému a další. Většina operací, kterými dohledový systém udržuje a vyhodnocuje data o serverech se provádí cyklicky pomocí uložených procedur.


Sort Criteria 37076

Occurrence

TextData

Database Name

124

OperationsManagerDW

5820 5226 960

15 26 20

tempdb tempdb OperationsManagerDW

388 201 48

266

14

OperationsManagerDW

19

196

7

OperationsManagerDW

28

168

14

OperationsManagerDW

12

145

5

OperationsManagerDW

29

116

4

OperationsManagerDW

29

87

3

exec StandardDatasetMaintenance @DatasetId=’BBEB9FE2E3B8-43FA-9F4CC6FF410499F2 exec sp_reset_connection exec sp_spaceused exec StandardDatasetMaintenance @DatasetId=’CE3874B5C423-4085-ACE589613509AE12 exec StandardDatasetMaintenance @DatasetId=’34382740EEEE-4FB6-94A54F4DB10C9D1B exec EventCategoryRowIdResolve @ManagementGroupGuid= ’626E0E1C37A5-EDB5-3466-0AC exec StandardDatasetMaintenance @DatasetId=’4109ACB3BA76-4E9B-88E0DED8D28BD04C exec EventCategoryRowIdResolve @ManagementGroupGuid= ’626E0E1C37A5-EDB5-3466-0AC exec EventCategoryRowIdResolve @ManagementGroupGuid= ’626E0E1C37A5-EDB5-3466-0AC exec EventCategoryRowIdResolve @ManagementGroupGuid= ’626E0E1C37A5-EDB5-3466-0AC

Avg Duration 299

OperationsManagerDW

29

Tabulka 4.2: Seznam SQL transakcí serveru Jupiter s celkovou nejdelší dobou zpracování Duration


31

Sort Criteria

Occurrence

TextData

Database Name

19,859

124

5,808

14

4,452

20

3,151

14

2,955 2,468

26 7

2,433

7

2,218

5

2,102

6

1,614

15

exec Standard- OperationsManagerDW DatasetMaintenance @DatasetId=’BBEB9FE2E3B8-43FA-9F4CC6FF410499F2 exec Standard- OperationsManagerDW DatasetMaintenance @DatasetId=’4109ACB3BA76-4E9B-88E0DED8D28BD04C exec Standard- OperationsManagerDW DatasetMaintenance @DatasetId=’CE3874B5C423-4085-ACE589613509AE12 exec Standard- OperationsManagerDW DatasetMaintenance @DatasetId=’34382740EEEE-4FB6-94A54F4DB10C9D1B exec sp_spaceused tempdb exec EventCate- OperationsManagerDW goryRowIdResolve @ManagementGroupGuid=’626E0E1C37A5-EDB5-3466-0AC exec RelationshipChange OperationsManagerDW @ManagementGroupGuid=’626E0E1C37A5-EDB5-34660ACAC67CF3 exec EventCate- OperationsManagerDW goryRowIdResolve @ManagementGroupGuid=’626E0E1C37A5-EDB5-3466-0AC exec ManagementPackMain- OperationsManagerDW tenance exec sp_reset_connection tempdb

Avg Disk Delay Read F: 0,160

0,415

0,223

0,225

0,114 0,353

0,348

0,444

0,350 0,108

Tabulka 4.3: Seznam SQL transakcí serveru Jupiter s nejvyšším zpožděním operací čtení z disku F: - čítač Avg \\JUPITER\PhysicalDisk: Avg. Disk sec/Read: 2 F:, označený jako Avg Disk Delay Read F:


V tabulkách 4.2 a 4.3 se při hodnocení celkového vlivu shodně ukázaly jako nejhorší SQL operace uložené procedury "exec StandardDatasetMaintenance", které se liší pouze svými parametry. Nejkritičtější je potom parametr @DatasetId=’BBEB9FE2-E3B8-43FA-9F4CC6FF410499F2. Při bližším zkoumání procedury a parametru bylo zjištěno, že se jedná o operaci PerformanceProcessStaging, což je zpracování dat o čítačích výkonů monitorovaných serverů a ta se aktivuje každých 5 minut a často trvá tak dlouho, že se nestihne dokončit. Vedlejší efekt tohoto jevu je zaznamenaná "transakce exec sp_reset_connection", což je systémová procedura SQL serveru, která ukončuje SQL relaci. Díky tomu, že je server vysoce přetížen, tato procedura trvá nepřiměřeně dlouho. Díky této analýze bylo možné najít přesnou příčinu vysokého vytížení serveru, byly jimi zmíněné procedury StandardDatasetMaintenance. Jako řešení problému byla provedena výrazná redukce nepotřebných záznamů dat v databázi OperationsManagerDW na základě jejich stáří. Po tomto zásahu došlo k okamžitému urychlení transakcí na serveru a zároveň k více než desetinásobnému snížení zatížení diskové jednotky F: a celého RAID pole. 4.2.7.2

Vyhodnocení problematických SQL transakcí na serveru SQL

Server SQL je centrální SQL server, který hostuje databáze pro 5 aplikací a využívá jej okolo 50 uživatelů. Tento server běží jako fyzický a v době měření nevykazoval žádné velké výkonnostní problémy. Podle tabulky 4.2 jediným čítačem, u kterého došlo k překročení doporučeného limitu je \\SQL \Memory: Pages/sec:. Pro tento jev se nabízí vysvětlení, že toto vysoké stránkování je způsobeno pravidelným zálohování transakčních logů. Proto budou sestaveny dva seznamy SQL transakcí, jeden bude řazen sestupně podle celkové doby zpracování Duration a druhý podle celkového stránkování serveru - čítače \\SQL \Memory: Pages/sec. Při analýze tabulky seznamu SQL transakcí serveru SQL s celkovou nejdelší dobou zpracování 4.4 je patrné, že transakcí, která je oproti ostatním abnormálně dlouhá je transakce "WAITFOR (Receive convert(xml,message_body) from MOMNotificationQueue), TIMEOUT 300000"nad databází OperationsManager. Jedná se o OLTP databázi aplikace Operation Manager 2007 R2, která zajišťuje proaktivní monitorování a dohled firemních serverů. SQL příkaz WAITFOR zpracovává následující proceduru, tak že se ukončí buď po uplynutí uvedeného času, v našem případě 300 sekund nebo po provedení procedury. Jedná se o transakci, která je součástí komunikačního mechanismu posílání zpráv SQL serveru, tzv Service Broker. Velká doba odezvy transakce je tedy již z podstaty příkazu daná a na práci uživatelů nemá vliv. Transakce do databáze GistControlling se během doby měření zaznamenaly pouze jednou a je otázkou, nakolik investice do jejich optimalizace budou přínosné. Transakce v databázích OperationManager a OperationManagerDW souvisí s monitorováním firemních serverů a jejich doba odezvy není pro práci uživatelů důležitá. Vyhodnocení další tabulky seznamu SQL transakcí serveru SQL s nejvyšším stránkováním mezi paměti a pevným diskem 4.5 přináší nečekaná zjištění. Vysoká míra stránkování přisuzovaná zálohování transakčních logů byla zřejmě způsobena jinými SQL transakcemi. Kromě zmíněné transakce WAITFOR se jedná o transakce do databáze Intranet_SP_ SharedServices_Search _DB, což je databáze indexů webové služby pro týmovou spolupráce Microsoft SharePoint Services [16]. Tyto transakce zřejmě vytvářejí nový indexový obsah webové služby a proto generují velké stránkování paměti. Kromě transakcí aplikace Operation Manager se


33

Sort Criteria 21175

Occurrence

TextData

Database Name

77

OperationsManager

48

6

35

5

32

4

15

3

15

3

11

1

10

1

10

1

10

1

WAITFOR (Receive convert(xml,message_body) from MOMNotificationQueue), TIMEOUT 300000 exec ManagementPackMaintenance exec dbo.p_SelectForTypeCache @LanguageCode1=’CSY’, @LanguageCode2=N’ENU’, @LastKnownMPLast exec dbo.p_SelectForTypeCache @LanguageCode1=’CSY’, @LanguageCode2=N’ENU’, @LastKnownMPLast exec StandardDatasetMaintenance @DatasetId=’47CA2669144C-4D32-B557D56663CC019D’ exec StandardDatasetMaintenance @DatasetId=’884DAF7D3F05-4475-B85A2D4045A8F2CB’ insert into #tmp123277 select tmp113a277_4.c_col, tmp113a277_4.c_row, a111.code_clen a insert into #tmp123286 select tmp113a286_4.c_col, tmp113a286_4.c_row, a111.code_clen a insert into #tmp123285 select tmp113a285_4.c_col, tmp113a285_4.c_row, a111.code_clen a insert into #tmp123284 select tmp113a284_4.c_col, tmp113a284_4.c_row, a111.code_clen a

Avg Duration 275

Operations- 8 ManagerDW Operations- 7 Manager

OperationsManager

8

Operations- 5 ManagerDW

Operations- 5 ManagerDW

GistControlling

11

GistControlling

10

GistControlling

10

GistControlling

10

Tabulka 4.4: Seznam SQL transakcí serveru SQL s celkovou nejdelší dobou zpracování Duration


Sort Criteria 12255

Occurrence

TextData

Database Name

77

OperationsManager

1058

1

1058

1

1058

1

321

3

274

1

WAITFOR (Receive convert(xml,message_body) from MOMNotificationQueue), TIMEOUT 300000 exec dbo .proc_MSS_ GetNextCrawlBatch 2,218935,20477,50 exec dbo .proc_MSS_ ProcessCommitted 10080,100,1085,1,1,2,218935, 0,3,1,2,33,N’anchorqh://a exec dbo .proc_MSS_ ProcessCommitted 10080,100,1086,1,1,2,218935, 0,3,1,2,33,N’anchorqh://a exec StandardDatasetMaintenance @DatasetId=’884DAF7D3F05-4475-B85A2D4045A8F2CB’ declare @p7 int set @p7=152166 declare @p8 int set @p8=4 exec dbo.proc_MSS_Crawl 1,3

71

3

27

1

19

6

9

1

exec StandardDatasetMaintenance @DatasetId=’47CA2669144C-4D32-B557D56663CC019D’ BACKUP LOG [QI] TO DISK = N’E:\BACKUP\TLOG\QI \backup\201003171400.trn’ WITH NOFORM exec ManagementPackMaintenance declare @p7 int set @p7=218926 declare @p8 int set @p8=4 exec dbo.proc_MSS_Crawl 2,3

Avg Pages/ sec: 159

Intranet_SP_1058 SharedServices_Search_DB Intranet_SP_1058 SharedServices_ Search_DB Intranet_SP_1058 SharedServices_ Search_DB Operations- 107 ManagerDW

Intranet 274 _SP_WSS_ Search_ INTRANET Operations 24 ManagerDW

master

27

Operations- 3 ManagerDW Intranet_SP_9 SharedServices_ Search_DB

Tabulka 4.5: Seznam SQL transakcí serveru SQL s nejvyšším stránkováním mezi paměti a pevným diskem - čítač \\SQL \Memory: Pages/sec, označený jako Avg Pages/sec:


35

podařilo zaznamenat transakci zálohování transakčních logů uvedenou jako BACKUP LOG [QI] TO DISK = N’E:\BACKUP \TLOG\QI\backup\201003171400.trn’ WITH NOFORM. U databázového serveru se potvrdil, jeho bezproblémový chod. Mezi deseti nejdelšími a nejvíce zatěžujícími transakcemi se zaznamenalo minimum transakcí, které přímo souvisí s uživatelskými aplikacemi. Zaznamenaly se především transakce, které souvisí s podpůrnými službami jako monitorování a dohled serverů nebo obsah indexů služby SharePoint Services [16].


Kapitola 5

Návrh nástroje pro automatické měření a analýzu zatížení serveru V této kapitole bude v souladu s navrženou metodikou měření navržena aplikace, která bude měření čítačů a zaznamenání SQL transakcí provádět automatizovaně. Aplikace nebude obsahovat techniky na hledání závislostí dat jako je uvedená statistická metoda korelačního Paersonova koeficientu nebo analýza pomocí neuronové sítě SOM. Důraz bude kladen na reálné užití, kdy bude z měření čítačů serveru proveden základní výkonnostní audit serveru a doplňkově trasování SQL transakcí a základní vyhodnocení jejich parametrů. Hlavním cílem vývoje této aplikace je zkrátit dobu a pracnost výkonnostních auditů na minimum a vyloučit lidské chyby, které vznikají při náročné ruční transformaci dat. Nejdůležitější funkcí aplikace je především měření a vyhodnocení čítačů nezávisle na monitorování SQL transakcí. Díky tomu bude aplikace otevřená a bude s ní možné vyhodnocovat zatížení i jiných Windows serverů než databázových. Hlavní myšlenka otevřenosti aplikace spočívá v možnosti importu expertních pravidel v otevřeném formátu XML. Tato expertní pravidla stanoví specialista aplikace nebo serverového systému, na kterém se audit provádí. Samotný audit, popř. kontrolu může pak provádět běžný správce serveru a díky tomu bude moci okamžitě vyhodnotit stav serveru, zjistit problém a zajistit nápravu. V první řadě bude aplikace navržena k profesnímu použití autora této práce a to hlavně pro výkonnostní audity zákaznických databázových serverů. Pomocí této aplikace bude možné okamžitě a spolehlivě určit problém a výrazně snížit náklady na provádění výkonnostních auditů. Aplikace v dalším textu ponese název SqlMonitor.

5.1

Seznam funkčních a nefunkčních požadavků

Sestavení požadavků na aplikaci bylo provedeno v programu společnosti Sparx - Enterprise Architect [9] a je složeno z agregovaného seznamu požadavků a detailního popisu jednotlivých požadavků.

37

38KAPITOLA 5. NÁVRH NÁSTROJE PRO AUTOMATICKÉ MĚŘENÍ A ANALÝZU ZATÍŽENÍ SERVERU

5.1.1

Funkční požadavky

Na obrázku 5.2 je uveden seznam všech funkčních požadavků. V dalším textu je uveden jejich detailní popis. Inicializace připojení k serverům • Připojení k měřenému serveru přes síť. Uživatel zadá název serveru, který chce vzdáleně přes síť měřit. Uživatel bude ověřen integrovaným přihlášením z Active Directory • Připojení k serveru s databází měřených dat. Nabídnout lokální nebo vzdálený server databázový server, kam se budou ukládat data z měření. Uživatel bude k databázovému serveru integrovaně ověřen přihlášeným účtem v Active Directory. • Výběr nebo založení databáze pro data měření. Nabídnout seznam databází, které jsou na SQL serveru a nechat možnost jednu z nich vybrat. Přitom se zkontroluje existence názvů tabulek, které aplikace bude používat. Nastavení měření • Automatická volba čítačů System Monitor ze souboru expertních znalostí. Bude se jednat o externí soubor ve formátu XML. V tomto souboru bude uveden seznam čítačů, které by se měli měřit, jejich limitní hodnoty, vyhodnocovací pravidla čítačů. Zde budou ve skupinách uvedeny hw komponenty, které nevyhovují zátěži. Vyhodnocení bude provedeno na základě skupin identifikátorů čítačů, jejichž průměrné hodnoty překročily limit. Po výběru souboru budou zadané čítače automaticky nastaveny na měření pro všechny instance. Uživatel bude mít možnost odebrat nepotřebné čítače a popř. přidat vlastní čítače. Ty ovšem nebudou součástí vyhodnocení. • Nastavení intervalu měření čítačů. Uživatel nastaví měřící interval, ve kterém se budou data z čítačů cyklicky sbírat. Nejmenší hodnota bude 1 sekunda. • Nastavení měření transakcí SQL serveru. Uživatel bude mít možnost nastavit filter na atribut Duration, tj. aby se zaznamenaly transakce delší než uvedený počet milisekund. Do měření budou automaticky zařazeny tyto události SQL: - TSQL: SQL:BatchCompleted - Stored Procedures: RPC: Completed a tyto atributy: - TextData je konkrétní SQL příkaz, tak jak jej odeslala na server aplikace. - StartTime a EndTime, datum a čas začátku a konce zpracování události. - Application Name, je název aplikace, která příkaz generovala. - Duration - čas v mikrosekundách, který označuje dobu trvání zpracování příkazu. - CPU - čas v milisekundách, po který daná událost využívala procesoru.

5.1. SEZNAM FUNKČNÍCH A NEFUNKČNÍCH POŽADAVKŮ

-

39

Reads - počet paměťových stránek, které bylo pro daný příkaz nutno načíst. Writes - počet paměťových stránek, který zaznamenaný příkaz potřebuje zapsat. Username - uživatel v jehož relaci se příkazy spouští. Hostname - název počítače, odkud jsou je relace navázaná.

• Ruční volba čítačů System Monitor. Po připojení k serveru se nabídne výběr čítačů, nejprve kategorie, čítač a nakonec instance. Po výběru se potvrdí zařazení čítače do seznamu měření. Takto bude možné vybrat několik čítačů. Ovládání měření • Měření čítačů. Po výběru čítačů a po nastavení intervalu bude uživatel moci spustit měření čítačů. Při měření se uživateli pouze informativně zobrazují aktuální hodnoty čítačů. Měření může uživatel ukončit interaktivně. Alternativně bude moci uživatel povolit ukládání dat do připravené databáze. Pro každé měření bude automaticky založena nová databázová tabulka. • Ovládání měření. Uživatel bude mít možnost měření spustit, ukončit a opětovně spustit. • Zaznamenání SQL transakcí. Uživatel může aktivovat trasování SQL transakcí. Kromě toho může doplňkově povolit ukládání těchto dat do databázového serveru. Vyhodnocení čítačů • Vyhodnocení čítačů. Po ukončení měření bude uživateli zobrazen časový interval měření. Tento interval bude sestaven z tabulky relační databáze čítačů podle nejstaršího a nejnovějšího času měření. Uživatel může upravit časový interval, podle toho pak budou vypočítány výsledky měření. • Výpočet průměrných hodnot čítačů. Z tabulky čítačů bude za zvolený časový interval proveden výpočet průměrných hodnot každého čítače a zobrazen uživateli v tabulkovém uspořádání. • Vyhodnocení slabých HW komponent. Pokud bylo měření nastaveno na základě souboru expertních znalostí, pak dojde k vyhodnocení pravidel z tohoto souboru a budou určena jedna nebo více možných příčin a nevyhovující hardware komponenty. U každého čítače budou uvedeny značky třech stavů: - Stav kritický - čítač překročil doporučenou mez. - Stav varování - čítač mírně překročil doporučenou mez. - Stav OK - čítač je v pořádku.


Vyhodnocení SQL transakcí • Spojení dat čítačů a SQL transakcí. Data z obou měření budou spojena na základě času čítače a StartTime a EndTime SQL transakce. Hodnoty čítačů transakcí budou seskupeny pro dané transakce podle aritmetického průměru. Vznikne nová tabulka spojených hodnot, kde budou uvedeny všechny transakce a průměry čítačů, které byly v době transakce naměřeny. • Vytvoření tabulky spojených dat seskupených podle SQL transakcí. Uvedená tabulka seskupí data podle atributu TextData a přidá atribut výskytu četnostni jednotlivých transakcí. Tím bude možné vyhodnotit celkové hodnoty čítačů.

5.1.2

Nefunkční požadavky

V následujícím textu je uveden detailní popis nefunkčních požadavků. • Platforma operačních a databázových systémů. Měřený, měřící a záznamový server budou Windows Server 2003 a vyšší. Databázové servery budou verze SQL 2005 a vyšší. • Uspořádání serverových rolí. Měřící a záznamový server mohou být provozovány na jednom fyzickém, popř. virtuálním serveru. Tyto role se nesmí provozovat na měřeném serveru, jinak by mohlo docházet ke zkreslení zatížení a vyhodnocení měření by bylo ovlivněno touto zátěží. • Uspořádání komponent aplikace. Aplikace SqlMonitor poběží pouze na měřícím serveru, není dovolena jakákoliv instalace podpůrných komponent na ostatní servery. Aplikace musí využít připravenosti vývojového prostředí Microsoft .NET Framework 2.0.

5.2

Návrh modelu jednání

Návrh modelu jednání částečně kopíruje strukturu jednotlivých funkčních požadavků. Byly sestaveny tyto případy užití: Sestavení souboru expertních znalostí F.1, Inicializace připojení F.2, Nastavení měření čítačů F.3, Nastavení měření SQL transakcí F.4, Ovládání měření F.5, Vyhodnocení čítačů F.6, Vyhodnocení SQL transakcí F.7. Model jednání zahrnuje dvě uživatelské role: • SQL Expert, který přesně ví, které čítače pro výkonnostní audit zaznamenat, jaké jsou jejich limitní hodnoty a umí vyhodnotit z těchto čítačů stav serveru a navrhnout řešení případného problému. Tyto znalosti SQL Expert specifikuje do XML souboru expertních znalostí. • SQL Operátor. Jedná se o osobu, která databázový server spravuje a má k němu plný přístup a je zpravidla odpovědná za bezproblémový chod serveru. Tato role provádí měření a výkonnostní audity a díky možnosti importu expertních pravidel dokáže zjistit stav serveru a odhalit případně problémy.

5.3. NÁVRH ARCHITEKTURY APLIKACE

5.3

41

Návrh architektury aplikace

Na obrázku 5.1 je naznačeno prostředí aplikace SqlMon. V tomto prostředí budou využity následující serverové systémy - serverové role. • Role Active Directory Jedná se o jeden nebo více doménových řadičů, které mají za úkol zajistit bezproblémové integrované ověření při připojení ke vzdáleným serverům. Aby toto bylo možné, musí SQL Operátor použít účet z Active Directory, tento účet nevyžaduje zvláštní doménová privilegovaná oprávnění. • Role měřeného serveru. Jedná se o server, na kterém se provádí výkonnostní audit. Aby se mohl SQL Operátor k serveru aplikací SqlMon připojit, musí být jeho doménový účet zařazen do skupiny lokálních administrátorů měřeného serveru. Kromě toho musí být na serveru povolena komunikace na TCP/IP porty pro rozhranní WMI. • Role měřícího serveru. Jedná se o server, na kterém poběží aplikace SqlMonitor. Data získaná z měření budou ukládána na záznamový server. Aby fungovalo integrované ověření na vzdálených serverech, musí se SQL Operátor přihlásit k tomuto serveru určeným doménovým účtem. • Role záznamový server. Jedná se o databázový server, na kterém se budou data z měření zaznamenávat, transformovat a analyzovat. Aby bylo možné provádět tyto operace, je nutné založit vybranému doménovému účtu SQL Login. Na tento login je třeba nastavit příslušné oprávnění na databázi měření.

5.4

Návrh automatizovaného vyhodnocení na základě zadaných expertních pravidel

Expertní pravidla jsou znalosti sestavené SQL expertem do XML souboru, které popisují jaké čítače zaznamenat, jaké jsou jejich limitní hodnoty a jsou zde popsány případné problémy a návrhy na jejich řešení. Určité expertní znalosti jsou potřebné pro databázový server a jiné například pro server webový nebo souborový. Je počítáno, že každá znalost bude popsána v samostatném XML souboru. Import hotového souboru expertních znalostí do aplikace provádí SQL Operátor. Podle čítačů v XML souboru aplikace automaticky vybere a nastaví požadované čítače. Po dokončení měření dojde k porovnání limitních hodnot uvedených v souboru a naměřených průměrných hodnot. Pokud dojde k překročení některého z těchto limitů, je čítač označen jako kritický. V posledním kroku analýzy aplikace prochází v XML souboru seznam problémů a podle kombinace kritických čítačů aplikace vybere kategorii problému a uživateli zobrazí popis problému a návrh řešení. Příklad souboru expertních znalostí pro databázové servery je uveden v příloze D.1.


Obrázek 5.1: Prostředí aplikace SqlMonitor, serverové role.

5.5

Návrh použitých technologií

V této kapitole je uveden přehled dostupných technologií, které je možné použít k vývoji aplikace. Protože se počítá, že prostředí aplikace SqlMonitor je Microsoft Windows, je pro realizaci výhodné použít vývojové prostředí Microsoft .NET 2.0 a vyšší, protože obsahuje řadu předpřipravených knihoven.

5.5.1

Programový přístup k výkonnostním čítačům

Měření a záznam čítačů je možné realizovat ve jmenném prostoru .NET System.Diagnostics. V tomto jmenném prostoru jsou třídy pro přístup do systémových logů Prohlížeče událostí, třídy pro monitorování běžících procesů a jsou zde třídy pro výkonové čítače. Konkrétně se jedná třídu PerformanceCounter. Při inicializaci instance třídy se předává konstruktoru kategorie čítače - CategoryName, název čítače - CounterName a volitelně název instance InstanceName a název serveru - MachineName. Podrobnosti jsou uvedeny na [12].

5.5. NÁVRH POUŽITÝCH TECHNOLOGIÍ

5.5.2

43

Programovým přístup k trasování SQL serveru

Dalším úkolem aplikace se sbírat z měřeného server záznamy o prováděných SQL příkazech a uložených procedurách, tzv. trasování. Trasování lze provádět ručně pomocí nástroje SQL Profiler nebo programově. Ve verzích Microsoft SQL Server 2000 a nižší bylo možné provádět trasování pouze spuštěním příslušných SQL uložených procedur sp_trace_create, sp_trace_setevent, sp_trace_setfilter a sp_trace_setstatus. Při aktivaci tohoto trasování se v určeném adresáři vytvářely soubory trasování a tyto soubory bylo možné hromadně importovat do databáze pomocí funkce n_trace_gettable. Popis tohoto trasování je uveden na [17]. V novějších verzích Microsoft SQL Server 2005 a vyšší je možné trasovat server programově přímo pomocí nového API ve jmenném prostoru Microsoft.SqlServer.Management.Trace assembly Microsoft.SqlServer.ConnectionInfoExtended. Třída, která zajišťuje přímé trasování se jmenuje TraceServer. Popis této třídy je uveden na [19].


Obrázek 5.2: Přehled všech funkčních požadavků

Kapitola 6

Ověření funkčnosti nástroje V této kapitole je funkčnost nástroje ověřena na základě popisu a ukázek skutečné aplikace SqlMonitor, která byla vytvořena v souladu s návrhem popsaným v této práci. Při implementaci této aplikace se vyskytly některé problémy, které vývoj prodloužily a znesnadnily, nicméně aplikaci se podařilo vytvořit, tak že zůstala zachována její funkčnost. Jako největší problém se ukázal programový přístup k měření čítačů. Bylo zjištěno, že objekty čítačů odvozené ze třídy PerformanceCounter se dělí na čítače se statickou hodnotou a dynamickou. Dynamické čítače popisují hodnotu za interval času, převážně za jednu sekundu. Pro získání hodnoty z dynamického čítače je nutné metodu pro načtení dat volat vícenásobně. Funkčnost aplikace byla testována ve virtuálním prostředí dvou serverů s operačním systémem Windows 2003 a databázovým systémem Microsoft SQL Server 2005. Serverové role měřící server a záznamový server byly sloučeny do serveru s názvem SQL2005. Měřený databázový server spojený s rolí doménového řadiče nese název DCSQL2005.

6.1

Nastavení aplikace

Aplikace SqlMonitor má jediný formulář pro nastavení konfigurace a serverových rolí. Bez nastavení tohoto formuláře je ovládání celé aplikace nepřístupné. Formulář nastavení uvedený na obrázku 6.1 vyžaduje nastavení role měřeného serveru, nepovinná je volba nastavení záznamového serveru. Pokud uživatel nastaví jméno záznamového serveru, aplikace se automaticky připojí na tento server a zobrazí seznam existujících databází, kde se později vytvoří tabulka čítačů a tabulka SQL transakcí. Názvy tabulek se zadávají ve stejném formuláři. Do těchto tabulek budou zaznamenávány hodnoty měření.

6.2

Měření čítačů

Pokud uživatel správně nastaví serverové role, dojde k odblokování ovládání aplikace a uživatel může provádět konfiguraci a spustit samotné měření. Konfigurace měření čítačů je uvedena na obrázku 6.2. Z tohoto snímku aplikace je vidět, že se podařilo ověřit funkcionalitu ručního i automatického zadání čítačů pro měření. Na tomto snímku je zaznamenáno automatické

45

46

KAPITOLA 6. OVĚŘENÍ FUNKČNOSTI NÁSTROJE

Obrázek 6.1: Konfigurace a nastavení serverových rolí aplikace SqlMonitor.

načtení čítačů podle XML souboru expertních znalostí s názvem sql_definition.xml a zároveň je zde vidět, že čítač PhysicalDisk: Disk reads/sec: 0C: byl zadán ručně. Aktivace měření se provádí zeleným tlačítkem "Start měření". Zaškrtnutním volby "Ukládat záznamy do databáze"je možné zajistit průběžné ukládání hodnot čítačů měření do tabulky SQL databáze. Tato volba provede kontrolu existence tabulky a nabídne její smazání. Po spuštění měření aplikace automaticky vytvoří tabulku tak, že každý sloupec je pojmenován přesně podle spojeného názvu čítače a každý řádek zaznamená data všech čítačů v jednom okamžiku měření. Díky tomu jsou tato data okamžitě připravená na analýzu. Aby snímání čítačů fungovalo správně a bez časových prodlev, bylo nutné metodu měření spouštět paralelně pro každý čítač v samostatném vlákně. Kvůli snímání dynamických čítačů bylo nutné hodnoty čítačů zaznamenávat v nekonečném cyklu, každý cyklus byl pozastaven právě na hodnotu času odpovídající intervalu měření. Jedině tak bylo možné spolehlivě za-

6.3. TRASOVÁNÍ TRANSAKCÍ

47

Obrázek 6.2: Nastavení a měření čítačů.

znamenávat i početný seznam čítačů. Na snímku aplikace 6.3 je ukázáno, že snímání hodnot čítačů v aplikaci je funkční. Dále bylo otestováno, že uvedený způsob záznamu dokáže souběžně zaznamenávat v nejkratším intervale jedné sekundy až 30 čítačů, což je počet dostatečně velký na provádění hlubších výkonnostních analýz.

6.3

Trasování transakcí

Podle uvedeného návrhu se podařilo vyvinout aplikaci tak, že dokázala na vzdáleném SQL serveru probíhající transakce zachytávat, jinými slovy server trasovat. K tomu bylo nutné v nástroji SQL Profiler nadefinovat šablonu trasování, zde se nastavily atributy trasování. Použitá třida TraceServer potom načítá parametry trasování právě z tohoto souboru. Na rozdíl od měření čítačů, kde se hodnoty načítaly cyklicky podle pevného intervalu, trasování transakcí vyžadovalo jiný programátorský přístup. Tady bylo nutné spustit z hlavního vlákna aplikace vlákno vedlejší a v jeho nekonečné smyčce čekat na vnější událost.

48


Obrázek 6.3: Měření čítačů.

Tato událost nastala v okamžiku, kdy vzdálený SQL server zpracoval SQL transakci. Tento přístup vyžadoval nalezení způsobu jak z vedlejšího vlákna řídit ovládací prvky formuláře, což je standardně z bezpečnostních důvodů v jazyce C# zakázáno. Toto se pomocí legální metody volání pomocné funkce delegáta podařilo vyřešit. Na snímku aplikace 6.4 je zobrazeno trasování vzdáleného SQL Serveru. Jsou zde zaznamenány veškeré transakce i uložené procedury.

6.3. TRASOVÁNÍ TRANSAKCÍ

Obrázek 6.4: Trasování SQL transakcí bez nastavení filtru Duration.

Bylo ověřeno, že v tomto trasování jsou zachyceny všechny požadované události: - TSQL: SQL:BatchCompleted - Stored Procedures: RPC: Completed

-

a tyto atributy: TextData - konkrétní SQL příkaz, tak jak jej odeslala na server aplikace. StartTime a EndTime - datum a čas začátku a konce zpracování události. Application Name - název aplikace, která příkaz generovala. Duration - čas v mikrosekundách, který označuje dobu trvání zpracování příkazu. CPU - čas v milisekundách, po který daná událost využívala procesoru. Reads - počet paměťových stránek, které bylo pro daný příkaz nutno načíst. Writes - počet paměťových stránek, který zaznamenaný příkaz potřebuje zapsat. Username - uživatel v jehož relaci se příkazy spouští. Hostname - název počítače, odkud jsou je relace navázaná.

49

50

6.4


Automatizované vyhodnocení ze souboru expertních pravidel

Po dokončení měření a uložení měřených dat do databáze je možné provést vyhodnocení čítačů. Formulář vyhodnocení se spouští z hlavního formuláře tlačítkem "Vyhodnoť měření z databáze". Ve formuláři vyhodnocení si může uživatel nastavit vlastní interval pro vyhodnocení dat. Samotné vyhodnocení se provádí po stisku tlačítka "Analyzuj!". Na obrázku 6.5 je vidět funkcionalita provedení automatizovaného vyhodnocení. V horním seznamu jsou všechny měřené čítače. U každého z nich je ve sloupci Avg uvedena průměrná hodnota čítače, ve sloupci Stav je hodnocení čítače. Hodnocení se provádí na základě expertních pravidel XML souboru. Stav ok znamená, že čítač nepřekročil doporučený limit, warning je stav, kdy čítač se nachází mezi hodnotou sloupce Limit a Chyba a stav error je stav, kdy čítač jasně překročil doporučenou mez. V dalším kroku dochází k porovnání čítačů ve stavu error s definicí problémů popsaných v souboru expertních pravidel. Každý problém je definován kombinací čítačů a pokud dojde ke shodě této kombinace s kombinací chybných čítačů, je tento problém vypsán ve spodní části formuláře. U každého problému je uveden jeho popis a seznam možných řešení problému. Například na obrázku 6.5 aplikace SqlMonitor zdetekovala nadměrné souborové operace na disku 0 C: a zároveň zdetekovala příliš dlouhou odezvu na zápis. Tento stav byl na testovacím serveru DCSQL2005 nasimulován kopírováním velkých souborů. Při dalších zatěžových testech bylo ověřeno, že aplikace SqlMonitor správně detekuje stavy čítačů a problémy serveru tak, jak jsou definovány v XML souboru expertních pravidel.

6.5. OVĚŘENÍ FUNKCIONALITY MĚŘENÍ DAT

51

Obrázek 6.5: Výpočet průměrných hodnot naměřených čítačů a automatizované vyhodnocení.

6.5

Ověření funkcionality měření dat

Zásadním požadavkem na ověření funkcionality aplikace SqlMonitor je ověření správnosti naměřených dat. Bylo nutné zjistit, zda hodnoty čítačů a SQL transakcí naměřené aplikací SqlMonitor odpovídají skutečnosti. Kvůli tomu bylo provedeno souběžné měření testovacího serveru DCSQL2005 pomocí nástroje SqlMonitor a vedle toho samostatně pomocí System Monitor a SQL Profiler. Bylo ověřeno, že výsledky čítačů a SQL transakcí se z těchto kontrolních měření shodují.

6.6

Závěr

Na základě návrhu byl vytvořen prototyp aplikace s názvem SqlMonitor. Při vývoji této aplikace se vyskytlo několik potíží, jejichž řešení si vynutilo použití paralelního zpracování ve více vláknech pomocí tzv. Thread Poolu. Bez tohoto postupu by nebylo možné získat data z více čítačů. Při testech aplikace bylo vyzkoušeno, že takto vytvořená aplikace zvládne souběžné měření 30 čítačů. Kromě toho bylo otestováno, že aplikace zároveň dokáže s velmi rychlou odezvou zaznamenávat a zobrazovat veškeré transakce vzdáleného serveru. Dále byla úspěšně ověřena funkcionalita automatického vyhodnocení a pomocí srovnávacího měření System Monitor a SQL Profiler na straně jedné a aplikace SqlMonitor na straně druhé byla

52


ověřena správnost naměřených dat. Tím lze považovat funcionalitu aplikace SqlMonitor měření čítačů, trasování SQL transakcí a automatické vyhodnocení vzdáleného serveru za ověřenou.

Kapitola 7

Závěr V této práci se podařilo odborně zpracovat problematiku výkonnostní analýzy databázových serverů různými způsoby přístupu. Přístup zkoumání výkonnosti serverů pomocí měření čítačů je vlastní správcům databázových serverů. V tomto pohledu se nezávisle na SQL transakcích hledá úzké místo v konfiguraci hardware serveru. Další přístup je zkoumání SQL transakcí, jejich doby odezvy, popřípadě jiných atributů. Tímto přístupem se hledají problematické transakce a řeší se jejich optimalizace. Metodika analýzy v této práci spojuje oba přístupy dohromady a pokouší se hledat závislosti mezi vytížením hardwarových komponent serveru a probíhajícími transakcemi. Kromě toho se v této práci podařilo popsat a ověřit metodiku nezávislou na standardním přístupu a to jsou způsoby hledání závislostí pomocí statistické metody Paersonova korelačního koeficientu a analýza zatížení pomocí neuronové sítě SOM. Zejména analýza pomocí SOM sítě poskytla poměrně ucelený a reálný pohled na vytížení serverů. Posuzování metodik analýz bylo provedeno na základě dat z měření reálných dvou SQL serverů, kde server Jupiter vykazoval velké výkonnostní problémy, zatímco server SQL pracoval optimálně. Aby bylo možné zmíněné analýzy provést, bylo nutné naplánovat správné kroky transformace dat. Podařilo se sestavit hodnotné SQL transformační skripty, které provádí převody datových typů, složité transpozice tabulek z řádků do sloupců, agregace a spojení dat čítačů a SQL transakcí. Díky provedeným analýzám byla odhalena příčina kritického stavu serveru Jupiter uložená procedura "exec StandardDatasetMaintenance", která prováděla velké datové operace v databázi aplikace Microsoft System Center Operations Manager. Po redukci dat v této databázi začal server Jupiter pracovat optimálně, zmizelo přetížení celého diskového pole SAN a došlo ke zrychlení odezvy ostatních připojených serverů. V poslední fázi této práce byl proveden návrh aplikace na automatizované měření a vyhodnocení čítačů a SQL transakcí. V rámci návrhu byly nalezeny technologie, které dovolují měřit čítače a trasovat SQL server nezávisle na nástrojích System Monitor a SQL Profiler. V návrhu byla vymyšlena celá funkcionalita aplikace. V závěru práce bylo provedeno hodnocení a ověření prototypové aplikace SqlMonitor, která byla vyvinuta podle návrhu. Bylo zjištěno, že aplikace dokáže data z čítačů a SQL transakce souběžně měřit i při definici vyššího počtu čítačů. Zároveň aplikace dokáže data

53

54

KAPITOLA 7. ZÁVĚR

čítačů ukládat v databázi ve formě jedné finální tabulky a díky tomu odpadá zdlouhavá a náročná transpozice řádků na sloupce. Z prototypu aplikace bude vytvořen úplný nástroj na měření a vyhodnocení databázových serverů. Autor této práce pracuje jako konzultant pro databázové servery Microsoft a díky použití aplikace SqlMonitor dojde k výraznému zkrácení doby analýzy z několika dní na několik hodin. To povede k výraznému snížení nákladů na výkonnostní analýzy a snížení ceny za jejich provádění. Dalším možným vývojem v oblasti výkonnostních analýz databázových serverů je zkoumání a optimalizace paralelního přístupu uživatelů k databázovým objektům, tzv. zamykání objektů a vyhodnocování indexových struktur. Tyto oblasti bývají dalším významným prvkem, který může ovlivnit rychlost odezvy SQL transakcí.

Literatura [1] S. Agarwal, B. Baryshnikov, T. Davidson, K. Elmore, D. Ribeiro, and J. Thomas. Troubleshooting Performance Problems in SQL Server 2005. http://technet.microsoft.com/en-us/library/cc966540.aspx#EGAA, stav z 13. 5. 2010. [2] B. Dorr. SQL Server 2000 I/O Basics. http://technet.microsoft.com/en-us/library/cc966500.aspx#EFAA, stav z 13. 5. 2010. [3] T. Kohonen. The Self-Organizing Map (SOM). http://www.cis.hut.fi/somtoolbox/theory/somalgorithm.shtml, stav z 13. 5. 2010. [4] B. McGehee. Performance audit - SQL Server Hardware Performance Checklist. http://www.sql-server-performance.com/articles/per/performance_audit_ part2_p1.aspx, stav z 13. 5. 2010. [5] Seagate product manual Barracuda 36es2 Family. http://www.seagate.com/staticfiles/support/disc/manuals/scsi/100182971B. pdf, stav z 10. 4. 2010. [6] SQL Server 2005 Books Online (November 2008) - Monitoring Disk Usage. http://msdn.microsoft.com/en-us/library/ms175903%28v=SQL.90%29.aspx, stav z 13. 5. 2010. R ns 10k.2SAS. [7] Seagate product manual Cheetah http://www.seagate.com/staticfiles/support/disc/manuals/enterprise/ cheetah/NS/Cheetah20NS2010K.2/100516228c.pdf, stav z 10. 4. 2010.

[8] Seagate product manual Cheetah 15k.7 SAS. http://www.seagate.com/staticfiles/support/disc/manuals/enterprise/ cheetah/15K.7/SAS/100516226b.pdf, stav z 10. 4. 2010. [9] Enterprise Architect - UML Design tools and UML CASE tools for software developments.

55

56

LITERATURA

http://www.sparxsystems.com.au/products/ea/index.html, stav z 13. 5. 2010. [10] MathWorks - MATLAB and Simulink for Technical Computing. www.mathworks.com, stav z 13. 5. 2010. [11] Visual Basic and Visual C# Concepts - Creating Custom performance counters. http://msdn.microsoft.com/en-us/library/5e3s61wf%28VS.71%29.aspx, stav z 10. 4. 2010. [12] .NET Framework Class Library - PerformanceCounter Class. http://msdn.microsoft.com/en-us/library/system.diagnostics. performancecounter%28v=VS.71%29.aspx, stav z 13. 5. 2010. R 10k.4 SAS. [13] Seagate product manual Savvio http://www.seagate.com/staticfiles/support/disc/manuals/enterprise/ savvio/10K.4/100539221c.pdf, stav z 10. 4. 2010. R 15k.2 SAS. [14] Seagate product manual Savvio http://www.seagate.com/staticfiles/support/disc/manuals/enterprise/ savvio/Savvio2015K.2/100516230c.pdf, stav z 10. 4. 2010.

[15] Microsoft System Center 2007 product page. http://www.microsoft.com/systemcenter/en/us/operations-manager.aspx, stav z 13. 5. 2010. [16] Microsoft Office SharePoint Server 2007 product page. http://www.microsoft.com/cze/sharepoint/produkt/default.aspx, stav z 13. 5. 2010. [17] Microsoft Knowledge Base KB270599 - HOW TO: Programmatically Load Trace Files into Tables. http://support.microsoft.com/?scid=kb%3Ben-us%3B270599&x=19&y=13, stav z 13. 5. 2010. R EE25.2 SeriesTM drives vs. [18] Technology Paper, Taking Storage to Extremes, Seagate other drives, flash SSDs. http://www.seagate.com/docs/pdf/whitepaper/tp594_storage_extremes.pdf, stav z 13. 5. 2010.

[19] Trace and Replay Objects: A New API for SQL Server Tracing and Replay. http://msdn.microsoft.com/en-us/library/ms345134%28SQL.90%29.aspx, stav z 13. 5. 2010.

Příloha A

Transformační SQL skripty A.1

SQL skript pro vytvoření souhrnu průměrných hodnot čítačů System Monitor

DECLARE @StartCounterDateTime DATETIME DECLARE @EndCounterDateTime DATETIME -- nastavení začátku intervalu do proměnné SET @StartCounterDateTime = ’2010-03-17 10:00:00’ -- nastavení konce intervalu do proměnné SET @EndCounterDateTime = ’2010-03-17 16:00:00’ -- deklarace proměnné @CounterID, do které je dynamicky kurzorem -- CursorVar načítána hodnota identifikátoru z tabulky CounterDetails -- pro každý čítač samostatně DECLARE @CounterID int DECLARE CursorVar CURSOR FOR SELECT CounterID FROM dbo.CounterDetails ORDER BY MachineName, ObjectName, CounterName OPEN CursorVar FETCH NEXT FROM CursorVar INTO @CounterID; WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO SqlAvgCounter (CounterID,Counter,AvgValue,StartCounterDateTime,EndCounterDateTime) -----

Spojení jedné z datových tabulek CounterData spolu s tabulkou CounterDetails a vygenerování sdruženého názvu čítače pod názvem Tento dotaz je filtrován podle dynamicky generovaného CounterID a zvoleného intervalu.

57

Counter.

58

PŘÍLOHA A. TRANSFORMAČNÍ SQL SKRIPTY

SELECT D.CounterID, MachineName + ’\ ’ + ObjectName + ’: ’ + CounterName + ’: ’ + ISNULL(InstanceName, ’’) Counter, AVG(CounterValue) AvgValue, @StartCounterDateTime StartCounterDateTime, @EndCounterDateTime EndCounterDateTime FROM dbo.CounterData D JOIN dbo.CounterDetails C ON D.CounterID = C.CounterID WHERE D.CounterID = @CounterID AND CounterDateTime >= @StartCounterDateTime AND CounterDateTime <= @EndCounterDateTime GROUP BY D.CounterID, MachineName, ObjectName, CounterName, InstanceName FETCH NEXT FROM CursorVar INTO @CounterID END CLOSE CursorVar; DEALLOCATE CursorVar;

A.2

Skript na transpozici čítačů z řádků do sloupců

-- Skript na automatizovanou transpozici čítačů z řádkové do sloupcové podoby. -- Minimální verze: SQL 2005 -- Autor: Jiří Rudolf, 30. 3. 2010 DECLARE @ExportTable VARCHAR(255); SET @ExportTable = ’CouterDataPivot’ -- Tabulka do které bude provedena transformace DECLARE @CmdPart1 VARCHAR(MAX); SET @CmdPart1 = ’SELECT [CounterDateTime]’ DECLARE @CmdPart2 VARCHAR(MAX); SET @CmdPart2 = ’ INTO ’ + @ExportTable + ’ FROM (SELECT [CounterDateTime], [CounterID], [CounterValue] FROM CounterData) A PIVOT (AVG (CounterValue) FOR CounterID IN (’ -- Obě proměnné typu String budou po sestavení spojeny a spuštěny jako SQL příkaz DECLARE @CmdDropTable VARCHAR(MAX); SET @CmdDropTable = ’DROP TABLE [dbo].[’ + @ExportTable + ’]’; -- Automatické smazání transponované tabulky DECLARE @CounterID int DECLARE CursorVar CURSOR FOR SELECT CounterID FROM dbo.CounterDetails ORDER BY MachineName, ObjectName, CounterName OPEN CursorVar FETCH NEXT FROM CursorVar INTO @CounterID; -- Do proměnné @CounterID se načítá obsah kurzoru WHILE @@FETCH_STATUS = 0 BEGIN SET @CmdPart1 = @CmdPart1 + (SELECT ’, [’ + CONVERT(VARCHAR(10), @CounterID) + ’] [’ + MachineName + ’\ ’

A.3. SKRIPT SLOUČENÍ TABULEK SQL PROFILER A SYSTEM MONITOR.

59

+ (SELECT REPLACE((SELECT REPLACE(ObjectName, ’[’, ’’)), ’]’, ’’)) + ’: ’ + (SELECT REPLACE((SELECT REPLACE(CounterName, ’[’, ’’)), ’]’, ’’)) + ’: ’ + (SELECT REPLACE((SELECT REPLACE(ISNULL(InstanceName, ’’), ’[’, ’’)), ’]’, ’’)) + ’]’ FROM CounterDetails WHERE CounterID = @CounterID) SET @CmdPart2 = @CmdPart2 + (SELECT ’[’ + CONVERT(VARCHAR(10), @CounterID) + ’], ’) FETCH NEXT FROM CursorVar INTO @CounterID END CLOSE CursorVar; DEALLOCATE CursorVar; SET @CmdPart2 = (SELECT REPLACE(@CmdPart2, \\ (SELECT ’[’ + CONVERT(VARCHAR(10), @CounterID) + ’],’), \\ (SELECT ’[’ + CONVERT(VARCHAR(10), @CounterID) + ’]’))) SET @CmdPart2 = @CmdPart2 + ’)) AS pvt ORDER BY [CounterDateTime]’ -- Sestavení příkazového stringu IF EXISTS (SELECT * FROM sys.objects WHERE object_id = \\ OBJECT_ID(N’[dbo].[’ + @ExportTable + ’]’) AND type in (N’U’)) EXEC (@CmdDropTable); -- Smazání exportované tabulky, pokud existuje EXEC (@CmdPart1 + @CmdPart2) -- Spuštění příkazového stringu

A.3

Skript sloučení tabulek SQL Profiler a System Monitor.

SELECT P.Duration, P.TextData, P.StartTime, P.EndTime, P.DatabaseName, P.CPU, P.Reads, P.Writes, AVG(CDP.[\\SQL\ Memory: Available Bytes: ]) AS [\\SQL\ Memory: Available Bytes: ], AVG(CDP.[\\SQL\ Memory: Pages/sec: ]) AS [\\SQL\ Memory: Pages/sec: ], AVG(CDP.[\\SQL\ PhysicalDisk: Avg. Disk sec/Read: 0 C: D:]) AS [\\SQL\ PhysicalDisk: Avg. Disk sec/Read: 0 C: D:], AVG(CDP.[\\SQL\ PhysicalDisk: Avg. Disk sec/Read: 1 E:]) AS [\\SQL\ PhysicalDisk: Avg. Disk sec/Read: 1 E:], AVG(CDP.[\\SQL\ PhysicalDisk: Avg. Disk sec/Write: 0 C: D:]) AS [\\SQL\ PhysicalDisk: Avg. Disk sec/Write: 0 C: D:], AVG(CDP.[\\SQL\ PhysicalDisk: Avg. Disk sec/Write: 1 E:]) AS [\\SQL\ PhysicalDisk: Avg. Disk sec/Write: 1 E:], AVG(CDP.[\\SQL\ PhysicalDisk: Disk Reads/sec: 0 C: D:]) AS [\\SQL\ PhysicalDisk: Disk Reads/sec: 0 C: D:], AVG(CDP.[\\SQL\ PhysicalDisk: Disk Reads/sec: 1 E:]) AS [\\SQL\ PhysicalDisk: Disk Reads/sec: 1 E:],

60

PŘÍLOHA A. TRANSFORMAČNÍ SQL SKRIPTY

AVG(CDP.[\\SQL\ PhysicalDisk: Disk Writes/sec: 0 C: D:]) AS [\\SQL\ PhysicalDisk: Disk Writes/sec: 0 C: D:], AVG(CDP.[\\SQL\ PhysicalDisk: Disk Writes/sec: 1 E:]) AS [\\SQL\ PhysicalDisk: Disk Writes/sec: 1 E:], AVG(CDP.[\\SQL\ Processor: % Processor Time: _Total]) AS [\\SQL\ Processor: % Processor Time: _Total] INTO CounterDataProfiler FROM Profiler AS P INNER JOIN CouterDataPivot AS CDP ON P.StartTime <= CDP.CounterDateTime AND P.EndTime >= CDP.CounterDateTime GROUP BY P.Duration, P.TextData, P.StartTime, P.EndTime, P.DatabaseName, P.CPU, P.Reads, P.Writes

A.4

Skript na výpis seznamu transakcí s celkovou nejvyšší dobou zpracování

SELECT TOP 10 (AVG([Duration]))/1000000*COUNT(*) SortCriteria, COUNT(*) Occurrence, TextData, DatabaseName, (AVG([Duration]))/1000000 [AvgDuration] FROM CounterDataProfiler WHERE (StartTime >= ’2010-03-16 5:00:00’) AND (EndTime < ’2010-03-16 16:00:00’) GROUP BY TextData, DatabaseName ORDER BY (AVG([Duration]))/1000000*COUNT(*) DESC

Příloha B

Tabulky

61

62

PŘÍLOHA B. TABULKY

Profiler CPU

Profiler Reads

Profiler Writes

JUPITER Memory: Pages/sec:

JUPITER PhysicalDisk: Avg. Disk sec/Read: 0 C:

JUPITER PhysicalDisk: Avg. Disk sec/Read: 2 F:

JUPITER PhysicalDisk: Avg. Disk sec/Write: 0 C:

JUPITER PhysicalDisk: Avg. Disk sec/Write: 2 F:

JUPITER PhysicalDisk: Disk Reads/sec: 0 C:

JUPITER PhysicalDisk: Disk Reads/sec: 2 F:

JUPITER PhysicalDisk: Disk Writes/sec: 0 C:

JUPITER PhysicalDisk: Disk Writes/sec: 2 F:

JUPITER Processor: % Processor Time: _Total

Matice korelačních koeficientů dat SQL Profiler a System Monitor pro server JUPITER

Profiler Duration

B.1

1,00 0,75 0,80 0,77 0,11 -0,23 -0,41 -0,24 -0,19 0,09 -0,02 -0,01 0,27 0,35

0,75 1,00 0,98 0,96 0,09 -0,07 -0,20 -0,04 -0,08 0,14 -0,08 0,09 0,35 0,49

0,80 0,98 1,00 0,98 0,08 -0,08 -0,22 -0,07 -0,09 0,11 -0,07 0,07 0,31 0,44

0,77 0,96 0,98 1,00 0,09 -0,07 -0,21 -0,06 -0,09 0,11 -0,06 0,07 0,33 0,44

0,11 0,09 0,08 0,09 1,00 0,00 -0,06 -0,07 -0,03 0,29 -0,04 0,02 0,06 0,05

-0,23 -0,07 -0,08 -0,07 0,00 1,00 0,38 0,25 0,10 -0,01 -0,30 0,02 0,22 0,10

-0,41 -0,20 -0,22 -0,21 -0,06 0,38 1,00 0,58 0,29 -0,07 -0,54 -0,01 -0,13 -0,37

-0,24 -0,04 -0,07 -0,06 -0,07 0,25 0,58 1,00 0,33 -0,04 -0,26 -0,04 0,16 -0,06

-0,19 -0,08 -0,09 -0,09 -0,03 0,10 0,29 0,33 1,00 -0,08 -0,26 -0,09 0,21 -0,18

0,09 0,14 0,11 0,11 0,29 -0,01 -0,07 -0,04 -0,08 1,00 -0,05 0,73 0,05 0,42

-0,02 -0,08 -0,07 -0,06 -0,04 -0,30 -0,54 -0,26 -0,26 -0,05 1,00 -0,07 -0,34 0,02

-0,01 0,09 0,07 0,07 0,02 0,02 -0,01 -0,04 -0,09 0,73 -0,07 1,00 -0,02 0,47

0,27 0,35 0,31 0,33 0,06 0,22 -0,13 0,16 0,21 0,05 -0,34 -0,02 1,00 0,59

0,35 0,49 0,44 0,44 0,05 0,10 -0,37 -0,06 -0,18 0,42 0,02 0,47 0,59 1,00

B.2. MATICE KORELAČNÍCH KOEFICIENTŮ DAT SQL PROFILER A SYSTEM MONITOR PRO SE

Profiler CPU

Profiler Reads

Profiler Writes

SQL Memory: Pages/sec:

SQL PhysicalDisk: Avg. Disk sec/Read: 0 C: D:

SQL PhysicalDisk: Avg. Disk sec/Read: 1 E:

SQL PhysicalDisk: Avg. Disk sec/Write: 0 C: D:

SQL PhysicalDisk: Avg. Disk sec/Write: 1 E:

SQL PhysicalDisk: Disk Reads/sec: 0 C: D:

SQL PhysicalDisk: Disk Reads/sec: 1 E:

SQL PhysicalDisk: Disk Writes/sec: 0 C: D:

SQL PhysicalDisk: Disk Writes/sec: 1 E:

SQL Processor: % Processor Time: _Total

Matice korelačních koeficientů dat SQL Profiler a System Monitor pro server SQL

Profiler Duration

B.2

1,00 -0,45 -0,44 -0,18 0,09 -0,09 -0,14 -0,11 -0,10 -0,25 -0,16 -0,30 -0,19 -0,61

-0,45 1,00 0,59 0,24 -0,06 -0,02 0,04 -0,01 0,01 0,21 0,05 0,34 0,10 0,52

-0,44 0,59 1,00 -0,01 -0,06 -0,03 -0,07 0,04 -0,02 0,09 -0,06 0,17 0,08 0,62

-0,18 0,24 -0,01 1,00 -0,01 0,01 0,13 0,03 0,09 0,48 0,52 0,72 0,18 0,14

0,09 -0,06 -0,06 -0,01 1,00 0,00 -0,01 -0,01 -0,01 0,11 0,36 -0,02 -0,03 -0,03

-0,09 -0,02 -0,03 0,01 0,00 1,00 0,12 0,82 0,95 0,04 0,04 0,01 0,45 -0,04

-0,14 0,04 -0,07 0,13 -0,01 0,12 1,00 0,06 0,06 0,21 0,63 0,15 0,11 0,01

-0,11 -0,01 0,04 0,03 -0,01 0,82 0,06 1,00 0,91 0,00 -0,01 0,00 0,81 -0,01

-0,10 0,01 -0,02 0,09 -0,01 0,95 0,06 0,91 1,00 0,02 -0,01 0,04 0,64 -0,03

-0,25 0,21 0,09 0,48 0,11 0,04 0,21 0,00 0,02 1,00 0,41 0,70 0,10 0,33

-0,16 0,05 -0,06 0,52 0,36 0,04 0,63 -0,01 -0,01 0,41 1,00 0,45 0,00 0,09

-0,30 0,34 0,17 0,72 -0,02 0,01 0,15 0,00 0,04 0,70 0,45 1,00 0,19 0,44

-0,19 0,10 0,08 0,18 -0,03 0,45 0,11 0,81 0,64 0,10 0,00 0,19 1,00 0,15

-0,61 0,52 0,62 0,14 -0,03 -0,04 0,01 -0,01 -0,03 0,33 0,09 0,44 0,15 1,00

64

PŘÍLOHA B. TABULKY

Příloha C

Matlab skripty C.1

Matlab skript pro vytvoření a vizualizaci SOM sítě.

% Analyza databazovych serveru pomoci SOM - Self-Organizing Map, Jiří Rudolf % Zpracovano pomoci SOM Toolbox 2.0 % http://www.cis.hut.fi/projects/somtoolbox/ clf reset; figure(gcf) echo on clc % % %

The SOM Toolbox has a special struct, called data struct, which is used to group information regarding the data set in one place.

% % % %

Here, a data struct is created using function SOM_DATA_STRUCT. First argument is the data matrix itself, then is the name given to the data set, and the names of the components (variables) in the data matrix.

pause % Strike any key to create SOM data struct ... SomData = som_data_struct([InputData],’name’,’Jupiter’,... ’comp_names’,{’Duration’,’CPU’,’Reads’,’Writes’,... ’\\JUPITER\ Memory: Available Bytes: ’,’\\JUPITER\ Memory: Pages/sec: ’,... ’\\JUPITER\ PhysicalDisk: Avg. Disk sec/Read: 0 C:’,... ’\\JUPITER\ PhysicalDisk: Avg. Disk sec/Read: 2 F:’,... ’\\JUPITER\ PhysicalDisk: Avg. Disk sec/Write: 0 C:’,... ’\\JUPITER\ PhysicalDisk: Avg. Disk sec/Write: 2 F:’,... ’\\JUPITER\ PhysicalDisk: Disk Reads/sec: 0 C:’,... ’\\JUPITER\ PhysicalDisk: Disk Reads/sec: 2 F:’,... ’\\JUPITER\ PhysicalDisk: Disk Writes/sec: 0 C:’,... ’\\JUPITER\ PhysicalDisk: Disk Writes/sec: 2 F:’,...

65

66

PŘÍLOHA C. MATLAB SKRIPTY

’\\JUPITER\ Processor: % Processor Time: _Total’}); SomData = som_data_struct([InputData],’name’,’SQL’,... ’comp_names’,{’Duration’,’CPU’,’Reads’,’Writes’,... ’\\SQL\ Memory: Available Bytes: ’,’\\SQL\ Memory: Pages/sec: ’,... ’\\SQL\ PhysicalDisk: Avg. Disk sec/Read: 0 C: D:’,... ’\\SQL\ PhysicalDisk: Avg. Disk sec/Read: 1 E:’,... ’\\SQL\ PhysicalDisk: Avg. Disk sec/Write: 0 C: D:’,... ’\\SQL\ PhysicalDisk: Avg. Disk sec/Write: 1 E:’,... ’\\SQL\ PhysicalDisk: Disk Reads/sec: 0 C: D:’,... ’\\SQL\ PhysicalDisk: Disk Reads/sec: 1 E:’,... ’\\SQL\ PhysicalDisk: Disk Writes/sec: 0 C: D:’,... ’\\SQL\ PhysicalDisk: Disk Writes/sec: 1 E:’,... ’\\SQL\ Processor: % Processor Time: _Total’}); % %

STEP 2: DATA NORMALIZATION ==========================

% % %

Since SOM algorithm is based on Euclidian distances, the scale of the variables is very important in determining what the map will be like. This can be done with function SOM_NORMALIZE:

pause % Strike any key to normalize data... SomData = som_normalize(SomData,’var’);

% %

STEP 3: MAP TRAINING ====================

% The function SOM_MAKE is used to train the SOM. By default, it % first determines the map size, then initializes the map using % linear initialization, and finally uses batch algorithm to train % the map. pause % Strike any key to create and train SOM... sMap = som_make(SomData);

% %

STEP 4: VISUALIZING THE SELF-ORGANIZING MAP: SOM_SHOW =====================================================

% The basic visualization of the SOM is done with function SOM_SHOW. pause % Strike any key to visualize SOM... som_show(sMap,’norm’,’d’) %som_show(sMap,’umat’,’all’)

C.1. MATLAB SKRIPT PRO VYTVOŘENÍ A VIZUALIZACI SOM SÍTĚ.

67

% STEP 4: VISUALIZING THE SELF-ORGANIZING MAP: SOM_GRID % ===================================================== pause % Strike any key to continue with 3D data... clf reset; figure(gcf) Co=som_unit_coords(sMap); U=som_umat(sMap); U=U(1:2:size(U,1),1:2:size(U,2)); som_grid(sMap,’Coord’,[Co, U(:)],’Surf’,U(:),’Marker’,’none’); view(-80,45), axis tight, title(’Distance matrix’)

pause % Strike any key to evaluate Final quantization error and Final topographic error. [qe,te] = som_quality(sMap,SomData)

68

PŘÍLOHA C. MATLAB SKRIPTY

Příloha D

XML soubory D.1

Příklad souboru expertních znalostí pro databázové servery

<sqlmon> Memory Pages/sec 18 <error>20 Memory Available Bytes 152043520 <error>146800640 Network Interface Bytes Total/sec * 100

69

70

PŘÍLOHA D. XML SOUBORY

7300000 <error>7340000 1000 73000000 <error>73400000 PhysicalDisk % Disk Time * 80 <error>90 PhysicalDisk Avg. Disk sec/Read * 0,015 <error>0,017 PhysicalDisk Avg. Disk sec/Write * 0,015 <error>0,017 Processor % Processor Time _Total

D.1. PŘÍKLAD SOUBORU EXPERTNÍCH ZNALOSTÍ PRO DATABÁZOVÉ SERVERY71

60 <error>75 SQLServer:Buffer Manager Buffer cache hit ratio 99,2 <error>98,9 <problems> <problem> <problemCategory>Memory swapping <description> Nedostatečná velikost paměti, která způsobuje vysoké stránkování na disk. Přidejte RAM. Zkontrolujte zda OS a SQL Server podporují větší kapacitu RAM. Pokud ne, přeinstalujte na vyšší edice, popřípadě na architekturu x64 a přidejte RAM. Zkontrolujte, zda není v instanci SQL serveru nastaven "Max Memory Limit", který omezuje využití volné RAM. <detection> 1 2 4 <detection> 1 2 <detection> 1 7

72


<problem> <problemCategory>Memory memory shortage <description> Nedostatečná velikost paměti. Přidejte RAM. Zkontrolujte zda OS a SQL Server podporují větší kapacitu RAM. Pokud ne, přeinstalujte na vyšší edice, popřípadě na architekturu x64 a přidejte RAM. <detection> 2 <detection> 2 8 <problem> <problemCategory>File system operations <description> Vysoké stránkování mezi paměti a diskem je způsobeno nedatabázovými operacemi se soubory, jako je kopírování, zálohování. Zjistěte zdroj souborových operací. Zkontrolujte procesy, otevřené soubory vzdálených uživatelů, zálohovací úlohy. <detection> 1

<problem> <problemCategory>Disk read delay

D.1. PŘÍKLAD SOUBORU EXPERTNÍCH ZNALOSTÍ PRO DATABÁZOVÉ SERVERY73

<description> Vysoké zpoždění při čtení z disku Snižte zátěž disku. Omezte operace čtení. Pokud disk obsahuje swapovací soubor, převeďte jej jinam. Pro diskové pole překonfigurujte uspořádání disků na rychlejší typ RAID-5 nebo RAID-10. Pro diskové pole přidejte další disky do RAID. Pro diskové pole zvyšte velikost jeho vyrovnávací cache. Pro diskové pole izolujte LUN na dedikovaný RAID. <detection> 5 <problem> <problemCategory>Disk write delay <description> Vysoké zpoždění při zápisu z disku Snižte zátěž disku. Omezte operace zápisu. Pokud disk obsahuje swapovací soubor, převeďte jej jinam. Pro diskové pole překonfigurujte uspořádání disků na rychlejší typ RAID-10.

74


Pro diskové pole přidejte další disky do RAID. Pokud je vyrovnávací cache vybavena záložní baterií nastavte ražime cache "Write Back", pokud je to možné nastavte vyšší velikost této cache. Pro diskové pole izolujte LUN na dedikovaný RAID. <detection> 6 <problem> <problemCategory>CPU high usage <description> Procesor je přetížen. Zkontrolujte procesy a zjištěte zdroj vytížení CPU a omezte jeho vliv. Pokud je zdrojem zatížení CPU SQL server a pokud server, OS a SQL Server podporují přidání CPU přidejte v souladu s licenčními podmínkami SQL Serveru další CPU. <detection> 1

Příloha E

Seznam použitých zkratek SOM Self-organizing map WMI Windows Management Instrumentation RAID Redundant Array of Inexpensive Disks OLTP Online Transaction Processing IOPS Input Output Operations per Second SAN Storage Area Network BMU Best-Matching Unit

75

76

PŘÍLOHA E. SEZNAM POUŽITÝCH ZKRATEK

Příloha F

UML diagramy F.1

Model jednání

77

78

PŘÍLOHA F. UML DIAGRAMY

Obrázek F.1: Model jednání - Sestavení souboru expertních znalostí.

F.1. MODEL JEDNÁNÍ

Obrázek F.2: Model jednání - Inicializace připojení.

79

80


Obrázek F.3: Model jednání - Nastavení měření čítačů.


Obrázek F.4: Model jednání - Nastavení měření SQL transakcí.

81

82


Obrázek F.5: Model jednání - Ovládání měření.


Obrázek F.6: Model jednání - Vyhodnocení čítačů.

83

84


Obrázek F.7: Model jednání - Vyhodnocení SQL transakcí.

Příloha G

Obsah přiloženého CD

Obrázek G.1: Seznam přiloženého CD

85

Automatická analýza výkonnosti databázových serverů. Jiří Rudolf

Recommend Documents