[Opensim-users] MySQL on seperate server?
Karen Palen
karen_palen at yahoo.com
Fri Feb 19 19:00:56 UTC 2010
This brings to mind the classic "Thing King" story
http://www.netjeff.com/humor/item.cgi?file=TheThingKing
This text was originally written as serious position paper circa 1965 by an IBM research team working on the idea of Virtual Memory.
They did actually use this terminology in describing their project! It was really weird listening to a talk by these folks. :-)
While it seems humorous today, it does give one of the best descriptions of the operation of virtual memory that I have ever seen!
I the relevence to the idea of a "historical" asset base should be obvious.
Any form of "historical" database is directly a tradeoff between "expensive" memory and complexity. However it too will have some finite limit to its size!
My thought is that some local grid (or maybe even client!) index of "this is the stuff that I use" might serve the purpose without getting into the "never delete anything" trap.
This too has some issues with malicious clients/grids. Having the "untrusted guy" merely handle an index key rather than the actual asset data would seem to solve most of the problems.
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, 2:55 AM
> 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
>
>
>
>
> -----Inline Attachment Follows-----
>
> _______________________________________________
> Opensim-users mailing list
> Opensim-users at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
More information about the Opensim-users
mailing list