Tuesday, December 20, 2011

Using DVDStore with PostgreSQL

We now have support for PostgreSQL in the popular DVDStore Benchmark which stresses database using an emulated DVDStore e-Commerce website. DVDStore Benchmark is maintained by Dave Jaffe (Dell) and  Todd Muirhead (VMware).  It is an open source database test kit. The beauty of the benchmark kit is it allows the same web application being deployed either as
  1. Java/Tomcat  and connect to the database,
  2. Web Server/PHP and connect to the database,
  3. IIS/ASP.NET connect to the database or
  4. Direct connect to the database and invoking the business logic as stored procedures stored on the database itself.

Currently the PostgreSQL implementation details are as follows
  1. Java/Tomcat using PostgreSQL JDBC driver,
  2. Web Server/PHP  using  PHP-postgres modules which uses  libpq
  3. Currently there is noIIS/ASP.NET web app  implementation for PostgreSQL
  4.  Direct connect to PostgreSQL database and business logic implemented in stored procedures however the driver is implemented using .NET C# and requires Npgsql

Setup instructions for the database are relatively quite easy.
  1. Download ds21.tar.gz  and also ds21_postgresql.tar.gz from  http://linux.dell.com/dvdstore/
  2. Unzip them on the system running PostgreSQL
  3. The default data size is 10MB. If you want a different size execute 'perl Install_DVDStore.pl' in the ds2 directory. (Expects perl to be available on the system. I used the option 100, MB , PGSQL, LINUX respectively for the options.)
  4. Assuming you are logged on as the DB Owner and the database is on the localhost at port 5432, execute the script pgsql_create_all.sh in the ds2/pgsqlds2 directory. It will create a database "ds2", two users "ds2/ds2" and "web/web", create tables, load tables, create indexes, update sequences and finally run analyze. (The script needs to be modified slighly if the database is already hardened and you want to control the creation of database and the users.)

Setup for the actual load driver is probably easiest on  another Windows platform as follows as it was designed for .NET platform.
  1. Download and install Windows SDK v6.1 and .NET 3.5 framework  on a Windows Client machine.
  2. Once installed start the CMD prompt from Programs-> Windows SDKv6.1-> CMD Prompt.
  3. Verify the above CMD prompt has path setup for gacutil in windows (Try 'gacutil/l')
  4. Download Npgsql 2.0.11 for msnet35 and install the dlls using the gacutil.exe (Note other versions of Npgsql may have issues.)
    •  gacutil/i Npgsql.dll
    •  gacutil/i Mono.security.dll
    •  gacutil/i policy-2.0.Npgsql.dll

With the above setup you can use the ds2webdriver.exe in ds2/drivers or the direct ds2pgsqldriver.exe in ds2/pgsqlds2. More on running the benchmark driver itself  in another post.

1 comment:

Unknown said...

Unfortunately the pg driver "crashed" with the following exception when I tried it on Windows 7 (64bit) and PostgreSQL 9.1.3

Unhandled Exception: System.IndexOutOfRangeException: Index was outside the bounds of the array.
at ds2xdriver.ds2Interface.ds2login(String username_in, String password_in, Int32& customerid_out, Int32& rows_returned, String
[]& title_out, String[]& actor_out, String[]& related_title_out, Double& rt)
at ds2xdriver.User.Emulate()
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ign
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()