Thursday Apr 15, 2010

My Last Day and Blog Post at Sun

I am getting close to finish my 10th year at Sun Microsystems, Inc now part of Oracle America, Inc. However before I finish the year, it is time for me to move on with my new ventures.

Today is my last day in Sun/Oracle.

My new coordinates  and my LinkedIn Profile.

Stay in touch.

Friday Feb 05, 2010

Building latest PostgreSQL on OpenSolaris

I am moving my PostgreSQL on OpenSolaris realted entries to a new external blog. Since it is not part of my $dayjob anymore. Hope you update your bookmarks too.

Read  "Building latest PostgreSQL CVS Head on OpenSolaris".

Wednesday Jul 22, 2009

iGen with PostgreSQL 8.4 on Sun Fire X4140

Recently I got access to the refreshed Sun Fire X4140 consisting of 2 x 6-core Opterons with 36GB RAM. Since the release of the final PostgreSQL 8.4 bits I had not tried it out so I downloaded the Solaris 10 binaries of PostgreSQL 8.4 (64-bits) from the download site of and took it for the test drive with the same iGen benchmarks that I had used earlier for my PGCon2009 presentation.

The system already had Solaris 10 5/09 installed with couple of  SSDs  and a RAID LUN for the database. I put the WAL log on an internal drive with ZFS intent log on SSDs and the tablespaces on the RAID LUN (on an external storage array).

Notice the crossing of the 400K tpm boundary with PostgreSQL here using this benchmark toolkit. None of my tests have ever done that before. I consider this to be a milestone achievement with PostgreSQL, Solaris 10, Sun Fire Systems with Opterons.

Friday May 29, 2009

Read Only Scalability Patch

Simon Riggs of 2nd Quadrant recently submitted a patch for testing which should improve read only scalability of Postgres. I took it for a test drive for my setup. In the first set of tests I used the same benchmark as previous ones so as to have the same reference point.

It seems changing the Number of Buffer Partitions for this workload does not have any impact. My dataset for this iGen benchmark is pretty small and should easily fit under 2GB size and hence may not be stressing the buffer partitions too much to warrant bigger number. The patch still helps to get good healthy 4-6% gain in peak values.

Thursday May 28, 2009

Postgres 8.4 Testing with new JDBC Drivers

At PGCon 2009, Jesper Pedersen talked to me about the new Binary Transfer patch which was submitted to the JDBC Driver for Postgres 8.4. I thought it will be nice to compare how the JDBC 8.4 driver compared to older 8.3 JDBC Driver. Hence I took it for a drive

The 8.4 JDBC Driver with BinaryTransfer patch seems to get to a better peak faster but since to taper off at high clients. I don't know if this benchmark was the right benchmark for it. Need more benchmarks which uses JDBC to see the performance difference with this feature.

Wednesday May 27, 2009

Postgres on OpenSolaris using 2x Quad Cores: Use FX Scheduler

During my PGCon 2009 presentation there was a question on the saw tooth nature of the workload results on the high end side of benchmark runs. To which Matthew Wilcox (from Intel) commented it could be scheduler related. I did not give it much thought at that time till today when I was trying to do some iGen runs for the JDBC Binary Transfer patch (more on that in another blog post) and also Simon's read only scalability runs . Then I realized that I was not following one of my one tuning advice for running Postgres on OpenSolaris. The advice is to  use FX Class of scheduler instead of the default TS Class on OpenSolaris . More details on various scheduler classes can be found on

Now how many times I have forgotten to do that with Postgres on OpenSolaris I have no idea. But yes it is highly recommended specially on multi-core systems to use FX scheduler class for Postgres on OpenSolaris. How much gain are we talking about? The following graph will give an indication using the default TS scheduler class Vs the FX Scheduler class using the iGen benchmark.

The gain is about 14% by just switching over to FX Class. How did I get Postgres server instance to use FX class? I cheated and put all processes of the user (with userid 236177)  in FX class using the following command line.

# priocntl -s -c FX -i uid 236177

