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
collector.txt.gz
Description: GNU Zip compressed data
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
