X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 385109FB33E for ; Wed, 30 Aug 2006 15:53:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 63644-05 for ; Wed, 30 Aug 2006 15:53:43 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id 4888C9FB323 for ; Wed, 30 Aug 2006 15:53:42 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id m3so286521ugc for ; Wed, 30 Aug 2006 11:53:41 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=iXEGr2Z0AVPB3nioOgaGN0145QRfrgCAyTbwWDGSJuDjzz3gHJWZyhxZS7D+Z+R16PSMLTvgxZec7Iqnl29D7Sq+DrdFtbquvdeIjtyyd5798e9+z7cVIMd9Ve+CtogMIHWHIHMyPREaoRHIPRuNZ9UvKUjmimi8yRlRy6lGfOY= Received: by 10.66.219.11 with SMTP id r11mr583906ugg; Wed, 30 Aug 2006 11:53:41 -0700 (PDT) Received: by 10.67.31.16 with HTTP; Wed, 30 Aug 2006 11:53:41 -0700 (PDT) Message-ID: <8d04ce990608301153t29f414a0r9f6a42d0cfcfade5@mail.gmail.com> Date: Wed, 30 Aug 2006 11:53:41 -0700 From: "Junaili Lie" To: "Jignesh K. Shah" Subject: Re: slow i/o Cc: pgsql-performance@postgresql.org In-Reply-To: <44F5D61E.6090103@sun.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_48038_1487039.1156964021224" References: <8d04ce990608281606r153ed796xcf5d9d3a44d8e632@mail.gmail.com> <44F45900.4000702@sun.com> <8d04ce990608291056la088194n84280509528a909d@mail.gmail.com> <44F5D61E.6090103@sun.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.337 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/484 X-Sequence-Number: 20669 ------=_Part_48038_1487039.1156964021224 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I have tried this to no avail. I have also tried changing the bg_writer_delay parameter to 10. The spike in i/o still occurs although not in a consistent basis and it is only happening for a few seconds. On 8/30/06, Jignesh K. Shah wrote: > > The bgwriter parameters changed in 8.1 > > Try > > bgwriter_lru_maxpages=0 > bgwriter_lru_percent=0 > > to turn off bgwriter and see if there is any change. > > -Jignesh > > > Junaili Lie wrote: > > Hi Jignesh, > > Thank you for my reply. > > I have the setting just like what you described: > > > > wal_sync_method = fsync > > wal_buffers = 128 > > checkpoint_segments = 128 > > bgwriter_all_percent = 0 > > bgwriter_maxpages = 0 > > > > > > I ran the dtrace script and found the following: > > During the i/o busy time, there are postgres processes that has very > > high BYTES count. During that non i/o busy time, this same process > > doesn't do a lot of i/o activity. I checked the pg_stat_activity but > > couldn't found this process. Doing ps revealed that this process is > > started at the same time since the postgres started, which leads me to > > believe that it maybe background writer or some other internal process. > > This process are not autovacuum because it doesn't disappear when I > > tried turning autovacuum off. > > Except for the ones mentioned above, I didn't modify the other > > background setting: > > MONSOON=# show bgwriter_delay ; > > bgwriter_delay > > ---------------- > > 200 > > (1 row) > > > > MONSOON=# show bgwriter_lru_maxpages ; bgwriter_lru_maxpages > > ----------------------- > > 5 > > (1 row) > > > > MONSOON=# show bgwriter_lru_percent ; > > bgwriter_lru_percent > > ---------------------- > > 1 > > (1 row) > > > > This i/o spike only happens at minute 1 and minute 6 (ie. 10.51, 10.56) > > . If I do select * from pg_stat_activity during this time, I will see a > > lot of write queries waiting to be processed. After a few seconds, > > everything seems to be gone. All writes that are not happening at the > > time of this i/o jump are being processed very fast, thus do not show on > > pg_stat_activity. > > > > Thanks in advance for the reply, > > Best, > > > > J > > > > On 8/29/06, *Jignesh K. Shah* > > wrote: > > > > Also to answer your real question: > > > > DTrace On Solaris 10: > > > > # dtrace -s /usr/demo/dtrace/whoio.d > > > > It will tell you the pids doing the io activity and on which > devices. > > There are more scripts in that directory like iosnoop.d, iotime.d > > and others which also will give > > other details like file accessed, time it took for the io etc. > > > > Hope this helps. > > > > Regards, > > Jignesh > > > > > > Junaili Lie wrote: > > > Hi everyone, > > > We have a postgresql 8.1 installed on Solaris 10. It is running > fine. > > > However, for the past couple days, we have seen the i/o reports > > > indicating that the i/o is busy most of the time. Before this, we > > only > > > saw i/o being busy occasionally (very rare). So far, there has > > been no > > > performance complaints by customers, and the slow query reports > > doesn't > > > indicate anything out of the ordinary. > > > There's no code changes on the applications layer and no database > > > configuration changes. > > > I am wondering if there's a tool out there on Solaris to tell > which > > > process is doing most of the i/o activity? > > > Thank you in advance. > > > > > > J > > > > > > > > ------=_Part_48038_1487039.1156964021224 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
I have tried this to no avail.
I have also tried changing the bg_writer_delay parameter to 10. The spike in i/o still occurs although not in a consistent basis and it is only happening for a few seconds.
 


 
On 8/30/06, Jignesh K. Shah <J.K.Shah@sun.com> wrote:
The bgwriter parameters changed in 8.1

