[Opensim-users] Database clean up

[M.E. Verhagen]

maybe it helps when you optize your mysql:

http://www.debianhelp.co.uk/mysqlperformance.htm

Op zaterdag 7 april 2012 schreef Gudule Lapointe (gudule at spekuloos.be) het
volgende:

> Thank you very much Justin for this detailed information.
>
> Actually, the monitoring I made since a couple of days allowed me to
> eliminate a range of possible causes (including network, disk, machine
> overload, etc). My conclusion to focus on mysql performance is far more
> than an assumption, even if I didn't detailed my tests here.
>
> The monitoring tools I have for mysql told me a range of causes of
> performance loose, including the index. But no precise indication on which
> database, with tables or which queries.
>
> The explanations you gave confirm me that the size of the grid (and the
> database) should not be a problem -- in normal operating conditions.
> So I am happy not to have to enter in the adventure to split databases
> right now.
>
> I'll check now if something like db/index corruption is in the air
> (anyway, a db optimization shouldn't hurt). And still investigating on
> other possible causes.
>
>
> --
> http://www.speculoos.net/
> secondlife://speculoos.net:8002/
> Speculoos, the belgian cookie-flavored metaverse
>
> Le 7 avr. 2012 à 03:10, Justin Clark-Casey a écrit :
>
> 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.
>
>
>

-- 
Groningen en Hannover Opensims:   secondlife://meverhagen.nl:8002:Hannover
ZW/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://opensimulator.org/pipermail/opensim-users/attachments/20120407/a144fca3/attachment.html>