5 ms·

At my current gig, we've been burned pretty badly by scalability problems with MySQL, and the prevailing wisdom is that new functionality and services that have

by xmodem 6y ago

At my current gig, we've been burned pretty badly by scalability problems with MySQL, and the prevailing wisdom is that new functionality and services that have to deal with data at scale should be built on Cassandra. My cries of "we wouldn't have this problem with Postgres" have mostly fallen on death ears.

pizza234 6y ago
Can you elaborate on the specifics of the scalability problems, and how the different databases do differently on them? It's an interesting subject. We're having scalability problems with our RDBMS, but it's something RDBMSs in general don't solve.
- xmodem 6y ago
  We have a few large (not large in the scheme of things, but large for MySQL) tables that we can't add columns to without locking the table for an extended period. In Postgres this operation is instant no matter the size of the table. This not the only problem, but it is the most pernicious
  - Thaxll 6y ago
    MySQL 8 aleviate that problem, although it's not perfect it's better than before: https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-in...
  - pizza234 6y ago
    I confirm the sibling post that adding a column is instaneous on MySQL 8 (although there are limitation), and point to the reference document: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op.... Outside the context of db comparisons, and in relation to the specific case, if you don't have triggers/foreign keys on a given MySQL table, Gh-ost¹ solves the DDL locking issues. ¹=https://github.com/github/gh-ost https://github.com/github/gh-ost
    - tomnipotent 6y ago
      > adding a column is instaneous on MySQL 8 Has been since MySQL 5.6 (released Feb `13).
      - zten 6y ago
        In the case that you're using MySQL 5.6-compatible Aurora RDS, you need to enable a special flag to get lock-free nullable column additions.
      - pizza234 6y ago
        Are you sure? Based on the official blog post¹, the INSTANT algorithm is new to MySQL 8.0. The manual of MySQL 5.6 indeed doesn't show any "Instant" option in the list of column operations². AFAIK, previous to MySQL 5.6, the fastest algorithm was INPLACE, which copies the data. ¹=https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-in... ²=https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-op...
  - spacemanmatt 6y ago
    Adding a null column on PostgreSQL is instant. Import your new column data then apply defaults and constraints. This is how people have avoided length locking periods on production databases for years.
Thaxll 6y ago
I don't see where PG would scale better than MySQL since InnoDB has historically been more performant than PG.
- kstrauser 6y ago
  Historically, MySQL has been better at single concurrency setups, like your average Wordpress site whose main reader is the person posting to it. PostgreSQL is usually much better in high volume scenarios. For instance, suppose you have a query that requires a full table scan. In PostgreSQL, that query comes in and the DB starts the scan. Now, a second such query comes in. PostgreSQL notes “I’m on row 10,000” and continues running one single scan, sending results to both queries. When it reaches the end of the table, it marks the first query as complete, then goes back to the beginning of the table and starts scanning again, sending results to the second query up until it reaches row 10,000. Now the second query has seen every row in the table and it’s finished. Now imagine 100 such queries arrive. Rather than doing 100 full table scans, PostgreSQL satisfies all of them concurrently, taking at most 2 full scans (assuming the 100th queries joins in on the very last row of the first scan). PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed. You can vertically scale a single-instance PostgreSQL server a lot higher than many people would believe.
  - tomnipotent 6y ago
    > PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed So does MySQL. Facebook, Google, and Percona have contributed many upstream patches to harden and improve MySQL over the years. MySQL also has much better observability to find issues causing problems, from index_statistics to hunting down individual queries causing locks. I agree that Postgres is by far the better engineered product, but it's not necessarily the better RDBMS product because of that. MySQL is battle tested in a way that Postgres isn't (yet).
- Shish2k 6y ago
  In my cases I found that mysql was faster for “select * from <table> where <unique key>=<blah>” — but for anything more complicated than that, postgres won (admittedly I haven’t checked recently - it was around 10 years ago that my 500-concurrent-user site was melting mysql, so I switched to postgres, which is now happy with 15,000 concurrent users - I’m mostly just responding to “historically more performant”)

by xmodem 6y ago

pizza234 6y ago

Can you elaborate on the specifics of the scalability problems, and how the different databases do differently on them? It's an interesting subject. We're having scalability problems with our RDBMS, but it's something RDBMSs in general don't solve.

xmodem 6y ago
We have a few large (not large in the scheme of things, but large for MySQL) tables that we can't add columns to without locking the table for an extended period. In Postgres this operation is instant no matter the size of the table. This not the only problem, but it is the most pernicious
- Thaxll 6y ago
  MySQL 8 aleviate that problem, although it's not perfect it's better than before: https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-in...
- pizza234 6y ago
  I confirm the sibling post that adding a column is instaneous on MySQL 8 (although there are limitation), and point to the reference document: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op.... Outside the context of db comparisons, and in relation to the specific case, if you don't have triggers/foreign keys on a given MySQL table, Gh-ost¹ solves the DDL locking issues. ¹=https://github.com/github/gh-ost https://github.com/github/gh-ost
  - tomnipotent 6y ago
    > adding a column is instaneous on MySQL 8 Has been since MySQL 5.6 (released Feb `13).
    - zten 6y ago
      In the case that you're using MySQL 5.6-compatible Aurora RDS, you need to enable a special flag to get lock-free nullable column additions.
    - pizza234 6y ago
      Are you sure? Based on the official blog post¹, the INSTANT algorithm is new to MySQL 8.0. The manual of MySQL 5.6 indeed doesn't show any "Instant" option in the list of column operations². AFAIK, previous to MySQL 5.6, the fastest algorithm was INPLACE, which copies the data. ¹=https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-in... ²=https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-op...
- spacemanmatt 6y ago
  Adding a null column on PostgreSQL is instant. Import your new column data then apply defaults and constraints. This is how people have avoided length locking periods on production databases for years.

Thaxll 6y ago

I don't see where PG would scale better than MySQL since InnoDB has historically been more performant than PG.

kstrauser 6y ago
Historically, MySQL has been better at single concurrency setups, like your average Wordpress site whose main reader is the person posting to it. PostgreSQL is usually much better in high volume scenarios. For instance, suppose you have a query that requires a full table scan. In PostgreSQL, that query comes in and the DB starts the scan. Now, a second such query comes in. PostgreSQL notes “I’m on row 10,000” and continues running one single scan, sending results to both queries. When it reaches the end of the table, it marks the first query as complete, then goes back to the beginning of the table and starts scanning again, sending results to the second query up until it reaches row 10,000. Now the second query has seen every row in the table and it’s finished. Now imagine 100 such queries arrive. Rather than doing 100 full table scans, PostgreSQL satisfies all of them concurrently, taking at most 2 full scans (assuming the 100th queries joins in on the very last row of the first scan). PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed. You can vertically scale a single-instance PostgreSQL server a lot higher than many people would believe.
- tomnipotent 6y ago
  > PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed So does MySQL. Facebook, Google, and Percona have contributed many upstream patches to harden and improve MySQL over the years. MySQL also has much better observability to find issues causing problems, from index_statistics to hunting down individual queries causing locks. I agree that Postgres is by far the better engineered product, but it's not necessarily the better RDBMS product because of that. MySQL is battle tested in a way that Postgres isn't (yet).
Shish2k 6y ago
In my cases I found that mysql was faster for “select * from <table> where <unique key>=<blah>” — but for anything more complicated than that, postgres won (admittedly I haven’t checked recently - it was around 10 years ago that my 500-concurrent-user site was melting mysql, so I switched to postgres, which is now happy with 15,000 concurrent users - I’m mostly just responding to “historically more performant”)