Archived

This topic is now archived and is closed to further replies.

Oracle vs MySql vs MSSql

This topic is 5561 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

for a medium size MMO game, say 150,000 membership and at peak 3000 active player concurrently in the game. which of the DBMS:Oracle, MySQL and MSSQL should be the best choice for this system? please give me some comment on both performance and cost efficient.

Share this post


Link to post
Share on other sites
What server are you going to be running this on? Linux? NT? If it was my choice I''d have to say run MySQL on a Linux server (possible Redhat, because it comes with the distro). The best part, is they are free... and we all like free don''t we. I''m not to sure how they stand up to each other in performance, I''d have to assume Oracle is the fastest (but it will probably cost you in the $$ department).

Share this post


Link to post
Share on other sites
Based on price alone, use Linux and MySQL. Based on stability and support, I''d say Linux (or other UNIX) with Oracle (if you can afford it).
I''d say that in this case, MS is a good middle road, being less expensive than Oracle and a high end UNIX box. In the past, performance leaned heavily in favor of Oracle, although I got out of the DB business a couple of years ago. I doubt that the DB performance would hurt you too bad anyways (you''ll need to be caching a lot of data anyways).

The people at MySQL a couple of years ago posted some benchmarks saying they were almost as fast as Oracle in some types of operations. I haven''t seen anything similar in a while, so I don''t know for sure how well MySQL stands up performance-wise.

It really depends on what kind of servers you are already working on. If you''re using Linux, stick with something that is easily run on linux (MySQL). If you''re using Windows go for a copy of MSSQL Server. I would assume with 150k subs that you are probably funded well enough to afford any of the above options.

Share this post


Link to post
Share on other sites
IBM has a new version of DB2 called DB2 EEE that is supposed to be reasonable priced. It also works on Linux and they claim it scales well across commodity hardware machines.

I use MSSQL, Oracle, DB2, and Omnidex at work and they all have their pros and cons.

I suggest you try them all out on an eval basis and make your determination from there. The bottom line is there is not a single DB that covers every solution.


LostLogic
www.lostlogic.com
Author, Multiplayer Game Programming
Author, Strategy Game Programming with Direct X 9 (Not yet released)

Share this post


Link to post
Share on other sites
thx i got lots of useful information.
it sounds that Linux+MySQL is good for a starting up company right?

is there any websites that do benchmarking on DBMS? you know the company that made the DBMS always claims they r the best!

Share this post


Link to post
Share on other sites

We use MSDE - although it has a 2 gig limit for the free license - we just spread across multiple single databases if we go over 2gigs.

Personally, use MySQL - because then you can run ok with Linux and \or m$.


Share this post


Link to post
Share on other sites
It depends if you want a transactional database or not. I probably wouldn''t use InnoDB in a high-volume server just yet (though I hear Slashdot uses it to great effect...) and if transactions is what you need, go for Oracle. You can download the full version of Oracle 9i Enterprise Edition for free from the Oracle site, so you can develop on the free version, and only have to pay anything when your site goes live (I''d assume by that time you''d be pretty well funded, even if you''re not at the moment). Mind you, it''s a 1.5GB download, so be prepared

If you don''t need transaction, and I can''t really say whether that''d be the case or not - it depends on how you write the game. Then MySQL might be a viable option. However, in my opinion, MySQL doesn''t have a few of the features that I''d like if I were writing a high-volume game server. Features like stored procedures, bound parameters, etc.

If I had my way, I''d have all of you shot!


codeka.com - Just click it.

Share this post


Link to post
Share on other sites
Maybe you should try out MimerSQL? I''ve been using it a lot at work recently and IMHO it''s a fast and unusually full-featured DBMS for that price. Haven''t found any benchmarks though. Runs on Win32, Linux, PocketPC and a few more.

Share this post


Link to post
Share on other sites
It has been a while since I have been checking out the DB situation but MySQL used to lack many nice features, such as nested queries and transactions, even though it was also known to be fast. There is another open source database called PostgreSQL that has many of the features that MySQL lack, but is also said to be a bit slower. I really do not know if any of those statements are true anymore.

Mythic seems to feel that MySQL works well for their game Dark Age of Camelot, which pretty much fits the game description in the original post.

Share this post


Link to post
Share on other sites
We''re using MySQL for our project. Sure, there are a few features that won''t be implemented by the time we go live (transactions in particular), but honestly we should never want to use those features. I can''t imagine anything that would upset the users more than running around playing and getting stuff and then all of a sudden the servers go down and you lose everything because "We had to roll back the game to a previous state".

Anyway, by the time you''d need the other features (nested subqueries and such), they should all be implemented, since 4.1 will have almost everything.

Share this post


Link to post
Share on other sites
quote:
I can''t imagine anything that would upset the users more than running around playing and getting stuff and then all of a sudden the servers go down and you lose everything because "We had to roll back the game to a previous state".

