MySQL on seperate server?

classic Classic list List threaded Threaded
48 messages Options
123
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

John Mieske
Well said.

:)

On Thu, Feb 18, 2010 at 11:56 AM, Master_Mirage <[hidden email]> wrote:


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



--
John Mieske / Winword Exonar
http://johnmieske.org
Space Grid Station

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Impalah Shenzhou
Interesting, very interesting.

I'm not an expert in Systems Architecture (well, I'm not an expert in anything :-P) so my method is "read, try, change, try again, read more..." but the last posts are opening my eyes. I will look carefully hardware specs, and will try to tweak operative system... Maybe Windows is not so evil :-P

About standard asset server, I suffered the problem with Mysql when my assets table grow up to 6 Gb. Maybe Oracle or MS-SQL can perform better with blobs...

Thanks for the comments



2010/2/18 John Mieske <[hidden email]>
Well said.

:)


On Thu, Feb 18, 2010 at 11:56 AM, Master_Mirage <[hidden email]> wrote:


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



--
John Mieske / Winword Exonar
http://johnmieske.org
Space Grid Station

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users



_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Karen_Palen
In reply to this post by Tedd Hansen-2
Hi,

I was deliberately being as neutral as I could in my tone since I do NOT want to start some "Fanboy" flamewar!

I do notice that a defrag operation is standard in all Windows versions and certainly does make a huge difference in file access.

I also take note that "defrag" is NOT a Linux utility and I have not experienced as problem due to its lack. My only real experience for potential fragmentation was in copying/moving a Ubuntu 9.04 install to another machine - about the crudest form of "defrag" there is!

I was careful to use a "file by file" copy and not "sector by sector" for exactly this reason. The old file system had been in heavy use for nearly a year, but I measured zero difference in hard drive performance!

From that I conclude that Linux does not experience a fragmentation problem.

You are welcome to debate the reasons for this, I see that as unproductive.

Karen

--- On Thu, 2/18/10, Tedd Hansen <[hidden email]> wrote:

