something doesn't make sense here - MySQL/InnoDB does put tables into files, but partitions get separate file.
MyRocks has a collection of files per each column family, and when you drop data it can quickly expunge files that don't contain data for other tables/partitions - and trigger compaction on neighbors, if needed.
this misses multiple points - lots of block access happens via internal caches (hyperclock/lru/etc) and filesystem isn't in the critical path as much as you'd think.
files are mapped out in relatively large chunks, especially compaction outputs - there's prealloaction, and usually you will just have a flat file->block conversion without huge trees or anything.
based on performance profiles filesystem doesn't do any heavy lifting, there's not that much fragmentation (and you usually keep some free space for flash GC anyway),
compaction output write is one logical operation on filesystem for tens of megabytes of data.
> filesystems are the unsung heroes underlying some LSM tree databases
Main use cases (social graph, messaging, ..) are on MySQL (and never left it). Storage engine is different, replication is improved, etc, but it is still tracking upstream MySQL tree.
The thing is - PG still relies on physical replication, and staying with logical one allows to use it for out-of-DBMS change data capture and reuse in other systems.
> MediaWiki is ...
Funny tidbit - I tried to move over Wikipedia to PG ages ago, and did the initial prototyping and made it work to a certain degree. But also I learned about InnoDB more at the time.
I'm not sure page level compression can get efficiency of LSM-Tree, and frequent page writes will be a write amplification problem if you end up doing any form of copy-on-write and try to do in-place overwrites.
¯\_(ツ)_/¯ optimizer guy says he did not do good job, shits on optimizer?
There're many reasons to pick MySQL over PG at large scale deployments (economics, replication strategies, etc) - and the fact that some queries will run better on PG may not outweight those benefits.
Don't get me wrong, I know many areas where MySQL sucks, and that is mostly in lacking execution strategies and optimizer problems. Indeed, in many of these ways MySQL is stuck years behind, but in other areas (MyRocks, modern hardware use, etc) it is far ahead of PG.
The thing is, optimizing has costs, and bypassing those costs is useful, if you're looking at economics of your system, and storage engines that pay attention to data patterns are ahead of just naive heaps of data.
It is very simple to diss MySQL when your business does not depend on managing petabytes of data.
P.S. We've migrated a major social network from PG onto our MySQL-based platform and our MySQL DBAs didn't even notice. :-)
I've not got a FB account due to reservations about FB and their business model but that doesn't mean I am above learning from what they do, thanks for the link, I'll have to find info elsewhere though.
I don't think you need a FB account to read the posts there, you can just treat it like a blog with a crappy interface (but very good content). Probably some mandate to "eat your own dog food".
