5 ms·

I have this question about a lot of sites. I've heard very few good things about MySQL and lots about PostgreSQL but MySQL seems to be used more and get more

by amock 16y ago

I have this question about a lot of sites. I've heard very few good things about MySQL and lots about PostgreSQL but MySQL seems to be used more and get more press.

bad_user 16y ago
It's just inertia ... I am familiar and worked with both ... but I still chose Mysql over Postgres simply because I have more confidence that I can control it. That's partly because Mysql always was more user friendly, and I also attended one Mysql conference and got to know some people working on Mysql. That said, overall Postgres is better, with one exception ... Mysql supports multiple storage engines for various needs. In Postgres you don't have that flexibility.
- vsync 16y ago
  > That's partly because Mysql always was more user friendly Examples? I started with Postgres and ignored MySQL from day one because of MySQL's deficiencies, and have had 0 problems learning what I needed in the years I've used it. Not to mention I can read a general SQL reference, or the standard, and the information actually applies. Unlike MySQL (quotation marks anyone?). > That said, overall Postgres is better, with one exception ... Mysql supports multiple storage engines for various needs. In Postgres you don't have that flexibility. "various needs": what, for those times when you don't care whether your data gets silently corrupted?
  - ericd 16y ago
    There are a decent number of things for which I don't care if the data gets corrupted a bit over the long run. Performance is a much bigger consideration.
    - rbranson 16y ago
      PostgreSQL has better performance on real-world, high-concurrency loads over multiple cores. MyISAM is slow on tables with lots of writers (table level locking). InnoDB is better, but PostgreSQL is even better. Supposedly MySQL 5.4 is going to fix this. Move into big queries with lots of joins, subqueries, using multiple indexes, and PostgreSQL starts to shine even more. You can even turn on async commit (since 8.3), which sends a commit acknowledgement message back to the client before the transaction is fully committed, allowing sync clients to move forward. While this does allow a very small window of time where, if the power was pulled on a machine, you'd lose just that bit of data for the tenth of a second it wasn't written to disk. All the other data is safe though, unlike MyISAM's default fsync=off, which gives it all of it's "performance." PostgreSQL also has async network I/O support baked into the libpq driver, which, if the client is written properly, makes this unnecessary. Note that async commit can be set per-connection and toggled on and off. Virtually all of the 7.x releases were focused on durability, and 8.x releases were focused on performance, culminating with 9.0, due out soon, which will bring the much-needed readable slaves. This is the final feature that will tip the scalability gap in PostgreSQL's favor. There are is a situation where MySQL shines: read-only MyISAM tables with simple queries where joins are not used. Virtually all of the write concurrency and data integrity features as well as PostgreSQL's performance using multiple indexes and joins become unimportant at this point. MyISAM also supports compressed read-only tables, which are great for this situation. I'd have to ask myself why I wasn't using a simpler NoSQL solution at this point though.
      - ericd 16y ago
        Yeah, MySQL could definitely be improved upon. The table-level write locking is a pain. I was just saying that 100% consistency/data integrity isn't really a major concern for a lot of applications. I've thought about switching to Postgres, but it doesn't currently appear to be worth the pain in my case.
        bad_user 16y ago
        Locking in Innodb is row-level.
        ericd 16y ago
        Yeah, I know, our conversation about read performance at the cost of data integrity being OK was about MyISAM (which doesn't have great concurrent write performance due to the table-level locking).
        bad_user 16y ago
        Locking in Innodb is row-level.
  - Shorel 16y ago
    Marketing driven data. It starts already corrupted.
  - bad_user 16y ago
    > "various needs": what, for those times when you don't care whether your data gets silently corrupted? I never had data-corruption in Mysql. Of course, I'm always working with InnoDB, maybe MyIsam is weaker And if Mysql would have been such a weak RDBMS, maybe it wouldn't have been used by companies like Google or Amazon (especially Amazon RDS, which are already configured Mysql instances offered to clients in Amazon's cloud). > "various needs": what, for those times when you don't care whether your data gets silently corrupted? Ebay is using the memory tables engine in Mysql instead of Memcached. According to their benchmarks, the Mysql client is more stable, and the combination is more scalable. There's also a whole ecosystem of third party storage engines. Like when Adobe developed an in-house distributed storage ... they also had an (internal project) storage engine for Mysql that interacted with it. Also, the soon to be replacement for InnoDB is Falcon ... http://forge.mysql.com/wiki/Falcon http://forge.mysql.com/wiki/Falcon (optimized for multiple cores). Or RethinkDB (YC startup) ... a storage engine optimized for solid-state drives ... http://rethinkdb.com/ http://rethinkdb.com/ And I don't really care about what others think ... Mysql served me well.
- jonhohle 16y ago
  For any data you want to reliably read back, MySQL really has one (standard) engine†. Nearly every MyISAM DB I've used in a high traffic environment has eventually corrupted. Your choice is really either InnoDB or ‘repair table’. † NDB may be a perfectly reliable option, I have not used it.

by amock 16y ago

I have this question about a lot of sites. I've heard very few good things about MySQL and lots about PostgreSQL but MySQL seems to be used more and get more press.

bad_user 16y ago

It's just inertia ... I am familiar and worked with both ... but I still chose Mysql over Postgres simply because I have more confidence that I can control it. That's partly because Mysql always was more user friendly, and I also attended one Mysql conference and got to know some people working on Mysql. That said, overall Postgres is better, with one exception ... Mysql supports multiple storage engines for various needs. In Postgres you don't have that flexibility.

vsync 16y ago
> That's partly because Mysql always was more user friendly Examples? I started with Postgres and ignored MySQL from day one because of MySQL's deficiencies, and have had 0 problems learning what I needed in the years I've used it. Not to mention I can read a general SQL reference, or the standard, and the information actually applies. Unlike MySQL (quotation marks anyone?). > That said, overall Postgres is better, with one exception ... Mysql supports multiple storage engines for various needs. In Postgres you don't have that flexibility. "various needs": what, for those times when you don't care whether your data gets silently corrupted?
- ericd 16y ago
  There are a decent number of things for which I don't care if the data gets corrupted a bit over the long run. Performance is a much bigger consideration.
  - rbranson 16y ago
    PostgreSQL has better performance on real-world, high-concurrency loads over multiple cores. MyISAM is slow on tables with lots of writers (table level locking). InnoDB is better, but PostgreSQL is even better. Supposedly MySQL 5.4 is going to fix this. Move into big queries with lots of joins, subqueries, using multiple indexes, and PostgreSQL starts to shine even more. You can even turn on async commit (since 8.3), which sends a commit acknowledgement message back to the client before the transaction is fully committed, allowing sync clients to move forward. While this does allow a very small window of time where, if the power was pulled on a machine, you'd lose just that bit of data for the tenth of a second it wasn't written to disk. All the other data is safe though, unlike MyISAM's default fsync=off, which gives it all of it's "performance." PostgreSQL also has async network I/O support baked into the libpq driver, which, if the client is written properly, makes this unnecessary. Note that async commit can be set per-connection and toggled on and off. Virtually all of the 7.x releases were focused on durability, and 8.x releases were focused on performance, culminating with 9.0, due out soon, which will bring the much-needed readable slaves. This is the final feature that will tip the scalability gap in PostgreSQL's favor. There are is a situation where MySQL shines: read-only MyISAM tables with simple queries where joins are not used. Virtually all of the write concurrency and data integrity features as well as PostgreSQL's performance using multiple indexes and joins become unimportant at this point. MyISAM also supports compressed read-only tables, which are great for this situation. I'd have to ask myself why I wasn't using a simpler NoSQL solution at this point though.
    - ericd 16y ago
      Yeah, MySQL could definitely be improved upon. The table-level write locking is a pain. I was just saying that 100% consistency/data integrity isn't really a major concern for a lot of applications. I've thought about switching to Postgres, but it doesn't currently appear to be worth the pain in my case.
      - bad_user 16y ago
        Locking in Innodb is row-level.
        ericd 16y ago
        Yeah, I know, our conversation about read performance at the cost of data integrity being OK was about MyISAM (which doesn't have great concurrent write performance due to the table-level locking).
      - bad_user 16y ago
        Locking in Innodb is row-level.
- Shorel 16y ago
  Marketing driven data. It starts already corrupted.
- bad_user 16y ago
  > "various needs": what, for those times when you don't care whether your data gets silently corrupted? I never had data-corruption in Mysql. Of course, I'm always working with InnoDB, maybe MyIsam is weaker And if Mysql would have been such a weak RDBMS, maybe it wouldn't have been used by companies like Google or Amazon (especially Amazon RDS, which are already configured Mysql instances offered to clients in Amazon's cloud). > "various needs": what, for those times when you don't care whether your data gets silently corrupted? Ebay is using the memory tables engine in Mysql instead of Memcached. According to their benchmarks, the Mysql client is more stable, and the combination is more scalable. There's also a whole ecosystem of third party storage engines. Like when Adobe developed an in-house distributed storage ... they also had an (internal project) storage engine for Mysql that interacted with it. Also, the soon to be replacement for InnoDB is Falcon ... http://forge.mysql.com/wiki/Falcon http://forge.mysql.com/wiki/Falcon (optimized for multiple cores). Or RethinkDB (YC startup) ... a storage engine optimized for solid-state drives ... http://rethinkdb.com/ http://rethinkdb.com/ And I don't really care about what others think ... Mysql served me well.
jonhohle 16y ago
For any data you want to reliably read back, MySQL really has one (standard) engine†. Nearly every MyISAM DB I've used in a high traffic environment has eventually corrupted. Your choice is really either InnoDB or ‘repair table’. † NDB may be a perfectly reliable option, I have not used it.