One thing to figure out is how to make sure Postgres uses FX scheduler class out of the box on OpenSolaris so I don't keep forgetting about that minute performance tip.

Friday May 22, 2009

PGCon 2009: Performance Comparison of Postgres 8.3 Vs Postgres 8.4

On the first day of PGCon 2009 I presented on my results of my testing with Postgres 8.4beta1 vs the earlier version (8.3.7). The good news is it should not cause any regressions to existing users of 8.3.7 to upgrade and exploit the opportunity to use the new features of Postgres 8.4. 

Comments/Questions welcome.

Monday May 18, 2009

Postgres 8.4 Lock Wait Statistics Tool

While working on my upcoming presentation for PGCon 2009 on Thursday, I found that sometimes it is misleading to just take one snapshot of locks to figure the hot locks in PostgreSQL workload characterization.

So again starting from one of the DTrace scripts I arrived at pglockwait_84.d

NOTE: It only works with operating systems that support DTrace. I have only tested it on OpenSolaris as of now.

It can either be used to track to summarize all PostgreSQL backends (using '\*')  or selected one using process id using 10 second interval. It also prints time so that it can be dumped into a file for post-processing analysis. 

An example output  is show below during dbt-2 runs using PostgreSQL 8.4 beta1.

# ./pglockwait_84.d '\*' 2009 May 19 02:52:14 Lock-Id Mode Wait-Time(ms) Count Dynamic Locks Exclusive 0 5 ProcArrayLock Shared 0 37 Dynamic Locks Shared 1 52 CLogControlLock Exclusive 1 85 BufFreelistLock Exclusive 1 81 CLogControlLock Shared 1 103 ProcArrayLock Exclusive 2 112 BgWriterCommLock Exclusive 10 123 BufMappingLock Exclusive 11 636 XidGenLock Exclusive 17 2 BufMappingLock Shared 34 1566 WALInsertLock Exclusive 49 2305 LockMgrLock Exclusive 65 852 2009 May 19 02:52:24 Lock-Id Mode Wait-Time(ms) Count XidGenLock Shared 0 1 XidGenLock Exclusive 0 12 ProcArrayLock Shared 1 86 BufFreelistLock Exclusive 4 240 BgWriterCommLock Exclusive 5 213 Dynamic Locks Shared 5 157 CLogControlLock Exclusive 6 238 CLogControlLock Shared 6 384 ProcArrayLock Exclusive 57 360 Dynamic Locks Exclusive 158 7 WALInsertLock Exclusive 187 7837 LockMgrLock Exclusive 226 3251 BufMappingLock Exclusive 289 2141 BufMappingLock Shared 895 5513 2009 May 19 02:52:34 Lock-Id Mode Wait-Time(ms) Count XidGenLock Shared 0 0 Dynamic Locks Exclusive 0 6 XidGenLock Exclusive 0 5 ProcArrayLock Shared 1 76 BufFreelistLock Exclusive 3 183 BgWriterCommLock Exclusive 4 118 ProcArrayLock Exclusive 5 229 Dynamic Locks Shared 5 91 CLogControlLock Exclusive 29 198 CLogControlLock Shared 62 272 BufMappingLock Exclusive 141 1685 LockMgrLock Exclusive 206 2175 WALInsertLock Exclusive 221 5540 BufMappingLock Shared 279 4180 2009 May 19 02:52:44 Lock-Id Mode Wait-Time(ms) Count XidGenLock Shared 0 0 Dynamic Locks Exclusive 0 3 XidGenLock Exclusive 0 5 ProcArrayLock Shared 0 67 BgWriterCommLock Exclusive 1 69 BufFreelistLock Exclusive 2 148 CLogControlLock Shared 3 262 CLogControlLock Exclusive 4 199 ProcArrayLock Exclusive 47 277 WALWriteLock Exclusive 64 2 BufMappingLock Exclusive 79 1599 WALInsertLock Exclusive 151 5949 LockMgrLock Exclusive 198 2377 BufMappingLock Shared 223 4345 Dynamic Locks Shared 1568 144 \^C

