Hacker News Reader

Advanced replication strategies in MySQL and Oracle mostly cause hair loss and divorce. Everywhere I have worked that uses these databases eventually convinces itself that it needs futuristic replication technology and then goes on to suffer from outages and weird corruption. It turns out, distributed consistency is a hard problem. Oracle and MySQL both benefit monetarily from people assuming it will work and then activating it, only to find out a month or two later that it's a complicated problem that requires actual planning and oversight.

I think Postgres will be a better product when it incorporates similar functionality, but the implementations in MySQL and Oracle lie somewhere on the border between false advertising and reckless endangerment. I appreciate that Postgres not having it as two-lines of configuration sends a message that you are making a commitment. Nearly everything else in Postgres is set-it-and-forget-it. You really want to have a DBA if you're going to try to make replication work. When you read the docs on having a hot standby and how much of a commitment that is and sense yourself getting tired, you're starting to appreciate what you might be getting yourself into with single-master replication, let alone multi-master.

> Nearly everything else in Postgres is set-it-and-forget-it.

There are many people who would disagree with that. Talking to Uber engineers would probably be a good idea to dispel that myth.

I read their article, and a dozen of the rebuttals. If you want to use MySQL badly enough, you can.

Uber switched from MySQL used as relational database, to Postgres used as relational database, to MySQL used as key-value store.

Nobody is saying MySQL is a bad key-value store!

Is key-value store the most stable scalable architecture? Reddit, I believe, uses the same idea

If you give up ACID (the C) you can scale more (the P), sure.

https://en.wikipedia.org/wiki/CAP_theorem

To restate your other reply, key-value allows you to sweep consistency problems under the rug. What happens if two transactions try to update the same row? One of the transactions fails. What happens if two clients write different values to the same key in a multi-master key-value store? The last one in wins and the first write is just lost.

Many of the NoSQL databases have provisions for these situations so they can handle both writes but bubble the decision up to the application layer. CouchDB, for instance, will tell you you've got multiple versions and has a way for you to tell it how to resolve the conflict. CRDTs let you bake the logic in for handling a conflict. MySQL does not do any of that. If your database consists of "likes" and other low-value data like that, you may not care if you're losing data at some low rate. Reddit is in that situation, and the decision seems to work for them. But I don't think it generalizes to all situations.

> There are many people who would disagree with that. Talking to Uber engineers would probably be a good idea to dispel that myth.

not a good idea, somebody who let their database master run out of storage space is probably not a good source.

Uber is a bad reference for good practices.

The Uber blog post got a lot of things wrong and if that is representative of their knowledge of PostgreSQL I would not suggest using them as an authority on PostgreSQL or databases in general.

This has not been my experience of multi-master asynchronous replication in MySQL at work. We use only one replica for actual work and the second master is read-only until failover - and is only write enabled after the primary is disabled - and it works quite well.

Getting replication bootstrapped synergises well with having a smooth backup restore procedure, similarly any hiccups in replication can be dealt with using restore.

We have other release QA procedures that use restored backups for testing, so our restore procedure is extremely well exercised. Really fast restores using the Percona tools is a considerable bonus of the MySQL world.

> This has not been my experience of multi-master asynchronous replication in MySQL at work. We use only one replica for actual work and the second master is read-only until failover

That's not multi-master. That's single master with a hot standby.

> any hiccups in replication can be dealt with using restore.

Restore isn't an option when your database is so large that it takes most of a day to copy the backup onto the replacement master to restore from it. (Not speaking hypothetically here.) Obviously restoring from a backup is still there as a last resort in this case, but you really don't want to have to go there.

Galera builds on that, and it works very well. It has weak points, but if you build a working Galera cluster and test it (test the failovers, test [re]bootstrapping a node), you can then be confident that it'll continue to work as intended for a long time.

There are rough edges. Upgrades, recovery from corrupted hot backup, etc. But it's very easy to rebuild the cluster in flight, for example when changing some fundamental cluster property, or after a PEBKAC, (with a not too big read-only window), though it requires plus one node, a regular MySQL/MariaDB server that acts as a donor (where Galera can bootstrap from), but that's not too surprising if you want to at least serve read-only requests.

You also can't use Galera on RDS, which is a deal breaker for some: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_M...

