From: Tom Lane <tgl@sss.pgh.pa.us>
To: "mal.oracledba" <mal.oracledba@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Newbie performance problem - semop taking most of time ?
In-reply-to: <1348429359621-5725062.post@n5.nabble.com>
References: <1348058099977-5724566.post@n5.nabble.com>
	<CAAZKuFZSf00V6O7JSmn5=okss7TY9i-nhV0v08amdd3naLH7fQ@mail.gmail.com>
	<1348429359621-5725062.post@n5.nabble.com>
Comments: In-reply-to "mal.oracledba" <mal.oracledba@gmail.com>
	message dated "Sun, 23 Sep 2012 12:42:39 -0700"
Date: Sun, 23 Sep 2012 16:36:48 -0400
Message-ID: <15299.1348432608@sss.pgh.pa.us>

"mal.oracledba" <mal.oracledba@gmail.com> writes:
> A snapshot of 'perf' output for one of the users below. It doesnt change
> much from that profile.

You might want to look into whether you could use int or bigint instead
of numeric for your indexed columns.  That trace is showing 4.5% of
runtime directly blamable on cmp_numerics plus its subroutine
cmp_abs_common, and I'll bet that a noticeable fraction of the
AllocSetAlloc, memcpy, and malloc traffic is attributable to numeric
operations too.  You could probably not expect to get much more than 5%
savings, but still, if you don't need fractions then that's 5% that's
just being wasted.

The bigger picture here though is a lot of context swaps, and I wonder
how much of that is blamable on your having activated commit_delay
(especially with such silly settings as you chose ... more is not
better there).

The fact that XLogInsert shows up high on the profile might indicate
that contention for WALInsertLock is a factor, though it's hardly proof
of that.  If that's the main problem there's not too much that can be
done about it in the near term.  (There's some work going on to reduce
contention for that lock, but it's not done yet, let alone in 9.2.)
In a real application you could possibly reduce the problem by
rearranging operations, but that would be cheating of course for a
benchmark.

			regards, tom lane