Load/stress-testing PostgreSQL queries

[Dallaylaen]

Is there a tool that would allow to storm a Pg database with lots of concurrent requests to determine their performance, reliability and chances of getting a deadlock?

Suppose I have a set of queries with bind values, which may in turn depend on other queries' results. I also have some test cases which I believe reflect real-life load.

So I'd like to have some module that would run those test cases in random order, repeatedly, in lots of threads, and measure performance, error rates, etc. for all queries separately.

I'm interested specifically in Perl because we are a Perl shop, but I wouldn't mind something scriptable in (say) python/lua/bash/etc. provided it does what I need.

[daxim]

pgexerciser

[ggoebel]

Visit the postgresql wiki Statement Playback and Performance QA Testing pages.

Tsung looks like a good candidate

[erix]

I'd start with the benchmark tool that comes with postgresql: pgbench.

pgbench can be used immediately with several pre-set scenario's (e.g. write-heavy, readonly), but it can also be given custom SQL to test your own data/queries.

Or perhaps start right away with Greg Smith's pgbench-tools, which is built on top of pgbench. It produces output in useful series of text, graphs, html. Very nice.

I've heard good things about Tsung but never used it. It would seem that the pgbench-tools are the obvious first thing to run as they require almost no work to get going, and will provide you with a good first idea about your system's performance.

Lots of tuning information in the mailing lists; especially pgsql-performance (and perhaps, pgsql-hackers). The search interface on the postgresql website isn't very good but the mbox files are available too.

http://www.postgresql.org/list/pgsql-performance/

http://www.postgresql.org/list/



(Here is a simple pgbench example, small-scale, readonly. Add command-line connection details if necessary. (this output is from version 9.3devel, 9.2 output is similar (slightly more verbose))).

#
#  initialise small table...
#

$ pgbench -h /tmp -i -s 10
creating tables...
100000 of 1000000 tuples (10%) done (elapsed 0.10 s, remaining 0.94 s)
+.
200000 of 1000000 tuples (20%) done (elapsed 0.21 s, remaining 0.85 s)
+.
300000 of 1000000 tuples (30%) done (elapsed 0.31 s, remaining 0.71 s)
+.
400000 of 1000000 tuples (40%) done (elapsed 0.40 s, remaining 0.59 s)
+.
500000 of 1000000 tuples (50%) done (elapsed 0.49 s, remaining 0.49 s)
+.
600000 of 1000000 tuples (60%) done (elapsed 0.59 s, remaining 0.39 s)
+.
700000 of 1000000 tuples (70%) done (elapsed 0.68 s, remaining 0.29 s)
+.
800000 of 1000000 tuples (80%) done (elapsed 0.77 s, remaining 0.19 s)
+.
900000 of 1000000 tuples (90%) done (elapsed 0.86 s, remaining 0.10 s)
+.
1000000 of 1000000 tuples (100%) done (elapsed 0.96 s, remaining 0.00 
+s).
vacuum...
set primary keys...
done.

#
#  readonly, 90 concurrent clients, 5 minutes run
#

$ pgbench -h /tmp -n -S -T 300 -c 90
transaction type: SELECT only
scaling factor: 10
query mode: simple
number of clients: 90
number of threads: 1
duration: 300 s
number of transactions actually processed: 6905843
tps = 23012.221067 (including connections establishing)
tps = 23044.131737 (excluding connections establishing)

edit: Of course, quite possibly you have to use a much larger initialisation-value (the 'scale', -s, in the init invocation); and the performance obviously gets *much* worse when the tables do not fit in memory anymore or write-mostly is used as opposed to readonly, as above.