On Mon, Nov 16, 2009 at 1:53 PM, Sergey Konoplev <gray...@gmail.com> wrote: > On Thu, Nov 12, 2009 at 4:42 AM, Robert Haas <robertmh...@gmail.com> wrote: >> On Wed, Nov 11, 2009 at 12:50 PM, Sergey Konoplev <gray...@gmail.com> wrote: >>> Was this situation mentioned before and is there a solution or >>> workaround? (I didn't find any) If not please give me a glue where to >>> dig or what information should I provide? >> >> I think you should use log_min_duration_statement or auto_explain to >> figure out which queries are giving you grief. I don't believe that >> 8.4 is in general slower than 8.3, so there must be something about >> how you are using it that is making it slower for you. But without >> more information it's hard to speculate what it might be. It's also >> not entirely clear that your 8.4 setup is really the same as your 8.3 >> setup. You might have different configuration, differences in your >> tables or table data, differences in workload, etc. Without >> controlling for all those factors it's hard to draw any conclusions. > > Well I turned on log_min_duration_statement, set up auto_explain, > pg_stat_statements, pgrowlocks, pg_buffercache, wrote some monitoring > queries and started to wait when the situation repeats. > > Today it happens! Situation was absolutely the same as I described in > my previous letter. One more thing I noticed about CPU user-time this > time is that after connections count gets close to pgbouncer threshold > it decreased from ~800 to ~10 very fast. > > Log monitoring shows me that query plans were the same as usual (thanx > auto_explain). > > I reset pg_stat_statements and few minutes later did select from it. I > noticed that slowest queries was on tables with high number of updates > (but isn't it usual?). > > I tried to get locks with this queries > > SELECT > t.tablename, > (SELECT count(*) FROM pgrowlocks(schemaname || '.' || t.tablename)) AS > locks > FROM pg_tables t > WHERE schemaname = 'public' > ORDER BY 2 DESC > LIMIT 10; > > SELECT * FROM pgrowlocks('public.person_online'); > > but nothing was returned. > > Here is portions of vmstat and iostat results http://pastie.org/701326 > > This time situation was saved by PG restart to. Obviously all I > provided tells almost nothing and I'm very confused with it. So please > tell me what else could I do to get more info? > >> Also, I don't believe this is an appropriate topic for pgsql-hackers. >> If you have EXPLAIN ANALYZE results for the affected queries, try >> pgsql-performance. > > I do have but this results are good and the same as when nothing has > happened when everything is allright.
Can you show us the non-commented settings from your postgresql.conf? Can you show us what the vmstat output looks like when everything is running normally? It looks like the blocks out are pretty high, but I don't know how that compares to normal for you. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers