Waiting for 8.5 – Multi-threaded pgbench

On 3rd of August, Tatsuo Ishii committed patch by ITAGAKI Takahiro:

Log Message:
-----------
Multi-threaded version of pgbench contributed by ITAGAKI Takahiro,
reviewed by Greg Smith and Josh Williams.
 
Following is the proposal from ITAGAKI Takahiro:
 
Pgbench is a famous tool to measure postgres performance, but nowadays
it does not work well because it cannot use multiple CPUs. On the other
hand, postgres server can use CPUs very well, so the bottle-neck of
workload is *in pgbench*.
 
Multi-threading would be a solution. The attached patch adds -j
(number of jobs) option to pgbench. If the value N is greater than 1,
pgbench runs with N threads. Connections are equally-divided into
them (ex. -c64 -j4 => 4 threads with 16 connections each). It can
run on POSIX platforms with pthread and on Windows with win32 threads.
 
Here are results of multi-threaded pgbench runs on Fedora 11 with intel
core i7 (8 logical cores = 4 physical cores * HT). -j8 (8 threads) was
the best and the tps is 4.5 times of -j1, that is a traditional result.
 
$ pgbench -i -s10
$ pgbench -n -S -c64 -j1   =>  tps = 11600.158593
$ pgbench -n -S -c64 -j2   =>  tps = 17947.100954
$ pgbench -n -S -c64 -j4   =>  tps = 26571.124001
$ pgbench -n -S -c64 -j8   =>  tps = 52725.470403
$ pgbench -n -S -c64 -j16  =>  tps = 38976.675319
$ pgbench -n -S -c64 -j32  =>  tps = 28998.499601
$ pgbench -n -S -c64 -j64  =>  tps = 26701.877815
 
Is it acceptable to use pthread in contrib module?
If ok, I will add the patch to the next commitfest.

Generally it's all pretty simple to understand, so let's just see how it goes on simple laptop of mine.

First, let's create test database:

=> pgbench -U pgdba -i -s 100 -p 5840

Generated tables:

    table_name    | row_count | pg_relation_size | pg_total_relation_size
------------------+-----------+------------------+------------------------
 pgbench_accounts |  10000000 |       1300324352 |             1480474624
 pgbench_branches |       100 |             8192 |                  57344
 pgbench_tellers  |      1000 |            40960 |                 114688
 pgbench_history  |         0 |                0 |                      0
(4 rows)

So, let's do some tests with single-threaded pgbench:

=> for CONCURRENCY in 1 5 10 20 50 100
do
    printf "%4u : " $CONCURRENCY
    pgbench -n -S -c $CONCURRENCY -s 100 -T 30 -U pgdba 2> /dev/null | \
        perl -ne 'print if s/\s*\(excluding connections establishing\)//'
done
   1 : tps = 2010.950825
   5 : tps = 9498.375793
  10 : tps = 8916.915113
  20 : tps = 8847.373778
  50 : tps = 7682.842104
 100 : tps = 7497.359333

And how it will work with more workers?

=> for WORKERS in 2 5 10
do
    for CONCURRENCY in 10 20 50 100
    do
        printf "Workers: %u; concurrency: %4u : " $WORKERS $CONCURRENCY
        pgbench -n -S -c $CONCURRENCY -j $WORKERS -s 100 -T 30 -U pgdba 2> /dev/null | \
        perl -ne 'print if s/\s*\(excluding connections establishing\)//'
    done
done
Workers: 2; concurrency:   10 : tps = 10575.765192
Workers: 2; concurrency:   20 : tps = 10398.844294
Workers: 2; concurrency:   50 : tps = 9337.354887
Workers: 2; concurrency:  100 : tps = 8667.289463
Workers: 5; concurrency:   10 : tps = 10741.912416
Workers: 5; concurrency:   20 : tps = 11589.755475
Workers: 5; concurrency:   50 : tps = 11261.211785
Workers: 5; concurrency:  100 : tps = 9458.993760
Workers: 10; concurrency:   10 : tps = 11945.691258
Workers: 10; concurrency:   20 : tps = 12302.165039
Workers: 10; concurrency:   50 : tps = 11536.045807
Workers: 10; concurrency:  100 : tps = 10472.515850

As you can see the numbers improved.

Generally – given concurrency C and W workers, each worker will open C/W connections to PostgreSQL. And since each of them (workers) is separate thread, we can fully utilize new, multi-core processors.

2 thoughts on “Waiting for 8.5 – Multi-threaded pgbench”

Comments are closed.