> From: Tedd Hansen <[hidden email]>
> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: [hidden email]
> Date: Thursday, February 18, 2010, 8:31 AM
> Hi
>
> NTFS filesystem does have all the features of ext3/ext4 +
> much more (except maybe live resize of fs), and have had it
> for at least 10 years. NTFS has been way ahead of its time
> for well over a decade. The journal is the part that keeps
> your data safe (introduced in ext3) in event of a crash.
> BOTH filesystems have pre-allocation of space when you open
> a file for write, which combats fragmentation as long as you
> have a fair amount of free space on your disk. But Windows
> 2008 also has prioritized IO and background defragmenting
> which allows it to organize things in the background without
> affecting the services you are running. Anyway you should
> have enough memory to not care too much about fragmentation,
> it doesn't cost much and gives a good boost.
>
> When that is said, the kernel does not matter that much
> anymore. That is a 90's discussion. Threading, memory
> handling, disk IO, all are competitive on all platforms
> today. Some advances are made now and then, but all the
> others are quick to follow.
>
> I highly recommend the book "Modern Operating Systems" for
> anyone who wants to dig in deep in OS' in general.
>
> Considerations to be made (from the top of my head):
> 1) Of course; how much memory, memory
> speed/bandwidth(bridge) and how many MHz's do you pack...
> 2) How much L2-cache do you have (quite important)?
> 3) How many physical CPU's do you have (cores share L2
> cache)?
> 4) How many memory channels do you have (simultaneous
> memory access)?
> 5) How are these channels mapped to the CPU's?
> 6) Do the software you run know how to utilize the
> CPU2PhysicalMem(data) mapping correctly (MS-SQL does, MySQL
> does not afaik)?
> 7) Are you forcing many CPU-intensive components to share
> memory bandwith (regions, several db components, etc)?
> Remember that CPU-intensive tasks usually involve modifying
> some amount of memory.
> 8) What other tasks are you doing on this computer which
> affects #7.
>
> Because of the fact that OpenSim is a CPU intensive package
> which uses a lot of code and memory you can run out of
> L2-cache on larger setups, hence you get a trashing behavior
> on treads.
> So splitting OpenSim over multiple machines (regions,
> databases, etc) will yield better performance. Putting too
> much on the same machine will after a certain point cause an
> exponential decrease in speed (trashing behavior).
>
> The exact point at where your installation will start
> trashing is highly dependent on hardware and number of
> objects ... but some general experience in this area would
> be very interesting.
> OpenSim/3D worlds are hw-intensive applications, and
> squeezing the most out of them will be an issue for anyone
> who plan to have more than a handful of users. (What is the
> TCO of 1k visitors, 10k, 100k, etc.)
>
> For kernel hackers...
> If you have a dedicated OpenSim-server you could try to
> change the default Hz of the kernel. Currently it's at 250,
> optimized for multimedia such as video display.
> Linus in e-mail explained it as "1kHz is also good in that
> it makes it easy to convert both to USER_HZ and to ms/ns.
> But maybe something like 250Hz would be better - still high
> enough that things like multimedia (which really wants
> higher frequencies in order to be able to sleep for
> fractional video-frames) should be happy, low enough that we
> use less CPU."
> You can set this to 100 to avoid an additional 150 task
> switches and MMU-reprograms per second. This would allow
> each thread to work longer on the data it has retrieved from
> memory before a switch occurs.
> This can be done for Windows as well with a simple registry
> change and reboot.
> Linux: http://kerneltrap.org/node/5411
> Windows: http://doc.sch130.nsc.ru/www.sysinternals.com/ntw2k/freeware/frob.shtml
>
> Definition of trashing = http://en.wikipedia.org/wiki/Thrash_(computer_science)
>
> BR,
>  Tedd
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]]
> On Behalf Of Karen Palen
> Sent: 18. februar 2010 15:21
> To: [hidden email]
> Subject: Re: [Opensim-users] MySQL on seperate server?
>
> Zonja did not specify, but I am guessing that he is using
> Windows since he needed to de-fragment his HD. The Linux
> ext3/4 file system takes care of that automatically in the
> journalling.
>
> Inevitably there will be many differences between a Windows
> based system and a Linux based one, if only because the two
> OSes are so different in their kernals.
>
> If you haven't already tried it, you should ge the best
> efficiency with one of the Linux server distros.
>
> I have played with Ubuntu server and it is much "leaner"
> than the desktop version. The Kernel is optimised for
> database type applications rather than giving a responsive
> GUI.
>
> I would assume that the other Linux server versions are
> similar, but I really don't know enough about them to
> compare. Any of them should come with all of the basic tools
> (LAMP, MySQL support etc) so it should boil down to which
> one fits your needs the best.
>
> There are hundreds of "standard" tests for performance, but
> as far as I know there is nothing yet that is oriented to
> OpenSim. One of the MySQL benchmarks should give you some
> good insight though.
>
> Karen
>
> --- On Thu, 2/18/10, Impalah Shenzhou <[hidden email]>
> wrote:
>
> > From: Impalah Shenzhou <[hidden email]>
> > Subject: Re: [Opensim-users] MySQL on seperate
> server?
> > To: [hidden email]
> > Date: Thursday, February 18, 2010, 5:02 AM
> > Then the problem are in my computers
> > :-P or in the tests we both did.
> >
> > I got better performance separating db from
> > opensim servers, but I don't know if prim
> > size/modifications/colors/texturing/scripting have
> some
> > influence in the results we both got.
> >
> > Any standard test for performance?
> > 2010/2/18 Zonja Capalini
> > <[hidden email]>
> >
> > I'm
> > using a Quad Core Q6600 @ 2.40 GHz with 3 GB of RAM
> and two
> > 372 GB HDsfor Condensation Land. Condensation runs
> two
> > Opensim instances (8 and 3 sims respectively)
> > in a serverless grid configuration AND the MySQL
> > database. I have the OS in one HD and MySQL
> > and Opensim in the other HD, and my startup times are
> > < 2 mins even if I havesims with > 10000
> > prims. When I log in with 512 m draw distance, I
> experience
> > some lagfor 15 seconds or so (on a 10 Mb ADSL
> > line) while everything is downloaded,
> >
> > then no lag. Tried it with four avatars, no
> > lag.
> > Defragmenting the HDs every so often does *a
> > lot* to improve performance.
> > ymmv
> >
> >   /Zonja
> >
> > On Thu, Feb 18, 2010 at 11:26 AM,
> > Impalah Shenzhou <[hidden email]>
> > wrote:
> >
> >
> > 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
> >
> >
> >
> >
> >
> > 2010/2/16 Steve <[hidden email]>
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Thank you, although this doesn't tie in to my
> bandwidth
> > usage reports :/
> >
> >
> >
> > I have 60Gig peak, 240 Gig off peak allowance with my
> > provider
> > download. No limit upload. I have had the sim up and
> > running for a week
> > 24/7 (one region  only - and 24/7 apart from a
> > OpenSimulator version
> > upgrade and the odd restart), using my net connection
> as
> > normal on my
> > main PC.
> >
> >
> >
> > My usage for 1 - 15 Feb is 11.78 Gig D/L (which
> includes 3
> > Gig HD vid
> > from BBC iPlayer) and 2 Gig upload.
> >
> >
> >
> > I was interested in the OP question because I have a
> SQL
> > server sat on
> > my web hosts servers doing nada, but it looks like I
> will
> > gain neither
> > performance nor speed by trying to use it, nor will I
> save
> > any
> > bandwidth.
> >
> >
> >
> > This is a real learning experience.
> >
> >
> >
> > Steve
> >
> >
> >
> > On 16/02/2010 8:15 PM, John Mieske wrote:
> > Oh I might add, that is 20 -
> > 40KBytes Upload one way and
> > 20 - 80 KB download one way. I thought I would
> meantion
> > that.
> >   John
> >
> >
> >
> >   On Tue, Feb 16, 2010 at 3:14 PM,
> > John Mieske
> >   <[hidden email]>
> > wrote:
> >
> >   here
> > is an example of what happened to me on a few Grids I
> have
> > tested. Per
> > AV I am uploading / downloading roughly 40KBytes a
> second.
> > Not kBits.
> > That is full load with everything going on. So I am
> giving
> > the most I
> > have seen in this case. That is roughly 320 KBit
> upload and
> > download
> > just for one avatar. So in theory, you should be able
> to
> > push less then
> > 40K IF your avatars are not really doing all of what
> they
> > want them to.
> > Example : Voice.
> >
> >
> >
> >     So with a 1 Meg upload, your
> looking at around 3
> > Avatars
> > smoothly. If your going to go with 2 Meg Upload which
> some
> > of us have,
> > then your could go to about 7 Avatars smoothly. Again,
> this
> > is all
> > dependant on what your USERS are doing. If all are
> building
> > and in
> > Voice all at once, then 1 Meg = 3 Avatars smoothly.
> >
> >
> >
> >     Keep in mind, this is a test I
> did myself on 5
> > Grids I have
> > tested. Each with different ISPs. So if your going to
> run a
> > Grid you
> > also have to account that the region that ties into
> your
> > main region
> > ALSO has to accomodate for the extra communication
> between
> > each region
> > that is not on the same computer / ISP. AND they are
> > constantly 24 / 7
> > communicating. So if you have a bandwidth limit, you
> could
> > be in
> > trouble. At a Previous version of Sim world, we had
> 500
> > Megabytes
> > transfer for about a weeks worth of time. And that is
> with
> > only 8
> > regions and VERY little activity. The limit ?  1
> > Terribyte. So in one
> > week, we ate up half our bandwidth limit. ( Thats all
> the
> > HOST allowed
> > at the time )
> >
> >
> >
> >     Bottom line, you MUST do the
> math to see if this
> > is even
> > possible to keep running before spending a lot of
> > money.
> >
> >
> >
> >     John
> >
> >
> >
> >
> >
> >     On Tue, Feb 16, 2010 at 2:41
> > PM, Steve <[hidden email]>
> > wrote:
> >
> >
> >
> > So out of interest, what are the upload limits per
> avatar
> > for example.
> > I have just shy of 1 Mb upload speed, and have
> happily
> > accommodated 8
> > Avatars (after one in particular who was throwing up
> masses
> > of errors
> > left). How far could I, in theory, push it?
> >
> >
> >
> > Steve
> >
> >
> >
> >
> >
> > On 15/02/2010 11:00 PM, Ken G. wrote:
> >       Alright, thanks Daz
> for
> > answering my
> > question and thanks
> > to John and Master_Mirage for the input. I am just a
> hobby
> > user of
> > opensim, connected to OSGrid, hoping that maybe there
> was a
> > way to
> > improve my mediocre connection :)
> >
> >
> >
> > Ken aka Key
> >
> >
> >
> >
> >
> >         On Mon, Feb 15,
> 2010 at
> > 3:00 PM, John
> > Mieske <[hidden email]>
> > wrote:
> >
> >         I
> > have
> > to agree with Darren here. If your doing this at home,
> you
> > might
> > as well have both on the same computer. Your internet
> in
> > general will
> > dictate what you can do. So if your running this at
> home,
> > hang up the
> > idea of two computer region serving because no one
> will see
> > a
> > difference when they already bogg down from a slow
> internet
> > connection
> > to your region. I have seen some Regions who run them
> at
> > home with the
> > best of intentions only shut down days later because
> they
> > realize their
> > internet only allowed 2 avatars to run smoothly. When
> they
> > got to 3,
> > their sim started to crash. It is all about your
> upload
> > speed. However,
> > if you got a FAT PIPE ( not to smoke hahaha ) then yea
> you
> > definitely
> > might want to try the way I suggested. Again
> everything in
> > OpenSim is
> > based on HOW you run your Grid / Regions. Nothing is
> 100%
> > set one way.
> > I only offered a suggestion to those who could use
> this
> > benefit. And
> > there are already some servers that has been setup
> this way
> > and they
> > are smooth.
> >
> >
> >
> >           John
> >
> >
> >
> >
> >
> >
> >           On Mon,
> Feb 15, 2010 at 3:39 PM, Darren
> > Williams <[hidden email]>
> > wrote:
> >
> >
> >
> >
> >
> >
> >
> >
> >         
>    Ken G asked:
> >
> >
> >         
>    Dumb question from a
> > casual opensim
> > user who
> > is not fully
> > clued in on it's workings, that popped into my head
> > after reading John
> > Mieske's
> > Space Grid Station post:
> >
> >
> >
> > Is it feasible and sensible while running opensim on a
> home
> > PC
> > connection to be
> > able to use a MySQL database on a shared web hosting
> > server, such as
> > Hostgator
> > for example? If possible, would this type of setup
> improve
> > performance
> > of a
> > lightly used standalone or grid-connected sim?
> >
> > I haven't put much thought into this idea, just
> wanted
> > to blurt it out
> > and see
> > if it's worth pursuing.
> >
> >
> >         
>    Ken,
> >
> >         
>    You
> > shouldn’t
> > receive any benefit at all doing the above.
> > In fact it will probably make things worse.
> >
> >             If
> I’m
> > reading this
> > right then the region server which you
> > have running locally would then be pulling/pushing the
> data
> > from across
> > the net
> > on the mysql server, back to your machine. It would
> > probably run worse.
> >
> >         
>    Daz
> >
> >
> >
> >
> >
> >
> >
> > _______________________________________________
> >
> > Opensim-users mailing list
> >
> >             [hidden email]
> >
> >             https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> >
> > John Mieske / Winword Exonar
> >
> >           http://johnmieske.org
> >
> > Space Grid Station
> >
> >
> >
> >
> >
> > _______________________________________________
> >
> > Opensim-users mailing list
> >
> >           [hidden email]
> >
> >           https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> >
> >
> >
> > _______________________________________________
> > Opensim-users mailing list
> > [hidden email]
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> >
> >
> >
> > _______________________________________________
> >
> > Opensim-users mailing list
> >
> >       [hidden email]
> >
> >       https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> >
> > John Mieske / Winword Exonar
> >
> >     http://johnmieske.org
> >
> > Space Grid Station
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> >
> > John Mieske / Winword Exonar
> >
> >   http://johnmieske.org
> >
> > Space Grid Station
> >
> >
> >   _______________________________________________
> > Opensim-users mailing list
> > [hidden email]
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> >
> > _______________________________________________
> >
> > Opensim-users mailing list
> >
> > [hidden email]
> >
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> > _______________________________________________
> >
> > Opensim-users mailing list
> >
> > [hidden email]
> >
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> > _______________________________________________
> >
> > Opensim-users mailing list
> >
> > [hidden email]
> >
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> > -----Inline Attachment Follows-----
> >
> > _______________________________________________
> > Opensim-users mailing list
> > [hidden email]
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
>
>
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>


     
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Karen_Palen
In reply to this post by Master_Mirage
I have to wonder if there is not some MySQL problem that is contributing to this.

Bob Wellman" <[hidden email]> 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 <[hidden email]> wrote:

> From: Master_Mirage <[hidden email]>
> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: [hidden email]
> 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
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>


     
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Tedd Hansen-2
In reply to this post by Karen_Palen
Hi

Just sharing info; no intent at OS/religious discussion... Recently had the same discussion with a friend where the defrag problem came up. :)
But the issue is relevant to performance.

(I'm by the way running OpenSim on my preferred OpenSim platform which is Linux Debian 5/Lenny, with OpenSim init.d-script and the works.)

Linux did have an offline defrag utility for ext2/ext3. But at least Debian seems to have dropped it due to incompatibility with new features in ext3:
http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=396449
http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=446691
Searching for it I did however come over a LOT of misconceptions on how (Windows) filesystems work. :)
All filesystems get fragmented over time (deleting small files now and then and having OS choose large free spaces for new files will eventually use up all large free blocks), and get quickly fragmented when the disk is nearing full. So for any system keep 10-20-30% free space at all times. Background online defrag is the best way to combat this when after it has occurred. Windows and Mac (iirc) do online background defrag, and also align frequently used files for faster startup/access.

So to conclude: If you don't drop below say 10% free space there is no need for defragmenting. And the cp-command is the best Linux defragmenter atm. :)


Another thing thing to note is that the performance for .Net is higher under Windows (MS) that Linux (Mono). I guess because Mono have focus on extending its features, and not so much on optimizing (yet). Make it work, make it stable, make it fast. How much difference I guess depends on the normal set of unlimited undeterminable variables. I've tried to compile OpenSim under Windows and run the .exe under Linux with varying results. Some unexplained crashes that disappeared with a recompile in Linux.

But in my opinion the cost of a few extra MHz in the CPU (or extra cores) is so low that it doesn't matter that much if Mono doesn't outperform MS. It is more important to me that it's easy to administer. BASH, Perl, VIM ffs. :)

BR,
 Tedd

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Karen Palen
Sent: 18. februar 2010 22:39
To: [hidden email]
Subject: Re: [Opensim-users] MySQL on seperate server?

Hi,

I was deliberately being as neutral as I could in my tone since I do NOT want to start some "Fanboy" flamewar!

I do notice that a defrag operation is standard in all Windows versions and certainly does make a huge difference in file access.

I also take note that "defrag" is NOT a Linux utility and I have not experienced as problem due to its lack. My only real experience for potential fragmentation was in copying/moving a Ubuntu 9.04 install to another machine - about the crudest form of "defrag" there is!

I was careful to use a "file by file" copy and not "sector by sector" for exactly this reason. The old file system had been in heavy use for nearly a year, but I measured zero difference in hard drive performance!

From that I conclude that Linux does not experience a fragmentation problem.

You are welcome to debate the reasons for this, I see that as unproductive.

Karen

--- On Thu, 2/18/10, Tedd Hansen <[hidden email]> wrote:

