Našel jsem několik nástrojů implementujících nějaký TPC-H-like benchmark, ale z různých důvodů jsem je shledal nepoužitelnými. Například DBT-3 je trochu zastaralý (poslední update v roce 2005) a výsledný dbgen mi z podivných důvodů padal.

Nástroj od TPC Councilu funguje celkem dobře, a ačkoliv nepodporuje PostgreSQL přímo, nedá příliš práce ho přiohnout. Stáhnout si ho můžete přímo na stránce TPC-H page (v pravém sloupci pod TPC-H specifikací). Stáhněte si tgz balíček (37MB).

Kompilace

Po rozbalení si musíte připravit Makefile - přejděte do dbgen adresáře, zkopírujte makefile.suite na Makefile a upravte následující proměnné (cca kolem řádky 109):

CC=gcc
DATABASE=ORACLE
MACHINE=LINUX
WORKLOAD=TPCH

Pokud nepoužíváte Linux, nastavte MACHINE na odpovídající hodnotu. Potom prostě zadejte "make."

Generování dat

Takže teď máte zkompilovaný nástroj "dbgen" který generuje data v CSV formátu. Stejně jako pgbench má scaling factor ovlivňující množství generovaných dat - výchozí hodnota (1) znamená přibližně 1GB zdrojových dat, t.j. asi 2GB dat po nalití do databáze. Takže vygenerujme 10GB dat:

$ ./dbgen -s 10

Tak získáme osms .tbl souborů v CSV formátu, v každém jsou data pro jednu tabulku. Problém je že každý řádek končí jedním "|" oddělovačem na konci řádku navíc, takže PostgreSQL nedokáže data naimportovat. Ale to je lehce řešitelné příkazem "sed" - stačí udělat toto:

for i in `ls *.tbl`; do sed 's/|$//' $i > ${i/tbl/csv}; echo $i; done;

Teď máme osm CSV souborů které lze načíst do databáze. Ale nejdříve ji musíem vytvořit.

Plnění databáze

Ačkoliv TPC-H specifikace databázovou strukturu popisuje, skripty pro její vytvoření součástí balíčku nejsou. Připravil jsem jednoduchý create skript který vytvoří všechny potřebné tabulky a alter skript který na nich nahodí cizí klíče (po naplnění). Takže vytvořte databázi a v ní tabulky

$ createdb tpch
$ psql tpch < tpch-create.sql

Teď už můžeme do databáze nalít vygenerovaná data - nezapomeňte že COPY vyžaduje absolutní cesty, použít můžete například následující skript

dir=`pwd`
opts="-h localhost pgbench"

psql $opts -c "COPY part FROM '$dir/part.csv'
                               WITH (FORMAT csv, DELIMITER '|')"

psql $opts -c "COPY region FROM '$dir/region.csv'
                               WITH (FORMAT csv, DELIMITER '|')"

psql $opts -c "COPY nation FROM '$dir/nation.csv'
                               WITH (FORMAT csv, DELIMITER '|')"

psql $opts -c "COPY supplier FROM '$dir/supplier.csv'
                               WITH (FORMAT csv, DELIMITER '|')"

psql $opts -c "COPY customer FROM '$dir/customer.csv'
                                WITH (FORMAT csv, DELIMITER '|')"

psql $opts -c "COPY partsupp FROM '$dir/partsupp.csv'
                               WITH (FORMAT csv, DELIMITER '|')"

psql $opts -c "COPY orders FROM '$dir/orders.csv'
                               WITH (FORMAT csv, DELIMITER '|')"

psql $opts -c "COPY lineitem FROM '$dir/lineitem.csv'
                               WITH (FORMAT csv, DELIMITER '|')"

a poté vytvořte cizí klíče

$ psql tpch < tpch-alter.sql

Teď máme databázi naplněnou daty, takže si připravme dotazy.

Generování dotazů

TPC-H specifikuje 22 dotazů (nebo spíše šablon), uložených v "queries" adresáři, a "qgen" nástroj který z těchto šablon generuje dotazy (víceméně je plní náhodnými daty). Před jeho použitím ale musíme vyřešit několik drobných problémů.

Zaprvé - většina dotazů používá ROWCOUNT (nebo variantu specifickou pro podporované databáze) namísto LIMIT. To je celkem jednoduché opravit - stačí projít šablony. Zadruhé cca pět dotazů používá korelované agregované poddotazy s obřími vnějšími tabulkami - to v PostgreSQL příliš dobře nefunguje, ale je lehké to přepsat na JOIN. Namísto dotazu typu

