[Opensim-users] MySQL on seperate server?

Thu Feb 18 22:33:16 UTC 2010

Ok I would like to make note..  each region SHOULD have its own SQL.. not
all tied into the Main Grid SQL. They should be seperate. Remember a
database growth will subside after a while because you can only do so much
per region.

now with that said..  Some like myself have megaregions. We use 4 or more
regions per SQL. No problem. But as Karen Said, they can get HUGE. What I do
is make each region run their own SQL normally. On seperate machines.  Now
that is not always cost effective. It can get expensive. This is why
companies Like Linden Labs can do what they do because they put the money
into their servers.

With this said. Try it. Assets from a USER is and will end up being the
biggest things that makes the databases grow. AND when you TP to another
region your inventory uploads to your destination region servers SQL. which
means when they are not in sync, you will have issues of missing prims or
clothing.

Is this all effecient ?  no..  but its all we got.. so the ones who are
serious about this should consider the future of their grids on regions to
SQL ratios and will raise the question of can their computers / Hard Drives
handle it.

This is where it all boils down too. If your just a casual user, just put it
 all on one machine and forget all the other machines and test and play.. :)

These are my experiences. Take it as you want.

John

On Thu, Feb 18, 2010 at 5:07 PM, Karen Palen <karen_palen at yahoo.com> wrote:

> 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
>

-- 
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/20100218/d7e1f0d1/attachment.html>