mattlord (u/mattlord)

mattlord commented on One million queries per second with MySQL planetscale.com/blog/one-... · Posted by u/aarondf

hbrn · 3 years ago

A relational database without relations is an oxymoron. As folks pointed out, you also have to throw ACID away. So what's left of the original database, SQL-dialect? I bet that gets limited too.

Look, I get it, you have to sell your product. Some folks want semi-infinitely scalable storage, and they don't understand that the only way to achieve it is turning their DB into a key-value store. As a side effect they would have to rewrite their whole application from scratch, but they would only realize it after they get vendor locked in.

You can advertise your solution as MySQL-compatible. And I can claim that it's dishonest.

mattlord · 3 years ago

> A relational database without relations is an oxymoron.

OK. You're the only one talking to this straw man though. :-) Every Vitess user that I'm aware of has a pretty typical 2NF/3NF schema design. A small sampling of them being listed here: https://vitess.io

You setup your data distribution/partitioning/sharding scheme so that you have data locality for 99.9999+% of your queries -- meaning that the query executes against a data subset that lives on a single shard/node (e.g. sharding by customer_id) -- and you live with the performance hit and consistency tradeoffs for those very rare cases that cross shard queries cannot be avoided (Vitess does support this). You should do this even if the solution you're using claims to have distributed SQL with ACID and MVCC guarantees/properties. There's no magic that improves the speed of light and removes other resource constraints. In practice most people say they want perfect security/consistency/<name your desired property here> but then realize that the costs (perf, resources, $$, etc) are simply so high that it is not practical for their business/use case.

I know MySQL fairly well (I started working at MySQL, AB in 2003) and you can certainly claim that "MySQL-compatible" is dishonest but I would offer a counter claim that either you don't know this space very well or you're not operating in good faith here.

mattlord commented on One million queries per second with MySQL planetscale.com/blog/one-... · Posted by u/aarondf

truth_seeker · 3 years ago

Correct me if my knowledge is outdated,

Vitess deployment comes at a cost:

1. You can't have single database view of all shards 2. No distributed ACID 3. No distributed JOINs

If I were to do it in PostgreSQL 14 natively without any other third party plugins, I can get help of Table Partitioning (say root server) + Tables on Foreign Servers (say leaf servers) through FDW. Parallel scans and JOINs are allowed. Also, PostgreSQL 15 (in beta now) will allow parallel commits to foreign server tables through FDW.

mattlord · 3 years ago

> If I were to do it in PostgreSQL 14 natively without any other third party plugins, I can get help of Table Partitioning (say root server) + Tables on Foreign Servers (say leaf servers) through FDW. Parallel scans and JOINs are allowed. Also, PostgreSQL 15 (in beta now) will allow parallel commits to foreign server tables through FDW.

I wish you all the best in operating that at scale and providing a reliable general purpose PostgreSQL DBaaS (even if only internally) around it. :-) PostgreSQL is great, not going to argue otherwise -- every solution has tradeoffs and if the tradeoffs of one solution fit your usage best then go with it. If you need to scale out and manage a large cluster of PostgreSQL instances as if it was a single logical database then I'd recommend something that's in a similar space as Vitess and PlanetScale -- Citus and Citus on Azure respectively. At the hand waving level it's always easy to "do this, and that", but it's a whole other matter when you develop a general purpose production-grade product that anyone can use and operate at virtually infinite scale. :-)

mattlord commented on One million queries per second with MySQL planetscale.com/blog/one-... · Posted by u/aarondf

tiffanyh · 3 years ago

In 2018, folks were getting 4M queries per second using SQLite (BedrockDB) [0].

This was also accomplished from just 1 server, not needing 40 shards like blog post.

[0] https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

mattlord · 3 years ago

The Expensify test was targeted at demonstrating the upper limits of SQLite itself. The hardware used: > 1TB of DDR4 RAM > 3TB of NVME SSD storage > 192 physical 2.7GHz cores (384 with hyperthreading)

And a very simple read-only query w/o overlapping rows, with the full dataset already loaded in memory: > The query itself just sums a range of 10 rows randomly located inside 10B row (447GB) two column database of random integers, one of which is indexed and the other not. This means that the test is fully cached in RAM, and the database is precached prior to the test. No writes are done during this test, only reads.

All that is good and fine for that purpose -- to demonstrate the limits of what SQLite can do in scaling UP.