It throws an output every 10 second and the time spent in acquiring the locks. For the BufMappingLock, LockMgrLock and Dynamic Locks it aggregates all of them together respectively. It's bit high on system resources if you track all Postgres backends but if you already know which one then it can be low on overhead. Hope it is useful to you too as I found it for my purpose.

Friday May 15, 2009

PostgreSQL Transactions Per Second Using Dtrace

 I modified one of Robert's dtrace scripts so that it is  useful for my purpose to measure often asked transactions per second  for random workload running on PostgreSQL.

The script is as follows:

#!/usr/sbin/dtrace -qs
	@startpersec["New"] = count();
	@commitpersec[ "Commit"] = count();
	@abort["Abort"] = count();
        printf("\*\*\*\*\*\*\*\* Transactions Per Second \*\*\*\*\*\*\*\*\*\\n");
	printf("%20s %15s\\n", "Txn Type", "Count");
	printa("%20s %@15d\\n", @startpersec);
	printa("%20s %@15d\\n", @commitpersec);
	printa("%20s %@15d\\n", @abort);

UPDATE: You can also download it pgtps.d

When you execute it you see outputs every second as follows:

# ./tps.d
\*\*\*\*\*\*\*\* Transactions Per Second \*\*\*\*\*\*\*\*\*
            Txn Type           Count
                 New             192
              Commit             192
               Abort               1

\*\*\*\*\*\*\*\* Transactions Per Second \*\*\*\*\*\*\*\*\*
            Txn Type           Count
                 New             175
              Commit             172
               Abort               0

\*\*\*\*\*\*\*\* Transactions Per Second \*\*\*\*\*\*\*\*\*
            Txn Type           Count
                 New             195
              Commit             198
               Abort               0

\*\*\*\*\*\*\*\* Transactions Per Second \*\*\*\*\*\*\*\*\*
            Txn Type           Count
                 New             183
              Commit             178
               Abort               2

How to interpret the output?

  • New mentions how many transactions started per second
  • Commit talks about how many transactions commited per second.
  • Aborts talks about transactions aborted in that second

Useful specially when some one  asks a questions that they are generally reading from a questionaire like how many transactions per second are we doing?

Where is your TPS report?

Tuesday Apr 21, 2009

Try Postgres 8.4 Beta1 using OpenSolaris Appliance for VirtualBox

Postgres 8.4 Beta1 community binaries are now for OpenSolaris 2008.11. The Beta1 binaries for OpenSolaris can be downloaded from binary location. Postgres 8.4 binaries for Solaris 10 are also available.

For people who don't have OpenSolaris installed on their laptop but want to try out the new improved DTrace Probes in Postgres 8.4beta1, you can install the Minimal OpenSolaris Appliance OVF image for VirtualBox 2.2 and install the Postgres 8.4beta1 binaries in the appliance to try it out. You can also use the DTrace probes on your Mac OS X too.

Easiest way to install the binaries on the OpenSolaris Appliance is to first install SUNWwget package from the OpenSolaris repository

pkg install SUNWwget

and then using copy the download mirror url for those binaries using http and download it with wget in the appliance

wget "\\

The community binaries typically should be untarred in /opt.

bzcat postgresql-8.4beta1-opensolaris.i386-32.tar.bz2 |tar -xf -

This will then have the binaries in /opt/postgres/8.4beta1/. If you also untar the 64-bit binaries then the the 64-bit binaries are available from /opt/postgres/8.4beta1/64.

One thing that I have noticed with these binaries that it does not pick up the libraries if installed in /opt by default so depending on the type of bits you may need to set the following

LD_LIBRARY_PATH=/opt/postgres/8.4beta1/lib; export LD_LIBRARY_PATH


LD_LIBRARY_PATH_64=/opt/postgres/8.4beta1/lib/64; export LD_LIBRARY_PATH_64

Beyond that everything should work as you would expect. Well almost... One thing to also note is that the new 8.4 GUC parameter effective_io_concurrency to allow readahead for bitmap scan index scans is disabled on OpenSolaris / Solaris 10.

 If you do find something that doesn't seem to work, please feel free to leave comments.



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


« April 2014