postgresql pgBench tool running user defined SQL script
benchmarking, multithreading, postgresql, sql
Solution
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.
Problem
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 ;)