About Second Life, databases, and such...

I'm pulling stuff from a bunch of different posts, so I'll do my best link to the threads (not specific posts, sorry) when I quote from them. No promises for complete consistency, but I'll try.


That sensence reminds me of something Joel Spolsky said about "core business competencies"2). He said you should do them in-house. Trimming, you're right, SL is in the data storage business, and they're in that business like no one else has been since the Internet started. Both of those things said, I think those are good reasons for them them to use MySQL.

It's open-source software, which means they not only have a great database to start with, but also an open codebase to extend upon. I'm not wholly up-to-date on Oracle or MS-SQL servers, but I would doubt they are as easily built upon -- not in the API sense, in the "re-writing chunks" sense. If their core competency is data storage in a new and unique way, MySQL affords them extensibility and specialization that no other database offers.

Oracle and MS-SQL may scale better, may supports grids better, but (from what I know) I doubt they can be as easily malleable at the lowest levels of code like MySQL can.


Linden Lab is already having trouble fixing a lot of non-database/asset realted problems, do they really have the time, money, or man-power to dedicate to retrofitting another database? A lot of my argument here will ultimately be opinion and speculation, but with the Open Source Metaverse Project, MASSIVE-3, and other projects that are popping up; I don't think Second Life can afford to stop progressing for the sake such a shake-up.

I won't lie, I like MySQL. It's my database of choice. However, as much as I think it is up to the task -- LiveJournal uses a MySQL cluster, and they don't have the number of failures or lost data that SL does -- without hard numbers or a few more years of experience, I'd feel foolish being too much of a MySQL advocate here. However, even if it isn't ideal, it's what they're using.

Can they afford the migration right now?


Yes it does. If it's the administration, that can much more easily replaced than their existing codebase. Personally, having dealt with both MySQL and system fuck-ups this year, I'm inclined to think that when Linden Lab says, "Our harddrives crashed," it's a hardware issue. If it is a hardware issue, then that's not MySQL's fault anymore than it would be Oracle's fault.

In fact, I can't think of a time when we've had a major outage that is was because of software. Weren't both of the big, 2004 crashes because of hardware/disk failure of a physical nature?


Don't outsource your core competency. Open-source software allows you to import and modify your core competency. Paying Oracle or someone else would not only outsource their core competency -- data access -- but it would cost them time, money, and man hours adjusting existing code. They might, and probably would, end up with a better project, but would anyone still be around to use it.

(Anyone who wants to throw Joel Spolsky's comments about how Netscape lost the browser war by restarting Mozilla from scratch are being proven wrong now as Firefox gains marketshare over IE, don't. IE is losing the browser war because it's stagnating. I don't see various 3D world projects either grinding to a halt or not arising given how easy it is now to build a 3D world. I also don't see Linden Lab making Second Life as malleable and dynamic as Mozilla is.)


According to this, it's already started.


When Second Life starts using more bandwidth and transferring more information than BitTorrent, Apache, or IRC do individually, I'll agree with you. Until then, I'm going to disagree and say that the problem isn't that open-source software can't keep up, it's because Linden Lab's business model is a centralized system in a world that thrives on distributed networks.

Second Life wouldn't have half as many data storage or scalability problems if Linden Lab and Philip Linden realized that they are creating a communication platform, not an eighth continent bound by physical laws. They need to understand the "Metaverse" was a fictional extension of the BBS, put down their dog-eared copies of Snowcrash, and start grokking how the Internet works.

---

1. "A few words about databases..."

2. "In Defense of Not-Invented-Here Syndrome"

3. "Another word on databases..."

they're going to have to jarod. they've backed themselves into a corner. at this point they no choice. you're a programmer, you know that sometimes you just have to start over and limp your current system along until you've reproduced something that works.

your point that mysql is open source is a good one. innovative technologist often rewrite major components of open source engines to suit their purposes. i imagine that ll's version of MySQL looks very little like the download at this point. but i'm still thinking they might file thirteen the whole thing after they've completed a working version.



agreed. they would pay loads of money for a more robust but less flexible product. it's a critical call. it was a critical call the first time around. at least they have some experience to draw from now.



true



actually, you're agreeing with the point of the other database posts that ll's core business is data storage and distribution. the internet is only a fair comparison if you look at it several years ago. a vast myriad of tools have been developed for it over time. a few have survived specifically because people found them useful.

my primary concern with second life right now is that people aren't finding the software useful for anything. i'm honestly afraid that it's going to used as a failed early model of w3b software that vanished when faced with competition focused on providing internet tools for people to use in business and education. the entertainment market, especially the gaming sector, is far too fickle and transitory to offer steady financial support to an evolving technology.

While the game industry can't provide steady financial support it's one of only two sectors that SL could be developed. The second is training for the military (and we know there is already a player in that niche ;)

THeres a lot of longwinded stuff in here im not going to read. If you are in the industry though you don't need to be convinced that Oracle is a stronger database platform in every way (except price) than MySQL. MySQL is meant for small to medium, non mission-critical applications. Oracle costs lots more, and there are good reasons for it. If uptime is important, if performance, scalability and all those other good things are concerns, MySQL is a poor choice. Why is MySQL the best choice as a database? Thats a dumb question to me, but i had to ask it. Hey how about Microsoft Access!

FileMaker Pro is looking more attractive all the time

Tell that to LiveJournal, Yahoo!, and a few others. Seriously.

And before anyone says, "Yes, but they don't have as many transactions per second as the asset server," read below...



1. Because it's open, which means that since data storage if Linden Lab's core competency they can modify and expand on an already existing, semi-decent platform. It may not be Oracle grade as databases go, but Oracle's code isn't as dynamic.

2. It is free. If SL's grid were actually several grids with several asset servers, we wouldn't be having these problems. Free should allow them to setup several asset servers, distribute the load. This goes back to administration, it's a centralist mindset, an old mainframe mindset.

3. It is on par with Oracle.

4. Because NASA invented "mission critical."

The biggest argument for Oracle seems to be that "it handles large amounts of mission critical transactions perfectly." Well, then... I don't have room to argue. If Second Life is always going to be one main grid -- we'll see how the Teen things goes -- with one asset server, then I cede my arguments to you. They should invest in Oracle immediately.

As we say in England, 'nuff said.

Anyone with SysAdmin or database experience knows MySQL is not suitable for highend work. At small workloads - it is on par with Oracle (even faster, since it doesnt have the overhead). But, unlike Oracle/ASE/etc it doesnt scale. This means, you hit major problems once your tables begin getting over 5GB (infact MySQL craps out if you have tables bigger than around 4GB, and begins throwing spontaneous errors)

Yes it's open. But no, that doesnt matter - for the same reason financial institutions choose commercial DB's (and they too are in the database business). It's because they scale, and they get a reliable support contract in the event of something failing. In the area's that count, those DB's are superior.

At the moment it's a moot point however, since LL is just giving CPR to the asset server while they can work on a clustered system, but this current system is looking like it's going to have to hold out for at least another year yet, and if there's any hope of a snap-in Commercial DB looking like it could take the place of MySQL within a few weeks, LL should look at it seriously.

Btw: MySQL is only running on the userserver. LL moved MySQL away from the asset server in January because it was dying.

-Adam

The 4GB limit was fixed in 4.x as far as I know. What other scaling problems does it have? Can anyone back up any of these claims about it mysteriously dropping rows? I have wrangled millions of rows in a single table with MySQL and not had any such problems. If they do exist and were not caused by DBA or SA error, I would like to see some specific info and proof relating to it, so that I know what to watch out for. Looking for stuff that can be duplicated or confirmed in some other way - not anecdotes.

MySQL supports some off-spec stuff, like AUTO_INCREMENT and LIMIT. If LL is taking advantage of these things, there is no drop-in replacement.

The argument about keeping it in-house by using an open-source RDBMS package doesn't hold much water, I think. If it did, no one would give a crap about Oracle or MSSQL or Informix or any other closed database platform. The deepest level of involvement you want with the internals is to have someone really knowledgeable tuning the config files, and to have your developers know how to do indexing and other stuff like that to make things run smoother. Manhandling the source tree should be a last-resort type deal.

As I see it, the only huge disadvantage to MySQL is its lack of a procedural language. I've heard that is being worked on. If you're not writing an app that would benefit from a PL, I guess it's a moot point.

I'm a MySQL fan - its my DB of choice for nearly all of my clients.

And I agree with almost all of the "pro-MySQL" points listed here.

But I do want to correct something: Utilizing open-source software because you can customize and extend it is ONLY a valid argument IF you have the staff and resources to take advantage of it. I like LindenLab; but at 30 employees, I don't know if they have enough coders to spend time re-writing chunks of the DB.

ORACLE is tops in the industry for 2 reasons:

1) They were high-performance early on in the Relational-DB scene, and have therefore built an image and a brand name that is backed up pretty well by their performance, and

2) More Importantly: Because ORACLE is a large professional organization that can offer customer support, service contracts, and custom installations (among other things). Many companies really WANT (and in a lot of cases, need) that kind of support - either so they don't have to worry about the system as much themselves (or staff up experts in-house); OR because they need that support to train/migrate an existing staff. With Open-Source, you don't usually get organized help or professional services - the Open-Source way of developing things is necessarily a bit more chaotic and messy (at least to the outside observer).

I personally don't believe that ORACLE has any "magic" software that does things no one else will ever be able to implement (and don't get me started on "Software Patents"! *groan*)... In most Enterprise-level situations, I personally would lean towards ORACLE - but LL & SL are a unique situaton. They're not a big company, they need to manage their financial assets and hardware carefully, and they're working on a system that is constantly evolving and changing.

Bottom line is, only they can fully know their own situation. If they make bad decisions, they will end up paying the price for it. Personally, I just concern myself with enjoying SL - if they have unacceptable downtime, I won't care WHAT causes it - its not going to change how much fun I'm having when I can't play.

As we now know, it wasn't the DB itself causing most of the issues; but rather an undiscovered "race-condition"-type bug. Which are always nasty when you don't catch them during the design-phase of a coding project!

Take care,

--Noel "HB" Wade
(Tread Whiplash)

4.1 was only just released. 4.X still isnt fully standard yet.

However, in answer to your other question - put a machine up to a high load (5.0+) and start running SQL queries. After a short while, some of them will start to drop. Myself and Nikoali Weber discovered this purely by accident, and have been attempting to find a fix/workaround for the problem.

-Adam

Bad!

What did it say in the MySQL logs?

You are making the classic mistake when comparing Open Source solutions to Closed Source Solutions. You are making the comparison of "pay" versus "free (as in beer)".

MySQL is about FREEDOM, not FREE as in no $$$ cost. The source code is open so that you can make modifications to the software yourself or by contracting those changes out to 3rd party developers (or even MySQL developers directly!).

You can't compare the price of software, and use that comparison to make technological decisions. Price doesn't matter. There is no direct correlation you can make using price alone.

The recent issues were explained by LL to be 1. from hardware issues, 2. from a few bugs in the client, and 3. the way they setup the system. They are working on fixing the hardware, fixing the bugs (which they did in record time over the New Years Eve day and night), and upgrading the system to scale better. Simply buying Oracle and slapping it into the system isn't going to magically fix things.

The problem wasn't: oh, we went with MySQL instead of Oracle because of price. In fact, MySQL probably costs them more, but allows them to customize it for the SL world. Maybe I'm wrong.

I'm a little worried, yes, as I heavily my time and money in the system as do many others. But I trust that LL is made up of an intelligent, savvy, and talented team.

I generally dislike appealing to work experience because I prefer the appeal of logic, but:

Are there any Oracle DBAs on this thread who have run 100 GB databases with millions of transactions per day? I was chief architect at one such company, so I have a bit of experience with those types of loads.

We originally ran on Oracle and ended up, yes, you guessed it -> migrating to MYSQL.

We were running into problems with Oracle at high loads and little memory, so we had to do something.

We had another choice other than mysql which was migrating to a mainframe which is what our oracle reps were suggesting. Yes, it made sense. Mainframes could handle the loads without sneezing and they had a long history of integration with oracle.

But the cost was too prohibitive and we felt we could cluster the mysql solution nicely. Which is what we did and now load is much lower.

