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.

Re:postgres

[FuzzyBad-Mofo]

Yes, and where's their comparison of various databases running on Amiga, Atari, and Macintosh hardware? You call this "fair and balanced"?!

Re:postgres

[SillyNickName4me]

While I wouldnt call Slashdot mission critical in anz waz, I would not call it a low frequented blog either.

Your post was stored using MySQL with InnoDB because that is what slashdot uses.

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?

[Codename_V]

I could have sworn that was illegal though.

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?

[Bert64]

Well, you could run the opensource darwin kernel on the intel box, not quite OSX but it's the closest you can currently get..
You could also compare netbsd/darwin/linux between x86 and ppc systems to get some idea of the performance differences between the 2 architectures.

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

[Tethys_was_taken]

Who said anything about PostGreSQL?

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

[Anonymous Coward]

...it may even be a mute argument...

My favorite! Sometimes I can win those!

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

[Anonymous Coward]

When you can compare someone's grasp of language to Joey Tribbiani's, it makes you wonder if they really understood the rest of the words they used in their post like "Tuple calculus", "Transaction journaling", "Operator space/system call overhead", "Disk cache timings"

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

[NutscrapeSucks]

Since none of the tests apparently simulated client load, this seemed more like an OS benchmark (for MySQL) than a database benchmark. (despite however everyone is spinning it).

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?

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

[zootm]

and it may even be a mute argument ...

or even more likely, a moot argument

"You are technically correct. The best kind of correct."

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

[Anonymous Coward]

Soory, wrong answer. The card says "moops".

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

[JabberWokky]

Yep - you put it better than I did.

This is a benchmark of how a specific database runs on different OSes. Period. It's pretty simple, but people seem to think that it is something else or was presented as something else. It wasn't. Simplicity seems to be beyond people.

--
Evan

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

[Anonymous Coward]

Indeed, FreeBSD focuses on stability, and you can in many cases get ten fold performance increases in tuning specific parts.
By doing generic tuning you can expect a doubled to quadrupeled performance.

In essence, these tests are stupid, you need to know your system well and what you want from it.
Linux lack in stability and shine in performance, that doesn't mean it's bad, but it doesn't fit everyone's shoes.

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

Re:Tuning on FreeBSD

[cjsnell]

Would you mind posting your tunings? Or, e-mail them to cjsnell on the gmail.com. :)

Chris

Re:Other Benchmarking

[tomstdenis]

I'd say that depends more on your compiler than anything else. Even in windows [the OS we love to hate] once the application is in ram it can steal pretty much the entire CPU time if it wants.

Then even on the heap side [e.g. libc] is fairly standard if you use GNU libc.

Things like file performance [or network] do vary because different OSes offer different storage algorithms [e.g. JFS vs Reiser vs Ext2 vs ...].

Tom

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?

[jadavis]

Interesting point.

Perhaps there's room for a simple embedded database that allows you to filter the results easily? I think SQLite might fit this description.

If you're making a "DB agnostic" application (by which I mean you just dumb the application down so that it doesn't use any relational features), I think it's a better idea to just bundle SQLite and then it doesn't matter what database they have.

Personally, I usually have several applications accessing the same data set, so I use an RDBMS for that (for many reasons). But many off-the-shelf web apps just don't benefit from that kind of thing.

Re:Why do people use MySQL over Postgres?

[fiftyfly]

What I don't understand is why many projects use MySQL or Postgres AT ALL.
<snip>
Same goes for XML, people use this stuff everywhere and anywhere they can

Why replicate database functionality when you don't have to? Esp if there a good chance there's a server in widespread use, probably already installed, available? Same thing goes for text file parsers. If you're going to store, transmit or import text files it makes sense to use XML. There's no point in wasting time, effort and encouraging engineering gotchyas be writing your own parser when a perfectly good tool is available. Leverage the tools and build something worth taking an interest in, don't waste those resources builing yet another $tool.

Re:Why do people use MySQL over Postgres?

[jadavis]

waaaaaay faster on writing

I'd be interested to see some benchmark results. I doubt that is true for highly concurrent UPDATEs. And you also failed to mention whether you were using MyISAM or InnoDB.

I think that databases should be benchmarked based on the application, not a set of queries.

Also, you should take into account concurrency, since that usually matters when performance matters.

Re:Why do people use MySQL over Postgres?

[HyperChicken]

"Very simple databases" and I'm guessing "very simple load". Try comparing PostgreSQL and MySQL while doing concurrent SELECT's, INSERT's, and UPDATE's. PostgreSQL will be faster under high concurrent load as it does row locking instead of table locking. PostgreSQL handles higher load than MySQL in general. Speed is usually pointless: It takes more time to run the PHP script or transfer the data back to the web client than it does to recall from the database.

Re:Why do people use MySQL over Postgres?

[HyperChicken]

A general statement like "it makes sense to use XML when dealing with text files" is pure stupidity.

XML increases the data size which increases the time it takes to transfer, the disk space, and the memory space it uses. Further, XML parsers are rather slow.

XML? I wouldn't.

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?

[fiftyfly]

A general statement like "it makes sense to use XML when dealing with text files" is pure stupidity.

Yeah I can see how such gneralization would be *ahem* stupid

XML increases the data size which increases the time it takes to transfer, the disk space, and the memory space it uses. Further, XML parsers are rather slow.

b/w is cheap, disk space is cheap, ram is cheap, cpu time is cheap, developer time well maybe not so much. If one is looking at a problem and the solution seems to be text files then non of your concerns are likely to a consideration. Engineering time and tool compatability are though.

XML? I wouldn't.

Never said you did but methinks someone has prematurely jumped on the XML bashing wagon. There are many problem domains where the _best_ solution isn't necessarily the most elegant or the most efficient one.

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?

[HyperChicken]

b/w is cheap, disk space is cheap, ram is cheap, cpu time is cheap, developer time well maybe not so much. If one is looking at a problem and the solution seems to be text files then non of your concerns are likely to a consideration. Engineering time and tool compatability are though.

Saying things like "hardware is cheap" leads to the performance problems of today. It's extremely bad thinking by a developer and leads to bad code.

There are plenty of areas where text files are fine but XML is not.

Never said you did but methinks someone has prematurely jumped on the XML bashing wagon. There are many problem domains where the _best_ solution isn't necessarily the most elegant or the most efficient one.

I think someone haphazardly jumped on the XML bandwagon after being sucked in by the media hype, never having actually looked at the "technology".

Re:Why do people use MySQL over Postgres?

[Profane MuthaFucka]

Postgres supports unicode now.

Re:Why do people use MySQL over Postgres?

[Boronx]

So I don't have to write my own data routines all the time.

So if I get hit by a truck, all accumulated data is right there, easily accessible even before my replacement has figured out my horrid code.

It scales. They've put a lot of working into making it easy to complexify your data structures as you need to. That's work I don't want to do, and especially don't want to wish I had done 3 years ago.

If I want to do some little thing, I can do it painlessly in any language I want, because someone else has already written the API.

And, sort of as a side benefit, it makes think of your data structure in a differently, which can occaisionally be enlightening.

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?

[zyridium]

And if you want to use a database with the benefits of both, try MSSQL :)

Re:Why do people use MySQL over Postgres?

[Spacejock]

I use Mysql (and PHP) because the first article I read on dynamic web pages involved LAMP. Finding more LAMP articles was a snap, and a week later my site was working and I didn't care what else was out there.

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?

[Mishura]

LAMP rocks. For a simple web server that makes use of PHP; its simply the easiest, and the performance ain't half-bad. If you can do this w/o much headache, then why bother with alternatives? (Plus all the components of LAMP is opensource; another plus!)

Does Postgres/Firebird/etc rock? Perhaps. But MySQL is the most "documented". I can walk into the bookstore and come out with some nice books about PHP/MySQL from different levels of difficulty. That's why its popular.

Its all about documentation. Printed documentation is the BEST. I simply hate reading stuff on a computer. I'll take a dead-tree book over an ebook or html docs anyday. (No offense to trees. :) )

Re:Why do people use MySQL over Postgres?

[Anonymous Coward]

You obviously have not used postgresql. This is how you normally write it:

CREATE TABLE folder (
FolderId SERIAL PRIMARY KEY, ...
);

SERIAL was introduced in postgresql 6.4 (1998, that's 7 years ago).

Re:Why do people use MySQL over Postgres?

[aDc_73]

Well if you like perl and bash you probably like dollar signs, but can you explain wtf is with this syntax (taken from the postgres manual):

$function$
BEGIN
RETURN ($1 ~ $q$[\t\r\n\v\\]$q$);
END;
$function$

Re:Why do people use MySQL over Postgres?

[aDc_73]

Why post as A/C? You made a good point. *adc is a noob* ;)

Re:Why do people use MySQL over Postgres?

[jabuzz]

Easy, it is historic. Way back in the early days of Linux Postgres did not do SQL, which left you with two choices, mSQL and MySQL. Basically mSQL fell by the wayside due to licensing issues which left MySQL as the only free SQL database available.

From there on in there is a certain amount of built in momentum that is hard to stop. Just look at the tactics that Microsoft resorted to, in order to overcome the momentum that Netscape had.

Re:Why do people use MySQL over Postgres?

[shadowmas]

i didnt know much about MySQL (still dont) but with all the people talking about it i figured i should give it a try for a new project. i was stunned to see that it either doesnt support or has very wierd quirkies for a relational database. referential integrity is not good, automatic defualt values etc, i admit mysql is fast. but with mysql i end up having to do most of the validation that i would otherwise use the sql server to do.

Re:Why do people use MySQL over Postgres?

[HyperChicken]

I completely admit most of my MySQL knowledge comes from the 3.x days.

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?

[Darren Winsper]

I do get very annoyed that MySQL automatically creates default values. It really, really annoys me that you can't create a table with, say, a date field in it that can't be inserted into without specifying that date. If you don't specify a date, MySQL doesn't piss and moan like it should, it just puts "0000-00-00" in for you. Argh!

Regarding referential integrity, I'm not sure why you think MySQL is sub-par, but InnoDB tables seem to behave sanely for what I'm using them for.

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?

[Bert64]

And a default function with the ability to execute SHELL COMMANDS, or write to the registry among other things.. Turning any sql injection vulnerability into a full system compromise.

Re:Why do people use MySQL over Postgres?

[zyridium]

Nice story, apart from the fact it is complete bullshit. Unless you mean you set up your database user to be in the sysadmin role...

And if you program using the normal database APIs you won't be having any injection attacks anyway... creating SQL commands by concatenating user input is just an ass of an idea anyway...

Re:Why do people use MySQL over Postgres?

[hachete]

Ah, ah. This is the reason that there are a lot of tools out there - which is one of the reasons that I use MySQL in an in-house projects. Not because it's the best featured SQL database in the world. It looks like a benign cycle is in place.

Most jobs you need tools which are "good enough". Hang on - isn't this one of the reasons that MS win out? It also gives the uber-geeks something to sneer about.

Re:Why do people use MySQL over Postgres?

[Spacejock]

I agree - the parts hook together so well and it's a neat acronym to boot.

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?

[Bert64]

Development cost is usually a one-off, bandwidth may be cheap but it soon adds up, as does memory and disk space, therefore the more heavily you plan to use the system the more you benefit from not using xml.. Also, in many places of the world bandwidth, diskspace and cputime are not cheap or readily available while developers are cheap.

Re:Why do people use MySQL over Postgres?

[justins]

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

If it's really performance you want from a database, you setup multiple disk volumes before you even begin worrying about multiple CPUs. The test machine this guy used is sort of symbolic of the knowledge level of the average MySQL user.

Re:Why do people use MySQL over Postgres?

[james_orr]

Yeah, I didn't say that postgres was around first, I said mysql did more first.

Re:Why do people use MySQL over Postgres?

[Decaff]

Saying things like "hardware is cheap" leads to the performance problems of today. It's extremely bad thinking by a developer and leads to bad code.

The is completely the wrong attitude. Performance problems should be the last worry of a developer. Other concerns like 'does it work right?' and 'Can others make use of this software?' and 'Will this software go out of date because no-one can understand the format of my text files?' are FAR more important than raw speed.

There are plenty of areas where text files are fine but XML is not.

Name one.

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?

[Bert64]

Either way, there are thousands of websites out there with sql injection attacks where you can execute shell commands on the database server. Were this any other type of database then these users would be slightly protected from their poor coding since the attackers could only corrupt the database and not infiltrate the system hosting it.

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?

Re:Why do people use MySQL over Postgres?

[ozzee]

Hmm. "Unicode" isn't an encoding.

I think the Unicode and ISO10646 comittees agreed that UNICODE is both the description of the standard and when used as an encoding specifier, it indicates UTF-16. (Not that I liked the idea but it is a valid interpretation.)

In the case of Postgresql, I think it uses utf-8 as the encoding which IMHO is the best encoding for Unicode since it is not stateful, whereas UTF-16 is.

Re:Why do people use MySQL over Postgres?

[arkanes]

If you're reading too and from files, and performance is such a factor that your XML parser is too slow (by the way, expat is *amazingly* fast and will often beat hand-rolled parsers that are not written by experts, even for simple grammars), then you probably shouldn't use a text file. XML has a huge share of warts. I'll go so far to say that it sucks. But it does have very clear and real advantages over raw text files. Data exchange is the most obvious and the most real, although the hype makes it bigger than it is, and it's realistically not a concern for many people. The other advantages are the saving in developer time of a consistent, well known set of APIs and file formats.

Re:Why do people use MySQL over Postgres?

[Qbertino]

I'm been working on a custom ERP system for 2 years now. Recently we went from flatfile to DB. Yes, you can have a flatfile ERP system, although the amount of maintanance modules does grow pretty fast.

We chose MySQL over Postgres and Firebird. Why?

One answer:
10 times the amount of available tools and resources.

Time to market is a fraction of what it would be with any other DB.

Otherwise I'd be using Firebird.

Re:Why do people use MySQL over Postgres?

[jadavis]

I've never quite understood that. I just download the debian packages for postgres and it works right away. The source isn't much harder to install, and I can't really see how the mysql source would be easier.

If you have an actual suggestion to make it easier to install postgres I'm sure the developers would implement it.

Re:Why do people use MySQL over Postgres?

[Jamesday]

In this case, it's more a question of maximising use of limited resources. Timeouts and saving problems at present are generally because of overloaded Apache web servers. A batch of 10 more (taking us over 50 machines total) was delivered a few days ago and will be entering service soon. On the database side I'm putting together specifications for our next batch of machines.

With enterprise considerations, putting a database-driven top 100 site together with equipment costing in the $100,000 range is probably contraindicated.:) While the equipment, tools and people are capable, that does produce some load stresses. Fortunately, we can deal with those, though exponential growth with doubling times in the 8-16 week range when not performance-limited are always a challenge. If money was being lost due to response time or problems, the economics would shift dramatically, in favor of larger budgets. In our case, we might even get increased revenue from modest problem levels, though not from significant ones, since that cuts growth.

For web applications the enterprise trend today seems to be to move away from big iron when it comes to delivering services directly to end users, even if the big iron remains present in the background. Very much a case of picking the best tool for each job, in my opinion. That could easily be a mixed environment.

I do wonder what Microsoft, Oracle or other major corporate players would charge a corporation for a license for a 6 dual CPU server license with Wikimedia query and traffic levels. Anyone know?

Re:Why do people use MySQL over Postgres?

[decefett]

MySQL was faster (slightly faster on reading, waaaaaay faster on writing)

The default install of PostgreSQL is paranoid about writing data to disk, basically as soon a you do an insert it is written, there is no buffering by the OS which means if your server crashes you don't lose data.

MySQL on the other hand lets the OS buffer the data before writing, this is much more efficient but dangerous (depending on your application).

