From: Scott Marlowe <scott.marlowe@gmail.com>
To: Barbara Stephenson <barbara@turbocorp.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Recommend dba maintenance tasks on a regular bases
Date: Fri, 12 Sep 2008 11:26:45 -0600
Message-ID: <dcc563d10809121026v6b39e3ccmec3d62720b1c2dc2@mail.gmail.com> (raw)
In-Reply-To: <200809121149.46137.barbara@turbocorp.com>
References: <200809121149.46137.barbara@turbocorp.com>
On Fri, Sep 12, 2008 at 9:49 AM, Barbara Stephenson
<barbara@turbocorp.com> wrote:
> Hello,
>
> We are currently using Postgresql 8.3.3 on Red Hat 4 and our largest database
> is around 8454 MB.
>
> I have recommend the below to my group but not sure if reindexing should be
> involved since autovacuum is on?
>
> How can I be sure auto vacumming is working fine? We haven't had any problems
> plus I do a query and it does list all the tables and shows the last update
> of auto vacuum and auto analyze. Is that it?
>
>
> 1- pg_dump - binary dump every midday and nightly
If uptime is critical, also look at setting up a PITR server.
> 2 - auto vacuum
> autovacuum = on
> log_autovacuum_min_duration = 0
> autovacuum_max_workers = 3
> autovacuum_naptime = 1min
> autovacuum_vacuum_threshold = 50
> autovacuum_vacuum_scale_factor = 0.2
> autovacuum_analyze_scale_factor = 0.1
What's your autovacuum sleep? 10 to 20 is pretty reasonable. If you
set it much higher autovacuum may not run fast enough to keep up.
Run regular vacuum verbose on the database and examine the last 20 or
so lines (need to be superuser I think) to see how many free space map
slots you need. FSM slots are super cheap (6 bytes each) but they do
come from shared memory. A good setting for a database in your size
range with high updates will be between the default 200k or so to 1M.
> 3- rotate data logs
pgsql has this ability built in, as does your OS. I prefer letting
pgsql log as I like the options better than syslog, plus it seems to
be faster.
Also, don't forget to add long term periodic maintenance. Things like
taking the server down every 6 or 12 months to blow out dust, run
memory and drive tests, etc...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scott.marlowe@gmail.com, barbara@turbocorp.com
Subject: Re: Recommend dba maintenance tasks on a regular bases
In-Reply-To: <dcc563d10809121026v6b39e3ccmec3d62720b1c2dc2@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox