Unpredictable shark slowdown after migrating to 8.4

From: Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Unpredictable shark slowdown after migrating to 8.4
Date: 2009-11-11 17:50:46
Message-ID: c3a7de1f0911110950l11facb7cu352c3fa078db9e3d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello community,

Second time after migration 8.3.7 --> 8.4.1 I was caught by this
problem. Migration was 8 days ago.
(note, I never seen such situation on 8.3)

Environment:

PostgreSQL 8.4.1 + patch
http://archives.postgresql.org/pgsql-committers/2009-10/msg00056.php
CentOS release 5.4 (Final)
SunFire X4600 M2; 3U; 8xOpteron 8380 2.5GHz; 96GB; 12x146GB 15K RPM
DB size ~90G TPS~1000

Symptoms:

In short period of time total query execution time increases incredibly.

Time Sum duration (ms)
17:17 12811
17:18 8870
17:19 33744
17:20 9991
17:21 13392
17:22 163928
17:23 1151709
17:24 12112797 <-- here it cuts due to connection threshold
17:25 12305390
17:26 12970853
17:27 12957648

LA changes rather insignificantly from ~6 to ~8.
CPU user time increases from ~350 to ~600 ), system from ~50 to ~250.
Neither additional significant disc activity nor iowait.
Another thing I noticed is autoanalyze finish on tables that few
minutes later were used in the first and mostly canceled by timeout
queries.

First time I assigned the blame to multiply locks on one of the
mentioned above tables. There was heavy delete. I started delete rows
manually by small batches and found a record that hung my delete for a
long time (many times greater then stmt timeout) even when I tried to
delete it by PK. Situation was saved by disabling some functional that
uses this table until next day when I got rid of the aggressive
deletes.

Second time I didn't find any reason that explains the situation.

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?

Thank you.

--
Regards,
Sergey Konoplev
--
PostgreSQL articles in english & russian
http://gray-hemp.blogspot.com/search/label/postgresql/

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2009-11-11 18:54:07 Re: Patch committers
Previous Message Peter Eisentraut 2009-11-11 17:45:25 Re: Patch committers