[Opensim-users] MySQL on seperate server?

Fri Feb 19 11:31:58 UTC 2010

Certain assets, like clothing, are referencing other assets (textures) in
their binary data; hence, you will need to analyze assets to scour them for
used asset ids.

Also, as Melanie pointed out some time ago, scripts may perfectly well
address assets (textures) programmatically by id, which makes it impossible
to be 100% sure that a certain asset will never again be referenced.

That said, of course each grid operator is free to implement whatever
restrictions on asset referencing they see fit, and thusly can scavenge
freely.

Why we keep copies is because the assets are supposed to be 'immutable'; so
if a clothing item 'c' contains texture 't' and is given to avatar 'a', then
the clothing item is modified, it will become item 'd' so that we can safely
assume that 'a' will not experience any changes in 'd', ever.

Also, immutable assets make for efficient caching on the client.

That said, OpenSim could do much better than it does today. There has been
an idea going around for quite some time to split the asset table in two,
one with the binary data and one with metadata, and join them on a binaryId;
this would allow the binary table to contain a sha-256 checksum that could
be used for fast de-duplication by making sure at least there are no binary
duplicates.

That, in combination with a two-level archive storage mechanism would
probably take the thing home.

(And of course, some apps could even allow overwriting assets)

/Stefan

From: opensim-users-bounces at lists.berlios.de
[mailto:opensim-users-bounces at lists.berlios.de] On Behalf Of Impalah
Shenzhou
Sent: den 19 februari 2010 09:57
To: opensim-users at lists.berlios.de
Subject: Re: [Opensim-users] MySQL on seperate server?

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 < <mailto:karen_palen at yahoo.com>
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/4181739d/attachment.html>