Re: New server to improve performance on our large and busy DB - advice?

On Tue, Jan 19, 2010 at 2:09 PM, Carlo Stonebanks
<stonec(dot)register(at)sympatico(dot)ca> wrote:
> Hi Scott,
>
> Sorry for the very late reply on this post, but I'd like to follow up. The
> reason that I took so long to reply was due to this suggestion:
>
> <<Run vacuum verbose to see if you're
> overrunning the max_fsm_pages settings or the max_fsm_relations.
>>>
>
> My first thought was, does he mean against the entire DB? That would take a
> week! But, since it was recommended, I decided to see what would happen. So,
> I just ran VACUUM VERBOSE. After five days, it was still vacuuming and the
> server admin said they needed to bounce the server, which means the command
> never completed (I kept the log of the progress so far, but don't know if
> the values you needed would appear at the end. I confess I have no idea how
> to relate the INFO and DETAIL data coming back with regards to max_fsm_pages
> settings or the max_fsm_relations.

yeah, the values are at the end. Sounds like your vacuum settings are
too non-aggresive. Generally this is the vacuum cost delay being too
high.

> So, now my questions are:
>
> 1) Did you really mean you wanted VACUUM VERBOSE to run against the entire
> DB?

Yes. A whole db at least. However...

> 2) Given my previous comments on the size of the DB (and my thinking that
> this is an exceptionally large and busy DB) were you expecting it to take
> this long?

Yes, I was figuring it would be a while. However...

> 3) I took no exceptional measures before running it, I didn't stop the
> automated import processes, I didn't turn off autovacuum. Would this have
> accounted for the time it is taking to THAT degree?

Nah, not really. However...

> 4) Any other way to get max_fsm_pages settings and max_fsm_relations?

Yes! You can run vacuum verbose against the regular old postgres
database (or just create one for testing with nothing in it) and
you'll still get the fsm usage numbers from that! So, no need to run
it against the big db. However, if regular vacuum verbose couldn't
finish in a week, then you've likely got vacuum and autovacuum set to
be too timid in their operation, and may be getting pretty bloated as
we speak. Once the fsm gets too blown out of the water, it's quicker
to dump and reload the whole DB than to try and fix it.