[Opensim-users] Database clean up

[Diva Canto]

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.

On 4/6/2012 10:53 AM, 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.
>
>
>
> 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.
>
> 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
> - which tables I can empty after having made a successful oar save of 
> my regions
> - 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?
>
> 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).
>
> --
> 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

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