Skip site navigation (1) Skip section navigation (2)

Re: I/O increase after upgrading to 8.3.5

From: Alexander Staubo <alex(at)bengler(dot)no>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: I/O increase after upgrading to 8.3.5
Date: 2009-02-13 15:58:21
Message-ID: 88daf38c0902130758g6961d47h608df62802cd908f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

pgsql-performance by date

Next:From: Alexander StauboDate: 2009-02-13 16:04:58
Subject: Re: I/O increase after upgrading to 8.3.5
Previous:From: Kevin GrittnerDate: 2009-02-13 14:46:42
Subject: Re: I/O increase after upgrading to 8.3.5

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group