[Opensim-users] MySQL on seperate server?

Fri Feb 19 14:38:36 UTC 2010

What about Bob ?  hahahaha   sorry had to say it..  Tension breaker .. ::
laughs ::

On Fri, Feb 19, 2010 at 4:33 AM, Karen Palen <karen_palen at yahoo.com> wrote:

> I think you summarize the problem quite well!
>
> At this point it must be done at the database (SQL) level.
>
> Unfortunately the solution is neither a simple nor straightforward since it
> involves essentially reconstructing the "back links" (or "reverse indexes")
> and identifying the assets which have nothing which refers to them! Somewhat
> analogous to unscrambling an omelet!
>
> Likewise duplicates require comparing MD5 hashes of every asset to find
> identical ones!
>
> Obviously there are short-cuts, but the biggest problem is ensuring
> database integrity and making certain that the correct links are maintained.
>
> The biggest problem comes when a central asset server is used such as found
> in OSGrid. OSGrid (and by implication most large OpenSim grids) are
> essentially dynamic, there is no guarantee that any specific region will be
> connected!
>
> The Second Life version of the problem problem is relatively benign since
> Second Life can assume that all regions are connected so a simple poll of
> the regions should produce an listing of every reference to an asset.
>
> Lacking this "continuous connect" means that some means must be provided to
> preserve references when a region is not connected!
>
> Adding a hypergrided asset server makes this problem even worse.
>
> The "time window" approach seems to be the most reasonable solution to
> these "disconnects". My instincts (developed from 45 years of EDA software!)
> tells me that things like the right "time window" will be  critical and not
> going to be easily determined.
>
> I wonder if there is a better solution?
>
> Bob Wellman has at least a preliminary solution to the simpler cases,
> hopefully his solution can be extended to the more complex ones.
>
> No doubt this will prove itself in testing. We can certainly expect some
> "interesting" surprises which no one has thought about along the way though.
>
> The usual caveats about alpha code apply!
>
> DISCLAIMER: Bob is doing this part time so please don't inundate him with
> requests for his code or for specific features! I am sure offers for help
> would be appreciated though.
>
> Karen
>
> --- On Fri, 2/19/10, Impalah Shenzhou <impalah at gmail.com> wrote:
>
> > From: Impalah Shenzhou <impalah at gmail.com>
> > Subject: Re: [Opensim-users] MySQL on seperate server?
> > To: opensim-users at lists.berlios.de
> > Date: Friday, February 19, 2010, 1:56 AM
> > I had the temptation to do a cron
> > script to delete "orphan" assets but, using grid
> > configuration, I've found that:
> > - Assets are disconnected from users. So the
> > only way to know the owner of an asset is to request info to
> > Inventory Servers. Not a real problem... or direct accessing
> > to inventory database or implementing a method for querying
> > on inventory server.
> > - Objects in regions are not in any user inventory, so
> > the script has to query all regions in the grid.-
> > It is possible that a user has his/her own inventory server,
> > so each of them should be checked as well.
> > - And of course the script should query the user
> > server first to get user data, and then the inventory server
> > defined in the data.- Could be possible to query
> > the last access for an asset too but... which is the time
> > window to consider an asset "useless"?
> >
> > So maybe for 10-20 regions I won't have too
> > much problems but "what if" I have
> > 100?
> > Opensim does not provide some methods natively
> > (I understand that it is a secondary problem nowadays) so
> > the easy solution is direct access to database.
> >
> >
> >
> >
> > 2010/2/18 Karen Palen <karen_palen at yahoo.com>
> >
> > I have to wonder if there is not some MySQL problem that is
> > contributing to this.
> >
> >
> >
> > Bob Wellman" <bob.wellman at hotmail.co.uk>
> > and I had an extended discussion about this a couple of
> > weeks ago.
> >
> >
> >
> > We both have observed that the OpenSim database never
> > deletes database entries for assets! In addition there are
> > numerous duplicate entries, especially
> >
> >
> >
> > Bob has actually done something about this and has written
> > some PHP based MySQL scripts to remove duplicate entries and
> > unused entries. Right now he is upgrading his scripts to
> > make them somewhat portable and I anticipate testing them on
> > my private region in the near future.
> >
> >
> >
> >
> > My database grew to over 1.2Gb for a single region! I used
> > a very crude way to fix the problem, I dumped all of my
> > inventory to an IAR file, then dumped the region data to an
> > OAR file.
> >
> >
> >
> > Now with 5 regions the database occupies less than 200Mb!
> >
> >
> >
> > Obviously if there is that much difference in the database
> > size then you are going to see a performance difference in
> > the database!
> >
> >
> >
> > This has not been a problem with OpenSim so far, probably
> > because very few users have kept a database for more than a
> > month or so. Our feeling is that this will soon become a
> > problem since OPenSim is now stable enough to get some
> > serious use!
> >
> >
> >
> >
> > We will be posting updates here and on the Wiki as soon as
> > there is anything to share!
> >
> >
> >
> > Karen
> >
> >
> >
> > --- On Thu, 2/18/10, Master_Mirage <mirage123 at verizon.net>
> > wrote:
> >
> >
> >
> > > From: Master_Mirage <mirage123 at verizon.net>
> >
> > > Subject: Re: [Opensim-users] MySQL on
> > seperate server?
> >
> > > To: opensim-users at lists.berlios.de
> >
> > > Date: Thursday, February 18, 2010, 9:56 AM
> >
> > >
> >
> > >
> >
> > > I arrive late, as usual :-)
> >
> > >
> >
> > > When a region has around 12000 primitives, with a db
> > server
> >
> > > on the same
> >
> > > machine you will have hell on earth or something like
> >
> > > that.
> >
> > >
> >
> > > Boot up time is around 10 minutes and is impossible to
> > do
> >
> > > anything but using
> >
> > > zoom. Impossible to move.
> >
> > >
> >
> > > I have tested this with 3 machines (4Gb ram, 500Gb hd
> > RAID,
> >
> > > Intel Core Duo
> >
> > > 1,73, Intel Core duo 2,3 and amd64) running windows
> > XP,
> >
> > > Vista and Windows
> >
> > > 7-64 bits.
> >
> > >
> >
> > > I was using grid mode (grid server on separate
> > computer...
> >
> > > no ROBUST or
> >
> > > UGAIM, it uses a PHP ugaim system).
> >
> > >
> >
> > > Separating Mysql to another machine on the same local
> >
> > > network (100 Mbps nc)
> >
> > > I could move, build, and even another avatar could
> > enter.
> >
> > >
> >
> > > Exporting db and running it on separate server with
> > Linux
> >
> > > (ubuntu server
> >
> > > 9.04) I could fill the region with 5000 more prims and
> > 2
> >
> > > avatars could work
> >
> > > smooth.
> >
> > >
> >
> > > Running opensim on Linux (amd64, 4gb ram, ubuntu
> > server
> >
> > > 9.04 64 bits) and db
> >
> > > on separate linux server 2 avatars could work smoothly
> > and
> >
> > > filling the
> >
> > > region with 25000 prims (no simple prims, but
> > different
> >
> > > sizes, texturized
> >
> > > and scripted).
> >
> > >
> >
> > > Anyway, that were my "home" experiences...
> > My conclusion:
> >
> > > separate db, don't
> >
> > > use windows.
> >
> > >
> >
> > > Greetings
> >
> > >
> >
> > >
> >
> > > Well 1st a bit on that. going back in time 1.5 years
> > now
> >
> > > running
> >
> > > opensimulator, At 1st it dident really matter as
> > there
> >
> > > wasent enough of a DB
> >
> > > to become a problem with YET. I had to take in
> > account
> >
> > > baised on (no was
> >
> > > really shure about anything) what it was i wanted
> > someday.
> >
> > > I knew that both
> >
> > > linux and win were used but seemed a hot argument and
> > still
> >
> > > no one really
> >
> > > had an answer for whats best for running a grid on. I
> >
> > > Decided  that the only
> >
> > > true way to know is run both and see.
> >
> > > So i purchaced 5 quad core servers all the same equip.
> > ram
> >
> > > ect. To me this
> >
> > > would be enough putter power to find out with. Remeber
> > that
> >
> > > opensimulator is
> >
> > > still alpha and was more so then.
> >
> > > I begain testing the MANY ways a grid can be formed
> > and the
> >
> > > equip. made it a
> >
> > > bit easyer to see it Side By Side in real time.
> >
> > > It dident take long to see that mysql would grow in
> >
> > > perpotion to the db size
> >
> > > as to its own demands.
> >
> > > At 1st it was faster to have mysql local to the
> > services
> >
> > > and was for
> >
> > > sometime. Later it started to glom onto the same
> > resources
> >
> > > the instances
> >
> > > needed and begain to get verry slow. At that point
> > assets
> >
> > > were around 3-4
> >
> > > gigs. Seeing the problem i knew i had to do something
> > as
> >
> > > the longer i waited
> >
> > > the worse it would get.
> >
> > > Moved Mysql to another box knowing latency would be
> > there.
> >
> > >
> >
> > > That indeed delt with the 'tipping point' it
> > reached. It
> >
> > > wasent hard to see
> >
> > > for my selph what happend.
> >
> > > That worked well for sometime but it begain to develop
> > its
> >
> > > own problems due
> >
> > > to the unknowen) as to its settings. Had to readj the
> > MySql
> >
> > > settings 4 times
> >
> > > and 2 total rebuilds due to the never ending growth
> > of
> >
> > > assest data. When it
> >
> > > hit around 10-11gigs assests a whole new set of
> > problems
> >
> > > showed up. The big
> >
> > > one is all that data has to be maintained and backed
> > up.
> >
> > > This took most of a
> >
> > > day of downtime as why its doing all that its locking
> > the
> >
> > > tables and
> >
> > > Opensimulator freeks. Thats not good and not only is
> > the
> >
> > > grid offline but
> >
> > > the amout of time by me to work on it became
> > excessive. As
> >
> > > orginaly i had
> >
> > > bee wise and bought enough computers i spent a good
> > while
> >
> > > learing about
> >
> > > Mysql replication. This does solve some of the
> > problems as
> >
> > > it lets me switch
> >
> > > the services to use the other Mysql and allows maint
> > to
> >
> > > happen with little
> >
> > > effort.
> >
> > > Only the one maint is beeing done on has tables locked
> > not
> >
> > > both.
> >
> > > Moveing to current time. 23+gigs assests and at my
> > goal of
> >
> > > 100 regions
> >
> > > (un-told amout of prims and scripts whatever) it was
> > a
> >
> > > verry good plan.
> >
> > > Is it as fast as it could be? Nope! The problem will
> >
> > > allways be there. Is it
> >
> > > scaleable? You betcha!.
> >
> > > I guess my point is Depends on what you expect your
> > grid
> >
> > > tobe and planning
> >
> > > assuming worse case as best as you can.
> >
> > > Opensimulator is made to be as felxable as it needs
> > tobe
> >
> > > and what oper.
> >
> > > system i find is a push. Both do an excellent job and
> > Both
> >
> > > can and do show
> >
> > > the same performance when set up properly.
> >
> > > For me the differances realy boiled down to what was
> > more
> >
> > > comfortable for me
> >
> > > to maintain. In my case im dealing with many servers
> > acting
> >
> > > as one. If you
> >
> > > ask me whats better ill say none.
> >
> > > My best suggestion for ppl starting out is expect
> > nothing
> >
> > > and be really
> >
> > > happy when it all works better than you though
> >
> > > :-)
> >
> > > There will allways be argument and theroy and thats a
> > good
> >
> > > thing but there
> >
> > > is no better judge than you and what you can see in
> >
> > > realtime.
> >
> > > :working:
> >
> > >
> >
> > > --
> >
> > > View this message in context:
> http://n2.nabble.com/MySQL-on-seperate-server-tp4575740p4592890.html
> >
> > > Sent from the opensim-users mailing list archive at
> >
> > > Nabble.com.
> >
> > > _______________________________________________
> >
> > > 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
> >
> >
> >
> >
> > -----Inline Attachment Follows-----
> >
> > _______________________________________________
> > 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
>

-- 
John Mieske / Winword Exonar
http://johnmieske.org
Space Grid Station
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://opensimulator.org/pipermail/opensim-users/attachments/20100219/7021cdf4/attachment.html>