> From: Tedd Hansen <[hidden email]>
> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: [hidden email]
> Date: Thursday, February 18, 2010, 8:31 AM
> Hi
>
> NTFS filesystem does have all the features of ext3/ext4 +
> much more (except maybe live resize of fs), and have had it
> for at least 10 years. NTFS has been way ahead of its time
> for well over a decade. The journal is the part that keeps
> your data safe (introduced in ext3) in event of a crash.
> BOTH filesystems have pre-allocation of space when you open
> a file for write, which combats fragmentation as long as you
> have a fair amount of free space on your disk. But Windows
> 2008 also has prioritized IO and background defragmenting
> which allows it to organize things in the background without
> affecting the services you are running. Anyway you should
> have enough memory to not care too much about fragmentation,
> it doesn't cost much and gives a good boost.
>
> When that is said, the kernel does not matter that much
> anymore. That is a 90's discussion. Threading, memory
> handling, disk IO, all are competitive on all platforms
> today. Some advances are made now and then, but all the
> others are quick to follow.
>
> I highly recommend the book "Modern Operating Systems" for
> anyone who wants to dig in deep in OS' in general.
>
> Considerations to be made (from the top of my head):
> 1) Of course; how much memory, memory
> speed/bandwidth(bridge) and how many MHz's do you pack...
> 2) How much L2-cache do you have (quite important)?
> 3) How many physical CPU's do you have (cores share L2
> cache)?
> 4) How many memory channels do you have (simultaneous
> memory access)?
> 5) How are these channels mapped to the CPU's?
> 6) Do the software you run know how to utilize the
> CPU2PhysicalMem(data) mapping correctly (MS-SQL does, MySQL
> does not afaik)?
> 7) Are you forcing many CPU-intensive components to share
> memory bandwith (regions, several db components, etc)?
> Remember that CPU-intensive tasks usually involve modifying
> some amount of memory.
> 8) What other tasks are you doing on this computer which
> affects #7.
>
> Because of the fact that OpenSim is a CPU intensive package
> which uses a lot of code and memory you can run out of
> L2-cache on larger setups, hence you get a trashing behavior
> on treads.
> So splitting OpenSim over multiple machines (regions,
> databases, etc) will yield better performance. Putting too
> much on the same machine will after a certain point cause an
> exponential decrease in speed (trashing behavior).
>
> The exact point at where your installation will start
> trashing is highly dependent on hardware and number of
> objects ... but some general experience in this area would
> be very interesting.
> OpenSim/3D worlds are hw-intensive applications, and
> squeezing the most out of them will be an issue for anyone
> who plan to have more than a handful of users. (What is the
> TCO of 1k visitors, 10k, 100k, etc.)
>
> For kernel hackers...
> If you have a dedicated OpenSim-server you could try to
> change the default Hz of the kernel. Currently it's at 250,
> optimized for multimedia such as video display.
> Linus in e-mail explained it as "1kHz is also good in that
> it makes it easy to convert both to USER_HZ and to ms/ns.
> But maybe something like 250Hz would be better - still high
> enough that things like multimedia (which really wants
> higher frequencies in order to be able to sleep for
> fractional video-frames) should be happy, low enough that we
> use less CPU."
> You can set this to 100 to avoid an additional 150 task
> switches and MMU-reprograms per second. This would allow
> each thread to work longer on the data it has retrieved from
> memory before a switch occurs.
> This can be done for Windows as well with a simple registry
> change and reboot.
> Linux: http://kerneltrap.org/node/5411
> Windows: http://doc.sch130.nsc.ru/www.sysinternals.com/ntw2k/freeware/frob.shtml
>
> Definition of trashing = http://en.wikipedia.org/wiki/Thrash_(computer_science)
>
> BR,
>  Tedd
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]]
> On Behalf Of Karen Palen
> Sent: 18. februar 2010 15:21
> To: [hidden email]
> Subject: Re: [Opensim-users] MySQL on seperate server?
>
> Zonja did not specify, but I am guessing that he is using
> Windows since he needed to de-fragment his HD. The Linux
> ext3/4 file system takes care of that automatically in the
> journalling.
>
> Inevitably there will be many differences between a Windows
> based system and a Linux based one, if only because the two
> OSes are so different in their kernals.
>
> If you haven't already tried it, you should ge the best
> efficiency with one of the Linux server distros.
>
> I have played with Ubuntu server and it is much "leaner"
> than the desktop version. The Kernel is optimised for
> database type applications rather than giving a responsive
> GUI.
>
> I would assume that the other Linux server versions are
> similar, but I really don't know enough about them to
> compare. Any of them should come with all of the basic tools
> (LAMP, MySQL support etc) so it should boil down to which
> one fits your needs the best.
>
> There are hundreds of "standard" tests for performance, but
> as far as I know there is nothing yet that is oriented to
> OpenSim. One of the MySQL benchmarks should give you some
> good insight though.
>
> Karen
>
> --- On Thu, 2/18/10, Impalah Shenzhou <[hidden email]>
> wrote:
>
> > From: Impalah Shenzhou <[hidden email]>
> > Subject: Re: [Opensim-users] MySQL on seperate
> server?
> > To: [hidden email]
> > Date: Thursday, February 18, 2010, 5:02 AM
> > Then the problem are in my computers
> > :-P or in the tests we both did.
> >
> > I got better performance separating db from
> > opensim servers, but I don't know if prim
> > size/modifications/colors/texturing/scripting have
> some
> > influence in the results we both got.
> >
> > Any standard test for performance?
> > 2010/2/18 Zonja Capalini
> > <[hidden email]>
> >
> > I'm
> > using a Quad Core Q6600 @ 2.40 GHz with 3 GB of RAM
> and two
> > 372 GB HDsfor Condensation Land. Condensation runs
> two
> > Opensim instances (8 and 3 sims respectively)
> > in a serverless grid configuration AND the MySQL
> > database. I have the OS in one HD and MySQL
> > and Opensim in the other HD, and my startup times are
> > < 2 mins even if I havesims with > 10000
> > prims. When I log in with 512 m draw distance, I
> experience
> > some lagfor 15 seconds or so (on a 10 Mb ADSL
> > line) while everything is downloaded,
> >
> > then no lag. Tried it with four avatars, no
> > lag.
> > Defragmenting the HDs every so often does *a
> > lot* to improve performance.
> > ymmv
> >
> >   /Zonja
> >
> > On Thu, Feb 18, 2010 at 11:26 AM,
> > Impalah Shenzhou <[hidden email]>
> > wrote:
> >
> >
> > 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
> >
> >
> >
> >
> >
> > 2010/2/16 Steve <[hidden email]>
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Thank you, although this doesn't tie in to my
> bandwidth
> > usage reports :/
> >
> >
> >
> > I have 60Gig peak, 240 Gig off peak allowance with my
> > provider
> > download. No limit upload. I have had the sim up and
> > running for a week
> > 24/7 (one region  only - and 24/7 apart from a
> > OpenSimulator version
> > upgrade and the odd restart), using my net connection
> as
> > normal on my
> > main PC.
> >
> >
> >
> > My usage for 1 - 15 Feb is 11.78 Gig D/L (which
> includes 3
> > Gig HD vid
> > from BBC iPlayer) and 2 Gig upload.
> >
> >
> >
> > I was interested in the OP question because I have a
> SQL
> > server sat on
> > my web hosts servers doing nada, but it looks like I
> will
> > gain neither
> > performance nor speed by trying to use it, nor will I
> save
> > any
> > bandwidth.
> >
> >
> >
> > This is a real learning experience.
> >
> >
> >
> > Steve
> >
> >
> >
> > On 16/02/2010 8:15 PM, John Mieske wrote:
> > Oh I might add, that is 20 -
> > 40KBytes Upload one way and
> > 20 - 80 KB download one way. I thought I would
> meantion
> > that.
> >   John
> >
> >
> >
> >   On Tue, Feb 16, 2010 at 3:14 PM,
> > John Mieske
> >   <[hidden email]>
> > wrote:
> >
> >   here
> > is an example of what happened to me on a few Grids I
> have
> > tested. Per
> > AV I am uploading / downloading roughly 40KBytes a
> second.
> > Not kBits.
> > That is full load with everything going on. So I am
> giving
> > the most I
> > have seen in this case. That is roughly 320 KBit
> upload and
> > download
> > just for one avatar. So in theory, you should be able
> to
> > push less then
> > 40K IF your avatars are not really doing all of what
> they
> > want them to.
> > Example : Voice.
> >
> >
> >
> >     So with a 1 Meg upload, your
> looking at around 3
> > Avatars
> > smoothly. If your going to go with 2 Meg Upload which
> some
> > of us have,
> > then your could go to about 7 Avatars smoothly. Again,
> this
> > is all
> > dependant on what your USERS are doing. If all are
> building
> > and in
> > Voice all at once, then 1 Meg = 3 Avatars smoothly.
> >
> >
> >
> >     Keep in mind, this is a test I
> did myself on 5
> > Grids I have
> > tested. Each with different ISPs. So if your going to
> run a
> > Grid you
> > also have to account that the region that ties into
> your
> > main region
> > ALSO has to accomodate for the extra communication
> between
> > each region
> > that is not on the same computer / ISP. AND they are
> > constantly 24 / 7
> > communicating. So if you have a bandwidth limit, you
> could
> > be in
> > trouble. At a Previous version of Sim world, we had
> 500
> > Megabytes
> > transfer for about a weeks worth of time. And that is
> with
> > only 8
> > regions and VERY little activity. The limit ?  1
> > Terribyte. So in one
> > week, we ate up half our bandwidth limit. ( Thats all
> the
> > HOST allowed
> > at the time )
> >
> >
> >
> >     Bottom line, you MUST do the
> math to see if this
> > is even
> > possible to keep running before spending a lot of
> > money.
> >
> >
> >
> >     John
> >
> >
> >
> >
> >
> >     On Tue, Feb 16, 2010 at 2:41
> > PM, Steve <[hidden email]>
> > wrote:
> >
> >
> >
> > So out of interest, what are the upload limits per
> avatar
> > for example.
> > I have just shy of 1 Mb upload speed, and have
> happily
> > accommodated 8
> > Avatars (after one in particular who was throwing up
> masses
> > of errors
> > left). How far could I, in theory, push it?
> >
> >
> >
> > Steve
> >
> >
> >
> >
> >
> > On 15/02/2010 11:00 PM, Ken G. wrote:
> >       Alright, thanks Daz
> for
> > answering my
> > question and thanks
> > to John and Master_Mirage for the input. I am just a
> hobby
> > user of
> > opensim, connected to OSGrid, hoping that maybe there
> was a
> > way to
> > improve my mediocre connection :)
> >
> >
> >
> > Ken aka Key
> >
> >
> >
> >
> >
> >         On Mon, Feb 15,
> 2010 at
> > 3:00 PM, John
> > Mieske <[hidden email]>
> > wrote:
> >
> >         I
> > have
> > to agree with Darren here. If your doing this at home,
> you
> > might
> > as well have both on the same computer. Your internet
> in
> > general will
> > dictate what you can do. So if your running this at
> home,
> > hang up the
> > idea of two computer region serving because no one
> will see
> > a
> > difference when they already bogg down from a slow
> internet
> > connection
> > to your region. I have seen some Regions who run them
> at
> > home with the
> > best of intentions only shut down days later because
> they
> > realize their
> > internet only allowed 2 avatars to run smoothly. When
> they
> > got to 3,
> > their sim started to crash. It is all about your
> upload
> > speed. However,
> > if you got a FAT PIPE ( not to smoke hahaha ) then yea
> you
> > definitely
> > might want to try the way I suggested. Again
> everything in
> > OpenSim is
> > based on HOW you run your Grid / Regions. Nothing is
> 100%
> > set one way.
> > I only offered a suggestion to those who could use
> this
> > benefit. And
> > there are already some servers that has been setup
> this way
> > and they
> > are smooth.
> >
> >
> >
> >           John
> >
> >
> >
> >
> >
> >
> >           On Mon,
> Feb 15, 2010 at 3:39 PM, Darren
> > Williams <[hidden email]>
> > wrote:
> >
> >
> >
> >
> >
> >
> >
> >
> >
>    Ken G asked:
> >
> >
> >
>    Dumb question from a
> > casual opensim
> > user who
> > is not fully
> > clued in on it's workings, that popped into my head
> > after reading John
> > Mieske's
> > Space Grid Station post:
> >
> >
> >
> > Is it feasible and sensible while running opensim on a
> home
> > PC
> > connection to be
> > able to use a MySQL database on a shared web hosting
> > server, such as
> > Hostgator
> > for example? If possible, would this type of setup
> improve
> > performance
> > of a
> > lightly used standalone or grid-connected sim?
> >
> > I haven't put much thought into this idea, just
> wanted
> > to blurt it out
> > and see
> > if it's worth pursuing.
> >
> >
> >
>    Ken,
> >
> >
>    You
> > shouldn’t
> > receive any benefit at all doing the above.
> > In fact it will probably make things worse.
> >
> >             If
> I’m
> > reading this
> > right then the region server which you
> > have running locally would then be pulling/pushing the
> data
> > from across
> > the net
> > on the mysql server, back to your machine. It would
> > probably run worse.
> >
> >
>    Daz
> >
> >
> >
> >
> >
> >
> >
> > _______________________________________________
> >
> > Opensim-users mailing list
> >
> >             [hidden email]
> >
> >             https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> >
> > John Mieske / Winword Exonar
> >
> >           http://johnmieske.org
> >
> > Space Grid Station
> >
> >
> >
> >
> >
> > _______________________________________________
> >
> > Opensim-users mailing list
> >
> >           [hidden email]
> >
> >           https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> >
> >
> >
> > _______________________________________________
> > Opensim-users mailing list
> > [hidden email]
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> >
> >
> >
> > _______________________________________________
> >
> > Opensim-users mailing list
> >
> >       [hidden email]
> >
> >       https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> >
> > John Mieske / Winword Exonar
> >
> >     http://johnmieske.org
> >
> > Space Grid Station
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> >
> > John Mieske / Winword Exonar
> >
> >   http://johnmieske.org
> >
> > Space Grid Station
> >
> >
> >   _______________________________________________
> > Opensim-users mailing list
> > [hidden email]
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> >
> > _______________________________________________
> >
> > Opensim-users mailing list
> >
> > [hidden email]
> >
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> > _______________________________________________
> >
> > Opensim-users mailing list
> >
> > [hidden email]
> >
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> > _______________________________________________
> >
> > Opensim-users mailing list
> >
> > [hidden email]
> >
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> >
> > -----Inline Attachment Follows-----
> >
> > _______________________________________________
> > Opensim-users mailing list
> > [hidden email]
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
>
>
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>



