Re: multi-threaded pgbench - Mailing list pgsql-hackers

I just took multi-threaded pgbench for an initial spin, looks good overall 
with only a couple of small rough edges.

The latest code works differently depending on whether you compiled with 
--enable-thread-safety or not, it defines some structures based on fork if 
it's not enabled:

#elif defined(ENABLE_THREAD_SAFETY)
#include <pthread.h>
#else
#include <sys/wait.h>
typedef struct fork_pthread       *pthread_t;
typedef int                        pthread_attr_t;
static int pthread_create(pthread_t *thread, pthread_attr_t *attr, void 
* (*start_routine)(void *), void * arg);
static int pthread_join(pthread_t th, void **thread_return);
#endif

That second code path, when --enable-thread-safety is turned off, crashes 
and burns on my Linux system:

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv 
-I../../src/interfaces/libpq -I. -I../../src/include -D_GNU_SOURCE   -c -o 
pgbench.o pgbench.c -MMD -MP -MF .deps/pgbench.Po
pgbench.c:72: error: conflicting types for pthread_t
/usr/include/bits/pthreadtypes.h:50: error: previous declaration of 
pthread_t was here
pgbench.c:73: error: conflicting types for pthread_attr_t
/usr/include/bits/pthreadtypes.h:57: error: previous declaration of 
pthread_attr_t was here

So that's the first problem to sort out, I was planning to test that path 
as well as the regular threaded one.  Since I'd expect there to be Linux 
packages built both with and without thread safety enabled, they both 
should work, even though people should always be turning safety on 
nowadays.

We should try to get a Windows based tester here too at some point, 
there's a completely different set of thread wrapper code for that OS that 
could use a look by somebody more familiar than me with that platform.

The second thing that concerns me is that there's a limitation in the code 
where the number of clients must be a multiple of the number of workers. 
When I tried to gradually step up the client volume the tests wouldn't 
run:

$ ./pgbench -j 16 -S -c 24 -t 10000 pgbench
number of clients (24) must be a multiple number of threads (16)

Once the larger issues are worked out, I would be much friendlier if it 
were possible to pass new threads a client count so that the last in the 
pool could service a smaller number.  The logic for that is kind of a 
pain--in this case you'd want 8 threads running 2 clients each while 8 ran 
1 client--but it would really be much friendlier and flexible that way.

Onto performance.  My test system has a 16 cores of Xeon X5550 @ 2.67GHz. 
I created a little pgbench database (-s 10) and used the default 
postgresql.conf parameters for everything but max_connections for a rough 
initial test.

Performance on this box drops off pretty fast once you get past 16 
clients; using the original, unpatched pgbench:

c   tps
16  86887
24  70685
32  63787
64  64712
128 60602

A quick test of the new version suggest that there's no glaring 
performance regression running it with a single client thread:

$ ./pgbench.orig -S -c 64 -t 10000 pgbench
tps = 64712.451737 (including connections establishing)

$ ./pgbench -S -c 64 -t 10000 pgbench
tps = 63806.494046 (including connections establishing)

So I moved onto to testing with a worker thread per CPU:

./pgbench -j 16 -S -c 16 -t 100000 pgbench
./pgbench -j 16 -S -c 32 -t 50000 pgbench
./pgbench -j 16 -S -c 64 -t 10000 pgbench
./pgbench -j 16 -S -c 128 -t 10000 pgbench

And got considerably better results:

c  tps
16  96223
32  89014
64  82487
128 74217

That's as much as a 40% speedup @ 32 clients, and even a decent win at 
lower counts.

The patch looks like it accomplishes its performance goals quite well 
here.  I'll be glad to run some more extensive performance tests, but I'd 
like to at least see the version without --enable-thread-safety fixed 
first so that I can queue up and compare both versions when I go through 
that.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD