Slashdot Mirror
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."
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?
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.
and it may even be a mute argument ...
or even more likely, a moot argument
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.
Moneyed corporations, non-working 'poor' and criminal prisoners are turning productive citizens into tax-slaves.
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.
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.
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.
Um, actually Postgres, tho named differently at the time, predates MySQL by a good decade.
- Adam L. Beberg - The Cosm Project - http://www.mithral.com/
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).
Humans are slow, innaccurate, and brilliant; computers are fast, acurrate, and dumb; together they are unbeatable
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."
Postgres supports unicode now.
Fascism trolls keeping me up every night. When I starts a preachin', he HITS ME WITH HIS REICH!
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. You are 100% wrong on that. PostgreSQL user management support is far superior to MySQL in every way.
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
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.
Well, it's good enough for Wikipedia.org so it's good enough for me.
Treehugger? Treehugger... Treehugger!
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.
Sincerely,
Pan Tarhei Hosé, PhD.
"Homo sum et cogito ergo odi profanum vulgus et libido."
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).
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!
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!
Lesbian Nazi Hookers Abducted by UFOs and Forced Into Weight Loss Programs - -all next week on Town Talk.
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.
Where Oracle ... can return simple select queries or complex insert or joined select queries in .5 to 1.0 seconds each
.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.
... I don't need to have nth degree optimized queries.
If Oracle is taking
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"
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.
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=.
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.
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...
In Soviet Washington the swamp drains you.
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...
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/
If you don't need standard SQL support
Firebird implements standard SQL. Firebird supports full SQL92 and most SQL99, according to the project website.