postgresql pgBench tool running user defined SQL script

Question

Please help me clarify, if the pgbench tool can execute my own sql scenarios in parallel way? Googling and local searching brought no positive result.

I run the script that execeutes with no errors. But after execution I see no signs, that my script was actually performed. Does pgbench commits transaction with my sql script?

That's an output I get:

C:\Program Files\PostgreSQL\9.2\bin>pgbench.exe -n -h dbserverhost -U postgres -
T 10 -c 64 -j 8 bench_dbname -f c:\Dev\bench_script.sql

transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 64
number of threads: 8
duration: 10 s
number of transactions actually processed: 1020
tps = 95.846561 (including connections establishing)
tps = 103.387127 (excluding connections establishing)

C:\Program Files\PostgreSQL\9.2\bin>

SQL script bench_script.sql is:

--comment here
begin;
  insert into schm.log values ( 'pgbench test', current_timestamp );
end;

SOLUTION

pgBench Windows version is sensitive to the order of the arguements passed to the utility: "bench_dbname" argument must be the last one parameter in a line.

This is the correct example of pgbench Windows version command line:

pgbench.exe -d -r -h 127.0.0.1 -U postgres -T 5 -f C:\Dev\bench_script.sql -c 64 -j 8 postgres

The most useful arguments for me were:

• -T 60 (time in seconds to run script)
• -t 100 (transaction amount per client)
• -d print detailed debug info to the output
• -r include in summary latency value calculated for every action of the script
• -f run user defined sql script in benchmark mode
• -c client amount
• -j thread amount

pgBench official doc

PgBench, I love you! :)

Best wishes everybody ;)

Answer 1

The "transaction type: TPC-B (sort of)" means that it did not process the -f option to run your custom sql script, instead it ran the default query.

On Windows versions, getopt seems to stop parsing the options once it reaches the first one that does not start with a hyphen, i.e. "bench_dbname". So make sure -f comes before that.

Answer 2

I guess you also need the -n option as long as you are using your custom script?

-n

--no-vacuum

Perform no vacuuming before running the test. 
This option is necessary if you are running a custom test scenario 
that does not
include the standard tables pgbench_accounts, pgbench_branches,
pgbench_history, and pgbench_tellers.