Comparing MySQL Performance

An anonymous reader writes "With the introduction of the 2.6 Linux kernel, FreeBSD 5-STABLE, Solaris 10, and now NetBSD 2.0, you might be wondering which of them offers superior database performance. These two articles will show you how to benchmark operating system performance using MySQL on these operating systems so you can find out for yourself if you're missing out. While this may not necessarily be indicative of overall system performance or overall database application performance, it will tell you specifically how well MySQL performs on your platform."

Comments

postgres

[fludlight]

what about postgresql?

Re:postgres

[TheWingThing]

If you R the FA, which not many do these days, he says he didnt have the time though he planned to do both, so he did MySQL. This is mentioned in the first link, which was /.ed last week. The first link was Part 1 which explains the setup and procedure. Part 2 (second link) explains the results.

What no Windows benchmarks?

[sjrstory]

I thought Windows XP was supposed to be the Fastest and the Most Reliable OS in the World

...never believe everything you read on the Intarweb. ;)

Re:What no Windows benchmarks?

[dioscaido]

All kidding aside, it would have been good to see these test also running on XP and OSX.

Re:What no Windows benchmarks?

[sbryant]

Certainly, XP (also 2K, 2K3) would have been an interesting comparison. OSX, however, is out of the question - all of the others ran on exactly the same hardware, a dual 1GHz PIII system. The point of the test was to see operating differences, without the hardware being a factor.

Of course, it would be possible to run the tests on the same Mac under OSX, Linux and any other OS available for that platform. That might have been interesting, but you wouldn't be able to compare the results directly to those from the PIII system.

-- Steve

Re:What no Windows benchmarks?

[rbanffy]

Maybe one should do it. It is sure useful to evaluate a database platform on many hardware confugurations in the same price/capabilities range. Maybe buying a XServe running OSX from Apple makes sense for doing MySQL more than, say, an IBM xSeries or an Itanic offering from HP running HP-UX.

Of course, such a test would involve some serious resources not everyone has available.

That's all well and good, but...

[Sheetrock]

MySQL is a different animal from PostgreSQL, which is itself a horse of a different color than DBI. To truly profile these operating systems you must take into account the differences in:

• Tuple calculus
• Transaction journaling
• Operator space/system call overhead
• Disk cache timings

And much more... in essence, you can't be certain these benchmarks hold true for the performance of all databases and it may even be a mute argument -- the same operating system may be tweaked differently if you're fileserving or mailserving or networkserving or if you're only dataserving. A useful tool, but one that must be run on each server.

Re:That's all well and good, but...

[_generica]

and it may even be a mute argument ...

or even more likely, a moot argument

Re:That's all well and good, but...

[iamsure]

Quoting Joey Tribbiani: "Its a moo point. It's like a cows opinion, its just moo."

Re:That's all well and good, but...

[dubl-u]

and it may even be a mute argument ...

or even more likely, a moot argument

So you think. It's all the rage now to have technial meetings where nobody speaks and all arguments are made through pantomime. I'm sure that's what he's talking about.

My favorite thing to explain that way is an elevator algorithm, although stochastic fair queueing is a close second.

Re:That's all well and good, but...

[JabberWokky]

Pretty much exactly what he said in the second article. Of course, he presented this specifically as a MySQL benchmark rather than a general OS comparison, so you've just killed a straw man.

--
Evan

Re:That's all well and good, but...

[10Ghz]

If this was a database-benchmark, he would have compared different databses running on same system. But this wasn't a database-benchmark. He benchmarked different OS'es running MySQL. No-one is trying to spin this in to a databse-benchmark, since this is not a database-benchmark. Hell, everyhting I have seen indicates that these articles are about the performance of the OS, and not performance of the database!

Re:That's all well and good, but...

[PieEye]

Man, if I had a nickel for every time I saw a mute stochastic fair queueing argument at Starbucks...I'd...uhhh...never mind.

Re:That's all well and good, but...

[LDoggg_]

Man, if I had a nickel for every time I saw a mute stochastic fair queueing argument at Starbucks...I'd...

Still not be able to afford a cup of their coffee?

mmm - performance

[zoloto]

There was another test that included PG-SQL, but I can't find the link now. Basically stating that posgresql burned the rest of them out of the water on a mid ranged server

if anyone finds the study/test, post a link?

Tuning on FreeBSD

[zulux]

On a PostgreSQL install, I almost quadrulpeled performance on FreeBSD 4.10 by bumping up the SHMMAX in FreeBSD, then tweaking PostgreSQL to use it for queries and indexes.

Make sure FreeBSD has DMA turned on as well, and make CFLAGS somthing other than a 486.

All of the *BSD are *VERY VERY* conservative and will do a lot better when properly configured.

Re:Tuning on FreeBSD

[a11]

Valid point. RedHat Linux is tuned for Oracle - including a larger shared memory region. A more valid comparison would be if each OS was first tuned for the database.
Something like shared memory, which is used for sorting, caching, and hashes, would slow a database down quite a bit if there was not enough.
I work with multi-terabyte databases daily, and by my observation, the flavor of UNIX is irrelevant if the IPC resources are adequate. When you're scanning a gig from disk, all DMA, an extra second in the kernel doesn't count in the O().

Re:Tuning on FreeBSD

[molnarcs]

It is almost a pity that ULE came back online just recently. I would be very much interested in a test with the ULE scheduler as well. See this post.I've been stress-testing it in the past few days (capturing tv progs with rtrpio 0 on mplayer into divx5 624x468 all filters - hqdn3d,hb/vb/dr/lb/ etc. - on in 4800 bitrate - on my athlon xp 2400+ and a very crappy capture card) - and so far, no problems.

Nevertheless, this is a very good benchmark conducted in a fair manner. I was pretty much surprised at how the guy lacking support (from Solaris no less) went on to find out by himself how to increase performance. This also underlies the point made by many in the "netbsd vs. free" benchmarK about the focus of FreeBSD being SMP in the past few years ... which has payed off nicely it seems.

Re:Tuning on FreeBSD

[Dom2]

Bear in mind that there are limits to the effectiveness of this technique. Spend a while with this tuning document for best results.

-Dom

If TFA gets slashdotted, these are his conclusions

[TheWingThing]

Conclusion/final thoughts

Both Linux 2.4 and 2.6 had the strongest showing overall for these tests, dominating just about every benchmark no matter the workload. Scalability for both kernels was also excellent with addition of an extra processor. In fact, I was surprised how well 2.4 had done, as I had somewhat expected 2.6 to show at least a noticeable, if slight, increase over 2.4. Instead, they took turns besting each other from test to test -- and in scalability -- for a fairly even overall showing.

Solaris 10 had a very strong showing as well, having great speed as well as great scalability. I think the results show that Solaris 10 is a great platform for MySQL. Of course, I didn't have Super Smack results as I couldn't get Super Smack to port to Solaris (as detailed in the previous article), so bear that in mind.

NetBSD 2.0 also had a very strong showing, although it was tarnished by two issues. One, MySQL on NetBSD 2.0 doesn't scale with the addition of CPUs. The results would seem to indicate that it might be wise to run a uniprocessor kernel even if two processors are available. The other issue was the poor I/O performance for the 10M row SysBench test. The SMP scalability issue is easy to understand since, to be fair, this is the first NetBSD release to support multiple processors. The I/O issue is more of a mystery, however.

FreeBSD 5.3 did relatively well in both KSE and linuxthreads mode, although with all the work that's been done in the SMP and threading realms, I was a little disappointed with the results. Still, it seems that the native threading model for the production release of FreeBSD-5 is ready for prime time, and can replace the long-standing FreeBSD convention of using linuxthreads with MySQL.

For FreeBSD 4.11, however, linuxthreads definitely helped with performance (and in many cases outperformed FreeBSD 5.3). With libc_r, performance lagged far behind linuxthreads for many tests, and there was little scalability. I would say it's highly advisable to build your FreeBSD 4.11 MySQL binary with linuxthreads.

For all the time it took, I think the tests were worth it. I learned quite a bit about MySQL performance in general, and I'd like to again thank Peter Zaitsev for his methodology recommendations and input, as well as Jenny Chen from Sun for her input.

Why do people use MySQL over Postgres?

[Vellmont]

Slightly off topic, but if it's really performance you want, why don't people just use Postgres? It's had a much better feature set for years, and is starting to get enterprise level features. It seems like MySQL is somehow the default choice for open source projects, but as far as I can tell it offers no advantages and many disadvantages over postgres.
Is it just MySQL is slightly easier to setup?

Re:Why do people use MySQL over Postgres?

[james_orr]

MySQL got the head start. Postgres has had a better feature set for a long time now, but when I first looked at it you couldn't do left joins and all triggers had to be written in C. At the same time MySQL could ... well .. do what MySQL does now.

