[Opensim-users] MySQL on seperate server?
Master_Mirage
mirage123 at verizon.net
Fri Feb 19 19:35:49 UTC 2010
I have been playing with mysql proxy, it is LUA scriptable and able to
filter,change evan refuse or redirect query's. For me that dosent really
help me but seems a good tool and works as a 'man in the middle' besides
just beeing a proxy. It may help to sort or at least see what Mysql server
is seeing. who knows im not that clever :-)
It did look usefull to me and i just use it for failover but its able to do
awhole lot more. It free and the bin or source is aval. in there downloads.
:working:
Karen Palen wrote:
>
> 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
>>
>
>
>
> _______________________________________________
> Opensim-users mailing list
> Opensim-users at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
>
--
View this message in context: http://n2.nabble.com/MySQL-on-seperate-server-tp4575740p4599688.html
Sent from the opensim-users mailing list archive at Nabble.com.
More information about the Opensim-users
mailing list