Monday Mar 02, 2009

New DTrace Probes in PostgreSQL 8.4

DTrace probes were introduced in PostgreSQL starting in 8.2. Initially, only a handful were added, and they were mostly for developers. In 8.4, many more (46 to be exact) have been added, and they are targeted more toward database administrators as shown below.

query-parse-start(const char \*)
query-parse-done(const char \*)
query-rewrite-start(const char \*)
query-rewrite-done(const char \*)
query-start(const char \*)
query-done(const char \*)
statement-status(const char \*)
sort-start(int, bool, int, int, bool)
sort-done(unsigned long, long)
buffer-read-start(ForkNumber, BlockNumber, Oid, Oid, Oid, bool)
buffer-read-done(ForkNumber, BlockNumber, Oid, Oid, Oid, bool, bool)buffer-flush-start(Oid, Oid, Oid)
buffer-flush-done(Oid, Oid, Oid)
buffer-sync-start(int, int)
buffer-sync-done(int, int, int)
buffer-write-dirty-start(ForkNumber, BlockNumber, Oid, Oid, Oid)
buffer-write-dirty-done(ForkNumber, BlockNumber, Oid, Oid, Oid)
checkpoint-done(int, int, int, int, int)
smgr-md-read-start(ForkNumber, BlockNumber, Oid, Oid, Oid)
smgr-md-read-done(ForkNumber, BlockNumber, Oid, Oid, Oid, const char \*, int, int)
smgr-md-write-start(ForkNumber, BlockNumber, Oid, Oid, Oid)
smgr-md-write-done(ForkNumber, BlockNumber, Oid, Oid, Oid, const char \*, int, int)
xlog-insert(unsigned char, unsigned char)

Documentation should be available in 8.4 doc page soon, but if you don't want to wait, check out the doc patch I recently submitted. If you're using or plan to use the probes, I'd love to hear your feedback, both positive and constructive!

Special thanks to Theo Schlossnagle, Robert Treat, Zdenek Kotala, Alvaro Herrera and Simon Riggs for their contributions with the probes as well as reviewing them.

Wednesday Nov 19, 2008

Test driving Hyperic HQ 4.x

Hyperic just released Hyperic HQ 4.x, and I recently took it for a test drive on OpenSolaris 2008.05. The installation went smoothly, but I ran into one issue when starting the server. With a 64-bit kernel, the startup script assumes you have 64-bit JRE on the system. It my case, the 64-bit JRE wasn't available, so the server startup failed. I could have installed the 64 bit version, but I decide to just comment out the check in the startup script ( to use the 32-bit version. Below are the details of the installation steps.

Before running the Hyperic HQ install script, setup the database first. Below are the commands I used for PostgreSQL. Run psql, and then run the following commands:

postgres=# create role admin with login createdb password 'hqadmin';
postgres=# CREATE DATABASE "HQ" OWNER admin;