The purpose of the PlanetScale post was demonstrating linear scalability by scaling OUT using a realistic workload (TPC-C is a warehouse / ecommerce site simulation) on small commodity VMs (ec2 isntances).

As noted, you're comparing two very different tests that had very different goals.

mattlord commented on PlanetScale Insights: Advanced query monitoring planetscale.com/blog/intr... · Posted by u/arjunnarayan

throwusawayus · 4 years ago

aurora serverless pricing is not based on cpu cycles. this is just not how ACUs actually work or scale or are priced, at all man

anyway i gather the answer to my question is that no, there are no other examples of managed sql dbs that bill the way you do. my complaint is this is inherently not transparent because it violates user expectations. users try comparing to io based provders and fail to understand the pricing math comparison (on io pricing 1 read = many rows) or caching implications (on io pricing, cached rows dont count as io)

as for denigrating rds, look to your ceos past hn comments. i would link to it, but last time i did that i got flagged, despite it being a recent thread that i was directly participating in

mattlord · 4 years ago

It's fairly difficult to find actual details on ACUs and how it all works, the best I found after spending significant time looking was things like: https://www.jeremydaly.com/aurora-serverless-the-good-the-ba...

According to AWS you're paying for chunks of CPU and memory on a per second basis: https://aws.amazon.com/rds/aurora/faqs/

It's hard to imagine that the CPU capacity is measured in anything other than CPU cycles (time slices of physical capacity) — in the same way it's hard to imagine that the memory capacity is measured in anything but bytes. But whatever, I don't care. It's cool, good for them. The point was... you don't think you're paying for reads of records that are cached? I give up, I fail to see how this can really be a good faith discussion.

I don't know how all other serverless database offerings do pricing. What difference does it make? They're all different. As a user, you want it to be based on your usage and to be fairly and reasonably priced while also being easily audited and predictable. Those are the key properties I would care about.

I honestly cannot see how you could be missing the point by this much and still be operating in good faith so I'll for real, for real stop. :-)

mattlord commented on PlanetScale Insights: Advanced query monitoring planetscale.com/blog/intr... · Posted by u/arjunnarayan

throwusawayus · 4 years ago

what other managed sql DBs charge based on rows read, regardless of whether they are on-disk or in-memory? honest question. i am familiar with a number of managed mysql and postgres products, and none of them bill this way that i have ever seen

and for the record, despite planetscale staffers repeatedly denigrating rds (your competitor) on hn, aurora’s patch set is not “minimal”

i do think vitess is cool for what its worth. i just think your managed db product has bananas billing and also is horrendously over hyped, and your ceo’s responses to criticism are very reminiscant of theranos or wework’s responses to same

mattlord · 4 years ago

I doubt that anyone would claim their billing metrics are perfect. If you find some specific workload that's actually cheaper on another serverless database offering then we'd love to hear about it (we strive for transparent, generous pricing). If you don't think that CPU usage based pricing — which is typical for serverless offerings and e.g. is what Aurora serverless uses in Aurora Capacity Units (ACUs) — is charging you for reads of cached data then I've got some bad news for you. :-) You're almost certainly being charged for reading the "row" from the network, write-ahead-logging for it and other ACID/MVCC related overhead, writing it to block device, reading it from the block device, reading it from memory, writing it to memory, sorting and comparing [pieces of] it, and writing it back to the network — all of these things take CPU cycles. I find this argument to be entirely missing the point.

