[Opensim-users] MySQL on seperate server?

Fri Feb 19 09:55:06 UTC 2010

Yes, the main problem is the p2p way opensim works. You'll never be
absolutely sure if you are deleting an unused asset.

Another temporary solution affecting only to Asset server (which I want to
implement on my own grid servers) is a historical backup asset server: When
an asset hasn't been used for X time, delete from main database and pass to
historical.

When an asset is requested, first look for it in main database, if it is not
there, search on historical, if it's there, copy to main and return.

Of course there could be some overload when some old users/regions come into
the grid... but keeping the main database to a minimal or "workable" size,
for me is a big advantage.

I was reading time ago about the Facebook architecture with thousand of
gigabytes wasted on memcache servers... This idea is similar, the main
database will work as a cache and the historical can be... even a file
storing system (remember that assets can be stored as xml files).

Greetings

2010/2/19 Karen Palen <karen_palen at yahoo.com>

> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://opensimulator.org/pipermail/opensim-users/attachments/20100219/d00f0e00/attachment.html>