Database cleanup

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Database cleanup

Karen_Palen
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
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

LaeMing
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
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

Karen_Palen
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 <[hidden email]> wrote:

> From: LaeMing <[hidden email]>
> Subject: Re: [Opensim-users] Database cleanup
> To: [hidden email]
> 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
> > [hidden email]
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>


     
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

William Magee
I use phpmyadmin to fiddle around in MySQL. It provides a gui for your
databases. I use Ubuntu; not sure what you might use with some other
operating system, but I am sure there are options.

On Tue, Dec 22, 2009 at 2:07 PM, Karen Palen <[hidden email]> 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 <[hidden email]> wrote:
>
>> From: LaeMing <[hidden email]>
>> Subject: Re: [Opensim-users] Database cleanup
>> To: [hidden email]
>> 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
>> > [hidden email]
>> > https://lists.berlios.de/mailman/listinfo/opensim-users
>> >
>>
>> _______________________________________________
>> Opensim-users mailing list
>> [hidden email]
>> https://lists.berlios.de/mailman/listinfo/opensim-users
>>
>
>
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>



--
William Magee, Ph.D.
XVIth Congress of the IABS, Planning Committee Chair
Dharma Drum Buddhist College
Jinshan, Taiwan
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

Paul Fishwick
In reply to this post by Karen_Palen
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 <[hidden email]> wrote:
>
>  
>> From: LaeMing <[hidden email]>
>> Subject: Re: [Opensim-users] Database cleanup
>> To: [hidden email]
>> 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
>>> [hidden email]
>>> https://lists.berlios.de/mailman/listinfo/opensim-users
>>>
>>>      
>> _______________________________________________
>> Opensim-users mailing list
>> [hidden email]
>> https://lists.berlios.de/mailman/listinfo/opensim-users
>>
>>    
>
>
>      
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
>  


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

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

justincc
In reply to this post by Karen_Palen
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!

Dumping OARs and IARs would work but it would be tedious.

For a long time I've wanted to write a tool that would effectively do this
automatically, identifying the cruft and dumping it via the same mechanisms that
are used to identify the required asset chains in OAR and IAR saves.  This
wouldn't be too hard to do for a standalone.  It would be okay for very small
grids (as long as they can be taken offline for a period).

The larger the grid, the more difficult it gets.  This simplistic dependency
identification approach seems near impossible for "open" grids (those allowing
3rd party region simulators).

Unfortunately, such a tool is way back on my pro bono list, way behind all the
other interesting things I've discussed recently but haven't done yet :)

>
> Karen
>
> --- On Mon, 12/21/09, LaeMing <[hidden email]> wrote:
>
>> From: LaeMing <[hidden email]>
>> Subject: Re: [Opensim-users] Database cleanup
>> To: [hidden email]
>> 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
>>> [hidden email]
>>> https://lists.berlios.de/mailman/listinfo/opensim-users
>>>
>> _______________________________________________
>> Opensim-users mailing list
>> [hidden email]
>> https://lists.berlios.de/mailman/listinfo/opensim-users
>>
>
>
>      
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>


--
Justin Clark-Casey (justincc)
http://justincc.org
http://twitter.com/justincc
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

Michael-9-3
This thread got me wondering, and I started poking at my DBs.

First, please note that I only host my regions at OSgrid... I don't have to deal with grid service DBs. Only my simulator's DBs. And I'm *assuming* that some cleanup does take place for these... that when a prim is rezzed it's added to the simulator's DB, and when it's removed or taken back into inventory it's deleted from the DB. At least, my DBs give me this impression, or they'd be monstrous at the rate I rez and delete stuff. :P

