Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL strugling during high load

From: "Mindaugas Riauba" <mind(at)bi(dot)lt>
To: "Cosimo Streppone" <cosimo(at)streppone(dot)it>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: PostgreSQL strugling during high load
Date: 2005-05-13 15:41:43
Message-ID: 02f901c557d2$43517e10$f20214ac@bite.lt (view raw or flat)
Thread:
Lists: pgsql-performance
> >>The "vacuum cost" parameters can be adjusted to make vacuums fired
> >>by pg_autovacuum less of a burden.  I haven't got any specific numbers
> >>to suggest, but perhaps someone else does.
> >
> >   It looks like that not only vacuum causes our problems. vacuum_cost
> > seems to lower vacuum impact but we are still noticing slow queries
"storm".
> > We are logging queries that takes >2000ms to process.
> >   And there is quiet periods and then suddenly 30+ slow queries appears
in
> > log within the same second. What else could cause such behaviour?
>
> I've seen that happen when you're placing (explicitly or
> *implicitly*) locks on the records you're trying to update/delete.
>
> If you're willing to investigate, `pg_locks' system view holds
> information about db locks.

  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.

  Thanks,

  Mindaugas

          |          |   584302172 | 11836 | ExclusiveLock            | t
    17236 |    17230 |             | 11836 | AccessShareLock          | t
    17236 |    17230 |             | 11836 | RowExclusiveLock         | t
   127103 |    17230 |             | 11836 | RowExclusiveLock         | t
   127106 |    17230 |             | 11836 | RowExclusiveLock         | t
   127109 |    17230 |             | 11836 | AccessShareLock          | t
   127109 |    17230 |             | 11836 | RowExclusiveLock         | t
   127109 |    17230 |             | 11837 | AccessShareLock          | t
   127109 |    17230 |             | 11837 | RowExclusiveLock         | t
    17236 |    17230 |             | 11837 | AccessShareLock          | t
    17236 |    17230 |             | 11837 | RowExclusiveLock         | t
   127106 |    17230 |             | 11837 | RowExclusiveLock         | t
   127103 |    17230 |             | 11837 | RowExclusiveLock         | t
          |          |   584302173 | 11837 | ExclusiveLock            | t
   127103 |    17230 |             | 11838 | RowExclusiveLock         | t
    17236 |    17230 |             | 11838 | RowExclusiveLock         | t
   127109 |    17230 |             | 11838 | RowExclusiveLock         | t
          |          |   584302174 | 11838 | ExclusiveLock            | t
    17285 |    17230 |             | 11838 | AccessShareLock          | t
    17251 |    17230 |             | 11838 | AccessShareLock          | t
   130516 |    17230 |             | 11838 | AccessShareLock          | t
   127106 |    17230 |             | 11838 | RowExclusiveLock         | t
    17278 |    17230 |             | 11838 | AccessShareLock          | t


In response to

Responses

pgsql-performance by date

Next:From: Steinar H. GundersonDate: 2005-05-13 16:12:14
Subject: Re: PostgreSQL strugling during high load
Previous:From: Cosimo StrepponeDate: 2005-05-13 15:23:13
Subject: Re: PostgreSQL strugling during high load

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group