7 ms·

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

35 points by MarkusWinand 9y ago

sfilargi 9y ago
"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?
- davidgould 9y ago
  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.
- jollife 9y ago
  after migrating, we did not encounter any problems with max connections again. after 2 weeks of production use, we, nevertheless, switched to pgbouncer to have a more stable connection management, though.
- petre 9y ago
  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.
- some_developer 9y ago
  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.
- pgaddict 9y ago
  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).
justinclift 9y ago
> [...] 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. :)
- jollife 9y ago
  thx, we fixed this in the text!
- apeace 9y ago
  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-question.html http://www.nizkor.org/features/fallacies/begging-the-questio...
  - jollife 9y ago
    thx, we fixed this!
mt42or 9y ago
"Cannot online add a new column" This is false. https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-... Very weird.
- MarkusWinand 9y ago
  From https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html 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.
  - mt42or 9y ago
    Could you elaborate why it prevents online DDL ?
    - MarkusWinand 9y ago
      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
      - tveita 9y ago
        The linked https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html 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.
thinkMOAR 9y ago
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.
- jollife 9y ago
  well, of course, we did not work on this project exclusively for 2 years. but, yep, we're really happy with our CEO giving us such a long time to think about/perform the migration. thanks for pointing out the problems with Safari. For me it's working with the latest versions of Safari/OSX. Can you send a screenshot via the support tool at swat.io?

35 points by MarkusWinand 9y ago

"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?

davidgould 9y ago
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.
jollife 9y ago
after migrating, we did not encounter any problems with max connections again. after 2 weeks of production use, we, nevertheless, switched to pgbouncer to have a more stable connection management, though.
petre 9y ago
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.
some_developer 9y ago
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.
pgaddict 9y ago
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).

justinclift 9y ago

> [...] 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. :)

jollife 9y ago
thx, we fixed this in the text!
apeace 9y ago
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-question.html http://www.nizkor.org/features/fallacies/begging-the-questio...
- jollife 9y ago
  thx, we fixed this!

mt42or 9y ago

"Cannot online add a new column" This is false. https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-... Very weird.

MarkusWinand 9y ago
From https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html 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.
- mt42or 9y ago
  Could you elaborate why it prevents online DDL ?
  - MarkusWinand 9y ago
    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
    - tveita 9y ago
      The linked https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html 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.

thinkMOAR 9y ago

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.

jollife 9y ago
well, of course, we did not work on this project exclusively for 2 years. but, yep, we're really happy with our CEO giving us such a long time to think about/perform the migration. thanks for pointing out the problems with Safari. For me it's working with the latest versions of Safari/OSX. Can you send a screenshot via the support tool at swat.io?