[clue] postgres questions?

[Quentin Hartman]

What version of postgres? If it's 9.x and is actually largely static as you
say, the autovacuuming that is usually enabled out of the box should be
sufficient to keep things under control. If not, something is causing
large, and probably repeated, deletes and inserts rather than updates. You
should be able to get some clues out of the server logs. Turn on all the
query logging so you can see everything. I would guess that there is
something making minor changes to every row over and over, maybe updating a
timestamp or something, that is causing the behavior your are seeing. If
that's the case, you should be able to adjust your xlog settings so that
they get recycled more quickly and that should also help. Ultimately though
this sounds like a misbehaving application.

QH

On Mon, Dec 5, 2016 at 7:47 AM, Mike Bean <beandaemon at gmail.com> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://cluedenver.org/pipermail/clue/attachments/20161205/080a3d47/attachment-0001.html