Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Is 20M of rows still a valid soft limit of MySQL table in 2023?

yishenggong.com

Post reply on HN
Print view View original

61–70 of 86 posts

Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?

#61

Post by Sparkyte »

fastball wrote: post #25
Sparkyte wrote: post #22

Hardware 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.

Postgres 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"

Yep, 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.

Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?

#62

Post by duskwuff »

mh- wrote: post #53
brootstrap wrote: post #51

Earlier quoted context omitted.

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.

With 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-op...

Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?

#63

Post by SteveNuts »

duskwuff wrote: post #62
mh- wrote: post #53

Earlier quoted context omitted.

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.

With 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-op...

And 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.

Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?

#64

Post by Dylan16807 »

Sparkyte wrote: post #22

Hardware 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.

> 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.

Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?

#65

Post by marginalia_nu »

FWIW, 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.

Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?

#66

Post by endorphine »

marginalia_nu wrote: post #65

FWIW, 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.

Generally 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

Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?

#68

Post by [deleted] »

endorphine wrote: post #66
marginalia_nu wrote: post #65

FWIW, 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.

Generally 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]

Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?

#69

Post by ddorian43 »

endorphine wrote: post #66
marginalia_nu wrote: post #65

FWIW, 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.

Generally 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

Learn about MVCC storage. You might have multiple running concurrent transactions. Which is the "true" row count? Hint: you have to count.

Re: Is 20M of rows still a valid soft limit of MySQL table in 2023?

#70

Post by jl6 »

ddorian43 wrote: post #69
endorphine wrote: post #66

Earlier quoted context omitted.

Generally 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

Learn about MVCC storage. You might have multiple running concurrent transactions. Which is the "true" row count? Hint: you have to count.

Makes me wonder if anybody would find a SELECT APPROXIMATELY COUNT(*) useful, which would ignore the impact of current transactions.
Post reply on HN