Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Evernote blog: WhySQL?

blog.evernote.com

Post reply on HN
Print view View original

31–40 of 77 posts

Re: Evernote blog: WhySQL?

#31

Post by riffraff »

sheff wrote: post #14
mapleoin wrote: post #4

Does anyone have a link to a decent comparison between MySQL and PostgreSQL? I'm really wondering why so many people use MySQL, even though it supports a lot fewer SQL features than PostgreSQL.

EnterpriseDB (who sell a commercialized version of Postgres) have a couple of MySQL vs Postgres white papers on their site, but hidden behind a registration wall : http://www.enterprisedb.com/resources-community/whitepapers-... . Robert Haas, a Postgres committer , occasionally blogs about comparisons : http://rhaas.blogspot.com/search/label/mysql Theres also http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL Historicall…

postgres is also not (yet, hopefully?) available in amazon's RDS, probably agin due to the replication topic.

Re: Evernote blog: WhySQL?

#32

Post by jcromartie »

huggyface wrote: post #29
jcromartie wrote: post #27

Earlier quoted context omitted.

I don't want to presume that I'm smarter than Craigslist's engineers, but why is this so hard? Schema changes should be captured in migrations, which are programs, and could be coordinated to run in parallel on thousands of machines in a moment with a shell script over SSH.

MySQL had some historic issues with schema changes -- adding a column to a table with a few million records could take hours. While it does have a lot of work to do -- it is restructuring every page of the database -- in competitive products such a change takes a minute fraction of the time (limited only by IO performance).

I see. That makes much more sense. Yes, I suppose I'm used to DB servers that are more nimble in that department.

Re: Evernote blog: WhySQL?

#33

Post by gizzlon »

huggyface wrote: post #29
jcromartie wrote: post #27

Earlier quoted context omitted.

I don't want to presume that I'm smarter than Craigslist's engineers, but why is this so hard? Schema changes should be captured in migrations, which are programs, and could be coordinated to run in parallel on thousands of machines in a moment with a shell script over SSH.

MySQL had some historic issues with schema changes -- adding a column to a table with a few million records could take hours. While it does have a lot of work to do -- it is restructuring every page of the database -- in competitive products such a change takes a minute fraction of the time (limited only by IO performance).

But altering a table would still block it? And possible even other tables as well? And take a long time?

Only ever done this in MySQL, where you really can't alter big, live, tables. Well, facebook made an utility for it: http://www.facebook.com/notes/mysql-at-facebook/online-schem...

Re: Evernote blog: WhySQL?

#34

Post by fkn »

Can anyone explain the following bit: "They’re cleanly partitioned into 20 million data separate data sets, one per user."

Does it mean they have a database per user? That can't be right is it?

Re: Evernote blog: WhySQL?

#35

Post by daleharvey »

fkn wrote: post #34

Can anyone explain the following bit: "They’re cleanly partitioned into 20 million data separate data sets, one per user." Does it mean they have a database per user? That can't be right is it?

Not likely, but they can very easily have a database holding all users with id starting with 'a'

Re: Evernote blog: WhySQL?

#36

Post by moe »

morganpyne wrote: post #11
mapleoin wrote: post #4

Does anyone have a link to a decent comparison between MySQL and PostgreSQL? I'm really wondering why so many people use MySQL, even though it supports a lot fewer SQL features than PostgreSQL.

Simple reliable replication has been a huge differentiator for a long time; enough so to put up with a lot of the other faults of MySQL. Have not revisited Postgres replication in a long time but I have seen that it has been worked on. Anyone with recent experience in both care to explain how the replication of both stacks up in recent versions?

Simple reliable replication

I cringe every time I read that. MySQL replication is many things, but it is not reliable (as anyone who has used it at scale will confirm).

I think the only reason this myth prevails is because hardly anyone ever actually verifies if their master/slave are in sync. A table checksum can be a real eye-opener here, especially on a deployment that's been running for while and undergone schema changes, restarts, network splits, etc.

Re: Evernote blog: WhySQL?

#37

Post by obtu »

morganpyne wrote: post #11
mapleoin wrote: post #4

Does anyone have a link to a decent comparison between MySQL and PostgreSQL? I'm really wondering why so many people use MySQL, even though it supports a lot fewer SQL features than PostgreSQL.

Simple reliable replication has been a huge differentiator for a long time; enough so to put up with a lot of the other faults of MySQL. Have not revisited Postgres replication in a long time but I have seen that it has been worked on. Anyone with recent experience in both care to explain how the replication of both stacks up in recent versions?

Simple, but not reliable. I've seen admins enable statement-based replication without understanding it, and trash the db. Which is generally my gripe with MySQL: it has some popular features that only work if you don't look at them too closely; starting with support for the SQL standard.

PostgreSQL's built-in replication is pretty easy to set up[1] and provides a writable master, and a cascade of slaves. Slaves can be synchronous or asynchronous, and the synchronicity can be turned off per transaction.

[1] http://www.depesz.com/2011/01/24/waiting-for-9-1-pg_baseback...

Re: Evernote blog: WhySQL?

#38

Post by swah »

fkn wrote: post #34

Can anyone explain the following bit: "They’re cleanly partitioned into 20 million data separate data sets, one per user." Does it mean they have a database per user? That can't be right is it?

Their SQL is executed per user, so they only touch around 1/20M the size of the database for any request.

Re: Evernote blog: WhySQL?

#39

Post by herge »

fkn wrote: post #34

Can anyone explain the following bit: "They’re cleanly partitioned into 20 million data separate data sets, one per user." Does it mean they have a database per user? That can't be right is it?

It means that the data from one user has no relations to the data from another user. So most if not all their queries only query the data of one user.

This is really useful for things like sharding, where you can split a database table onto more than one machine, because there will be few queries that will stall fetching data from one machine to another.

Re: Evernote blog: WhySQL?

#40

Post by huggyface »

mapleoin wrote: post #4

Does anyone have a link to a decent comparison between MySQL and PostgreSQL? I'm really wondering why so many people use MySQL, even though it supports a lot fewer SQL features than PostgreSQL.

MySQL was very, very easy to get started with, getting to a cruise quickly. PostgreSQL offered more of a curve.

In the Windows world the same is true of SQL Server -- the setup, connectivity, and basic usage is so incredibly easy that it made it the first choice of many teams.

This seems incredible -- that products are chosen on such an irrelevant-in-the-long-term basis -- however it has proven true across almost all of the computing market, even targeting highly skilled developers. PHP has few competitive merits, yet it was the default option for many because it was so easy to make something basic in.

There's a lesson there in that.

Post reply on HN