7 ms·
Is 20M of rows still a valid soft limit of MySQL table in 2023?
- tbrownaw 3y ago...yes, performance will drop when your set of active data exceeds available memory and your system needs to reference slower storage. I assume this would apply to anything.
- nonethewiser 3y agobut that threshold is surely not generally 20M rows? I mean wouldn't it be completely dependent on how much memory you have?
- irjustin 3y agoit's obviously based on the data size itself, memory available etc etc. Pretty clearly people ran the exact same test, fill a table with a bunch of empty/meaningless data and see where performance degrades - then write a catchy headline title/post. In the end, it's more nuanced than that but i think the overall theme is know your tuning once you get to certain data sizes.
- pollux1997 3y agoThanks for repost the blog! Some crazy guys have already told me that they have mysql table >1.4B rows (~180GiB), underestimated mysql's power lol
- mh- 3y agoMy current employer has a single InnoDB table that is 14-15 TB.
- nonethewiser 3y agowhat is it?
- mh- 3y agoEvents 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).
- numbsafari 3y agoWhat is your mean time to recovery like when you have to restart your system?
- mh- 3y agoWe 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.
- pbreit 3y agoHow many rows?
- deleted 3y ago[deleted]
- bombcar 3y agoThree. But many columns.
- arthurcolle 3y agoTrying to solve the three-body problem numerically? Haha you fools this was done years ago in FORTRAN
- mh- 3y agoSeveral billion.
- Sparkyte 3y agoI bet queries take minutes.
- koolba 3y agoAn indexed read should be no different than on a table with on the order of 100K rows. And that’s even with good ol’ spinning rust. It’s all about logical I/O operations which, for an indexed read, would only be a handful regardless of size as it’s operating on a btree: https://en.m.wikipedia.org/wiki/B-tree https://en.m.wikipedia.org/wiki/B-tree Now creating that index from scratch might take a while though…
- brootstrap 3y agohave worked on 20TB+ tables before. proper schema / index / query, shit is still fast as hell when you do it right
- mh- 3y agoYep, 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.
- duskwuff 3y agoWith MySQL 8.0+, you might not even need pt-osc. A lot of schema changes can be performed online now. https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op...
- SteveNuts 3y agoAnd still my coworkers who 20 years ago were burned by some MySQL minor issue will laugh at the mere suggestion that we could probably use MySQL. People still think it's a toy to this day and they've struggled to shake that perception.
- spudlyo 3y agoHow long does it take you to do a schema change?
- hu3 3y agoBig MySQL deployments typically use pauseless online schema changes tooling like gh-ost: https://planetscale.com/docs/learn/online-schema-change-tools-comparison#trigger-based-vs-triggerless https://planetscale.com/docs/learn/online-schema-change-tool...
- mh- 3y agoThis 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).
- icedchai 3y ago10+ years ago I worked at a smallish company and we had a table with over 500 million rows. This was on a high end system with several spinning rust disks in a raid 10 and 256 gigs of RAM. Billions in modern hardware seems quite reasonable.
- pbreit 3y agoIsn't 20m rows super tiny?
- hedora 3y agoYes. MySQL has been laughing at that for over 20 years (especially if it fit under 2GB, since CPUs used to be 32 bit).
- totalhack 3y agoHaven't had trouble into the low hundreds of millions on a single RDS server with 16GB of ram. YMMV.
- candiddevmike 3y agoIs that web scale?
- bombcar 3y agoYou run MongoDB as a backing store. Very web much scale.
- arthurcolle 3y agoLiterally pays for itself in scale
- weard_beard 3y agoAnyone who wants to get these references: https://www.youtube.com/watch?v=b2F-DItXtZs https://www.youtube.com/watch?v=b2F-DItXtZs
- arthurcolle 3y agoThis is Google: https://www.youtube.com/watch?v=3t6L-FlfeaI&t=12s https://www.youtube.com/watch?v=3t6L-FlfeaI&t=12s I don't even know how to count that low
- arthurcolle 3y ago"Does /dev/null support sharding?" - literally dying over here, so good
- TylerE 3y agoSomeone needs to remake this, just global search and replace “web scale” with “blockchain”.
- Sparkyte 3y agoHardware has limits as well as software. You would need a completely different architecture to accomodate a 20M+ database. Usually schemeless the database overhead is typically what is responsible how many rows it handle and because your client interacts with the MySQL engine it is at limit and performance of that engine. So yeeeee-no. Time to use a different database.
- fastball 3y agoPostgres is also a relational DB and rows per table are "limited by the number of tuples that can fit onto 4,294,967,295 pages"
- Sparkyte 3y agoYep, it is all different. Software and hardware. But what it comes down to isn't the number but the performance and we often have to watch these metrics closely.
- Dylan16807 3y ago> You would need a completely different architecture to accomodate a 20M+ database. The article shows that nothing notable happens at 20M, so what limits are you talking about? One more level of depth on the B+ tree is very easy to deal with.
- daguava 3y ago[dead]
- bagels 3y agoYou should be able to handle a billion for the right (simple) table and hardware. It really depends on the data and queries.
- deleted 3y ago[deleted]
- evanelias 3y agoIn my direct experience, with small rows and a carefully optimized workload and schema, 1 to 2 billion rows was totally fine in a single MySQL 5.1 table (InnoDB) on SSDs twelve years ago. Granted, that was on bare metal. But hardware has also improved a fair bit since then...
- deleted 3y ago[deleted]
- makmanalp 3y agoJust off the top of my head I can think of a few cases where we easily have 100-1000x that on a single table in shared node on kube, with an ebs volume (point being, bog standard, if a tad large, commodity hardware). If you properly index things and your workload isn't too crazy in a way that needs access to all of this at once (e.g. point lookups with some recency bias), classic RDBMSs have this use case covered - and it's achievable by anyone!
- ericbarrett 3y agoLove the article, but one criticism: I've found gp3 to be better than gp2 in all aspects, especially consistency—and it's cheaper. For a long time you could only use RDS with gp2 but I know of no reason to choose it in 2023.
- forgetfreeman 3y agoWhen was this ever a thing?
- dijit 3y agothere 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)
- jerryjerryjerry 3y agoIn 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.
- marginalia_nu 3y agoFWIW, Marginalia Search has had nearly a billion rows in a table. It runs on a single PC with 128 Gb RAM. It's survived several HN death hugs. Though I would say there are things you can do with a 20M table that you can't with a 1B table. If the query doesn't have an index, it will take hours. Even SELECT COUNT(*) takes like 10 min.
- endorphine 3y agoGenerally speaking, why does SELECT COUNT(*) takes so much? I'd expect that the database maintains internal bookkeeping structures with table metadata that contain the number of rows in each table. I reckon this is probably not true? If so, is it because it keeping a counter like that up-to-date would be inefficient Edit: I just realized I might be misunderstanding what that query does
- deleted 3y ago[deleted]
- ddorian43 3y agoLearn about MVCC storage. You might have multiple running concurrent transactions. Which is the "true" row count? Hint: you have to count.
- jl6 3y agoMakes me wonder if anybody would find a SELECT APPROXIMATELY COUNT(*) useful, which would ignore the impact of current transactions.
- qeternity 3y agoMany DB systems have some sort of HLL function to provide a similar approximation (although I think you’re overestimating the costs that MVCC impose on large datasets).
- 3y ago
- davgoldin 3y agoWe've got MySQL 8 running on high-end-ish consumer hardware, with 150 tables ranging from thousands to billions of rows. Largest table is a bit over 5 billion rows and 520GiB. Parallel network backup takes about 3 hours.
- hashim 3y agoWhat does that table do?
- jve 3y agoI see no problems of benchmark apart from you'd be avoiding table scans in prod for tables of those sizes - that kind of benchmark kind of pointless. Well, it shows a point, but not something you would want to use anyway. Once data is cached, using indexed lookups are fast, 0.5ms.
- encoderer 3y agoWe get sub-ms response time on a table with over 250 million rows. We have a covering index for all high-volume queries, running on rds (aurora) with 16gb ram.
- frodowtf 3y agoDo you need to do aggregations/scans on the table as well or is it mostly direct row accesses?
- encoderer 3y agoYeah, we do a lot of those and they do take a lot longer, and with much wider variance. On the order of 500ms-5s. These are all done async so it’s not a problem but we have begun dual writing to clickhouse to get us out of the aggregations business.
- AtlasBarfed 3y agoB+ trees scale vertically very well, until they don't.
- whalesalad 3y agovery cool repo from the author full of all kinds of tests and scripts: https://github.com/gongyisheng/playground/blob/main/mysql/row_test/select_test.py https://github.com/gongyisheng/playground/blob/main/mysql/ro... might need to fork/run this on psql to compare performance.
- KingLancelot 3y ago[dead]
- grumple 3y agoYou can safely go to a billion rows. Use indexes. Don't do silly things like count(*) or select *, select the small data sets you need using your indexes. If you need a full table count for some reason, use a primary key that auto increments and select the max, or use information_schema for an estimate. Nobody will sweat the slight inaccuracy on a table that size.
- Guest9081239812 3y agoAgreed. I have a handful of tables with over 2 billion rows and have experienced no issues. One database is 2.5TB in size. I feel like 20 million rows is a relatively small MySQL table.
- mensetmanusman 3y agoWhat types of data with over two billion rows?
- Guest9081239812 3y agoIt's related to user activity, along the lines of products a user views. The other large tables in the billions of rows are about analyzing how products are performing on other websites over time, helping to determine past and current trends for businesses.
- grumple 3y agoWe have two tables at nearly 2 billion rows for line items on invoices (we aggregate data for a substantial portion of a major industry). We have ~20 tables that are over 100 million rows but less than 1 billion. Looks like another ~20 tables over 20 million. To get this info on your own mysql db: `select * from information_schema.TABLES;`. As previously disclaimed, the TABLE_ROWS here are an estimate generally, see https://dev.mysql.com/doc/mysql-infoschema-excerpt/5.7/en/information-schema-tables-table.html https://dev.mysql.com/doc/mysql-infoschema-excerpt/5.7/en/in... (same in MySQL 8; we use both 5.7 and 8)
- pollux1997 3y ago
- andrewmcwatters 3y agoI love real meat articles like this where software engineers actually explain why particular limits exist, or why technical things are they way they are. Good read. Thanks for sharing.