Re: PostgreSQL strugling during high load
От | Mindaugas Riauba |
---|---|
Тема | Re: PostgreSQL strugling during high load |
Дата | |
Msg-id | 02f901c557d2$43517e10$f20214ac@bite.lt обсуждение исходный текст |
Ответ на | PostgreSQL strugling during high load ("Mindaugas Riauba" <mind@bi.lt>) |
Ответы |
Re: PostgreSQL strugling during high load
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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
В списке pgsql-performance по дате отправления: