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.
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.
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.
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.
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.)
HFT, adtech, scientific engineering and many other industries are magnitudes farther ahead.
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>
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.
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.
Restoring a backup takes minutes for multi-TB restores.
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.
1TiB=1,000GiB; if your drives do 300MB/sec, that's still one hour.
A significant number of Oracle shops use Sybase Replication Server to replicate from an Oracle - to an Oracle database.
Go figure...
There are many people who would disagree with that. Talking to Uber engineers would probably be a good idea to dispel that myth.
Nobody is saying MySQL is a bad key-value store!
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.
not a good idea, somebody who let their database master run out of storage space is probably not a good source.
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.
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).
There are reasons to choose PostgreSQL over MySQL (and vice versa), but the article is certainly an example of unfair evangelism.
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.
No. Speaking as an outsider, the Postgres community is about the nicest (and smartest) there is.
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.
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 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.
* 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.
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
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.
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.
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.
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.
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...
> 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?
> - 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).
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.
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."
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!
> - 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.
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?
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.
If I had the budget and developers to make PostgreSQL as performant as Oracle, I would be living on an island next to Larry.
Even in case you care about regulatory compliance, there are other options besides Enterprise Edition.
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.
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.
[1] https://cloud.google.com/spanner/docs/schema-and-data-model#...
Speaking of comparing MySQL and PostgreSQL functionality, I've been spending some time recently on this very topic. Both are great databases with interesting and sometimes unique features. Here are some of the features PostgreSQL is missing as compared to MySQL:
- synchronous multi-master cluster (Galera, Group Replication)
- distributed in-memory grid with automatic sharding (NDB)
- semi-synchronous replication (will be available in PostgreSQL 10)
- built-in logical replication (will be available in PostgreSQL 10)
- built-in event scheduler
- clustered indexes
- declarative partitioning (limited functionality will be available in PostgreSQL 10)
- optimizer hints
- optimizer tracing
- efficient MVCC implementation that is unaffected by XID wraparound problems and VACUUM issues (see also Uber's report on moving from PostgreSQL to MySQL)
- page-level compression
- page-level encryption
- page-level incremental backups
- user variables
- NoSQL client APIs (HandlerSocket, memcached protocol, X protocol)
- virtual columns
- ability to choose data page size on database creation
- ability to specify column order when adding a new column with ALTER TABLE
- write-optimized storage engines similar to MyRocks and TokuDB
All of that may seem small from a developer's standpoint. Those things, however, are huge for people who design and operate the busiest websites on the internet. Which is probably why MySQL still shines in that field.