Pointing out that surely Amazon would like to keep their patch set to a minimum (there's a high cost in maintaining custom patches as you upgrade MySQL) is in no way implying that their patch set is small. Minimal means the minimum required for what you need, rather than being some point of pride.

I'm certainly not on here bashing any other offerings. Between the two of us, I only see one person trolling / bashing. :-) With that, I will leave you to your opinions which you are of course free to have. Best of luck.

mattlord commented on PlanetScale Insights: Advanced query monitoring planetscale.com/blog/intr... · Posted by u/arjunnarayan

throwusawayus · 4 years ago

tz setup is done with executing a single script. not surprising they could fix quickly. bigger surprise is they forgot to do this before youre support request. generally this is table stakes for managed DB

this is fourth day in a row of planetscale ads^H^H^H blog posts being on hn front page. as i mentioned on yesterdays thread, innodb_rows_read is known to be buggy. regardless, by design it includes cached rows. terrible thing to base billing on. real cloud providers base it on i/o instead since this is more reasonable metric of "use"

planetscale's fork of mysql-server adds only a single commit, which exposes rows_read in an extra place. this from company that keeps talking about "building a database" https://github.com/planetscale/mysql-server

mattlord · 4 years ago

Installing the time zone tables on a single instance is certainly not hard: https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.ht...

The trickier part is orchestrating the ongoing management of that across a large dynamic fleet. And in this case, it was much more than simply loading the tables but about using them to support importing databases into PlanetScale: https://github.com/vitessio/vitess/pull/10102

I'll link to my other comment on the billing issue: https://news.ycombinator.com/item?id=31509240

We've had to do some other changes to our MySQL fork as well that will show up there, but we'd love to not have any patches! We'd love to keep the patch set minimal (just as Amazon certainly does with RDS and Aurora). And I would certainly argue that Vitess, which is what we build PlanetScale around, is a meaningful piece of technology that pairs with MySQL to make a great database: https://vitess.io. You're of course free to disagree — and I wish you all the best as you work to build something great in the future.

mattlord commented on ELT your data with PlanetScale Connect planetscale.com/blog/extr... · Posted by u/WolfCop

throwusawayus · 4 years ago

worse, theyre pricing is based on innodb_rows_read, even includes cached rows.. so every single ELT job costs $$$ based on how many rows you have, even if all in buffer pool cache already

double-worse, innodb_rows_read known to be buggy! example https://jfg-mysql.blogspot.com/2022/03/rows-examined-not-tru... -- this bug is in customer's favor but what if other bugs are not?!

mattlord · 4 years ago

This is at least the second time you've made this exact same comment on an article. So in an attempt to respond to what I will assume are arguments made in good faith...

A database stores and serves requested data. Pricing is based on your usage of this service — how much data are you requesting it to serve in this case. It does not matter if a piece of data is cached or not, it's a database service not a block device service — and certainly not one with an independent cache in front of it that is somehow magically free to operate. Are you insinuating that e.g. Redis should always be free because its keys/records are in memory? I'm sorry, but I fail to see the logic in your argument here.

Of course ELT jobs consume resources. You're using the service, and you're paying based on your usage. This is simply how serverless / usage based models work. Again, I fail to follow the logic here.

innodb_rows_read is a count of the rows that MySQL (the query execution layer) reads from InnoDB (via the storage engine API). This does not count reads from internal / system / data dictionary tables as that is covered by innodb_system_rows_read. It's not "a bug" that it is what it is — InnoDB internally reads (b+ tree) pages and index records (as it uses index organized tables), and that's what happens with Index Condition pushdowns, it can apply query predicates directly as it's examining the index leaf nodes; it's only when pushing results back up to MySQL through the storage engine interface that these InnoDB records get converted to MySQL's generic row format (which includes going from InnoDB's arch independent big endian format to system/little endian format). This metric is telling you exactly what it should (not a bug) — how many rows does MySQL read from InnoDB — there are other handler and InnoDB specific internal metrics that you can use if you want other numbers (e.g. information_schema.innodb_metrics). JFG's blog post was (correctly) noting that if you as an system operator are trying to calculate the full cost of a query then you cannot rely solely on how many rows MySQL reads from InnoDB but instead look at how much work InnoDB does (which is much more challenging as you have b-tree traversal and maintenance, MVCC overhead, prefretching, etc involved) — and ultimately how many system resources are used in total (I/O amplification being one factor with InnoDB's index organized tables and update-in-place model). So parsing and optimizing costs, how many bytes are read from disk, pages from memory, was a temp table used, was a sort file used, etc. Again, this innodb_rows_read metric's intended behavior/meaning is NOT a bug and this metric will always favor the user for usage based billing. Your insinuation that this is somehow buggy and cannot be trusted as a metric so beware... here I would say that you are simply mistaken. This was an intentional decision made to offer simplicity, transparency, and to benefit users (we also go through great efforts to subtract rows read due to internal operations, just as MySQL itself does).

Hopefully this helps to allay your concerns.

mattlord commented on MySQL Group Replication: It’s in 5.7.17 GA mysqlhighavailability.com... · Posted by u/morgo

mattlord · 9 years ago

You can see the changelog here: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-17....

The new product page here: http://www.mysql.com/products/enterprise/high_availability.h...

The documentation chapter here: http://dev.mysql.com/doc/refman/5.7/en/group-replication.htm...

A look at the performance here: http://mysqlhighavailability.com/performance-evaluation-mysq...