select
	sum(l_extendedprice) / 7.0 as avg_yearly
from
	lineitem,
	part
where
	p_partkey = l_partkey
	and p_brand = ':1'
	and p_container = ':2'
	and l_quantity < (
		select
			0.2 * avg(l_quantity)
		from
			lineitem
		where
			l_partkey = p_partkey
	)
LIMIT 1;

tak dostaneme dotaz

select
	sum(l_extendedprice) / 7.0 as avg_yearly
from
	lineitem,
	part,
	(SELECT l_partkey AS agg_partkey,
                0.2 * avg(l_quantity) AS avg_quantity
          FROM lineitem GROUP BY l_partkey) part_agg
where
	p_partkey = l_partkey
	and agg_partkey = l_partkey
	and p_brand = ':1'
	and p_container = ':2'
	and l_quantity < avg_quantity
LIMIT 1;

který produkuje stejný výsledek a je daleko rychlejší (ten první dotaz mi nikdy ani nedoběhl). Nejsem si úplně jist jak striktní TPC-H je, co se týká formátu dotazů, ale mým cílem stejně nebylo provádět 100% správný TPC-H benchmark, spíše je to dobré výchozí místo pro DSS benchmark.

Modifikované dotazy jsou k dispozici zde - stačí je rozbalit do nového adresáře 'queries-pg'

$ mkdir queries-pg
$ tar -xzvf ~/tpch-queries.tgz

a můžete začít generovat skript s dotazy. Takto

$ DSS_QUERY=queries-pg ./qgen > workload.sql

získáte skript který spustí právě jeden dotaz pro každý vzor. Pokud chcete generovat skript jen s některými dotazy, vypište jejich ID na příkazovou řádku takto:

$ DSS_QUERY=queries-pg ./qgen 2 3 7 > workload.sql

Tak získáte skript jen s dotazy 2.sql, 3.sql a 7.sql. Pokud chcete vygenerovat větší skript s více dotazy, můžete udělat například toto

for r in `seq 1 10`
do
  rn=$((`cat /dev/urandom|od -N3 -An -i` % 10000))
  DSS_QUERY=queries-pg ./qgen -r $rn >> workload.sql
done

Všimněte si jak je qgen pokaždé inicializován jiným náhodným číslem (standardně pro inicializaci používá timestamp, takže sputění během jedné vteřiny dá stejné dotazy).

Spuštění skriptu

Máme naplněnou databázi, víme jak generovat dotazy. Ale jak je spouštět? Uvažoval jsem o využití pgbench "custom skriptů" ale bylo by celkem obtížné generovat všechny ty různé náhodné hodnoty různých typů.

Používám tedy velmi jednoduchý postup - rozhodnu se kolik "klientů" chci spustit (s DSS benchmarkem toto bývá relativně nízké číslo - např. 4), připravím odpovídající počet skriptů (např. workload-1.sql ... workload-4.sql) a pak je spustím zhruba takto:

#start the processes
for c in `seq 1 4`
do
  /usr/bin/time -f "total=%e" -o result-$c.log \
                psql tpch < workload-$c.sql > /dev/null 2>&1 &
done;

# wait for the processes
for p in `jobs -p`
do
   wait $p;
done;

Tím získám sadu logů s výsledky pro každý skript. Dá se na tom samozřejmě spousta věcí zlepšit, např. sbírat různé statistiky (vmstat, iostat, ...) a tak dále.

Mějte ale na paměti že uvedené dotazy musí přežvýkat spoustu dat (v závislosti na zvoleném scale factoru), a pokud se něco pokazí (např. je zvolen špatný plan), potrvá to věčně. Proto obvykle vytvářím sadu souborů s jedním dotazem na soubor, a pokud dotaz neskončí v daném limitu tak ho zabiji. To lze zařídit například pomocí příkazu "timeout" takto

# prepare queries and execute them with 60 seconds timeout
for q in `seq 1 22`
do
  DSS_QUERY=queries-pg ./qgen $q > q-$q.sql
  timeout 60 psql tpch < q-$q.sql
done

Tentokrát už inicializace přes generování náhodných čísel není potřeba, protože dotazy většinou trvají výrazně déle než jednu vteřinu.