Sounds like a reason not to use RDS more than a reason not to use Gallera.

Why would you use RDS? AWS is overpriced.

If you really want no-ops deployment, use something that requires minimal amount of ops, like ElasticSearch. Just give it an obscene amount of RAM and it'll work fine for ages.

RDS or not, if you want to run a big site, you'll need (dev)ops expertise. And a lot of it. Famously, Netflix - the big full-cloud business, - employs the most aggressive performance engineering and other "auxiliary" development groups. (Because AWS is fckn!1 expensive, and when you can run on a thousand less nodes, it'll cost you less. And yes, of course Netflix gets a big discount, but you won't, at least not initially.)

> no-ops deployment

This is the worst joke in tech really. Developers/Managers all jumping on the "cloud" bandwagon because "hey we don't need ops" and then you end up with "Galera is such a POS, you can't use it with RDS".. <facepalm>

Neither Netflix nor any of the top "startups" are anywhere near aggressive performance. In fact they're usually full of slow fragile tech that only looks great to blog about.

HFT, adtech, scientific engineering and many other industries are magnitudes farther ahead.

> If you really want no-ops deployment, use something that requires minimal amount of ops, like ElasticSearch. Just give it an obscene amount of RAM and it'll work fine for ages.

I can assure you this is not the case if your indexes are very large and their number is constantly growing, and/or clients are doing very complex queries that stress the system.

The same thing applies to anything really, if you try to "shard" (partition, or simply do multi tenancy) based on files, MySQL tables, MongoDB collections, and of course elasticsearch indexes.

And you have to plan ahead and start with a large number of shards, to be able to scale your ES cluster.

Yet it's much better than trying to get some kind of SQL based cluster going, because those require knowledge of a lot of subtle internal details.

It's multi-master from MySQL's settings perspective.

Restoring a backup takes minutes for multi-TB restores.

Just because MySQL calls it multi-master doesn't make it so. What you described is single master with a hot spare. Real multi master is when a client can write to both servers and they will be consistent by the magic fairy dust that is replication.

Minutes for multi TB restores? Only if both your main and your backup are on SSD, which is still not common for multi TB databases, I think.

1TiB=1,000GiB; if your drives do 300MB/sec, that's still one hour.

How exactly do you get that multi-TB backup onto the database server's disk so quickly? Even with a gigabit network interface, that's only 125MB/s, which is over two hours[0] to transfer 1TB. Even if you have a 10 gigabit network, that's ~13 minutes[0].

Also consider if the backups are compressed -- a 1TB compressed file also takes time to uncompress. If the data compresses well, you could even run into the disk being the bottleneck, not the network.

[0] Well, more than that; those numbers assume you're actually getting the link rate with no overhead, which of course isn't reality.

Interesting side note:

A significant number of Oracle shops use Sybase Replication Server to replicate from an Oracle - to an Oracle database.

Go figure...

RE: Oracle Replication - Have you looked at DbVisit? Compared to the PhD you need for GoldenGate, you can get by with a high school diploma with this stuff.

http://www.dbvisit.com/

> The guy has deliberately changed a default setting (strict_mode) to make MariaDB/MySQL look worse. When someone pointed that out in the comments, he left it unresponded.

I don't think the comments read like that. Someone pointed out that some MySQL variants have changed the default in a recent version and the author criticised the defaults, so the implication is that he wasn't using a bleeding-edge MySQL installation and got the previous defaults.

Agree, I might have misread the discussion. However, strict mode is on my default since MySQL 5.7 (released in 2015) and MariaDB 10.2 (which is also the version he was using). So he changed that, but didn't explicitly state it. Even when multiple people mentioned sql_mode.

> However, strict mode is on my default since MySQL 5.7 (released in 2015) and MariaDB 10.2 (which is also the version he was using). So he changed that, but didn't explicitly state it.

From the comments of the original article, the actual version he was using is 10.0: > this is the default version coming with Fedora core 23 (mariadb.x86_64, 1:10.0.28-1.fc23).

OK, I see it now, thanks. I would even edit my original comment if I could. Still, the original article is biased without mentioning strict mode, the fact that it's been recommended to turn it on in any serious deployment for years, and that it is enabled by default in the latest MySQL/MariaDB releases.

