Sysbench with PostgreSQL on Solaris

With the acquisition of MySQL I expect that many people might end up comparing MySQL and PostgreSQL using sysbench. It is like a micro-benchmark utility which includes an oltp mode which is used quite a bit to show MySQL performance. It can actually also be used with PostgreSQL. So this post is about how to configure sysbench to work with PostgreSQL. (Primarily a note for myself since I had to do hunt around to get it configured for PostgreSQL).

 
First download the latest version of sysbench. I had downloaded the version sysbench-0.4.8. After gunzip/untar I had to to figure out few steps to get the right configure script for it.

I am going to use the Sun Studio Compiler (since PostgreSQL in my case is also built with Sun Studio Compiler). So I will need the compiler cc in my path. Also after couple of tries I found that I also need the right version of PostgreSQL binaries since the configure script uses pg_config to figure out the Libraries and include files. So make sure your path is setup right before using the configure script. You might also need make, gmake in your path too.

Plus if you dont have mysql already installed on your system (and in your path), you have to use --without-mysql otherwise compilation fails since --with-mysql is default even if you select --with-pgsql since it can support multiple databases with the same binary.

$ cd sysbench-0.4.8

$ PATH=/usr/postgres/8.2/bin:/opt/SUNWspro/bin:/usr/ccs/bin:/usr/sfw/bin:$PATH

$ export PATH

$ ./configure --prefix=$HOME/sysbench-0.4.8 --without-mysql --with-pgsql \
 CFLAGS="-xO2 -m64" CC=cc

$ gmake 

$ gmake install 

So hopefully if all goes well in your setup the sysbench binary should now be available in $HOME/sysbench-0.4.8/bin directory

Now how to run the benchmark with PostgreSQL. First of all make sure that PostgreSQL instance is running and  a database  for the test (for example called dbtest) is available. (I assume that the user is already familiar with Postgres and skipping the steps for it).

Now use the sysbench binary to setup the table for the oltp test. You will generally need to select the size of the table (in terms of rows) to prepare for it.

Here are the ones that I used. (Replace pguser/pgpass with right credentials)

$ ./sysbench --test=oltp --pgsql-user=pguser --pgsql-password=pgpass \
 --pgsql-db=dbtest --db-driver=pgsql --oltp-dist-type=special \
 --oltp-table-size=100000 --oltp-read-only=on --num-threads=16 prepare

$ ./sysbench --test=oltp --pgsql-user=pguser --pgsql-password=pgpass \
 --pgsql-db=dbtest --db-driver=pgsql --oltp-dist-type=special \
--oltp-table-size=100000 --oltp-read-only=on --num-threads=16 run

 
I have extra arguments which may not be needed for both statements but since I was using bash up/down a lot, its just easier to replace prepare/run and num-threads while keeping the rest the same.

However before doing prepare  again make sure to drop the table sbtest

$ psql dbtest -c "drop table sbtest"

Enjoy using sysbench with PostgreSQL on Solaris.