Hi,
I suffer from huge increase in number of disk writes after migration
from 8.0.3 to 8.1.2. The average number of sectors written increased 10
times and transfer stays at 20MB of writes per second during work hours
(with 20% cpu time wasted in iowait). The number of queries lasting more
than 300ms increased thrice.
So, any clues what's going on?
The hardware is Dual 275, 8 GBs RAM, two 73 GB 15K SCSI 320 with
software RAID1. The S is 64bit Debian Sarge with 8.1.2 build from
sources in exactly the same way as 8.0.3 was built before.
My 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
The system is quite busy but the numer of queries hasn't changed after
migration. The cluster contains about 100 databases (some of them with
2000 tables) with total size of 20GBs. During peak hours it hits 160
SELECTS and about 18 modifying queries per second. The DBs are fully
vacuumed every day, and vacuum analyze is performed on critical tables
every 30 minutes.

Mon, Jan 16, 2006 at 05:09:18PM -0500, Qingqing Zhou wrote:
AFAICS the difference maybe related to this between 8.0 and 8.1 is the
introduction of autovacuum. But seems you didn't turn it on. Do you observed
only writes increased or both read/write? If the latter, that might be an
unoptimized query plan invloves seqscan Can you find out what's the
writes on?

Autovacuum is turned off.
The reads remains on very, very low level. According to monitored values
from /sys/block/md?/stat (confirmed by iostat output) the reads rate
stays at 70-80 blocks per second, while the writes rate keeps at 20000 blocks.

However, I found something interesting.

Playing with postgresql.conf I changed "stats_command_string" to off,
reloaded config, and restarted connection from applications (the postgresql
wasn't restarted). The write rate immediately drops down, and is now
at 8000-9000 blocks per second (which is still 4-5 times more than in
8.0.3).

Looking at the CPU usage in top, I found strange stats for postgres processes
(ps aux dump):
11:25 /
0:25 postgres: writer process
4:21 postgres: stats buffer process
201:46 postgres: stats collector process
(PostgreSQL is running for two and a half day now).

I found some ps -aux dumps from last week, (with 8.0.3 and
stats_command_string enabled) and CPU usage was completely different:
31:07 /
175:10 postgres: writer process
30:55 postgres: stats buffer process
58:43 postgres: stats collector process
(PostgreSQL was running for 12 days).

It seems that some changes to stats collector introduced in 8.1 are now eating
my CPU power, and probably also the (I)/ bandwidth. :(

Thanks for help,

Qingqing Zhou wrote:
A similar problem was reported before:

But we conclude that's not related to pgstats. See if that's related to
your situation.

Unfortunately, I don't think so. The 8.0.3 run just fine. And I don't
see any pgsql_tmp files. The problems started after migration to 8.1.2.
I posted to pgsql-bugs, following Tom suggestion.
BTW, I made some benchmarks on test machine (which is 32bit 2xPIII
1.4GHz) with pgbench and I was quite amazed:
pgbench -S -c 50 -t 10000 -v pgbench
with stats collector disabled resulted in:
tps = 3178.346439 (including connections establishing)
tps = 3183.360731 (excluding connections establishing)

with stats collector enabled, but stats_command_prompt disabled:
tps = 3143.376908 (including connections establishing)
tps = 3147.564695 (excluding connections establishing)

and with stats_command_prompt enabled:
tps = 2446.136610 (including connections establishing)
tps = 2448.785260 (excluding connections establishing)

However, I didn't notice any suspicious write activity.

Thanks for suggestion,