If you are willing to sacrifice data integrity for performance (if you use MySQL you obviously don't care about data integrity), PostgreSQL can also be configured to allow OS to buffer disk writes with a one line config file change, fsync=false.

With fsync=false PostgreSQL should be at least as fast as MySQL.

Re:Why do people use MySQL over Postgres?

[Decaff]

XML has a huge share of warts. I'll go so far to say that it sucks

I really don't understand this sort of comment. XML is based on years of research into how to add meaningful markup to textual information in a way that is easy for software to parse and process. It is also designed for internationalisation and extensibility.

Re:Why do people use MySQL over Postgres?

[ErikZ]

I have yet to be able to install and use postgres.

This "Slightly easier to setup" is one of the big reasons MySQL took off. People could EASILY install MySQL.

Postgres? Not so much.

Re:Why do people use MySQL over Postgres?

[arkanes]

... by committee, with all the political problems, design inefficencies, and contradicting goals that would indicate. People have this idea that "years of reasearch" neccesarily means "better", let me tell you it ain't neccesarily so.

Furthermore, even if XML were the best way to markup text, and it was the easiest for software to parse and process, and it had the best support for extensibility and i18ln (and I wouldn't neccesarily grant any of those things), XML could still suck because of all it's design warts that are unrelated to those features.

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.

Re:Useless Benchmarks

[setagllib]

In name only. Read the errata and lists, it was to get it out into the open for testing, not for adoption by production environments (although some have done so very successfully).

I would argue that a stock Linux kernel given out with an unfortunate binary distribution wouldn't have features that do make a difference. Many skimp on things like local APICs (for interrupt routing), add on SMP (which is slower on UP), and root knows what else. He configured a Linux from scratch and would NOT have made it generic, since he knows what he's doing. He did NOT reconfigure the BSDs (save the essentials) and we all know how conservative their defaults are.

It wouldn't make all the difference, but it would mean NetBSD 2 would beat Linux better in the UP case, and would hopefully stink less in the IO case (but I repeat, that's something really weird, and it's boggling the mailing lists). FreeBSD would do better too, which it really needs to.

Just saying, someone who knows what they're doing for all systems should benchmark, not a Linux yuppie who decides the world needs another biased benchmark to decide not to use a BSD. But I'm not saying Linux isn't a marvel of performance (well, there's still eth1394 sucking, but nobody seems to care), it's just not as far ahead as this bench would 'indicate'.

Re:Useless Benchmarks

[justins]

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 wouldn't worry about it. Running the OS software, the swap space, the database software, and all the database storage on the same disc is going to torpedo I/O performance regardless. Having to rattle the drive head back and forth between all that stuff is ridiculous, and the test results would have been skewed by simple problems like where on the disc the database files are in relation to the OS files, and crap like that.

But he could afford 2 CPUs for some reason.

Re:Useless Benchmarks

[Fweeky]

These benchmarks are actually pretty close to what you get no matter how well you tune things. FreeBSD consistantly performs a lot poorer than Linux for some reason, although they're slowly improving.

Disk IO is irrelevent; you see this even on on read-only tests against completely cached tables. Believe me, I wish to hell it wasn't.

Re:Useless Benchmarks

[Brandybuck]

Let me guess, the benchmarks you use to prove this didn't optimize FreeBSD either?

Re:Useless Benchmarks

[Brandybuck]

Even though its quite known FreeBSD 5.3 ain't doing very well (DFBSD is)...

Considerign that Dragonfly hasn't yet released a stable version, this marks your whole post as a troll.

Re:Useless Benchmarks

[Fweeky]

Let me guess, you don't follow any FreeBSD mailing lists, you've never done any MySQL benchmarks yourself, you've never spent countless hours tweaking everying imaginable to try to improve MySQL's performance under FreeBSD, and you've never had to manage a heavily loaded production MySQL cluster in a primarily FreeBSD environment?

Re:Useless Benchmarks

[Brandybuck]

Gee, we were talking about benchmarks. Benchmarks. As in objective comparisons between two different things. Not anecdotes from mailing lists.

I am under no delusions that FreeBSD is is perfect, but to assume from its imperfection that it must be worse than Linux is unwarranted. It may very well be true that Linux is faster in all aspects than FreeBSD, but without fair and objective benchmarks with optimizations for both sides, I have no way of knowing.

Re:Useless Benchmarks

[LuSiDe]

1.0 got released in july 2004. But i weren't even meaning it terms of 'stability' or 'stable version' so back off.

Re:Useless Benchmarks

[Fweeky]

Huh? I'm talking about MySQL on FreeBSD and you take it as an attack on FreeBSD's general performance? You've completely missed my point here; FreeBSD's great for most use, it just happens that MySQL on Linux performs much better than MySQL on FreeBSD (and just about everything else it seems).

This isn't an OS advocacy issue (I vastly prefer FreeBSD to Linux), this is a real problem for certain heavy MySQL users. I'm not on the "FreeBSD suxx Linux r0xx!!" side here, I'm on the "Someone please track down and remove the bottleneck(s) so we can depenguinate the few Linux servers we've been forced to migrate to" side. Sheesh.

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:use windows!

[davidpb145]

I take it your using a 5 year old SQL server 2000...

Did you even read the article?

[BobPaul]

From what I gathered in the article he never GAVE benchmark results? Where the hell did all of what you posted come from?

From the article:
In the next article, I'll present the results for all six operating systems.

Re:Did you even read the article?

[isometrick]

Click the second link in the summary, sir.

Re:Slashdotted too soon

[isometrick]

Linky

Re:Slashdotted too soon

[BobPaul]

Oh wow! You mean there were too links in the summary.

Hee-haw! Hee-haw!

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?

[negative0]

Your sig says 11 types who can count in binary, and then names only two? Who's the third group? Or did you mean 10 types? Maybe the third type are the people who can count *and* tell jokes in binary?

Re:Different filesystems?

[Shano]

Or maybe he's in the latter group?

Re:Different filesystems?

[tayhimself]

Why is irony so lost on some people?

Re:Different filesystems?

[PedanticSpellingTrol]

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

Re:Different filesystems?

[bill_mcgonigle]

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

Ideally, no. For most databases the filesystems just get in the way. They maintain a set of plain files for the database and try to do almost everything themselves.

To the extent that the filesystem can stay out of the way various filesystems may bench better than others.

In fact, Oracle would rather you gave it a raw partition than a filesystem, so there's nothing to get in the way.

Re:Different filesystems?

[bill_mcgonigle]

Why is irony so lost on some people?

You have to consider the odds of a given comment/sig on Slashdot being wrong, ill-conceived and inappropriate vs. the odds of it containing a deep irony.

If Stephen Wright had made the comment it might have garnered different respect.

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.

Re:Mac OS X.

[inertia187]

Nevermore.

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

[zeromemory]

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

They do if they want to build a single-purpose, high-performance MySQL server or cluster.

Re:How relevant are those benchmarks

[Black Art]

Not to mention what it takes to tune the OS and keep it running correctly.

In my experience Linux is MUCH easier to keep running than Windows.

Windows administration relies on far too many "black magic" registry tweaks. In order to make adjustments in many cases, you need to set obscure registry entries that are usually only known through rumor and hearsay. (If you can fix it at all.) Every place I have worked with a mixed environment, the Windows admins spent more time fixing and patching and tuning to keep machines running instead of getting other projects done. I controlled 5x the Unix boxes and rarely had a problem.

Anyone who says "Windows is easier to admin" has either never admined a Unix network, a Windows network or both.

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:How relevant are those benchmarks

[oconnorcjo]

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.

If you read the article you would know that only Linux and Solaris took any real advatage of multiple processors.

might as well buy cheaper hardware unless you are planning to use one of those two OS's

Re:How relevant are those benchmarks

[klreed42]

Um, YES.

Not explicitly, no. But while I worked at a large HedgeFund (700 people or so) they benchmarked various hardware/OS platforms to determine moving from Sun boxes to PC boxes.

For a 200 CPU IBM blade server it would cost them 400k, for the equivalent SUN box it would cost them 1.6M. The blade outperformed the Sun box by 2-4 for their applications, which was a significant performance boost for their real time applications.

They made a strategic direction to go and replace all SUN boxes with Linux boxes.

So, the OS may not have been evaluated, other than on vendor support, expected longevity, and reliability. But I find this comment unnerving "It's easy to lose that $1,700 in downtime, spend it on a Windows engineer's new RHCE or such...". Simply because it assumes that Windows is (and will be) more reliable than the alternatives.

Re:How relevant are those benchmarks

[ShieldW0lf]

So, the OS may not have been evaluated, other than on vendor support, expected longevity, and reliability. But I find this comment unnerving "It's easy to lose that $1,700 in downtime, spend it on a Windows engineer's new RHCE or such...". Simply because it assumes that Windows is (and will be) more reliable than the alternatives.

I believe what he was saying was "If you have the option to choose between changing architechtures or doubling the hardware to achieve a certain performance, doubling the hardware will be cheaper. It's cheaper to double your hardware than to train a favorite monkey to use another OS."

Re:How relevant are those benchmarks

[YU Nicks NE Way]

even microsoft run a lot of their backend stuff on solaris and as/400 systems for instance

Not true now, and hasn't ever been true. We got rid of our last Unix box -- which ran Xenix mail -- on the main campus about eight years ago, and got rid of the last of the solaris boxes at Hotmail about three years ago.

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.

It's the cutsie name

[toadlife]

"MySQL" sounds like something Yahoo or Microsoft would release. PostgreSQL sounds like ... an open source application.

Re:It's the cutsie name

[CmdrGravy]

Personally I say it MyS-Q-L and think MySequel is a horrible term but then I also refer to Microsofts S-Q-L Server in the same way and pronounce Linux as Lie-nux.

Re:It's the cutsie name

[FrostedChaos]

That's because you're a LIE-beral, isn't it?

The only thing worse than your 80th DB argument...

[abulafia]

...on slashdot is the 81st.

As someone who develops on PG, Mysql, Oracle and several other DBs, let me just say that if you're so wrapped up with your RDMBS that you react like that, you should really take up jogging, get a dog, start doing heroin, or something.

Christ on a cracker, some geeks are more of a downer than condom breakage. Coding is supposed to be fun.

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

Re:Procedural problem with NetBSD multiprocessor

[Anonymous Coward]

it's because NetBSD insist on N:M threading, and there's no sane way to do that (which is why the people behind NPTL gave up trying on Linux and just made threads so lightweight you could afford a few thousand running at once)

So NetBSD is really asking the user for a hint: How many _real_ threads should we use to emulate the threads you've asked for ?

Needless to say providing performance hints could always help a threading implementation, but as we saw here, it also means the administrator must painfully figure out which settings are optimal.

Re:Other Benchmarking

[GreyWolf3000]

You should have used malloc--in Linux, you malloc till you run out of memory, Linux starts killing processos to free it up.

Re:Other Benchmarking

[GreyWolf3000]

Doh! Please no one correct me, please no one correct me...

Re:You know you're a nerd when...

[Profane MuthaFucka]

...and you claim that emacs will kick the shit out of both of them.

Wikipedia

[MikeCapone]

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

Re:Wikipedia

[HvitRavn]

Hehe, it's good enough for wikipedia/mediawiki allright, but look how slow it [wikipedia] is. I use mediawiki on a website of mine as well, and it lags the entire system.

Re:Wikipedia

[MikeCapone]

That's true. My post just meant that I don't need anything more than Wikipedia, though. More than one person can have a point at the same time.. Heh.

Re:NewsForge == OSDN == Slashdot

[MikeCapone]

I think you missed the joke. Not that it was that funny...

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

[Donny Smith]

> MySQL is GPL so you need to pay if you want to use it in any non-GPL software.

I find it quite amazing how dozens of companies I know have no clue about mySQL licensing. They think everything one finds on a Linux install CDs is free.

While it's unlikely mySQL.com will ever squeeze these guys into paying up, it's funny to see all those bozos thinking they've living in compliance.

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

[Red Pointy Tail]

Thanks for the wonderfully written post!

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.

Re:MySQL vs PostgreSQL

[kannibal_klown]

GPL only applies to redistributing the software/information-system that uses it.

Most companies use it as an in-house database. Maybe it hosts something for a web page, maybe it holds non-sensitive information to query against, etc. In this case, they're free to use MySQL for, well, free.

Re:MySQL vs PostgreSQL

[3ryon]

Your post was so informative and reliable, I think you must be on ACID.

Re:MySQL vs PostgreSQL

[natedgreat]

My experience with SQLite though is that later if you want to migrate to another database, it is very cumbersome. I would up spend $10k just to have a DBA come in and move tables becasue I could not import SQLite into something like SQL or Oracle directly.

Re:MySQL vs PostgreSQL

[renoX]

I doubt that it is possible to do this: dual licensing *increase* the user possibilities not limit it.
As long as the GPL allows you to use the program without any consequence if you don't redistribute it, the other license doesn't matter..

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:Missing option

[Mishura]

PPC architecture, yes. All these OSes run on x86.

However, I'd like to see on the PPC side; Mac OSX server vs. opensource Darwin (not apple's OSX server) vs. Yellowdog (or debian, or gentoo..) linux. Plase, just for f**ks sake, do not try to compare these with the x86 results. Apples n Oranges. (or is that Intels?)

