[Opensim-users] Database clean up

Sat Apr 7 18:13:57 UTC 2012

Thanks A LOT, guys. Server is back to normal conditions, even faster!

	- thanks to your advices about the grid size and how it should not be harmful, I saved a lot of time, focusing on right things
	- thanks to your advices on mysql optimization, I get an increased speed of 400%
	- after eliminating these issues, I still got errors, but found easily, knowing where *not* to look ;-)

I found a small, little, mini-bikini difference between 0.7.2 and 0.7.3, which I didn't notice before (and I don't remember having read that, but I have read so much docs)

Robust.HG.ini.example in OS 0.7.2:
	SRV_ProfileServerURI = "http://127.0.0.1:8002/user"

Robust.HG.ini.example in OS 0.7.3:
	SRV_ProfileServerURI = "http://127.0.0.1:8002"

Woot! That was the missing link. I changed robust config according, and now it's running smoothly like before!

(So, there *was* a performance issue coming from the last update, but I didn't notice it before mysql began to suck!)

THANK YOU, YOU ROCK!

--
http://www.speculoos.net/
secondlife://speculoos.net:8002/
Speculoos, the belgian cookie-flavored metaverse

Le 7 avr. 2012 à 13:54, Gudule Lapointe a écrit :

> I said
>> anyway, a db optimization shouldn't hurt
> 
> I'd better have broken my leg. Made a check & optimize & repair, optimized my.conf and restarted mysqld. And now nearly all databases are corrupted.
> I know, this is off-topic. Just needed to grumble.
> 
> Thank you Mark Edward for the reference, it will be helpful, once I get my db restored ;-)
> 
> 
> --
> http://www.speculoos.net/
> secondlife://speculoos.net:8002/
> Speculoos, the belgian cookie-flavored metaverse
> 
> Le 7 avr. 2012 à 09:01, M.E. Verhagen a écrit :
> 
>> 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/ 
>> _______________________________________________
>> Opensim-users mailing list
>> Opensim-users at lists.berlios.de
>> https://lists.berlios.de/mailman/listinfo/opensim-users
> 
> _______________________________________________
> Opensim-users mailing list
> Opensim-users at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://opensimulator.org/pipermail/opensim-users/attachments/20120407/ed0a1d12/attachment.html>