9 ms·

Partitioning GitHub’s relational databases to handle scale

236 points by sergiomattei 5y ago

rulionti 5y ago
Interesting insights and refreshing to see large scale providers still relying on „old-school“ tech! What I did not fully grasp - isn’t Vitess it’s own database system built on top of a K/V store similar to e.g. CockroachDB? Here it sounds like they only use pieces of Vitess in front of regular MySQL primaries with sharding? It’s also curious that they sharded based on domains. I assume at some point the QPS on specific domains (e.g. Gist) will be so high that partitioning no longer is effective? edit:// Looks like Vitess is indeed a set of tools on top of MySQL that allow for these type of scaling operations. It is not re-implementation of MySQL using different underlying technologies.
- alexpotato 5y ago
  I remember reading a post from Jeremy Zawodny back in the early 2000s that was basically: "Start virtually sharding your DB data so that when you have to physically shard it, it's a lot easier."
  - baybal2 5y ago
    The whole of Alibaba websites (Aliexpress, TaoBao, Tmall) rely on a single MySQL database for stock count keeping.
    - matesz 5y ago
      Also they developed their own storage engine for MySQL (X-Engine) which is based on LSM data structure and using FPGA to accelerate levels compaction [1], whitepaper [2]. "In addition, FPGA hardware is used to accelerate the compaction process, further maximizing the performance of the system. This marks the first time that hardware acceleration was applied to the storage engine of an OLTP database." [1] https://www.alibabacloud.com/blog/new-insights-into-x-engine https://www.alibabacloud.com/blog/new-insights-into-x-engine... [2] https://dl.acm.org/doi/pdf/10.1145/3299869.3314041 https://dl.acm.org/doi/pdf/10.1145/3299869.3314041
      - baybal2 5y ago
        404
        matesz 5y ago
        https://www.alibabacloud.com/blog/new-insights-into-x-engine-the-new-storage-engine-of-rds-for-mysql_596209 https://www.alibabacloud.com/blog/new-insights-into-x-engine...
- WatchDog 5y ago
  > In addition to vertical partitioning to move database tables, we also use horizontal partitioning (aka sharding). This allows us to split database tables across multiple clusters, enabling more sustainable growth. We’ll detail the tooling, linters, and Rails improvements related to this in a future blog post. They make a clear distinction between "vertical partitioning" and "horizontal partitioning", I think there are probably bigger wins to be had, and probably bigger challenges in the horizontal partitioning than the vertical. All of this work only got them a 2 factor reduction in load, whereas with an ideal horizontal solution, they could scale it infinitely.
- d3nj4l 5y ago
  refreshing to see large scale providers still relying on „old-school“ tech! Somewhat OT but Github is really "old school" - they don't have an SPA frontend and render most things server side the classic Rails way, with lots of UX enhancements coming from judicious use of JS and websockets for interactions. That's one of the things I find very endearing about their platform.
  - mediumdeviation 5y ago
    That approach unfortunately also makes keeping state updates consistent very difficult. Some pet peeves - when reviewing code, if you set up some file filters then scroll down to trigger more files to load, those newly loaded files are not affected by the previously set filters. Or occasionally the page would helpfully offer to reload to show new commits, but clicking that link loses the current white space and file filter settings. SPA frameworks aren’t a silver bullet here, and you can definitely fix these without using one, but these bugs are definitely much harder to make since the DOM is rendered from a single source of truth instead of being patched piecemeal.
    - d3nj4l 5y ago
      That’s a good point. I’ve faced a couple of issues like that doing pure server side rails and mostly dealt with them by not having “infinite” scroll, though I guess that’s not an option in all cases. I think Stimulus can go some way in making experiences like that possible without a full SPA.
- qeternity 5y ago
  Vitess is to MySQL as Citus is to Postgres
  - xdanger 5y ago
    And Microsoft owns Citus and Github.
- tybit 5y ago
  What you’re describing sounds more like TiDB. Like a MySQL flavoured cockroach DB with its own K/V store TiKV.
- paulfurtado 5y ago
  Vitess can be thought of as a very smart MySQL proxy with additional MySQL management features built in. Centralized vtgates proxy to vttablet services which run on the same host as MySQL, the tablet then queries the local MySQL server. On top of this, a lot of magic can be built
fulafel 5y ago
Reading this I wondered why in sharding posts I rarely see mentions of the default textbook solution to transactions in N systems, namely "two-phase commit". Upon searching I now found an informative Dropbox sharding blog post covering it at https://dropbox.tech/infrastructure/cross-shard-transactions-at-10-million-requests-per-second https://dropbox.tech/infrastructure/cross-shard-transactions... .
- rkuska 5y ago
  I believe they don't need 2P commits when transactions don't span multiple partitions. It's explained here https://github.blog/2021-09-27-partitioning-githubs-relational-databases-scale/#transaction-linter https://github.blog/2021-09-27-partitioning-githubs-relation...
  - fulafel 5y ago
    Yes, they've taken that as a constraint and paid a significant engineering and tooling cost.
- aseipp 5y ago
  Because most of these systems in the real world are simply designed, by experience, to not use cross-shard transactions, so 2PC isn't relevant. There's nothing to "mention." It's a cultural choice as much as a technical one, really. Now, "well designed applications do not use 2PC at scale" is a baseline assumption for several reasons. Vitess for example basically follows this principle. 2PC/Cross-shard in Vitess can be done and you could make it faster, but essentially all users and the developers instead take the view that applications designed to scale with Vitess should instead avoid 2PC at the design stage. Why do they take this view? Because their real-world experience is that sharding scales forever and cross-shard transactions don't, no matter the effort. It might scale far, but never "enough." This kind of experience is what drives such a design philosophy. There's of course a feedback loop to all this -- nobody will use 2PC if it's slow, so therefore it's avoided, and nobody will patch it to be fast, because you can just avoid it instead of spending time on that, etc etc. Also, designing for such a system up front is maybe more difficult, or less familiar. But I think it should be noted historically and contextually that this whole push for what is effectively strongly-consistent databases on the WAN where strong transactions won't obliterate performance is a relatively recent trend. You can build something like this bespoke if you're careful but in terms of COTS tools, well, that's been very limited until recently. So the institutional knowledge that can be carried around for engineers is all built around a different set of assumptions, like "don't use 2PC." My own personal experience talking to people "in the wild" (whatever that means) is that the Vitess philosophy is relatively popular at a lot of high-scale places, and probably isn't going away anytime soon. It helps that you can, you know, just use tools like Vitess. But the push for strongly consistent WAN databases (Yugabyte, CockroachDB, etc) isn't slowing down either, so...
xvilka 5y ago
Why not to migrate to the PostgreSQL which can handle more data and load than MySQL or MariaDB? It's also better at partitioning.
- smt88 5y ago
  I'm a huge Postgres fan, but... 1. Migrating is a huge, difficult, expensive project at Github's scale -- almost as bad as a rewrite 2. "Can handle more data and load" doesn't make sense as a blanket statement How much data and load Postgres can handle vs MySQL is determined by lots of variables. It's neither absolute nor consistent. It depends more on database and infrastructure design than on the underlying RDBMS. The best reason to choose Postgres over MySQL is not hardware efficiency but rather developer efficiency, although some projects get both.
  - zip1234 5y ago
    "Changing the tires on a moving vehicle" but the moving vehicle is one of those road trains in Australia with 50 tires.
    - WJW 5y ago
      Wouldn't it actually get easier to move the tires on a vehicle with a lot of them? You can't change the tire on a unicycle while moving, but it would theoretically already be possible on a motorcycle if you can wheelie long enough (the back wheel is more difficult) and each wheel beyond that just adds more stability and redundancy.
- WatchDog 5y ago
  Uber once chose to switch from postgres to mysql, for performance reasons. There was a lot of debate and discussion on HN[0] about it. I think there is enough complexity in either system, that you can't make generalizations that a given system can "can handle more data and load". [0]: https://news.ycombinator.com/item?id=26283348 https://news.ycombinator.com/item?id=26283348
  - 5y ago
smackeyacky 5y ago
Interesting approach, although I think SQL Server could do this kind of stuff natively (i.e. partition based on something easily indexable). Since Microsoft own GitHub, they could give it a try and potentially not go broke on licensing fees like the rest of us would. https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/pa...
- pizza234 5y ago
  As fas as I understand, the partitioning is rooted at the application level, by preventing cross-domain joins: > Building on top of schema domains, two new SQL linters enforce virtual boundaries between domains. They identify any violating queries and transactions that span schema domains by adding a query annotation and treating them as exemptions. If a domain has no violations, it is virtually partitioned and ready to be physically moved to another database cluster Table-level partitioning doesn't help with this (AFAIU), as queries access multiple tables anyway (without app-level changes). The standand db-level feature closest to what they're doing, if somebody "wants to try this at home", is probably tablespacing (or separate dbs, in the next step). I see some inspiration from microservices (separation of models/storages), except that they're (I suppose) keeping the monolith approach.
  - smackeyacky 5y ago
    You can kind of do the same thing with SQL server table partitions but it would largely rely on the original data model being very clean. Depending on how old that code base is, and how experienced the developers were, that might not have been an option I suppose. I am glad it isn't me trying to split it up :-)
- ksec 5y ago
  >Since Microsoft own GitHub, they could give it a try and potentially not go broke on licensing fees like the rest of us would. They are currently contributing to both Vitess and Rails. I am guessing they are also helping / a paying customer of PlanetScale. Which means all the improvement and edgecase are battle tested on Github and upstreamed. I much rather they continue their current path. So others could enjoy the Github Stack. I know HN hate Oracle and MySQL. But generally speaking I think Oracle has been doing a great job in Java and MySQL development.
paxys 5y ago
It seems like every large service which started with a vanilla MySQL setup is now utilizing Vitess for scaling/sharding. Does anyone know how a service like Github would store & backup the Git repo itself? Not in MySQL, I imagine?
- iudqnolq 5y ago
  They use the regular filesystem (via a library version of git: libgit2). It seems crazy that they use the standard off the shelf option but they've contributed a massive amount of work to improving it. https://github.blog/2009-10-20-how-we-made-github-fast/ https://github.blog/2009-10-20-how-we-made-github-fast/
  - sofixa 5y ago
    >It seems crazy that they use the standard off the shelf option but they've contributed a massive amount of work to improving it. Especially considering Gitlab developed Gitaly due to scale and redundancy issues with a regular filesystem.
    - iudqnolq 5y ago
      Skimming search results for that I think GitHub does something similar. It seems Gitaly is an rpc layer between the frontend and git c code running on file servers, no?
- vtbassmatt 5y ago
  How we store them: https://github.blog/2016-04-05-introducing-dgit/ https://github.blog/2016-04-05-introducing-dgit/ (now called Spokes) I don't see anywhere that we've talked publicly about how backups work. If there's interest, I can see if someone wants to write a blog post. Disclosure: I'm the GitHub product manager for Git systems including storage and protocols.
  - pas 5y ago
    > If there's interest There is! Storage, backup & restore at that scale are always interesting :)
redis_mlc 5y ago
> In 2019, mysql1 answered 950,000 queries/s on average, 900,000 queries/s on replicas, and 50,000 queries/s on the primary. Impressive numbers. Busiest master I worked on has been 15,000 qps. (SSD can do 1+ million IOPS and support 2 mysqld server processes at 100,000 qps.) Source: DBA.
- syspec 5y ago
  That's not a source.
qaq 5y ago
On one hand there is a ton of distributed RDBMS like Cockroach, Yugobyte etc. and yet it looks like a good majority of large projects are opting for Vitess. Is coordination overhead so heavy in those systems that they are not viable for these use cases?
- ies7 5y ago
  Cockroach & Yugabyte support Postgresql protocol while Vitess support Mysql.
  - qaq 5y ago
    TiDB is MySQL compatible
- dub 5y ago
  When people choose Vitess over Cockroach, TiDB, etc., it's usually not because they have any reason to think Cockroach wouldn't work. Vitess is just a far simpler, safer, more predictable migration if you're already using mysql and have a ton of legacy code and operational tooling. For a brand new project without legacy code, newer generation databases become an easier sell.
- gregwebs 5y ago
  Normally a head-to-head comparison isn't properly done unless a company has already adopted Vitess and is looking to switch from that to a NewSQL database. Deploying a new database at scale is a lot of work. It's often the case that organizations pick one that has social proof and otherwise makes sense to them and go with that choice if they can make it meet their requirements. Otherwise they might have to triple their workload by comparing three different candidates while their DB is under pressure. And the comparison would likely show that one option is superior in certain ways, but that another option is better for other workloads, etc. Github had already spent effort developing and operating some MySQL coordination tools that they are probably still able to keep using them in their Vitess setup. Generally Vitess has that appeal that it seems to be similar to what you are used to and operationally easier to understand than a NewSQL system. With a NewSQL system the work of this entire blog post is unnecessary (you can still do joins, high availability fail-over is built in), but you have to learn to trust and operate a system that is initially much less familiar. Disclaimer: I work on TiDB.
Gerald4799 5y ago
This allows us to split database tables across multiple clusters, enabling more sustainable growth. We’ll detail the tooling, linters, and Rails improvements related to this in a future blog post. https://www.advancedmd.ltd/ https://www.advancedmd.ltd/
jstx1 5y ago
There's something really cool about doing work at that scale.
technicolorwhat 5y ago
Its almost like applying DDD bounded contexts after the fact.Breaking up your apps in their boundaries and moving them out on the storage level. I do have some questions though: 1. wonder what happens on the edge of the boundaries when a table does need data from another domain. And what if that domain/cluster is down? 2. How do they physically connect to the cluster? A seperate db connection?
black_13 5y ago
Scale another one those silikon valley words
efxhoy 5y ago
Cool, we have a similar setup with dedicated databases for logically separated parts of the app. I work at an online classifieds platform built on rails. For example we have a listing-events database that acts as an event log for our listings. It’s pretty huge so having it on a separate db is great, mainly for rds storage costs.
SkeuomorphicBee 5y ago
Interesting read, I wouldn't think such approach would work for the systems I maintain because my data is not as patitionable as theirs. But when addressing access across domains then they mentioned: > Joining data in the application instead of in the database is another common solution. [...] In some cases, this leads to surprising performance improvements. So, maybe such approach is not impossible for a more heavily connected dataset, although probably prohibitively expensive if you have to re-write most queries into separate-queries+in-app-join.
venthur 5y ago
Does anyone know which linters exactly they were using?
- arthurschreiber 5y ago
  We wrote our own linters that hooked into the `sql.active_record` event emitted by ActiveRecord when a query gets executed. See https://api.rubyonrails.org/classes/ActiveSupport/Notifications.html https://api.rubyonrails.org/classes/ActiveSupport/Notificati... for the API to subscribe to these notifications.
jayd16 5y ago
Why invent virtual partitions when you could use something like multiple databases (as in create database) or maybe even multiple user rights?
- arthurschreiber 5y ago
  As described in the blog post, large paths of our database schema have grown in an organic fashion. These virtual partitions allow us to prepare our database access for splitting out groups of tables into separate clusters in the medium term, with sharding those clusters being a next step and a long term solution to handle our database growth.
  - jayd16 5y ago
    I understand the desire, but why were db users with rights for specific tables or separate databases not enough separation for your interim testing. What did the virtual partitioning add that those other techniques could not provide?
    - arthurschreiber 5y ago
      We could enable the linter in production to silently log problematic queries without actually affecting their execution. If we used separate db users as you're suggesting, any query that we didn't catch beforehand (e.g. via our CI builds) would cause noticeable problems for our users, which is something that we want to avoid. Additionally, switching to a separate user account would require holding open twice the amount of connections to each database server (old db user plus new db user), which probably would be fine but is still a lot of additional connections at our scale.
      - nycjay 5y ago
        Would it double the number of connections? If you are doing the same volume of total work and not looking to increase concurrency, wouldn't you end up with something closer to two connection pools with ~1/2 the size compared to one connection pool with your old size?
tianzhou 5y ago
"In 2019, mysql1 answered 950,000 queries/s on average, 900,000 queries/s on replicas, and 50,000 queries/s on the primary." Glad folks from GitHub shared this story. As a database practitioner managing couple world largest database fleets in the past and see the scale a single db can support, I am 100% convinced that only a very very tiny fraction of application needs sharding. Using "boring" technology to build an innovative product, not the other way around.
- hinkley 5y ago
  You need a better information architecture to handle read replicas than just running a monolithic database. You need a much more complicated/limited one to do sharding. I wonder if people see it as ripping the bandaid off to go straight to sharding. Or perhaps being multitenant introduces that idea. Seems like everyone, or at least the louder ones, discover that they have one 'whale' who gets to be too big even for a single database, or buys one of your other customers, and then where are you?
hinkley 5y ago
I somewhat miss being on smaller teams. I realized recently that my code has been surrounded by backend services for so long that I should probably take SQL off of my list of skills. Even now when I'm 'on the backend' I'm not talking directly to any relational database, just REST calls or KV gets. The area I really want to chew on is partial indexes, now that it's no longer a niche feature. I'd like to have a more informed opinion of how far you can scale a system without resorting to sharding, just by use of more efficient indexes. Especially on read-dominated problem domains.

236 points by sergiomattei 5y ago

Interesting insights and refreshing to see large scale providers still relying on „old-school“ tech! What I did not fully grasp - isn’t Vitess it’s own database system built on top of a K/V store similar to e.g. CockroachDB? Here it sounds like they only use pieces of Vitess in front of regular MySQL primaries with sharding? It’s also curious that they sharded based on domains. I assume at some point the QPS on specific domains (e.g. Gist) will be so high that partitioning no longer is effective? edit:// Looks like Vitess is indeed a set of tools on top of MySQL that allow for these type of scaling operations. It is not re-implementation of MySQL using different underlying technologies.

alexpotato 5y ago
I remember reading a post from Jeremy Zawodny back in the early 2000s that was basically: "Start virtually sharding your DB data so that when you have to physically shard it, it's a lot easier."
- baybal2 5y ago
  The whole of Alibaba websites (Aliexpress, TaoBao, Tmall) rely on a single MySQL database for stock count keeping.
  - matesz 5y ago
    Also they developed their own storage engine for MySQL (X-Engine) which is based on LSM data structure and using FPGA to accelerate levels compaction [1], whitepaper [2]. "In addition, FPGA hardware is used to accelerate the compaction process, further maximizing the performance of the system. This marks the first time that hardware acceleration was applied to the storage engine of an OLTP database." [1] https://www.alibabacloud.com/blog/new-insights-into-x-engine https://www.alibabacloud.com/blog/new-insights-into-x-engine... [2] https://dl.acm.org/doi/pdf/10.1145/3299869.3314041 https://dl.acm.org/doi/pdf/10.1145/3299869.3314041
    - baybal2 5y ago
      404
      - matesz 5y ago
        https://www.alibabacloud.com/blog/new-insights-into-x-engine-the-new-storage-engine-of-rds-for-mysql_596209 https://www.alibabacloud.com/blog/new-insights-into-x-engine...
WatchDog 5y ago
> In addition to vertical partitioning to move database tables, we also use horizontal partitioning (aka sharding). This allows us to split database tables across multiple clusters, enabling more sustainable growth. We’ll detail the tooling, linters, and Rails improvements related to this in a future blog post. They make a clear distinction between "vertical partitioning" and "horizontal partitioning", I think there are probably bigger wins to be had, and probably bigger challenges in the horizontal partitioning than the vertical. All of this work only got them a 2 factor reduction in load, whereas with an ideal horizontal solution, they could scale it infinitely.
d3nj4l 5y ago
refreshing to see large scale providers still relying on „old-school“ tech! Somewhat OT but Github is really "old school" - they don't have an SPA frontend and render most things server side the classic Rails way, with lots of UX enhancements coming from judicious use of JS and websockets for interactions. That's one of the things I find very endearing about their platform.
- mediumdeviation 5y ago
  That approach unfortunately also makes keeping state updates consistent very difficult. Some pet peeves - when reviewing code, if you set up some file filters then scroll down to trigger more files to load, those newly loaded files are not affected by the previously set filters. Or occasionally the page would helpfully offer to reload to show new commits, but clicking that link loses the current white space and file filter settings. SPA frameworks aren’t a silver bullet here, and you can definitely fix these without using one, but these bugs are definitely much harder to make since the DOM is rendered from a single source of truth instead of being patched piecemeal.
  - d3nj4l 5y ago
    That’s a good point. I’ve faced a couple of issues like that doing pure server side rails and mostly dealt with them by not having “infinite” scroll, though I guess that’s not an option in all cases. I think Stimulus can go some way in making experiences like that possible without a full SPA.
qeternity 5y ago
Vitess is to MySQL as Citus is to Postgres
- xdanger 5y ago
  And Microsoft owns Citus and Github.
tybit 5y ago
What you’re describing sounds more like TiDB. Like a MySQL flavoured cockroach DB with its own K/V store TiKV.
paulfurtado 5y ago
Vitess can be thought of as a very smart MySQL proxy with additional MySQL management features built in. Centralized vtgates proxy to vttablet services which run on the same host as MySQL, the tablet then queries the local MySQL server. On top of this, a lot of magic can be built

fulafel 5y ago

Reading this I wondered why in sharding posts I rarely see mentions of the default textbook solution to transactions in N systems, namely "two-phase commit". Upon searching I now found an informative Dropbox sharding blog post covering it at https://dropbox.tech/infrastructure/cross-shard-transactions-at-10-million-requests-per-second https://dropbox.tech/infrastructure/cross-shard-transactions... .

rkuska 5y ago
I believe they don't need 2P commits when transactions don't span multiple partitions. It's explained here https://github.blog/2021-09-27-partitioning-githubs-relational-databases-scale/#transaction-linter https://github.blog/2021-09-27-partitioning-githubs-relation...
- fulafel 5y ago
  Yes, they've taken that as a constraint and paid a significant engineering and tooling cost.
aseipp 5y ago
Because most of these systems in the real world are simply designed, by experience, to not use cross-shard transactions, so 2PC isn't relevant. There's nothing to "mention." It's a cultural choice as much as a technical one, really. Now, "well designed applications do not use 2PC at scale" is a baseline assumption for several reasons. Vitess for example basically follows this principle. 2PC/Cross-shard in Vitess can be done and you could make it faster, but essentially all users and the developers instead take the view that applications designed to scale with Vitess should instead avoid 2PC at the design stage. Why do they take this view? Because their real-world experience is that sharding scales forever and cross-shard transactions don't, no matter the effort. It might scale far, but never "enough." This kind of experience is what drives such a design philosophy. There's of course a feedback loop to all this -- nobody will use 2PC if it's slow, so therefore it's avoided, and nobody will patch it to be fast, because you can just avoid it instead of spending time on that, etc etc. Also, designing for such a system up front is maybe more difficult, or less familiar. But I think it should be noted historically and contextually that this whole push for what is effectively strongly-consistent databases on the WAN where strong transactions won't obliterate performance is a relatively recent trend. You can build something like this bespoke if you're careful but in terms of COTS tools, well, that's been very limited until recently. So the institutional knowledge that can be carried around for engineers is all built around a different set of assumptions, like "don't use 2PC." My own personal experience talking to people "in the wild" (whatever that means) is that the Vitess philosophy is relatively popular at a lot of high-scale places, and probably isn't going away anytime soon. It helps that you can, you know, just use tools like Vitess. But the push for strongly consistent WAN databases (Yugabyte, CockroachDB, etc) isn't slowing down either, so...

xvilka 5y ago

Why not to migrate to the PostgreSQL which can handle more data and load than MySQL or MariaDB? It's also better at partitioning.

smt88 5y ago
I'm a huge Postgres fan, but... 1. Migrating is a huge, difficult, expensive project at Github's scale -- almost as bad as a rewrite 2. "Can handle more data and load" doesn't make sense as a blanket statement How much data and load Postgres can handle vs MySQL is determined by lots of variables. It's neither absolute nor consistent. It depends more on database and infrastructure design than on the underlying RDBMS. The best reason to choose Postgres over MySQL is not hardware efficiency but rather developer efficiency, although some projects get both.
- zip1234 5y ago
  "Changing the tires on a moving vehicle" but the moving vehicle is one of those road trains in Australia with 50 tires.
  - WJW 5y ago
    Wouldn't it actually get easier to move the tires on a vehicle with a lot of them? You can't change the tire on a unicycle while moving, but it would theoretically already be possible on a motorcycle if you can wheelie long enough (the back wheel is more difficult) and each wheel beyond that just adds more stability and redundancy.
WatchDog 5y ago
Uber once chose to switch from postgres to mysql, for performance reasons. There was a lot of debate and discussion on HN[0] about it. I think there is enough complexity in either system, that you can't make generalizations that a given system can "can handle more data and load". [0]: https://news.ycombinator.com/item?id=26283348 https://news.ycombinator.com/item?id=26283348
- 5y ago

smackeyacky 5y ago

Interesting approach, although I think SQL Server could do this kind of stuff natively (i.e. partition based on something easily indexable). Since Microsoft own GitHub, they could give it a try and potentially not go broke on licensing fees like the rest of us would. https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/pa...

