PostgreSQL 8.0 occasionally slow down

From: "Ho Fat Tsang" <namiwf(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: PostgreSQL 8.0 occasionally slow down
Date: 2007-06-28 06:54:31
Message-ID: 97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I am new for postgresql server. And now i work on a projects which
requires postgreSQL 8.0 and Java. I don't know why the server occasionally
slow down a bit for every 3 minutes.
I have changed the log configuration so that it logs all statement
transaction > 1000 ms and the result shown below :

============================================================================
<elf2 2007-06-28 14:30:25 HKT 46835574.7a64> LOG: duration: 1494.109 ms
statement: commit;begin;
<elf2 2007-06-28 14:33:34 HKT 468354a8.7415> LOG: duration: 1048.429 ms
statement: commit;begin;
<elf2 2007-06-28 14:33:35 HKT 468354a9.7418> LOG: duration: 1580.120 ms
statement: commit;begin;
<elf2 2007-06-28 14:33:37 HKT 468354a9.7418> LOG: duration: 1453.620 ms
statement: commit;begin;
<elf2 2007-06-28 14:36:51 HKT 468354a9.7419> LOG: duration: 1430.019 ms
statement: commit;begin;
<elf2 2007-06-28 14:36:53 HKT 468354a9.7418> LOG: duration: 1243.886 ms
statement: commit;begin;
<elf2 2007-06-28 14:36:54 HKT 468354a9.7419> LOG: duration: 1491.821 ms
statement: commit;begin;
<elf2 2007-06-28 14:36:54 HKT 468354a9.7418> LOG: duration: 1266.516 ms
statement: commit;begin;
...
...
<elf2 2007-06-28 14:40:54 HKT 468354a9.741b> LOG: duration: 1776.466 ms
statement: commit;begin;
<elf2 2007-06-28 14:40:54 HKT 468357ec.d5a> LOG: duration: 1500.132 ms
statement: commit;begin;
...
...
<elf2 2007-06-28 14:44:07 HKT 46835477.73b7> LOG: duration: 1011.216 ms
statement: commit;begin;
<elf2 2007-06-28 14:44:12 HKT 46835477.73b7> LOG: duration: 1009.187 ms
statement: commit;begin;
<elf2 2007-06-28 14:44:13 HKT 468352f9.7194> LOG: duration: 1086.769 ms
statement: commit;begin;
<elf2 2007-06-28 14:44:14 HKT 46835477.73b7> LOG: duration: 1481.627 ms
statement: commit;begin;
...
...
<elf2 2007-06-28 14:47:44 HKT 468354a9.7419> LOG: duration: 10513.208 ms
statement: commit;begin;
<elf2 2007-06-28 14:48:22 HKT 468354a9.7419> LOG: duration: 38126.708 ms
statement: commit;begin;

============================================================================

For each 3 ~ 4 minutes , there are many transactions which requires (>1
seconds) for execution. It is strange for me seems the tables size is quite
small (~ 10K < 20K row). I can said the rate of incoming transactions is
quite steady through our the testing. So i am quite confusing why the
performance degrades for every 3 ~ 4 minutes. I am wondering if there is any
default scheduled task in the postgreSQL 8.0

The configurations which i have amended in postgresql.conf.

max_fsm_pages = 100000
vacuum_cost_delay = 10

The machine using :
512 RAM
Gentoo Linux

Do anyone can help me about this ? or any resolution for a sudden
performance degrade ( because the application i need to develop is quite
time-critical).

Thank.
Twinsen

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Huxton 2007-06-28 07:02:44 Re: update query taking too long
Previous Message Chris 2007-06-28 06:49:58 Re: update query taking too long