Re:Missing option

[prockcore]

What about Mac OS X?

We use MySQL on a G5 xserve. Performance hasn't been a problem for us, but we haven't really stress tested it yet. We also use MySQL on two of our Suns (V210 Dual 1.25) and it has been rock solid and extremely fast on both machines. We stress tested Apache2 on the xserve and it fell apart before the V210... so I'm going to say that the v210 is probably a better mysql box than the xserve as well.. but the xserve is $1000 cheaper.

FWIW, mysql says that the most *stable* platforms for mysql is Solaris and SuSE Linux. Strange, I know.

Why are we even doing this?

[l33t-gu3lph1t3]

No serious DBA uses MySQL. Oracle, Sybase, PeopleSoft(soon to be oracle), IBM...

MySQL is what they use to teach you structured query in school. Databases have come a long way in FEATURES in the last 10 years...

Re:Why are we even doing this?

[freemacmini]

"MySQL is what they use to teach you structured query in school"

And it's terrible for that. People should use firebird it has much better SQL compliance.

Re:Why are we even doing this?

[cranos]

For its niche target MySQL is perfect. We manage to run a nice little sales reporting system using MySQL as the backend and we haven't had any problems.

The DB's you have listed can be serious overkill when you are dealing with small to medium sized businesses especially when you have OSS dbs such as MySQL and PostgreSQL available.

Re:Why are we even doing this?

[Patoski]

No serious DBA uses MySQL. Oracle, Sybase, PeopleSoft(soon to be oracle), IBM...

MySQL is what they use to teach you structured query in school. Databases have come a long way in FEATURES in the last 10 years...

Any decent craftsman will use the correct tool for the work at hand.

Sometimes the right tool is the most feature packed product you can find (Oracle). Other times the smaller tool with fewer features but better performance is preferable (MySQL).

Don't go squirrel hunting with an elephant gun.

Re:Huh

[DenDave]

Perhaps if they had a little bit of that old magic SQL compliance

Certainly you have read the documentation and noted their standpoint on compliance. It's a question of choice and that's all yours..

I don't believe you!

[Phidoux]

They only said 20% faster?

MySQL still faster than Firebird

[plibnik]

Our company uses Firebird as a main database engine, it is indeed feature-rich compared to MySQL (triggers, stored procs, events, transactions, versions) and more mature/stable than PostgreSQL; yet we replicate the base to MySQL/PHP system for web portal functionality.
It turns out cheaper to establish a replication between two databases and use strong features of both (rich feature set and Borland's visual data controls + PHP/MySQL integration and raw speed), than settle for any single DB.

It would be disgusting to have no triggers/procedures on main DB; but working from PHP with Firebird seems to be not too native.

(We did also consider PostgreSQL but dropped it due to our 24x7 requirements and its VACUUM problems...)

Re:MySQL still faster than Firebird

[Unordained]

working from PHP with Firebird seems to be not too native

Really? I've found the ibase_* functions rather clean, though php.net's examples fail to clearly indicate how to use transactions, even though it's quite easy (and a really good idea.) I wonder if that's just part of the "get up and going" approach, or a hold-over from other *cough*MySQL*cough* RDBMSs that didn't have/encourage transactions.

While we use IBX for our Win32 apps, we don't use any of the visual components, and we could just as easily do with IBObjects, or even a few wrappers around the C API. We use IBX about as much as I use the ibase_* functions in PHP, with wrappers to STL structures. (Note: IBX isn't guaranteed to continue working with Firebird, as Borland does its own thing with Interbase. We're screwing ourselves over by using it.)

Do you convert the database layout to a data-warehouse/star topology for use with MySQL? I could see how a simpler table layout, particularly for mostly read-only operations, would make MySQL more appealing. (Firebird has a read-only mode in which transactions are ignored and things speed up, but we've never used it. All our reporting is done against the live database, up to the last committed transaction. Managers won't have it any other way.)

I thought I heard something about PG's "vacuum" being fixed/replaced in the near future, so the 24x7 thing won't be an issue anymore?

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.

What about Firebird?

[Futurepower(R)]

What about the Firebird relational database?

Evans Data says it is the best, in a survey done for 2005, but copyrighted 2003. (I'm uncertain how much they should be trusted.)

XP isn't a server operating system

[blorg]

As an other poster hinted, Windows 2000 server or 2003 server _would) be an interesting comparison. And Windows (albeit with SQL Server) does not do too badly when it comes to database performance, particularly when you consider Price/Performance.

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.

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.

Re:Say what?

[ErikZ]

Heh. It only takes once.

"Why bother?" I thought. "It's just a dinky little website with a few hundred stories. No need to normalize the tables. One table will do."

Man, trying to normalize the database a few years later as more functionality was added to the website was a pain in the ass, and a lot of work I could of avoided if I did it right the first time.

The school of "Learning the hard way" is always open and accepts everyone, even if they didn't apply.

databases ? where ?

[robnauta]

The heading is misleading. What do 'database performance' and testing a toy like mySQL have to do with eachother ?

MySQL may be fast, because its features are so limited. Sure, it stores and retrieves records, but its partial implementation of SQL (without subqueries etc) and blatant bugs that violate SQL (try inserting '123456' into a varchar(4) column, it will silently truncate to '1234' instead of giving a 'Inserted value too large for column' error) make it useless for anything reliable.

If 'fast' is your only important concern you should be using flat files or dbm/ndbm files, but there is more to a database than speed.

Re:databases ? where ?

[gregfortune]

Argggg... It has subqueries (MySQL 4.1). It has transactions (INNOdb tables). It has a binary log. etc, etc, etc. Please check this kind of bull against the crash-me section of MySQL's site.
http://dev.mysql.com/tech-resources/features.html

Not sure what to say about the truncation because MySQL does perform in the manner you describe. What confuses me is why you're even allowing bad data to reach the database... How about you just let your users know they entered more data than you had designed the schema to handle?

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:Amazing

[ducomputergeek]

I've noticed this over the years, especially from the postgres fans and more recently firebird SQL. However, people should read what MySQL was designed for orginally (raw speed with SELECT) and what the project use is going to be.

MySQL is ideal for running a CMS or other website with slight or moderately changing content. Why? Because, from my experience, your running more SELECT queries than Update or INSERT commands. Plus the fact that there are way more web apps (especially OSS) that have been written in PERL/MySQL or PHP/MySQL.

I ran a browser-based online game that was orginally designed for php/MySQL simply because I could deploy on most hosting accounts with that combo the cheapest. After a while, MySQL would become easily corrupted and crashed because of high loads with many more Updates, Insert, and delete queries. As more people were playing and I was able to switch to self-managed dedicated servers I switched to PostgreSQL and rewrote the code base and it solved most of the problems, but if more than 400 users were online, the system would slow down considerably. Granted everything was run on a single OpenBSD box, but it was only costing me USD 65 a month to host and on a college budget that was about what I could afford with revenues from the game.

It still largely depends on what your objectives and type of project your going to be doing. If I were to develop some in house intranet system or the like, probably would consider a system like PostgreSQL. Same with an E-commerce site. But if its a blog site or other CMS powered system where there aren't tons of content added on a daily basis, chances are I am going to go with some MySQL based solution.

Something else to consider, especially in being in the consulting world, is on staff talent. If all your people are MSCE's then using any *iux-based solution isn't a good Idea because of the huge overhead costs in retraining or hiring new/additonal staff. If the people and systems are Solaris/SPARC-based, then find a solution for that situation. If a company is building an IT division or system from scratch, then its time to look at various *iux solution including Linux, *BSD, and Apple. Company I work for now is in video production. 99% of our systems are Macintosh. When it came time to build a 6TB SAN, we went with Xserve. More expensive than an x86/Linux or *BSD based system. Not when you start comparing solutions (including support agreements) from vendors like IBM. But on staff are 6 full-time Apple Nerds. We already had a couple small workgroup servers with 10.2 Server on them so training costs were not significate for us.

Right tool for the right job period. When I worked as a consultant for 2 years I learned real quick that being a zealot for one system over another doesn't work and there are many more factors involved in IT decision making and TCO decisions than just hardware and software. Learned very quick that hardware and software are much cheaper than labour and downtime.

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:Impressed with Solaris

[chez69]

yeah, if you run AIX you really see how much faster the RS/6000s are compared to SUN

Re:Impressed with Solaris

[bpechter]

Not with AIX on x86.
8-)

I've used Solaris 1.x-9 and Aix 3.2.x->4.1.4
and I've had good luck and results with both.
I'm sorry IBM didn't put AIX on x86 and went with Linux. I'd have liked the availability of both.

I think AIX is easier for non-tech heavy sites with the gui based admin tools.

Webmin's not quite as slick. And when an RS6000
dies with numbers in the LED's it's rather cool.

Bill

Solaris 10

[SQLz]

Its good to see that Linux 2.6 kicked Solaris's ass.

Re:Solaris 10

[SQLz]

Never had a linux system that took a shit, sorry. Never had a linux system that kept me up till 2am either. Actually, I've never had a Solaris box die on me other but on the flip side, I've only used solaris for low bandwidth tasks like DNS.

Re:Solaris 10

[htd2]

Its good to see that Linux 2.6 kicked Solaris's ass.

So you think that Linux 2.6 being ~5% faster than a beta version of Solaris 10 is evidence that Linux 2.6 kicked Solaris's ass ??

Interesting, the reality is more like a wakeup call for Linux advocates. Single CPU performance is hardly Solaris's sweet spot up to now but Sun seem to have managed to deliver rather good single CPU performance which coupled with Solaris's undoubted scalability will make a rather daunting combination when compared with Linux.

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

Nice flamewar in the comments

[tayhimself]

RTFA and then check out the comments. There is a moron AC (who happens to love gentoo) who thinks recompiling the toolchain gives him faster binaries. Another AC spends a great deal of time trying to explain that this is not possible before the flaming starts. Fun stuff! If all the OS's are configured similarly (which the author didnt say they were) they should perform the same except for the 2cpu tests. There simply isnt a whole lot for the OS to do in these tests other than retrieve stuff from disk or something. The 2cpu tests are definitely interesting.

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:License correction on MySQL

[Rasta Prefect]

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.

Uhhh...No. If you want to use MySQL _code_ in non GPL software, you have to buy a license. Theres nothing that prevents MySQL from working _with_ non-GPL software. I have no idea where you got this idea about dual licensing, but there appears to be a lot of it posted on this article.

Re:License correction on MySQL

[_xeno_]

Probably from MySQL's license page, which seems to be confused about how you can or can't distribute their database. Specifically:

Under the Open Source License, you must release the complete source code for the application that is built on MySQL.

...

Free use for those who never copy, modify or distribute. As long as you never distribute the MySQL Software in any way, you are free to use it for powering your application, irrespective of whether your application is under GPL license or not.

Obviously if your application only uses their database there's no reason it would need to be GPLed, since you're distributing two separate pieces of software.

However, there appears to be some sort of confusion when it comes to using the drivers, which are also under the GPL. I expect that no matter what MySQL says, if you're using JDBC or ODBC drivers than you don't need to GPL your application, since the linking doesn't occur until runtime and you never actually call any MySQL methods, just ODBC/JDBC methods. (Same for Perl's DBI, probably.) PHP's MySQL drivers are more questionable, because they're explicitly only for MySQL.

