Re: concurrency performance degradation

I tried disabling foreign keys to test this and I didn't see a real
performance increase. Does setting pgenable in the pg_trigger table to
false disable all the foreign keys?

Is there any way to get a good trace of the locks or lock contention?

Thanks,
Sheer

On Fri, 26 Oct 2001, Stephan Szabo wrote:

> On Fri, 26 Oct 2001, Sheer El-Showk wrote:
>
> > Hi,
> >
> > I have an application that uses foreign keys rather heavily in postgres.
> > It supports multiple concurrent sessions with high transaction volumes.
> > Ideally the server should be able to have 20-30 connections open to the
> > database each partaking in similar operations. Each transaction may
> > involve 20+ writes and maybe 100 reads.
> >
> > When run single threaded, I can perform on transaction per second (30 or
> > so writes, 100 or so reads, plus time spent int he application), but if I
> > increase concurrency to about 10 active threads all doing similar
> > operations (on the same set of tables) I gain very little from it. I
> > notice in the process listing that the postges processes are almost all in
> > "INSERT WAITING..." or "UPDATE WAITING..." except for one which is in a
> > "SELECT" of an "INSERT". My confusion here is that I thought postgres
> > used record-level locking so I should be able run multipel concurrent
> > inserts on a table without locking.
> >
> > The reason I mention foreign keys above is that I imagine that foreign
> > keys might complicate locking issues and I use them a _lot_. I also
>
> If you're doing alot of inserts or updates to rows that match the same row
> in the primary key table this may be explained by the foreign key locking
> as I believe a row lock is grabbed on the primary key row to prevent
> it from being removed by another transaction.
>