How Swat.io migrated from MySQL to PostgreSQL in 2 years | Hacker News Reader

How Swat.io migrated from MySQL to PostgreSQL in 2 years

garage.socialisten.at

35 pointsMarkusWinand13 comments

> [...] the maintenance headaches with MySQL started to have negative effects on our teams morality [...]

As a general observation, "morality" (eg moral values) might be the wrong word there. Guessing you're meaning "morale" (?), which is more like "how happy our team members are".

That minor nitpick aside though, thanks for sharing. :)

I also enjoyed the article, and also have a nitpick which you may find interesting.

> Still, over time every new internal project pretty soon begged the question...

You probably mean "raised the question". Begging the question is a type of logical fallacy[0].

[0] http://www.nizkor.org/features/fallacies/begging-the-questio...

"Cannot online add a new column" This is false. https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-...

Very weird.

From https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-...:

  Add column
  In-Place?: *Yes*
  Rebuilds Table?: *Yes*
  Permits Concurrent DML?: *Yes* (Concurrent DML is not permitted when adding an auto-increment column.)
  Only Modifies Metadata?: *No*
  Data is reorganized substantially, making it an expensive operation.

In practice, the last no is a serious problem.

Could you elaborate why it prevents online DDL ?

I think the point of the article is that adding an index to a big table with lots of writes is practically not possible:

Quoting from the article:

  real problem for big tables

  as adding a few columns to our biggest tables started to take 2+ hours
  or sometimes was completely unpredictable and exceeded our announced downtime windows.

Apparently, adding a column was even a problem during a maintenance window because the runtime was even longer than they expected.

Compared to PostgreSQL:

  As long as the new column is NULL and does not have a default value,
  it’s in practice a no-op to add it. No matter if your table size is 100MB or 100GB

The linked https://www.percona.com/doc/percona-toolkit/2.1/pt-online-sc... generally works really well though, and in the simplest case it's as easy as following the usage example. I'm surprised that they knew about it yet didn't invest the time to learn it.

Interesting read, and jealous somebody gave you 2 years for such migration :)

Small point of attention, on Safari/OSX swat.io loads as a white page with a scrollbar (it notices the page is larger in height), but only to be populated with content only 5 to 10 seconds after.

"We had complete lock ups where max_connections was exceeded and we could never find a source, internal or external, to our system. Literally hundreds of connections doing SELECT statements but nothing else. Eventually manually killing them “solved” it"

Wouldn't having "max_connections" postgresql processes create more problems?

Usually not. When you reach max connections it does not cause problems for existing connections ie, no "complete lock ups)", it just stops allowing new connections. Also, there are normally reserved connections for postgresql superusers so you can query to find out where all the excess connections are coming from and kill them as needed.

I second that. We had hit the same problem with our mysql database a week ago and a manual restart was needed to fix the problem.

Hard to say. Now that we switched to PostgreSQL, operations run much more smoother. You literally can't compare it. But this is also attributed to the major refactoring we did under the hood too; as I pointed out in the article, I simply "driver switch" didn't cut it :-)

We hit the max connections limit in postgres too at peak times (but no lock-up or similar thing happened) and per advice of our hosting provider we added pgbouncer to the stack last week and will hopefully don't have much problems here anymore.

Disclaimer: I wrote that article.

I don't think PostgreSQL has issues with max_connections, it'll simply start rejecting new connection requests (as pointed out by davidgould).

What we see in practice, though, is that people often increase max_connections to rather insane values. There's only a certain number of active connections each box can support (say, ~2*cores, sometimes more), and it's one of the things we check whenever a customer contacts us with performance issues. Oh, you increased max_connections to 7243, it was running fine for a while and then you got a short burst of activity on Monday morning and it's crawling out of the rack? pgbouncer FTW in most cases (in transaction pooling mode).

Reply on news.ycombinator.com