Hlavní navigace

MySQL vs PostgreSQL vs Firebird II

Pavel Stěhule 3. 11. 2005

Článek na Root.cz mě vyprovokoval jednak k úvahám o smyslu srovnávacích testů, jednak k vlastnímu porovnání. Tyto analýzy nejsou tak úplně od věci. Nemají ale sloužit k jalové výměně názorů, který že systém je lepší, ale k diagnostice chyb. Hlavně těch mých. To, jak provádím testy, ukazuje na moje znalosti testovaných systémů, a pokud v testu dělám chybu, mám možnost, že mne na ni někdo upozorní a ukáže mi, jak ji nedělat. A ukáže to i ostatním.

U každého testu musí být předem jasné, co se testuje a co ne. Můj jednoduchý test vůbec nebere v potaz vliv protokolů, hostitelských prostředí a ani sofistikovanost optimalizace prováděcích plánů (pokud ano, tak pouze z jednoho úzkého pohledu). Testuji, jak rychle je RDBMS schopná provést filtrovaný dotaz obsahující čtyřnásobný JOIN. Proč čtyřnásobný? Přišlo mi to jako vhodný kompromis daný klasickým datovým modelem: zákazník, faktura, položka, produkt. Dál prokazuji, že všechny testované systémy jsou prakticky použitelné pro realizaci podobného informačního systému s relativně hodně dobrou odezvou. Není testována celková propustnost db. systému, chování při nedostatku paměti, stabilita, transportní vrstva, chování o.s. při zatížení, závislosti na konfiguračních parametrech atd.

Pokud jste původní článek nečetli, přečtěte si srovnání databází MySQL vs PostgreSQL vs Firebird.

Ono je dobré vědět, co systém zvládne a při jakých odezvách, protože pak při problémech s výkonem člověk nemá tendence hledat chyby v systému, ale u sebe (99%). V podstatě neexistuje pomalá databáze, jen chybně napsané selecty. Stačí jeden nebo dva a celá databáze jde do kolen. Klasická chyba je:

SELECT DISTINCT .. FROM a,b,c WHERE a.pk = b.fk

Pokud tabulka c měla na začátku provozu deset testovacích záznamů, tak se nic nestalo, po pár letech provozu se dotaz prováděl tři hodiny. A ostatní uživatelé měli pocit, že to zas dneska nějak nejede. Nemá cenu si hrát s konfigurací databáze, dokud nemám jistotu, že jsem neudělal, nebo někdo z mých předchůdců, stejnou chybu. Naštěstí už není problém logovat všechny dotazy zpracovávané déle, než je určitý limit (mně se osvědčilo 200 ms).

Delší dobu jsem navíc uvažoval o podobném testu, chtěl jsem se podívat na výkonnost PostgreSQL oproti MsSQL na MS Windows. V testech, které jsem si pro sebe dělal před rokem (jednalo se pouze o relativně komplikovanější selekty), byla PostgreSQL srovnatelná s MsSQL 2k.

Testovací data jsem získal metodou Monte Carlo, bez exaktních parametrů. Hlavně šlo o to získat data, která by neměla rovnoměrné rozdělení. Naprosto záměrně jsem si vybral nekomplikované SQL příkazy – chci otestovat základní funkcionalitu.

  • Začínám s 50 zákazníky a 20 produkty v databázi
  • každý den přidám 20 nových zákazníků rovnoměrně rozdělených do tří kategorií a, b, c.
  • nakoupí vždy – věrný zákazník
  • pokud je v db déle než dva roky, nekoupí nic
    pokud je v db jeden až dva roky, koupí s 50% pravděpodobností
    pokud je v db méně než jeden rok, koupí vždy
  • nikdy nekoupí nic – jalové záznamy v db.
  • pokud už zákazník bude nakupovat, má 15 (velký zákazník – kateg. a) nebo pět (malý zákazník – kateg. b) pokusů výběru zboží. Zboží je rozděleno do čtyř kategorií:
    1. bezkonkurenční stálice
    2. stálice, prodá se ale pouze v 50% případů
    3. sezonní záležitost pokud je starší tří let, neprodejné
      starší dvou let prodejné s pravděpodobností 30 %
      starší roku prodejné s pravděpodobností 50 %
      pokud není starší než rok, prodá se vždy
    4. zboží na sklad, neprodá se nikdy

    histogram:
    a: **********
    b: ******
    c: **
     d:

