Getting sysbench to work with Postgres on Flash SSD

System information:

OS: RHEL 5.8.
Sysbench: 0.4.12
Postgres: 9.2.3
RAM: 64GB
CPU: 16 cores E5-2650 0 @ 2.00GHz
SSD: STEC s1120 2TB PCIe
STEC s840 2TB SAS
Running EXT3 on the SSDs

Installing Sysbench.

1. Download sysbench 0.4.12 from http://sourceforge.net/projects/sysbench/
tar zxvf

 ./configure --without-mysql --with-pgsql
2. Edit ./sysbench/drivers/pgsql/drv_pgsql.c

as per

http://jkshah.blogspot.com/2010/10/postgres-9-and-sysbench-0412.html

otherwise sysbench prepare will take a very long time to populate the table.

3. make
Make barfs at libtool with some weird error that looks like its from X11.
../libtool: line 838: X--tag=CC: command not found
../libtool: line 871: libtool: ignoring unknown tag : command not found
../libtool: line 838: X--mode=link: command not found
../libtool: line 1004: *** Warning: inferring the mode of operation is deprecated.: command not found
../libtool: line 1005: *** Future versions of Libtool will require --mode=MODE be specified.: command not found

This isn't an X11 error. Its because libtool sets ECHO=echo and then uses $echo. Edit libtool so all occurrences of $echo are changed to $ECHO, and make will work.

4. make install 

Installs sysbench into /usr/local/bin by default. Make sure you have that in your path.

Installing/initializing Postgres

1. Follow instructions for install PG RPMs here:

http://yum.postgresql.org/

and here

http://wiki.postgresql.org/wiki/YUM_Installation

Make sure the right drive is mounted properly on /var/lib/pgsql before running any initialization commands.

2. Start up PG:

/usr/pgsql-9.2/bin/pg_ctl -D /var/lib/pgsql/9.2/data start

Configuration/tuning

1. Mount ext3 FS on the SSDs with the following options.
data=writeback,noatime,nodiratime
Ext4 has better options for SSDs, but is recommended only for RHEL 6.x distributions with the newer kernel.
2. Postgres docs recommend raising kernel.shmmax and kernel.shmall, however with 64G of memory these values appear to be pretty large anyway.
3. Changes to postgresql.conf:

shared_buffers = 32GB        # Half of available memory
work_mem = 12GB              # Increase the amount of memory used for in-memory sorts/user.
maintenance_work_mem = 1GB    # Used in vacuum    
effective_io_concurrency = 16      # Increase # of I/O threads. Could probably bump this higher for SSD.
checkpoint_segments = 256   # Checkpoint every 4GB instead of every 48M which is the default with value =3      
effective_cache_size = 32GB      # Half of available memory.
default_statistics_target = 1000   # Used by query planner
update_process_title = off           # Don't update ps -e with SQL statements.
autovacuum_max_workers = 16      # Increase to # of cores available to speed up optimization.

I didn't test this, but apparently changing random_page_cost to 1 can help as well.
http://www.databasesoup.com/2012/05/random-page-cost-revisited.html

Running sysbench

1. First create the PG database:

su - postgres

createdb dbtest

# Confirm its been created

psql -c "\l"

2. Run sysbench prepare which populates the table in PG (sbtest by default)

sysbench --test=oltp --pgsql-user=postgres_user --pgsql-password=postgres_user_password --pgsql-host="" --pgsql-db=dbtest --db-driver=pgsql --oltp-table-size=100000000 --num-threads=16 prepare

We're creating a 100M row table here which consumes about 30G of space. This can take about 10 minutes or so.

3. Run the actual test:

THREADS="8 16 24 32 48 60 72 84 100"

for t in $THREADS

do

  # First clear cache

  vmstat -SM

  sync; echo 3 > /proc/sys/vm/drop_caches

  vmstat -SM

  sysbench --test=oltp   --pgsql-user=postgres_user --pgsql-password=postgres_user_password --pgsql-host="" --pgsql-db=dbtest --db-driver=pgsql --oltp-dist-type=gaussian --oltp-table-size=100000000  --oltp-test-mode=complex --num-threads=$t  --max-time=600 --max-requests=25000000 run

done

If you run sysbench with oltp-test-mode=complex, it seems to behave differently with MySQL/InnoDB vs. Postgres as described here:

http://jkshah.blogspot.com/2010/11/sysbench-postgresql-90-and-oltp-complex.html

If your table size is small (i.e. 1M rows) or if you use more threads, you will run into deadlocks.

I worked around this by increasing the number of rows in sbtest to 100M , and restricting max-requests to 25M. Need to work with the sysbench folks to fix this.