Most of the MySQL posts here are actually about clever ways to do things most DBAs and developers have taken for granted for years. Do you wonder why no-one seems to be writing blog posts about "sharding" Sybase or DB2 or Oracle or Postgres...?
MySQL is popular with startups though, so it's not surprising that there's a lot written about it.
It's true that MySQL has some lame limitations, but I don't believe that there are any silver bullets out there. Google tried to switch their ads system from MySQL to a "real" database once, and it was basically a disaster and had to be abandoned in favor of MySQL (I wasn't working on it, so I can't really give all the details).
Another problem we had with MySQL that Bret didn't mention was that it would try to be "smart", and sometimes it would "randomly" (from our perspective) choose a very inefficient strategy, and we would have to waste a lot of time figuring out what it was doing and how to force it to do the right thing. The approach Bret describes basically avoid any MySQL "smarts" and treats it as a dumb, but fast and well tested B-tree. This gives us fairly reliable and predictable performance characteristics because we know exactly what it's doing (mostly).
No, there are no silver bullets, but it seems to me that people reach for Mysql a bit too quickly, without considering the pros and cons. And while it's improving, Mysql has had many frustrating things in the past... to me it's always seemed like a "worse is better" kind of thing. Sure, it's "fast", but at what cost? Once you go to InnoDB, you lose that speed advantage.
One thing that's not a tech tradeoff, and is generally a Postgres win, is the BSD style licensing. You can take Postgres and do whatever you want with it with no worries.
> Google tried to switch their ads system from MySQL to a "real" database once,
I hope the part that actually handles money has been fiddled with by Google to be robust.
Actually, Postgres doesn't have any great out-of-the-box solution for partitioning the database across machines. The usual suggestion is Slony, but that is no where near as robust and widely deployed as MySQL replication. The GPL licence for MySQL isn't really a problem for webapps anyway.
OTOH, Postgres does somewhat better than MySQL on a single box with multiple cores (it's fairly linear up to 8 CPU, which is much better than MySQL) - mainly because of the work Sun put into scaling it before they bought MySQL. (At least - that's according to some people from Sun who do a lot of performance work with both databases)
It's not as robust? Do you have a source for that? You're doubtless right about it not being as widely used.
I've always preferred the fact that Postgres tried to do things correctly. Most recently, I bumped into this with Mysql, and it reminded me why I get irritated when I use it:
Postgres does somewhat better than MySQL on a single box with multiple cores ... mainly because of the work Sun put into scaling it before they bought MySQL.
That's not really the case. Sun has some some benchmarking, but most of the work on Postgres SMP performance was done by others (mostly Tom Lane, who works for Red Hat, and various EnterpriseDB employees).
How do hash joins help with sharding? Surely once you go above a certain number of writes per second you're going to need to have more than one writable database server, at which point you need to start partitioning and better join algorithms aren't going to do anything to help.
If you can't hash join then you can't join over large datasets anyway, so sharding costs you nothing in that respect.
Right now, using off the shelf kit and doing nothing particularly clever, running a major commercial RDBMS you could do 10,000 commits/sec and handle 100T of data on a single instance. Sure it would cost you a pretty penny, but the thing is, unless running a database is the one competitive advantage your company has, you're better off keeping your people focussed on the thing that does make you money.
I'm not too sure if you are arguing for or against the commercial RDBMS.
To me, that sounds like a pretty good argument against it - the great thing about MySQL/Postgres+Sharding is that it scales down, as well as up. You can start out with a single server, then gradually add in extra servers as you need them. With the "single big DB server model" it doesn't work like that - you have to make a pretty decent investment early on in the software licence, build your software to use it, and then the pricing isn't linear, either.
Plus, the backup/redundancy thing sucks too - with sharded MySQL you need a couple of spare cheap servers, but with Oracle etc you need to pay twice for the licence and a second server.
OTOH, in a corporate environment it's much easier to predict your usage and the pricing is easier to justify. Also your priority is safety+justifiability first rather than price or even price/performance.
Comments
Pretty much it's MySQL only.
Most of the MySQL posts here are actually about clever ways to do things most DBAs and developers have taken for granted for years. Do you wonder why no-one seems to be writing blog posts about "sharding" Sybase or DB2 or Oracle or Postgres...?
Actually, they do, e.g. http://highscalability.com/skype-plans-postgresql-scale-1-bi...
MySQL is popular with startups though, so it's not surprising that there's a lot written about it.
It's true that MySQL has some lame limitations, but I don't believe that there are any silver bullets out there. Google tried to switch their ads system from MySQL to a "real" database once, and it was basically a disaster and had to be abandoned in favor of MySQL (I wasn't working on it, so I can't really give all the details).
Another problem we had with MySQL that Bret didn't mention was that it would try to be "smart", and sometimes it would "randomly" (from our perspective) choose a very inefficient strategy, and we would have to waste a lot of time figuring out what it was doing and how to force it to do the right thing. The approach Bret describes basically avoid any MySQL "smarts" and treats it as a dumb, but fast and well tested B-tree. This gives us fairly reliable and predictable performance characteristics because we know exactly what it's doing (mostly).
No, there are no silver bullets, but it seems to me that people reach for Mysql a bit too quickly, without considering the pros and cons. And while it's improving, Mysql has had many frustrating things in the past... to me it's always seemed like a "worse is better" kind of thing. Sure, it's "fast", but at what cost? Once you go to InnoDB, you lose that speed advantage.
One thing that's not a tech tradeoff, and is generally a Postgres win, is the BSD style licensing. You can take Postgres and do whatever you want with it with no worries.
> Google tried to switch their ads system from MySQL to a "real" database once,
I hope the part that actually handles money has been fiddled with by Google to be robust.
Actually, Postgres doesn't have any great out-of-the-box solution for partitioning the database across machines. The usual suggestion is Slony, but that is no where near as robust and widely deployed as MySQL replication. The GPL licence for MySQL isn't really a problem for webapps anyway.
OTOH, Postgres does somewhat better than MySQL on a single box with multiple cores (it's fairly linear up to 8 CPU, which is much better than MySQL) - mainly because of the work Sun put into scaling it before they bought MySQL. (At least - that's according to some people from Sun who do a lot of performance work with both databases)
It's not as robust? Do you have a source for that? You're doubtless right about it not being as widely used.
I've always preferred the fact that Postgres tried to do things correctly. Most recently, I bumped into this with Mysql, and it reminded me why I get irritated when I use it:
http://journal.dedasys.com/2008/11/11/another-mysql-doesnt-d...
I've often bumped into things like that that just irk me.
Postgres does somewhat better than MySQL on a single box with multiple cores ... mainly because of the work Sun put into scaling it before they bought MySQL.
That's not really the case. Sun has some some benchmarking, but most of the work on Postgres SMP performance was done by others (mostly Tom Lane, who works for Red Hat, and various EnterpriseDB employees).
Excuse my ignorance, but what's the Postgres solution to sharding?
It's to write efficient hash joins, partitioning and row versioning into your core database engine.
How do hash joins help with sharding? Surely once you go above a certain number of writes per second you're going to need to have more than one writable database server, at which point you need to start partitioning and better join algorithms aren't going to do anything to help.
If you can't hash join then you can't join over large datasets anyway, so sharding costs you nothing in that respect.
Right now, using off the shelf kit and doing nothing particularly clever, running a major commercial RDBMS you could do 10,000 commits/sec and handle 100T of data on a single instance. Sure it would cost you a pretty penny, but the thing is, unless running a database is the one competitive advantage your company has, you're better off keeping your people focussed on the thing that does make you money.
I'm not too sure if you are arguing for or against the commercial RDBMS.
To me, that sounds like a pretty good argument against it - the great thing about MySQL/Postgres+Sharding is that it scales down, as well as up. You can start out with a single server, then gradually add in extra servers as you need them. With the "single big DB server model" it doesn't work like that - you have to make a pretty decent investment early on in the software licence, build your software to use it, and then the pricing isn't linear, either.
Plus, the backup/redundancy thing sucks too - with sharded MySQL you need a couple of spare cheap servers, but with Oracle etc you need to pay twice for the licence and a second server.
OTOH, in a corporate environment it's much easier to predict your usage and the pricing is easier to justify. Also your priority is safety+justifiability first rather than price or even price/performance.
Which commercial database? Any idea which web scale applications are using it, and why the other ones aren't?
MySpace runs on Microsoft SQL Server.
http://blog.de-hao.com/2007/11/17/myspacecom-running-a-megas...
Yes, and it's partitioned out the Wazzooo.