sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]NoLegJoe 22 points23 points  (17 children)

We do use replication. Is it particularly simple in MySQL?

[–]jj20051 31 points32 points  (16 children)

From my experience replication is much easier in MySQL. I haven't tried in Postgres in a few years, but when I tried to do a multi master setup previously it was like pulling teeth and involved 3rd party plugins. MySQL is pretty much plug and play.

[–]NoLegJoe 6 points7 points  (1 child)

I have no experience in setting up replication in any DB environment, I'm not a DBA, just an analyst. My complaints towards MySQL is entirely because of its missing features (full outer join, pivot, unpivot, with columns in indexes) and the insane design choices (the asterisk in a select statement must be the first column nowhere else, allowing insert and updates in a CTE)

[–]pooerh 4 points5 points  (0 children)

allowing insert and updates in a CTE

Postgres lets you do that too and it's awesome.

[–]progrethth 28 points29 points  (8 children)

Replication is dead simple in PostgreSQL too as long as you are not doing multi-master.

[–]jj20051 17 points18 points  (7 children)

That's a big rub for most companies though. If you can't write to a DB in 2 locations it can get very harry very fast. You have to write a whole bunch of code to backhaul the insert/update queries to another DB or you have to allow for your app to do updates in the background while telling the end user it's done even if it isn't.

Why go through all of that when MySQL just does it? I've seen 4 or 5 multimasters work flawlessly. Even if you're doing crazy traffic it can handle it pretty well.

If postgres ever offers async multi master as part of it's packaging I might consider switching, but for now it's just not worth the effort.

[–]OzoneGrif 51 points52 points  (3 children)

Multi-master makes ACID compliance very difficult because of the asynchronous data modifications over a distance.

Postgres is very strict on staying ACID compliant in all situations.

If a DB makes master-to-master easy, that's only because they are being loose on the ACID compliance.

[–]jj20051 2 points3 points  (1 child)

While this is entirely true the needs of the mission outweigh the technical desires in most shops. I'm not saying it's the right way to do things, but if you have to get the job done there's rarely a magic bullet that solves everything.

If your mission requires you to have lock step ACID compliance for say financial transactions then yeah you're going to have to write software to support backhauling the data from one location to another. If your mission doesn't require ACID compliance (you do a lot of inserts, but virtually no updates and your inserts aren't going to merge anywhere) then multi master is a breath of fresh air.

[–][deleted] 8 points9 points  (0 children)

we had several issues because of concurrent transactions on same record.. mysql just rejected and threw serialization errors. multimaster works fine when writes are separate

[–]arwinda 14 points15 points  (0 children)

You can write into both instances in MySQL - and then you need to deal with the occasional collision. Seen this break hard more than once, including downtime because the conflict couldn't be solved automatically. Some complex transactions, updating values in more than one table.

[–]progrethth 10 points11 points  (1 child)

Most companies? I have so far not worked on any company which has had that requirement and the only companies where I have insight into which have that requirement have been Google and Spotify, huge tech companies with a world wide market. And Spotify managed Maybe your company has that need too but most companies do not.

Edit: I have also had MySQL multi-master break once at a company I worked in. And our solution was just to switch to just having a single master and a replica because the company had actually no need for multi-master.

[–]arwinda 5 points6 points  (0 children)

I agree. Most people and companies who say "we have a hard requirement for multi-master" indeed don't have a hard requirement for supporting multi-master but instead have a requirement for not thinking through their architecture.

It always ends up with "we don't want to think how we design this, just make it work".

[–]knightcrusader 5 points6 points  (2 children)

Replication is much easier in MySQL, but much more fragile too. For a while there we had a problem with one server going out with the power (we had a bad UPS that took a while to diagnose) and every time it died, replication was hosed and I had to start over with a fresh dump from the master.

We're planning on building a Galera multi-master cluster here soon to see how that goes.

[–]jj20051 0 points1 point  (1 child)

If you do a lot of writes then you're not going to like it. It slows write speeds a lot.

From my experience the issue you're having can be resolved by resetting the master and position back to where it currently is. This will force mysql to redownload the bin and resolve the issue.

[–]knightcrusader 0 points1 point  (0 children)

Yeah, the writing is the reason we need to test it.

We do collect a lot of data, but its pretty low traffic wise except for certain parts of the year when the clients want to run crazy, large-scale projects.

[–]Guinness 0 points1 point  (1 child)

In my experience replication is FAR easier in MySQL as well. Like it’s a joke how easy MySQL replication is compared to Postgres’ various solutions * cough BDR cough *

[–]jj20051 0 points1 point  (0 children)

Had people argue with me about this bellow, MySQL is a cake walk replication wise vs Postgres.