From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mindaugas Riauba <mind@bi.lt>
Cc: Cosimo Streppone <cosimo@streppone.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL strugling during high load
Date: Fri, 13 May 2005 12:42:44 -0400
Message-ID: <24733.1116002564@sss.pgh.pa.us> (raw)
In-Reply-To: <02f901c557d2$43517e10$f20214ac@bite.lt>
References: <01cc01c557ba$a4fdb0b0$f20214ac@bite.lt>
<23217.1115991763@sss.pgh.pa.us>
<022901c557c5$73df1220$f20214ac@bite.lt>
<4284C661.4050406@streppone.it>
<02f901c557d2$43517e10$f20214ac@bite.lt>
"Mindaugas Riauba" <mind@bi.lt> writes:
> Hm. Yes. Number of locks varies quite alot (10-600). Now what to
> investigate
> further? We do not use explicit locks in our functions. We use quite simple
> update/delete where key=something;
> Some sample (select * from pg_locks order by pid) is below.
The sample doesn't show any lock issues (there are no processes waiting
for ungranted locks). The thing that typically burns people is foreign
key conflicts. In current releases, if you have a foreign key reference
then an insert in the referencing table takes an exclusive row lock on
the referenced (master) row --- which means that two inserts using the
same foreign key value block each other.
You can alleviate the issue by making all your foreign key checks
deferred, but that just shortens the period of time the lock is held.
There will be a real solution in PG 8.1, which has sharable row locks.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mind@bi.lt, cosimo@streppone.it
Subject: Re: PostgreSQL strugling during high load
In-Reply-To: <24733.1116002564@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox