<div dir="ltr">As a bit of follow-up from later in the day:<div><br></div><div>Things are looking much, much better. I will likely resize this db back to a micro again overnight.</div><div><br></div><div><b>DB CPU utilization</b><i> (Times in UTC)</i></div>
<div><img src="cid:ii_1442339f8152f66c" alt="Inline image 1" width="420" height="235"><br></div><div><br></div><div><b>DB Connections</b><i>(Times in UTC)</i></div><div><img src="cid:ii_144233a4cb3d7e78" alt="Inline image 2" width="420" height="227"><br>
</div><div><br></div><div><b>Overall Thimble performance</b> <i>(Times in CDT, -6 hours from UTC)</i></div><div><img src="cid:ii_144233a9bf1c97bc" alt="Inline image 3" width="420" height="179"><br></div><div><br></div><div>
<b>Thimble Database performance </b><i>(Times in CDT, -6 hours from UTC)</i></div><div><i><img src="cid:ii_144233c33e99cc13" alt="Inline image 4" width="827" height="428"><br></i></div><div><i><br></i></div><div><i><br></i></div>
<div>Hoooray!</div><div>JP</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Feb 11, 2014 at 12:06 PM, JP Schneider <span dir="ltr"><<a href="mailto:jp@mozillafoundation.org" target="_blank">jp@mozillafoundation.org</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 took two comparisons for 11:30am cst - 12:00pm, between yesterday and today.<div><br></div><div>Yesterday, before adding indexes:</div>
<div><img src="cid:ii_144221fdfffbea6c" alt="Inline image 1"><br></div>
<div><br></div><div>Today, after adding indexes:</div><div><img src="cid:ii_14422204acdc9d03" alt="Inline image 2"><br></div><div><br></div><div><img src="cid:ii_1442220d6d978530" alt="Inline image 3"><br></div><div><br>
</div>
<div><br></div></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Feb 11, 2014 at 11:59 AM, JP Schneider <span dir="ltr"><<a href="mailto:jp@mozillafoundation.org" target="_blank">jp@mozillafoundation.org</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">OK, we tackled the first big query issue from LegacyProjects.<div><br></div><div>Example query:</div><div>
SELECT * FROM thimble.LegacyProjects WHERE LegacyProjects.short_url_id='123456' LIMIT 1;<br></div>
<div><br></div><div>Before index creation, we were scanning 200,000 rows for each run.</div><div>I added a 6 character index (procedure analyse shows max char length of 6 on short_url_id)</div><div>After index creation, we scan only one row.</div>
<div><br></div><div>My script found another example query that hasn't been awesome, whose performance is in the below newrelic screengrab. 2k ms for a query is not good!</div><div><img src="cid:ii_14422169c4059675" alt="Inline image 1"><br>
</div><div><br></div><div>I managed to catch that query running in my monitor script:</div><div><div><b>SELECT * FROM `ThimbleProjects` WHERE `ThimbleProjects`.`userid`=''<a href="mailto:someemail@address.com" target="_blank">someemail@address.com</a>' AND `ThimbleProjects`.`title`='gre-201' LIMIT 1;</b></div>
</div><div><br></div><div>An explain on that showed each one of those was unkeyed, and scanning over 40k rows.</div><div><img src="cid:ii_1442217ea81c8ddf" alt="Inline image 2" width="841" height="30"><br></div><div><br>
</div>
<div>I indexed the userid field, which is varchar(255) but had no index/key, and the same query now shows a lot of improvement, down to one scanned row.</div><div><img src="cid:ii_1442218bba06bbd0" alt="Inline image 3" width="841" height="30"><br>
</div><div><br></div><div>I'll continue monitoring the thimble db for long runners, and start looking at some of the other dbs for similar easy wins.</div><div><br></div><div>Cheers,</div><div>Your DBA</div></div><div>
<div><div class="gmail_extra">
<br><br><div class="gmail_quote">On Tue, Feb 11, 2014 at 11:07 AM, JP Schneider <span dir="ltr"><<a href="mailto:jp@mozillafoundation.org" target="_blank">jp@mozillafoundation.org</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'll mirror those in the bugs, but it is evident that the t1.micros we threw in last week were not up to handling thimble.<div><img src="cid:ii_14421e162908cbe0" alt="Inline image 1" width="420" height="233"><br>
</div><div><br></div><div>So then the question becomes: What is stressing the db? We have a few clues to go on, and ways to get more clues.</div><div><br></div><div>1) The LegacyThimble database likely needs some indexing for use on the /p/:oldid path. The smaller instance size exacerbated the issue, but this looks like a potential root cause of stressing the db. </div>
<div><img src="cid:ii_14421e3e53b54d89" alt="Inline image 2" width="420" height="304"><br></div><div>2) Slow query log is currently off, and we can enable it. However, this will take a minute or so of downtime, so I am holding off during the day.</div>
<div><br></div><div>3) I have a script running on our monitoring box that is checking the processlist every 20 seconds to try to grab the offending query.</div><div><br></div><div><br></div></div><div><div>
<div class="gmail_extra">
<br><br><div class="gmail_quote">On Tue, Feb 11, 2014 at 10:50 AM, Jon Buckley <span dir="ltr"><<a href="mailto:jon@mozillafoundation.org" target="_blank">jon@mozillafoundation.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div style="word-wrap:break-word"><div>JP is putting his findings in <a href="https://bugzilla.mozilla.org/show_bug.cgi?id=970833" target="_blank">https://bugzilla.mozilla.org/show_bug.cgi?id=970833</a></div><div><div>
<br><div><div>On Feb 11, 2014, at 11:03 AM, David Ascher <<a href="mailto:da@mozilla.com" target="_blank">da@mozilla.com</a>> wrote:</div><br><blockquote type="cite"><div style="word-wrap:break-word">do we know why the db was pegged?<div>
<br></div><div>—da</div><div><br><div><div>On Feb 11, 2014, at 7:11 AM, David Humphrey <<a href="mailto:david.humphrey@senecacollege.ca" target="_blank">david.humphrey@senecacollege.ca</a>> wrote:</div><br><blockquote type="cite">
<div bgcolor="#FFFFFF" text="#000000">
<div>Great work, JP, thanks.<br>
<br>
On 14-02-11 8:28 AM, JP Schneider wrote:<br>
</div>
<blockquote type="cite">
<div dir="ltr">I have now fully recovered thimble. I did so by
resizing the existing thimble database, so we have had no data
loss.
<div><br>
</div>
<div>In total, we were down from <span style="font-family:arial,sans-serif;font-size:13.333333015441895px">12:17:22(UTC)
- 13:26:00(UTC). The app itself was available, but no user
data was able to make it to our database.</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13.333333015441895px"><br>
</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13.333333015441895px"><br>
</span></div>
</div>
<div class="gmail_extra">
<br>
<br>
<div class="gmail_quote">On Tue, Feb 11, 2014 at 7:20 AM, JP
Schneider <span dir="ltr"><<a href="mailto:jp@mozillafoundation.org" target="_blank">jp@mozillafoundation.org</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">Now that I am the process of recovering this,
some additional details.
<div><br>
</div>
<div>It looks like our db was a bit stressed:</div>
<div><span><ATT00001.jpeg></span><br>
</div>
<div><br>
</div>
<div>After forcing a failover, I was able to connect once
again.</div>
<div>I am currently resizing this instance back to an
m1.small.</div>
</div>
<div>
<div>
<div class="gmail_extra">
<br>
<br>
<div class="gmail_quote">On Tue, Feb 11, 2014 at 7:02
AM, JP Schneider <span dir="ltr"><<a href="mailto:jp@mozillafoundation.org" target="_blank">jp@mozillafoundation.org</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">Good morning,
<div><br>
</div>
<div>There seems to have been some corruption in
our Thimble production database. I am looking
into this now.</div>
<div><br>
</div>
<div>The symptom to the end user seems to be
when an item is published, no URL is given for
your new make.</div>
</div>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</div>
</blockquote>
<br>
</div>
_______________________________________________<br>Webmaker-dev mailing list<br><a href="mailto:Webmaker-dev@mozilla.org" target="_blank">Webmaker-dev@mozilla.org</a><br><a href="https://mail.mozilla.org/listinfo/webmaker-dev" target="_blank">https://mail.mozilla.org/listinfo/webmaker-dev</a><br>
</blockquote></div><br></div></div>_______________________________________________<br>Webmaker-dev mailing list<br><a href="mailto:Webmaker-dev@mozilla.org" target="_blank">Webmaker-dev@mozilla.org</a><br><a href="https://mail.mozilla.org/listinfo/webmaker-dev" target="_blank">https://mail.mozilla.org/listinfo/webmaker-dev</a><br>
</blockquote></div><br></div></div></div><br>_______________________________________________<br>
Webmaker-dev mailing list<br>
<a href="mailto:Webmaker-dev@mozilla.org" target="_blank">Webmaker-dev@mozilla.org</a><br>
<a href="https://mail.mozilla.org/listinfo/webmaker-dev" target="_blank">https://mail.mozilla.org/listinfo/webmaker-dev</a><br>
<br></blockquote></div><br></div>
</div></div></blockquote></div><br></div>
</div></div></blockquote></div><br></div>
</div></div></blockquote></div><br></div>