pizza234 5y ago
As fas as I understand, the partitioning is rooted at the application level, by preventing cross-domain joins: > Building on top of schema domains, two new SQL linters enforce virtual boundaries between domains. They identify any violating queries and transactions that span schema domains by adding a query annotation and treating them as exemptions. If a domain has no violations, it is virtually partitioned and ready to be physically moved to another database cluster Table-level partitioning doesn't help with this (AFAIU), as queries access multiple tables anyway (without app-level changes). The standand db-level feature closest to what they're doing, if somebody "wants to try this at home", is probably tablespacing (or separate dbs, in the next step). I see some inspiration from microservices (separation of models/storages), except that they're (I suppose) keeping the monolith approach.
- smackeyacky 5y ago
  You can kind of do the same thing with SQL server table partitions but it would largely rely on the original data model being very clean. Depending on how old that code base is, and how experienced the developers were, that might not have been an option I suppose. I am glad it isn't me trying to split it up :-)
ksec 5y ago
>Since Microsoft own GitHub, they could give it a try and potentially not go broke on licensing fees like the rest of us would. They are currently contributing to both Vitess and Rails. I am guessing they are also helping / a paying customer of PlanetScale. Which means all the improvement and edgecase are battle tested on Github and upstreamed. I much rather they continue their current path. So others could enjoy the Github Stack. I know HN hate Oracle and MySQL. But generally speaking I think Oracle has been doing a great job in Java and MySQL development.

paxys 5y ago

It seems like every large service which started with a vanilla MySQL setup is now utilizing Vitess for scaling/sharding. Does anyone know how a service like Github would store & backup the Git repo itself? Not in MySQL, I imagine?

iudqnolq 5y ago
They use the regular filesystem (via a library version of git: libgit2). It seems crazy that they use the standard off the shelf option but they've contributed a massive amount of work to improving it. https://github.blog/2009-10-20-how-we-made-github-fast/ https://github.blog/2009-10-20-how-we-made-github-fast/
- sofixa 5y ago
  >It seems crazy that they use the standard off the shelf option but they've contributed a massive amount of work to improving it. Especially considering Gitlab developed Gitaly due to scale and redundancy issues with a regular filesystem.
  - iudqnolq 5y ago
    Skimming search results for that I think GitHub does something similar. It seems Gitaly is an rpc layer between the frontend and git c code running on file servers, no?
vtbassmatt 5y ago
How we store them: https://github.blog/2016-04-05-introducing-dgit/ https://github.blog/2016-04-05-introducing-dgit/ (now called Spokes) I don't see anywhere that we've talked publicly about how backups work. If there's interest, I can see if someone wants to write a blog post. Disclosure: I'm the GitHub product manager for Git systems including storage and protocols.
- pas 5y ago
  > If there's interest There is! Storage, backup & restore at that scale are always interesting :)

redis_mlc 5y ago

> In 2019, mysql1 answered 950,000 queries/s on average, 900,000 queries/s on replicas, and 50,000 queries/s on the primary. Impressive numbers. Busiest master I worked on has been 15,000 qps. (SSD can do 1+ million IOPS and support 2 mysqld server processes at 100,000 qps.) Source: DBA.

syspec 5y ago
That's not a source.

qaq 5y ago

On one hand there is a ton of distributed RDBMS like Cockroach, Yugobyte etc. and yet it looks like a good majority of large projects are opting for Vitess. Is coordination overhead so heavy in those systems that they are not viable for these use cases?

ies7 5y ago
Cockroach & Yugabyte support Postgresql protocol while Vitess support Mysql.
- qaq 5y ago
  TiDB is MySQL compatible
dub 5y ago
When people choose Vitess over Cockroach, TiDB, etc., it's usually not because they have any reason to think Cockroach wouldn't work. Vitess is just a far simpler, safer, more predictable migration if you're already using mysql and have a ton of legacy code and operational tooling. For a brand new project without legacy code, newer generation databases become an easier sell.
gregwebs 5y ago
Normally a head-to-head comparison isn't properly done unless a company has already adopted Vitess and is looking to switch from that to a NewSQL database. Deploying a new database at scale is a lot of work. It's often the case that organizations pick one that has social proof and otherwise makes sense to them and go with that choice if they can make it meet their requirements. Otherwise they might have to triple their workload by comparing three different candidates while their DB is under pressure. And the comparison would likely show that one option is superior in certain ways, but that another option is better for other workloads, etc. Github had already spent effort developing and operating some MySQL coordination tools that they are probably still able to keep using them in their Vitess setup. Generally Vitess has that appeal that it seems to be similar to what you are used to and operationally easier to understand than a NewSQL system. With a NewSQL system the work of this entire blog post is unnecessary (you can still do joins, high availability fail-over is built in), but you have to learn to trust and operate a system that is initially much less familiar. Disclaimer: I work on TiDB.