Didn''t Asheron''s Call rollback their game by two days or something when a bug crashed the economy of the game? And yes, such a bug can upset the users more than the rollback. I know people who stopped playing Anarchy Online because Funcom had let money duplication exploits crash the economy in ways that pushed up prices of unusual items to levels where those items became totally unattainable by honest players (high level players who could not afford unusual low-level "spells" etc.).

Share this post


Link to post
Share on other sites
quote:
is there any websites that do benchmarking on DBMS? you know the company that made the DBMS always claims they r the best!


You should use a lot of caution when searching for benchmark results of various DBMS-apps as many vendors (including Microsoft and Oracle) require in their EULAs that people get permission from the vendor before publishing benchmark results.

Share this post


Link to post
Share on other sites
quote:
Original post by solinear
We''re using MySQL for our project. Sure, there are a few features that won''t be implemented by the time we go live (transactions in particular)


Transactions are implemented right now in the InnoDB table type, InnoDB also supports row-level locking and lots of other cool features usually associated with Oracle (store procedures and sub-queries being the main exceptions). However, I''m not 100% convinced that InnoDB is mature enough to use in a high-volumne, high-uptime production situation like a game. I also don''t think you''ll have a hard time convincing your investors of the same, either.

Transactions are also supported in BerkeleyDB, but that doesn''t seem as popular as InnoDB.

If I had my way, I''d have all of you shot!


codeka.com - Just click it.

Share this post


Link to post
Share on other sites
I would like to clarify something between MS-SQL and Oracle.

Think about the type of data that a typical game uses; almost completely floats, ints, and small strings. Now think about how both MS-SQL and Oracle store and manage these things.

MS-SQL stores both floats and ints in their native format both in memory and on the hard drive. Stings are stored in either 256 byte blocks or 4096 bye blocks, depending on how much space you need.

Oracle on the other hand stores both floats and ints in a proprietary 21 byte format. While 21 byte precision is needed for banking and other financial applications, it is a waste of both space and processing power for our games. Strings are stored in a block that can be as large as 4MB. While this makes the DB very flexible, it is a waste when most of our strings are less than 256 bytes. The overhead to handle the larger blocks is wasted again.

Although Oracle has features that make it a great business DB, those features are useless and hinder when it comes to MMP games. MS-SQL, MySQL, and PostgreseSQL will beat it every time.

When selecting a DB, you also have to consider the solutions for backing it up. Our games require snapshots of the database, otherwise someone could find a dup bug and exploit it. Not many DBs allow snapshots or even online backups. Something to think about.

And I am sad to learn of the limitations of MySQL. Stored procedures are a must and transactions help reliability enough to be worth it. I wouldn''t use a DB that didn''t have both.

Stephen Manchester
Senior Technical Lead
Virtual Media Vision, Inc.
stephen@virtualmediavision.com
(310) 930-7349

Share this post


Link to post
Share on other sites
quote:
Original post by smanches
Oracle on the other hand stores both floats and ints in a proprietary 21 byte format.



I don''t know how they store all their data, but I would bet that it''s not always in this 21 byte format. The SQL standard allows you to specify the precision of all numerical datatypes so you can say that an integer has at most 10 digits or whatever you like. If they''re storing a 1 bit integer as a 21-byte internal deal, I''d be very surpized.

quote:

Strings are stored in a block that can be as large as 4MB.



Not VARCHAR type strings. They''re stored the same way as SQL Server does. You''re thinking of BLOBs or CLOBs. These are meant for storing large amounts of text in the database and work very differently to VARCHAR fields.

Besides all that, Oracle is still faster than SQL Server, especially in a high-end or clustered server. In our own tests at work, once you get over four CPUs, SQL Server pretty much just drops off completely, and even then you''ve got to make sure you configure it all properly.


If I had my way, I''d have all of you shot!


codeka.com - Just click it.

Share this post


Link to post
Share on other sites
According to the SDK, there is only the 21 byte number type. I''ll have to look again for the varchar thing. Didn''t se it the first time.

Your missing my point about why not to use Oracle. I''m not saying Oracle doesn''t rock in a large enterprise business need. It is just way to much of a DB for our games. You would never have a quad or bigger machine running the DB for a game. A dual processor machine is all you need for a game backend.

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
I believe that I read an article about Dark Ages of Camelot''s development and how they used Linux+MySQL because of the fact that they had to buy their servers (nobody would give them a loan).

Linux+MySQL sounds like the best choice to me.

Share this post


Link to post
Share on other sites
Best perfomance realy in MySQL and Unix if data not complex, and frequently updateable tables have less then 50M records. (if data more, you need thing about good hardware, DB lake Oracle or write your own specific DB for your task).

For get more perfomance of MySQL you need know: insert/update is hard for CPU, but if server has two or more CPU use multithread (or multiprocess) and call all insert/update (and delete too) in another threads. Thats not block you main loop of server.


In my project I use own "cashe" for changed data and dump "cache" preriodicaly to MySQL.

About another DB - MySQL is fastest that I used (MS SQL, Postgrese,DB2).

Share this post


Link to post
Share on other sites
Of course MySQL is fast, it doesn''t support transactions. (At least not in the MyISAM table type). Compare speed with Oracle/MS SQL Server vs. MySQL + InnoDB table types, that''s a bit more relevant.

Plus about Oracle vs. SQL Server in games, sure you don''t usually run a large database server (and SQL Server has reasonable clustering anyway). But the other thing is, especially if you''re an indie setup, you want a prototype to show to investors. You can''t get SQL Server for free, you''d have to get a universal subscript to MSDN which isn''t exactly cheap. However, you can download Oracle 9i Enterprise Edition for free from the Oracle website. It''s about 1.5GB, but that''s better than the couple thousand dollars for an MSDN subscription for SQL Server.


If I had my way, I''d have all of you shot!


codeka.com - Just click it.

Share this post


Link to post
Share on other sites
If you link to the MySQL server, this includes using it over the network, then your whole project becomes GPL under the terms of the license. This is explained in the online documentation. You can buy a non-GPL commercial license because MySQL is duel licensed.

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
quote:
Original post by fnagaton
If you link to the MySQL server, this includes using it over the network, then your whole project becomes GPL under the terms of the license. This is explained in the online documentation. You can buy a non-GPL commercial license because MySQL is duel licensed.



You only have to release your code under the GPL if you distribute your program. Since you probably won''t distribute the server side of your game, you don''t need to release the source either.

Of course, releasing the source under the GPL would be a nice thing anyway, but it''s not required.

Hope this helps.

Share this post


Link to post
Share on other sites
quote:
Original post by smanches
I would like to clarify something between MS-SQL and Oracle.

MS-SQL stores both floats and ints in their native format both in memory and on the hard drive. Stings are stored in either 256 byte blocks or 4096 bye blocks, depending on how much space you need.



I thought SQLServer has a default 2K blocksize and recommends it although it''s not the most efficient ?

quote:

Oracle on the other hand stores both floats and ints in a proprietary 21 byte format. While 21 byte precision is needed for banking and other financial applications, it is a waste of both space and processing power for our games.



Storage, yes if you run on a 4 GB disk. Why bother discussing 21 byte numbers when you could attack Oracle''s need for a 100 MB SYSTEM, 50 MB RBS, 50 MB TEMP and 3x4 MB redo log files ? Even when a 20 MB database you''re talking about 250 MB minimum storage.

quote:

Strings are stored in a block that can be as large as 4MB. While this makes the DB very flexible, it is a waste when most of our strings are less than 256 bytes. The overhead to handle the larger blocks is wasted again.



This is totally untrue. A string is a varchar2, limited at 2000 in Oracle 7 and 4000 in Oracle 8 but it takes up the space that is actually used. So a table with rows that have 10 varchar2(2000) fields which are only used for 10-byte strings will still fill 40 rows inside a single 4K data block.

quote:

Although Oracle has features that make it a great business DB, those features are useless and hinder when it comes to MMP games. MS-SQL, MySQL, and PostgreseSQL will beat it every time.



In single user speed yes, but how about multiuser functionality ? Oracle has row locks, other databases just lock the whole block. If you have an 8K blocksize and 10 rows in each block, the others will lock the entire block and cause processes that need a row that is not being updated to lock, while Oracle provides the before-committing data through the rollback tablespaces, never blocking a process unless it needs the exact same row.

quote:

When selecting a DB, you also have to consider the solutions for backing it up. Our games require snapshots of the database, otherwise someone could find a dup bug and exploit it. Not many DBs allow snapshots or even online backups. Something to think about.



Actually this is where Oracle excels, online and offline backups, with an option to do a full database export while the database is online, for restoring single tables easily.

Share this post


Link to post
Share on other sites
quote:
Original post by fnagaton
If you link to the MySQL server, this includes using it over the network, then your whole project becomes GPL under the terms of the license. This is explained in the online documentation. You can buy a non-GPL commercial license because MySQL is duel licensed.



This would disqualify MySQL for anything serious, except hobbyists. Are you sure ? How about websites ? Do they have to disclose all of their PHP source code when they use MySQL ?

SQL databases are an established field. It''s nostalgic to read about Oracle 1,2,3 etc, in the PDP11 or VAX era when they ran in 64 kbyte for processes and 64 kbyte memory for data, and how they developed with read consistency etc. Oracle 5 and 6 were professional in the late 80''s and Oracle 7 in 1993 with added PL/SQL is like the Oracle we know now. It''s a shame to see products like MySQL now at a level of where Oracle was in 1985 (no transactions, no read consistency, no row locking), even though so many people work on it.

Share this post


Link to post
Share on other sites