[Opensim-users] MySQL on seperate server?

Fri Feb 19 08:56:41 UTC 2010

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