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. -- Regards, Sergey Konoplev -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers