Slashdot Mirror
MySQL Beats Commercial Databases in Labs Test
An anonymous reader writes "Many of the big players now offer free or 'light' versions of their databases, some would call them crippleware. Builder AU compared databases from Oracle, IBM, Microsoft and MySQL, and the open source offering came out on top."
And what about PostgreSQL? It should fare very well.
Indeed. In fact, tests in the past showed postgres was a better choice over mysql. But don't take my word, compare it yourself with those in the article...
http://www.postgresql.org/
Actually, it's quite true, and if you look at what's actually going on behind the scenes inside the database, it makes sense.
:)
If there's nothing going on in the database, then the 'summary' value that MySQL keeps is probably spot on accurate. But, if there's lots of simultaneous inserts and deletes, then it's really going to be very approximate. Until things are all flushed, the summary may include all the inserts and none of the deletes, or vice versa. If you wanted to make the summary information accurate, then you'd have to establish locks and the like around that summary value, and THAT will slow the database down. As it stands, inserts and deletes can be executed with ZERO regard to each other.
Postgresql has a similar problem, except instead of offering a summary value and informed that it's an estimate, whenever you do a count(*) it actually scans the entire table file looking for 'valid' rows. Ie, count(*) is not instantaneous. I think they were going to address this issue in a later release (or perhaps it's in 8.1 already), but it's NOT a simple thing. However, if you wanted instant answers in Postgresql NOW, you can do it by setting up a trigger on insert and delete, and maintaining your own summaries. This is a performance hit, of course... but you'd get the same, or a similar hit, if the database was maintaining for you.
What the 'big guys' do, I don't know. But... don't knock MySQL for doing something weird
Postgresql, for comparison, will give you an 'accurate' value, but it actually has to create rows: it can't rely on summary information.
Both you and the parent aren't reading what they wrote. SELECT COUNT (*) is accurate, it was SHOW TABLE STATUS that gives the estimate (as it should, IMO).
I'm not sure what Oracle version that's for. Oracle DB Stadnard is $15k per processor. Enterprise is $40,000 per CPU. There is also a Standard One edition that's "only" $5k per CPU. In addition to the in-beta Oracle XE(free). On the bright side, pretty much all of Oracle's stuff is free for development use.
Every time you post an article on Slashdot, I kill a server. Think of the servers!
Parent is quite right. Count(*) is expensive, especially on big tables, so it's handy to have a quicker way that's not as accurate. MSSQL has it, too: ...basically, you're querying the table that the query optimizer uses, asking for how many rows it thinks there are in the primary key.
select rows from sysindexes where [id]=object_id('tablename') and indid2
Nothing wrong with that not being accurate. It's much faster than counting every row, and if all you care about is whether there are less than 1,000,000 rows or something, it's fine. If you care about exact numbers, use count(*), which IS accurate on MySQL.
-b
If I wanted a sig I would have filled in that stupid box.
I just finished reading it, and the didn't run any performance tests (which I would have liked to have seen). They were comparing options, features, prices, etc. The only mention of performance was things like "here are some neat tunable options that affect performance." and "but performance won't scale that well because you are limited to one CPU" (not direct quotes). This article is an overview, not a "lab test". I'm not sure where that came from. The original article says they were "tested" also. They installed all the products and poked around, but no performance numbers were presented at all.
Comment forecast: Bits of genius surrounded by a sea of mediocrity.
To be fair to MySQL, that's only for tables of type InnoDB. MyISAM and other storage engines do return an accurate count. From here:
It should be noted, though, that you have to use InnoDB tables for all those "modern" database features like transactional support* and foreign key constraints.
It may be a bit of a bother, but it's not that hard to create the "counter table" for whatever it is you need to count. All the major DBs have something that's a pain in the ass...at least with MySQL you didn't have to pay for the pain.
*BDB and NDB Cluster are apparently transaction safe as well, but I have no experience with them; and for whatever reason, they don't seem to be popularly used.
@ASP.NET's parent-teacher meeting: "Little Johnny.NET is very bright, but he doesn't play well with others."
This is THE most retarded review of modern database systems that I've ever read. From the moment I read the overview of MSSQL Express, I knew what the writer's opinion was going to be, and that was completely tilted in MySQL's favor. The basic descriptions of product feature were in most cases wrong. One would get the impression from this article that a major RDBMS would always allow dirty reads. And while it's true that you CAN do that, it is not the default behavior for any of them. It has to be explicity done and you have to go out of your way in your SQL code to make that happen.
It's things like that where you just ultimately conclude that the writer(s) of this article just does not know what the hell he's talking about and doesn't have a basic understanding of the concepts or products under review. It's just more OSS nonsensical propaganda in my opinion. And don't fool yourselves into thinking that an article like this is going to change any IT manager's mind about what DBMS he's going to deploy in his enterprise.
Actually, even the crippled versions of DB2, Oracle, and MSSQL still have the underpinings for advanced features that MySQL doesn't support. From real replication to actual performance monitoring (all three of the big guys provide detailed hooks into the guts of the DB) to support for multiple filegroups and indexes and databases spread across filegroups, the big DB's have features that are important but impact performance.
SHOOT!! you want to see MySQL get its bum kicked on performance? Run a test on a filesystem against MySQL.
Comparing performance among databases is only meaningful if all of the candidates have the features of which you need. MySQL has come a long way, and I use it in production every day, but this is kind of a silly comparison. The free versions of the big DB's are meant to provide an easy migration path to more feature-complete versions; if you use Sequel Server Express and want to upgrade to something that that supports clustering and log shipping, you may your money and get your features. With MySQL, if you outgrow it, you either need to start writing code, migrating to something else, or sitting on your hands waiting for it to get there.
Recap, for those who won't RTFC and want to slag me: I like MySQL. I use it for mission critical purposes in production environments. However, comparing a simpler product's performance to (crippled versions of) more robust products is silly.
Cheers
-AC
This is utterly false.
MSDE is based off SQL Server 2000, which itself a revision to SQL Server 7. MS Access has NOTHING to do with SQL Server (excpet proving nice single DB front ends via ADPs). When your dishonest (or just stupid) so early in a article, you loose your reader.
IIRC, EULAs are considered void in Australia because it's a contract occuring after the monetary transaction. After you paid, there is no way additional conditions can be added.
That's how it should be everywhere.
Slashdot anagrams to "Sad Sloth"
Lab test? What test? This was a list of features from the product documentation. What a disingenuous title.
Slashdot - where whining about luck is the new way to make the world you want.
If you want to distribute MySQL with your application to a customer, you have to pay a license fee. That means that for many people, MS SQL Express may be better.
If I wanted to do some complex database logic, I'd probably consider MS SQL Express, as stored procedures on MySQL haven't been out there for long.
If you are building a database to go on low-cost LAMP hosting, MySQL does the job well.
For a piece of shareware requiring a small database, something like SQLLite is probably better than these options.
And that is fixed year ago...
that this is a terrible review, there really isn't much option for the average site.
Have you checked the licenses on Oracle for instance. If I remember correctly, the commercial license prevents publications of benchmarks without approval from Oracle.
Having said that, if *I* were supreme overload of database comparisons, here's what I would do:
- Decide on a reference hardware platform in both 32 and 64 bit. I would also include a non-x86_64 hardware platform such as pSeries. Of course this will limit the SQL Server tests but that's Microsoft's own choice.
- Also decide on a common disk layout for the databases. Many commercial databases and even PostgreSQL will perform poorly out of the box on a flat disk layout. Seperate index, data and logs on unique volumes. If you decide to go RAID5 for any LUN, stick at least 6 disks under that LUN. RAID1 for log files. You also need to decide on which filesystem you want to use. This all of course determines which OS you use. I'm assuming Linux in this scenario. Most PostgreSQL recommendations I've seen recommend XFS on RAID10 but RHEL and SUSE don't include XFS support without going unsupported with the vendor in a kernel recompile.
- Bring in a skilled DBA for each product. It shouldn't be too hard to find someone who wants to get published in his respective product.
- Provide no OS tuning except the defaults recommended by the manufacturer of the database. OS tuning varies from vendor to vendor. Some suggest SHMMAX to be one setting while others suggest another number. You can't compare apples to apples when you've tuned I/O at 64k blocks for DB2 and 128K for Oracle (not that you would for either).
- Test all workloads. You may notice that some vendors provide a different product configuration for DSS, OLTP and OLAP. Some vendors even provide a different version of the product for a specific workload.
- Use the same DDL where possible. Really think about this for a moment. Alot of tests I've seen determine raw select, raw insert and raw update speeds but don't take into account the complex DDL that most business have. Take our layout for instance:
1) We have an OLTP system.
2) It also has a schema for OLAP that is populated by triggers from the OLTP tables.
3) We load our warehouse off of the denormalized tables and also provide the OLAP functions within our application from those tables. (Our warehouse is updated each morning but we have a requirement in the application for realtime data for the current business day)
Now with those above requirements, INSERT and UPDATE are going to perform much slower than what a raw benchmark would tell me and IMHO is much more indicative of real world design.
- Note which "levers" you have available to pull. With DB2, I can put specific tables on different LUNs via tablespaces. I can also assign tables and indexes to different bufferpools. Quite honestly, I can't do any of that with MySQL (well with InnoDB I can via some symlink madness). I can accomplish the tablespaces option with PostgreSQL but not the unique bufferpools for certain tablespaces or indexspaces.
- Also note what maintenance is required to actually keep the database performing. REORGs in DB2. VACs in PGSQL. I can update and insert 10mil rows to DB2/MYSQL/PGSQL but what happens when I need to go back and select out those rows? This leads to the next test:
- Test the optimizer! This is probably the biggest thing for me. How does the optimizer determine which access path to take? What factors influence that? I would not intentionally write shitty SQL but developers aren't DBAs. They don't normally concern themselves with the BEST path or even the quickest path to the data as long as they get the data they need. Don't talk to me about OR functions or LEFT OUTER JOINs that I've seen spit out by ORM products or worse yet SELECT * and doing the logic in the application. Run EXPLAIN plans on all queries you're testing. In the end, the optimizer is the biggest factor in the database per
"Fighting the underpants gnomes since 1998!" "Bruce Schneier knows the state of schroedinger's cat"
And I'm an avowed MySQL hater. I think it's a shitty hack of a database and there is no problem domain where there isn't at least one other product is a better solution. But this "no support" line is almost as wrong as it is stupid.
By the way, the kind of support you're claiming you need (24/7 on call support with access to someone who can provide a patch for a new problem) will be enormously expensive, where it's even available - there's no support plan Microsoft provides that makes those sort of promises, for example. You might get a patch in a half hour if it's a known problem and they have one ready for it, but you'll probably spend more than that half hour just convincing the rep that you have that specific problem. IBM or some of the other more service oriented companies may provide that kind of support. It's going to be expensive though - expensive enough that if you're worried about how much it's going to cost, it's probaby more than your company is worth.