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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
