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. ;)

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...

[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.

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.

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?

[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?

[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?

[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!

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.

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...

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: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."

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

[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.

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.

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.

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