[Opensim-users] Database cleanup and purge

[Justin Clark-Casey]

Unfortunately, this is a very hard (maybe impossible) problem in a distributed system.  You would have to chase down all 
asset references that remote regions hold.  This is difficult when the regions are hosted on different machines, let 
alone when they are hosted by different people.

It's more feasible on a standalone, since all the data is in one database.  One would need to write a tool that chases 
down all the references that assets and inventory items hold to each other.  This is essentially what OAR/IAR saving 
does, so one could reuse the code in OpenSim if one is using a CLR language.  This could run as a separate executable - 
it's probably not suitable to be in the runtime itself.

However, it has restricted application, which is probably why it hasn't been done yet.

There are other ways to reduce the asset database size.  The chief one is to detect identical assets and eliminate them. 
  This is what Dave Coyle's asset data service SRAS does by calculating hashes for the assets 
(https://github.com/coyled/sras).  The asset service bundled with OpenSim should do this too at some point.

Loading the same OARs and IARs should not bloat the database since the asset is not duplicated if the UUID is the same. 
  If loading an *AR twice can be shown to be expanding the asset db twice, then this is a fileable bug.

On 17/07/11 18:18, Karen Palen wrote:
> Another aspect of the recent discussion about backups is the ability to
> cleanup the database and purge "orphaned" assets.
>
> If you look closely at an OpenSim database that has been in use for  a
> while the assets portion is huge, even if all regions and inventory has
> been deleted.
>
> I first ran across this after loading several huge OAR and IAR files
> multiple times - each time the files were loaded it created an entire
> asset tree in the database. The database grew to 10Gb with only 4 active
> regions!
>
> It seems that when an object/wearable/texture/whatever is deleted then
> all that is actually removed is th epointer entry that refers to the
> underlying data, if all references to that data have been deleted then
> it is left as an orphan. There are very good reasons not to delete these
> orphans from within OpenSim, but at present there is no mechanism to do
> this even offline.
>
> About the best that can be done at present is to dump everything to
> OAR/IAR files and reload then periodically. This is a reasonable
> workaround, but not really a long term solution.
>
> Back when 0.6.8/9 (different database setup) was in use there was an
> effort to create an SQL macro set to do this, but it was shelved as
> 0.7.x came closer.
>
> Is there some other solution out there that I don't know about?
>
> Maybe this would be a worthwhile support project. I have very little SQL
> experience, but a lifetime as a C/C++ programmer and would be willing to
> contribute what I can.
>
> Karen
> _______________________________________________
> 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