Re: Catching up Production from Warm Standby after maintenance - Please help

On Tue, Jul 7, 2009 at 4:58 PM, Jennifer
Spencer<jenniferm411@hotmail.com> wrote:
> Hi -  Some answers, questions and comments below
>
>> So, what class machines are these, and specifically how much ram, what
>> kind of RAID controllers, and how many hard drives are you throwing at the
>> problem?
> We have two identical enterprise Linux machines coming.  They are not here
> yet (hence my planning concerns).  Presently, we have a lesser but still
> good enterprise machine with 64gb of RAM, 8 CPUs and ~1 TB of space.  The
> new ones coming have 5 TB each of RAID disks mirrored across to another 5TB
> (20 5.4gb spinning disks).  Not sure the RAID level but I think it's level
> six.  They each have 16 CPUs, and (I believe) 128 Gb of RAM, connected to
> our NFS network on a 10g switch (at least I think it's the 10 gigabit switch
> - it might be one gigabit).

Assuming that your db is mostly read, RAID-6+1 is ok, but see if you
can benchmark things with RAID-10 to be sure, as RAID-10 is generally
the best choice.  Also, get a battery backed cache if you can.

>>  I wonder, what do you use to decide when to reindex?  Under this
>> situation, it's very possible that you don't need to do it all that often.
>>  Are you just flying 'willy-nilly' about reindexing things, or is there some
>> indicator you use?
> I am hoping to use system statistics for the table(s).  In theory, once the
> index size gets to be a larger logical fraction of the table size than it
> reasonably should be, it's time to consider re-indexing.  I thought to use
> some combination of pg_stat_user_indexes info and pg_statio_all_indexes, but
> I haven't had to do this yet and if you have suggestions, that'd be
> helpful.  I should be able to run a check of things once/week and cron it to

Generally speaking vacuuming should take care of index and table bloat
to prevent this.

>>Define "maintenance".  Maybe you're bringing along a few more bad habits
>> from Sybase.
>  Oh, I am sure that's likely.  Heh.  I lock out the users, reindex things
> that need reindexing, based on use patterns and size, I update all
> histograms and statistics for user and system tables, I remove any dead rows
> possible
> (hopefully all of them),

depending on your usage patterns some dead space is actually desirable.

> and I make a nice clean tape set when I am done -
> before letting anyone back in.  I often cycle the server (pg_ctl start/stop)
> as well.  Sometimes we take that time to power cycle the whole machine
> and do any machine maintenance we can after the nice clean backup is done.

Good time to fsck the volumes as well.

>> If you're running a version prior to 8.4, make sure your Free Space
>> Manager settings are adequate. (A VACUUM VERBOSE at the database level will
>> report where you're at in that regard in the last few lines.)
> I show the following in postgresql.conf (this has not been tuned):
> # - Free Space Map -
> max_fsm_pages = 204800                  # min max_fsm_relations*16, 6 bytes
> each
>                                         # (change requires restart)
> #max_fsm_relations = 1000               # min 100, ~70 bytes each
>                                         # (change requires restart)

Both are probably way too low.  I'm guessing there's more than 1000
things in your db to vacuum.