Now, run the install script (assuming you're unpacked the download in /var/tmp) and note the answers I entered in bold.

-bash-3.2# /var/tmp/hyperic-hq-installer/
Initializing Hyperic HQ 4.0.1 Installation...
Loading taskdefs...
Taskdefs loaded
Choose which software to install:
1: Hyperic HQ Server
2: Hyperic HQ Agent
You may enter multiple choices, separated by commas.
HQ server installation path [default '/home/hyperic']:
	1: Oracle 9i/10g
	2: PostgreSQL
	3: MySQL 5.x
What backend database should the HQ server use? [default '1']:
Enter the JDBC connection URL for the PostgreSQL database [default 'jdbc:postgresql://localhost:5432/HQ?protocolVersion=2']: Enter

Enter the username to use to connect to the database:
Enter the password to use to connect to the database: enter password here
(again): enter password again
HQ agent installation path [default '/usr/local/hyperic']: Enter

Loading install configuration...
Install configuration loaded.
Preparing to install...
Validating agent install configuration...
Validating server install configuration...
Checking server webapp port...
Checking server secure webapp port...
Checking server JRMP port...
Checking server JNP port...
Checking database permissions...
Verifying admin user properties
Validating server DB configuration...
Installing the agent...
Looking for previous installation
Unpacking agent to: /usr/local/hyperic/agent-4.0.1...
Setting permissions on agent binaries...
Fixing line endings on text files...
Installation Complete:
  Agent successfully installed to: /usr/local/hyperic/agent-4.0.1

 You can now start your HQ agent by running this command:

  /usr/local/hyperic/agent-4.0.1/bin/ start

Installing the server...
Unpacking server to: /usr/local/hyperic/server-4.0.1...
Creating server configuration files...
Copying binaries and libraries to server installation...
Copying server configuration file...
Copying server control file...
Copying server binaries...
Copying server libs...
Setting up server database...
Setting permissions on server binaries...
Fixing line endings on text files...
Installation Complete:
  Server successfully installed to: /usr/local/hyperic/server-4.0.1

 You can now start your HQ server by running this command:

  /usr/local/hyperic/server-4.0.1/bin/ start

 Note that the first time the HQ server starts up it may take several minutes
 to initialize.  Subsequent startups will be much faster.

 Once the HQ server reports that it has successfully started, you can log in
 to your HQ server at: 

  username: hqadmin
  password: hqadmin

 To change your password, log in to the HQ server, click the "Administration"
 link, choose "List Users", then click on the "hqadmin" user.

Setup completed.
A copy of the output shown above has been saved to:

At this point the installation is completed, and the server can be started with the following line, but as you can see HQ failed to start.

-bash-3.2# /usr/local/hyperic/server-4.0.1/bin/ start
Starting HQ server...
Initializing HQ server configuration...
Checking jboss jndi port...
Checking jboss mbean port...
Setting -d64 JAVA OPTION to enable SunOS 64-bit JRE
Booting the HQ server (Using JAVA_OPTS=-XX:MaxPermSize=192m -Xmx512m -Xms512m -d64)...
HQ failed to start
The log file /usr/local/hyperic/server-4.0.1/logs/server.out may contain further details on why it failed to start.

Looking at the script, I notice that it checks if you're running a 64-bit kernel. If so, it uses the 64-bit JRE. Since I only have 32-bit JRE on my system, I commented out the following section in

#if [ $THISOS = "SunOS" ] ; then
#       ARCH=`isainfo -kv`
#       case $ARCH in
#               \*64-bit\*)
#                 echo "Setting -d64 JAVA OPTION to enable SunOS 64-bit JRE"
#                       HQ_JAVA_OPTS="${HQ_JAVA_OPTS} -d64"
#                       ;;
#       esac

And rerunning the script, the server started up properly.

-bash-3.2# /usr/local/hyperic/server-4.0.1/bin/ start
Starting HQ server...
Removing stale pid file /usr/local/hyperic/server-4.0.1/logs/
Initializing HQ server configuration...
Checking jboss jndi port...
Checking jboss mbean port...
Booting the HQ server (Using JAVA_OPTS=-XX:MaxPermSize=192m -Xmx512m -Xms512m)...
HQ server booted.
Login to HQ at:

At this point you can just hit the server by pointing the browser to, and you should see the Portal Dashboard.

This is just a quick cheatsheet to get you started. The official installation/configuration instructions are available on Hyperic website.

Friday Oct 31, 2008

OFBiz and PostgreSQL

There are a quite number of Open Source ERP apps out there, but only a few (Compiere/Adempiere, OpenBravo, xTuple, OFBiz) seem to have gained traction. Recently, I tried out OFBiz and getting it to work with PostgreSQL was quite simple. OFBiz is actually shipped with an embedded Java DB (Derby), so for testing or development, you probably don't need an external DB.

I did my test on OpenSolaris 2008.05, so if you use a different version of Solaris or a different OS, the instructions below may vary.

First, check out the source, compile, and run with the embedded database to make sure everything works properly.

  1. Create a directory where you want OFBiz to reside, and run "svn co ofbiz"
  2. Go to the OFBiz directory (now referred to as $OFBIZDIR), and run "./ant run-install"
  3. In $OFBIZDIR, run "java -jar ofbiz.jar"
  4. Once OFBiz is started, check out the sample app by pointing the browser to http://localhost:8080/ecommerce/

Once your have it working with Derby, follow the steps below to switch to PostgreSQL. I use version 8.3.x.

  1. Assuming you have Postgres installed and ready to go, run:

    postgres $ createuser -S -D -R -P -E ofbiz
    postgres $ createdb -O ofbiz ofbizdb

  2. Edit the OFBiz DB config file in $OFBIZDIR/framework/entity/config/entityengine.xml

    <delegator name="default" entity-model-reader="main" entity-group-reader="main" entity-eca-reader="main" distributed-cache-clear-enabled="false">
    <group-map group-name="org.ofbiz" datasource-name="localpostnew"/>

    <datasource name="localpostnew"

  3. Copy PostgreSQL JDBC driver to $OFBIZDIR/framework/entity/lib/jdbc. Make sure to use the JDBC4 driver.
  4. Go to $OFBIZDIR, and run "./ant run-install" to populate the data in PostgreSQL
  5. Re/start OFBiz by running "java -jar ofbiz.jar"
  6. Once OFBiz is up and running, you should be able to access http://localhost:8080/ecommerce/

That's all there is to it.

Jignesh has a post on setting up OpenBravo with Postgres on OpenSolaris 2008.05.

Tuesday Sep 30, 2008

Setting up MediaWiki with PostgreSQL 8.3

Setting up MediaWiki (I use version 1.13.1) with PostgreSQL 8.3 is quite straightforward. Thanks to Greg Mullane for fixing the issues.

Since tsearch2 is integrated into core in 8.3, you only need to run the following three commands before running the MediaWiki install script.

$ createuser -S -D -R -P -E wikiuser
$ createdb -O wikiuser wikidb
$ createlang plpgsql wikidb

With 8.2, you have to run the following commands in addition to the above.

$ psql wikidb < /usr/postgres/8.2/share/contrib/tsearch2.sql (path for PostgreSQL on Solaris)
$ psql -d wikidb -c "grant select on pg_ts_cfg to wikiuser;"
$ psql -d wikidb -c "grant select on pg_ts_cfgmap to wikiuser;"
$ psql -d wikidb -c "grant select on pg_ts_dict to wikiuser;"
$ psql -d wikidb -c "grant select on pg_ts_parser to wikiuser;"
$ psql -d wikidb -c "update pg_ts_cfg set locale = current_setting('lc_collate') where ts_name = 'default';"

If you need to upgrade to PostgreSQL 8.3 from an older version of PostgreSQL, you may find the following links useful.

Wednesday Aug 20, 2008

Setting up Drupal with PostgreSQL on OpenSolaris 2008.05

Drupal is quickly gaining popularity among the many Open Source Content Management Systems (CMS) out there. AFAIK, Drupal is mostly used with MySQL on Linux, so I wanted to find out myself how easy (or hard) it is to get it working with PostgreSQL on OpenSolaris. Below were the steps I followed:

  1. Install OpenSolaris 2008.05. The installation from the Live CD went very smoothly, much simpler than previous versions of Solaris. Great job to the installer team! If you don't have the CD, you can download it from Remember, OpenSolaris comes with the bare minimum, so additional software need to be installed from the network repository.

  2. Install Web Stack (Apache, PHP, MySQL, etc). After the OS installation, I fired up Firefox and just followed the instructions from the Welcome Page, clicked on "the world of OpenSolaris" link and that took me to From there, I followed these links: "Web Stack Getting Started Guide" > "2. Getting Started" > "Installing Web Stack Components" > "Setting Up Your AMP Development Environment". I decided to install everything using the following command.

  3. # pkg install amp-dev

  4. Initialize the Web Stack Environment & Start Apache. To do this, I just followed the Web Stack Getting Started Guide. Clicked on Applications > Developer Tools > Web Stack Getting Start Guide, and did the followings:
  5. Initialize the environment: Clicked on Applications > Developer Tools > Web Stack Initialize

    (The guide is a bit confusing, so run the following commands first before starting Apache/MySQL; otherwise you'll get errors)
    # svccfg import /var/svc/manifest/network/http-apache22.xml
    # svccfg import /var/svc/manifest/application/database/mysql.xml

    Start Apache & MySQL: Clicked on Applications > Developer Tools > Web Stack Admin > Start Apache2/MySQL servers

    Point browser to http://localhost so check that Apache is running.

  6. Install PostgreSQL. I wanted to install PostgreSQL 8.3 from the network repository, but I found out that 8.3 didn't make it to OpenSolaris 2008.05 which was based on Nevada build 86. To see all the available PostgreSQL packages, run.

    # pkg search -r postgres

    The output shows a lot of packages, but unfortunately the 8.3 packages are for later builds of Nevada. I did try to install one package, but it didn't work.

    I could build Postgres 8.3 myself, by fortunately Sun also provides the binary in tarball format at, so I just used this version.

  7. Setup PostgreSQL. I wanted to run Postgres using using the "postgres" user, but it's setup as a role by default, so I did the following to change it to a normal user:

    a) As root, run "passwd -d postgres"
    b) Edit /etc/user_attr and change the type for "postgres" from role to normal
    c) Change the home directory and shell for "postgres" using the usermod command

    Now I need to create the Postgres DB cluster. Make sure the directory where Postgres is installed is added to PATH.

    postgres$ initdb -D /directory/path/to/data
  8. Install Drupal 6.4. Follow the instructions in INSTALL.txt and copy the files to /var/apache2/2.2/htdocs.

    Follow the instructions in INSTALL.pgsql.txt to setup the database. Here's what I did. Make the Postgres is running.

    postgres$ createuser --pwprompt --encrypted --no-adduser --no-createdb drupal
    Enter password for new role:
    Enter it again:
    Shall the new role be allowed to create more new roles? (y/n) y

    postgres$ createdb --encoding=UNICODE --owner=drupal drupaldb

  9. Configure Drupal. Make sure Apache is running and point the browser to http://localhost, and you should see the Drupal setup page.

  10. That's it. Besides a couple of small hiccups, I think the whole process was quite straightforward.

Monday Jul 21, 2008

BIG News for the PostgreSQL team at Sun

If you haven't heard already, Peter Eisentraut is joining Sun to work on PostgreSQL. This is very exciting, and I'm looking forward to working with Peter on some interesting projects.

Just as Peter decided to join Sun, Josh Berkus decided to leave us. Josh has made many contributions, and he will be missed. I have learned a lot from him for the past couple of years. Josh, good luck with your new endeavors.

BTW, I'm at OSCON this week. I arrived here Saturday night so I could attend PDXPug Day. I'm glad I decided to attend this mini-conference because the talks were excellent and I also got to meet some cool PostgreSQL people.

Monday Apr 28, 2008

Austin's First PostgreSQL User Group Meeting

A few of us are getting together to start a PostgreSQL User Group in Austin. If you're interested in PostgreSQL and live in the area, come join us!

Our kick off meeting will be on Tuesday, May 6th from 6-8pm. Please RSVP to (also subscribe to ) if you plan to attend so we can pre-register all visitors to save time and know how many pizza to get! BTW, pizza is FREE.

I'm pleased to be able to offer Sun's facility for this meeting. Here's the address and link to Google map.

Sun Microsystems
Building 8 - Longhorn Conference Room
5300 Riata Park Ct
Austin, TX

Thursday Apr 03, 2008

DTrace probes in PostgreSQL now work on Mac OS X Leopard

The issue with PostgreSQL's DTrace probes not working with Mac OS X Leopard as reported in the mail list has been fixed and checked into the 8.4 development tree. The problem had to do with the fact that Leopard's DTrace implementation not supporting the -G flag.

If you're curious about the gory details, check out the proposal, patch, and code commit.

With the new implementation, the steps for adding new probes are slightly different than before, but the provider and probe names remain the same. For details on how to use and add new probes, refer to the online doc.

Many thanks to Peter Eisentraut, Tom Lane, and Alvaro Herrera for their valuable feedback and assistance!

Sunday Aug 27, 2006

User-Level DTrace Probes in PostgreSQL

I'm excited to announce that PostgreSQL 8.2 now has user-level DTrace probes embedded in the source code. These probes will enable users to easily observe the behavior of PostgreSQL with simple D scripts, even in production. As you may already know, DTrace is being ported to FreeBSD and Mac OS X, so PostgreSQL users will be able to use the embedded probes on these OSes as well besides Solaris. My hope is that the presence of DTrace probes will not only help users identify one-off performance problem but will also enable developers to identify systemic performance and scalability issues on big multi-cpu/core/thread systems.

Here's the current list of available probes.
provider postgresql {
        probe transaction__start(int);
        probe transaction__commit(int);
        probe transaction__abort(int);
        probe lwlock__acquire(int, int);
        probe lwlock__release(int); 
        probe lwlock__startwait(int, int);
        probe lwlock__endwait(int, int);
        probe lwlock__condacquire(int, int);
        probe lwlock__condacquire__fail(int, int);
        probe lock__startwait(int, int);
        probe lock__endwait(int, int);

As you can see, the number of probes is small initially, but more will be added over time, and I encourage the community to identify areas in PostgreSQL where more observability is needed, for both developers and admins.

PostgreSQL runs on many operating systems, and the community is quite strict about keeping the code generic. To accomodate this, we created a higher level of abstraction whereby generic macro names are used instead of the DTrace specific macros. For example, we define the following macros PG_TRACE, PG_TRACE1, etc. which ultimately translate to DTRACE_PROBE, DTRACE_PROBE1, ... when used on system with DTrace. Doing this allow the tracing code to use generic macro names and these macros can be mapped to other tracing facilities for other operating systems.

The next few sections explains how to:
  • Compile PostgreSQL with DTrace
  • Use the existing DTrace probes
  • Add new DTrace probes

Compile PostgreSQL with DTrace

By default DTrace probes are disabled, and the user needs to explicitly tell the configure script to make the probes available in PostgreSQL. Certainly, enabling DTrace only makes sense on Operating Systems with DTrace facility. Currently DTrace is available on Solaris 10+ and soon on FreeBSD and Mac OS X.

To include DTrace probes in a 32 bit binary, specify --enable-dtrace to configure. For example:
        $ configure --enable-dtrace ...

To include DTrace probes in a 64 bit binary, specify --enable-dtrace and DTRACEFLAGS="-64" to configure. For example:

         Using gcc compiler:
        $ configure CC='gcc -m64' --enable-dtrace DTRACEFLAGS='-64' ...
         Using Sun compiler:
        $ configure CC='/path_to_sun_compiler/cc -xtarget=native64' --enable-dtrace DTRACEFLAGS='-64' ...

a) To successfully compile PostgreSQL 8.2 with --enable-dtrace, you need to run Solaris Express. The DTrace version in Solaris 10 (up until 11/06) does not allow probes to be added to static functions. This limitation will be fixed in the next update of Solaris 10.
b) When using DTRACEFLAGS='-64', you also have to tell the compiler to build 64 bit binary as shown in the configure lines above; otherwise, you will get compilation errors.