We were also nervous about migrating to a solution which simply further centralized all our data - oracle replication did not work for us well in a high update environment. One big mistake on our DBA's part (guess what, they are human, btw! shocking!) and we would be royally hooped.

So now the solution is mysql with caching environments on the application servers to keep load low. Data is partitioned and replicated and sometimes machine(s) implode beyond recognition or our dba accidentally does something dumb and a small subset of the customers cry a river. But no longer are there any company wide blackouts which upsets everyone beyond belief.

In the end, my general architectural conclusion is:

- never rely on complicated centralization of data, no matter how tempting it sounds
- assume that your sysadmins will hoop a centralized setup at least once a month no matter how brilliant they seem

- AND MOST IMPORTANTLY OF ALL - run everything at very very low load, cpu and memory. If you start to see anything over 25% cpu / memory / disk, you are suffering from a performance CRISIS. It's time to add more servers.

I know the last one is hard to swallow, but it's completely true.

Between peak load and extra load when you're rebooting / rebuilding arrays / suffering from software error, that extra 75% safety margin will be chewed up very very quickly and you will start to lose queries / data / whatever, no matter what platform you are on.

Unfortunately, you have to make a choice because of that last point. Go mainframe or go grid computing. We chose the latter..

And so, btw, is everyone else as far as I know.

The big guys that run oracle in high mission critical stuff have been pretty much doing the same thing:

Keeping load very very low and clustering oracle (across mainframes) as if it were a very shaky data storage. Except they do it differently with MQSeries and data is 'clustered' at every segment of the path, so they can go forward and back to recover data.

Oh yeah, and don't rely on database atomic transactions. Use an application layer with hardcoded rollback capabilities.

If something fails, you really need massive logging anyways to find out why
The database is usually a bit brain dead when it comes to rolling back
The limitations that it puts on you (centralized data, single tables, etc) will bite you in the ass time and time again.

Last comment!

The 4GB limit was never an issue in our migration to mysql and we dealt with tables waaay bigger than 4GB.

Where are you getting your information?

Perhaps just a few comments here...

In my past experience, "bottlenecks" arise mostly from three issues...

1) Hardware failures that were not properly "covered" with an emergency plan.
2) Inefficient algorithms.
3) Not enough fine-tuning (and my, what a difference does that make!

Saying that, I'd expect that a perfectly tuned, highly efficient algorithm on a Windows system running Access would handle the kind of load SL has very well

It would perhaps need 20 times as many machines, and the appropriate number of high-cost licenses, though. And, of course, an excellent Windows sysadmin - not someone who just got a MS certificate and has spent the last 5 or 6 years loading CDs on the tray and pressing buttons on dialog boxes.

LL adds to all the above requirements two trade-offs:

1) Everything has to be dirt cheap. LL is a small company, with a small budget, and with a small income from a too cheap "entertainment platform" (how many "games" do you know that you can pay US$ 9.95 for lifetime access?). The more licensing you add, the more "mission critical" hardware you have, and the more outsourcing contracts you need to make your operation work... the more expensive it gets, to a point where you cannot support the business model.

2) One of these days (2005 is a good target), LL will start releasing parts of its software - either on the server or on the client - into the open source, and expect the 5000+ programmers in SL to contribute their coding efforts to make SL a much better platform and with tons of new features.

So, throwing everything away, and restarting with a few Sun Enterprises running Oracle, or a few IBM mainframes using DB2... means skyrocketing the cost of the business operation, and forget about any "open source" release of the code. After all, how many of you have spare SPARC motherboards at home...?

Having said this, I can fully understand LL's decisions. I'd do the same myself. It's far cheaper to add more hardware to keep the servers well below a threshold of constant CPU load (blaze, you're so right!!). And a good combination of programmers and sysadmins will certainly be able to design a better algorithm, deployed on a better distribution of critical access to resources, that will scale better.

Throwing high cost software and hardware on a problem is just masking a bad algorithm/architecture, not fixing it. The end result is, you'll have problems further along the road again, when the order of magnitude of the grid size changes - say, when we have 170,000 or 1,700,000 users.

