Re: Unpredictable shark slowdown after migrating to 8.4 - Mailing list pgsql-hackers

On Mon, Nov 16, 2009 at 1:53 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
> On Thu, Nov 12, 2009 at 4:42 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Wed, Nov 11, 2009 at 12:50 PM, Sergey Konoplev <gray.ru@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