Earlier quoted context omitted.
MySQL (then Vitess) ran Youtube, but nowadays I do believe most product teams are using Spanner.
Yeah I think (heard anecdotally) both google/YouTube and Facebook (and many others) started with MySQL. Spanner for distributed writes has inspired most implementations although Google is the only one I know about that implements TrueTime (atomic clocks). The same year that the Spanner paper came out (after Percolator) an alternate approach (Calvin) was also published, and some of us are using that (our DB's design i…
The part of Postgres we hate the most: Multi-version concurrency control
131–140 of 148 posts
Re: The part of Postgres we hate the most: Multi-version concurrency control
#132Earlier quoted context omitted.
I remember it differently - we needed replication for "hot" backups. At that time, scalability was a major issue - so anyone (including businesspeople) wanted to have a scalable architecture. MySQL spoke to the practical (default install on cPanel hosts, easy replication) and the aspirational (you're going to blow up and need to scale). Digg.com also had a really influential technical team - hearing about how they di…
maybe you were on the more funded side of history in this. As for me, Digg is way after LAMP got solidly plonked into "what I need for a dynamic website on cheap". Essentially, start at 2000-2001 and more and more people going into running websites for all kinds of reasons (forums, blogs, webshops, etc. often hosted on low end offerings)
Re: The part of Postgres we hate the most: Multi-version concurrency control
#133IMHO part of the issue is that Postgres was built on the assumption that snapshot isolation would be widely used. I don't think this has proven to be the case. Snapshot isolation isn't as robust and straightforward as strict serializability, but it also isn't as performant as READ COMMITTED. It seems like the worst of both worlds.
Re: The part of Postgres we hate the most: Multi-version concurrency control
#134Earlier quoted context omitted.
I remember when Uber got roasted by the postgresql mailing list over this: ultimately, a post mortem was done on all of Uber's claims, and it was basically proven that they were incompetent, did not read any available "best practices" guides, did not seek any external help, and treated it like it was some sort of mysql-esque database and used it as wrong as humanly possible. Uber's workload at the time, ironically, w…
That's still the PostgreSQL problem: it has insane defaults. https://www.postgresql.org/docs/current/runtime-config-resou... tells you what all the parameters do, but not why and how to change them. "If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system." Why not set it to 25% of the memory in my system by default, then? "Se…
Re: The part of Postgres we hate the most: Multi-version concurrency control
#135Earlier quoted context omitted.
I remember when Uber got roasted by the postgresql mailing list over this: ultimately, a post mortem was done on all of Uber's claims, and it was basically proven that they were incompetent, did not read any available "best practices" guides, did not seek any external help, and treated it like it was some sort of mysql-esque database and used it as wrong as humanly possible. Uber's workload at the time, ironically, w…
That's still the PostgreSQL problem: it has insane defaults. https://www.postgresql.org/docs/current/runtime-config-resou... tells you what all the parameters do, but not why and how to change them. "If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system." Why not set it to 25% of the memory in my system by default, then? "Se…
That the defaults don't handle top users is hardly an issue.
Re: The part of Postgres we hate the most: Multi-version concurrency control
#136This post has a valid point. But the last line makes it clear why they care so much about it. Yeah, table bloat and transaction ID wraparounds are terrible, but easily avoidable if you follow a few simple guidelines. Typically in my experience, best way to avoid these issues are to set sensible vacuum settings and track long running queries. I do hate the some of the defaults in the Postgres configuration are too con…
> "But making sure that PostgreSQL’s autovacuum is running as best as possible is difficult due to its complexity." The problem, as the article states it, is that a "sensible" vacuum setting for one table is a terrible setting for another depending on how large these tables are. On a 100 million tuple table you'd be waiting 'til there there were 20 million garbage tuples before taking action.
Re: The part of Postgres we hate the most: Multi-version concurrency control
#137One of the weird things about Postgres MVCC is that it is "optimized for rollback," as one person memorably quipped to me. This is not to imply a design principle, it's more a description of how things ended up, and the general argument behind this quip is Postgres lacks "UNDO" segments. On the one hand, this does make the model Postgres uses admirably simple: the WAL is all "REDO," and the heap is all you need to ac…
Coming to Postgres, UNDO logs and no vacuum https://github.com/orioledb/
Re: The part of Postgres we hate the most: Multi-version concurrency control
#138This was a fun read. But now I have a couple of questions 1. Since MySQL keeps delta to save storage costs, wouldn't read and writes slower because now I have to build the full version from the delta 2. On secondary indexes, they highlight the reads will be slower and also say: > Now this may make secondary index reads slower since the DBMS has to resolve a logical identifier, but these DBMS have other advantages in…
1. It's a backward delta. So it's only needed when a transaction touches a row that has been modified by a concurrently running transaction. (Details differing depending on isolation level etc.). Writes can be faster because only the modified attributes need to be written to the delta undo log, not the whole row. I guess reads can be faster too in some cases, e.g. less fragmentation and wasted space (better cache usa…
what is delta undo log?
Re: The part of Postgres we hate the most: Multi-version concurrency control
#139Earlier quoted context omitted.
Coming to Postgres, UNDO logs and no vacuum https://github.com/orioledb/
what is an UNDO log and how does it solve the problem?
Later on, VACUUM has to plow through everything and check the oldest running transaction to see whether the tuple can be "frozen" (old enough to be seen by every transaction, and not yet deleted) or the space reclaimed as usable (deleted and visible to nothing). Index tuples likewise must be pruned at this time.
In systems with an UNDO log, the tuple is mutated in place and the contents of the old version placed into a sequential structure. In the case where the transaction commits, and no existing concurrent repeatable read level transactions exist, the old version in the sequential structure can be freed, rather than forcing the system to fish around doing garbage collection at some later time to obsolete the data. This could be considered "optimized for commit" instead of the memorable "optimized for rollback."
On the read side, however, you need special code to fish around in UNDO (since the copy in the heap is uncommitted data at least momentarily) and ROLLBACK needs to apply the UNDO material back to the heap. Postgres gets to avoid all that, at the cost of VACUUM.
[1] The exception is "HOT" (heap only tuple) chains, which if you squint look a tiny bit UNDO-y. https://www.cybertec-postgresql.com/en/hot-updates-in-postgr...
Re: The part of Postgres we hate the most: Multi-version concurrency control
#140Yup. A lot of heavy users of Postgres eventually hit the same barrier. Here's another take from Uber: https://www.uber.com/blog/postgres-to-mysql-migration/ I had a similar personal experience. In my previous job we used Postgres to implement a task queuing system, and it created a major bottleneck, resulting in tons of concurrency failures and bloat. And most dangerously, the system failed catastrophically under loa…
>> In my previous job we used Postgres to implement a task queuing system, and it created a major bottleneck, resulting in tons of concurrency failures and bloat Yet, every month or two an article about doing exactly this is upvoted to near the top of HN. It can of course work but might hard to replace years later once "barnacles" have grown on it. Every situation is different of course.