Though there's oddities. Not long after I began hosting regions on OSgrid (Feb. 2009), I acquired a bit of furniture with the name Cushioned Stool. Now, I could swear I only have one of these rezzed at the moment. Yet a query on my DB brings up 32 rows, each with it's own UUID, all with the same RegionUUID (which is expected), and some have a non-zero GroupUUID (huh, that's unexpected). The only other way each of these differ is position and rotation values.

I have no idea why this is, or whether it's safe to remove any of these rows. Worse, a query for the name "Primitive" brings up 8000+ rows, and I have no idea how many might be erroneous, or how to tell which are. Some guidance from someone well informed on the opensim databases would definitely be helpful. If a wiki page about the sim DBs exists, a link might be helpful too. :)

I've created shell scripts to automate and schedule certain tasks, and would love to make one for cleaning up kipple if I only knew what I was looking for.

Marcus Llewellyn
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

Michael-9-3
Okay... an apology! I'm very red in the face about this...

I have 32 rows for "Cushioned Stool" because they do exist, rezzed, in world. I was mistaken about the name of the item I had in mind. <D'oh!!!>

One suggestion that came to mind for me, though, for people looking to clean up is to check to see if you've either removed/replaced a region, or given a region a new UUID. I imagine that, in either of these circumstances, there's nothing telling your simulator to remove prims that have that RegionUUID from the DB. I've realized that I can cut about 4000 rows out of mine by doing just this.

Marcus Llewellyn

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

Karen_Palen
In reply to this post by Paul Fishwick
As someone else points out the IAR/OAR approach is workable but slow.

This looks like I need to do some more hunting.

Karen

--- On Tue, 12/22/09, Paul Fishwick <[hidden email]> wrote:

> From: Paul Fishwick <[hidden email]>
> Subject: Re: [Opensim-users] Database cleanup
> To: [hidden email]
> Date: Tuesday, December 22, 2009, 8:28 AM
> 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 <[hidden email]>
> wrote:
> >
> >   
> >> From: LaeMing <[hidden email]>
> >> Subject: Re: [Opensim-users] Database cleanup
> >> To: [hidden email]
> >> 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
> >>> [hidden email]
> >>> https://lists.berlios.de/mailman/listinfo/opensim-users
> >>>
> >>>       
> >> _______________________________________________
> >> Opensim-users mailing list
> >> [hidden email]
> >> https://lists.berlios.de/mailman/listinfo/opensim-users
> >>
> >>     
> >
> >
> >       
> > _______________________________________________
> > Opensim-users mailing list
> > [hidden email]
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >   
>
>
> --
> Paul Fishwick, PhD
> Professor
> University of Florida
> CISE Department, CSE 301
> Gainesville, FL 32611
> Email: [hidden email]
> Web: http://www.cise.ufl.edu/~fishwick
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>


     
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

Karen_Palen
In reply to this post by William Magee
I use the same set-up (MySql Admin/Ubuntu). Works nicely, but I have to believe that the job would be cleaner and safer with the usage and cross reference knowledge in the OpenSim app!

I am very cautious about fiddling witht he contents of databases directly there are too many subtle things that can get screwed up!

Karen
--- On Mon, 12/21/09, William Magee <[hidden email]> wrote:

> From: William Magee <[hidden email]>
> Subject: Re: [Opensim-users] Database cleanup
> To: [hidden email]
> Date: Monday, December 21, 2009, 11:32 PM
> I use phpmyadmin to fiddle around in
> MySQL. It provides a gui for your
> databases. I use Ubuntu; not sure what you might use with
> some other
> operating system, but I am sure there are options.
>
> On Tue, Dec 22, 2009 at 2:07 PM, Karen Palen <[hidden email]>
> 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 <[hidden email]>
> wrote:
> >
> >> From: LaeMing <[hidden email]>
> >> Subject: Re: [Opensim-users] Database cleanup
> >> To: [hidden email]
> >> 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
> >> > [hidden email]
> >> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >> >
> >>
> >> _______________________________________________
> >> Opensim-users mailing list
> >> [hidden email]
> >> https://lists.berlios.de/mailman/listinfo/opensim-users
> >>
> >
> >
> >
> > _______________________________________________
> > Opensim-users mailing list
> > [hidden email]
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
>
>
>
> --
> William Magee, Ph.D.
> XVIth Congress of the IABS, Planning Committee Chair
> Dharma Drum Buddhist College
> Jinshan, Taiwan
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>


     
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

Karen_Palen
In reply to this post by Michael-9-3
I definitely have the remove/replace region problem. I suspect that I have more than that too though :-)

