Comment by HarrisonFisk | Hacker News Reader

Comment by HarrisonFisk | Hacker News Reader

Parent Full thread

(I am a manager of the Data-Perf team at FB which deals with lots of different DB technologies)

MySQL (specifically InnoDB) is extremely efficient as a storage backend compared to PostgreSQL.

There are a few features that make InnoDB better in many cases:

1. Change buffering for IO bound workloads: If you are IO bound, then InnoDB change buffering is a huge, huge win. It basically is able to reduce IO required for secondary index maintenance by a huge amount.

http://dev.mysql.com/doc/innodb/1.1/en/innodb-performance-ch...

2. InnoDB compression: When you are space constrained (say using flash storage), then being able to compress your data is a big win. In our case, it reduces space by around 40% which translates directly to 40% less servers required. While you could do something like run PG on ZFS with compression, for an OLTP workload, you want the compression in the DB so that it can do a lot of work to minimize the compressing and decompressing of data.

https://dev.mysql.com/doc/refman/5.6/en/innodb-compression-i...

3. Clustered index: The InnoDB PK is a clustered index. This makes a lot of query patterns (such as range scans of the PK) very cheap. Combined with covering indexes (which PG now has too!), you can really minimize the IO required by properly tuned queries.

There are a variety of smaller things as well, such as InnoDB doing logical writes to the redo log vs. PG doing full page writes, so on very high write systems, the REDO log bytes written will be dramatically less. Also MySQL replication has traditionally been more flexible than PG, but PG has made some great strides recently, so I don't know if I would maintain that position still.

Your smaller points don't make much sense to me... PG doesn't have a REDO log, that's just not the way it's architected. If you mean WAL, a patch went out in 9.4 to prevent updates to a page from rewriting more than necessary, so they're not doing full-page writes each time. Clustered index also makes inserts slower--it's again not a straight win for MySQL (and what query patterns other than range scans of the PK does it make cheaper?). Finally, while obviously it's not the same as InnoDB compression, TOAST does a rather good job in practice, and Postgres's indexes are quite efficiently compressed (especially with new changes in 9.4). I can't speak to (1), but it's not at all clear to me that any of these advantages put MySQL ahead in the long term, and certainly not for all workloads.

Right, PG calls the REDO log the WAL (for most purposes they are the same thing). I did not know that 9.4 can do partial page writes to the WAL now. Guess I will have more reading to do, thanks for pointing it out! A nice blog post by a colleague recently showing how large writes to redo logs matter is (not about PG, but why it is significant in the context of size of entries):

http://smalldatum.blogspot.com/2014/03/redo-logs-in-mongodb-...

As far as when clustering a table is useful, see the CLUSTER command in PG. It is roughly the same places you would want to do it, except it is automatically maintained. You do need to realize what is going on to minimize impact on inserts, but in a lot of cases, data in inserted in generally ascending order so it mostly 'free'. This does make GUIDs really bad for PKs in InnoDB.

Clustered indexes are like covering indexes (which PG got recently). You don't quite realize how useful they are until you get access to it ;)

InnoDB compression for us is primarily for non-lob objects, so TOAST is quite a bit different than the cross-row compression that we get. We will normally do compression of large objects outside of the DB whenever possible.

I'm not saying that InnoDB is always better than PG, but in a lot of cases that I have tested it with, it is indeed better. PG has come a long way recently, including options such as covering indexes to close the gap.

Thanks a lot for the detailed reply, this is why I love HN!

1-2 years back I saw a benchmark stating that PG works better than MySQL on I/O constrained AWS instances, so I didn't expect this ("I've learned something today").

I think Postgres is a better default choice because you are likely to be safer and for it to behave properly with your data. In certain cases, apparently Mysql might be faster for certain workloads, and with certain tradeoffs. That's the kind of informed decision you can make once you're scaling to that size.

Thanks. Presumably these optimizations could be added to postgres as well, no?

Since work is/will have to be done to the chosen db, it would be best for everyone if the one chosen was of high integrity.

There is nothing that prevents these from being implemented for PostgreSQL. In fact, they have already done a lot of optimizations that close the gap, such as covering indexes. Before covering indexes, InnoDB was even further ahead of them.

Of the ones mentioned, I would guess compression is the most complex. For InnoDB this took many years to get the point of it being usable and efficient. Many naive implementations can cause huge overheads for CPU which makes it unusable.

Reply on news.ycombinator.com