Basically, MySQL seems to want people to believe that you need to GPL your code if you so much as use their database. Otherwise, you'd need a license. That's BS - you aren't allowed to add restrictions to a GPLed product. And the GPL is pretty clear that your use of a product does not make you fall under the GPL.

Using the MySQL DB drivers in non-Open Source code, on the other hand... That's much more murky. Personally I believe that it's a non-issue in cases where your code uses a public interface like ODBC, JDBC, or Perl's DBI to access MySQL.

Re:Other Benchmarking

[joib]

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


Linpack, stream, polyhedron, livermore kernels, you name it. Most of them are freely available and require little more than a Fortran compiler.

what a lightweight

[justins]

(1) 9 GB SCSI-160 (7200 RPM)

Okay, this guy is not a database person. Now, the choice of database product to benchmark with might have been a dead giveaway, but so is the choice of configuration. If you're not going to test with a real database configuration (which the author of the article has probably never seen) why bother? Funny that he'd test on a machine with two CPUs and one drive, though. Great judgement in hardware selection.

Releasing the results against an old build of Solaris x86 a few days after the general release of Solaris 10 is pretty funny, too.

Compiling his own versions of the software without even looking at the patches the maintainers of the FreeBSD and OpenBSD ports of MySQL use was really good judgemenet. I mean, heck, they might know something about how to make that software perform properly on their platforms, and we wouldn't want that skewing the results! "After all, I'm testing the operating systems, not their pre-packaged MySQL distributions or source builds."

What... a... loser...

Re:what a lightweight

[justins]

You're completely full of beans as anonymous cowards so often are but even still, I have to thank you, since you actually responded to my message instead of just modding it, as several others have done. (both up and down)

Anyway, those are two totally disparate examples, since Terraserver uses a fancy-schmancy SAN and Google's strategy has been to stuff as many drives as possible into commodity hardware. And it's irrelevant to anything anyhow since if you're benchmarking a database configuration you might as well benchmark one that people use.

Okay, that's not totally fair, I'm sure a lot of people use the article's configuration. I'm sure a lot of MySQL admins have all their stuff on one big drive rather than moving data out across spindles. Because they're so clever and ahead of the curve.

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/

Re:performance

[ShieldW0lf]

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.

Shouldn't you be using a database to do the performance testing then?

it comes with webhosts

[hawk]

I pay something like $4/month for my host. I get one mysql db. If I want a different db, I have to go to a more expensive plan.

hawk

Appropriate Tools for appropriate people

[WillAffleckUW]

seems to me that not everyone needs Oracle, which can cost tens of thousands per CPU, even with the educational or non-commerical discount, and that we have to look at each person's needs individually.

Do we need field-level locking, or will row-level locking suffice?

Do we need distributed servers with failbacks and rollbacks or will a simpler solution with a periodic backup suffice?

Each database system design should match the needs and at least reasonable expectations of growth - One Size does not necessarily fit all.

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

OS X? How about OpenDarwin?

[bshroyer]

There's nothing preventing you from installing OpenDarwin http://www.opendarwin.org/ on x86. This puts you effectively in an OS X environment on x86, only without the Aqua UI.

Re:OS X? How about OpenDarwin?

[ErikZ]

The last OpenDarwin news item was 5 months ago. That can't be good.

Re:OS X? How about OpenDarwin?

[bshroyer]

(+1 Funny)

I agree. I don't know what kind of developer community they've got, or if they just stop development to let Apple catch up... I also don't know why anyone would want to run Darwin on x86. But there it is.

Depends

[Stone316]

The filesystem is an important factor but its just one piece to the puzzle. Bottlenecks could exist anywhere... When talking about disk you have to make sure it is indeed the root cause of the problem.

ie, we had a job that would take 40 minutes to complete and it needed to run hourly.. That only allowed 20 minutes for users to analyze those results. The root cause of the problem was the poorly designed application but we couldn't get management approval to fix it.

A contributing factor to the problem was the server... Even tho we had multiple disks, they were only setup in concat mode. We convinced the unix team to strip the data and our job went from 40 minutes to 20. (If we were able to properly redesign the app, including the disk change, we could probably had most of the application almost real-time...)

In another case a DBA had set their SGA to have a buffer cache of 1 Meg. Server was tuned, app was tuned but not the database.

What i'm trying to say is.. if your experiencing performance problems you have to make sure you find the root cause before you make any recommendations.

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.

Re:Wow am I disappointed in FreeBSD

[chrysalis]

This is why the DragonFlyBSD project does exist.

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

Re:The SMP results are wrong.

[setagllib]

It does, but you might not WANT to have a single process taking up more than one processor because of threads. The change is entirely trivial and not hard to do, but the flexibility allows you to share load on a per-process or close to per-thread basis, depending on this value.

But I agree it'd be saner to have the default be the number of processors, and if I read correctly this is the situation in -current right now.

Why is it out of the question?

[PCM2]

I don't think it would be out of the question at all to compare benchmarks of an Apple machine with a street price comparable to the Intel machine used. After all, if you're going to buy a machine to run MySQL on and you have budget XX, why wouldn't you consider a Mac if it turned out to be faster?

MySQL and data integrity

[Jamesday]

MySQL ships with the InnoDB database engine as a standard part. InnoDB will write to its log with every completed transaction, flushing that to the hardware. If you lose power, it'll reapply the transactions from that log automatically, without hassle, when the database server is restarted. The log writes are sequential so you can get both excellent write caching (into dirty database pages in RAM) and transaction durability (via those immediate flushes of the log to disk). InnoDB is also the database engine which offers transaction support, so if you want the normal transaction guarantees like durability, you're going to be using it anyway. This isn't a theoretical capability - I've had production Wikipedia servers go down with tens of thousaands of dirty database pages in RAM and come back up with all data, while I was asleep.

Alternatively, though it's still in alpha, there's the choice to use the Cluster engine to spread the load over many redundant servers via mirroring in the database storage engine. Very interesting technology for me, though still too early for Wikipedia to use.

For those with high read load or worries about single machine failure, replication is also standard and it's easy to set up a web server or two to do replication in the background to get the last bits of data saved before a disaster hit the master. On the Wikipedia technical to do list is a replicating slave in Paris, so the building falling down on the main site will still leave even very recent updates available.

The combination of InnoDB and replication makes it easy for me to sleep well. The one thing it doesn't yet do as a standard part of the package to complete the picture is automatic failover to the best slave if the master dies and stays dead. I think the failover bit is on the to do list for the 5.n series. Cluster does, though - one of the reasons why I'm watching it with interest.

None of this means PostreSQL is bad. Just different.

Re:MySQL and data integrity

[alan_d_post]

The combination of InnoDB and replication makes it easy for me to sleep well.

Wow, collision of theory and practice can be painful, huh?

Re:FreeBSD still compiles with DEBUG and assertion

[confusion]

I think they only used lc_r on the 4.11 release test. For 5.3 they used the new KSE and the older LinuxThreads.

I did expect KSE to do better against LT, though. KSE has been sold as being lighter weight and faster than LT.

Jerry
http://www.syslog.org/

Re:Wow am I disappointed in FreeBSD

[CryBaby]

Funny, I had the opposite reaction. Considering that he tested optimized Gentoo (I'm assuming it was comiled from source for i686) w/ ReiserFS (isn't it still considered non-standard in Linux?) against binary installations (i.e. compiled for i486) of FreeBSD 4 & 5, I would say that FreeBSD delivered a very strong performance.

Regarding 4 vs. 5: if you consider that many code paths in FreeBSD 5 are considerably more expensive than in 4 due to the fine-grained mutexing, it's impressive that the first truly useable release of 5.x is already keeping up (for the most part) with the simpler, highly uni-processor optimized FreeBSD 4. It was also impressive that KSE delivered roughly the same performance as LinuxThreads, again considering the small amount of time that KSE has been in widespread use.

If you read the FreeBSD developer lists even a little bit, you would find that the focus of work so far has been on laying the foundation for fine-grained SMP and threading to eliminate the scalability cap of FreeBSD 4's fully locked (i.e. non-SMP) kernel. It will take some time before the fruits of that labor result in significantly higher performance (especially in the uni- and dual-processor cases). In some areas (e.g. packet throughput), FreeBSD 5 already trounces FreeBSD 4 and Linux 2.6. In others, the shorter code paths and years of optimization in FreeBSD 4 still can't be beat. This seems quite reasonable to me.

I have to point out just a few of the most significant flaws in these benchmarks in the hope that people will interpret the results appropriately:

1.) The domain socket (i.e. local, non-network) tests were performed using super smack. As the author notes, super smack does not compile out-of-the-box on FreeBSD. That's because it is developed on Linux and (obviously) the author(s) don't test or optimize it for FreeBSD. It's reasonable to assume that it's performance may therefore suffer quite a bit on FreeBSD and no benchmarks were performed to isolate this potential penalty. If you think I'm stretching, consider this: PostgreSQL was (and may still be) primarily developed on FreeBSD. If the development team never tested and optimized it to perform well on Linux, would you consider a PostgreSQL on Linux vs. PostgreSQL on FreeBSD a fair (or even meaningful) benchmark?

2.) While the author fails to clarify whether the sysbench tests were performed remotely or locally, he does mention that he thought sysbench gave meaningful results over the network, so I'm going to go out on a limb and guess that the sysbench results are over a network connection. If the sysbench tests were local, then the same criticism applies as the super smack tests - the benchmarking tool performance and load on each OS must be accounted for so that it may be isolated in the results if they are to have any meaning.

I'm sure you can see where I'm heading but, if any of these benchmarks are remote, we need to know the OS and hardware of the client machine. This is never mentioned. While remote database testing is a good idea in that the benchmark workload is automatically isolated, one must take into account that the benchmarks now reflect network performance between different OS's (assuming the same client machine is used for each test). If careful, informed TCP/IP tuning is not performed on each server to eliminate differences (and the author mentions nothing about this), the OS with the most aggressive default tuning or default tuning that most closely matches the client will certainly show superior results that have nothing to do with MySQL performance. Givens the author's obviously increased familiarity with Linux over the other OS's tested, I would guess that his client machine (if any) was running Gentoo Linux, giving that OS an extreme advantage in network performance. If you're not familiar with FreeBSD, it's default TCP/IP settings are extremely conservative. A good example is the kern.ipc.somaxconn kernel tunable which controls the maximum number of simultaneous TCP connections. It is set to

Re:Wow am I disappointed in FreeBSD

[CryBaby]

You misunderstood most of my points. For instance, you say that the benchmark tools were run locally (although I don't see in the article where the author clarifies that all tests were local) so their workload does not need to be isolated. What if super smack runs 15% slower on FreeBSD because it is not well-written, portable code and/or has had very little testing on FreeBSD? Since super smack is not a real-world client, that 15% performance difference MUST be isolated (are we disagreeing on the meaning of "isolated" or do you get my point?). In fact this post seems to confirm my suspicion of super smack's poor performance on FreeBSD, and even my initial guess at the percentage.

What would you have him test? Some imaginary database that is developed on FreeBSD and which runs fastest on FreeBSD? OK. Meanwhile, the rest of us will try to get some useful work done.

Alright, calm down tough guy. I can't really see how an imaginary database benchmark would help, but niether do the benchmarks we're discussing now becuase of the all the points I raised in my last post. What would be interesting is to see the tests repeated with the client workload properly isolated by alternately running each benchmark on two different client machines. The client machines would ideally have identical hardware but different OS's (say SUSE and FreeBSD 4.11). An attempt would then be made to tune all TCP/IP stacks (clients and servers) for the highest compatibility. That means only enabling common features (e.g. no SACK because NetBSD doesn't have it) and probably disabling window scaling altogether. Since this is not supposed to be a network benchmark, the goal would be to eliminate network performance variations to hide any networking advantages one OS has over another.

While using MySQL for a benchmark is useful because it is a real world app in every sense, the benchmarks would be far more useful if another similar app such as PostgreSQL was used in addition. This would still not provide anything close to a scientific control to eliminate unknown variations, it would at least be one step closer.

I'll close by mentioning that when I moved the busiest MySQL server that I administer (avg ~ 200 queries/sec) from Red Hat Linux 8 to FreeBSD 4 on a Dell 6650, I saw a drop in load average and CPU utilization of about 20% (same MySQL version and config). There was no noticeable speed difference. So FreeBSD ended up providing a meaningful benefit that would never have been reflected in this or similar benchmarks. All benchmarks have their shortcomings and holes, this one just happens to be especially riddled with them.

Re:Wow am I disappointed in FreeBSD

[Billly Gates]

Acutally I agree with you 100% on FreeBSD4.

I still like FreeBSD and I did not intend to start a flamewar.

I am talking about FreeBSD 5.x.

If you put it on your Dell server at work I guarantee you that a decrease in performance would be observed.

All the really good developers left Fbsd and 5.x is a disaster to many old time BSD hackers who either left the project or switched to another version.

Dragonfly looks interesting and many of the former FreeBSD and BSDunix hackers are working on it. I hope it stabilizes soon but that wont happen until more people use it and more software is ported to it.

Test Results

[sourceview]

ARGHH!! This is worse than the cliff hanger cowboy thrillers I saw every Saturday afternoon at the old State Theatre on the East Side of Waterloo, Iowa. I had to wait a week to find out how the hero survived going over the cliff. How long will I have to wait this time???