Re: Huge number of disk writes after migration to 8.1

On 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 /usr/lib/postgresql/bin/postmaster
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 /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 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)/O bandwidth. :(

Thanks for help,
--
Marcin