There are lots of ideas in the SL grid that have lead to problems that I can't understand how they managed to pull off for so long. A centralized asset server (even if on redundant, load-balancing machines)? A centralized user server? How did LL manage to think that this would ever scale well? Also, like Morgaine Dinova so fruitfully discussed a few months ago, a model that keeps 90% of the grid unused in terms of CPU power, and have the remaining 10% completely overloaded with events of 30+ avatars attending, is certainly badly designed.

For me, it's not a question if "MySQL can support a complex operation like SL". I have several good experiences using MySQL on complex, data-intensive operations, and it certainly handled the load quite well, despite many quirks (and yes, MySQL has many!). All of them relied upon clever architecture and well-thought algorithms. Of course, a few used MySQL as a "cheap replacement" of a high-end database system - when the problem was in the architecture, not in the database itself.

Looking from outside, what I see is, 3000-5000 simultaneous users (not many more) getting streams of data around 100 Kbps, on a grid of 400+ servers. This is not much load. I have seen FreeBSD servers serving 1000 users simultaneously with streams of 30-50 Kbps per user - on a single server. The database was MySQL. The machines were bi-Pentiums. The RAM was about 1 GB. Of course, all the cache ran in memory, and not on the RAID disks. Developing the correct architecture that enabled this took some time and experience - but this was the sort of solution that actually replaced 70 Windows boxes with MS SQL (same hardware), just by having a much better design.

In the end... I agree, LL has to rethink their whole "grid design". I disagree, MySQL is not the culprit, or the use of open source tools. Open source tools can and will be on par with performance and reliability. You just need to employ them well. How? *shrugs* I don't know! Give me access to the code and 6 months time, and I'll make a few suggestions for improvement But, then again, this is exactly what is supposed to happen in a year or so

Jarod, I'm sorry I didn't reply to this sooner. Your comments were strong enough, and well-thought out enough, that I needed to give them some time to sink in and digest.... and then I got distracted. I don't read these forums much... not in the habit of checking them.

Your comments about owning the code and sticking with MySQL do have some validity, but.... LL is awfully small, and I don't think they've developed very much DB expertise.

What they need, first and foremost, is at least one BRILLIANT DB guy/gal. They need someone who knows, ideally, several databases, and can help them choose their future architecture and design. And with that expertise, they need major corporate focus and commitment to getting it right. Beyond that, it's just implementation.

If they do want to stick with MySQL, they may need to get one or more of its developers on staff... if they commit that heavily to the open-source environment, they should be trying to fund its development, and having a MySQL developer on staff will give them insight into the code design they'd otherwise have trouble getting.

I'm honestly not sure they have time to do a refactoring to a grid environment. They're already fairly committed to the central-DB model, and it strikes me that they're in total crisis mode at this point. So many things are broken, and it's almost always the DBs involved. Whatever gets done, has to get done FAST, and that usually means throwing money at the problem.

Also, as far as outstripping open-source stuff... I didn't mean ALL open source, just open-source databases. The SL model is centralized data, and I'm just not sure that any of the open-source databases can handle this kind of data volume and transaction load. If they can't quickly refactor across several smaller databases, then they're likely to have to go to some form of big iron.

If they switch away from MySQL, I do agree that, over the long-term, they may be taking a big hit to their future viability as a corporation. But if they die in the next six months because all the users bail from frustration, it won't matter anyway. They've backed themselves into a corner, and all the remaining possible choices appear sub-optimal.

Trimming, I don't think "users will bail out from frustration". Users are pretty tolerant See how many hundreds of millions still use Windows

And BTW, yes, I fully agree they should invest in a team of top system and database administrators. There are certainly lots of them around after the crash of the so-called "new economy".

Actually, SL is not really centralized data, but partially centralized data (the user and asset servers). Still, your insights are valid. You just need one bottleneck to screw up the whole system And SL has two!

I heard it from a Linden that the asset server actually uses text files for each prim, served on request by Apache, and improved in speed by the Squid HTTP cache. That makes sense, as some prims don't get updated for months, so if you can have squid cache the prim data, you can greatly speed up each sim computer asking for the data on that prim.