create or replace function priprava_testu(maxdate date) returns void as $$
declare
  d date = to_date('01-01-2000','DD-MM-YYYY');
  pc_zak integer = 0; pc_prod integer = 0;
  zak zakaznik; prod produkt;
  zid integer; pid integer;
  fid integer;
  r integer;
  cyklu integer = 0;
begin
  truncate zakaznik, faktura, polozka, produkt;
  perform setval('faktura_id_seq',1,'f');
  perform setval('polozka_id_seq',1,'f');
  perform setval('produkt_id_seq',1,'f');
  perform setval('zakaznik_id_seq',1,'f');
  alter table faktura disable trigger all;
  alter table polozka disable trigger all;
  -- vychozich 50 zakazniku
  for i in 1..50 loop
    insert into zakaznik(vlozeno) values(d-1);
    pc_zak := pc_zak + 1;
  end loop;
  for i in 1..20 loop -- a 20 produktu
    insert into produkt(vlozeno) values(d-1);
    pc_prod := pc_prod + 1;
  end loop;
  while d < maxdate loop
    cyklu := cyklu + 1;
    -- raise notice 'cyklus: %', cyklu;
    -- kazdy den 20 novych zakazniku a kazdy mesic 1 novy produkt
    for i in 1..20 loop
      insert into zakaznik(vlozeno) values(d-1);
      pc_zak := pc_zak + 1;
    end loop;
    if extract(day from d) = 1 then
      insert into produkt(vlozeno) values(d-1);
      pc_prod := pc_prod + 1;
    end if;
    -- prijde 50 zakazniku denne
    for i in 1..50 loop -- 20
      zid := rnd(pc_zak);
      select * into zak from zakaznik where id = zid;
      continue when zak.kategorie = 'c';
      continue when zak.kategorie = 'b' and zak.vlozeno < ago(d,'2 years');
      continue when zak.kategorie = 'b' and zak.vlozeno < ago(d,'1 year') and random() > 0.5;
      -- jeste neni jiste jestli budu generevat fakturu
      fid = null;
      for j in 1..case zak.kategorie when 'a' then 15 else 5 end loop
        pid = rnd(pc_prod);
        select * into prod from produkt where id = pid;
        continue when prod.kategorie = 'd'; -- nikdy se neprodaji
        if prod.kategorie = 'c' then
          r := rnd(10);
          continue when prod.vlozeno < ago(d,'3 years');
          continue when prod.vlozeno < ago(d,'2 years') and r <= 3;
          continue when prod.vlozeno < ago(d,'1 years') and r <= 5;
        end if;
        continue when prod.kategorie = 'b' and random() < 0.5;
          -- ok, mam koupeno
        if fid is null then
          insert into faktura(vlozeno, zakaznik_id) values(d, zid);
          fid := currval('faktura_id_seq');
        end if;
        insert into polozka(faktura_id, produkt_id, kusu)
          values(fid, pid, rnd(10));
      end loop;
    end loop;
    d := d + 1;
  end loop;
  alter table faktura enable trigger all;
  alter table polozka enable trigger all;
end;
$$ language plpgsql; 

Po simulaci 20 let má největší tabulka necelých milión řádků. Nemám důvod pro generování více hodnot, pro to, co chci testovat, mám hodnot dost a platí víc dat, víc čekání při importu. Rozhodně dalším zajímavým testem by bylo sledování závislosti času provádění dotazu na objemu dat (detekce singularit). Já se spokojím s tím, že na objemu, který jsem si subjektivně určil za dostačující, jsem nezjistil žádný problém.

Informace o testovací databázi
tabulka počet řádků indexy
zákazník 146150 pk
faktura 152865 pk, vloženo
položka 940826 pk
produkt 260 pk

