[Opensim-users] Database cleanup

[Paul Fishwick]

Karen
 This is really good point, and it would be interesting to know more 
about how OSGrid
manages the cruft that seems to accumulate over time. Apparently, 
OpenSim does not
remove assets. So, if you delete assets from a region, they still remain 
in the asset table
taking up room. Just in 3 months of me and two others occasionally going 
in and doing
minor work in a 2x2 region world, the sql dump (which captures 
everything in a database)
 has increased from 100MB to 152MB.
 What is needed is:

  * an equivalent to a registry clean -- a "clean region" or just plain 
"clean" that removes all
     dangling assets (meaning that when something is removed on a 
region, it is removed
     completely from the database)

  * a 'delete user' command which deletes not only the entry in the user 
table but also all inventory owned
     by that user and all asset references in inventory which are not on 
the estate, and which are not
     connected to any other inventory.

  The question is whether this can be done with SQL queries, or whether 
a program would need
to be written using the mySQL api. Anyway, it is on my long list of 
things to get students to do
as I get them interested in OpenSim.
   You may be right, though, that the easiest solution is to save OAR 
and save IAR for each user
and rebuild from there. In theory, this should result in a smaller database.

-paul


Karen Palen wrote:
> That is one way to solve the problem, although I don't know MySQL well enough to figure out how!
>
> I was actually hoping that OpenSim had some built in utility that would do this in a more intelligent way though. Essentially the knowledge about what is deleted or not is fairly high level information within OpenSim itself. A "touch" would at best be an emulation of this.
>
> Ideally this would run regularly and keep down the "cruft" build-up.
>
> Next best would be to dump each region to an OAR file, each avatar's inventory to an IAR file then build a new database from that! It would work, but could take several days of effort to make sure that it was done right. My next thought is to use Second Inventory to do the same thing, but that could be even slower!
>
> Karen
>
> --- On Mon, 12/21/09, LaeMing <laeming at exemail.com.au> wrote:
>
>   
>> From: LaeMing <laeming at exemail.com.au>
>> Subject: Re: [Opensim-users] Database cleanup
>> To: opensim-users at lists.berlios.de
>> Date: Monday, December 21, 2009, 7:34 PM
>> Hmmm. When I was in the DB manually
>> editing all my regions' ground textures 
>> and so (36 region megaregion, this was far quicker than
>> doing it in-sim), I 
>> noticed a lot of old regiondata from earlier iterations of
>> my OpenSim 
>> experiments hanging about too. I was able to hand-delete
>> all the unwanted 
>> regioninfo from the mySql tools, but the tools didn't want
>> to let me enter 
>> edit mode for the binary blobs of region heightmaps, so I
>> couldn't work out 
>> how to hand-delete those.
>>
>> I imagine, like Karen, there is also a lot of dangling
>> cruft built up in my 
>> inventory too. Since I am the only user for now, would it
>> be fair to say 
>> anything in world or in my current inventory has been
>> 'touched' last login, 
>> and assuming mySql does (or can) flag touch dates for
>> entries, could I just 
>> find the method of deleting all entries untouched since my
>> last login (after a 
>> full backup of course)?
>>
>> Lae
>>
>>
>> On Tuesday December 22 2009 09:52:22 Karen Palen wrote:
>>     
>>> My OpenSim database now tops 1Gb with only a single
>>>       
>> region!
>>     
>>> Looking at the database with MySql tools shows that
>>>       
>> there is a whole lot of
>>     
>>>   stuff that I have tried and discarded still in
>>>       
>> the database and that it
>>     
>>>   does not seem to be flagged for removal.
>>>
>>> Is there some OpenSim database maintenance tool that I
>>>       
>> haven't found yet?
>>     
>>> How DO people like OSGrid keep the database to a
>>>       
>> manageable size?
>>     
>>> Karen
>>>
>>>
>>>
>>> _______________________________________________
>>> 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
>>
>>     
>
>
>       
> _______________________________________________
> Opensim-users mailing list
> Opensim-users at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
>   


-- 
Paul Fishwick, PhD
Professor
University of Florida
CISE Department, CSE 301
Gainesville, FL 32611
Email: fishwick at cise.ufl.edu
Web: http://www.cise.ufl.edu/~fishwick