So MySQL became the popular open-source database.

Re:Why do people use MySQL over Postgres?

[Unordained]

I was at Barnes & Noble tonight, waiting to go pick someone up at the airport. Looking through the tech-book section, apart from noticing a lack of theory books, I noticed that MySQL has done well for itself in the publishing world. SQL books most often refer to MySQL for examples, PHP books (and other "learn this language so you can incorrectly apply what you halfway learn to any problem you encounter" books) generally include a large section devoted to database stuff, seemingly always involving MySQL ... There were more MySQL books than Oracle books, more Oracle books than MS-SQL books, more of those than of PostgreSQL books, and no Firebird, MaxDB, Ingres, DB2, Cloudscape, HSQL, etc. books that I recall.

If most all programming were taught in Basic, don't you think people would wind up coding apps in Basic, regardless of its actual worthiness? (Today, it's Java, not Basic. But now I've called upon myself the wrath of the Java coders.)

I personally use Firebird at work and at home. Installation is a snap, particularly from binaries. Unpack, run installer, go. Works under *nix and windows, and has an embedded version appropriate for standalone apps. Its creator is the father of generational record-keeping (which some RDBMSs like MS-SQL have only recently gotten around to), and it's like an "easier to use under windows" cousin to PostgreSQL, feature-wise.

Other than good marketing (including a lot of word-of-mouth rumors) in favor of MySQL, I can't explain its success. When you have to help someone implement a query as a correlated sub-query because their RDBMS of choice (MySQL) doesn't do certain joins properly, or doesn't do them quickly (yes, even with indexing -- the optimizer is just stupid), ... you start to wonder why they don't use an RDBMS where they can express exactly what they mean, directly, and have it be fast. And MySQL's popularity has lead to a lot of people learning some very bad habits (particularly about doing a lot of client-side work, like joins or aggregates, or using sub-queries where joins should work) ... but people love it! (I think this is related to the stockholm syndrome, in a way; I also see it with data-entry clerks who are convinced that their crappy, buggy, expensive, old, vendor-supplied software is better than anything anyone will ever build, ever. If you're an in-house programmer, you're not a real programmer, either. Masochists.)

Re:Why do people use MySQL over Postgres?

[harlows_monkeys]

Slightly off topic, but if it's really performance you want, why don't people just use Postgres?

When I measured MySQL and PostgreSQL on very simple databases, MySQL was faster (slightly faster on reading, waaaaaay faster on writing). Since most things people need a database for just requires simple databases, MySQL wins on performance in most applications.

Re:Why do people use MySQL over Postgres?

[Duncan3]

Um, actually Postgres, tho named differently at the time, predates MySQL by a good decade.

Re:Why do people use MySQL over Postgres?

[SerialHistorian]

I've written a bunch of enterprise-class stuff on MySQL.

The first and second answers are inertia. All of my tools work with MySQL and I'd have to spend a week or two re-writing them for PostgresSQL, and I can't shake loose that kind of time right now.

Also, I have a set of redundant, mirrored MySQL servers in my colo box that run all of the websites I've built, and I'd have to get more rack space or convert everything over to Postgres at the same time. Neither of which are cost effective when what I have ... works.

The third answer is that MySQL is blazingly fast at doing simple things. Where Oracle (The other RDBMS that I'm familiar with) can return simple select queries or complex insert or joined select queries in .5 to 1.0 seconds each, MySQL can return simple queries in .01 seconds and stupidly complex queries in 5-10 seconds. Since 100% of what I'm doing is simple selects or can be hacked very quickly to seem like simple selects, there's no reason to use anything more powerful for what I'm doing.

I don't need to have "good habits" ... I don't need to have nth degree optimized queries. I don't use 99.99% of the features that MySQL has, not to mention all the features that Postgres has that I wouldn't use. (And don't get me started on Oracle.) It's also faster for me, in both database query return and programmer time, to execute 5 simple, general, fast queries that are part of a code library (and when the database structure changes, edit that one code library) than it is for me to write one really complex query for each code module (and have to edit every module when the database structure changes).

What it comes down to is that it works well as a lightweight database for websites and web apps, and there's a ton of community support and literature. It's not Oracle. It never will be. It's not useful for everything. But when you need a lightweight database to handle a ton of simple select queries without melting down, .... MySQL fits the bill. Why swat a fly with a sledgehammer?

Re:Why do people use MySQL over Postgres?

[bani]

because mysql is easier for ISPs to manage. adding new users with very fine grained db control is very simple for ISPs. not so with postgresql -- it can be a real bear for ISPs to manage with 1000's of separate users.

and ISPs are the driving force behind a lot of what is widely deployed.

this is one of the same reasons PHP won out over everything else -- because it integrates easily and because it's easy to manage. not because it's "the best" designed language or the most powerful.

mysql is "good enough", quite frankly the majority of people out there using sql in deployment are using eg phpbb or postnuke or whatnot and would gain little to nothing from using postgresql.

Re:Why do people use MySQL over Postgres?

[Profane MuthaFucka]

Postgres supports unicode now.

Re:Why do people use MySQL over Postgres?

[ozzee]

> Postgres does not (this was the case 1 year back

What are you talking about ? Postgresql has supported unicode for at least 4 years that I know of, probably more. You will need to create the database to support unicode.

From the man page:
createdb - create a new PostgreSQL database
...
-E encoding
--encoding encoding
Specifies the character encoding scheme to be used in this database.

--encoding UNICODE will work only if the support is compiled in
- configure the database as
% configure --enable-multibyte=UNICODE

Re:Why do people use MySQL over Postgres?

[Unknown Relic]

And MySQL's popularity has lead to a lot of people learning some very bad habits (particularly about doing a lot of client-side work, like joins or aggregates, or using sub-queries where joins should work)

I'm sorry, but how does MySQL's popularity lead to people using sub-queries in place of joins? It's only with MySQL 4.1 which was only recently released that decent sub-query support even existed in MySQL. As for the comment about indexes, it's been possible to specify exactly which indexes a query should use for ages. Now, I'm the first to admit that MySQL is far from perfect, but we don't have to go making up additional flaws to knock it with.

That being said, I do think you're right about MySQL's marketing efforts being a major part of its success. I also think you hit the nail on the head with regards to the ease of installation, or actually, ease of maintenance. While it's not an issue any more, Postgres used to be burdened with table locking issues from vacuum, essentially forcing the database offline while it performed its daily maintenance. MySQL never had this problem and ran well on cheap hardware, and so was at the time a natural choice over Postgres for use on the web. Since then it's continued to be good enough for most people (and is getting MUCH better with 4.1 and the upcoming 5.0 release) and as such there's been little inclination to go through the hassles of migrating to another RDBMS. As long as it continues to be good enough, people will keep using it.

Re:Why do people use MySQL over Postgres?

[Phidoux]

Because MySQL is so much easier to say than PostgreSQL... Sounds like a speech impediment.

Re:Why do people use MySQL over Postgres?

[200_success]

It's even simpler in PostgreSQL. In the psql client shell:

psql=# CREATE USER bar PASSWORD 'password';
psql=# CREATE DATABASE foo OWNER=bar;

Or, use the equivalent on the Unix shell:

# createuser --pwprompt bar
# createdb --owner=bar foo

All of the expected privileges are automatically granted on the foo database to user bar, since bar is its owner!

Re:Why do people use MySQL over Postgres?

[Jamesday]

Wikipedia uses MySQL's InnoDB storage engine, with row level locking and lots of concurrent inserts and selects. It uses the standard MySQL replication and full text search as well. One database server took the site to the top 1500 or so worldwide. Now, with more than half of the load for search, 5 handle the online load for around 200 million queries and about 1.2 million inserts or updates per day for what Alexa currently ranks as 81st most popular English language site. Before some recent code improvements increased slave server use we had one server doing sustained rates in excess of 3,000 queries per second.

We'll soon have 40 web servers hitting those database boxes, so we'll take all the speed we can get.:)

Simply, MySQL gets the job done and has proved sufficient scalability and reliability in practice. PostgreSQL is also clearly an excellent database server. Each has pros and cons. Pick whichever fits the job at hand.

Memcached, Squid and PHP are also key parts of the scalability picture for Wikipedia.

Re:Why do people use MySQL over Postgres?

[aixou]

Why swat a fly with a sledgehammer?

As an exercise of motor skills.

Re:Why do people use MySQL over Postgres?

[higginsm2000]

I've written a bunch of enterprise-class stuff on MySQL.

How do you define enterprise class? I certainly wouldn't define a simple website (however large) as an enterprise class application and that seems to be what you are talking about.

Re:Why do people use MySQL over Postgres?

[chez69]

conidering that slashdot often gives 503 errors, I wouldn't consider it even close to a real high availabily application

slashdot enterprise quality? my shit is more enterprise quality

Re:Why do people use MySQL over Postgres?

[Ragica]

One thing that i don't usually see factored in to the PostgreSQL vs MySQL debate is security.

Writing as an admin who generally keeps his systems up to date, and is forced to maintain MySQL on several boxes, I can tell you if you pay attention to security advisories MySQL is a pain in the ass. So much so that I let the security updates lag a bit (maybe more than a bit). I finally got around to updating the MySQL servers a few weeks ago when no less than six security advisories had been outstanding. Granted, some of these were minor issues, but still... they warrented security advisories.

In the same period, how many security advisories were posted against PostgreSQL? Zero.

(Though to be fair, one security update was released on PostgreSQL just after I did these upgrades.)

All I can say is there is a curious similarity between the name of this "My" SQL server and Microsoft Windows' well known naming convention for folders. I dare not think if there are any further comparisons between these two products could be drawn...

Re:Why do people use MySQL over Postgres?

[arkanes]

On the other hand, Wikipedia suffers from intermittent timeouts and editing errors. Perhaps the lesson here is not that MySQL is optimal for enterprise scalability, but that it can be leveraged into it, although there may be better options?

Useless Benchmarks

[Anonymous Coward]

From the article: I used the GENERIC configurations unmodified, expect for above-mentioned changes and adding SMP support.

FreeBSD's GENERIC kernel config is for i486. If he'd commented out two lines, he could've tested for i686, which is what a P3 is. As it is, these benchmarks aren't helpful at all, because the optimizations assume a machine inferior to what's actually being used. He failed to eliminate enough variables for these to be meaningful.

Re:Useless Benchmarks

[setagllib]

Entirely right, and some user-space optimization could have gotten the final few percent in too. He installed stock BSDs and recompiled their kernels straight, didn't tweak any options that weren't necessary to run the suite, and compared to a Linux optimized from the ground up (Gentoo + his knowledge of Linux itself). Real clever benchmark.

That NetBSD performed worse than FreeBSD for disk IO is really strange. I have never seen this happen in any of the machines I've tried both on (hint: a lot), so either he has a very exotic disk controller which isn't supported properly (weird) or there's a disturbance in the force. Members of the mailing lists are talking it over with him now, and a follow-up should arrive eventually.

I would have liked to see results of FreeBSD 5-STABLE too, because he compared a refined Linux and a solid NetBSD to a FreeBSD release that was deemed not-ready-for-benching-let-alone-production on day 0, which gave it little chance. It's interesting to see if the claims 5.4 will be much better hold water.

use windows!

[mboverload]

I use Windows Server 2003 for all my SQL needs. It is 20% faster than an equivilent Linux machine!

Well, at least thats what Microsoft told me...

Re:Did you even read the article?

[isometrick]

Click the second link in the summary, sir.

Different filesystems?

[dolphinling]

I might just be naive, but doesn't database performance depend a lot more on filesystem than OS?

Re:Different filesystems?

[bn557]

Depends on the amount of ram in the box, really. If you have 4 gigs of ram and a 1gig database, it can cache the whole DB, so reads can execute quickly, while writes do take a bit of time to update the physical files. Tuning the cache in mysql can greatly improve performance(and also bring a machine to it's knees, if my memory serves me correctly).

Re:Different filesystems?

[PedanticSpellingTrol]

He's obviously added another layer of depth to the joke by counting in base one.

same reason people use windows

[RelliK]

because everyone else does.

Mac OS X.

[freemacmini]

Too bad he could not test on Mac OS X.

Of course in order to do that he would have to install the OSes on a PPC machine and I don't think freebsd on PPC is ready for prime time yet.

How relevant are those benchmarks

[Donny Smith]

Does anyone go: "OK, I need the OS for my mySQL project. I'll benchmark BSD, Linux, Windows, and choose the fastest OS."

Difference among OS should be pretty much unimportant unless one's an ISP or big enterprise. I would choose the OS based on completely different criteria:
1) Existing skillset (advantage to existing skills)
2) Existing deployed OS (advantage to OS already deployed)
3) My company's OS strategy (advantage to the OS and the CPU platform we chose to standardize on)
4) Existing software (if I already have X vendor's backup agent for mySQL on Linux or database tuning tools, I wouldn't use BSD just to (potentially) gain an extra 5% in some ludicrous benchmark result).

Today's hardware (and operating systems) are so cheap that it's almost irrelevant what OS and hardware goes into many a project.
Look at the new HP's 25p and 35p blades (Opteron-based) - a 2 processor 1GB RAM version is just some $1,700 more expensive than a 1 processor 512MB RAM version.
It's easy to lose that $1,700 in downtime, spend it on a Windows engineer's new RHCE or such...

Re:How relevant are those benchmarks

[Bert64]

Recompiling the kernel to remove unnecessary features/drivers and enable support for modern processor features seems very intuitive to me. Removing as many unnecessary features as possible and runing it as closely as possible to the host system is a basic step when configuring a system, the difference is linux lets you take the tuning several steps furthur.

And your second coment depends on the organisation, a lot of strongly-microsoft shops work the way you describe, and only use unix for jobs where windows can't handle the task at hand (even microsoft run a lot of their backend stuff on solaris and as/400 systems for instance). While many other companies use unix for most backend tasks while leaving windows strictly for end user interaction.

Re:Other Benchmarking

[E_elven]

Does anyone know of a nice computational/science benchmark that runs on Linux/Windows/BSD and is free?

Try:

int main(int argc, char** argv)
{
for (long i = 0; true; ++i)
{
char* x = new char[i*i];
std::cout << "I love Stevie Hawking!" << std::endl;
}
}

You'll need a stopwatch.

Procedural problem with NetBSD multiprocessor

[Bushcat]

It seems like the performance of NetBSD will be re-evaluated, so expect the results to be recast in the next few days.

See the message thread titled "NetBSD performance" at http://software.newsforge.com/article.pl?sid=04/12 /27/1243207: an anonymous reader asks "Did you enable PTHREAD_CONCURRENCY? You have to set that variable to the number of CPUs in your system, else you won't be able to run more than one thread at a time, even you have more than one...". He replies "Sunofa. The $PTHREAD_CONCURRENCY environment variable wasn't set, as I had no idea it was an option. ... It could very well be the issue. In the next few days I'll re-run the NetBSD tests with that set."

Wikipedia

[MikeCapone]

Well, it's good enough for Wikipedia.org so it's good enough for me.

MySQL vs PostgreSQL

[Pan T. Hose]

what about postgresql?

That is a very good question, I don't know why has it been moderated as off-topic. Naturally it is useless to compare MySQL performance to MySQL performance ignoring any other options. (It is essentially the same tactic Micro$oft is doing all the time! Do we really want to parrot them?) First of all, there are MySQL gotchas and PostgreSQL gotchas, so you have to know whether the particular glitches are acceptable for you before you decide to use either RDBMS. Understanding the relational algebra, set theory and predicate calculus is essential to understand what the relational model is all about. Lack of this knowledge often leads to confusing tuples with OOP-style objects and other stupidity, so you will save a lot of time learning it first.

Now, the performance. Generally speaking MySQL is faster for a heavy load of simple read-only queries (like Slashdot) while PostgreSQL is faster for complex read-write queries (like a bank). Once you turn on the ACID support in MySQL it is no longer so fast, and it can really crawl because of row or even table (sic!) locking, a mistake avoided for decades by any advanced database. Here is another comparison. See also this recent thread on Slashdot. One of the best comparisons of Oracle, MySQL and PostgreSQL was done by the Computer division of Fermilab (Fermi National Accelerator Laboratory), this is a must-read.

There is a lot to read about it if you need more comarisons, but the general rule of thumb is that if you want lots of very simple read-only and very few read-write queries when the integrity of your data is not critical, you should probably choose MySQL. When you need that (or better) speed but the data is critical and you need ACID transactions which would severly slow down MySQL, try SQLite, the easiest choice there is, especially using Perl where you don't even need to install it (but just like with every other database, there are SQLite gotchas too, you need to be aware of them). If you need full ANSI SQL compatibility, ACID transactions, scalability and your data integrity is important, you should probably choose Oracle or PostgreSQL. There are also licensing issues. Oracle is proprietary. MySQL is GPL so you need to pay if you want to use it in any non-GPL software. PostgreSQL is released under a free-for-all BSD license. SQLite is public domain.

As you can see, there is no one-size-fits-all database. Every one has its strengths and weaknesses. The correct choice is a matter of trade-offs and finding out which database is optimal for your particular niche. Good luck.

Re:MySQL vs PostgreSQL

[renoX]

Uh?
As long as you don't redistribute it, doing whatever you want with a GPL program is safe..

Re:MySQL vs PostgreSQL

[agilen]

He was modded offtopic, because this article was about OS benchmarks, not database benchmarks. The title of TFA is "Using MySQL to benchmark OS performance"...so its seeing which OS mySQL runs best on, not which database is best.

Missing option

[xgamer04]

What about Mac OS X? I know (actually, not really) that Solaris doesn't run on Apple hardware, but it would be interesting to compare the same stuff on an Xserve and also be able to test the OS X performance.

Solaris 10 -- the *good* x86 Solaris

[tesmako]

Must say that it is very nice to see Solaris up there in the top in the tests that it was featured in. Seems Sun was not joking around when they claimed that Solaris 10 would be greatly improved on x86. As is often said around here; More choice is good.

Re:What about Firebird? - good point

[Anonymous Coward]

What about the Firebird relational database?

Good point. "Firebird is a relational database offering many ANSI SQL-92 features" [emphasis added] PostgreSQL "supports SQL92 and SQL99" [emphasis added]. "New code modules added to Firebird are licensed under the Initial Developer's Public License. (IDPL). The original modules released by Inprise are licensed under the InterBase Public License v.1.0. Both licences are modified versions of the Mozilla Public License v.1.1." On the other hand, "PostgreSQL is released under the BSD license." Other than that they are mostly comparable, so you have risen a very good point. If you don't need standard SQL support and the license is acceptable, Firebird is a very good option.

Say what?

[blorg]

Where Oracle ... can return simple select queries or complex insert or joined select queries in .5 to 1.0 seconds each

If Oracle is taking .5 to 1.0 seconds to return 'simple select queries', you are doing something wrong. Very large unindexed tables, perhaps. Alternatively if Oracle is taking the same amount of time to return simple and complex queries, that might indicate that something is wrong with the connection between your app and Oracle.

Your 'code library' sounds an awful lot like what stored procedures tend to be useful for - presenting a stable external 'API' for accessing the database. If the database changes internally, you just change the stored procedures, and all applications using these procedures carry on as normal.

I don't need to have "good habits" ... I don't need to have nth degree optimized queries.

Uh huh.

I agree completely that you don't need to 'swat a fly with a sledgehammer' and some applications genuinely only do need a simple database with a few simple tables.

But good habits come in useful, particularly if circumstances change and you have to scale up rapidly - your website becomes massively more popular, your HR application suddenly needs to incorporate new features, whatever. And in any case MySQL has been getting a lot more advanced database features lately, so it's no harm to know them. They might just come in handy.

Amazing

[cranos]

Somebody posts a comparison of an application running on different OS's as a system benchmark, and what do people do? Attack MySQL.

God guys get over it, MySQL is here and it has actually proven itself to be usefull. Yes its missing features and has issues, but it fills the niche it is aimed at.

Re:If TFA gets slashdotted, these are his conclusi

[jmcneill]

By default NetBSD's threading library in 2.0 only runs on one processor. To enable (experimental) support for scheduling a threaded process against multiple processors, you can set the environment variable PTHREAD_CONCURRENCY=.

Impressed with Solaris

[MajorDick]

I have always had a soft sop for Solaris since 2.51, it secure, and stable, in my experience, but alas its always been SLOW on x86 hardware.
These benchmarks show that at least with mySql its pretty fast, but more importantly look at the solaris benchmarks, they are nearly identically consistent across all test, where others vary much.

Ive always kinda thought of Solaris as a 4 wheel drive truck in low range, but it looks like they added a turbo :) I wonder if its a result of better x86 optimization or the new Filesystem

Re:Impressed with Solaris

[htd2]

Ive always kinda thought of Solaris as a 4 wheel drive truck in low range, but it looks like they added a turbo :) I wonder if its a result of better x86 optimization or the new Filesystem

The test was done with standard Solaris UFS+ apparently they used forcedirectio. UFS+ on Solaris 10 is pretty much the same as UFS+ on Solaris 9.

I hope they re-run the tests on Solaris 10 when ZFS ships in the next release which is due mid year.

I was suprised by how close Solaris 10 was to Linux, after all they used a beta OS on a very tiny system, hardly Solaris's sweet spot in the past.

Re:XP isn't a server operating system

[Anonymous Coward]

And Windows (albeit with SQL Server) does not do too badly when it comes to database performance,

You can't just fire a MySQL benchmark at SQL Server, though, and expect it to perform well. SQL Server is much less of a SELECT engine than MySQL; you need to use different paradigms, notably heavy use of stored procedures and functions.

Oracle is the middle ground - it performs very well if you program to its own model or not.

Comparing MySQL to PostgreSQL

[Sierpinski]

One thing I noticed about version of mysql (prior to version 4.1, I believe) was that mysql didn't support the notion of nested queries, which at the time, was what I really needed to perform. An upgrade to mysql 4.1 solved this, but something that what I would consider to be an integral part of sql was just "left out" of previous versions just reinforced my decision to use PostgreSQL instead. (The mysql databaase was a product of a coworker, not using our organizational standards.)

License correction on MySQL

[scorp1us]

While your post is thorough and accurate,
you glossed over the fact that MySQL is now dual licensed. This DOES have repercussions. The GPL version can only be used by GPL software OR as a special exception. The special exception is made for PHP (and maybe others). If you are a Bank and choose MySQL you have to BUY a license.

I wonder how much there is to the MySQL great for websites (many read, few write) and the PHP license exception.

MySQL 4+ is not the MySQL that we all came to know and love in the 3.x days. Previously, I used MySQL 3.x but when I needed to upgrade, I moved to PostgreSQL because of the new license alone.

Let me re iterate my take. PHP license allows you to make commercial websites with it. MySQL allows its GPL license to be used with PHP regardless of purpose of the PHP scripts by special exception. Had there been no special exception, we'd have seen the downfall of MySQL and the upshoot of PostgreSQL or SQLite.

As a user/admin of all 3, I find that you can either use PostgreSQL or get away with using SQLite. Incedentally, try using SQLite with SQLRelay if you need network access for SQLite.

Re:Wow am I disappointed in FreeBSD

[Zemplar]

Learn more about the benchmarks and WHY FreeBSD is slightly slower NOW. This time next year you will likely fully understand FreeBSD's decisions when they are again the benchmark leader on a 'typical' hardware configuration. The future is all about multi-processors and multi-core processors. Guess what? FreeBSD 5.x is ready.

FreeBSD still compiles with DEBUG and assertions

[mi]

The FreeBSD-5.x releases still compile with the debug code and assertions. From the libc_r/Makefile :

# Uncomment this if you want libc_r to contain debug information for
# thread locking.
CFLAGS+=-D_LOCK_DEBUG

# enable extra internal consistancy checks
CFLAGS+=-D_PTHREADS_INVARIANTS

It was foolish, in my opinion, to keep this in the release. I wonder, how many points the OS lost in the benchmarks because of it...

MySQL had better be fast - price increase

[xxxJonBoyxxx]

MySQL had better be fast - they just cranked the price again. In the last two months the per-100-copy bulk price of MySQL jumped from about $100 to about $230 a server.

(Yeah, I'm looking hard at Postgres now.)

Re:MySQL had better be fast - price increase

[csnydermvpsoft]

Why would you buy a license unless you need support, which you wouldn't get with Postgres unless you payed as well?

Re:MySQL had better be fast - price increase

[xxxJonBoyxxx]

Why would you buy a (MySQL) license unless you need support...

For that price you still don't get support. We pay for licenses because we ship MySQL as part of a commercial software package.

Read this: http://www.mysql.com/company/legal/licensing/

Give me ANSI SQL or give me death

[WillAffleckUW]

well, i've been having some interesting ODBC issues passing the same SQL queries to Access and to Oracle, so let's not pretend that the ANSI SQL compatible world is that compatible ...

Re:What about Firebird? - good point

[Best ID Ever!]

If you don't need standard SQL support

Firebird implements standard SQL. Firebird supports full SQL92 and most SQL99, according to the project website.

Gentoo From Scratch???

[RoadWarriorX]

After reading the benchmarks, I noticed that the Linux distribution chosen was Gentoo. I like Gentoo (and use it myself). The author of the newsforge article does not really state if the all of the kernel, libraries and applications were built completely from scratch or did he use a stage 2 or 3 install.

If the box was built from source, I would expect that Linux benchmarks would be higher simply because the kernel, libraries, and applications were most likely tuned to the hardware. Otherwise, I would like to see RedHat, or SUSE, or other "out of the box" distros in addition to the others.

Just my $0.02