[Opensim-users] MySQL on seperate server?

Fri Feb 19 12:54:22 UTC 2010

 Actually Stefan you are quite right... Asset references are everywhere that's why it has taken us ages to write something to find and analyse them ALL.

We had already recognised that asset references occur in many places including inside certain asset blobs and our analysis tools takes that into account. It finds every reference to an asset UUID no matter where it occurs. Our Adimin Tool finds even those references inside a script or clothing assets. We even analyse those in a particle system attached to a prim (the particle texture for instance). And belive me they are in many other places as well. We have been very thorough. I don't say we haven't missed one but, if we have missed one, the way we have written the process makes adding another source of references fairly easy to do.

For duplicates the checksum idea is what we are actually doing in our tools. However we are doing via a batch process directly on the database to avoid altering the opensim code itself (for now). This is a very inefficient wayt to do this but work. With the advent of Robust we can do far better in future on how we actually store and retrieve assets entirely. So long term the asset storage/retireval/achiving solution will be quite different to what we have now I believe. With some fairlt small changes I reckon we can make asset work much more efficiently. Again I have some ideas on where I think we should go on this in the long term, if you fancy talking it through sometime. IMHO efficiency in this area is vital to the scalability of Opensim so we need a plan to get to an efficent design and takes stpes to get there.

eb 2010 12:31:58 +0100
Subject: Re: [Opensim-users] MySQL on seperate server?

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

_________________________________________________________________
Send us your Hotmail stories and be featured in our newsletter
http://clk.atdmt.com/UKM/go/195013117/direct/01/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://opensimulator.org/pipermail/opensim-users/attachments/20100219/3a926670/attachment.html>