There are reasons to choose PostgreSQL over MySQL (and vice versa), but the article is certainly an example of unfair evangelism.

It's a pretty damning point against MySQL that there's a default setting that you have to know to change, or else the DB will happily throw away your data.

A lot of the evangelism against MySQL is rooted in a deep skepticism against letting a DB that would even have such a feature, much less leave it on by default for years after sort-of admitting that it's probably a bad idea, anywhere near your data. I loved MySQL back in the day when it would work straight out of the box on both Windows and Linux (and god knows not a lot of web software did back then), and Postgres was always, frankly is still, a bit opaque to actually get from zero to one. But I also remember discovering the varchar overflow and the users being pissed at me for losing their data. Luckily it wasn't a really important field and we discovered it reasonably fast, so fallout was contained. But the same app actually dealt with payments, it could have been much worse.

It's basically a country song: MySQL is the kind of guy that's tons of fun, always the centre of the party, but used to disappear for multi-day benders without a word, but have now cleaned up and is disappointed that his high school sweetheart prefers the boring guy who went to college, got a job and was never late for a date.

True, but then you can sing a similar song about PostgreSQL being the kind of guy that is cool all around, and is always there for that high school sweetheart, but still that badass MySQL gets all the fun and is always center of the party, which is oh so wrong.

Which is already telling something about PostgreSQL evangelism.

No. Speaking as an outsider, the Postgres community is about the nicest (and smartest) there is.

I know what I'm talking about. Over the last couple of years I had to speak to many people in the PostgreSQL community. While there are indeed many smart and nice people with balanced views and opinions there, the majority has this ideological bias. With ideology being that PostgreSQL is superior to MySQL in every single way. All counter-examples and arguments are usually ignored. I think it's something unique to PostgreSQL community, I've never met anything like that in other technical communities.

As someone who might fairly get scolded for this, we feel like praising MySQL's features is a bit like enjoying the recessed lighting and granite counters in a house whose roof is actively leaking.

There are certainly features MySQL has that Postgres doesn't, but data integrity is problem 1, and most of us got burned by MySQL on that one at some point before we got here. Or, we deployed MySQL and then found out that the features we were depending on were InnoDB-only, but switching to InnoDB cratered our performance. Or we tripped on one too many of MySQL's numerous special cases or silent limitations.

You can use MySQL productively. You can even benefit from its special features. But the thing taken together is a bit of a mine field.

Postgres has some false advertising issues too ("object-relational"?) but the day-to-day living with it is a lot nicer, because the roof doesn't leak. I can live without the granite counters.

Data integrity is of course important, and MySQL indeed has a weak story here, especially in older versions.

Is PostgreSQL flawless when it comes to data integrity? No, that would also be false advertising. Some examples:

- data checksums are disabled by default (silent data corruptions!), and the user manual specifically warns they are not cheap. And the reasons they are expensive is that PostgreSQL does not support O_DIRECT IO;

- removing a file on the filesystem may lead to a silent loss of data in PostgreSQL: https://www.postgresql.org/message-id/c571dfc5-91b0-0df2-4e3...

- some important control structures (which also impact data visibility and thus, integrity) are not even protected by checksums: https://www.postgresql.org/message-id/CAA4eK1L%3DXS3k7X9pfYK...

- there are no tools to verify replication integrity, similar to pt-table-checksum for MySQL; that would require statement-based replication, which is not supported in PostgreSQL, even in the next major release

Why would O_DIRECT make checksums any cheaper? You always update the checksum at the same time as the data so I cannot see how O_DIRECT could matter.

I am pretty sure the real reasons for checksums being off by default are two:

1) That you need to WAL log hint bit changes if for checksums to work. Hint bits are part of PostgreSQL's MVCC implementation and that you do not need to WAL log them is a clever optimization. This extra WAL traffic can at least in theory hurt some workloads.

2) Probably most importantly: nobody has provided the benchmarks necessary to show that the cost is not too high.

Without O_DIRECT, the page may be updated multiple times in the page cache before it hits the disk. The only thing PostgreSQL can do is to update the checksum before expelling the page from its own cache, so it will be doing unnecessary work in write-intensive workloads. But yes, hint bits and WAL is also a good point.

But PostgreSQL does not update the file directly so this is not an issue. Instead PostgreSQL does all updates in its buffer cache, and then has a background writer which flushes modified shared buffers to the file (but without using O_DIRECT), and then finally it fsyncs the modified file. The window between modifying the file and fsyncing is usually not big so almost always when a page is updated multiple times that will happen before a page is written from the buffers to the OS's file cache.

The above is obviously inefficient which slows down writes, but checksums are not affected by this since those are only calculated on flush. The costs of not using O_DIRECT are 1) extra work on flushing to disk, 2) IO spikes due to the unpredictable nature of when and in what order the OS decides to flush to disk, and 3) wasted RAM. The main benefit is that being able to use the file cache on read loads (by tuning PostgreSQL with small buffers) is that PostgreSQL is nicer to run on shared machines, for example my laptop, since the OS can steal back the read cache when PostgreSQL stops using it.

Checksums on modified pages should only be computed immediately prior to transferring pages to disk. DIRECT_IO makes that transfer point explicit so you can compute the checksum exactly once per transfer regardless of the number of page modifications. If you use the kernel cache like PostgreSQL, you must apply the checksum pessimistically because you don't know when the page will _actually_ be written back to disk by the kernel in-between page modifications.

Computing page checksums burns quite a bit of memory bandwidth, and memory bandwidth is a major resource bottleneck in modern databases. In the DIRECT_IO case, the checksum memory bandwidth should be the same as your storage bandwidth; pessimistic checksumming without DIRECT_IO can burn memory bandwidth that significantly exceeds the storage bandwidth.

Particularly in cases where you have modern, high-performance storage devices, like PCIe connected flash arrays, you can't afford to burn memory bandwidth on checksumming beyond the minimum technically required.

As I explained in another reply, PostgreSQL does no write directly to the files/file cache. It has its own buffer cache and only calculates the checksum when flushing dirty pages to disk (where PostgreSQL does a normal, cached write syscall and then fsyncs). The lack of O_DIRECT causes other performance issues, but not this one.

> data checksums are disabled by default (silent data corruptions!)

Use ZFS.

> removing a file on the filesystem may lead to a silent loss of data in PostgreSQL

Don't remove database files. LOL. Looking at the link:

> data file is zeroed after some power loss(it is the most known issue on XFS in the past)

Use ZFS.

> not even protected by checksums

Use ZFS.

> and most of us got burned by MySQL on that one at some point before we got here

Using 3 bytes per codepoint for UTF8 was the one that got me to switch (boy wasn't that fun to discover).

Yes it was fixed a while ago now, but it wasn't fixed when I needed it and so I switched to PostgreSQL and never looked back.

> Or, we deployed MySQL and then found out that the features we were depending on were InnoDB-only, but switching to InnoDB cratered our performance.

Or worse, we were expecting InnoDB but the default on the mysqld which comes with the RHEL version the client used to deploy our software was MyISAM, so the cascade didn't work when deleting a row, and the dangling reference caused an exception deep in the framework we were using, making parts of our software stop working.

Describing the production operation of MySQL as a minefield is an excellent analogy. I maintained a list of the mines I stepped on in the last job I had where MySQL was used and here are the two I encountered with the biggest blast radius:

* If you have pre-5.6.4 date/time columns in your table and perform any kind of ALTER TABLE statement on that table (even one that doesn't involve the date/time columns), it will TAKE A TABLE LOCK AND REWRITE YOUR ENTIRE TABLE to upgrade to the new date/time types. In other words, your carefully-crafted online DDL statement will become fully offline and blocking for the entirety of the operation. To add insult to injury, the full table upgrade was UNAVOIDABLE until 5.6.24 when an option (still defaulted to off!) was added to decline the automatic upgrade of date/time columns. If you couldn't upgrade to 5.6.24, you had two choices with any table with pre-5.6.4 types: make no DDL changes of any kind to it or accept downtime while the full table rewrite was performed. To be as fair as possible, this is documented in the MySQL docs, but it is mind-blowing to me that any database team would release this behavior into production. In other words, in what world is the upgrade of date/time types to add a bit more fractional precision so important that all online DDL operations on that table will be silently, automatically, and unavoidably converted to offline operations in order to perform the upgrade? To me, this is indicative of the same mindset that released MySQL for so many years with the unsafe and silent downgrading of data as the default mode of operation.

* Dropping a table takes a global lock that prevents the execution of ANY QUERY until the underlying files for the table are removed from the filesystem. Under many circumstances, this would go unnoticed, but I experienced a 7-minute production outage when I dropped a 700GB table that was no longer used. Apparently, this delay is due to the time it takes the underlying Linux filesystems to delete the large table file. This was an RDS instance, so I had no visibility into the filesystem used and it was probably exacerbated by the EBS backing for the RDS instance, but still, what database takes out a GLOBAL LOCK TO DROP A TABLE? After the incident, I googled for and found this description of the problem (https://www.percona.com/blog/2009/06/16/slow-drop-table/) which isn't well-documented. It's almost as if you have to anticipate every possible way in which MySQL could screw you and then google for it if you want to avoid production downtime.

There are others, too, but to this day, those two still raise my blood pressure when I think about them. In addition to MySQL, I've pushed SQL Server and PostgreSQL pretty hard in production environments and never encountered gotchas like that. Unlike MySQL, those teams appear to understand the priorities of people who run production databases and they make it very clear when there are big and potentially disrupting changes and they don't make those changes obligatory, automatic, and silent as MySQL did.

The object-relational stuff works (table inheritance). Or at least, it did the last time I tried it out a few years back; I've never used it in production.

Your insistence on replying to every single comment on this thread combined with your statements like "I know what I'm talking about" (and you don't) suggests to me that perhaps you're an evangelist yourself?

It's just something I care about. You may call me an evangelist if you like, I consider myself an anti-evangelist.

Indeed you're not very effective in prasing MariaDB.

> - synchronous multi-master cluster (Galera, Group Replication)

Which doesn't work, see [1]. What other features of those you listed the authors claim that work, and just don't when you look deeper?

Lot of the big companies are using MySQL as a NoSQL database, and that's not a bad way to use MySQL, but it's a pretty bad relational database, see [2].

[1]: https://aphyr.com/posts/327-call-me-maybe-mariadb-galera-clu...

[2]: https://grimoire.ca/mysql/choose-something-else

Let me just tell you that I've read both links before.

If you've read the aphyr link with this (and the rest of his conclusion):

> Unfortunately, even in totally healthy clusters, with no node failures or network failures, Galera Cluster does not satisfy its claims of Snapshot Isolation

...how on earth can you straight-facedly claim that it's a plus point for MySQL over PostgreSQL?

I didn't realise a few of these existed, so thanks.

> - page-level encryption

This got me excited, but for anyone else looking: to keep the key securely (not in a file on the DB server, where an attacker can get it + the DB files) you need to but MySQL Enterprise Edition. Quoting from https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-en...:

> The InnoDB tablespace encryption feature in non-enterprise editions of MySQL uses the keyring_file plugin for encryption key management, which is not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).

Well, personally what I see is people comparing a group of developers that are adamant on never corrupting any data, where everything is secondary and will refuse to push any feature that may cause problems with a group that is adamant on increasing market share, if it's by doing a low quality MVP so be it, that I have seen corrupting data on production several times, and whose software still carries the scars from those MVP's errors.

Yes, MySQL is all featureful¹. The only thing missing there is guarantees that your data will stay good.

1 - Some of those are "no, we won't ever implement it" features that will do nothing but eat your data by design. Why would you ever want them in a persistence layer? Cache on memory somewhere better.

> Some of those are "no, we won't ever implement it" features that will do nothing but eat your data by design. Why would you ever want them in a persistence layer? Cache on memory somewhere better.

I feel a significant motivation for MySql development are lightweight webapps on PHP like Wordpress where the developers are beholden to a third-party hosting environment and have a significant front-end focus...

From that perspective I can see why bells and whistles that would be moronic in a normal application would start sticking to the database itself.

MongoDB suffers from some of the same market pressure: being easy to demo and get from 0 to SAP in 10 min is great, but having to choose crazy insecure defaults and be open for data loss in order to get there is a showstopper in Enterprise circles. In both cases the ironic demands for competent administration are much higher because of all the potential for foot-shooting.

"It's always worth it to take the pain up front."

There is one point I need to add it here. Note that I'm not DB expert, but I need to say why I prefer Postgres over Mysql.

Create a table like the following:

create table user (name varchar(4));

MySql:

insert into user (name) values ('antoaravinth');

The above statement works even though I have given the size of my varchar as 4! And retrieving gives me `anto`!!

Postgres:

insert into user (name) values ('antoaravinth');

Stops me there with the relevant exception! That shows how Postgresql is doing small things right.

After seeing this, I never used MySql!

As said in many other comments: This is because you used MySQL in non strict mode. You don't run MySQL on Production in non strict mode.

As said in many other comments, the idea that there is even a "strict mode" which was not on by default for many years is itself a problem and a red flag for other problems.

Tell that to developers who develop with the default config and then expect that to work in "Production".

MySQL has similar issues to postgres vacuum. If 'checkpoint age' gets too high throughput drops by ~5x to prevent WAL wrapping. We hit this in production when using 5.5. They've made a lot of improvements to flushing in recent versions, but it isn't a strict advantage. With both DBs as long as you monitor you would be fine.

> - optimizer hints

> - optimizer tracing

Hints are bad. They always have been. If you need hints your query planner is broken.

https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion

Even Oracle Tom says DO NOT USE HINTS IN PRODUCTION! Report the bug to Oracle and let them fix it properly.

As a general recommendation maybe this is ok. But, unrealistic relative to the world we live in. Sometimes you need to use hints. Oracle / PostgreSQL / what have you, are not perfect nor are developers writing the SQL.

Is your customer going to wait for the next version of Oracle? Is your dev team going to fix some sev 3 performance bug?

Hints allow you to use your brain to help the optimizer. Why would you make a hard rule not to use them? Why else are they there?

I won't speak for Oracle because it's always been a "if you have the money you can get a custom patch for the behavior you want" game.

But for Postgres, you contact the developers. If your problems are costing you so much money why wouldn't you just hire a core developer on the Postgres team or hire EnterpriseDB on contract to work on your issues? They'll fix the bug, upstream the fix, and roll you updated packages you can push out to your servers. Not entirely different than working with Oracle, except you are working in the open source world.

Hints are bad. Next time you upgrade your database you may end up with worse performance due to other changes in the query planner. Once your data crosses a certain size threshold it may also start performing worse. They are unpredictable. You will never know when they could break. Using them is the wrong solution.

I agree with paragraph three. A hint is a directive. Now we are stuck with it. Bad stuff can ensue...maybe. But, maybe my customer is pissed now. Total cost to fix: a couple hours.

If I had the budget and developers to make PostgreSQL as performant as Oracle, I would be living on an island next to Larry.

EDIT: Never mind, I read too much into what another commenter wrote.

No, this is all from MySQL Community Edition.

Then what about (https://news.ycombinator.com/item?id=15172965)?

Does that comment say I'm listing features only available in MySQL Enterprise Edition?

It does say that one feature you’re listing is not really sensible to use without the Enterprise Edition, but you’re right, it does not say that. Original comment redacted.

It is, in fact sensible without Enterprise Edition. The only thing it says is that page-level encryption in Community Edition is not intended as a regulatory compliance solution. Nothing more, nothing less.

Even in case you care about regulatory compliance, there are other options besides Enterprise Edition.

What are the other options, and when would page-level encryption in Community Edition be useful?

If I'm understanding it correctly, page-level encryption is to protect against someone gaining access to the DB server and copying the files off it. If the key is stored in a file on this DB server, I might as well not bother encrypting the pages.

master-master is evil.

Not sure of your other points, i have no need for them.

What i really don't like on MySQL (and i don't care for a lot of other stuff): MySQL 5.6 (yeah don't forget your version numbers) really sucks for queries with or.

It sucks to write 2 selects with an union which is 100x faster than the same query with one select and ONE or.

I have been curious to ask someone who is knowledgeable about databases/MySQL: Spanner has a feature for creating interleaved tables, which helps data locality when performing joins on a distributed database. [1] Is this an important feature for a large database, and does MySQL provide anything similar?

[1] https://cloud.google.com/spanner/docs/schema-and-data-model#...