[Opensim-users] MySQL on seperate server?
Karen Palen
karen_palen at yahoo.com
Fri Feb 19 18:43:12 UTC 2010
Bob,
I look forward to helping any way I can!
I suspect that we have to address the standalone or "small grid" problem first since it is really a subset of the worldwide grid problem.
BTW I still need to get the Caledon Railway stuff working on PMGrid BTW - a couple of the scripts did not copy over for some reason.
Karen
--- On Fri, 2/19/10, Bob Wellman <bob.wellman at hotmail.co.uk> wrote:
> From: Bob Wellman <bob.wellman at hotmail.co.uk>
> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: opensim-users at lists.berlios.de
> Date: Friday, February 19, 2010, 3:34 AM
>
>
>
>
>
> Just to let you know:
>
>
>
> I am nearing completion of the first rough draft of the
> documentation on our PMgrid Administration tools, which
> includes among other things an analysis of asset
> usages (for small self contained grids only) and the
> ability to archive redundant assets. To use Karen's
> analogy we have "unscrambled the omelette"...LOL.
> BTW: These tools also deal with duplicate assets.
>
>
>
> As Karen points out we have done all this by working
> directly at the database (SQL) level using a .php web based
> interface. We hope in future to make this available to other
> small grid operators so they can attach it to their own to
> web/opensim interface sites to manage their grid(s). That
> day is I am afraid a way off yet especially as the database
> is about to change dramatically in 0.7 and we will need a
> rewrite of what we have done at that stage to match the new
> 0.7 database schema. So our aim is post 0.7
> (plus rewrite of PMgrid Admin Tools) we will try to
> open source that new version for people. In the meantime I
> intend to release the draft documentation on the current
> version soon (a few weeks away) for people to see and
> comment on.
>
>
>
> Just to put credit where its due I wish to point out that
> whilst I am managing and documenting this, the hard
> programming work has all been done by Peetr Maynard of
> PMgrid who is doing all this in his spare time. This
> coupled with the fact that we are running a grid
> and experimenting on other Opensim issues means
> progress on this will be slow. My appologies for that.
>
>
>
> I am available a lot of the time (retired from RL work
> unlike Peetr.... /me has smug grin on face) for a voice chat
> on windows live messenger (bob.wellman at hotmail.co.uk)
> for anyone who wants to discuss what we are doing on this or
> other topics. I regard all input as useful, thoughts as well
> as action. Your thoughts may or may not change what we
> are doing but it will certainly change how we think about
> what we are doing.
>
>
>
> BTW: We are also exploring an idea for distributing asset
> serving geographically to solve an issue with US users
> on UK grids running slowly, and vice versa, but that is
> another story and too early to share yet, except in voice.
>
>
>
>
>
> > Date: Fri, 19 Feb 2010 01:33:26 -0800
> > From: karen_palen at yahoo.com
> > To: opensim-users at lists.berlios.de
> > Subject: Re: [Opensim-users] MySQL on seperate
> server?
> >
> > 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
>
> Do you have a story that started on
> Hotmail? Tell
> us now
>
> -----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