3 ms·
I didn't understand how they avoid downtime during the upgrade - they start with "Stop traffic to the database cluster" and then perform a bunch of steps that e
by simonw 5y ago
I didn't understand how they avoid downtime during the upgrade - they start with "Stop traffic to the database cluster" and then perform a bunch of steps that each take 5-10 minutes or longer before starting traffic again - so do they just accept the downtime and schedule it in advance, or did I miss a detail?
- vonnieda 5y ago> One other detail to note is that we built our software architecture in such a way that we can stop traffic to our PostgreSQL databases, queue the transactions, and run a PostgreSQL update without affecting payments acceptance.
- foobiekr 5y agoThat’s downtime by any reasonable measure depending on the exact specifics. Is writing to a journal while the DB is down “downtime”? Site wise no but DB-wise yes.
- rattray 5y agoYeah, they architect their application to accept DB downtime – but I'm sure their services are still degraded to some degree or another during this, and they aren't clear how much total DB downtime they need for this (and how that time scales across various axes). Overall my takeaway is basically "if you want to upgrade a large Postgres db, you'll need like an hour of planned downtime and a lot of careful work" which… doesn't make me excited to upgrade big postgres db's in production.
- rattray 5y agoI did a little googling, and still find the answers are "logical replication, which is slow for large DB's" or "pg_upgrade, which requires nontrivial downtime". Gitlab wrote a much more in-depth rundown in 2020 of a pg_upgrade, which in their case took 2h of downtime and 4h total time: https://about.gitlab.com/blog/2020/09/11/gitlab-pg-upgrade/ https://about.gitlab.com/blog/2020/09/11/gitlab-pg-upgrade/ (submitted to HN here: https://news.ycombinator.com/item?id=29926316 https://news.ycombinator.com/item?id=29926316). They included a video of the whole thing which is pretty incredible.
- znpy 5y ago> Overall my takeaway is basically "if you want to upgrade a large Postgres db, you'll need like an hour of planned downtime and a lot of careful work" HA is one of those things where MySQL wins hands down, sadly. Sadly in the sense that PostgreSQL HA still looks like a couple of hacks held together with duct tape, at least when compared to MySQL solutions. The Percona MySQL distribution has multi-master HA (Percona Xtradb Cluster) which is great for HA, and vanilla MySQL 8.x has group replication which iirc also is multi-master. MariaDB instead has multi-master based on Galera Cluster (although i'm not very knowledgeable about MariaDB). In PostgreSQL world there are many HA solutions, none of which is both multi-master and open source (sadly).
- dikei 5y agoOnly people who haven't run MySQL multi-master HA at scale praise it. In reality, it's a world of pain, where one single seemingly innocent query can take down the entire cluster. Speaking from operation experience with MariaDB Galera and Percona Xtradb Cluster (which is also Galera).
- throwdbaaway 5y agoIn practice, you should typically only send write traffic to 1 galera node, and only fall back to "multi-master" for a split-second when promoting another galera node as the new writer node, either due to scheduled maintenance or unexpected downtime on the old writer node. This setup works great when being fronted by a fleet of stateless proxysql nodes. The whole DBA team can sleep well, without having to worry about whether the automated failover logic would actually work or not. Also note that galera is just a set of API (https://github.com/codership/wsrep-API https://github.com/codership/wsrep-API), which can be implemented against other databases to bring the same replication capability.
- dikei 5y ago> In practice, you should typically only send write traffic to 1 galera node Yes, we did that too, it's the only way to reduce the deadlock and certification failure with Galera. However, it won't prevent a single `ALTER TABLE` to lock up the entire cluster. You can use `pt-online-schema-change` to workaround that, but it's not perfect. We've switched to simple Asynchronous Replication with Openark Orchestrator for the majority of our clusters and the DBA team is much happier now.
- gbrown_ 5y agoWhilst others have mentioned their application is architected to cope with this I wanted to echo the sentiment. This post feels very much like a “draw the rest of the owl” meme.