Re: PostgreSQL strugling during high load

От: Tom Lane
Тема: Re: PostgreSQL strugling during high load
Дата: ,
Msg-id: 24733.1116002564@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: PostgreSQL strugling during high load  ("Mindaugas Riauba")
Список: pgsql-performance

Скрыть дерево обсуждения

PostgreSQL strugling during high load  ("Mindaugas Riauba", )
 Re: PostgreSQL strugling during high load  ("Steinar H. Gunderson", )
 Re: PostgreSQL strugling during high load  (Tom Lane, )
  Re: PostgreSQL strugling during high load  (Mischa Sandberg, )
 Re: PostgreSQL strugling during high load  ("Mindaugas Riauba", )
  Re: PostgreSQL strugling during high load  (Tom Lane, )
   Re: PostgreSQL strugling during high load  (Donald Courtney, )
  Re: PostgreSQL strugling during high load  (Cosimo Streppone, )
  Re: PostgreSQL strugling during high load  ("Matthew T. O'Connor", )
   Re: PostgreSQL strugling during high load  ("Thomas F. O'Connell", )
 Re: PostgreSQL strugling during high load  ("Mindaugas Riauba", )
  Re: PostgreSQL strugling during high load  ("Steinar H. Gunderson", )
 Re: PostgreSQL strugling during high load  ("Mindaugas Riauba", )
  Re: PostgreSQL strugling during high load  (Tom Lane, )
 Re: PostgreSQL strugling during high load  ("Mindaugas Riauba", )
 Re: PostgreSQL strugling during high load  ("Anjan Dave", )
  Re: PostgreSQL strugling during high load  (Donald Courtney, )
  Re: PostgreSQL strugling during high load  (Vivek Khera, )
  Re: PostgreSQL strugling during high load  (Josh Berkus, )
   Re: PostgreSQL strugling during high load  (Steve Poe, )
 Re: PostgreSQL strugling during high load  ("Anjan Dave", )

"Mindaugas Riauba" <> 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


В списке pgsql-performance по дате сообщения:

От: John Arbash Meinel
Дата:
Сообщение: Re: Optimize complex join to use where condition before
От: David Brown
Дата:
Сообщение: Re: ok you all win what is best opteron (I dont want a