Re: I/O increase after upgrading to 8.3.5

On Fri, Feb 13, 2009 at 3:46 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>>>> Alexander Staubo <alex(at)bengler(dot)no> wrote:
>> After upgrading from 8.2 to 8.3.5, the write load on our database
>> server has increased dramatically and inexplicably -- as has the CPU
>> usage.
>
> Did you do a VACUUM ANALYZE of the database after loading it? Without
> the database VACUUM, the first read of any page causes it to be
> rewritten to set hint bits. Without an ANALYZE, it might be picking
> very inefficient plans. I actually run a VACUUM FREEZE ANALYZE after
> loading a database (as with the upgrade), to prevent massive rewrites
> of everything in the database at some later date due to freeze
> activity.

Thanks, the lack of statistics should explain why things are a bit
slow. I ran a "vacuum freeze analyze" now, but the I/O level is still
quite high.

I have verified using pidstat that the I/O is all caused by
PostgreSQL. Here's some sample output from iostat, interval 1 second
(the 4th column is KB written):

sda 1173.00 68.00 149672.00 68 149672
sda 14.00 0.00 1712.00 0 1712
sda 2.00 0.00 336.00 0 336
sda 679.00 344.00 115200.00 344 115200
sda 238.00 0.00 61764.00 0 61764
sda 436.00 0.00 95004.00 0 95004
sda 14.00 0.00 1032.00 0 1032
sda 1882.00 72.00 82380.00 72 82380
sda 173.00 8.00 7936.00 8 7936

What I find odd is that PostgreSQL is only clocking at about 8 tuple
modifications per second average (on our user tables). There are
around 800 transactions per second, but most of these are only reads.
How can 8 tuples/sec result in 115MB writes per second? The database
is not large enough and the tuples not large enough to explain those
huge amounts of data. At 115MB/s you could rewrite the entire database
in 1.5 minutes.

Is there any way to determine the *sizes* of the tuple mods, not just
the frequency, that PostgreSQL performs? What kinds of tools are
available to track down the causes of these writes?

Alexander.