mysql 5.0

Mysql 5.0 is now available. It looks like a major upgrade:
http://www.mysql.com/news-and-events/news/article_959.html

It's gettin good press:
http://www.sqlsummit.com/articles/MySQL5.htm

What version of mysql does SL use? Will SL be upgraded to 5.0 anytime soon?

I can't believe I got no response on this one. Well, I'm not lettin the question die. Especially in the wake of recent inventory losses. The asset server database NEEDS some upgrades.

SL uses mysql 4.0.24. We're just starting compatibility testing with 4.1. While 5.0 has alot to offer, it's still in development, and mysql AB recommends: "use this for previewing and testing new features" - so we won't be switching to it until well AFTER it's declared "stable".

It's true that the inventory server needs an upgrade, which is in progress. We have a shiny new system but ran into stability problems with the initial configuration, and so had to delay putting it online until it was too late. Now it's online and we're (slowly) moving residents over to it.

Err. MySQL5 isnt even in production release yet.

Going into an unsupported database, without seeing any long term reliability tests in it yet, just begs for a catastrophic accident. You want to increase stability by upgrading to an unproven, potentially unstable release? I dont grasp your logic.

LL is doing the right thing, keeping with what has been proven to work. The only thing 5.0 offers is new features. It is not a stability/reliability upgrade. It's a matter of bringing MySQL to complete SQL92 compliance, which frankly is irrelevant for LL.

-Adam

Also, the asset server and the DB server are two very different pieces of software.

The DB server (aka user server) is MySQL, the asset server a long long time ago was MySQL, but LL eventually replaced it with an in-house database system when it was proven that MySQL was going to collapse badly under the strain of several TB sized tables.

Oh! Could this be the reason for the sudden issues people are having with logins?
Keep up the good work Ian

That is the reason, yes. Things are improving - the most recent slowdowns are actually from the load of moving people from old to new. We're going to be a little less aggressive with that now that we've made some progress, so things should improve.

heh yea repace stable and fast with inhouse that has constant issues (wheres my gold stars)

Umm... I dropped them on your profile but I guess there must have been some issues with asset transfer. *runs and hides*

DB switched because MySQL couldnt handle terabyte tables?

With the MyISAM storage engine in MySQL 3.23, the maximum table size was increased to 65536 terabytes

source:
http://dev.mysql.com/doc/refman/5.0/en/table-size.html


The maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits.

What storage engine and OS is LL usin with MySQL? MySQL should be more than able to handle terabyte tables.

As to stability, MySQL Ab seems to think that its production-ready:
http://www.mysql.com/news-and-events/news/article_959.html

and
http://dev.mysql.com/doc/refman/5.0/en/stability.html


quote: "With MySQL 5.0 being that stable, I encourage you to do all of your new database development using MySQL 5.0."

As for 5.0 being only a "standards compliance" release:

"Performance enhancements: A number of improvements were made in MySQL 5.0 to improve the speed of certain types of queries and in the handling of certain types. These include:..."

or

"Precision Math: MySQL 5.0 introduced stricter criteria for acceptance or rejection of data, and implemented a new library for fixed-point arithmetic. These contributed to a much higher degree of accuracy for mathematical operations and greater control over invalid values. See Chapter 22, Precision Math."

and

"Performance Improvements in the InnoDB Storage Engine:"

or

" The performance of the MySQL client library is increased through the use of a receive buffer that greatly reduces the number of system calls which the library needs to make. This can speed up the retrieval of large result sets by a factor of 2."

etc, etc, etc.

I'm not sure what your point is here.



They wouldn't have to try very hard to introduce stricter criteria for all of their data. Geez, you declare something a varchar(10) and give it something 12 characters long it will just be helpful and chop it off for you. Or accept that February 31st date converting it to March 3 or something. Blech.

lol w/ Malachi Petunia

Big difference between what it says it can do, and what it can do. For asset storage, MySQL is the wrong tool for the job, no questions asked.

Also, while MySQL may /support/ 65kTB databases, it wont in practice. A table bigger than about 4GB will start running very slowly; especially for any moderately complicated queries; especially not when you scale that to 8TB.

"MySQL AB today announced the general availability of MySQL 5.0, the most significant product upgrade in the company's ten-year history. The major new version delivers advanced SQL standard-compliant features such as stored procedures, triggers, views & new pluggable storage engines. Over 30 enterprise platform and tool vendors have also expressed enthusiastic support for the new release of the world's most popular open source database."

Yeah. Sounds like a dog to me.

What's the right tool? (Closed and/or open source)

For storing assets (files, basically) we use a cluster of webservers running apache. HTTP is efficent, stable, well-understood, and easy to cache. Because the filesystem is many TB and the performance demands are high, these webservers use a commercial clustered filesystem which makes all data available from all nodes, even if one of them is down. (Although it is not bug-free, which led to the asset performance problems we had a while back) This gives us an order of magnitude more throughput than putting assets in mysql directly.

It's not clear that we will always use this method - ultimately we hope to move primary asset storage onto the sims themselves, but that would involve a high-risk custom distribution mechanism.

Thanks for the info on how it works. That's a cool solution.