_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

John Mieske
In reply to this post by Karen_Palen
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 <[hidden email]> wrote:
I have to wonder if there is not some MySQL problem that is contributing to this.

Bob Wellman" <[hidden email]> 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 <[hidden email]> wrote:

> From: Master_Mirage <[hidden email]>
> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: [hidden email]
> 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
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>



_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users



--
John Mieske / Winword Exonar
http://johnmieske.org
Space Grid Station

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Master_Mirage
In reply to this post by Karen_Palen
I totaly agree and that is the real problem. No matter how good it is,eventualy it hits a brick wall and at best we can do is by more time and hope other solutions can be found.

In this case 'running mysql on a sep server?' is realy a good question and the best way to say it is "When will i have to run it like that?'.  Its not a question of if one should do it.

Perforamce will be the last of ppl worries when that glass gets full!

For me it wasent something i wanted to do, just something i had no choice to do. The problem will allways be there and i feel knowing that 1st, then planning on it ahaid of time is for now the best way to look at it.

I think thats why this is an excellent question to ask!


Karen Palen wrote
I have to wonder if there is not some MySQL problem that is contributing to this.

Bob Wellman" <bob.wellman@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@verizon.net> wrote:

> From: Master_Mirage <mirage123@verizon.net>
> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: opensim-users@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@lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-users
>


     
_______________________________________________
Opensim-users mailing list
Opensim-users@lists.berlios.de
https://lists.berlios.de/mailman/listinfo/opensim-users
Our New Web Page
Http://www.TritonGrid.com
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Impalah Shenzhou
In reply to this post by Karen_Palen
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 <[hidden email]>
I have to wonder if there is not some MySQL problem that is contributing to this.

