<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">I experience lot of timeout problems. I checked every side of the installation, and I suspect the database to be the bottleneck.<div><br></div><div>The main question is: how can I clean up the database? Detail description below…</div><div>Any advice on any part of the problem is welcome.</div><div><br></div><div><br><div><br></div><div>Current setup comes from an initial test installation, and changed a lot before going to prod (versions changes, server changes, oar save and load, etc).</div><div>However it has been working quite fine for more than 3 months, since latest big change.</div><div><br></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>- version: 0.7.3-post-fixes</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>- robust server, with 7 simulators, for a total of 56 regions</div><div><span class="Apple-tab-span" style="white-space: pre; "> </span>- From these region, I would say 15 à 20 are really active, others are placeholders, without content.</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>- About 20 registered users. Usually 3 or 4 concurrent users</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>- Each region has it's own mysql database, and robust uses a single one.</div><div><br></div><div>Since around 5 days, I get continuous timeout, access to inventory or assets errors and sometimes region crashes.</div><div><br></div><div>Though they were no recent change on the set up when the problems began. Hence my suspicions on the database.</div><div><br></div><div>(CPU, memory and disk usage don't show any overload)</div><div><br></div><div>Regions database are fairly light (~20MB)</div><div>Robust database is huge: 2.6 GB</div><div>I am not sure such a big database is common for setup like ours.</div><div><br></div><div>So it looks obvious that I should clean up the database, which may contain a lot of outdated items.</div><div>Fair enough. How can I do?</div><div><br></div><div>I would like to know </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>- which tables I can empty without losses, at all</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>- which tables I can empty after having made a successful oar save of my regions</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>- how can I clean up the inventory and assets server, without having my users loosing their inventory?</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>- and any advise on how to optimize mysql server for this usage is welcome</div><div><br></div><div>Another clue is to split robust inventory and/or assets servers, so, in this case</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>- which tables do I have to split?</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>- are there risks of losses if the address/port of inventory and assets server change?</div><div><br></div><div>And, last but not least, monitoring sql show warning of lot of queries are made on non-indexes fields of mysql tables. Which is not harmful for marginal queries, but is a big problem for recurring ones. I can't imagine parsing the whole code to guess where there are such recurring queries. Any idea on how to track guilty queries? A clue: it seems to be happening mostly when accessing foreign content (content taken on HG regions, or content used by HG visitor).</div><div><br></div><div apple-content-edited="true">
<span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; font-size: medium; "><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; font-size: medium; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; font-size: medium; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; font-size: medium; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; font-size: medium; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; font-size: medium; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div>--</div><div><a href="http://www.speculoos.net/">http://www.speculoos.net/</a></div><div><a href="secondlife://speculoos.net:8002/">secondlife://speculoos.net:8002/</a></div><div>Speculoos, the belgian cookie-flavored metaverse</div></div></span></div></span></div></span></div></span></div></span></span>
</div>
<br></div></body></html>