"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.
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.
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).
> [...] 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. :)
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.
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.
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?
17 comments
[ 3.3 ms ] story [ 52.2 ms ] threadWouldn't having "max_connections" postgresql processes create more problems?
after 2 weeks of production use, we, nevertheless, switched to pgbouncer to have a more stable connection management, though.
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.
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).
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. :)
> 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...
Very weird.
Quoting from the article:
Apparently, adding a column was even a problem during a maintenance window because the runtime was even longer than they expected.Compared to PostgreSQL:
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.
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?