Masarykova univerzita Fakulta informatiky
Výkonnostní srovnání relačních databází Bakalářská práce
Lukáš Košárek 2010
Prohlašuji, že tato práce je mým původním autorským dílem, které jsem vypracoval samostatně. Všechny zdroje, prameny a literaturu, které jsem při vypracování používal nebo z nich čerpal, v práci řádně cituji s uvedením úplného odkazu na příslušný zdroj. Brno 20.5.2010
2
Děkuji RNDr. Vlastislavu Dohnalovi, Ph.D. za odborné vedení práce a pomoc při zajištění hardwarových prostředků.
3
Shrnutí Cílem této bakalářské práce je otestování výkonu vybraných relačních databázových systémů. Testování bude probíhat pomocí vytvořené aplikace, která simuluje připojení více uživatelů najednou a měří čas prováděných operací. V první části práce se s vybranými systémy krátce seznámíme. Následovat bude část věnovaná hlavnímu testu, ve které bude představena koncepce testu, databázové schéma, prováděné dotazy a postup při testování. Závěrečná část práce odhalí výsledky prováděných testů, vyvstalé problémy a závěr.
Klíčová slova Databáze, RDBMS, testování, výkon, propustnost databáze, MySQL, PostgreSQL, Firebird, Oracle, Microsoft SQL server.
4
Obsah 1
Úvod..................................................................................................................................... 7
2
Testované databázové systémy ........................................................................................ 8 2.1 2.1.1
MySQL ......................................................................................................................... 8 Datová úložiště ................................................................................................... 8
2.2
PostgreSQL.................................................................................................................. 9
2.3
Firebird ...................................................................................................................... 10
2.4
Microsoft SQL Server............................................................................................... 11
2.5
Oracle ......................................................................................................................... 12
2.6
Použité verze a nastavení testovaných databázových systémů ........................ 13
3
Použitý hardware ............................................................................................................. 14
4
Komplexní test .................................................................................................................. 15 4.1
Průběh testu .............................................................................................................. 15
4.2
Datový model ........................................................................................................... 16
4.3
Popis a naplnění tabulek ......................................................................................... 17
4.4
Prováděné bloky dotazů ......................................................................................... 21
4.4.1
Objednávka ....................................................................................................... 22
4.4.2
Náhled detailu zboží........................................................................................ 25
4.4.3
Přehled zboží dle kategorií ............................................................................. 25
4.4.4
Řazený přehled zboží ...................................................................................... 26
4.4.5
Přihlašování uživatelů ..................................................................................... 26
4.4.6
Kontrola tržeb ................................................................................................... 26
4.4.7
Kontrola objednávek........................................................................................ 26
4.4.8
Informace o uživateli ....................................................................................... 27
4.5
Indexy a integritní omezení .................................................................................... 27
5
5
4.6
Postup při testování ................................................................................................. 28
4.7
Výsledky testů .......................................................................................................... 28
4.7.1
Vliv indexů na cizích klíčích ........................................................................... 29
4.7.2
Propustnost v závislosti na počtu vláken ..................................................... 30
Vzniklé problémy ............................................................................................................. 37 5.1
MySQL ....................................................................................................................... 37
5.2
PostgreSQL................................................................................................................ 37
5.3
Firebird ...................................................................................................................... 37
5.4
Microsoft SQL Server............................................................................................... 38
5.5
Oracle XE ................................................................................................................... 38
6
Závěr .................................................................................................................................. 39
7
Bibliografie ........................................................................................................................ 40
6
1 Úvod S rozvojem výpočetní techniky přichází stále silnější potřeba ukládat a zpracovávat větší a větší objemy informací. Už ve svých počátcích byla výpočetní technika využívána zejména pro zpracování velkého množství dat. S jejich neustálým nárůstem bylo nevyhnutelné oddělení těchto dat od programové části. A právě to dalo příležitost ke vzniku prvních databázových systémů. Dnes jsou databázové systémy důležitou a nepostradatelnou součástí v mnoha odvětvích informatiky. Vyžadujeme od nich především spolehlivé ukládání, vyhledávání a zpracovávání velkého množství informací. Krom těchto základních požadavků nám zprostředkovávají spoustu dalších užitečných funkcí. Mezi ně patří například multiuživatelský a multiaplikační přístup k informacím, nebo také jejich zabezpečení před neoprávněným použitím. Důležitým parametrem těchto databázových systémů je rychlost, jakou nám jsou schopné dané služby poskytovat. A právě na měření a porovnávání rychlosti zpracování velkého množství informací bude tato práce zaměřena. V následující 2. kapitole se blíže seznámíme s výběrem testovaných systémů. Krátce bude zmíněna jejich historie, důležité parametry a počáteční nastavení. Ve 3. kapitole bude popsán hardware, na kterém byly testy prováděny. Následovat bude 4. kapitola s podrobným popisem komplexního testu včetně databázového schéma, naplnění tabulek a průběhu testování. Závěr 4. kapitoly odkrývá výsledky provedených testů. Kapitola č. 5 je věnována problémům, které se během celého testování vyskytly. Závěr shrnuje dosažené výsledky a přinesená pozitiva celé práce.
7
2 Testované databázové systémy Hlavní roli při výběru níže uvedených systémů hrála jejich dostupnost. Až na jedinou výjimku to byly verze dostupné zdarma. Výjimkou byl komerční Microsoft SQL Server 2008, který byl k dispozici na testovacím serveru. Z pochopitelných důvodů nebylo možné otestovat všechny zdarma dostupné DB systémy. Vybráno bylo pouze několik běžně a často používaných.
2.1 MySQL V současné době velmi oblíbený open source relační databázový systém. Jeho výrobce uvádí, že za dobu jeho existence, bylo staženo více než 100 milionů kopií. [1] MySQL je často využívána na web serverech pro potřeby webových aplikací. V kombinaci Linux, PHP a Apache tvoří oblíbený zaklaní web serverový balíček, známý pod zkratku LAMP. Jedná se o multiplatformní databázi, která je dostupná pod dvěma licencemi. Základní verze, MySQL Community Server, je zdarma distribuovaná pod licencí GNU. MySQL Enterprise, je komerční, placená verze, obsahující rozšířené funkce a nástroje. [2] Za zrodem MySQL stojí především finský programátor Michael Widenius, respektive švédská firma MySQL AB, kterou v roce 1995 založil společně s Davidem Axmarkem a Allanem Larssonem. Nyní MySQL spadá pod společnost Sun Microsystems, dceřiné společnosti Oracle Corporation, která firmu MySQL AB v roce 2008 koupila. [3]
2.1.1 Datová úložiště MySQL má k dispozici několik datových úložišť. Tato úložiště je možné kombinovat i v jedné databázi na úrovni kompletních tabulek. Tímto se značně liší oproti konkurenci. Dává uživateli možnost vybrat si pro konkrétní tabulku takové datové úložiště, které bude nejlépe vyhovovat jeho požadavkům. Mezi nejznámější a nejčastěji používané patří MyISAM a InnoDB. Existují ale i další jako například Memory (heap), Falcon nebo solidDB. [4] 2.1.1.1 MyISAM MyISAM je základním úložištěm MySQL a vychází z jeho předchůdce ISAM. Je jedním z nejstarších datových úložišť v MySQL. Podporuje například fulltextové indexy, je 8
možné indexovat i prvních 500 znaků typu text a blob. Dále podporuje ukládání prostorových dat a jejich indexování. Maximální velikost úložiště je 256 TB. Hlavní nevýhoda MyISAM a dnes také častý důvod proč se přechází na úložiště InnoDB, je chybějící podpora cizích klíčů a transakčního zpracování dotazů. To ovšem nemusí být vždy nevýhoda. Pokud například nepotřebujeme od databáze zaručovat konzistenci dat a požadujeme co nejvyšší rychlost, potom MyISAM může být tím správným řešením. Další nevýhoda je velmi hrubé zamykání záznamů, které je možné provádět pouze na úrovni celých tabulek. Každá tabulka tohoto úložiště je rozdělena do dvou, respektive do tří souborů. .MYD – Soubor obsahující data tabulky. .MYI – Soubor obsahující indexy tabulky. .FRM – Tento soubor sám o sobě není součástí datového úložiště MyISAM. Patří přímo k MySQL serveru. Jsou v něm uloženy definice tabulky. [5] 2.1.1.2 InnoDB InnoDB je datové úložiště podporující transakční zpracování dotazů. Zaručuje konzistenci dat – ACID – a je možné provádět klasické operace jako rollback a commit. Je zde také implementovaná podpora cizích klíčů, která ve starším úložišti MyISAM chyběla. Pokrok nastal i v zamykání záznamů, které je v InnoDB možné provádět na úrovni jednotlivých řádků tabulky, nikoliv na celých tabulkách. Je možné zde ukládat prostorová data, ale bez podpory indexace. Oproti MyISAM také chybí fulltextový index. Úložiště InnoDB bylo vytvořeno pro MySQL externí firmou Innobase Oy. Stejně jako několik dalších úložišť, např. solidDB, NitroEDB, Infobright nebo Kickfire. [6]
2.2 PostgreSQL PostgreSQL je objektově-relační databázový systém, šířený pod licencí MIT. Je tedy zdarma a jsou k dispozici jeho otevřené zdrojové kódy. Historie PostgresSQL, respektive Postgres, jak se dříve databázový systém jmenoval, sahá do roku 1986. Za jeho zrodem stojí Michael Stonebraker z University of California v Berkeley. Milníkem ve vývoji Postgresu je rok 1996, kdy byl dotazovací jazyk POSTQUEL nahrazen podmnožinou jazyka SQL a celý vývoj se odklonil od akademické obce směrem k světu open
9
source. Od té doby prošel PostgreSQL značným vývojem a díky práci stovek vývojářů z celého světa patří dnes ke špičce v oboru databázových systémů. [7] Jedná se o multiplatformní databázový systém, použitelný pod systémy Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64) i Windows. Podporuje transakční zpracování dotazů i kontrolu cizích klíčů. Je tedy plně ACID kompatibilní a automaticky zaručuje konzistenci dat v databázi. Jsou zde také implementovány vnořené transakce. Maximální velikost tabulky činí 32 TB. Velikost samotné databáze není nijak omezena. [8] Podpora ukládání a práce s vícerozměrnými daty je možná přes nástavbu PostGIS. Ta je vyvíjena také pod open source licencí GNU a nabízí široké uplatnění v oblasti geografických informačních systémů. Zajímavou vlastností PostgreSQL je dědičnost. Tabulka může být nastavena tak, aby přebírala některé vlastnosti rodičovské tabulky. V praxi se jedná o dědění sloupců rodičovské tabulky do tabulky potomka. S tímto rozdílným přístupem k databázi přichází také jisté problémy. Když například v jedné tabulce potomka vytvoříme záznam s primárním klíčem x a v tabulce druhého potomka vytvoříme jiný záznam s primárním klíčem x, budeme mít v rodičovské tabulce dva záznamy s duplicitním primárním klíčem. Druhý problém nastává při odkazování přes cizí klíč. Budeme-li se chtít pomocí cizího klíče odkázat na tabulku rodiče, můžeme se odkázat pouze na záznamy, které jsou přímo v rodičovské tabulce, nikoli na záznamy v tabulkách potomků. I přes tyto problémy se ale najdou situace, ve kterých nám dědičnost může ulehčit práci a umožní nám pohled na databázi z objektově orientovaného světa. [9] Dědičnosti se například s výhodou využívá při horizontálním dělení databáze, tzv. partitioning. Kdy je potřeba velkou tabulku, rozdělit na více částí. [10]
2.3 Firebird Firebird vychází z databázového systému InterBase 6.0 od společnosti Borland. Jedná se o vývojovou větev zmíněného InterBase, která vznikla po tom, co se společnost Borland rozhodla uvolnit zdrojové kódy svého systému. Od roku 2000, kdy Firebird vznikl, pokračuje svou vlastní cestou pod vedením neziskové organizace Firebird Foun-
10
dation. Ta se také stará o vývoj dalšího souvisejícího software a obslužných nástrojů. [11] Firebird spadá pod volnou open source licenci Initial Developer's public licence. Ta vychází z licence Mozilla Public License a dovoluje bezplatné používání, bez jakýchkoliv omezení funkčnosti. Je také možné měnit zdrojové kódy a takto upravený systém dále distribuovat pod libovolnou jinou licencí. [12] Firebird oficiálně podporuje operační systémy Windows, Linux, MacOSX a Solaris. Z pohledu funkčnosti nabízí Firebird transakční zpracování dotazů i podporu cizích klíčů. Je plně ACID a zaručuje tak konzistenci dat databáze. Je možné vytvářet triggery a uložené procedury. Celá databáze firebidru, včetně tabulek, indexů a trigrů, je uložena v jednom souboru s příponou FDB. Ten je možné uložit na libovolné místo i mimo vlastní adresář databáze a popřípadě ho jednoduše přesouvat kopírováním.
2.4 Microsoft SQL Server Také firma Microsoft má na trhu s relačními databázovými systémy svého zástupce. Do tohoto odvětví vstoupil Microsoft poprvé v roce 1989 s produktem SQL Server 1.0. Ten vyvíjel společně s firmami Sybase a Ashton-Tate. Jednalo se o databázový server určený pro systém OS/2. S nástupem Windows NT byla vydána verze SQL Serveru 4.21, která byla určena právě pro tento operační systém. Poté se Microsoft začal od spolupracujících firem odvracet a pokračoval ve vývoji SQL Serveru samostatně. Až do nedávné doby byly ke komerčnímu využití k dispozici pouze placené verze SQL Serveru. Jistou výjimku tvořila verze Developer Edition. Ta byla k dispozici ke stažení a používání zdarma. Ovšem pouze k developerskému vývoji programů a pro studenty. Komerční využití bylo licencí zakázáno. [13] V současné době je k dispozici Microsoft SQL Server 2008. Zde se jedná o první verzi tohoto systému, která byla uvolněna zdarma i pro komerční použití. Pochopitelně s jistými omezeními. Tato omezení se týkají maximální velikosti databáze a maximálního využití hardware.
11
Velikost databáze je omezena na 4 GB. Dále je omezena velikost využitelné operační paměti, kde je limit stanoven na 1 GB. Do tohoto limitu se nezapočítává běh serveru, ale pouze načtená data. Posledním omezením je využitelnost procesoru. Express Edition dokáže využít pouze potenciál jednoho fyzického procesoru. Co se týče funkčnosti, nabízí Microsoft vcelku širokou paletu funkcí a vylepšení. Zaručení konzistence dat pomocí transakčního zpracování a podporou cizích klíčů je samozřejmostí. Za zmínku stojí integrování fulltextového vyhledávání přímo do databázového systému, které bylo přidáno v poslední verzi. Stejně tak byla přidána lepší podpora pro zacházení s multimediálními objekty v databázi. Rozšíření podpory se dočkalo také ukládání prostorových informací. V databázi je také možné pracovat s XML daty. [14] K řízení a správě databáze Microsoft automaticky dodává SQL Server Managmet Studio. To je, dle mého názoru, velmi dobře zpracované a ulehčuje celou správu databáze.
2.5 Oracle Oracle Corporation je největším světovým výrobcem podnikového software. [15] Jeho specializací jsou především databázové systémy, jejichž vývojem se zabývá již více než 30 let. Zakladatelem a vůdčí osobností společnosti je Larry Ellison. První Oracle databáze, Oracle Version 2, byla vůbec první relační databázový systém na trhu. Také byla první, kdo implementoval jazyk SQL. S každou další verzí přicházel Oracle s něčím novým a na svou dobu téměř přelomovým. Od verze 3 se stal tento RDBMS přenositelný na různé platformy. Verze 4 přinesla zaručení konzistence dat. Verze 5 umožňovala provozovat distribuované databázové systémy. Verze 7 přichází s programovatelnou databází. Osmá verze se soustředí na ukládání a zpracování rozmanitých druhů dat. Poté přišly verze 8i a 9i, zaměřené na internetové aplikace. Verze 10g (grid) a aktuální 11g se soustředí na ukládání vícerozměrných informací. [16] Všechny zmíněné verze, vyjma Oracle 10g XE, jsou komerční. Oracle 10g XE se stal první zdarma poskytnutou databází této firmy. Má daná omezení na velikost databáze i schopnosti využít dostupné hardwarové prostředky. Omezení se týká operační paměti, kde je maximum stanoveno na 1 GB RAM a to včetně běhu samotného Oracle. Další omezení je kladeno na procesor. V případě víceproce-
12
sorového stroje připustí databáze využití výkonu maximálně ekvivalentu jednoho procesoru, respektive jednoho jádra procesoru. Každé jádro procesoru je v tomto případě bráno jako 1 procesor. Posledním omezením je limitovaná velikost databáze a to na 4 GB. [17]
2.6 Použité verze a nastavení testovaných databázových systémů Vybrány byly aktuální oficiální verze systémů (Únor 2010). V základním nastavení byly změněny pouze parametry pro maximální počet připojených uživatelů, aby server při provádění testů neodmítal další požadovaná spojení. U některých DB systémů bylo potřeba k úspěšnému provedení testů změnit ještě další parametry. Konkrétně šlo transakční zpracování u Microsoft SQL serveru. Podrobnější informace budou uvedeny v kapitole 5. Ostatní nastavení bylo pokud možno ponecháno tak, jak bylo výrobcem doporučeno a přednastaveno při instalaci. Pro MySQL bylo vybráno datové úložiště InnoDB, jelikož podporuje transakční zpracování, které je v testu vyžadováno. MySQL server byl nastaven jako dedicate server, neomezující hardwarové prostředky. Firebird byl nainstalován ve verzi clasic, která spouští samostatné vlákno pro každé připojení k databázi. Seznam použitých verzí: 1) MySQL 5.1.45 2) Postgresql 8.4.2 3) Firebird 2.1.3 4) Oracle 10g XE 5) MS SQL 2008 Express / MS SQL 2008
13
3 Použitý hardware Testovalo se na dvou počítačích. Prvním z nich byl špičkový server, který databázovým systémům poskytnul maximální možný výkon a nechal je ukázat jejich plný potenciál. Podrobnější popis jeho sestavy naleznete níže.
HP Server ammon.fi.muni.cz CPU: 2x Intel Xeon X5460 3.16 GHz RAM: 16 GB HDD: 4x 150GB s 15 000 rpm v poli RAID 5 pro ukládání dat 1x 150GB s 15 000 rpm jako systémový OS:
Windows Server 2008 standard
Druhým počítačem byl běžný notebook. Ten měl představovat slabý stroj, který bude databázové systémy omezovat pomalým HDD, procesorem i málo pamětí RAM. Cílem bylo zjistit, jak se jednotlivé databáze vyrovnají s tímto hardwarovým omezením a jaké budou rozdíly mezi výkonem jednotlivých databází na různých strojích. Níže jsou uvedeny jeho parametry.
Fujitsu Siemens AMILO Pro V3515 CPU: Intel Core 2 T5300 1,73 GHz RAM: 2,49 GB HDD: 250 GB 7200 rpm OS:
Windows XP Home OEM
14
4 Komplexní test Tento test se snaží o kompletní a co možná nejvíce reálnou zátěž databázového serveru. Jsou v něm zahrnuty dotazy typu SELECT, INSERT, UPDATE i DELETE. Ty jsou prováděny jak v transakcích, tak i mimo ně. Konzistence dat, zaručená právě transakčním zpracováním dotazů a kontrolou cizích klíčů, byla v testu důležitým faktorem. Cílem bylo také otestovat, jak se databázový server bude chovat v případě, že dojde k uváznutí transakce – deadlocku. K těmto situacím dochází tehdy, když si dvě nebo více transakcí navzájem, uzamknou zpracovávané řádky a čekají na jejich uvolnění. Další důležitou a mnohdy často používanou součástí databázových systémů jsou řadící algoritmy. Pro jejich otestování je v testu zařazen blok s klauzulí ORDER BY, která výsledný výběr dat řadí dle zadaných kritérií. Právě řazení většího objemu dat, často velmi zpomaluje celý databázový server a odčerpává značné hardwarové prostředky. Nicméně i tyto náročné dotazy jsou dnes po databázových serverech vyžadovány. Jak už bylo zmíněno, hlavním cílem testu není změřit pouze čas provedení dotazů zaslaných jedním uživatelem. Takový scénář v reálném světě nebývá příliš častý. Opravdový, mnohem realističtější náhled na výkon vytíženého serveru nám dá až simulace s připojením více uživatelů. Právě na souběžné zpracování dotazů z mnoha připojení byl tento test koncipován.
4.1 Průběh testu Při testování byl zvolen počet bloků, které se budou vykonávat. Ten zůstával statický a měnil se pouze počet připojených vláken, která bloky prováděla. Tímto způsobem bylo nasimulováno reálnější zatížení databáze při obsluze více uživatelů současně. Počet bloků byl zvolen tak, aby délka testu byla v průměru kolem 10 minut. Což se ukázalo jako dostačující, soudě podle malých odchylek mezi opakováním testu se stejným nastavením. Na serveru Ammon toto kritérium nejlépe splňoval počet 100 000 bloků, které celkem obsahovaly průměrně kolem 1 086 000 jednotlivých dotazů. Počty vláken byly zvoleny na 1, 10, 25, 50, 100 a 200. Toto rozdělení, jemnější na začátku a hrubší na konci, se ukázalo pro testování jako nejvhodnější. Právě při menších počtech připojení se propustnost databáze mění nejvíce.
15
Při běhu testu jsou bloky dotazů vybírány v náhodném pořadí, ale tak, aby jejich počet odpovídal procentuálnímu rozvržení a byl při každém provedeném testu se stejnými parametry totožný. Díky tomu vznikají pouze malé odchylky v počtech jednotlivých dotazů, kladených na databázový server během jednoho testu. Průměrná odchylka se pohybuje okolo hranice 1 ‰ (zjištěno z 10 měření se 100 000 bloky). Celkový počet dotazů je na konci podělen celkovým časem, potřebným pro provedení testu. Tím získáme podíl určující propustnost databáze – průměrný počet dotazů provedených za sekundu. Toto číslo bude hlavním kritériem výkonnosti.
4.2 Datový model Datový model je postaven na zjednodušené simulaci provozu distribuční firmy. Tato firma dává svým zákazníkům k dispozici on-line katalog zboží, který si můžou prohlížet, listovat jím, řadit produkty podle různých kritérií a zobrazovat si detaily jednotlivých produktů. Firma si vede evidenci zákazníků. Ti se můžou přihlašovat do systému pod svým přihlašovacím jménem a heslem. Dále firma přijímá a vyřizuje objednávky svých zákazníků. Během zpracování objednávky můžou zákazníci kontrolovat její stav. Firma si může dělat přehledy o počtu objednávek a tržbě. Datový model obsahuje celkem 11 tabulek znázorněných v následujícím diagramu.
16
ERD
Zdroj: zpracováno autorem.
4.3 Popis a naplnění tabulek Aby byly zaručené shodné podmínky pro všechny testované databázové systémy, byla náhodná data pro naplnění tabulek vygenerována do souboru. Z tohoto souboru pak byla následně neimportována do každé z testovaných databází. Tak bylo možné docílit toho, že každá databáze bude obsahovat naprosto shodná data.
17
Počáteční naplnění dat bylo následující. 1. uzivatele Tabulka se základními uživatelskými údaji. Na začátku testu obsahuje 20 000 řádků, které zůstávají po celou dobu testu neměnné. id_uzivatele – primární klíč tabulky, typ integer. login – přihlašovací jméno uživatele. Skládá se ze 7 – 12 znaků anglické abecedy a čísel.
Průměrná délka záznamu je mezi 9 a 10 znaky. Typ varchar délky 20. heslo – hash hesla uživatele. Délka hashe je 40 znaků. Obsahuje znaky anglické abecedy
a čísla. Typ char délky 40. jmeno, prijmeni – obsahují 1 slovo generátoru lorem ipsum. Jeho průměrná délka je okolo
5 znaků. Typ varchar délky 20 znaků. email – email uživatele obsahuje neprázdnou předponu, @ a neprázdnou příponu. Mi-
nimální délka činí 7 znaků, maximální 17, průměrná délka je okolo 12 znaků. Typ varchar délky 20 znaků. telefon – devítimístné číslo. Typ integer.
2. adresy Obsahuje adresy uživatelů. Každý uživatel může mít jednu nebo dvě adresy. V průměru by tak tabulka měla obsahovat okolo 30 000 záznamů. Tyto záznamy zůstávají během testu statické. id_adresy – primární klíč tabulky. Typ integer. id_uzivatele – cizí klíč z tabulky uzivatele. ulice – 1 slovo z lorem ipsum generátoru zřetězené s číslem. Průměrná délka mezi 8 a 9
znaky. Typ varchar délky 20. obec – 1 slovo z lorem ipsum generátoru. Průměrná délka asi 5 znaků. Typ varchar
délky 20. psc – pětimístné číslo. Typ integer.
18
3. nakupni_kosik Do tabulky nakupni_kosik se vkládá zboží, které bude následně objednáno. Na začátku testu je tabulka prázdná. Během testu postupně naplňuje a promazává. id_uzivatele – cizí klíč z tabulky uzivatele. id_zbozi – cizí klíč z tabulky zbozi. ks – počet kusů daného zboží, Typ integer. V testu se hodnota pohybuje od 1 do 10.
4. objednavky Tabulka objednávky obsahuje hlavní údaje o objednávce. Na začátku testu je tabulka prázdná, v průběhu testu se plní. id_objednavky – primární klíč tabulky. Typ integer. id_uzivatele – cizí klíč z tabulky uzivatele. cas – datum a čas vytvoření objednávky. Typ timestamp. stav_objednavky – číslo symbolizující stav objednávky. 1 – nevyřízená objednávka, 2 –
vyřízená objednávka. id_adresy – cizí klíč z tabulky adresy. Typ integer.
5. polozky_objednavky Tabulka obsahuje objednané zboží, patřící do dané objednávky. Na začátku testu je prázdná, v průběhu testu se plní. id_zbozi – cizí klíč z tabulky zbozi. id_objednavky – cizí klíč z tabulky objednavky. ks – počet kusů. Typ integer. cena_ks – cena za 1. kus. Typ double. stav_polozky – číslo stavu dané položky. 1 – objednáno, 2 – připraveno. Typ integer.
6. zbozi Tabulka zbozi na začátku testu obsahuje 100 000 položek. V průběhu testu se mění počet prodaných kusů daného zboží, jinak data zůstávají statická. id_zbozi – primární klíč tabulky. Typ integer.
19
id_dph – cizí klíč z tabulky dph. jmeno – 1 až 4 slova z generátoru lorem ipsum. Minimální délka je 2 znaky, maximální
38 znaků, průměrná délka cca 17 znaků. Typ varchar délky 50. popis – 0 až 300 slov z generátoru lorem ipsum. Maximální délka 1772 znaků, průměrná
délka cca 885 znaků. Typ text / blob, dle podpory databáze. cena – 0 až 89999. Typ double. poznamka – 0 až 2 slova z generátoru lorem ipsum. Maximální délka 10 znaků, průměr-
ná mezi 2 a 3 znaky. Typ varchar délky 20. prodanych_ks – na začátku testu všude nastaveno na 0, v průběhu se počet navyšuje.
Typ Integer. 7. dph Tabulka obsahující sazby DPH. Na začátku testu jsou v tabulce 3 položky s DPH 0, 9 a 19. Ty se v průběhu testu nemění. id_dph – primární klíč tabulky. Typ integer. dph – sazba DPH.
8. sklady Tabulka eviduje jména skladů zboží. Na začátku testu obsahuje 10 položek, které zůstávají během testu statické. id_skladu – primární klíč tabulky. Typ integer. jmeno – 1 až 2 slova z lorem ipsum generátoru. Minimální délka 2 znaky, maximální 8
znaků, průměr 4 – 5 znaků. Typ varchar délky 20. 9. zbozi_sklad Vazební tabulka pro M:N relaci mezi tabulkou zbozi a sklady. Na začátku testu obsahuje 1 až 2 záznamy pro každou položku tabulky zbozi, v průměru tedy 150 000 záznamů. V průběhu testu se mění atribut ks. Ostatní data zůstávají neměnná. id_zbozi – cizí klíč z tabulky zbozi. id_skladu – cizí klíč z tabulky sklady. ks – počet kusů daného zboží na skladu. Náhodné hodnoty od 0 do 9. Typ integer.
20
10. kategorie Tabulka obsahuje kategorie zboží, podle kterých lze položky třídit. Na začátku testu je v tabulce 200 kategorií, které zůstávají po dobu testu neměnné. id_kategorie – primární klíč tabulky. Typ integer. jmeno – obsahuje 1 až 2 slova z generátoru lorem ipsum. Minimální délka je 2 znaky,
maximální 21 znaků a průměrná délka je cca 8 znaků. Typ varchar délky 30. 11. zbozi_kategorie Vazební tabulka pro relaci M:N spojující tabulky zbozi a kategorie. Na začátku testu tabulka obsahuje 1 až 2 záznamy pro každou položku z tabulky zboží. V průměru tabulka obsahuje 150 000 záznamů. Ty jsou po dobu testu statické. id_zbozi – cizí klíč z tabulky zbozi. id_kategorie – cizí klíč z tabulky kategorie.
4.4 Prováděné bloky dotazů Každý blok se skládá z jednoho, či více typů dotazů, které jsou při výběru bloku provedeny. Pro každý z těchto bloků je pevně dané procentuální zastoupení četnosti provádění. Zároveň je výběr bloků do jisté míry náhodný, aby byly jednotlivé dotazy rovnoměrně rozprostřeny. Mezi bloky není nastavena žádná umělá čekací doba, takže jsou dotazy na databázi zasílány nepřetržitě. Komplexní test je složen z následujících bloků dotazů. Procenta v závorce vyjadřují procentuální zastoupení bloku v celém testu.
Objednávka (15%)
Náhled detailu zboží (35%)
Přehled zboží dle kategorií (10%)
Řazený přehled zboží (10%)
Přihlašování uživatelů (20%)
Kontrola tržeb (1%)
Kontrola objednávek (1%)
Informace o uživateli (8%)
21
V následujícím textu budou popsány prováděné dotazy i s jejich příklady. Tyto příklady obsahují konkrétní hodnoty, ty se ovšem během testu náhodně mění a k opakování naprosto stejného dotazu dochází zřídka.
4.4.1 Objednávka Blok objednávka je složen ze sedmi částí, které je nutné provádět v daném pořadí. V případě, že bude tento blok vybrán, je provedena pouze jedna část. A to ta která má následovat dle daného pořadí. Na začátku celého bloku je náhodně vybráno id uživatele, pro které bude celá objednávka a dotazy k ní vztažené probíhat. I.
Vložení zboží do nákupního košíku
Aplikací je vybráno náhodně 1 – 10 položek obsažených v tabulce zbozi (pouze zvolení id_zbozi bez čtení z databáze). Ty se vloží do tabulky nakupni_kosik. V průměru je tak
vložení provedeno 5,5x. INSERT INTO nakupni_kosik (id_uzivatele, id_zbozi, ks) VALUES('1', '2', '3');
II.
Vytvoření objednávky
Tato část bloku testuje chování databáze při transakčním zpracování dotazů. Celý blok vytvoření objednávky a odepsání zboží ze skladů je uzavřen ve 2 transakcích. Obsahují průměrně asi 31 jednotlivých dotazů. Z čehož je asi 7 insertů, 4 selecty, 19 updatů a 1 delete. START TRANSACTION;
V tabulce objednávky je vytvořen záznam s id uživatele, aktuálním datem a časem, stavem objednávky a id adresy. INSERT INTO objednavky(id_uzivatele, cas, stav_objednavky, id_adresy) VALUES('1', '2010-10-10 22:50:11', '1','1');
Z tabulky objednávky je načteno id vytvořené objednávky. SELECT max(id_objednavky) AS id FROM objednavky WHERE id_uzivatele = '1';1
Zjišťování posledního vloženého id tímto způsobem není zcela korektní a existují k němu vhodnější nástroje. Nicméně i s nekorektními dotazy se musí DB systémy v běžném provozu vyrovnávat. Navíc tímto způsobem lze zjistit id u všech systémů identicky. 1
22
Přečte se obsah nákupního košíku. SELECT nakupni_kosik.id_zbozi, ks, cena FROM nakupni_kosik JOIN zbozi ON nakupni_kosik.id_zbozi = zbozi.id_zbozi WHERE id_uzivatele = '1';
Obsah košíku se vloží do tabulky polozky_objednavky. Tento dotaz se provádí 1 až 10 krát, v průměru je tak proveden 5,5x během každého bloku. INSERT INTO polozky_objednavky (id_zbozi, id_objednavky, ks, cena_ks, stav_polozky VALUES ('1', '1', '1', '1', '1');
V tabulce zbozi se upraví údaj do počtu prodaných kusů daného zboží. Tento dotaz je prováděn stejně jako předchozí v průměru 5,5x v každém bloku. UPDATE zbozi SET prodanych_ks = (prodanych_ks + 1) WHERE id_zbozi = '1';
Na konci transakce se smaže obsah nákupního košíku. DELETE FROM nakupni_kosik WHERE id_uzivatele = '1'; COMMIT;
Nyní se objednané zboží vyhledá na skladech a odepíše. Do příznaku stav_polozky se nastaví číslo 2, signalizující, že zboží bylo kompletně odepsáno ze skladu a je připraveno k expedici. Pokud nebyl k dispozici dostatečný počet kusů, zmenšíme skladové zásoby na 0 a čekáme na doplnění zboží. Vybereme objednané zboží. SELECT id_zbozi, ks FROM polozky_objednavky WHERE id_objednavky = '1' AND stav_polozky = '1';
Spustíme transakci a vybrané zboží vyhledáme na skladech. Jedno zboží může být vedeno na jednom nebo více skladech. Průměrný počet skladů pro jedno zboží je 1,5. Tedy průměrný počet provedení dotazu je 1,5 x 5,5 (průměrný počet objednaných položek) = cca 8 dotazů. START TRANSACTION; SELECT id_skladu, ks FROM zbozi_sklad WHERE id_zbozi = '1' AND ks > 0;
Poté provedeme aktualizaci skladových zásob. Příkaz je proveden průměrně 8x v každém bloku. UPDATE zbozi_sklad SET ks = '1' WHERE id_zbozi = '1' AND id_skladu = '1';
Nakonec provedeme aktualizace stavu položky a transakci odsouhlasíme commitem. Aktualizace je provedena, pouze pokud je daná položka nalezena na skladu.
23
UPDATE polozky_objednavky SET stav_polozky = '2' WHERE id_objednavky = '1' AND id_zbozi = '1'; COMMIT;
III.
Kontrola stavu objednávky
Kontrola objednávky představuje načtení veškerých informací o objednávce. Dotaz obsahuje čtyřnásobný JOIN tabulek. U každé objednávky probíhá dvakrát. Poprvé po provedení části II – vytvoření objednávky, a podruhé po provedení části IV a V – doplnění a odepsání zboží ze skladu. SELECT objednavky.id_objednavky, cas, stav_objednavky, polozky_objednavky.id_zbozi, ks, cena_ks, stav_polozky, ulice, obec, psc, uzivatele.jmeno, prameni FROM objednavky JOIN polozky_objednavky ON objednavky.id_objednavky = polozky_objednavky.id_objednavky JOIN uzivatele ON objednavky.id_uzivatele = uzivatele.id_uzivatele JOIN adresy ON objednavky.id_adresy = adresy.id_adresy JOIN zbozi ON polozky_objednavky.id_zbozi = zbozi.id_zbozi WHERE objednavky.id_uzivatele = '1' AND stav_objednavky = '1';
IV.
Doplnění zboží na sklad
Navýšíme počet kusů na skladě těch položek objednávky, které zatím nebyly označeny stavem 2 – připraveno k expedici. V první části tyto položky vybereme a v druhé části provedeme vyhledání skladů, na kterých se daná položka nachází. Potom navýšíme skladové zásoby o počet kusů, který byl objednán. Celá druhá část je uzavřena v transakci. SELECT id_zbozi, ks FROM polozky_objednavky WHERE id_objednavky = '1' AND stav_polozky = '1'; START TRANSACTION; SELECT id_skladu, ks FROM zbozi_sklad WHERE id_zbozi = '1' UPDATE zbozi_sklad SET ks = (4 + 8) WHERE id_skladu = '1' AND id_zbozi = '1'; COMMIT;
V.
Odepsání zboží ze skladu
Tato část představuje rezervaci naskladněného zboží pro danou objednávku. Vybereme nevyřízené položky objednávky a ty následně vyhledáváme na skladech, kde snížíme počet jejich kusů. Potom označíme položku objednávky jako připravenou – nastavíme hodnotu stav_polozky = 2. Část po prvním selectu je uzavřena v transakci.
24
SELECT id_zbozi, ks FROM polozky_objednavky WHERE id_objednavky = '1' AND stav_polozky = '1'; START TRANSACTION; SELECT id_skladu, ks FROM zbozi_sklad WHERE id_zbozi = '1' AND ks > 0; UPDATE zbozi_sklad SET ks = '1' WHERE id_zbozi = '1' AND id_skladu = '1'; UPDATE polozky_objednavky SET stav_polozky = '2' WHERE id_objednavky = '1' AND id_zbozi = '1' COMMIT;
VI.
Kontrola stavu objednávky
Viz krok číslo III. Probíhá ekvivalentně. VII.
Vyhotovení faktury
Provedeme výběr všech údajů o objednávce potřebných pro vyhotovení faktury a následně objednávku označíme za vyřízenou, nastavením atributu stav_objednavky na hodnotu 2. SELECT objednavky.id_objednavky, cas, polozky_objednavky.id_zbozi, ks, cena_ks, ulice, obec, psc, uzivatele.jmeno, prijmeni FROM objednavky JOIN polozky_objednavky ON objednavky.id_objednavky = polozky_objednavky.id_objednavky JOIN uzivatele ON objednavky.id_uzivatele = uzivatele.id_uzivatele JOIN adresy ON objednavky.id_adresy = adresy.id_adresy JOIN zbozi ON polozky_objednavky.id_zbozi = zbozi.id_zbozi WHERE objednavky.id_uzivatele = '1' AND stav_objednavky = '1'; UPDATE objednavky SET stav_objednavky = '2' WHERE id_objednavky = '1';
4.4.2 Náhled detailu zboží Z tabulky zbozi je vybrán 1 náhodný řádek podle atributu id_zbozi. SELECT zbozi.id_zbozi, dph, jmeno, popis, cena, poznamka, prodanych_ks FROM zbozi JOIN dph ON zbozi.id_dph = dph.id_dph WHERE id_zbozi = '1'
4.4.3 Přehled zboží dle kategorií Představuje výběr přes pomocnou tabulku zbozi_karegorie. Každý dotaz vrací v průměru 750 řádků tabulky zbozi. SELECT zbozi.id_zbozi, dph, zbozi.jmeno, popis, cena, poznamka, prodanych_ks, kategorie.jmeno FROM zbozi JOIN dph ON zbozi.id_dph = dph.id_dph
25
JOIN zbozi_kategorie ON zbozi_kategorie.id_zbozi = zbozi.id_zbozi JOIN kategorie ON zbozi_kategorie.id_kategorie = kategorie.id_kategorie WHERE zbozi_kategorie.id_kategorie = '1'
4.4.4 Řazený přehled zboží Jedná se o stejný dotaz na výběr zboží jako v přehledu zboží dle kategorií, tentokrát ale řazený podle jednoho ze 4 atributů. (id_zbozi, jmeno, cena, prodaných_ks) SELECT zbozi.id_zbozi, dph, zbozi.jmeno, popis, cena, poznamka, prodanych_ks, kategorie.jmeno FROM zbozi JOIN dph ON zbozi.id_dph = dph.id_dph JOIN zbozi_kategorie ON zbozi_kategorie.id_zbozi = zbozi.id_zbozi JOIN kategorie ON zbozi_kategorie.id_kategorie = kategorie.id_kategorie WHERE zbozi_kategorie.id_kategorie = '1' ORDER BY prodaných_ks
4.4.5 Přihlašování uživatelů Obsahuje 2 dotazy. Prvním vybereme náhodný login z tabulky uzivatele a druhým vyhledáme id_uzivatele a jeho heslo přes atribut login. Do časů přihlašování uživatelů počítáme pouze druhý dotaz, jelikož běžně zadává login uživatel a nemusí se vyhledávat. SELECT login FROM uzivatele WHERE id_uzivatele ='1'; SELECT id_uzivatele, heslo FROM uzivatele WHERE login ='Maruska';
4.4.6 Kontrola tržeb Agregační funkcí sum() sečteme všechny ceny vyřízených položek objednávky. SELECT sum(cena_ks*ks) FROM objednavky JOIN polozky_objednavky ON polozky_objednavky.id_objednavky = objednavky.id_objednavky WHERE stav_objednavky = 2 AND stav_polozky = 2
4.4.7 Kontrola objednávek Agregační funkcí count() zjistíme počet všech vyřízených objednávek. Funkcí sum() spočítáme průměrnou cenu jedné objednávky.
26
SELECT count(objednavky.id_objednavky) AS pocet_obj, sum(cena_ks*ks) / count(objednavky.id_objednavky) AS avg_cena_obj FROM polozky_objednavky JOIN objednavky ON objednavky.id_objednavky = polozky_objednavky.id_objednavky WHERE stav_objednavky ='2' GROUP BY polozky_objednavky.id_objednavky
4.4.8 Informace o uživateli Vybereme informace náhodného uživatele z tabulky uzivatele a adresy. SELECT uzivatele.id_uzivatele, login, heslo, jmeno, prijmeni, email, telefon, id_adresy, ulice, obec, psc FROM uzivatele JOIN adresy ON uzivatele.id_uzivatele = adresy.id_uzivatele WHERE uzivatele.id_uzivatele = '1'
4.5 Indexy a integritní omezení Pro všechny databáze byly vytvořeny dodatečné indexy na tabulkách zbozi, objednavky a polozky_objednavky. V tabulce zbozi šlo o indexy, které mohly být využity při řazení. Konkrétně šlo o atributy jmeno, cena a prodaných_ks. Na id_zbozi jako primárním klíči, který se také využíval pro řazení, je index vytvářen všemi testovanými databázemi automaticky. Tabulka objednavky byla vybavena indexem na atributu stav_objednavky a cas. Tabulka polozky_objednavky byla doplněna o index stav_polozky. Všechny odkazy na tabulky uložené v nějakém atributu byly definovány jako cizí klíče. Zde jsou už mezi použitými databázemi rozdíly. Některé si vytváří indexy na cizí klíče automaticky, jiné nechávají tuto volbu na správci databáze. Z výše uvedených dotazů je patrné, že indexy na cizích klíčích budou jistě výhodou. K zajištění shodných podmínek pro všechny databáze byly indexy na cizích klíčích přidány u PostgreSQL, MS SQL a Oraclu. Právě tyto 3 zmíněné databázové systémy si indexy na cizích klíčích sami nevytvářejí. Nicméně pro zajímavost byl proveden test i bez přidání těchto indexů. Jeho výsledky budou prezentovány v kapitole 4.7.
27
4.6 Postup při testování Testy na serveru Ammon probíhaly vzdáleně prostřednictvím 1GB sítě. Aplikace provádějící testy běžela na jiném serveru, odkud posílala dotazy na Ammon. Tak byl celý výkon serveru vyhrazen databázovým systémům. Síť byla pro test dostatečně propustná a server v odesílání dat nijak neomezovala. Ve špičkách docházelo k maximálnímu zatížení sítě na 75 %. Testy na notebooku AMILO Pro probíhaly kompletně na localhostu samotného stroje. Aplikace běžela spolu s databázovým systémem. Pro zajištění co největší hadrwarové dostupnosti byl na počítači spuštěn vždy jen jeden databázový systém. Samotný komplexní test byl prováděn s dodržením následujícího postupu. Do databáze byla naimportována data z datového souboru. Následně byl spuštěn test s 10 vlákny, který běžel po dobu cca 5 minut. Tento soubor dotazů sloužil jen pro zaběhnutí databáze. Z prvních testů totiž vyplynulo, že rozdíl mezi prvním a druhým provedením naprosto stejného testu je často značně velký. To je způsobeno pravděpodobně tím, že při prvním provádění si databázový server nahraje do RAM části databáze a následně je už nečte z pomalého disku, ale z rychlé paměti. Protože cílem testu není zjištění rychlosti načítání databáze do RAM, je před začátkem testu proveden zmíněný soubor dotazů. Po tomto krátkém zaběhnutí je vyprázdněna tabulka objednavky, polozky_objednavky a nakupni_kosik a je spuštěn ostrý test.
V prvním měření je k databázi připojeno pouze jedno vlákno. Tímto zjistíme jak rychle je databáze schopna provádět sériově posílané dotazy od jednoho klienta. Po dokončení testu jsou opět vyprázdněny zmíněné 3 tabulky a následuje test s 10 vlákny. Vždy provádíme stejný počet bloků, takže je možné porovnávat i celkové časy testů. Stejným způsobem testujeme 10, 25, 50, 100 a 200 vláken.
4.7 Výsledky testů Jako první bude následovat test databází Oracle XE, PostgreSQL a MS SQL Server 2008 Express, zaměřený na vliv indexů na cizích klíčích. Poté budou uvedeny výsledky hlavního testu všech databázových systémů. Tento test u výše uvedených tří databází probíhal už s dodatečně přidanými indexy na cizích klíčích.
28
4.7.1 Vliv indexů na cizích klíčích Jak už bylo dříve zmíněno, PostgreSQL, MS SQL a Oracle si sami indexy na cizích klíčích nevytváří. Proto se nabízela možnost zjistit, jaký vliv budou chybějící index mít na výkon databáze. Test byl proveden na notebooku AMILO Pro. Bylo v něm zahrnuto jedno spuštění komplexního testu s 10 vlákny a 10 000 bloky. Na níže uvedeném grafu můžeme vidět rozdíly zmíněných 3 databázových systémů s indexy a bez indexů na cizích klíčích. Hodnota uvedená na ose y znázorňuje propustnost databáze – dotazy provedené za sekundu. Vliv indexů na cizích klíčích na propustnost
Vliv indexů na ck – datazy/s 700 600 500 400
PostgreSQL
300
MS SQL Express
200
Oracle
100 0 bez indexů na ck
s indexy na ck
Zdroj: zpracováno autorem. Výsledky testu dopadly dle očekávání. Všechny databáze byly s indexy výrazně rychlejší než bez nich. Nejmarkantnější rozdíl byl vidět u PostgreSQL, u kterého byl při použití indexů zaznamenán bezmála čtyřnásobný nárůst propustnosti. Naopak nejmenší rozdíl byl naměřen na databázi Oracle, u které se zvýšila propustnost pouze o 13 %. Nicméně z grafu je patrné, že propustnost Oraclu bez indexů byla oproti dalším dvěma databázím velmi vysoká. Z čehož by se dalo usuzovat, že si Oracle vytvořil svůj vlastní dočasný index, který i po odečtení režií na vytvoření značně zvýšil jeho výkon.
29
4.7.2 Propustnost v závislosti na počtu vláken Obecný trend v dosažených časech, respektive v propustnosti databáze, je takový, že s přibývajícím počtem připojení propustnost stoupá. To až k dosažení maxima, ve kterém propustnost opět začne pozvolna klesat. Tento test byl proveden na notebooku AMILO pro i na serveru Ammon. Jako první budou uvedeny výsledky naměřené na notebooku. 4.7.2.1 Notebook Propustnost databáze v závislosti na počtu vláken – notebook.
Propustnost 1 – 200 vláken | notebook 1200
1000
dotazy / s
800
600
400
200
0 0
50
100
150
200
vlákna PostgreSQL
MS SQL 2008 Express
Oracle
MySQL
Firebird
Firebird test bez řazení
Zdroj: zpracováno autorem.
30
Graf znázorňuje propustnost databáze. Na ose x je uveden počet vláken, na ose y počet dotazů za sekundu, které databáze v testu průměrně vykonala. Celý test se nepodařilo dokončit databázi Firebird, která již při 25 vláknech odmítala některá připojení. Tento problém se nepodařilo odstranit ani při pokusech o změnu nastavení databáze. Více k tomuto tématu bude napsáno v kapitole 5. Pro databázi Firebird jsou v grafu také uvedeny 2 řady. První z nich, pojmenovaná Firebird, představuje regulérní provedení testu. Jelikož měl ale Firebird velké problémy s řazením výsledků, které trvalo i o 5 řádů déle než jiné dotazy, rozhodl jsem se klauzuli ORDER BY z testu vynechat a provést tento upravený test. Výsledky tohoto upraveného testu jsou v grafu uvedeny pod názvem Firebird test bez řazení. Více o tomto problému bude taktéž zmíněno v kapitole 5. Druhým nedokončeným testem je 200 vláken na databázi PostgreSQL. Při tomto počtu připojení se databázový systém na notebooku vypnul. PostgreSQL, stejně jako Firebird, spouští pro každé připojení nový proces. Při pokusu o spuštění takto velkého počtu procesů nebylo možné alokovat dostatek operační paměti pro každé připojení a PostreSQL se ukončil. K ukončení docházelo v okamžiku 100% naplnění operační paměti. U ostatních databází proběhl test bez větších problémů. Z dosažených výsledků výrazně vyčnívá databáze MySQL. S jedním vláknem sice podprůměrných 257 dotazů za sekundu. Ovšem od 10 vláken ostatní databáze převyšuje téměř o 100 %. Ve svém maximu s 25 vlákny se MySQL dostala až na 1116 dotazů za sekundu. Druhou v pořadí byla databáze Oracle. Ta i s jedním vláknem dokázala vykonat téměř 400 dotazů za sekundu a v tomto ohledu ji překonal pouze PostgreSQL. Od 10 vláken a víc však vítězí. Ve svém maximu, 50 připojení, dokázal Oracle provést průměrně 631 dotazů za sekundu. Na pomyslném třetím místě v prostupnosti se umístila databáze PostgreSQL. Ta dosáhla vůbec nejlepšího výsledku s jedním připojeným vláknem. Ten činil 437 dotazů za sekundu. Svého maxima dosáhl PostgreSQL při počtu 10 připojení, a to 543 dotazů za sekundu. Od 25 do 50 vláken výsledek mírně klesal. Na 100 vláknech byl již horší výrazně a 200 vláken se nepodařilo provést.
31
Čtvrté místo v testu prostupnosti obsadila databáze od Microsoftu, SQL Server 2008 Express. Při jednom připojeném vláknu dosáhla výsledku 293 dotazů za sekundu. S více vlákny se propustnost zvýšila. Nicméně, ve svém maximu, 25 vláken, dosáhla podprůměrných 481 dotazů za sekundu. Test databáze Firebird nedopadl příliš dobře. I přes snahu o přenastavení základní konfigurace se nepodařilo uskutečnit připojení 25 klientů a výše. Ani s méně připojenými vlákny ale nedosáhl Firebird dobrých výsledků. V regulérním testu byla jeho prostupnost 66 dotazů za sekundu s jedním vláknem, respektive 103 s 10 vlákny. V testu bez klauzule ORDER BY se jeho prostupnost výrazně zvýšila. Ovšem i tak činila v maximu s 10 vlákny pouhých 349 dotazů za sekundu. Na závěr tohoto testu ještě graf, zobrazující maximální dosažené propustnosti u jednotlivých databází. Maxima dosažené propustnosti na notebooku.
Maxima | notebook 1200 1000 800 600 400 200 0
dotazy / s
MySQL
Oracle
PostgreSQL
MS SQL 2008 Express
Firebird test bez řazení
Firebird
1116,49
631,62
543,46
481,21
349,19
103,19
Zdroj: zpracováno autorem. 4.7.2.2 Server Ammon Stejný test jako na notebooku byl proveden i na výkonném serveru Ammon. S výjimkou přidání testu komerční databáze Microsoft SQL Server 2008. Jak můžeme vidět na
32
následujícím grafu, výsledky tohoto testu se značně liší od výsledků, dosažených na omezeném hardwarovém vybavení. Propustnost databáze v závislosti na počtu vláken – Ammon.
Propustnost 1 – 200 vláken | server 4000 3500 3000
dotazy / s
2500 2000 1500 1000 500 0 0
50
100
150
200
Vlákna PostgreSQL
MS SQL 2008
Oracle XE
MySQL
Firebird
Firebird bez řazení
MS SQL 2008 Express
Zdroj: zpracováno autorem. Velká rozdílnost výsledků může být způsobena více faktory. Jedním z nich je výchozí nastavení od výrobce databázového systému. To může být uzpůsobeno pro nějakou typickou konfiguraci hardware a v případě, kdy je k dispozici silnější hardware, ho nedokáže databáze využít. Jelikož úkolem tohoto testování není nalézt nejvhodnější konfiguraci databázového systému, ale zjistit výkon v běžné konfiguraci dané výrobcem, nebyla tato možnost v testu nijak zohledněna.
33
Druhým vysvětlením rozdílných výsledků, a to u databáze Oracle XE a Microsoft SQL Server 2008 Express, je jejich úmyslné omezení. Jsou to verze zdarma, jinak komerčního software. A jak již bylo zmíněno v kapitole 2, u obou těchto databází jsou omezeny využitelné hardwarové prostředky. Microsoft SQL Server je na tom z pohledu omezení o mnoho lepe, jelikož dokáže využít vícejádrový procesor a v konečném důsledku i více paměti RAM, což se také do výsledků promítlo. V testu se ukázaly jako nejlepší 2 databáze. Komerční MS SQL Server 2008 a PostgreSQL. Obě tyto databáze byly velmi vyrovnané. Průběh jejich grafu je téměř stejný, pouze svá maxima mají při jiných počtech vláken. Těsným vítězem v maximální prostupnosti se stal MS SQL Server s 3786 dotazy za sekundu při 50 vláknech. PostgreSQL překonal hranici 3688 dotazů za sekundu při 25 vláknech. Ovšem PostgreSQL měl rychlejší start než Microsoft. Při 25, a velmi výrazně při 10 vláknech, překonal jeho propustnost. Po zmíněných vítězích následuje velký propad. Ten se zastavuje až u výsledků Microsoft SQL Server 2008 Express. I přes svoje hardwarová omezení dosáhl poměrně dobrých výsledků a překonal MySQL, Firebird i Oracle XE. Ve svém maximu dostáhl Express Edition 2056 dotazů za sekundu. Je překvapivé, že vítěz testu provedeného na notebooku, MySQL, dosahuje na serveru propustnosti maximálně 1701 dotazů za sekundu a zůstává daleko za MS SQL a PostgreSQL. Nápovědou by mohla být analýza časů provádění jednotlivých bloků. Z té vyplývá, že na serveru byly rychleji provedené bloky s transakcemi, zato bloky používající agregační funkce se zpomalily téměř desetinásobně. To je způsobeno pravděpodobně tím, že na notebooku bylo v testu prováděno 10 000 bloků, zatímco na serveru 100 000 bloků. Tak docházelo k většímu zaplnění tabulek, a právě u MySQL se s větším zaplnění rapidně začala zhoršovat doba odezvy dotazů, které používaly agregační funkce přes celé tabulky. To také potvrzuje sledování datového toku ze serveru v průběhu MySQL testu. Viz obrázek níže.
34
Vytížení sítě během testu MySQL
Zdroj: zpracováno autorem. Graf znázorňuje procentuální vytížení 1GB sítě při probíhajícím testu. Na začátku je síť vytěžována téměř na 50 %, zatímco ke konci testu byla tato hodnota poloviční. U osta ostatních databází databází bylo vytížení sítě napříč celým testem neklesající a korespondovalo s dodo saženým výkonem. Pro potvrzení této myšlenky jsem provedl jedno spuštění testu bez předchozího v vyyprázdnění tabulek objednavky, polozky_objednavky a nakupni_kosik. Oproti testu s vymazá vymazáním tabulek v něm propustnost propustnost databáze výrazně klesla. To potvrdilo domněnku, že má vytížená databáze MySQL problém při použití agregačních funkcí na větších ttaabulkách. U ostatních testovaných databází k tomuto jevu nedocházelo. U Oracle XE se na v výkonném ýkonném serveru projevila omezení této volné verze k maximá maximálnímu využití hardware. Což se ovšem dalo s jistotou předvídat. Oracle XE měl mo možžnost využít pouze potenciál jednoho jádra procesoru z osmi dostupných, navíc musel vystačit s pouhým 1 GB operační p paměti aměti jak pro data, tak pro svůj provoz provoz. Jeho proostupnost se od 10 do 200 připojení držela mezi hranicemi 831 a 885 dotazy za sekundu. Regulérní test Firebidru nedopadl příliš dobře ani na výkonném serveru. S řazením většího množství dat má Firebird velké problémy. Je pravdou, že bylo požado požadováno váno řazení v průměru 750 řádků, ke kterému nemuselo být možné použít index. Ten sice byl vytvořen, ale díky výběru dat přes vazební tabulku mohlo být výhodnější ho vůbec nevyužít. nevyužít Problém se zdál být v malém množství operační p paměti, aměti, kterou Firebird využíval (cca 5 MB na každé vlákno) vlákno). Ovšem i po razantním zvýšení počtu a velikosti stránek dat dataa-
35
báze v operační paměti se výkon nijak dramaticky nezvýšil. Došlo sice k patrnému nárůstu, nicméně i s ním by zůstal Firebird na posledním místě tohoto testu. O mnoho lepších výsledků dosáhl Firebird s testem bez klauzule ORDER BY. V tomto přídavném tesu dosáhl maximálního výsledku 2087 dotazů za sekundu a překonal tak i databázi MySQL. Dobrý výkon mu ale nevydržel příliš dlouho a na 50 vláknech už byla propustnost pouze 832 dotazů za sekundu. Tento test byl ale přidán jen pro zajímavost, aby bylo patrné na kolik Firebird zatěžuje řazení. Porovnání je zde možné jen mezi oběma testy Firebirdu, nikoli mezi ostatními databázemi, jelikož ty testem bez řazení výsledků neprošly. Na závěr opět graf srovnání maximální naměřené propustnosti u všech databází. Maxima dosažené propustnosti na serveru Ammon
Maxima propustnosti | server 4000 3500 3000 2500 2000 1500 1000 500 0
dotazy / s
MS SQL 2008
PostgreS QL
Firebird bez řazení
MS SQL 2008 Express
MySQL
Oracle XE
Firebird
3785,76
3688,68
2087,8
2056,89
1701,28
885,94
197,38
Zdroj: zpracováno autorem.
36
5 Vzniklé problémy Následující část může být v některých bodech subjektivní a ovlivněná mou znalostí nebo neznalostí systému. Ale i přesto považuji za vhodné zmínit problémy, které se při průběhu testování vyskytly.
5.1 MySQL Při prvním testování se objevil problém s pomalým zpracováním řádků s dlouhým textem. Tento problém byl zapříčiněn tím, že MySQL server byl nainstalován s nastavením developer server, který má omezený přístup k operační paměti. Po překonfigurování na možnost dedicate server, který nesvazuje omezení, byl problém vyřešen. Výsledky uvedených testů byly naměřeny s nastavením dedicate server.
5.2 PostgreSQL Jediný problém, který se za celou dobu testování objevil, bylo spadnutí Postgresu na notebooku s připojením 200 vláken. Ten se nepodařilo vyřešit. Jak již bylo zmíněno, nejspíš souvisí s velkým počtem procesů spuštěných na stroji s malou operační pamětí. Nicméně provoz databázového systému na takto slabém stroji pro připojení 200 uživatelů současně asi není v reálné situaci příliš pravděpodobný.
5.3 Firebird U databáze Firebird se objevily dva problémy. Velký problém bylo nastavit systém tak, aby přijímal požadovaný počet připojení. Byly ozkoušeny verze Superserver i Classic server. Na ani jedné z nich tento problém nebyl vyřešen a i přes značné úsilí se na notebooku nepodařilo připojit 25 klientů a více. Na serveru se tento problém nevyskytnul a bez problému proběhlo připojení 200 klientů. Jelikož nastavení na serveru i na notebooku bylo totožné, problém asi nebude přímo v samotném nastavení systému. Druhý řešený problém byl v JDBC ovladači. Ten odmítal procházet načtená data, pokud se během procházení provedl jakýkoliv SQL dotaz. To bylo způsobeno nastavením AUTOCOMMIT na hodnotu ON, při kterém je zmíněné chování od ovladače vyžadováno.
Protože bylo potřeba provádět bloky transakcí bez commitu za každým z příkazů, byl autocommit vypnut a odsouhlasení transakcí bylo prováděno testovacím programem.
37
5.4 Microsoft SQL Server Největší problém u SQL Serveru byl s prováděním transakcí. První spuštěný test se během asi 20 sekund zaseknul a databáze přestala přijímat další příkazy. Po následné analýze bylo zjištěno, že dojde k uváznutí všech transakcí objednávky. Tento problém byl způsoben tím, že si Microsoft nedělal kopie řádků, které při transakci zamykal. Potom dotaz na zjištění posledního vloženého id pomocí funkce MAX() způsobil uváznutí transakce. Takovéto zjišťování posledního id samozřejmě není zcela korektní a mělo by se provádět jinými nástroji. Tento způsob byl zvolen z toho důvodu, že takto lze získat poslední id stejně ve všech testovaných databázích. To ovšem není omluvou pro zkolabování celé databáze, protože stejný problém by způsobil jakýkoliv příkaz procházející celou tabulku. Tento problém byl vyřešen nastavením parametru READ_COMMITTED_SNAPSHOT na hodnotu ON a TRANSACTION ISOLATION LEVEL na hodnotu READ COMMITTED.
5.5 Oracle XE U databáze Oracle bylo o něco složitější nastavení maximálního počtu připojení. Nelze to totiž provést nastavením jednoho parametru. Bylo potřeba zvýšit parametry SESSIONS, PROCESSES a TRANSACTIONS. Poté na notebooku fungovalo vše v pořádku.
Na serveru Ammmon se objevil ještě problém se vzdáleným připojením. Pro více souběžných vláken se začala připojení sama ukončovat s chybou socket read error. Tato chyba však s největší pravděpodobností nebyla způsobena přímo nastavením Oracle databáze, jako spíš nastavením samotného Microsoft Windows na serveru, nebo vzdáleného počítače. Z některého PC připojení fungovalo bez problému a z jiného nikoliv.
38
6 Závěr Za úspěch této práce bych považoval především to, že se kromě seznámení čtenáře se základní výkonnostní stránkou některých používaných databázových systémů, podařilo také poukázat několik zajímavých skutečností, které nemusí být na první pohled patrné. Jednou z těchto skutečností je fakt, že i zdarma dostupné databáze mohou dosahovat vyrovnaných výsledků v porovnání s některými komerčními produkty. Databázový systém PostgreSQL ukázal v testu na serveru téměř shodné výsledky jako komerční Microsoft SQL Server 2008. Druhým zajímavým zjištěním je, že použitý hardware může ovlivnit dosažené výsledky velmi rozdílným způsobem u každého z testovaných systémů. Názor, že výkon použitého stroje příliš neovlivňuje rozdíly výkonu mezi srovnávanými systémy, se ukázal jako mylný. A to obzvlášť tehdy, neprovádíme-li optimalizace v nastavení databázového systému. Právě ponechání základní konfigurace databázových serverů by se dalo považovat za jistou slabinu tohoto testu a mohlo by být kritizováno. Na druhou stranu zasahování do nastavení databází by mohlo způsobit značnou neobjektivitu. Navíc snahou vývojářů by mělo být vytvoření co možná nejuniverzálnějšího systému, který bude potřebovat jen minimum zásahů a omezit tím potřebu zdlouhavého studování manuálů a následného nastavování velkého množství parametrů. I přesto si myslím, že by bylo zajímavé provést také testy s optimalizací nastavení databází. To by ovšem značně přesahovalo možnosti a rozsah této práce. Na závěr bych chtěl podotknout, že vždy bylo snahou zaručit všem systémům stejné podmínky a žádný z nich nijak nezvýhodňovat, či naopak neznevýhodňovat. Nicméně 100% objektivnost jde při pokusech o napodobení reality dosáhnout jen stěží. I přesto si myslím, že byla tato práce přínosná a umožnila čtenáři udělat si základní přehled o výkonnostní stránce dostupných a často používaných databázových systémech.
39
7 Bibliografie 1. Oracle Corporation. About MySQL. Mysql.com. [Online] 11. Duben 2010. [Citace: 12. Duben 2010.] http://www.mysql.com/about/. 2. Oracle Corporation. MySQL Database Software Products. MySQL. [Online] 12. Duben 2010. [Citace: 12. Duben 2010.] http://www.mysql.com/products/. 3. Wikipedia contributors . MySQL AB. Wikipedia, The Free Encyclopedia. [Online] 29. Březen 2010. [Citace: 12. Duben 2010.] http://en.wikipedia.org/w/index.php?title=MySQL_AB&oldid=352763817. 4. Oracle Corporation. Chapter 13. Storage Engines. MySQL.com. [Online] 24. Duben 2010. [Citace: 24. Duben 2010.] http://dev.mysql.com/doc/refman/5.1/en/storageengines.html. 5. Oracle Corporation. The MyISAM Storage Engine. mysql. [Online] 12. Duben 2010. [Citace: 12. Duben 2010.] http://dev.mysql.com/doc/refman/5.1/en/myisam-storageengine.html. 6. Oracle Corporation. The InnoDB Storage Engine. mysql. [Online] 12. Duben 2010. [Citace: 12. Duben 2010.] http://dev.mysql.com/doc/refman/5.1/en/innodb.html. 7. PostgreSQL Global Development Group. History. PostgreSQL. [Online] 1. Leden 2010. [Citace: 17. Duben 2010.] http://www.postgresql.org/about/history. 8. PostgreSQL Global Development Group. About. PostgreSQL. [Online] 25. Únor 2010. [Citace: 17. Duben 2010.] http://www.postgresql.org/about/. 9. PostgreSQL Global Development Group. 5.8. Inheritance. PostgreSQL. [Online] 15. Březen 2010. [Citace: 17. Duben 2010.] http://www.postgresql.org/docs/8.3/static/ddlinherit.html#DDL-INHERIT-CAVEATS. 10. PostgreSQL Globel Development Group. PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Partitioning. PostgreSQL. [Online] 15. Březen 2010. [Citace: 2. Květen 2010.] http://www.postgresql.org/docs/current/static/ddl-partitioning.html. 11. Firebird Foundation Incorporated. Chapter 2: About Firebird . firebirdSQL. [Online] 31. Březen 2010. [Citace: 20. Duben 2010.] http://www.firebirdsql.org/manual/ufbabout.html.
40
12. Firebird Foundation Incorporated. Initial Developer's PUBLIC LICENSE Version 1.0. Firebird. [Online] 21. Duben 2010. [Citace: 2010. Duben 2010.] http://www.firebirdsql.org/index.php?op=doc&id=idpl. 13. Wikipedia contributors. Microsoft SQL Server. Wikipedia, The Free Encyklopedia. [Online] 21. Duben 2010. [Citace: 10. Květen 2010.] http://en.wikipedia.org/wiki/Microsoft_SQL_Server. 14. Microsoft. SQL Server 2008: Express. Microsoft. [Online] 12. Duben 2010. [Citace: 4. Květen 2010.] http://www.microsoft.com/sqlserver/2008/en/us/express.aspx. 15. Oracle Corporation. About Oracle. Oracle. [Online] 10. Duben 2010. [Citace: 10. Duben 2010.] http://www.oracle.com/us/corporate/index.html. 16. Oracle Corporation. Oracle Celebrates 30 Years of Innovation. Oracle. [Online] 16. Květen 2010. [Citace: 11. Duben 2010.] http://www.oracle.com/technology/oramag/oracle/07-jul/o4730.html. 17. Oracle Corporation. Oracle Database Express Edition Installation Guide. Oracle. [Online] Leden 2006. [Citace: 11. Duben 2010.] http://www.oracle.com/technology/software/products/database/xe/files/install.102/b25 144/toc.htm. 18. Transaction Processing Performance Council. TPC. TPC. [Online] [Citace: 21. Únor 2010.] http://www.tpc.org/. 19. Ault, Mike, a další. Database Benchmarking: Practical Methods for Oracle & SQL server. Kittrell : Rampant Techpress, 2007. 978-0977671533.
41
