Re: Thousands of semops for every i/o

"Jeffrey W. Baker" <jwbaker@acm.org> writes:
> This is the strace of a process which is deleting rows from four tables,
> inside a transaction, one row at a time.  There are a lot of semops for
> every i/o.  There are about 30 connections to this database currently.
> I thought deletes in a transaction just flew along in Pg, because they
> simply wrote the deleted transaction ID on the record.  It used to work
> fine in my previous locally-built 7.2 on Debian, but this is 7.2.2 on
> SuSE Enterprise Server 8.2.

The first thing that comes to mind is that the thing is using SysV
semaphores as a substitute for spinlocks.  If this is on a hardware
platform that is supposed to have TAS() support in s_lock.h or s_lock.c,
then it's a configuration or build error.  If it's on some heretofore
unknown platform, you need to write some TAS() code to get decent
performance.

One way to check is to see how many semaphores Postgres has created
("ipcs -s -a" should help here).  If it's more than approx. one per
max_connection then you're using semas for spinlocks.

            regards, tom lane