Karen

--- On Tue, 12/22/09, Michael <[hidden email]> wrote:

> From: Michael <[hidden email]>
> Subject: Re: [Opensim-users] Database cleanup
> To: [hidden email]
> Date: Tuesday, December 22, 2009, 2:34 PM
> Okay... an apology! I'm very red in the
> face about this...
>
> I have 32 rows for "Cushioned Stool" because they
> do exist, rezzed, in world. I was mistaken about the name of
> the item I had in mind. <D'oh!!!>
>
>
> One suggestion that came to mind for me, though, for people
> looking to clean up is to check to see if you've either
> removed/replaced a region, or given a region a new UUID. I
> imagine that, in either of these circumstances, there's
> nothing telling your simulator to remove prims that have
> that RegionUUID from the DB. I've realized that I can
> cut about 4000 rows out of mine by doing just this.
>
>
> Marcus Llewellyn
>
>
> -----Inline Attachment Follows-----
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>


     
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

William Magee
In reply to this post by Karen_Palen
Oh, Karen sorry I did not see that it was you when I mentioned
phpmyadmin. Of course I know you are an Ubuntu user.

Have you considered making a copy and running database experiments
safely on a cloned sim?

Cheers,
Bill

On Wed, Dec 23, 2009 at 8:24 AM, Karen Palen <[hidden email]> wrote:

> I use the same set-up (MySql Admin/Ubuntu). Works nicely, but I have to believe that the job would be cleaner and safer with the usage and cross reference knowledge in the OpenSim app!
>
> I am very cautious about fiddling witht he contents of databases directly there are too many subtle things that can get screwed up!
>
> Karen
> --- On Mon, 12/21/09, William Magee <[hidden email]> wrote:
>
>> From: William Magee <[hidden email]>
>> Subject: Re: [Opensim-users] Database cleanup
>> To: [hidden email]
>> Date: Monday, December 21, 2009, 11:32 PM
>> I use phpmyadmin to fiddle around in
>> MySQL. It provides a gui for your
>> databases. I use Ubuntu; not sure what you might use with
>> some other
>> operating system, but I am sure there are options.
>>
>> On Tue, Dec 22, 2009 at 2:07 PM, Karen Palen <[hidden email]>
>> 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 <[hidden email]>
>> wrote:
>> >
>> >> From: LaeMing <[hidden email]>
>> >> Subject: Re: [Opensim-users] Database cleanup
>> >> To: [hidden email]
>> >> 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
>> >> > [hidden email]
>> >> > https://lists.berlios.de/mailman/listinfo/opensim-users
>> >> >
>> >>
>> >> _______________________________________________
>> >> Opensim-users mailing list
>> >> [hidden email]
>> >> https://lists.berlios.de/mailman/listinfo/opensim-users
>> >>
>> >
>> >
>> >
>> > _______________________________________________
>> > Opensim-users mailing list
>> > [hidden email]
>> > https://lists.berlios.de/mailman/listinfo/opensim-users
>> >
>>
>>
>>
>> --
>> William Magee, Ph.D.
>> XVIth Congress of the IABS, Planning Committee Chair
>> Dharma Drum Buddhist College
>> Jinshan, Taiwan
>> _______________________________________________
>> Opensim-users mailing list
>> [hidden email]
>> https://lists.berlios.de/mailman/listinfo/opensim-users
>>
>
>
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>



