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