Bob Wellman" <[hidden email]> 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 <[hidden email]> wrote:

> From: Master_Mirage <[hidden email]>
> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: [hidden email]
> 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
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>



_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users


_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Karen_Palen
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 <[hidden email]> wrote:

> From: Impalah Shenzhou <[hidden email]>
> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: [hidden email]
> 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 <[hidden email]>
>
> I have to wonder if there is not some MySQL problem that is
> contributing to this.
>
>
>
> Bob Wellman" <[hidden email]>
> 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 <[hidden email]>
> wrote:
>
>
>
> > From: Master_Mirage <[hidden email]>
>
> > Subject: Re: [Opensim-users] MySQL on
> seperate server?
>
> > To: [hidden email]
>
> > 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
>
> > [hidden email]
>
> > https://lists.berlios.de/mailman/listinfo/opensim-users
>
> >
>
>
>
>
>
>
>
> _______________________________________________
>
> Opensim-users mailing list
>
> [hidden email]
>
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
>
>
>
> -----Inline Attachment Follows-----
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>


     
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Impalah Shenzhou
Yes, the main problem is the p2p way opensim works. You'll never be absolutely sure if you are deleting an unused asset.

Another temporary solution affecting only to Asset server (which I want to implement on my own grid servers) is a historical backup asset server: When an asset hasn't been used for X time, delete from main database and pass to historical.

When an asset is requested, first look for it in main database, if it is not there, search on historical, if it's there, copy to main and return.

Of course there could be some overload when some old users/regions come into the grid... but keeping the main database to a minimal or "workable" size, for me is a big advantage.

I was reading time ago about the Facebook architecture with thousand of gigabytes wasted on memcache servers... This idea is similar, the main database will work as a cache and the historical can be... even a file storing system (remember that assets can be stored as xml files).

Greetings



2010/2/19 Karen Palen <[hidden email]>
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 <[hidden email]> wrote:

> From: Impalah Shenzhou <[hidden email]>
> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: [hidden email]
> 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 <[hidden email]>
>
> I have to wonder if there is not some MySQL problem that is
> contributing to this.
>
>
>
> Bob Wellman" <[hidden email]>
> 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 <[hidden email]>
> wrote:
>
>
>
> > From: Master_Mirage <[hidden email]>
>
> > Subject: Re: [Opensim-users] MySQL on
> seperate server?
>
> > To: [hidden email]
>
> > 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
>
> > [hidden email]
>
> > https://lists.berlios.de/mailman/listinfo/opensim-users
>
> >
>
>
>
>
>
>
>
> _______________________________________________
>
> Opensim-users mailing list
>
> [hidden email]
>
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
>
>
>
> -----Inline Attachment Follows-----
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>



_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users


_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Bob Wellman
In reply to this post by Karen_Palen
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 ([hidden email]) 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: [hidden email]
> To: [hidden email]
> 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 <[hidden email]> wrote:
>
> > From: Impalah Shenzhou <[hidden email]>
> > Subject: Re: [Opensim-users] MySQL on seperate server?
> > To: [hidden email]
> > 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 <[hidden email]>
> >
> > I have to wonder if there is not some MySQL problem that is
> > contributing to this.
> >
> >
> >
> > Bob Wellman" <[hidden email]>
> > 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 <[hidden email]>
> > wrote:
> >
> >
> >
> > > From: Master_Mirage <[hidden email]>
> >
> > > Subject: Re: [Opensim-users] MySQL on
> > seperate server?
> >
> > > To: [hidden email]
> >
> > > 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
> >
> > > [hidden email]
> >
> > > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> > >
> >
> >
> >
> >
> >
> >
> >
> > _______________________________________________
> >
> > Opensim-users mailing list
> >
> > [hidden email]
> >
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
> >
> >
> > -----Inline Attachment Follows-----
> >
> > _______________________________________________
> > Opensim-users mailing list
> > [hidden email]
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
>
>
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users


Do you have a story that started on Hotmail? Tell us now
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Bob Wellman
In reply to this post by Impalah Shenzhou
Impalah
 
I would especially like to discuss your ideas in voice using windows live messenger ([hidden email]) as it is directly in the area we are thinking on with distributed asset serving so there may be some useful cross feed of ideas to be had.
 
 

Date: Fri, 19 Feb 2010 10:55:06 +0100
From: [hidden email]
To: [hidden email]
Subject: Re: [Opensim-users] MySQL on seperate server?

Yes, the main problem is the p2p way opensim works. You'll never be absolutely sure if you are deleting an unused asset.

Another temporary solution affecting only to Asset server (which I want to implement on my own grid servers) is a historical backup asset server: When an asset hasn't been used for X time, delete from main database and pass to historical.

When an asset is requested, first look for it in main database, if it is not there, search on historical, if it's there, copy to main and return.

Of course there could be some overload when some old users/regions come into the grid... but keeping the main database to a minimal or "workable" size, for me is a big advantage.

I was reading time ago about the Facebook architecture with thousand of gigabytes wasted on memcache servers... This idea is similar, the main database will work as a cache and the historical can be... even a file storing system (remember that assets can be stored as xml files).

Greetings



2010/2/19 Karen Palen <[hidden email]>
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 <[hidden email]> wrote:

> From: Impalah Shenzhou <[hidden email]>
> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: [hidden email]
> 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 <[hidden email]>
>
> I have to wonder if there is not some MySQL problem that is
> contributing to this.
>
>
>
> Bob Wellman" <[hidden email]>
> 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 <[hidden email]>
> wrote:
>
>
>
> > From: Master_Mirage <[hidden email]>
>
> > Subject: Re: [Opensim-users] MySQL on
> seperate server?
>
> > To: [hidden email]
>
> > 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
>
> > [hidden email]
>
> > https://lists.berlios.de/mailman/listinfo/opensim-users
>
> >
>
>
>
>
>
>
>
> _______________________________________________
>
> Opensim-users mailing list
>
> [hidden email]
>
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
>
>
>
> -----Inline Attachment Follows-----
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>



_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users



Not got a Hotmail account? Sign-up now - Free
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Stefan Andersson-3
In reply to this post by Impalah Shenzhou

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: [hidden email] [mailto:[hidden email]] On Behalf Of Impalah Shenzhou
Sent: den 19 februari 2010 09:57
To: [hidden email]
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 <[hidden email]>

I have to wonder if there is not some MySQL problem that is contributing to this.

Bob Wellman" <[hidden email]> 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 <[hidden email]> wrote:

> From: Master_Mirage <[hidden email]>

> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: [hidden email]

> 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
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>



_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users

 


_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Impalah Shenzhou
In reply to this post by Bob Wellman
I haven't used hotmail since 2004 :-) I hope I can remember my old passwords, and my spoken english :-)