--
William Magee, Ph.D.
XVIth Congress of the IABS, Planning Committee Chair
Dharma Drum Buddhist College
Jinshan, Taiwan
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

Michael-9-3
In reply to this post by Karen_Palen
Inactive regions are pretty easy to fix. But, as always, a backup is recommended before poking that thar bear with a stick. :)

Ya just need to take your favorite database tool, and connect to your OpenSim db. First you'll need the UUIDs of any inactive regions. If you no longer have a record of this somewhere (say, in an old XML or INI region file) you can compare the UUIDs from your active regions to the results of the following query, and take note of the ones that are dead:

SELECT RegionID FROM estate_map;

Looks to me like there's only really two tables to worry about here named "prims" and "primshapes". We'll start with safe queries that don't actually change anything, so you can get a feel for whether this is worthwhile for you. We'll also only do one stale region for the sake of simplicity. Let's start by seeing just how many rows you might be able to remove. Run this simple query:

SELECT COUNT(*) FROM prims WHERE RegionUUID = '<OldRegionUUID>';

Replace <OldRegionUUID> with the inactive region's UUID. That query will return a number  equaling the number of stale prims sitting in that table.

That's the prims table, which is easiest since each prim has a RegionUUID column. The primshapes table doesn't, so we'll have to use the prims table to figure out which shapes are stale. The following query will do this for you:

SELECT COUNT(*)
    FROM primshapes
    WHERE EXISTS
    (SELECT * FROM prims WHERE
    prims.RegionUUID = '<OldRegionUUID>'
    AND prims.UUID = primshapes.UUID);

This *should* return the exact same number for primshapes that that you got from the query to the prims table. If you do not, stop right here, and don't make any changes to your database. If both counts are the same though, you now know how many rows you can remove by combining the two counts. So now we'll remove those rows. Make sure you have that backup before the next steps.

Now we'll remove the stale prims from the primshapes table. It's important to do this with primshapes before modifying the prims table. The following query will do so:

DELETE FROM primshapes
    WHERE EXISTS
    (SELECT * FROM prims WHERE
    prims.RegionUUID = '<OldRegionUUID>'
    AND prims.UUID = primshapes.UUID);

Finally, the query to remove them from the prims table:

DELETE FROM prims WHERE RegionUUID = '<OldRegionUUID>';

And there you have it. These queries removed over 9000 rows from my little OpenSim database. You can also remove those stale region UUIDs from the estate_* tables, but that should be pretty easy to do via a GUI of your choice since these tables tend to be very small in comparison to the prim tables. I just left them out of sheer laziness... they're a single row, so the space they take up is insignificant.

Marcus Llewellyn


On Tue, Dec 22, 2009 at 6:27 PM, Karen Palen <[hidden email]> wrote:
I definitely have the remove/replace region problem. I suspect that I have more than that too though :-)

Karen


_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

Karen_Palen
In reply to this post by William Magee
Actually I was hoping to find some neat script somewhere that just fixed the problem forever.  

Well I can dream at least ...

They say that a true programmer is someone who spends a week automating a 20 minute problem and feels good about the result :-)

I suspect that I will have to end up with multiple databases though. An "experimental" one and a "build things" one.

Probably isolated in dedicated virtual machines like I do with software tryouts.

It is amazing how many Windows problems go away when an app is the ONLY one installed on a VM for example. That strategy has also served me well in isolating trojans and "backdoors" in commercial software. So far no major packages (like the infamous Turbo-Tax DRM that permanently crippled your machine a few years ago), but I use and beta test a wide range of stuff and it amazing what you find sometimes.

Karen

--- On Tue, 12/22/09, William Magee <[hidden email]> wrote:

> From: William Magee <[hidden email]>
> Subject: Re: [Opensim-users] Database cleanup
> To: [hidden email]
> Date: Tuesday, December 22, 2009, 7:50 PM
> Oh, Karen sorry I did not see that it
> was you when I mentioned
> phpmyadmin. Of course I know you are an Ubuntu user.
>
> Have you considered making a copy and running database
> experiments
> safely on a cloned sim?
>
> Cheers,
> Bill
>
> On Wed, Dec 23, 2009 at 8:24 AM, Karen Palen <[hidden email]>
> wrote:
> > I use the same set-up (MySql Admin/Ubuntu). Works
> nicely, but I have to believe that the job would be cleaner
> and safer with the usage and cross reference knowledge in
> the OpenSim app!
> >
> > I am very cautious about fiddling witht he contents of
> databases directly there are too many subtle things that can
> get screwed up!
> >
> > Karen
> > --- On Mon, 12/21/09, William Magee <[hidden email]>
> wrote:
> >
> >> From: William Magee <[hidden email]>
> >> Subject: Re: [Opensim-users] Database cleanup
> >> To: [hidden email]
> >> Date: Monday, December 21, 2009, 11:32 PM
> >> I use phpmyadmin to fiddle around in
> >> MySQL. It provides a gui for your
> >> databases. I use Ubuntu; not sure what you might
> use with
> >> some other
> >> operating system, but I am sure there are
> options.
> >>
> >> On Tue, Dec 22, 2009 at 2:07 PM, Karen Palen
> <[hidden email]>
> >> 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 <[hidden email]>
> >> wrote:
> >> >
> >> >> From: LaeMing <[hidden email]>
> >> >> Subject: Re: [Opensim-users] Database
> cleanup
> >> >> To: [hidden email]
> >> >> 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
> >> >> > [hidden email]
> >> >> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >> >> >
> >> >>
> >> >>
> _______________________________________________
> >> >> Opensim-users mailing list
> >> >> [hidden email]
> >> >> https://lists.berlios.de/mailman/listinfo/opensim-users
> >> >>
> >> >
> >> >
> >> >
> >> >
> _______________________________________________
> >> > Opensim-users mailing list
> >> > [hidden email]
> >> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >> >
> >>
> >>
> >>
> >> --
> >> William Magee, Ph.D.
> >> XVIth Congress of the IABS, Planning Committee
> Chair
> >> Dharma Drum Buddhist College
> >> Jinshan, Taiwan
> >> _______________________________________________
> >> Opensim-users mailing list
> >> [hidden email]
> >> https://lists.berlios.de/mailman/listinfo/opensim-users
> >>
> >
> >
> >
> > _______________________________________________
> > Opensim-users mailing list
> > [hidden email]
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
>
>
>
> --
> William Magee, Ph.D.
> XVIth Congress of the IABS, Planning Committee Chair
> Dharma Drum Buddhist College
> Jinshan, Taiwan
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>


     
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

Karen_Palen
In reply to this post by Michael-9-3
Thank you, I am in the process of setting up a VM on another machine with a cloned database and this looks like a good chance to try this out.

I agree that 90% of the actual space taken is prims however like tracking memory leaks on software even a small one will eventually cause major problems.

I think Bill is right that I need at least separate databases for "try weird things" and "build things" and probably more. :-)

I have a very long history of sticking virtual screwdrivers in things to see what happens, but I have learned to do that on a copy (well most of the time) :-) That compulsion did cause some excitement early in my Engineering career when I work d on heavy machinery ...

I will let you know how it turns out.

Karen

--- On Wed, 12/23/09, Michael <[hidden email]> wrote:

> From: Michael <[hidden email]>
> Subject: Re: [Opensim-users] Database cleanup
> To: [hidden email]
> Date: Wednesday, December 23, 2009, 12:21 AM
> Inactive regions are pretty easy to fix.
> But, as always, a backup is recommended before poking that
> thar bear with a stick. :)
> Ya just need to take your favorite database
> tool, and connect to your OpenSim db. First you'll need
> the UUIDs of any inactive regions. If you no longer have a
> record of this somewhere (say, in an old XML or INI region
> file) you can compare the UUIDs from your active regions to
> the results of the following query, and take note of the
> ones that are dead:
>
> SELECT RegionID FROM estate_map;
> Looks to me like there's only really two
> tables to worry about here named "prims" and
> "primshapes". We'll start with safe queries
> that don't actually change anything, so you can get a
> feel for whether this is worthwhile for you. We'll also
> only do one stale region for the sake of simplicity.
> Let's start by seeing just how many rows you might be
> able to remove. Run this simple query:
>
> SELECT COUNT(*) FROM prims WHERE RegionUUID =
> '<OldRegionUUID>';
> Replace <OldRegionUUID> with the inactive
> region's UUID. That query will return a number
>  equaling the number of stale prims sitting in that
> table.
>
> That's the prims table, which is easiest
> since each prim has a RegionUUID column. The primshapes
> table doesn't, so we'll have to use the prims table
> to figure out which shapes are stale. The following query
> will do this for you:
>
> SELECT COUNT(*)    FROM
> primshapes    WHERE EXISTS  
>  (SELECT * FROM prims WHERE  
>  prims.RegionUUID =
> '<OldRegionUUID>'    AND
> prims.UUID = primshapes.UUID);
>
> This *should* return the exact same number for
> primshapes that that you got from the query to the prims
> table. If you do not, stop right here, and don't make
> any changes to your database. If both counts are the same
> though, you now know how many rows you can remove by
> combining the two counts. So now we'll remove those
> rows. Make sure you have that backup before the next
> steps.
>
> Now we'll remove the stale prims from the
> primshapes table. It's important to do this with
> primshapes before modifying the prims table. The following
> query will do so:
>
> DELETE FROM primshapes    WHERE
> EXISTS    (SELECT * FROM prims
> WHERE    prims.RegionUUID =
> '<OldRegionUUID>'    AND
> prims.UUID = primshapes.UUID);
>
> Finally, the query to remove them from the prims
> table:
> DELETE FROM prims WHERE RegionUUID =
> '<OldRegionUUID>';
> And there you have it. These queries removed
> over 9000 rows from my little OpenSim database. You can also
> remove those stale region UUIDs from the estate_* tables,
> but that should be pretty easy to do via a GUI of your
> choice since these tables tend to be very small in
> comparison to the prim tables. I just left them out of sheer
> laziness... they're a single row, so the space they take
> up is insignificant.
>
> Marcus Llewellyn
>
> On Tue, Dec 22, 2009 at 6:27 PM,
> Karen Palen <[hidden email]>
> wrote:
>
> I
> definitely have the remove/replace region problem. I suspect
> that I have more than that too though :-)
>
>
>
> Karen
>
>
>
> -----Inline Attachment Follows-----
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>


     
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: Database cleanup

Karen_Palen
In reply to this post by Michael-9-3
That did the trick, - thank you!

