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/
