MySQL vs PostgreSQL vs Firebird II

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.

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:

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.

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

* 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'; 

* 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'; 

* 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é.