<div dir="ltr">No worries, yeah, I've pretty much resigned myself to doing a vacuum/reindex 2 or 3 times a month. I'm fairly convinced that the deadlocks I've found are happening around RHN errata, and there's only one repo that's importing errata, but I haven't figured out how to disable that function. I've gone as far as chatting with some of the DBA's who work here in other functions. They're not as afraid of deadlocks as I am, but they also have access to the queries/code, and since it's basically an open-source implementation of satellite and I'm not a DBA, that's not going to happen. So really my only choice is to patch it, but it (the system), is a relatively delicate single point of failure in this environment, so I really don't want to stick my hand in that bee-hive if I can help it. It'd be easier to try and get more storage for the NFS share it runs on, but of course, the problem with that logic, is I have no idea if there's a cap on how much space it will eat. For all I know it will keep growing ad-infinitum.</div><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Dec 12, 2016 at 11:20 AM, Chris Fedde <span dir="ltr"><<a href="mailto:chris@fedde.us" target="_blank">chris@fedde.us</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">I know that this one is getting kind of old but I thought I'd chime in anyway. You can ask postgresql to log the queries it is running.<div>It might be worth reviewing <a href="https://www.postgresql.org/docs/8.4/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT" target="_blank">https://www.<wbr>postgresql.org/docs/8.4/<wbr>static/runtime-config-logging.<wbr>html#RUNTIME-CONFIG-LOGGING-<wbr>WHAT</a> as a step along the way.</div><div>Of interest might be the 'log_statement'</div><div><br></div><div>Also the freenode#postgresql irc channel has been very helpful to me in the past.</div><div><br></div><div>Good luck!</div><span class="HOEnZb"><font color="#888888"><div>chris</div></font></span></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Dec 7, 2016 at 8:37 AM, Quentin Hartman <span dir="ltr"><<a href="mailto:qhartman@gmail.com" target="_blank">qhartman@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Sorry to hear that. Such is the life of us Internet janitors, always cleaning up someone else's spilled lunch...<br></div><div class="m_-6451900427628926376HOEnZb"><div class="m_-6451900427628926376h5"><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Dec 6, 2016 at 2:13 PM, Mike Bean <span dir="ltr"><<a href="mailto:beandaemon@gmail.com" target="_blank">beandaemon@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Yeah, tell me about it. Problem is just to make things super interesting there is way too much of the network with fish-hooks into thing, it's not easy to patch. Apparently the term "single point of failure" - wasn't thoroughly considered when designing/implementing this environment. I just recently met a DBA at my company, asked their opinion. After answering some questions we arrived at basically the same conclusion. If you can't get into the code and look at the actual queries, your only real practical option is to patch it; which I definitely cannot do easily. (sigh)<div><br></div></div><div class="m_-6451900427628926376m_-8635807529514521207HOEnZb"><div class="m_-6451900427628926376m_-8635807529514521207h5"><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Dec 5, 2016 at 1:50 PM, Quentin Hartman <span dir="ltr"><<a href="mailto:qhartman@gmail.com" target="_blank">qhartman@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div>I should add that there have been some pretty massive improvements to postgres since 8.4, and it wouldn't be at all surprising if your problem just disappeared after an upgrade.<span class="m_-6451900427628926376m_-8635807529514521207m_-6502176060456402920HOEnZb"><font color="#888888"><br><br></font></span></div><span class="m_-6451900427628926376m_-8635807529514521207m_-6502176060456402920HOEnZb"><font color="#888888">QH<br></font></span></div><div class="m_-6451900427628926376m_-8635807529514521207m_-6502176060456402920HOEnZb"><div class="m_-6451900427628926376m_-8635807529514521207m_-6502176060456402920h5"><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Dec 5, 2016 at 1:48 PM, Quentin Hartman <span dir="ltr"><<a href="mailto:qhartman@gmail.com" target="_blank">qhartman@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div>Oof, 8.4, that's tough. It's been long enough since I touched an 8.x box, I'm not sure off hand. It should be in the main conf though. Here's what should be the relevant section i nthe official docs: <a href="https://www.postgresql.org/docs/8.4/static/runtime-config-logging.html" target="_blank">https://www.postgresql.org/doc<wbr>s/8.4/static/runtime-config-lo<wbr>gging.html</a><br><br></div>As an aside, that's not a supported version anymore. Unless you have a compelling reason not to, upgrading to a 9.2+ (9.6 is current) should be at or near the top of your to-do list...<span class="m_-6451900427628926376m_-8635807529514521207m_-6502176060456402920m_8131589494932958803HOEnZb"><font color="#888888"><br><br></font></span></div><span class="m_-6451900427628926376m_-8635807529514521207m_-6502176060456402920m_8131589494932958803HOEnZb"><font color="#888888">QH <br></font></span></div><div class="m_-6451900427628926376m_-8635807529514521207m_-6502176060456402920m_8131589494932958803HOEnZb"><div class="m_-6451900427628926376m_-8635807529514521207m_-6502176060456402920m_8131589494932958803h5"><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Dec 5, 2016 at 1:41 PM, Mike Bean <span dir="ltr"><<a href="mailto:beandaemon@gmail.com" target="_blank">beandaemon@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">It's 8.4 - unfortunately we're having some problems with autovacum appearing to be configured correctly, not realistically running when it should<div><br></div><div>POSTGRES_LAST_AUTOVACUUM CRITICAL: DB "xe" (host:localhost) (port=1935) DB: xe TABLE: pg_catalog.pg_class: 17:11 November 18, 2016 (16 days 20 hours 23 minutes)<br></div><div><br></div><div>the thursday log is largely full of checkpoint_segments spam, and ONE deadlock condition:</div><div><br></div><div><div>DETAIL: Process 9758 waits for ShareLock on transaction 40741339; blocked by process 9794.</div><div> Process 9794 waits for ShareLock on transaction 40740891; blocked by process 9758.</div><div> Process 9758: select * from rhn_channel.update_needed_cach<wbr>e($1) as result</div><div> Process 9794:</div></div><div><br></div><div>there hasn't been much in the way of logging since the deadlock, just more checkpoint_segment spam</div><div><div>HINT: Consider increasing the configuration parameter "checkpoint_segments".</div><div>LOG: checkpoints are occurring too frequently (16 seconds apart)</div></div><div><br></div><div>Since as near as I can tell the only meaningful logging over the last 4 days is the deadlock, I'm inclined to think the deadlock has some relationship with the DB growing 40GB over the weekend. Quentin's probably right, I probably need to turn up the logging levels. Do we know where those are? in the .conf file?</div></div><div class="m_-6451900427628926376m_-8635807529514521207m_-6502176060456402920m_8131589494932958803m_-8362780188321382994HOEnZb"><div class="m_-6451900427628926376m_-8635807529514521207m_-6502176060456402920m_8131589494932958803m_-8362780188321382994h5"><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Dec 5, 2016 at 11:06 AM, Grant Johnson <span dir="ltr"><<a href="mailto:grant@amadensor.com" target="_blank">grant@amadensor.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">There is a good chance that the Thursday log holds some clues about what<br>
the activity was. It is likely repeated often, and will be easy to<br>
find, as it was happening a lot. If the volume was very high, you also<br>
see messages about checkpoints, because changes were out pacing the<br>
normal background DB writes. If these were happening only during this<br>
anomalous time, I would not worry about it, but if it happens during<br>
normal operations, some tuning changes may be needed.<br>
<br>
If you are having updates that exceed your free space map, the normal<br>
vacuum will not release the space, and instead you will need to run a<br>
vacuum full, which has the unfortunate side effect of locking tables<br>
while it runs, so it impedes write operations.<br>
<br>
So, I would look at the log, see if anything starts making sense, and<br>
then clean up with a full vacuum during a low activity time, and perhaps<br>
a reindex all. I have found that doing full clean up every year or so<br>
helps, but generally, leaving the free space in the files with normal<br>
vacuum/auto-vacuum helps performance.<br>
<div class="m_-6451900427628926376m_-8635807529514521207m_-6502176060456402920m_8131589494932958803m_-8362780188321382994m_6392774321791334606HOEnZb"><div class="m_-6451900427628926376m_-8635807529514521207m_-6502176060456402920m_8131589494932958803m_-8362780188321382994m_6392774321791334606h5"><br>
<br>
On Mon, 2016-12-05 at 07:47 -0700, Mike Bean wrote:<br>
> Of particular interest is<br>
><br>
><br>
> -rw------- 1 postgres postgres 94479 Dec 2 21:29 postgresql-Fri.log<br>
> -rw------- 1 postgres postgres 0 Dec 5 00:00 postgresql-Mon.log<br>
> -rw------- 1 postgres postgres 0 Dec 3 00:00 postgresql-Sat.log<br>
> -rw------- 1 postgres postgres 0 Dec 4 00:00 postgresql-Sun.log<br>
> -rw------- 1 postgres postgres 208184 Dec 1 23:59 postgresql-Thu.log<br>
> -rw------- 1 postgres postgres 86154 Nov 29 21:57 postgresql-Tue.log<br>
> -rw------- 1 postgres postgres 89399 Nov 30 21:48 postgresql-Wed.log<br>
><br>
><br>
> Huh - want to bet the writes were actually on thursday? The plot<br>
> thickens!<br>
><br>
> On Mon, Dec 5, 2016 at 7:43 AM, Mike Bean <<a href="mailto:beandaemon@gmail.com" target="_blank">beandaemon@gmail.com</a>><br>
> wrote:<br>
><br>
> Do we have members who are comfortable/skilled with postgres?<br>
> Long story short we have a spacewalk server running<br>
> postgres and the DB is showing really erratic behavior. We<br>
> used to do weekly vacuums to keep it from running out of<br>
> space, I figured out that doing a reindex bumped the free<br>
> space from 20-80 GB. Over the weekend, it dropped to roughly<br>
> 60, we went one week without doing a vacuum reindex, and it's<br>
> back down to 20.<br>
><br>
><br>
> I know for a fact there's no one else in the organization<br>
> making major changes on this DB, and certainly nothing that<br>
> could justify 40GB of writes in 3 days. Not really being a<br>
> DBA, I'm running low on my bag of tricks, thought I'd see if<br>
> CLUE had any advice. Running vacuums once a week to keep it<br>
> from filling the disk feels like taking aspirin to get over<br>
> your gunshot wound. Sure, it'll keep the server running, but<br>
> ultimately what I really want to know is why a largely static<br>
> spacewalk DB is writing 60GB to the disk in a 2 week interval.<br>
><br>
><br>
><br>
><br>
</div></div><div class="m_-6451900427628926376m_-8635807529514521207m_-6502176060456402920m_8131589494932958803m_-8362780188321382994m_6392774321791334606HOEnZb"><div class="m_-6451900427628926376m_-8635807529514521207m_-6502176060456402920m_8131589494932958803m_-8362780188321382994m_6392774321791334606h5">> ______________________________<wbr>_________________<br>
> clue mailing list: <a href="mailto:clue@cluedenver.org" target="_blank">clue@cluedenver.org</a><br>
> For information, account preferences, or to unsubscribe see:<br>
> <a href="http://cluedenver.org/mailman/listinfo/clue" rel="noreferrer" target="_blank">http://cluedenver.org/mailman/<wbr>listinfo/clue</a><br>
<br>
<br>
______________________________<wbr>_________________<br>
clue mailing list: <a href="mailto:clue@cluedenver.org" target="_blank">clue@cluedenver.org</a><br>
For information, account preferences, or to unsubscribe see:<br>
<a href="http://cluedenver.org/mailman/listinfo/clue" rel="noreferrer" target="_blank">http://cluedenver.org/mailman/<wbr>listinfo/clue</a><br>
</div></div></blockquote></div><br></div>
</div></div><br>______________________________<wbr>_________________<br>
clue mailing list: <a href="mailto:clue@cluedenver.org" target="_blank">clue@cluedenver.org</a><br>
For information, account preferences, or to unsubscribe see:<br>
<a href="http://cluedenver.org/mailman/listinfo/clue" rel="noreferrer" target="_blank">http://cluedenver.org/mailman/<wbr>listinfo/clue</a><br></blockquote></div><br></div>
</div></div></blockquote></div><br></div>
</div></div><br>______________________________<wbr>_________________<br>
clue mailing list: <a href="mailto:clue@cluedenver.org" target="_blank">clue@cluedenver.org</a><br>
For information, account preferences, or to unsubscribe see:<br>
<a href="http://cluedenver.org/mailman/listinfo/clue" rel="noreferrer" target="_blank">http://cluedenver.org/mailman/<wbr>listinfo/clue</a><br></blockquote></div><br></div>
</div></div><br>______________________________<wbr>_________________<br>
clue mailing list: <a href="mailto:clue@cluedenver.org" target="_blank">clue@cluedenver.org</a><br>
For information, account preferences, or to unsubscribe see:<br>
<a href="http://cluedenver.org/mailman/listinfo/clue" rel="noreferrer" target="_blank">http://cluedenver.org/mailman/<wbr>listinfo/clue</a><br></blockquote></div><br></div>
</div></div><br>______________________________<wbr>_________________<br>
clue mailing list: <a href="mailto:clue@cluedenver.org" target="_blank">clue@cluedenver.org</a><br>
For information, account preferences, or to unsubscribe see:<br>
<a href="http://cluedenver.org/mailman/listinfo/clue" rel="noreferrer" target="_blank">http://cluedenver.org/mailman/<wbr>listinfo/clue</a><br></blockquote></div><br></div>
</div></div><br>______________________________<wbr>_________________<br>
clue mailing list: <a href="mailto:clue@cluedenver.org">clue@cluedenver.org</a><br>
For information, account preferences, or to unsubscribe see:<br>
<a href="http://cluedenver.org/mailman/listinfo/clue" rel="noreferrer" target="_blank">http://cluedenver.org/mailman/<wbr>listinfo/clue</a><br></blockquote></div><br></div>