Thread: Regular disk activity of an idle DBMS

Regular disk activity of an idle DBMS

From
Andrej Podzimek
Date:
Hello,

after configuring a new home server with PostgreSQL 9.0.4, I observe some regular disk activity, even though the server
iscompletely idle (disconnected from the network, no users but one logged in). There are very short write bursts once
inabout 3 seconds. This does not affect performance in any way, but I would like to know whether this is normal.
PerhapsI misconfigured something. With the 8.4.x version I used before, there were no such regular disk writes. 

I used the following approach to detect who is writing to disk:
http://www.xaprb.com/blog/2009/08/23/how-to-find-per-process-io-statistics-on-linux/This is what I obtained after about
3minutes of observation: 

    TASK                   PID      TOTAL       READ      WRITE      DIRTY DEVICES
    postgres             10437      10736          0      10736          0 dm-2
    md127_raid5            630        648          0        648          0 sdc, sda, sdb, sdd
    flush-253:3          29302        553          0        553          0 dm-3
    jbd2/dm-2-8           3411         62          0         62          0 dm-2
    flush-253:2           3835         35          0         35          0 dm-2
    jbd2/dm-3-8           3413         20          0         20          0 dm-3
    jbd2/dm-1-8           3409         12          0         12          0 dm-1
    flush-253:1            465         11          0         11          0 dm-1
    postgres             10434          9          0          9          0 dm-2
    jbd2/dm-5-8            789          6          0          6          0 dm-5
    postgres               850          4          0          4          0 dm-2
    bash                   400          4          0          4          0 dm-5
    flush-253:5            398          4          0          4          0 dm-5

These are my (non-default) PostgreSQL settings:

    # grep -Pv '^[ \t]*#|^[ \t]*$' /var/lib/postgres/data/postgresql.conf
    listen_addresses =
'::1,2002:53f0:5de8::1,2002:53f0:5de8:1::1,2002:53f0:5de8:2::1,2002:53f0:5de8:3::1,127.0.0.1,83.240.93.232,10.0.1.1,10.0.2.1,10.0.3.1'
    max_connections = 128                   # (change requires restart)
    ssl = on                                # (change requires restart)
    shared_buffers = 512MB                  # min 128kB
    temp_buffers = 64MB                     # min 800kB
    max_prepared_transactions = 128         # zero disables the feature
    work_mem = 16MB                         # min 64kB
    maintenance_work_mem = 128MB            # min 1MB
    max_stack_depth = 16MB                  # min 100kB
    effective_io_concurrency = 3            # 1-1000. 0 disables prefetching
    checkpoint_segments = 16                # in logfile segments, min 1, 16MB each
    log_destination = 'syslog'              # Valid values are combinations of
    autovacuum_max_workers = 8              # max number of autovacuum subprocesses
    datestyle = 'iso, dmy'
    lc_messages = 'cs_CZ.UTF-8'                     # locale for system error message
    lc_monetary = 'cs_CZ.UTF-8'                     # locale for monetary formatting
    lc_numeric = 'cs_CZ.UTF-8'                      # locale for number formatting
    lc_time = 'cs_CZ.UTF-8'                         # locale for time formatting
    default_text_search_config = 'pg_catalog.cs'

The machine runs ArchLinux. It is a standard piece of x86_64 commodity hardware. There are four SATA drives configured
asa RAID5 array. The file system is ext4. 

Is there an easy way to detect what exactly causes PostgreSQL to write these small amounts of data on an idle machine
ona regular basis? Stopping all daemons that connect to PostgreSQL (OpenFire, Apache, Courier-MTA) does not change
anything.Any hints would be very helpful. There is actually no performance or usability issue. I just want to
*understand*what is going on. 

Andrej


Attachment

Re: Regular disk activity of an idle DBMS

From
Greg Smith
Date:
On 05/28/2011 11:02 AM, Andrej Podzimek wrote:
> after configuring a new home server with PostgreSQL 9.0.4, I observe
> some regular disk activity, even though the server is completely idle
> (disconnected from the network, no users but one logged in). There are
> very short write bursts once in about 3 seconds.

There are a couple of things that can cause unexpected disk activity:

-autovacuum running in the background.  Setting
log_autovacuum_min_duration  may help you determine when this is happening.
-checkpoint activity.  Turning on log_checkpoints, as well as looking
for changes in the pg_stat_bgwriter view, may help explain if this is
the case.
-Hint bit updates.  Even if you are only reading from a table, in some
situations write activity can be generated.  See
http://wiki.postgresql.org/wiki/Hint_Bits for more information.
-Statistics collector updates.  If the one logged in user is doing
anything at all, they might be generating something here.