My idea is to substitute entirely grid servers (UGAIM) by php/aspx/python/ruby scripts and run them on a standard web server, for improving stability (a script shouldn't hang entire server) and allowing clustering (and "proxying"), for example.

I've created a project in sourceforge with the php approach of this: https://sourceforge.net/apps/trac/unga/wiki.

Actually I am cleaning the sources and resolving some weird problems on linux-64, so there aren't any sources available yet. I haven't too much time but I promise it will be soon. It is quite stable, but I prefer to give an usable system too to the public.

Talking about geographically distributed servers... I suppose you want a system like Aikamai or amazon S3. Detect country and serve from the nearest machine, or even duplicate servers over countries.

The last information I got about that (job partners who work on that kind of media projects) is that it hardly depends on ISPs. For example Aikamai, in Spain, has agreements with Telefonica to redirect traffic to Aikamai local servers (located near Telefonica's main nodes) instead of main. So connecting from Spain to pages like Antena3 (private spanish television, who uses Aikamai for storing assets) and requesting videos or images is even faster than local storage on the company web server.

Contact with me by mail and we'll try to synchronize our agendas to talk.

Meanwhile... this is one of the most useful threads I have read in months. I've taken more than 20 possible ideas for my TO-DO list.

Greetings



2010/2/19 Bob Wellman <[hidden email]>
Impalah
 
I would especially like to discuss your ideas in voice using windows live messenger ([hidden email]) as it is directly in the area we are thinking on with distributed asset serving so there may be some useful cross feed of ideas to be had.
 
 

Date: Fri, 19 Feb 2010 10:55:06 +0100
From: [hidden email]

To: [hidden email]
Subject: Re: [Opensim-users] MySQL on seperate server?

Yes, the main problem is the p2p way opensim works. You'll never be absolutely sure if you are deleting an unused asset.

Another temporary solution affecting only to Asset server (which I want to implement on my own grid servers) is a historical backup asset server: When an asset hasn't been used for X time, delete from main database and pass to historical.

When an asset is requested, first look for it in main database, if it is not there, search on historical, if it's there, copy to main and return.

Of course there could be some overload when some old users/regions come into the grid... but keeping the main database to a minimal or "workable" size, for me is a big advantage.

I was reading time ago about the Facebook architecture with thousand of gigabytes wasted on memcache servers... This idea is similar, the main database will work as a cache and the historical can be... even a file storing system (remember that assets can be stored as xml files).

Greetings



2010/2/19 Karen Palen <[hidden email]>
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 <[hidden email]> wrote:

> From: Impalah Shenzhou <[hidden email]>
> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: [hidden email]
> 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 <[hidden email]>
>
> I have to wonder if there is not some MySQL problem that is
> contributing to this.
>
>
>
> Bob Wellman" <[hidden email]>
> 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 <[hidden email]>
> wrote:
>
>
>
> > From: Master_Mirage <[hidden email]>
>
> > Subject: Re: [Opensim-users] MySQL on
> seperate server?
>
> > To: [hidden email]
>

> > 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
>
> > [hidden email]
>
> > https://lists.berlios.de/mailman/listinfo/opensim-users
>

> >
>
>
>
>
>
>
>
> _______________________________________________
>
> Opensim-users mailing list
>
> [hidden email]
>
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
>
>
>
> -----Inline Attachment Follows-----
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>



_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users



Not got a Hotmail account? Sign-up now - Free

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users



_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Stefan Andersson-3
In reply to this post by Stefan Andersson-3

Of course I meant to say

 

“that we can safely assume that ‘a’ will not experience any changes in ‘c’, ever”

 

Also, it should be noted that this is by and large an SL protocol issue, as the viewer is actually the one that decides to upload a shirt as a new asset (the server don’t even recieve the info about what asset it is replacing) and also, if memory serves me right, the client decides what guid to assign the various new assets.

 

So a malicious client could try to target specific asset id’s to replace them – something that actually becomes more relevant in a hypergrid setting.

 

/Stefan

 

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Stefan Andersson
Sent: den 19 februari 2010 12:32
To: [hidden email]
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: [hidden email] [mailto:[hidden email]] On Behalf Of Impalah Shenzhou
Sent: den 19 februari 2010 09:57
To: [hidden email]
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 <[hidden email]>

I have to wonder if there is not some MySQL problem that is contributing to this.

Bob Wellman" <[hidden email]> 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 <[hidden email]> wrote:

> From: Master_Mirage <[hidden email]>

> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: [hidden email]

> 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
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>



_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users

 


_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Bob Wellman
In reply to this post by Stefan Andersson-3
 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: [hidden email] [mailto:[hidden email]] On Behalf Of Impalah Shenzhou
Sent: den 19 februari 2010 09:57
To: [hidden email]
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 <[hidden email]>

I have to wonder if there is not some MySQL problem that is contributing to this.

Bob Wellman" <[hidden email]> 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 <[hidden email]> wrote:


> From: Master_Mirage <[hidden email]>

> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: [hidden email]

> 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
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>




_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users

 



Do you have a story that started on Hotmail? Tell us now
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

John Mieske
In reply to this post by Karen_Palen
What about Bob ?  hahahaha   sorry had to say it..  Tension breaker .. :: laughs ::

On Fri, Feb 19, 2010 at 4:33 AM, Karen Palen <[hidden email]> wrote:
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 <[hidden email]> wrote:

> From: Impalah Shenzhou <[hidden email]>
> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: [hidden email]
> 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 <[hidden email]>
>
> I have to wonder if there is not some MySQL problem that is
> contributing to this.
>
>
>
> Bob Wellman" <[hidden email]>
> 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 <[hidden email]>
> wrote:
>
>
>
> > From: Master_Mirage <[hidden email]>
>
> > Subject: Re: [Opensim-users] MySQL on
> seperate server?
>
> > To: [hidden email]
>
> > 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
>
> > [hidden email]
>
> > https://lists.berlios.de/mailman/listinfo/opensim-users
>
> >
>
>
>
>
>
>
>
> _______________________________________________
>
> Opensim-users mailing list
>
> [hidden email]
>
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
>
>
>
> -----Inline Attachment Follows-----
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>



_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users



--
John Mieske / Winword Exonar
http://johnmieske.org
Space Grid Station

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Master_Mirage
In reply to this post by Bob Wellman
It is verry clear to me and not hard to understand the verry real problem of data storage. FragStore helps but its not the answer. We simply cannot keep dumping more and more data in and never take any out.
With the use of OAR and now IAR, data storage demmand will be evan a bigger problem as how fast ppl will and can fill up there assests. Something needs to take out the trash or it will blowup in ppls faces at a rate never seen sofar. With that said, Im not say the sky is falling ether. Mysql can deal with many many gigs of data and small grids like mine are hardly pushing it. At 23.4g assests thats around 1/4 the cap. it can actualy handle. With things like OAR and IAR it will and does shorten the time we thought we had to come up with a better solution. At 1st the progrssion is slow but as more content is created the faster it begins to fill. Copys of copys forming a region that is also copyed via oar...
Users inventorys that users never clean lol.
Anyway the Toys are great OAR,IAR excellent but we stall have the same problem as allways and now less time to work out something better i feel.


Bob Wellman wrote
 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@lists.berlios.de [mailto:opensim-users-bounces@lists.berlios.de] On Behalf Of Impalah Shenzhou
Sent: den 19 februari 2010 09:57
To: opensim-users@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@yahoo.com>
I have to wonder if there is not some MySQL problem that is contributing to this.

Bob Wellman" <bob.wellman@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@verizon.net> wrote:

> From: Master_Mirage <mirage123@verizon.net>

> Subject: Re: [Opensim-users] MySQL on seperate server?
> To: opensim-users@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@lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-users
>



_______________________________________________
Opensim-users mailing list
Opensim-users@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/
_______________________________________________
Opensim-users mailing list
Opensim-users@lists.berlios.de
https://lists.berlios.de/mailman/listinfo/opensim-users
Our New Web Page
Http://www.TritonGrid.com
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

John Mieske
You know...  because of this group I have learned a lot. Databases are an item a lot of do take for granted.  Including me. I do believe you hit the nail on the head here. The whole problem is the databases from the get go. Once that is solved, then matters like "More then one computer to separate SQL from servers" would actually not be a problem here. So preach it brotha..  we need better inventory control bottom line. hahahaha.

On Fri, Feb 19, 2010 at 9:43 AM, Master_Mirage <[hidden email]> wrote:

It is verry clear to me and not hard to understand the verry real problem of
data storage. FragStore helps but its not the answer. We simply cannot keep
dumping more and more data in and never take any out.
With the use of OAR and now IAR, data storage demmand will be evan a bigger
problem as how fast ppl will and can fill up there assests. Something needs
to take out the trash or it will blowup in ppls faces at a rate never seen
sofar. With that said, Im not say the sky is falling ether. Mysql can deal
with many many gigs of data and small grids like mine are hardly pushing it.
At 23.4g assests thats around 1/4 the cap. it can actualy handle. With
things like OAR and IAR it will and does shorten the time we thought we had
to come up with a better solution. At 1st the progrssion is slow but as more
content is created the faster it begins to fill. Copys of copys forming a
region that is also copyed via oar...
Users inventorys that users never clean lol.
Anyway the Toys are great OAR,IAR excellent but we stall have the same
problem as allways and now less time to work out something better i feel.
B-)


Bob Wellman wrote:
>
>
>  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: [hidden email]
> [mailto:[hidden email]] On Behalf Of Impalah
> Shenzhou
> Sent: den 19 februari 2010 09:57
> To: [hidden email]
> 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 <[hidden email]>
> I have to wonder if there is not some MySQL problem that is contributing
> to this.
>
> Bob Wellman" <[hidden email]> 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 <[hidden email]> wrote:
>
>> From: Master_Mirage <[hidden email]>
>
>> Subject: Re: [Opensim-users] MySQL on seperate server?
>> To: [hidden email]
>> 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
>> [hidden email]
>> https://lists.berlios.de/mailman/listinfo/opensim-users
>>
>
>
>
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> 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/
> _______________________________________________
> Opensim-users mailing list
> [hidden email]
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
>

--
View this message in context: http://n2.nabble.com/MySQL-on-seperate-server-tp4575740p4598171.html
Sent from the opensim-users mailing list archive at Nabble.com.
_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users



--
John Mieske / Winword Exonar
http://johnmieske.org
Space Grid Station

_______________________________________________
Opensim-users mailing list
[hidden email]
https://lists.berlios.de/mailman/listinfo/opensim-users
Reply | Threaded
Open this post in threaded view
|

Re: MySQL on seperate server?

Master_Mirage
Well john, Im really not trying to scare ppl off. I love ppl getting there stuff running making there worlds and none of this is stopping that. The more that take whatever thay have and start doing it the better the realtime info will be. Less guessing and more 'This is what i have,this is the problem i had and this is what i did to fix it' matters. This problem, alot wount run into for a verry long time but thay should at least beaware of it. New ppl starting armed w. that can plan things out better b4 thay pop out the 1st block.
The more hindsite we gather the better it is for all of us. I try not to talk about things that i havent allready done or problems i havent seen. I see good questions like this, thay dieserve a good answer.
When i asked the same type of questions way back the replys ranged from 'Depends' or 'You will never fill it' to the one i hated the most 'Email me when you get to that point..lol'. If theres something out there that i should know about the better off i am when planning it out. Had i gotten a real answer then, it would have saved alot of time and guessing and agravation. It would not have stopped me from doing it though. I simply would have started out a bit differntly is all.
My advice to this is "If you can, DO make mysql on a sep box' Max it out and pull the mon and keyboard from it lol. That box is now dedicated to do nothing else but that.
This is something I ended up having todo and continues to work quite well.
Anything passed today's git rev. is guessing. There working on it and this could change later. We cant flip to the last page of the opensim book and see who done it because its still beeing written.



John Mieske wrote
You know...  because of this group I have learned a lot. Databases are an
item a lot of do take for granted.  Including me. I do believe you hit the
nail on the head here. The whole problem is the databases from the get go.
Once that is solved, then matters like "More then one computer to separate
SQL from servers" would actually not be a problem here. So preach it
brotha..  we need better inventory control bottom line. hahahaha.

On Fri, Feb 19, 2010 at 9:43 AM, Master_Mirage <mirage123@verizon.net>wrote:

>
> It is verry clear to me and not hard to understand the verry real problem
> of
> data storage. FragStore helps but its not the answer. We simply cannot keep
> dumping more and more data in and never take any out.
> With the use of OAR and now IAR, data storage demmand will be evan a bigger
> problem as how fast ppl will and can fill up there assests. Something needs
> to take out the trash or it will blowup in ppls faces at a rate never seen
> sofar. With that said, Im not say the sky is falling ether. Mysql can deal
> with many many gigs of data and small grids like mine are hardly pushing
> it.
> At 23.4g assests thats around 1/4 the cap. it can actualy handle. With
> things like OAR and IAR it will and does shorten the time we thought we had
> to come up with a better solution. At 1st the progrssion is slow but as
> more
> content is created the faster it begins to fill. Copys of copys forming a
> region that is also copyed via oar...
> Users inventorys that users never clean lol.
> Anyway the Toys are great OAR,IAR excellent but we stall have the same
> problem as allways and now less time to work out something better i feel.
> B-)
>
>
> Bob Wellman wrote:
> >
> >
> >  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@lists.berlios.de
> > [mailto:opensim-users-bounces@lists.berlios.de] On Behalf Of Impalah
> > Shenzhou
> > Sent: den 19 februari 2010 09:57
> > To: opensim-users@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@yahoo.com>
> > I have to wonder if there is not some MySQL problem that is contributing
> > to this.
> >
> > Bob Wellman" <bob.wellman@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@verizon.net> wrote:
> >
> >> From: Master_Mirage <mirage123@verizon.net>
> >
> >> Subject: Re: [Opensim-users] MySQL on seperate server?
> >> To: opensim-users@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@lists.berlios.de
> >> https://lists.berlios.de/mailman/listinfo/opensim-users
> >>
> >
> >
> >
> > _______________________________________________
> > Opensim-users mailing list
> > Opensim-users@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/
> > _______________________________________________
> > Opensim-users mailing list
> > Opensim-users@lists.berlios.de
> > https://lists.berlios.de/mailman/listinfo/opensim-users
> >
> >
>
> --
> View this message in context:
> http://n2.nabble.com/MySQL-on-seperate-server-tp4575740p4598171.html
> Sent from the opensim-users mailing list archive at Nabble.com.
> _______________________________________________
> Opensim-users mailing list
> Opensim-users@lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-users
>



--
John Mieske / Winword Exonar
http://johnmieske.org
Space Grid Station

_______________________________________________
Opensim-users mailing list
Opensim-users@lists.berlios.de
https://lists.berlios.de/mailman/listinfo/opensim-users
Our New Web Page
Http://www.TritonGrid.com
123