Re: Vacuum goes worse

Stéphane Schildknecht wrote:
> I wonder vacuum verbose would tell me if fsm parameters were not too
> badly configured, but I can't get the 4 last lines of the output...

Why not?

> Whats's more, I wonder what we could monitor to get some explanation of
> the recent time increase, and then have a quite-sure way of configuring
> the server.

sar or iostat output would be a good start, to determine if it's waiting
for I/O or what.

> I have to say the database is hosted, accessed in production on a 24/7
> basis and then every change in configuration has to be scheduled.
>
> Some more information you may ask:
> chackpoint_segments : 32
> checkpoint_timeout    : 180
> checkpoint_warning   : 30
> wal_buffers                 : 64
> maintenance_work_mem : 65536
> max_fsm_pages                : 400000
> max_fsm_relations           : 1000
> shared_buffers                : 50000
> temp_bufers                    : 1000
>
> We also have 4Gb RAM.
>
> Isn't checkpoint_segments too low as all files in pg_xlogs seem to be
> recycled within a few minutes. (In fact among the 60 files, at least 30
> have been modified during the few minutes of that particular vacuum).

Increasing checkpoint_segments seems like a good idea then. You should
increase checkpoint_timeout as well, 180 is just 3 minutes. How much
concurrent activity is there in the database? 30 pg_xlog files equals
512 MB of WAL; that's quite a lot.

Have you changed the vacuum cost delay settings from the defaults?

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com