Friday May 16, 2008

PGCon 2008 next week in Ottawa, Canada

It is that time of the year when many PostgreSQL fans gather in Ottawa, Canada for PGCon 2008 next week. This will be my first visit to PGCon in Ottawa. Earlier this year I had presented two sessions "PostgreSQL and Benchmarks" and "Best Practices of PostgreSQL on Solaris" at PostgreSQL Conference 2008 East in Maryland. Thanks to that visit, this time I might recognize many people by face this time around.

Sun is a Gold Sponsor at PGCon 2008. There will be quite a bit of presence from Sun in PGCon. Josh, Max, Robert, Magne, Zdenek, Jim, Mayuresh et all will be present out there.

Josh Berkus is doing a tutorial on  "GUCs: A Three Hour Tour" on Tuesday morning 9:00 am.

Susanne Ebrecht (from MySQL team @ Sun) will also be presenting "What PostgreSQL could learn from MySQL" on Thursday 1:30pm.

 I  will be presenting "Problems with PostgreSQL on multi-core Systems with multi-terabyte Data" at Thursday 3:00pm.  I am actually presenting on behalf of the PostgreSQL Performance Team @ Sun and based on various performance work done by the group.

Robert Lor is also doing a  5-min Lightning Talk on New DTrace Probes proposed for PostgreSQL 8.4. Not to steal his thunder but  the demo will be quite interesting. It is part of Lightning Talks on Thursday 5:30pm

Zdenek Kotala will be presenting "PostgreSQL Upgrade Project" on Friday 3:00pm . In-place Upgrade in an eagerly awaited project.

Personally I will be meeting many of the Sun folks also for the first time in person. Generally we talk on conference calls, emails, etc.  Definitely looking forward to that.


Tuesday Feb 12, 2008

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.



Jignesh Shah is Principal Software Engineer in Application Integration Engineering, Oracle Corporation. AIE enables integration of ISV products including Oracle with Unified Storage Systems. You can also follow me on my blog


« August 2016