[Opensim-users] Database clean up

Sat Apr 7 01:10:19 UTC 2012

On 06/04/12 18:53, Gudule Lapointe wrote:
> I experience lot of timeout problems. I checked every side of the installation, and I suspect the database to be the
> bottleneck.
>
> The main question is: how can I clean up the database? Detail description below…
> Any advice on any part of the problem is welcome.

 From the data below I'm quite surprised you're having problems - this is not a particularly large grid.  I strongly 
recommend actually measuring performance where you can and finding the actual bottleneck, rather than assuming that 
certain things are issues.  You might find that the issue is not actually OpenSimulator related (e.g. a network issue, 
machine overloaded for other reasons, etc).  In particular, I don't think 2.3Gb is all that large for an asset database.

Unfortunately, I'm not aware of tools to measure things such as inventory service response, though in principle they 
would not be all that hard to write.

pCampbot [1] can do some simulator testing where many libomv clients are logged onto a simulator at once, though some of 
its actions are currently highly unrealistic (e.g. logging in 20 bots simultaneously).

I've written up some grid performance discussion and possible solutions at [1].  However, this only covers the issues 
I've personally seen.  In general, scaling a grid is very hard and largely a step into an evolving unknown.  It's also 
the hardest area to work in since diaganosing issues is very time consuming (and not always something I have the time to 
help with, unfortunately, not least because it's an area I'm still learning about).  But again, I don't think your grid 
numbers are actually high enough to encounter the more complex issues.

More comments below.

>
>
>
> 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).
> However it has been working quite fine for more than 3 months, since latest big change.
>
> - version: 0.7.3-post-fixes
> - robust server, with 7 simulators, for a total of 56 regions
> - From these region, I would say 15 à 20 are really active, others are placeholders, without content.
> - About 20 registered users. Usually 3 or 4 concurrent users
> - Each region has it's own mysql database, and robust uses a single one.
>
> Since around 5 days, I get continuous timeout, access to inventory or assets errors and sometimes region crashes.
>
> Though they were no recent change on the set up when the problems began. Hence my suspicions on the database.
>
> (CPU, memory and disk usage don't show any overload)
>
> Regions database are fairly light (~20MB)
> Robust database is huge: 2.6 GB
> I am not sure such a big database is common for setup like ours.

This is not a very unusual size and I don't think that it's a problem to be honest.

>
> So it looks obvious that I should clean up the database, which may contain a lot of outdated items.
> Fair enough. How can I do?
>
> I would like to know
> - which tables I can empty without losses, at all

Nothing that would make any significant difference compared to deleting/deduplicating assets.  There are some (a) 
suggestion for asset dedupe at [1].  Asset deletion is a hard problem, though I think there might be some future stuff 
that can be done by deleting/retiring assets that haven't been accessed for a very, very long time.

> - which tables I can empty after having made a successful oar save of my regions

You could empty the region database, naturally.  I'm assuming you're not storing all region and service data in a single 
database.  Even then there is a list of region tables on the wiki.

To be able to remove all tables you would also need to save IARs for every single user.  I think there could be an 
automatic tool for this but it would require considerable work.  Also, I'm not sure the restoration from whole inventory 
backup story is currently very good.

Even if you save all OARs and IARs you'll still lose things such as home position for users and region script state. 
However, I don't think any of this is absolutely crucial for a data restore (e.g. users can always just reset their home 
positions manually).

> - how can I clean up the inventory and assets server, without having my users loosing their inventory?
> - and any advise on how to optimize mysql server for this usage is welcome
>
> Another clue is to split robust inventory and/or assets servers, so, in this case
> - which tables do I have to split?
> - are there risks of losses if the address/port of inventory and assets server change?
>

Better to split services into separate processes than tables.  See [1] for more information.  The biggest bottleneck 
with services is actually the embedded HTTP server rather than db size, but your grid numbers aren't really high enough 
to hit these issues, imo.

> 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).

To be honest, it wouldn't totally surprise me if there are some index issues and areas where things could be made much 
more efficient.  I don't know how you would track down bad queries, though I'm sure MySQL has some kind of tracking that 
could be done.

HG is another issue entirely.  If foreigners are accessing their content then performance depends on their home services 
and on network performance.  However, this shouldn't affect non-HG users or region operations.

[1] http://opensimulator.org/wiki/Performance#Grid

>
> --
> http://www.speculoos.net/
> secondlife://speculoos.net:8002/
> Speculoos, the belgian cookie-flavored metaverse
>
>
>
> _______________________________________________
> Opensim-users mailing list
> Opensim-users at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-users

-- 
Justin Clark-Casey (justincc)
http://justincc.org/blog
http://twitter.com/justincc