Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 3FC929FC0FE for ; Thu, 28 Jun 2007 03:54:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 64842-04 for ; Thu, 28 Jun 2007 03:54:32 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.226]) by postgresql.org (Postfix) with ESMTP id A410D9FC0ED for ; Thu, 28 Jun 2007 03:54:33 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 68so364414wri for ; Wed, 27 Jun 2007 23:54:32 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=GQOw+eSC2deQquzZdvXpyUnL/JjFNjA3Csy67vZNEJazr7Iz41YL9vhDJdWKw+WPjaC0QzeMNEGU6PxBSOkGoUqOzwvOTPmeV+dE0VKXSwa6JJnCv/g8RfqIy9rAMAw24js4Xujjd9FgfPk4EwB06pD2yPvC3QsgS7S8h/DKk74= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=m58/rtR4QDJaTEjtNEAfrGybQbPxRPjJ7pojMTwr+laXPt6QSbQ/PRHZcGhDgh6vpb+RhrTAqovfPyUHkFnmfUkc1NQWfVMDVPt3rAJUOjmk1ILAnMcFN12Es8fd1QQr72ReaJ5rPCNxugqaXCdRnPRdPSSdoxgho+orTS7A5LY= Received: by 10.142.116.14 with SMTP id o14mr86406wfc.1183013671866; Wed, 27 Jun 2007 23:54:31 -0700 (PDT) Received: by 10.143.30.13 with HTTP; Wed, 27 Jun 2007 23:54:31 -0700 (PDT) Message-ID: <97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com> Date: Thu, 28 Jun 2007 14:54:31 +0800 From: "Ho Fat Tsang" To: pgsql-performance@postgresql.org Subject: PostgreSQL 8.0 occasionally slow down MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_37815_32915371.1183013671836" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.242 tagged_above=0 required=5 tests=BAYES_05=-1.11, HTML_10_20=1.351, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200706/618 X-Sequence-Number: 25651 ------=_Part_37815_32915371.1183013671836 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 : ============================================================================ LOG: duration: 1494.109 ms statement: commit;begin; LOG: duration: 1048.429 ms statement: commit;begin; LOG: duration: 1580.120 ms statement: commit;begin; LOG: duration: 1453.620 ms statement: commit;begin; LOG: duration: 1430.019 ms statement: commit;begin; LOG: duration: 1243.886 ms statement: commit;begin; LOG: duration: 1491.821 ms statement: commit;begin; LOG: duration: 1266.516 ms statement: commit;begin; ... ... LOG: duration: 1776.466 ms statement: commit;begin; LOG: duration: 1500.132 ms statement: commit;begin; ... ... LOG: duration: 1011.216 ms statement: commit;begin; LOG: duration: 1009.187 ms statement: commit;begin; LOG: duration: 1086.769 ms statement: commit;begin; LOG: duration: 1481.627 ms statement: commit;begin; ... ... LOG: duration: 10513.208 ms statement: commit;begin; 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 ------=_Part_37815_32915371.1183013671836 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
------=_Part_37815_32915371.1183013671836--