Re: Unpredictable shark slowdown after migrating to 8.4

On Thu, Nov 12, 2009 at 4:42 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Nov 11, 2009 at 12:50 PM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)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