Trochu mne zaráží, když se jako argument pro přenositelnost db použije věta: „Kdybychom během vývoje přišli na to, že db nestačí, tak můžeme bezproblémově přejít na jiný systém.“. To je pravda, ale už v okamžiku návrhu db. schématu si mohu (mám) otestovat vhodnost systému. Je to jedna z mála věcí, které jdou ověřit, aniž bych musel programovat. Náklady na několikadenní verifikaci se musí bohatě vrátit – odladí se db. schéma, získají se testovací data, první vzorové SQL pro reporty, minimalizujete změny db. schématu. Opět klasická ukázková chyba v návrhu – import stávajících dat se nechává na poslední etapu vývoje. Důvod je jednoduchý – pracnost, nebudeme to přeci dělat dvakrát (jen výjmečně se jedná pouze o přesun dat, často je to spojené s čištěním, aktualizací, přeformátováním, slučování dat z několika is – moje poslední zkušenost: vlastní vývoj cca dva roky, konverze + sloučení dat ze tří různých systémů jeden rok.). Je tu jeden háček, riziko, že zjistíme, že taky ta data prostě nenaimportujeme, nebo v lepším případě, když už je naimportujeme, tak že je to s nimi pomalé, vyskytnou se občas problémy v zobrazení. Toto riziko lze minimalizovat (v současnosti většina vyvíjených aplikací nahrazuje existující systémy – málokde po vás nebudou požadovat konverzi dat).

Strávil jsem jeden vzrušující den importy, exporty mezi Postgresem, Oraclem, MySQLem, Firebirdem. Existují teda lepší způsoby jak příjemně prožít den, nicméně můžu říct, že jsem se něčemu novému naučil. Chtěl jsem si potvrdit hypotézu, že všechny databáze jsou víceméně dnes už natolik kvalitní, že i náročnější dotazy nad většími objemy by pro ně neměly představovat problém a neměly by mezi nimi být ani větší rozdíly. Pokud jsou, tak to nebude signalizovat rozdíl mezi systémy, jako spíš moji nešikovnost zacházet s databází. A pokud mezi nimi rozdíly nebudou, tak mohu být spokojený, protože budu vědět, že s databázemi zacházím správně.

Jestli se tato hypotéza potvrdila, nebo ne, nechám na vás. Na statistické testy nemám tu správnou kvalifikaci. Subjektivně si myslím, že rozdíly jsou, ale menší, než jsem čekal. Jedna nebo dvě vteřiny nic neznamená. Spíš mne překvapila svižnost Oraclu – není to zas až takový mastodot, jak se obecně věří. Příjemně překvapilo MySQL, má se za to, že komplikovanější SQL příkazy nezvládá, není to pravda. Navíc rozdíl mezi innodb a myisam tabulkami je menší, než se povídá, takže není důvod proč se innodb tabulkám vyhýbat (na win SELECT z innodb tabulek byl rychlejší než z myisam).

Narazil jsem na několik zajímavostí:

  1. V případě Firebirdu se snažte u importované tabulky povypínat indexy. Pokud je nevypnete, import se protáhne, asi tak z jedné minuty na dvacet. Každých 50 tisíc řádků vložte COMMIT (u Oracle 100 tisíc). Bez vložených COMMITů systém pojede na 100 % a Firebird se poflakuje na 1 %. Je znát, že u Fb a Oraclu se dělá import jinak – načítal jsem SQL dump. Ve win Firebird žádné problémy neměl a žádné berličky nepotřeboval. Nedivím se rozporuplným názorům na Fb, ve win je to úplně něco jiného a jako embled server bych asi neuvažoval o ničem jiném.
  2. Firebird a MySQL indexuje i cizí klíče, což dost pomáhá. Funguje to i v PostgreSQL, kde se implicitně index na sloupce s cizími klíčí nevytváří. V Oraclu to v jednom případě hodně pomohlo a ve zbývajících dvou mírně uškodilo. První výsledky na innodb s indexovanými cizími klíči byly dost špatné. Pomohl rebuild indexů (pravděpodobně změna typu tabulek není úplně košer, co se týče indexů).
  3. Každá databáze má jiný způsob jak zobrazit prováděcí plány. PostgreSQL a MySQL příkaz Explain Select .., Firebird Set plan, Oracle set autotrace on. V plánech jsem si pouze ověřoval použití indexů.
  4. Pro import a provádění SQL příkazů obsahujích datumy ve formátu YYYY-MM-DD je nezbytné provést příkaz alter session set nls_date_format = 'YYYY-MM-DD'. Metapříkaz pro import souboru je znak @.
  5. U postgresu vacuum analyze je nutnost, přidáním paměti se dá ještě něco dohnat, nicméně výchozí nastavení je v pohodě (pro tento test). Ekvivalent u firebirdu set statistics index…žádný pozitivní vliv neměl – pravděpodobně se nejedná o ten případ, kdy by mělo smysl tento příkaz spouštět.
  6. Importoval jsem do isam tabulek, příkazem ALTER TABLE ENGINE = innodb jsem je překlopil do innodb.
  7. PostgreSQL drží indexy v cache, takže první dotaz trvá dvakrát déle než druhý (se změněnými parametry). Ostatní databáze takový rozdíl mezi prvním a druhým dotazem nemají. Je to znát u rychlých dotazů. PostgreSQL je rychlejší na Linuxu, Firebird ve win, a nevím proč, innodb tabulky jsou ve win o dost rychlejší.
  8. zopakuji obecná doporučení, o kterých vím (pokud víte o dalších, napište je): PostgreSQL: jednou denně vacuum, při větších změnách v datech vacuum analyze Firebird: při změnách v tabulkách nad 30 % rebildnout indexy, importovat bez indexů a pravidel, ty vytvořit dodatečně, při importu větších tabulek, každých 50 tis. záznamů COMMIT (pouze na Linuxu).

