On Fri, Feb 13, 2009 at 3:46 PM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote: >>>> Alexander Staubo <alex@xxxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
