Re: slow i/o

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(dot)K(dot)Shah(at)sun(dot)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
> >
>