Odbočím: V diskusi na Root.cz bylo v jednom příspěvku tvrzení, že v 8.1 už není potřeba spouštět vacuum, protože už tam je autovacuum. To je pravda jen napůl. Je tam, ale defaultně je vypnuté – což může být drobná past pro začátečníky. Důvodů je víc: je to neověřené provozem, použití vynucuje překopání CRON skriptů, konečně běh záleží na provozních statistikách, které jsou implicitně vypnuté taky.

Čísla, která uvádím, berte jen jako orientační a platná pouze pro můj test a mou nijak optimalizovanou konfiguraci. Na jejich základě (i dalších podobných testů) se nedá posuzovat kvalita nebo výkon. Díky tomu je tenhle článek jen o fous objektivnější než diskutování u piva. Navíc hodnoty, které jsem naměřil, jsou i na tak jednoduchém příkladě natolik rozporuplné, že je nejde jednoduše interpretovat. Trochu více bych se dozvěděl analýzou a porovnáním prováděcích plánů, hmm :-( tak po několikatýdenním studiu.

Testovací prostředí: Sarge, W2k3 server, Pentium M 1.6 (2Mb), 512MB – Prestigio Nobile 156 Firebird-2 (alfa), Oracle 10g (pouze linux), PostgreSQL8.1, MySQL5, SQL Server 2k, SQL Server 2k5

První test
myisam innodb ora* pgsql* fb mssql*
Linux 0.10 1.95 0.22 0.05 0.10
w2k3 0.11 0.09 0.12 0.15 0.05

* dodatečně přidány indexy na cizí klíče

select count(*)
from zakaznik z join faktura f on z.id = f.zakaznik_id
                join polozka p on f.id = p.faktura_id
                join produkt pr on p.produkt_id = pr.id
where f.vlozeno between '2000-01-01' and '2000-02-01'
      and pr.trida = 'a'; 
Druhý test
myisam innodb ora* pgsql* fb mssql*
Linux 1.95 2.72 0.57 0.97 4.44
w2k3 1.02 0.90 4.30 2.49 0.80

* dodatečně přidány indexy na cizí klíče

select count(*)
from zakaznik z join faktura f on z.id = f.zakaznik_id
                join polozka p on f.id = p.faktura_id
                join produkt pr on p.produkt_id = pr.id
where z.kategorie = 'b' and pr.trida = 'b'; 
Třetí test
myisam innodb ora* pgsql* fb mssql*
Linux 0.28 2.18 0.18 0.10 1.13
w2k3 0.31 0.22 0.50 0.57 0.57

* dodatečně přidány indexy na cizí klíče

select count(*)
from zakaznik z join faktura f on z.id = f.zakaznik_id
                join polozka p on f.id = p.faktura_id
                join produkt pr on p.produkt_id = pr.id
where f.vlozeno between '2000-01-01' and '2000-02-01'
   or f.vlozeno between '2001-01-01' and '2001-02-01'
   or f.vlozeno between '2002-01-01' and '2002-02-01'
   or f.vlozeno between '2003-01-01' and '2003-02-01'
 and pr.trida = 'a'; 

Není náhodou, že Oracle i Microsoft připravili expres verze svých databází, které jsou k dispozici zdarma nebo skoro zdarma. Je to vliv tlaku O.S. SQL databází i toho, že jedna nastupuje mladší generace DBA, která si dovede život bez velkých databází představit. Oracle se možná rozzpomněl na staré časy. Microsoft SQL Server XE jsem neviděl, ale Oracle Database XE mohu jedině doporučit. Jak instalace, tak provoz jsou překvapivě jednoduché.

Našli jste v článku chybu?

3. 11. 2005 11:04

Pavel Stehule (neregistrovaný)
hmm tak mam smulu. A delam si z toho ... Oraclisti by meli byt spokojeni za reklamu. Dopadlo to pro ne dobre. Pokud bych je nakrk nejak zvlast, tak at se ozvou. Neni problem si mne najit.

Patrim k tem zivlum, kteri maji problemy nektere veci respektovat, a tohle je jedna z nich. Kdyby se bod d) striktne dodrzoval, tak by se na netu nesmela objevit zadna informace o tom co v Oracle a kdy pouzit, protoze to je v jistem smyslu taky benchmark aniz by se k tomu nevyjadrilo Oracle. Podivejte se na n…

