4 ms·

Not to bikeshed, but what about Postgres makes the big scalability story any different than the MySQL story, unless you're talking about a commercial distribute

by strlen 13y ago

Not to bikeshed, but what about Postgres makes the big scalability story any different than the MySQL story, unless you're talking about a commercial distributed RDBMS built on top of Postgres like EnterpriseDB? In terms of scalability in the small, many of the MySQL 5.6 replication features skew this comparison in MySQL's direction: multi-threaded (optionally row-based) replication, global transaction ids, and the like make it easier to improve availability, add read-slaves, and host multiple databases/shards on a single machine (replication afaik is no longer per-server, it is now per-database). There's also been work on tuning innodb for SSDs (which is a near certain recommendation for your dataset -- it gives plenty of breathing room to forestall horizontal partitioning). I'd also look very heavily into performance with large buffer caches, compression (great way to reduce IOPS and squeeze the most out of SSD or memory space), etc... I am curious to see if any of these were compared between MySQL and Postgres. As far as I understand, innodb's compression is somewhat more advanced than Postgres, but running either on ZFS is an even better bet for this. On the other hand, Postgres has a better query optimizer, supports more complex data and relation formats, and so on. However, most of these features aren't going to be used at scale (I am presuming you're talking about OLTP workloads). Honestly this is a bit of an unusual decision -- I'd probably start a project with Postgres and avoid using MySQL until later, but choosing Postgres out of scalability reasons seems a bit odd. I'm curious to know why!

gbog 13y ago
Maybe it was not only for scalability, but also for reliability or embracing the Do the Right Thing thing? From my experience with PostgreSQL and MySQL, it is a bit like the difference between python and php, one is a great work of craftmanship that is reliable, predictible, coherent, enjoyable to work with and minimise the wtf/mn rate (which is the best quality measure in software), while the other is a bunch of hacks knit together to make it work asap and its wtf/mn is skyscraping.
- strlen 13y ago
  Those are perfectly fine reasons, but the stated reason had been scalability.
  - brennen 13y ago
    See my reply to rpedela elsewhere in this thread.
- csmuk 13y ago
  Exactly this. To put it bluntly after managing both for years, Postgres doesn't scare the shit out of me like MySQL does. I've had quite a few moments with MySQL doing stupid things that aren't intuitive or right, particularly in the backup/restore space. For example backup one schema and restore onto later versions doesn't always work. Postgres has the same feel as *BSD i.e. deterministic. It is well documented and does exactly what the manual says and is devoid of surprises. It feels engineered and I can provide reliably engineered solutions because of this. Basically I can sleep at night. I really give less of a crap about scalability. I'd throw a bigger box at the problem or use heavy caching. I've built much larger ecommerce solutions with orders of magnitude more hits/orders than sparkfun on much smaller kit
  - Negitivefrags 13y ago
    I have to say, my experience with Postgres is the opposite. Sometimes the query planner will suddenly decide to pick a bad execution plan. I have been woken up in the night multiple times and found that the problem is that Postgres has arbitrarily decided to stop using an index and changed to do a full table scan and the database has ground to a halt under the load. Doing an ANALYZE sometimes fixes it. Increasing the statistics target for the table in addition sometimes fixes it. One time we had to add an extra column to an index to make it decide to use it even though it shouldn't have needed it (and didn't a few hours before!). The developers seem dead set against allowing you to override the query planner to add determinism. I'm not saying MySQL is any better, as I have not used that in production before.
    - csmuk 13y ago
      I genuinely haven't had this problem and we have a fairly large instance. I'll keep my eyes peeled though. Thanks for the heads up.
    - lucian1900 13y ago
      MySQL is much, much worse. It barely even has a query planner in the first place and it will often ignore perfectly good indexes. Query planner changing its mind can be a problem for most DBs, Postgres is not special in this regard.
- jafaku 13y ago
  > while the other is a bunch of hacks knit together to make it work asap and its wtf/mn is skyscraping. You mean hacks like these? http://docs.python.org/2/library/abc.html http://docs.python.org/2/library/abc.html I agree.
- Frencil 13y ago
  This is perhaps one of the more succinct ways of summarizing our core reasons for the switch. There's a lot more to it than that, of course, but in a nutshell we saw the upcoming scaling of our data footprint and complexity and wanted to use (what we evaluated to be) the better tool for the job.
HarrisonFisk 13y ago
Doing compression on the ZFS level is significantly worse than InnoDB compression. InnoDB has a lot of really smart optimizations which make it much better than just zipping things up. Included are the modification log (so you only have to re-compress occasionally) and a dynamic scaling ability to keep compressed pages in memory rather than always decompressing. These optimizations are really only possible with an understanding of the data. I would only consider ZFS for something like an append-only data warehouse type system.
- rpedela 13y ago
  Do you have any references?
  - strlen 13y ago
    This is a good intro for those (like me) who are unfamiliar: https://blogs.oracle.com/mysqlinnodb/entry/innodb_compression_improvements_in_mysql https://blogs.oracle.com/mysqlinnodb/entry/innodb_compressio... I know that a lot of work has been done on InnoDB compression, but I didn't quite grasp the extent, thinking it wasn't far different from Postgres ( http://www.postgresql.org/docs/current/static/storage-toast.html http://www.postgresql.org/docs/current/static/storage-toast.... ).
- mtdewcmu 13y ago
  I'm sure you would want the compression done by the database and not the filesystem, since there are many ways to do compression to fit specific applications, and the database knows what it's trying to do. I read a little bit of the MySQL docs regarding how it uses compression, and it sounded pretty different than general-purpose compression.
jeffdavis 13y ago
I don't know the answer in this case, but a few thoughts: * Why don't you think the optimizer is a consideration? That's a big deal, because it helps the database choose the right algorithms as your data changes. Continuing gracefully while the input data is changing sounds like scalability to me. * Postgres is generally considered to scale to many concurrent connections and many cores. Useful for OLTP. * Sometimes the right feature or extensibility hook can make a huge difference allowing you to do the work in the right place at the right time. For instance, LISTEN/NOTIFY makes it easier to build a caching layer that's properly invalidated -- not trivial to get right if the feature is missing, which might mean that you're not getting as much out of caching as you could be. Tuning for SSDs, compression, buffer caching, etc., are the antithesis of scalability. Those things are pure performance issues. Scalability is about a system that adapts as input data and processing resources change. (Your point about replication does concern scalability, of course.)
- vidarh 13y ago
  > * Postgres is generally considered to scale to many concurrent connections and many cores. Useful for OLTP. But MySQL scales trivially to many servers. Postgres replication is finally getting there, but it's been incredibly slow coming, while setting up huge MySQL installations with a dozen+ replicas, with selective sharding etc. has been effortless for about a decade.
  - damncabbage 13y ago
    ... while setting up huge MySQL installations with a dozen+ replicas ... If you're careful about your function calls, sure. Include a UUID() or SYSDATE() in your INSERT statements, and the replication goes to pieces as the function calls are run separately on each slave (with differing results).
    - morgo 13y ago
      I recommend switching to Row-based Replication, which avoids this problem.
applecore 13y ago
Multi-version concurrency control (MVCC) is more performant and scalable in PostgreSQL than in MySQL with InnoDB (which uses row-level locking).

by strlen 13y ago

gbog 13y ago

Maybe it was not only for scalability, but also for reliability or embracing the Do the Right Thing thing? From my experience with PostgreSQL and MySQL, it is a bit like the difference between python and php, one is a great work of craftmanship that is reliable, predictible, coherent, enjoyable to work with and minimise the wtf/mn rate (which is the best quality measure in software), while the other is a bunch of hacks knit together to make it work asap and its wtf/mn is skyscraping.

strlen 13y ago
Those are perfectly fine reasons, but the stated reason had been scalability.
- brennen 13y ago
  See my reply to rpedela elsewhere in this thread.
csmuk 13y ago
Exactly this. To put it bluntly after managing both for years, Postgres doesn't scare the shit out of me like MySQL does. I've had quite a few moments with MySQL doing stupid things that aren't intuitive or right, particularly in the backup/restore space. For example backup one schema and restore onto later versions doesn't always work. Postgres has the same feel as *BSD i.e. deterministic. It is well documented and does exactly what the manual says and is devoid of surprises. It feels engineered and I can provide reliably engineered solutions because of this. Basically I can sleep at night. I really give less of a crap about scalability. I'd throw a bigger box at the problem or use heavy caching. I've built much larger ecommerce solutions with orders of magnitude more hits/orders than sparkfun on much smaller kit
- Negitivefrags 13y ago
  I have to say, my experience with Postgres is the opposite. Sometimes the query planner will suddenly decide to pick a bad execution plan. I have been woken up in the night multiple times and found that the problem is that Postgres has arbitrarily decided to stop using an index and changed to do a full table scan and the database has ground to a halt under the load. Doing an ANALYZE sometimes fixes it. Increasing the statistics target for the table in addition sometimes fixes it. One time we had to add an extra column to an index to make it decide to use it even though it shouldn't have needed it (and didn't a few hours before!). The developers seem dead set against allowing you to override the query planner to add determinism. I'm not saying MySQL is any better, as I have not used that in production before.
  - csmuk 13y ago
    I genuinely haven't had this problem and we have a fairly large instance. I'll keep my eyes peeled though. Thanks for the heads up.
  - lucian1900 13y ago
    MySQL is much, much worse. It barely even has a query planner in the first place and it will often ignore perfectly good indexes. Query planner changing its mind can be a problem for most DBs, Postgres is not special in this regard.
jafaku 13y ago
> while the other is a bunch of hacks knit together to make it work asap and its wtf/mn is skyscraping. You mean hacks like these? http://docs.python.org/2/library/abc.html http://docs.python.org/2/library/abc.html I agree.
Frencil 13y ago
This is perhaps one of the more succinct ways of summarizing our core reasons for the switch. There's a lot more to it than that, of course, but in a nutshell we saw the upcoming scaling of our data footprint and complexity and wanted to use (what we evaluated to be) the better tool for the job.

HarrisonFisk 13y ago

Doing compression on the ZFS level is significantly worse than InnoDB compression. InnoDB has a lot of really smart optimizations which make it much better than just zipping things up. Included are the modification log (so you only have to re-compress occasionally) and a dynamic scaling ability to keep compressed pages in memory rather than always decompressing. These optimizations are really only possible with an understanding of the data. I would only consider ZFS for something like an append-only data warehouse type system.

rpedela 13y ago
Do you have any references?
- strlen 13y ago
  This is a good intro for those (like me) who are unfamiliar: https://blogs.oracle.com/mysqlinnodb/entry/innodb_compression_improvements_in_mysql https://blogs.oracle.com/mysqlinnodb/entry/innodb_compressio... I know that a lot of work has been done on InnoDB compression, but I didn't quite grasp the extent, thinking it wasn't far different from Postgres ( http://www.postgresql.org/docs/current/static/storage-toast.html http://www.postgresql.org/docs/current/static/storage-toast.... ).
mtdewcmu 13y ago
I'm sure you would want the compression done by the database and not the filesystem, since there are many ways to do compression to fit specific applications, and the database knows what it's trying to do. I read a little bit of the MySQL docs regarding how it uses compression, and it sounded pretty different than general-purpose compression.

jeffdavis 13y ago

I don't know the answer in this case, but a few thoughts: * Why don't you think the optimizer is a consideration? That's a big deal, because it helps the database choose the right algorithms as your data changes. Continuing gracefully while the input data is changing sounds like scalability to me. * Postgres is generally considered to scale to many concurrent connections and many cores. Useful for OLTP. * Sometimes the right feature or extensibility hook can make a huge difference allowing you to do the work in the right place at the right time. For instance, LISTEN/NOTIFY makes it easier to build a caching layer that's properly invalidated -- not trivial to get right if the feature is missing, which might mean that you're not getting as much out of caching as you could be. Tuning for SSDs, compression, buffer caching, etc., are the antithesis of scalability. Those things are pure performance issues. Scalability is about a system that adapts as input data and processing resources change. (Your point about replication does concern scalability, of course.)

vidarh 13y ago
> * Postgres is generally considered to scale to many concurrent connections and many cores. Useful for OLTP. But MySQL scales trivially to many servers. Postgres replication is finally getting there, but it's been incredibly slow coming, while setting up huge MySQL installations with a dozen+ replicas, with selective sharding etc. has been effortless for about a decade.
- damncabbage 13y ago
  ... while setting up huge MySQL installations with a dozen+ replicas ... If you're careful about your function calls, sure. Include a UUID() or SYSDATE() in your INSERT statements, and the replication goes to pieces as the function calls are run separately on each slave (with differing results).
  - morgo 13y ago
    I recommend switching to Row-based Replication, which avoids this problem.

applecore 13y ago

Multi-version concurrency control (MVCC) is more performant and scalable in PostgreSQL than in MySQL with InnoDB (which uses row-level locking).