[clue] postgres questions?

[Grant Johnson]

There is a good chance that the Thursday log holds some clues about what
the activity was.    It is likely repeated often, and will be easy to
find, as it was happening a lot.   If the volume was very high, you also
see messages about checkpoints, because changes were out pacing the
normal background DB writes.  If these were happening only during this
anomalous time, I would not worry about it, but if it happens during
normal operations, some tuning changes may be needed.

If you are having updates that exceed your free space map, the normal
vacuum will not release the space, and instead you will need to run a
vacuum full, which has the unfortunate side effect of locking tables
while it runs, so it impedes write operations.

So, I would look at the log, see if anything starts making sense, and
then clean up with a full vacuum during a low activity time, and perhaps
a reindex all.   I have found that doing full clean up every year or so
helps, but generally, leaving the free space in the files with normal
vacuum/auto-vacuum helps performance.


On Mon, 2016-12-05 at 07:47 -0700, Mike Bean wrote:
> Of particular interest is
> 
> 
> -rw-------  1 postgres postgres  94479 Dec  2 21:29 postgresql-Fri.log
> -rw-------  1 postgres postgres      0 Dec  5 00:00 postgresql-Mon.log
> -rw-------  1 postgres postgres      0 Dec  3 00:00 postgresql-Sat.log
> -rw-------  1 postgres postgres      0 Dec  4 00:00 postgresql-Sun.log
> -rw-------  1 postgres postgres 208184 Dec  1 23:59 postgresql-Thu.log
> -rw-------  1 postgres postgres  86154 Nov 29 21:57 postgresql-Tue.log
> -rw-------  1 postgres postgres  89399 Nov 30 21:48 postgresql-Wed.log
> 
> 
> Huh - want to bet the writes were actually on thursday?    The plot
> thickens!
> 
> On Mon, Dec 5, 2016 at 7:43 AM, Mike Bean <beandaemon at gmail.com>
> wrote:
>         
>         Do we have members who are comfortable/skilled with postgres?
>         Long story short we have a spacewalk server running
>         postgres and the DB is showing really erratic behavior.  We
>         used to do weekly vacuums to keep it from running out of
>         space, I figured out that doing a reindex bumped the free
>         space from 20-80 GB.  Over the weekend, it dropped to roughly
>         60, we went one week without doing a vacuum reindex, and it's
>         back down to 20.
>         
>         
>         I know for a fact there's no one else in the organization
>         making major changes on this DB, and certainly nothing that
>         could justify 40GB of writes in 3 days.  Not really being a
>         DBA, I'm running low on my bag of tricks, thought I'd see if
>         CLUE had any advice.   Running vacuums once a week to keep it
>         from filling the disk feels like taking aspirin to get over
>         your gunshot wound.  Sure, it'll keep the server running, but
>         ultimately what I really want to know is why a largely static
>         spacewalk DB is writing 60GB to the disk in a 2 week interval.
>         
>         
> 
> 
> _______________________________________________
> clue mailing list: clue at cluedenver.org
> For information, account preferences, or to unsubscribe see:
> http://cluedenver.org/mailman/listinfo/clue