Earlier quoted context omitted.
My current employer has a single InnoDB table that is 14-15 TB.
I bet queries take minutes.
have worked on 20TB+ tables before. proper schema / index / query, shit is still fast as hell when you do it right
Is 20M of rows still a valid soft limit of MySQL table in 2023?
51–60 of 86 posts
Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?
#52Earlier quoted context omitted.
Events that are enriched off of other data in the same DB. The system design predates me, but it is solid (albeit difficult to operate at this scale - usual stuff like schema changes, replication bootstrapping).
What is your mean time to recovery like when you have to restart your system?
We keep hot masters (typical MySQL master-master-slave(s) setup) on standby.
Bootstrapping one from scratch (like if we need to stand up a new replica)? We restore a disk image on GCP from point in time snapshots, then let it catch up on replication. So it'll depend on how far behind it is when it comes online.
TLDR: a few hours in the worst case. ~Zero downtime in practice.
Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?
#53Earlier quoted context omitted.
I bet queries take minutes.
have worked on 20TB+ tables before. proper schema / index / query, shit is still fast as hell when you do it right
Yep, was just sharing this experience in the comments as well. By far the most painful part is schema migrations, and that's well-solved with pt-osc or gh-osc.
Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?
#54When was this ever a thing?
there used to be some hard and fast rules about scaling vertically in sysadmin circles.
You can see where they come from when you consider some pretty static numbers in systems: like that page sizes are 4k, block sizes are usually pretty standard (512b or 4k) and network interfaces (at least the throughput) haven't increased for a decade or more.
Some of those rules need to be challenged as the technologies or systems change over time. 20M rows in a highly updated table should be an old "rule" though, at the latest from 2010, when mech drives were popular, because having your entire table index fitting on a few sectors on physical disk made a pretty substantial performance difference. Indexes need to flush to disk on update. (or they used to)
Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?
#55Earlier quoted context omitted.
Literally pays for itself in scale
Anyone who wants to get these references: https://www.youtube.com/watch?v=b2F-DItXtZs
This is Google: https://www.youtube.com/watch?v=3t6L-FlfeaI&t=12s
I don't even know how to count that low
Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?
#56Earlier quoted context omitted.
How long does it take you to do a schema change?
Big MySQL deployments typically use pauseless online schema changes tooling like gh-ost: https://planetscale.com/docs/learn/online-schema-change-tool...
This is what we do. So "how long" is a difficult thing to put a number on, since you're intentionally throttling it to avoid impacting the database's performance (or building up slave lag).
Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?
#57Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?
#58Haven't had trouble into the low hundreds of millions on a single RDS server with 16GB of ram. YMMV.
We had about half a billion rows in one table back in 2008, and doing thousands of updates/inserts/deletes per second. Can't remember how much RAN we had, but we sure did have a few spinning disks.
Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?
#59In general, NewSQL (distributed database) may still be a good solution to address scalability limit encountered by a single server/node database like MySQL/PG. Currently, there are quite a few options in this domain: Spanner, PG-compatible (CockroachDB, YugaByte), MySQL-compatible (TiDB, MariaDB xPand), and other middleware products to handle sharding.