Figuring out if the writes are happening from a regular PostgreSQL
process, or if they are happening via the background writer, might also
be useful here.  Saving the output from "top -b -c" can be useful for
this.  The iotop command is very helpful for tracking down this sort of
problem too.  The background writer process, which also handles
checkpoints, will have the same process ID once it's started.  So will
the statistics collector.  If you track I/O to one of those two, it
should narrow possible causes quite a bit.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


Re: Regular disk activity of an idle DBMS

From
Andrej Podzimek
Date:
Hello,

>> after configuring a new home server with PostgreSQL 9.0.4, I observe some regular disk activity, even though the
serveris completely idle (disconnected from the network, no users but one logged in). There are very short write bursts
oncein about 3 seconds. 
>
> There are a couple of things that can cause unexpected disk activity:
>
> -autovacuum running in the background. Setting log_autovacuum_min_duration may help you determine when this is
happening.
> -checkpoint activity. Turning on log_checkpoints, as well as looking for changes in the pg_stat_bgwriter view, may
helpexplain if this is the case. 

I repeatedly looked at that view, but it did not change during at least three *minutes*, so there is probably no
unexpectedcheckpoint activity. 

> -Hint bit updates. Even if you are only reading from a table, in some situations write activity can be generated. See
http://wiki.postgresql.org/wiki/Hint_Bitsfor more information. 
> -Statistics collector updates. If the one logged in user is doing anything at all, they might be generating something
here.

I identified the most active process, at least twenty times more active than any other process on the system:

    postgres  3086  0.1  0.0  34688  2584 ?        Ss   03:11   1:16 postgres: stats collector process

So it's the statistics collector. However, there does not seem to be any database activity at all. I tried looking at
thenumbers returned by this query: 

    select datname, tup_returned, tup_fetched from pg_stat_database ;

Nothing changes there. When OpenFire, Courier-MTA and Apache are restarted, a few numbers change, but othrewise they
remainunchanged pretty long. There is no obvious activity that could trigger a disk write 20 times a minute... 

Andrej


Attachment

Re: Regular disk activity of an idle DBMS

From
Greg Smith
Date:
On 05/29/2011 02:42 PM, Andrej Podzimek wrote:
> I identified the most active process, at least twenty times more
> active than any other process on the system:
>
>     postgres  3086  0.1  0.0  34688  2584 ?        Ss   03:11   1:16
> postgres: stats collector process
>
> So it's the statistics collector. However, there does not seem to be
> any database activity at all. I tried looking at the numbers returned
> by this query:
>
>     select datname, tup_returned, tup_fetched from pg_stat_database ;
>
> Nothing changes there. When OpenFire, Courier-MTA and Apache are
> restarted, a few numbers change, but othrewise they remain unchanged
> pretty long. There is no obvious activity that could trigger a disk
> write 20 times a minute...

There are things that the statistics collector might be updating that
don't show up in the pg_stat_database totals.  It aims to write updates
approximately every 500ms, so your write rate sounds normal.  The
expectation is that the operating system is actually caching most of
those, so that the actual load on the system is minimal.  So it sounds
like you've identified the cause here, and it is normal, expected activity.

One thing that can cause statistics overhead to be higher than it should
be is a larger statistics file than is strictly necessary.  We hear
reports of those sometimes, I've never been completely clear on all of
the possible causes that make this happen.  But running "select
pg_stat_reset();" should clear that out and start fresh again.  That
will sometimes eliminate situations where the I/O seems larger than it
should be for people.

If you do that, and there's still activity going on, there's no easy way
to fix that.  As mentioned in
http://www.postgresql.org/docs/9.0/static/monitoring-stats.html , it's
possible to change PGSTAT_STAT_INTERVAL at server compile time to make
it write statistics less frequently.  There's no easier way to adjust
that though.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


Re: Regular disk activity of an idle DBMS

From
Andrej Podzimek
Date:
>> Nothing changes there. When OpenFire, Courier-MTA and Apache are restarted,
>> a few numbers change, but othrewise they remain unchanged pretty long. There
>> is no obvious activity that could trigger a disk write 20 times a minute...
>
> How many databases are in your pg cluster?

There are currently 19 of them, but only about 5 are used actively (queried at least once a day).


Attachment

Re: Regular disk activity of an idle DBMS

From
Scott Marlowe
Date:
On Sun, May 29, 2011 at 12:42 PM, Andrej Podzimek <andrej@podzimek.org> wrote:

> Nothing changes there. When OpenFire, Courier-MTA and Apache are restarted,
> a few numbers change, but othrewise they remain unchanged pretty long. There
> is no obvious activity that could trigger a disk write 20 times a minute...

How many databases are in your pg cluster?

Re: Regular disk activity of an idle DBMS

From
andrej@podzimek.org
Date:
This message has been digitally signed by the sender.
Attachment