Try

bgwriter_lru_maxpages=0
bgwriter_lru_percent=0

to turn off bgwriter and see if there is any change.

-Jignesh


Junaili Lie wrote:
> Hi Jignesh,
> Thank you for my reply.
> I have the setting just like what you described:
>
> wal_sync_method = fsync
> wal_buffers = 128
> checkpoint_segments = 128
> bgwriter_all_percent = 0
> bgwriter_maxpages = 0
>
>
> I ran the dtrace script and found the following:
> During the i/o busy time, there are postgres processes that has very
> high BYTES count. During that non i/o busy time, this same process
> doesn't do a lot of i/o activity. I checked the pg_stat_activity but
> couldn't found this process. Doing ps revealed that this process is
> started at the same time since the postgres started, which leads me to
> believe that it maybe background writer or some other internal process.
> This process are not autovacuum because it doesn't disappear when I
> tried turning autovacuum off.
> Except for the ones mentioned above, I didn't modify the other
> background setting:
> MONSOON=# show bgwriter_delay ;
>  bgwriter_delay
> ----------------
>  200
> (1 row)
>
> MONSOON=# show bgwriter_lru_maxpages ;  bgwriter_lru_maxpages
> -----------------------
>  5
> (1 row)
>
> MONSOON=# show bgwriter_lru_percent ;
>  bgwriter_lru_percent
> ----------------------
>  1
> (1 row)
>
> This i/o spike only happens at minute 1 and minute 6 (ie. 10.51, 10.56 )
> . If I do select * from pg_stat_activity during this time, I will see a
> lot of write queries waiting to be processed. After a few seconds,
> everything seems to be gone. All writes that are not happening at the
> time of this i/o jump are being processed very fast, thus do not show on
> pg_stat_activity.
>
> Thanks in advance for the reply,
> Best,
>
> J
>
> On 8/29/06, *Jignesh K. Shah* < J.K.Shah@sun.com
> <mailto:J.K.Shah@sun.com>> wrote:
>
>     Also to answer your real question:
>
>     DTrace On Solaris 10:
>
>     # dtrace -s /usr/demo/dtrace/whoio.d
>
>     It will tell you the pids doing the io activity and  on which devices.
>     There are more scripts in that directory like iosnoop.d, iotime.d
>     and others which also will give
>     other details like file accessed, time it took for the io etc.
>
>     Hope this helps.
>
>     Regards,
>     Jignesh
>
>
>     Junaili Lie wrote:
>      > Hi everyone,
>      > We have a postgresql 8.1 installed on Solaris 10. It is running fine.
>      > However, for the past couple days, we have seen the i/o reports
>      > indicating that the i/o is busy most of the time. Before this, we
>     only
>      > saw i/o being busy occasionally (very rare). So far, there has
>     been no
>      > performance complaints by customers, and the slow query reports
>     doesn't
>      > indicate anything out of the ordinary.
>      > There's no code changes on the applications layer and no database
>      > configuration changes.
>      > I am wondering if there's a tool out there on Solaris to tell which
>      > process is doing most of the i/o activity?
>      > Thank you in advance.
>      >
>      > J
>      >
>
>

------=_Part_48038_1487039.1156964021224--