29. 12. 2005 12:37

Pavel Stěhule (neregistrovaný)
Nástřel , se 100 000 tisici to jeste proslo, paraganska padina na jistotu 50 000 :-)
Podnikatel.cz: Platební brány a EET? Stále s otazníkem

Platební brány a EET? Stále s otazníkem

Vitalia.cz: I církev dnes vyrábí potraviny

I církev dnes vyrábí potraviny

Podnikatel.cz: Přivýdělek u Airbnb nebo Uberu? Čekejte kontrolu

Přivýdělek u Airbnb nebo Uberu? Čekejte kontrolu

Podnikatel.cz: Přehledná titulka, průvodci, responzivita

Přehledná titulka, průvodci, responzivita

Vitalia.cz: Jak koupit Mikuláše a nenaletět

Jak koupit Mikuláše a nenaletět

DigiZone.cz: Recenze Westworld: zavraždit a...

Recenze Westworld: zavraždit a...

Vitalia.cz: Baletky propagují zdravotní superpostel

Baletky propagují zdravotní superpostel

Lupa.cz: Google měl výpadek, nejel Gmail ani YouTube

Google měl výpadek, nejel Gmail ani YouTube

Měšec.cz: U levneELEKTRO.cz už reklamaci nevyřídíte

U levneELEKTRO.cz už reklamaci nevyřídíte

Vitalia.cz: Tesco: Chudá rodina si koupí levné polské kuře

Tesco: Chudá rodina si koupí levné polské kuře

Podnikatel.cz: Dárky v podnikání. Jak je uplatnit v daních?

Dárky v podnikání. Jak je uplatnit v daních?

Vitalia.cz: Paštiky plné masa ho zatím neuživí

Paštiky plné masa ho zatím neuživí

Podnikatel.cz: Na poslední chvíli šokuje vyjímkami v EET

Na poslední chvíli šokuje vyjímkami v EET

Lupa.cz: UX přestává pro firmy být magie

UX přestává pro firmy být magie

120na80.cz: Rakovina oka. Jak ji poznáte?

Rakovina oka. Jak ji poznáte?

DigiZone.cz: Sony KD-55XD8005 s Android 6.0

Sony KD-55XD8005 s Android 6.0

Vitalia.cz: „Připluly“ z Německa a možná obsahují jed

„Připluly“ z Německa a možná obsahují jed

Podnikatel.cz: Prodává přes internet. Kdy platí zdravotko?

Prodává přes internet. Kdy platí zdravotko?

Podnikatel.cz: 1. den EET? Problémy s pokladnami

1. den EET? Problémy s pokladnami

Podnikatel.cz: K EET. Štamgast už peníze na stole nenechá

K EET. Štamgast už peníze na stole nenechá