Use Existing DTrace Probes

Using the probes in PostgreSQL is similar to using probes in other DTrace providers. Below is an example of a simple D script using the transaction-start, transaction-commit, and transaction-abort probes. The script prints out the total number of started, committed, and aborted transactions.

#!/usr/sbin/dtrace -qs 

        @start["Start"] = count();
        self->ts  = timestamp;

        @abort["Abort"] = count();

        @commit["Commit"] = count();
        @time["Total time (ns)"] = sum(timestamp - self->ts);

Executing the above script produces the following output.

# ./txn_count.d `pgrep -n postgres`

  Start                                         71
  Commit                                   70
  Total time (ns)                        2312105013

A number of sample D scripts are available from the DTrace's PgFoundry project

To learn more about DTrace, refer to the HowTo and DTrace Guides.

Add New DTrace Probes

New DTrace probes can easily be added to PostgreSQL. For example, if you were to add transaction-start probe, follow these simple steps:

1) Add the probe definitions to src/backend/utils/probes.d
   provider postgresql {
        probe transaction__start(int);

When a dash (-) is used in the probe name, it needs to be converted to double underscores (__) in the probe definition file. So, the above probe is called transaction-start in the D script.

2) Add "PG_TRACE1 (transaction__start, s->transactionId);" to backend/access/transam/xact.c
   static void

         \* generate a new transaction id
        s->transactionId = GetNewTransactionId(false);


        PG_TRACE1 (transaction__start, s->transactionId);


a) PG_TRACE1 is mapped to DTRACE_PROBE1. See src/include/pg_trace.h
b) The provider name for all probes in PostgreSQL is called postgresql per the decision by the developers, so it's not specified in PG_TRACE. See src/include/pg_trace.h.
c) Make sure the data types in the probe definition match the argument passed to the probe. In this case s->transactionId has to be an integer (int).

When you have probes that might be useful to the community at large, send a proposal/patch to to get feedback from the developers.

3) Check to make sure the new probe is available

After recompiling, run the new binary, and as root, execute the following DTrace command to check that your newly added probe is available.

# dtrace -l -n transaction-start

More details which led to DTrace inclusion in PostgreSQL

1) Proposal submitted to the developer community.
2) Presented the proposal at the PostgreSQL Anniversary Summit (first developers conference). The timing of the conference was perfect, and I was fortunate to have the opportunity to present the proposal and demo'ed DTrace to a live audience. I think the discussion with the developers/hackers after the conference was key in solidifying the proposed implementation and getting the thumbs up for inclusion into 8.2.
3) Patch submitted and follow-on discussions
4) Patch was updated by Peter Eisentraut
5) Patch was finally committed by Peter Eisentraut


Many people from the community and at Sun provided excellent feedback on the proposal and implementation, but without the help for the following individuals, it would not have been possible to get DTrace into PostgreSQL.

Gavin Sherry - Gavin was the first person in the community to help us identify locations in PostgreSQL to insert probes. We initially wanted to create a demo, and with a short notice, Gavin made himself available to talk with us.

Tom Lane - At the PostgerSQL Anniverary Summit, Tom helped verify the probe locations and corrected a few of them and provide excellent feedback on how the framework should be implemented.

Peter Eisentraut - Peter stepped up to help incorporate DTrace into PostgreSQL build system. His help was invaluable in getting DTrace in 8.2 before code freeze.

Angelo Rajadurai - Angelo is a DTrace guru in MDE, and he was a great help in getting me up to speed with adding user-level probes and DTrace in general.

Adam Leventhal and Bryan Cantrill (The DTrace creators) - For making themselves available to answer questions and provide feedback.




« June 2016