4 ms·

Thanks 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

by pollux1997 3y ago

mh- 3y ago
My current employer has a single InnoDB table that is 14-15 TB.
- nonethewiser 3y ago
  what is it?
  - mh- 3y ago
    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).
    - numbsafari 3y ago
      What is your mean time to recovery like when you have to restart your system?
      - mh- 3y ago
        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.
- pbreit 3y ago
  How many rows?
  - deleted 3y ago
    [deleted]
  - bombcar 3y ago
    Three. But many columns.
    - arthurcolle 3y ago
      Trying to solve the three-body problem numerically? Haha you fools this was done years ago in FORTRAN
  - mh- 3y ago
    Several billion.
- Sparkyte 3y ago
  I bet queries take minutes.
  - koolba 3y ago
    An 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 ago
    have worked on 20TB+ tables before. proper schema / index / query, shit is still fast as hell when you do it right
    - mh- 3y ago
      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.
      - duskwuff 3y ago
        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-operations.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op...
        SteveNuts 3y ago
        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.
- spudlyo 3y ago
  How long does it take you to do a schema change?
  - hu3 3y ago
    Big 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 ago
      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).
icedchai 3y ago
10+ 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.

Thanks 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 ago

My current employer has a single InnoDB table that is 14-15 TB.

nonethewiser 3y ago
what is it?
- mh- 3y ago
  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).
  - numbsafari 3y ago
    What is your mean time to recovery like when you have to restart your system?
    - mh- 3y ago
      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.
pbreit 3y ago
How many rows?
- deleted 3y ago
  [deleted]
- bombcar 3y ago
  Three. But many columns.
  - arthurcolle 3y ago
    Trying to solve the three-body problem numerically? Haha you fools this was done years ago in FORTRAN
- mh- 3y ago
  Several billion.
Sparkyte 3y ago
I bet queries take minutes.
- koolba 3y ago
  An 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 ago
  have worked on 20TB+ tables before. proper schema / index / query, shit is still fast as hell when you do it right
  - mh- 3y ago
    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.
    - duskwuff 3y ago
      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-operations.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op...
      - SteveNuts 3y ago
        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.
spudlyo 3y ago
How long does it take you to do a schema change?
- hu3 3y ago
  Big 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 ago
    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).

icedchai 3y ago

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