Gerald4799 5y ago

This allows us to split database tables across multiple clusters, enabling more sustainable growth. We’ll detail the tooling, linters, and Rails improvements related to this in a future blog post. https://www.advancedmd.ltd/ https://www.advancedmd.ltd/

jstx1 5y ago

There's something really cool about doing work at that scale.

technicolorwhat 5y ago

Its almost like applying DDD bounded contexts after the fact.Breaking up your apps in their boundaries and moving them out on the storage level. I do have some questions though: 1. wonder what happens on the edge of the boundaries when a table does need data from another domain. And what if that domain/cluster is down? 2. How do they physically connect to the cluster? A seperate db connection?

black_13 5y ago

Scale another one those silikon valley words

efxhoy 5y ago

Cool, we have a similar setup with dedicated databases for logically separated parts of the app. I work at an online classifieds platform built on rails. For example we have a listing-events database that acts as an event log for our listings. It’s pretty huge so having it on a separate db is great, mainly for rds storage costs.

SkeuomorphicBee 5y ago

Interesting read, I wouldn't think such approach would work for the systems I maintain because my data is not as patitionable as theirs. But when addressing access across domains then they mentioned: > Joining data in the application instead of in the database is another common solution. [...] In some cases, this leads to surprising performance improvements. So, maybe such approach is not impossible for a more heavily connected dataset, although probably prohibitively expensive if you have to re-write most queries into separate-queries+in-app-join.

venthur 5y ago

Does anyone know which linters exactly they were using?

arthurschreiber 5y ago
We wrote our own linters that hooked into the `sql.active_record` event emitted by ActiveRecord when a query gets executed. See https://api.rubyonrails.org/classes/ActiveSupport/Notifications.html https://api.rubyonrails.org/classes/ActiveSupport/Notificati... for the API to subscribe to these notifications.

jayd16 5y ago

Why invent virtual partitions when you could use something like multiple databases (as in create database) or maybe even multiple user rights?

arthurschreiber 5y ago
As described in the blog post, large paths of our database schema have grown in an organic fashion. These virtual partitions allow us to prepare our database access for splitting out groups of tables into separate clusters in the medium term, with sharding those clusters being a next step and a long term solution to handle our database growth.
- jayd16 5y ago
  I understand the desire, but why were db users with rights for specific tables or separate databases not enough separation for your interim testing. What did the virtual partitioning add that those other techniques could not provide?
  - arthurschreiber 5y ago
    We could enable the linter in production to silently log problematic queries without actually affecting their execution. If we used separate db users as you're suggesting, any query that we didn't catch beforehand (e.g. via our CI builds) would cause noticeable problems for our users, which is something that we want to avoid. Additionally, switching to a separate user account would require holding open twice the amount of connections to each database server (old db user plus new db user), which probably would be fine but is still a lot of additional connections at our scale.
    - nycjay 5y ago
      Would it double the number of connections? If you are doing the same volume of total work and not looking to increase concurrency, wouldn't you end up with something closer to two connection pools with ~1/2 the size compared to one connection pool with your old size?

tianzhou 5y ago

"In 2019, mysql1 answered 950,000 queries/s on average, 900,000 queries/s on replicas, and 50,000 queries/s on the primary." Glad folks from GitHub shared this story. As a database practitioner managing couple world largest database fleets in the past and see the scale a single db can support, I am 100% convinced that only a very very tiny fraction of application needs sharding. Using "boring" technology to build an innovative product, not the other way around.

hinkley 5y ago
You need a better information architecture to handle read replicas than just running a monolithic database. You need a much more complicated/limited one to do sharding. I wonder if people see it as ripping the bandaid off to go straight to sharding. Or perhaps being multitenant introduces that idea. Seems like everyone, or at least the louder ones, discover that they have one 'whale' who gets to be too big even for a single database, or buys one of your other customers, and then where are you?

hinkley 5y ago

I somewhat miss being on smaller teams. I realized recently that my code has been surrounded by backend services for so long that I should probably take SQL off of my list of skills. Even now when I'm 'on the backend' I'm not talking directly to any relational database, just REST calls or KV gets. The area I really want to chew on is partial indexes, now that it's no longer a niche feature. I'd like to have a more informed opinion of how far you can scale a system without resorting to sharding, just by use of more efficient indexes. Especially on read-dominated problem domains.