8 ms·
Migrating Our Django App from MariaDB Galera to PostgreSQL and Patroni
- codedokode 9y agoThis link doesn't open neither in Chromium 46 (ERR_SPDY_INADEQUATE_TRANSPORT_SECURITY) nor in Firefox 45 (shows just blank page). Is it only me? I thinks there is something with their server setup.
- ubernostrum 9y agoWorked for me because my ad-blockers stopped their analytics script. It loads over HTTPS, but seems to include code that will load some other URL over HTTP. That would be an HSTS violation and should cause your browser to bail out.
- _rami_ 9y agoHi! :) do you explicitly see that HTTP URL or is it just a guess? I'm struggling to find it right now :(
- ubernostrum 9y agoIn line 43 of piwik.js I see an HTTP (not HTTPS) URL being constructed. I don't have the time to try to de-minify it and see what it does with that URL, but it definitely seems to be an HTTP URL.
- _rami_ 9y agoThat's weird indeed (although I don't see any connections to a http: URL in the dev console). If someone else reports a similar problem, I'll just remove piwik alltogether.
- _rami_ 9y agoHmm, weird, cannot reproduce this in either Chrome or Firefox, with or without ad blocker. Anyone else having this problem?
- elnygren 9y agoWhy do you want SQLite for development? Running Postgres on your dev machines is a docker one-liner that you can put in the README.md for your devs to copy paste. Most sane CI services also offer Postgres.
- always_good 9y agoI cringe at the thought of using so few features of Postgres that you can actually use Sqlite.
- _rami_ 9y agoMe too, at times. Django makes it bearable though, and going forward we'll probably use more advanced features of PostgreSQL in places where we can fail gracefully or fall back to simpler behaviour on other databases.
- mattmanser 9y agoI honestly feel sad for your colleagues, from your comments on this thread it sounds like you've made a very unnecessarily complicated, over-engineered, nightmare of a process they have to suffer through. Everything's got a justification, and the justifications suck. Use postgres, or don't. Having a stupid number of slow tests is the problem to fix, the solution is not limiting your developers to using basic SQL.
- shlomi-noach 9y ago
- shlomi-noach 9y agoI'm curious whether pretix attempted using MariaDB/Galera with single-write mode, such that the three servers still run synchronous replication, but only one of them gets the writes. I suspect (disclaimer: not running Galera myself) "WSREP has not yet prepared node for application use" and deadlocks would be mitigated, and am curious to learn if that were indeed the case.
- _rami_ 9y agoNo, we haven't. You are absolutely right that this would probably mitigate the deadlocks. However, (1) the deadlocks/WSREP was a problem annoying us, while the query performance was a problem leading to real customer complaints, (2) as I see it right now, we would have needed to take complicated steps to ensure that all application nodes always connect to the same database node; just a hardcoded priority would probably not be enough, (3) a mechanism for (2) would likely make us loose more of the advantages of Galera, e.g. short failover downtimes
- shlomi-noach 9y agoSince you already use HAProxy you should get that almost for free. Your app would talk to the databased by communicating through HAProxy, which would direct traffic to the single-writer; HAProxy's health tests would detect who the single-writer is, and would adapt in the event of failover.
- clon 9y agoIn my experience it did indeed remove a lot of deadlocks when we went from multi master writing to electing a single "writable" node. So in the end it is pretty much the same as deploying some read only slaves, plus automatic master promotion. But not quite. Since even with this setup you might get a lot more weird deadlocks compared to a normal master slave setup, even running SELECT. Before we ditched Galera we were never really able to solve a couple classes of deadlocks, one involving CREATE TEMPORARY TABLE AS SELECT. Also, not all Galera deadlocks are really deadlocks, but actually Galera certification errors, reported as deadlocks to the client, so this distinction must be drawn as well. Remember, a slave just stupidly replays stuff from the binlog onto a known state in a single thread, in the same serialised order that the master has already been able to commit. Galera on the other hand will attempt to certify and commit individual transactions. A certification does not really guarantee that the trx will be able to be committed when it comes to that. Not even in the case of single writer node, in many circumstances. I'd say, after working with Galera/PXC for several years at considerable scale, that the premise of multi master writing only works in very narrow domains and even then you need to write a lot of application code to recover from Galera idiosyncrasies.
- deleted 9y ago[deleted]
- tpetry 9y agoDid you evaluate citus?
- _rami_ 9y agoWe haven't in depth, as they only advertise with their sharding and distributed query features etc and less with their HA features (if that is even in Citus Community). Sharding data is something we won't do before we absolutely need it, since disk capacity is not at all our constraint right now and our scaling problem isn't that its hard to serve many tenants at once, but we may need to be able to serve one tenant with very high concurrency the minute their tickets go on sale (which would only affect one shard on a usual sharding setup). Are you using Citus for HA? Would love to hear your thoughts!
- manigandham 9y agoCitus is a sharding solution, not about multi-master or high availability.
- manigandham 9y agoThese traditional relational databases are fundamentally single-master systems. They have all gotten better at replication and failover but multi-master is not something that just be bolted-on and will never go well. Most companies don't really need multi-master either, the performance level of modern servers is so high that usually failover is good enough. If it's really needed, it's better to look at options like CockroachDB or TiDB instead that are built from the beginning to be multi-master. Also it's unclear from the article but why would the followers be so out of sync? Is there a really bad network? Especially if they set quorum writes on the master then at least 1 follower should always be up to date.
- _rami_ 9y ago> Also it's unclear from the article but why would the followers be so out of sync? Is there a really bad network? Especially if they set quorum writes on the master then at least 1 follower should always be up to date. Yeah, I should have gone into more detail there. I'm talking about cases like after an outage when there is a lot to catch up with, or worse, if there are conflicting timelines (e.g. after a power outage) and synchronization stops working completely -- the broken follower will still answer queries and I've not yet found a simple way to monitor this condition.
- tbarbugli 9y agoAm I the only one thinking this is a terrible Buy versus Build decision? You can get HTTP load balancing, Postgresql with automatic fail-over and queuing up and running in a matter of minutes from AWS or GCE.
- jimktrains2 9y agoPerhaps they don't want to be locked into a vendor's ecosystem or want newer versions of postgres or a different configuration?
- garyrob 9y agoI was wondering about this as well. I hope the OP responds.
- clon 9y ago>MariaDB Galera is really easy to set up and maintain. I take exception to that. When you log into all of your nodes after a network snafu and discover that every single node has attempted to perform a full state transfer, involving the destruction of the data directory... 1. rm /var/mysql 2. Attempt to do FST 3. Crap happens After a while, all nodes were left with no data. Off to backups :) I am sure there is a way to prevent that, but Galera is still not easy ops wise.
- _rami_ 9y agoOuch. FTR, patroni does a similar thing, but instead of rm /var/lib/postgres it does a mv /var/lib/postgres /var/lib/postgres.bak.date ;)
- clon 9y agoA much nicer approach, if you have the luxury of data disk usage below 50%.
- tetha 9y agoMaybe it's my small scale experience, but so far, multi-master relational databases with automated fail-over seems like a lot of risk for just a little payoff. If you can handle the risk, and the operational/development cost, and you need the payoff, go for it. I'm not in that spot. If I have a master01 with master02 replicating as a standby, I can switch between these two masters within 5 - 15 minutes depending on my setup and at what infrastructural level I do the switch - I could reconfigure the application, switch a dns entry, use a load balancer like maxscale. It's downtime, but it's a low-risk recovery with well-known impacts. With a multi-master setup, I have to do at least two things: First, I must ensure my applications transaction-safety. Read-Write splits with an application with bad transaction management is fun, and write-splitting will end up with even more of a mess. And then I need to setup and operate the multi-master setup, which is a non-trivial decision and selection imo. Just look at the number of possible solutions for postgres. This in turn allows the system to automatically failover in case of trouble - which my current infrastructure would have had to do 3 times over two years, and it would have helped 2 times at most. Except if the failover itself fails and ends up harder to handle than the database failover, like in your case or in other really scary postgres failover horror stories. And interestingly enough, in our b2b context, our customers actually prefer a well-known, low-risk failure plan, even if it is 30 minutes of outage.
- aargh_aargh 9y agoThanks for the detailed write-up, especially the dead-ends/failed approaches.
- zimbatm 9y agoHere is how to do it with zero downtime: Each customer has a completely separate data set, except maybe for login information. This makes it a good candidate for sharding. Sharding is also a good idea because eventually one big ticket sale event is going to hog the whole system and having a way to isolate it allows the rest of the customers to keep working. 1) Add a shard ID to the customer table. If it's blank, use the existing database. 2) Create additional master-slave pairs with the same DB schema 3) Create a tool to migrate customer data between databases 4) Migrate customers individually, creating minimal downtime during their transition. With streaming replication this can be turned to zero downtime. Wordpress has similar scaling issues and talked a lot on how they do it.
- innocentoldguy 9y agoWhile I think, overall, the article provides good information, I would not recommend developing with SQLite if you’re using some other database in production. I have run into too many cases where doing this leads to hard to find bugs.
- _rami_ 9y agoOur CI rans our test suite against all of SQLite, MySQL and PostgreSQL which worked well enough that I can only remember one or maximum two bugs caused by this, but yes, wise words.
- abkfenris 9y agoPatroni has now implemented a Sync endpoint that you can check with HA_Proxy. https://github.com/zalando/patroni/pull/578 https://github.com/zalando/patroni/pull/578
- zzzeek 9y ago> We talked to a few database experts at the side of one or two conferences and this made us lose any trust that Galera is able to perform even remotely well in terms transaction isolation, constraint enforcement, etc., even though we did not observe such problems in practice ourselves. there's a statement really short on specifics. Galera has nothing to do with constraint enforcement, these are functions of the underlying MySQL / MariaDB database, and the major constraint enforcement issue there is CHECK constraints, which only MariaDB 10.2 actually supports, and that's a very recent thing. Similarly with transaction isolation, that's also a function of the MySQL/MariaDB engine with the exception of Galera nodes accepting write-sets from other nodes, but while those introduce the concept of your whole transaction being rejected, it doesn't introduce any other negative isolation effects, except if you were hoping to have your transactions talk to each other with dirty reads. Like a lot of these "we migrated from X to Y" stories, all the negatives they refer to here that are Galera-specific are resolveable. The "MySQL sucks" part of it, e.g. the issue with the query planner, sure. Multi-master Postgresql will be extremely useful if a product as easy to use as Galera is produced.
- agnivade 9y ago> MariaDB has a hard limit of using one index per table in a single query. Wait, seriously ? What is the rationale behind something like this ?
- tetha 9y agoThis seems to be not generally true. MysqlDB > 5.5 and MariaDB > something less than 5.3 support index_merge in their query plans. Skimming the mysql page, this allows mysql to evaluate different parts of the where-clause on different indexes and merges the results later on. Most examples on the mysql-man page also use just a single table in their queries.
- nh2 9y agoInteresting article. > Your browser is usually clever enough to use the working IP address if the other one is not working. I found this to not work well. Have you tested it? For me it took 1-2 minutes for Chromium to switch over to the second entry after it had loaded the page from the first entry once (with a white page and spinning spinner during that time); this is usually not acceptable for site visitors as they will get impatient and leave already after a couple seconds. What I'm using instead for a setup is multiple A entries in combination with active removal of down IP addresses from the response using e.g. Route53 health checks and 10-second TTLs.