mysql vs postgre vs oracle

[alexluana_]

witch one offers the best performance when in comes to a server database?

witch one offers the best performance when it comes to a web-server database?

[alexluana_]

well I guess in the end mysql is better.. why?

cause as void513 mentioned:

- fastest when it comes to default install

- larg community and a lot of experiences means a lto of tests that conclude in further better and faster support

- easy to improve as you allready mentioned the "drive"trick and not only

- better stability

- better performance

[Locario]

Peharps this will be interesting for you:

http://www.day32.com/MySQL/

[void513]

something else to keep in mind is there are many 3rd part software that works with mysql for create db,entring data and mantaing the database.

that make easier to work with.not sure what out ther for ms sql or postgre.

[alexluana_]

ty very much void513 locario and unkle...

if you guys can give me a light or some sites to read from about my other thread, where I am asking which linux distro is the best choice when it comes to wow server, I would be very gratefull...

Since last week and still searching today and continuing until I will find a decent answer... I can't find evidence or good objectiv points of view when it comes to linux distros.... I want to run the best or the one that can be tweeked to be the best performance for a wow server, and of course... I am not talking about prepayd license like in RHEL case, but the opensource versions...

I do cline for CentOS, but a lot of ppl prefer Debian x64. Of course their explination is subject, and I believe mine is 2. I am thinking to CentOS like I thing to RHEL copy... fact that gets me to the result that if RHEL is the best outthere, than CentOS has all the cookies inside and other distros not really ALL of them but maybe a part of not at all...

Please give me a light in this matter!

[Unkle Nuke]

If you really like CentOS that much, you already have your answer.

Keep in mind those Redhat features you like so much are just as likely to be found in open source alternatives. For security and network performance, all the Linuxes are equal, more or less. It all boils down to how well you configure everything.

[Unkle Nuke]

There is no simple answer to your questions. A lot of factors will affect your choice. The best I can do is offer you some insights, information, and advice so you may educate yourself, then make an informed decision. Forgive me if this post is long, but there's a lot to cover in giving you a place to start. Bear with me and I promise it will be worth the trouble.

I have no experience with Oracle's DB. But I have used a few other relational databases. Hopefully, someone familiar with Oracle will post their observations.

PostgreSQL is regarded as outdated, but it is still a very excellent DB for small deployments or tasks where large scalability isn't a priority. It's also the most rock-solid of the free/open DBs, having had decades of development. Being the first standard of SQL databases, it was also my first exposure when I tried to learn SQL many years ago. It lacked some syntax found in MySQL, but it may be those limitations have changed since I switched over, years ago, to MySQL.

MySQL offers the best features for a highly scalable, multi-purpose DB, making it a great choice for supporting many different operating schemas, such as a game server and web server. I believe this is why MaNGOS chose MySQL. I also chose it for that reason and for my own preferences. I liked MySQL's expanded syntax, better support of other database engines, and being easier to work with for a newbie. If you want to experiment a little, try using the Falcon database engine plug-in, turbo-charging MySQL with what many regard as the fastest, most efficient engine there is for MySQL.

Microsoft's SQL server is my least favorite choice. Being proprietary, MS inevitably saddles you with its own idea of standards, meaning how you work with their DB won't easily translate to other SQL databases. You're also stuck with using Windows as your DB platform. Compared to MySQL and PostgreSQL, I find MS SQL Server to be clunky and counter-intuitive. Some like it, but I can't offer any reasons why you would consider using this one, aside from its tight intergration with Microsoft's dev tools and Windows.

There is a new generation of transactional databases, such as Hadoop, NoSQL, MongoDB, and others. I haven't tried any of them, but NoSQL and its forks look quite interesting. Unlike the older relational DBs, these new ones are geared towards handling massive data sets with high loads. Such DBs are used as the backends of deployments for cloud storage, social media sites, and big search engines. NoSQL has been frequently hyped as the "MySQL-killer", favored much by web developers for its relatively low overhead while handling large amounts of data transactions. I'm not sure how well these types of DBs would work in a small hobbyist environment. I haven't done any close research on specific projects or their forks.

If you want to set up a simple game server/website platform on common PC hardware, then MySQL is likely your best choice, with PostgreSQL being a close second.

For full server hardware or a large-scale deployment, MySQL is still a good choice. However, you may want to research some of the NoSQL forks. Some are intended to be a replacement for MySQL, since it has fallen into the clutches of "evil" corporate hands. :rolleyes:

Learning your way around a database is important. Newer DBs may not have as large of a support structure or well-finished documentation as the older, more established DBs. Both MySQL and PostgreSQL have a huge support community and extensive documentation, plus many utilities and add-ons. NoSQL, and its many forks, may have good support, due to its rapid growth and high popularity. Since MySQL is the database of choice for MaNGOS, you will be able to rely on this community better for support, if your setup will include a MaNGOS server.

In the end, the database that will work best for you depends on the hardware platform, network bandwidth, type of servers you plan to run, number of users, and data schemas. How well you understand configuring, tuning, and optimizing your database and servers, based on hardware and use, will make much more difference in performance than which DB software you choose. Even the best DB will lag and gobble system resources if you set up everything improperly. Your own skills, need for support, and personal preferences will also play a part in your decision.

Do your homework, researching each of the databases, but trust the opinions of other users more than any hype you read from the DB software publishers. Your efforts will be rewarded with having the tool that you're most comfortable using and best suits your needs.

[alexluana_]

Ty very much for this really detailed overlook...

My plan was to make a server that support somewhere to 2-4k players using as base:

- CentOs (or Debianx64 - dunno witch yet) ... But I heard that centos is a clone of RHEL witch is the best (at least that is what everyone say)... but I got to the conclusion that any distro can be the best if you configure it right.

- MySQL or PostgreeSQL - this is why I am asking...Havin in mind that postgreesql is also a clone of oracle... one of the dbs used by big companies...

- XW6600 server with 16 Gb in the beggining with the posibility to upgrade it to 32GB...

- and of course MaNGOS Wotlk repository (because that is what I like...wotlk)

Any suggestions?

[void513]

I agree with you UnkleNuke ms sql is more of a headach and is alot more dif to learn and setup.mysql prob would be easiest and most versatile.

[Locario]

MySQL or PostgreeSQL

I have heard in pass that PostgreSQL works better with heavy loads than MySQL, PostgreSQL "eats" minor resources.

[Unkle Nuke]

Absolutely! :lol:

[alexluana_]

well dunno about this locario... I wonder this 2... witch one eats more resources?

but as unklenuke said...mysql is supported by mangos team, so, it was made a lot of improvements in code to work very very well with mysql..

I wonder if those changes are applied for postgree as well?!

[Locario]

well dunno about this locario... I wonder this 2... witch one eats more resources?

but as unklenuke said...mysql is supported by mangos team, so, it was made a lot of improvements in code to work very very well with mysql..

I wonder if those changes are applied for postgree as well?!

MySQL, in the past some ppl in *nix world tell me mysql eats more resources than Postgree. I don´t know the improvements from mangos to mysql, but i'm sure the mangos team have done the best to squeeze mysql at max.

if you have a server with lots of gb mysql not be problem

[void513]

Heres a few interesting web site on mysql vs postgree that might clear up a few things.

http://tech.gaeatimes.com/index.php/archive/postgresql-vs-mysql-comparative-review/

http://www.mysqlvspostgresql.com/

there are many more if u google it

[Unkle Nuke]

The trouble with most online articles comparing one against another database software is they rarely bother with tuning them for optimal performance. When they do take that extra step, the tests are rarely comparable to everyday workloads. Another problem is finding a reliable, scientific benchmark that is done with hardware comparable to your own.

For a MaNGOS server, you will likely find little difference in speed between MySQL and PostgreSQL, given the types of data used. There can be a huge number of transactions, but the actual data written or read from each table is small.

If you anticipate having a giant server, with a total player base numbering into the thousands and hundreds active at any given time, you can speed things up enormously by splitting the databases across several drives. Notice I say "drive", not partition. This reduces delays from excessive head seeking and minimizes thrashing from having too many read/write operations queued on one drive.

You may put 'realmd' on its own drive, if you have a large player base. I would do the same with the characters databases. The same goes for the auction databases, if you use AHBot or have a busy Auction House.

I wish we had transaction data from all our server admins, so we could post a graph showing the various parts of the overall database and an average of how many read/write cycles each part has over an hour of peak server activity.

For a multi-processor server, say one that has two dual-core Xeon CPUs, you will certainly want one of those Xeons dedicated to your database, if you are using the same machine to also run the servers.