<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
In the past I experienced those problems when one of the tables
(assets) became corrupted and lost its primary key. I still don't
know what prompted that to happen; it may have been a DB crash
followed by a restore from backup that didn't go well. All I know is
that the assets table suddenly didn't have a primary key. That made
the entire thing run awful.<br>
<br>
On 4/6/2012 10:53 AM, Gudule Lapointe wrote:
<blockquote
cite="mid:985D7240-3669-4F38-838A-0C80C34CE6F1@spekuloos.be"
type="cite">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 moz-do-not-send="true"
href="http://www.speculoos.net/">http://www.speculoos.net/</a></div>
<div><a moz-do-not-send="true"
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>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Opensim-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Opensim-users@lists.berlios.de">Opensim-users@lists.berlios.de</a>
<a class="moz-txt-link-freetext" href="https://lists.berlios.de/mailman/listinfo/opensim-users">https://lists.berlios.de/mailman/listinfo/opensim-users</a></pre>
</blockquote>
<br>
</body>
</html>