Обсуждение: Regular disk activity of an idle DBMS
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
Вложения
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
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
Вложения
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
>> 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).
Вложения
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?
This message has been digitally signed by the sender.