Thread: From pgsql-general: Huge number of disk writes after migration to 8.1

Short introduction:
After migration from 8.0.3 to 8.1.2 I noticed huge increase in number of
bytes written to disk. I think it's caused by stats collector process,
but I may be wrong.

Some details:

1. Hardware: 2*Opteron 275, Tyan motherboard, 8GBs DDR ECC RAM, 2*SCSI
73GB 15k RPM drives.

2. Software: Debian Sarge amd64, kernel 2.6.12.5, PostgreSQL 8.1.2 built
from sources with:
./configure --host=x86_64-linux --build=x86_64-linux --enable-recode
--enable-nls --enable-integer-datetimes --disable-debug --disable-rpath
--without-tcl --without-perl --without-python --without-pam
--with-openssl --with-gnu-ld --without-krb5 --without-tk --without-java
--with-maxbackends=1024 --with-pgport=5432 --enable-thread-safety
gcc (GCC) 3.4.4 20050314 (prerelease) (Debian 3.4.3-13)
Note: Exactly the same environment were used to build previous 8.0.3
version.

3. 14-20GBs of data, ~120 DBs, ~60 used simultaneously, 1000-2000 tables
in each DB, 200-250 connections. Queries: 120-160 selects, 18-25
INSERT/UPDATE/DELETE per seconds during work hours.


4. postgresql.conf:
shared_buffers=30000
max_prepared_transactions=0 #(line added after migration 8.1)
work_mem=256000
maintenance_work_mem=512000
max_fsm_pages=1800000
max_fsm_relations=80000

fsync_on

checkpoints_segments=12
effective_cache_size=800000

log_destination='syslog'
client_min_messages=warning
log_min_messages=notice
log_min_duration_statement=200

stats_start_collector = on
stats_command_string = on


5. The writes rate stays at 20MB/s during work hours, the reads rate is
unnoticeable (60KB-100KB/s). With 8.0.3 the writes rate stayed at 1-1,5MB/s.
6. After disabling stats_command_string, the writes rate stepped down to
~10MB/s
7. No autovacuum, VACUUM FULL is performed every night, and VACUUM
ANALYZE is performed every 30 minutes on few critical tables.
8. About 2000 (with stats_command_string enabled) queries lasts longer
than 300ms, after disabling the stats_command_string it dropped to 900
(it used to be ~500-600 with 8.0.3). The duration of the longest query
in last two day was 18s.
9. ps aux shows:
208:39 postgres: stats collector process
 12:33 /usr/lib/postgresql/bin/postmaster
  0:28 postgres: writer process
  4:23 postgres: stats buffer process
after three days run. Two days with stats_command_string enabled
resulted in ~180 minutes of CPU time utilized by stats collector,
while during near one day with disabled command_string stats collector
utilized only 58 minutes.

With 8.0.3 it used to be like that:
 31:07 /usr/lib/postgresql/bin/postmaster
175:10 postgres: writer process
 30:55 postgres: stats buffer process
 58:43 postgres: stats collector process
(PostgreSQL was running for almost 12 days).

Tom Lane wrote:
> Yeah, something wrong there :-(.  What did you say your platform was
> exactly?  Would you strace the collector process, and send maybe ten K
> or so of trace output to pgsql-bugs?  It's probably not appropriate for
> pgsql-general.

The gzipped strace output of collector is attached. Unfortunately, the
server wasn't very busy (just 10-20% utilization).
The stats_command_string was disabled during stracing.

Thanks in advance
--
Marcin

Marcin <migor@op.pl> writes:
> After migration from 8.0.3 to 8.1.2 I noticed huge increase in number of
> bytes written to disk. I think it's caused by stats collector process,
> but I may be wrong.

The strace output shows that the collector is writing about 8.5MB to the
stats display file on each cycle.  That seems like rather a lot :-(.
A quick comparison of the 8.0 and HEAD versions of pgstats says that the
8.1 file format should be slightly bulkier than 8.0, but not a lot so.

The first theory that comes to mind is that 8.1 is somehow failing to
discard dead table entries and continues to write them when no longer
needed.

Could you send me the following data (off-list since we know it will be
several meg):

* A copy of $PGDATA/global/pgstat.stat (note this file is rewritten
every half second, so copy it before you start packing it into a
message)

* The output of "ps auxww | grep postgres", taken at the same time you
copy pgstat.stat (or as close as you can easily get, anyway).

This does not need to be done under particularly heavy load, in fact
light load would be better since it'd be more obvious if there are
too many backend entries.

Thanks!

            regards, tom lane