However, a closer look reveals that the inactive regions are only a small part of the problem :-( Only about 2Mb of the 600Mb total!

500Mb is devoted to assets - 9500 of them! Over half textures and a great many duplicates (to judge from the descriptions anyway).

I don't see any easy fix by inspection, since the assets depend on other tables to identify if they are used.

Karen

--- On Wed, 12/23/09, Michael <[hidden email]> wrote:

> From: Michael <[hidden email]>
> Subject: Re: [Opensim-users] Database cleanup
> To: [hidden email]
> Date: Wednesday, December 23, 2009, 12:21 AM
> Inactive regions are pretty easy to fix.
> But, as always, a backup is recommended before poking that
> thar bear with a stick. :)
> Ya just need to take your favorite database
> tool, and connect to your OpenSim db. First you'll need
> the UUIDs of any inactive regions. If you no longer have a
> record of this somewhere (say, in an old XML or INI region
> file) you can compare the UUIDs from your active regions to
> the results of the following query, and take note of the
> ones that are dead:
>
> SELECT RegionID FROM estate_map;
> Looks to me like there's only really two
> tables to worry about here named "prims" and
> "primshapes". We'll start with safe queries
> that don't actually change anything, so you can get a
> feel for whether this is worthwhile for you. We'll also
> only do one stale region for the sake of simplicity.
> Let's start by seeing just how many rows you might be
> able to remove. Run this simple query:
>
> SELECT COUNT(*) FROM prims WHERE RegionUUID =
> '<OldRegionUUID>';
> Replace <OldRegionUUID> with the inactive
> region's UUID. That query will return a number
>  equaling the number of stale prims sitting in that
> table.
>
> That's the prims table, which is easiest
> since each prim has a RegionUUID column. The primshapes
> table doesn't, so we'll have to use the prims table
> to figure out which shapes are stale. The following query
> will do this for you:
>
> SELECT COUNT(*)    FROM
> primshapes    WHERE EXISTS  
>  (SELECT * FROM prims WHERE  
>  prims.RegionUUID =
> '<OldRegionUUID>'    AND
> prims.UUID = primshapes.UUID);
>
> This *should* return the exact same number for
> primshapes that that you got from the query to the prims
> table. If you do not, stop right here, and don't make
> any changes to your database. If both counts are the same
> though, you now know how many rows you can remove by
> combining the two counts. So now we'll remove those
> rows. Make sure you have that backup before the next
> steps.
>
> Now we'll remove the stale prims from the
> primshapes table. It's important to do this with
> primshapes before modifying the prims table. The following
> query will do so:
>
> DELETE FROM primshapes    WHERE
> EXISTS    (SELECT * FROM prims
> WHERE    prims.RegionUUID =
> '<OldRegionUUID>'    AND
> prims.UUID = primshapes.UUID);
>
> Finally, the query to remove them from the prims
> table:
> DELETE FROM prims WHERE RegionUUID =
> '<OldRegionUUID>';
> And there you have it. These queries removed
> over 9000 rows from my little OpenSim database. You can also
> remove those stale region UUIDs from the estate_* tables,
> but that should be pretty easy to do via a GUI of your
> choice since these tables tend to be very small in
> comparison to the prim tables. I just left them out of sheer
> laziness... they're a single row, so the space they take
> up is insignificant.
>
> Marcus Llewellyn
>
> On Tue, Dec 22, 2009 at 6:27 PM,
> Karen Palen <[hidden email]>
> wrote:
>
> I
> definitely have the remove/replace region problem. I suspect
> that I have more than that too though :-)
>
>
>
> Karen
>
>
>
> -----Inline Attachment Follows-----
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>


     
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Disposed PhysActors / *waves finger*, Don't be evil

drwhiet@spacefriends.de
Hello,

Mayby somebody on the list here has knowledge or could think about an
solution.
Since some weeks now have a strange RED message  in the Console Window :

02:50:48 - [PHYSICS]: The scene reused a disposed PhysActor! *waves finger*,
Don't be evil.  A couple of things can cause this.  An improper prim
breakdown(be sure to set prim_geom to zero after d.GeomDestroy!   An
improper buildup (creating the geom failed).  
Or, the Scene Reused a physics actor after disposing it.)

This happens on Windows Xp 32bit and also on Windows 2003 Server 64bit. It
happens after
some times the Sim is up and running. It sucks away slowly the memory of the
machine.
This error throws on the last 3 offical releases (no experimental branches /
gits)

my first thought was (as i was playing with some random movement scripts)
that a prim
has moved to a location unreachable and I tried to isolated the problem by
moving all
reachable items (i) to a fresh simulator. But problems still occurs.

The sim is just running a bunch of rotator scripts (all plain cube prims) ..

and about 5-6 sculptie rocks ? As there is no information about the evil
item ..

what can i do ?

best regards
and quiet peacefull days ..

Wordfromthe Wise
(regions connected to the Osgrid)

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users