{"id":60371,"date":"2021-09-27T09:02:13","date_gmt":"2021-09-27T16:02:13","guid":{"rendered":"https:\/\/github.blog\/?p=60371"},"modified":"2022-03-02T09:44:58","modified_gmt":"2022-03-02T17:44:58","slug":"partitioning-githubs-relational-databases-scale","status":"publish","type":"post","link":"https:\/\/github.blog\/engineering\/infrastructure\/partitioning-githubs-relational-databases-scale\/","title":{"rendered":"Partitioning GitHub\u2019s relational databases to handle scale"},"content":{"rendered":"

More than 10 years ago, GitHub.com started out like many other web applications of that time\u2014built on Ruby on Rails, with a single MySQL database to store most of its data.<\/p>\n

Over the years, this architecture went through many iterations to support GitHub’s growth and ever-evolving resiliency requirements. For example, we started storing data for some features (like statuses<\/a>) in separate MySQL databases, we added read replicas to spread the load across multiple machines, and we started using ProxySQL to reduce the number of connections opened against our primary MySQL instances.<\/p>\n

Yet at its core, GitHub.com remained built around one main database cluster (called mysql1<\/code>) that housed a large portion of the data used by core GitHub features, like user profiles, repositories, issues, and pull requests.<\/p>\n

With GitHub\u2019s growth, this inevitably led to challenges. We struggled to keep our database systems adequately sized, always moving to newer and bigger machines to scale up. Any sort of incident negatively affecting mysql1<\/code> would affect all features that stored their data on this cluster.<\/p>\n

In 2019, in order to meet the growth and availability challenges we faced, we set a plan in motion to improve our tooling and our ability to partition relational databases. As you can imagine, this was a complex challenge necessitating the introduction and creation of various tools as outlined below.<\/p>\n

The result, we see in 2021, is a 50% load reduction on database hosts housing the data that once was on mysql1<\/code>. This contributed significantly to reducing the number of database-related incidents and improved GitHub.com\u2019s reliability for all our users.<\/p>\n

Virtual partitions<\/span><\/a><\/h2>\n

The first concept we introduced was virtual partitions of database schemas. Before database tables can be moved physically, we have to make sure they are separated virtually<\/em> in the application layer, and this has to happen without impacting teams working on new or existing features.<\/p>\n

To do that, we group database tables that belong together into schema domains and enforce boundaries between the domains with SQL linters. This allows us to safely partition data later without ending up with queries and transactions that span partitions.<\/p>\n

Schema domains<\/span><\/a><\/h3>\n

Schema domains are a tool we came up with to implement virtual partitions. A schema domain describes a tightly coupled set of database tables that are frequently used together in queries (such as, when using table joins or subqueries) and transactions. For example, the gists<\/code> schema domain contains all of the tables supporting the GitHub Gist feature–lik\u200be the gists<\/code>, gist_comments<\/code> and starred_gists<\/code> tables. Since they belong together, they should stay together. A schema domain is the first step to codify that.<\/p>\n

Schema domains put clear boundaries in place and expose sometimes-hidden dependencies between features. In the Rails application, the information is stored in a simple YAML configuration located at db\/schema-domains.yml<\/code>. Here\u2019s an example illustrating the contents of that file:<\/p>\n

gists:\n  - gist_comments\n  - gists\n  - starred_gists\nrepositories:\n  - issues\n  - pull_requests\n  - repositories\nusers:\n  - avatars\n  - gpg_keys\n  - public_keys\n  - users\n<\/code><\/pre>\n
A linter makes sure that the list of tables in this file matches our database schema. In turn, the same linter enforces the assignment of a schema domain to every table.<\/p>\n
SQL linters<\/span><\/a><\/h3>\n
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.<\/p>\n
Query linter<\/span><\/a><\/h4>\n
The query linter verifies that only tables belonging to the same schema domain can be referenced in the same database query. If it detects tables from different domains, it throws an exception with a helpful message for the developer to avoid the issue.<\/p>\n
Since the linter is only enabled in development and test environments, developers encounter violation errors early in the development process. In addition, during CI runs, the linter ensures that no new violations are introduced by accident.<\/p>\n
The linter has a way to suppress the exception by annotating the SQL query with a special comment: \/* cross-schema-domain-query-exempted *\/<\/code><\/p>\n
We even built and upstreamed a new method to ActiveRecord<\/a> to make adding such a comment easier:<\/p>\n
Repository.joins(:owner).annotate(\"cross-schema-domain-query-exempted\")\n# => SELECT * FROM `repositories` INNER JOIN `users` ON `users`.`id` = `repositories.owner_id` \/* cross-schema-domain-query-exempted *\/\n<\/code><\/pre>\n
By annotating all queries that cause failures, a backlog of queries needing modification can be built. Here’s a couple approaches we often use to eliminate exemptions:<\/p>\n
    \n
  1. Sometimes, an exemption can easily be addressed by triggering separate queries instead of joining tables. One example is using ActiveRecord<\/code>‘s preload<\/a><\/code> method instead of includes<\/a><\/code>.\n
    Another challenge is has_many :through<\/code> relations that lead to JOIN<\/code>s across tables from different schema domains. For that, we worked on a generic solution that got upstreamed to Rails as well<\/a>: has_many<\/code> now has a \u200bdisable_joins<\/code>\u200b option that tells Active Record not to do any JOIN<\/code> queries across the underlying tables. Instead, it runs several queries passing primary key values.<\/p>\n<\/li>\n
  2. \n
    Joining data in the application instead of in the database is another common solution. For example, replacing INNER JOIN<\/code> statements with two separate queries and instead performing the “union” operation in Ruby (for example, A.pluck(:b_id) & B.where(id: ...)<\/code>).<\/p>\n
    In some cases, this leads to surprising performance improvements<\/em>. Depending on the data structure and cardinality, MySQL’s query planner can sometimes create suboptimal query execution plans, whereas an application-side join has a more stable performance cost.<\/p>\n<\/li>\n<\/ol>\n
    As with almost all reliability and performance-related changes, we ship them behind Scientist experiments<\/a> that execute both the old and new implementations for a subset of requests, allowing us to assess the performance impact of each change.<\/p>\n
    Transaction linter<\/span><\/a><\/h4>\n
    In addition to queries, transactions are a concern as well. Existing application code was written with a certain database schema in mind. MySQL transactions guarantee consistency across tables within a database. If a transaction includes queries to tables that will move to separate databases, it will no longer be able to guarantee consistency.<\/p>\n
    To understand which transactions need to be reviewed, we introduced a transaction linter. Similar to the query linter, it verifies that all tables which are used together in a given transaction belong to the same schema domain.<\/p>\n
    This linter runs in production with heavy sampling to keep the performance impact at a minimum. The linting results are collected and analyzed to understand where most cross-domain transactions happen, allowing us to decide to either update certain code paths or adapt our data model.<\/p>\n
    In cases where transactional consistency guarantees are crucial, we extract data into new tables that belong to the same schema domain. This ensures they stay on the same database cluster and therefore continue to have transactional consistency. This often happens with polymorphic tables<\/em> that house data from different schema domains (for example, a reactions<\/code> table storing records for different features like issues, pull requests, discussions, etc.)<\/p>\n
    Moving data without downtime<\/span><\/a><\/h2>\n
    A schema domain that is virtually isolated is ready to be physically moved to another database cluster. To move tables on the fly, we use two different approaches: Vitess, and a custom write-cutover process.<\/p>\n
    Vitess<\/span><\/a><\/h3>\n
    Vitess<\/a> is a scaling layer on top of MySQL that helps with sharding needs. We use its vertical sharding feature<\/a> to move sets of tables together in production without downtime.<\/p>\n
    To do that, we deploy Vitess’ VTGate<\/a> in our Kubernetes clusters. These VTGate processes become the endpoint for the application to connect to, instead of direct connections to MySQL. They implement the same MySQL protocol and are indistinguishable from the application side.<\/p>\n
    The VTGate processes know the current state of the Vitess setup and talk to the MySQL instances via another Vitess component: VTTablet<\/a>. Behind the scenes, Vitess’ table moving feature is powered by VReplication<\/a>, which replicates data between database clusters.<\/p>\n
    Write-cutover process<\/span><\/a><\/h3>\n
    Because Vitess\u2019 adoption was still in its early stages at the beginning of 2020, we developed an alternative approach to move large sets of tables at once. This mitigated the risk of relying on a single solution to ensure the continued availability of GitHub.com.<\/p>\n
    We use MySQL’s regular replication feature to feed data to another cluster. Initially, the new cluster is added to the replication tree of the old cluster. Then a script quickly executes a series of changes to effect the cutover.<\/p>\n
    \"\"
    The MySQL database cluster setup before executing the write-cutover process<\/figcaption><\/figure>\n
    Before running the script, we prepare the application and database replication so that a destination cluster called cluster_b<\/code> is a sub-cluster of the existing cluster_a<\/code>. ProxySQL<\/a> is used for multiplexing client connections<\/a> to MySQL primaries. The ProxySQL instance on cluster_b<\/code> is configured to route all traffic to the cluster_a<\/code> primary. The use of ProxySQL allows us to change database traffic routing quickly and with minimal impact on database clients\u2014in our case, the Rails application.<\/p>\n
    With this setup, we can move database connections to cluster_b<\/code>  without splitting anything. All read traffic still goes to hosts replicating from the cluster_a<\/code> primary. All write traffic remains with the cluster_a<\/code> primary too.<\/p>\n
    In this situation, we run a cutover script executing the following:<\/p>\n
      \n
    1. Enable read-only mode for the cluster_a<\/code> primary. At this point, all writes to cluster_a<\/code> and cluster_b<\/code> are prevented. All web requests that try to write to these database primaries fail and result in 500s.<\/li>\n
    2. Read the last executed MySQL GTID<\/a> from the cluster_a<\/code> primary.<\/li>\n
    3. Poll the cluster_b<\/code> primary to verify the last executed GTID has arrived.<\/li>\n
    4. Stop replication on the cluster_b<\/code> primary from  cluster_a<\/code>.<\/li>\n
    5. Update ProxySQL routing configuration on cluster_b<\/code> to direct traffic to the cluster_b<\/code> primary.<\/li>\n
    6. Disable read-only mode for the cluster_a<\/code> and cluster_b<\/code> primaries.<\/li>\n
    7. Celebrate!<\/li>\n<\/ol>\n
      After thorough preparation and exercising, we learned that these six steps execute in only a few tens of milliseconds for our busiest database tables. Since we execute such cutovers during our lowest traffic time of day, we only cause a handful of user-facing errors because of failed writes. The results of this approach were better than we expected.<\/p>\n
      Learnings<\/span><\/a><\/h3>\n
      The write-cutover process was used to split up mysql1<\/code>, our original main database cluster. We moved 130 of our busiest tables at once\u2013\u2013those that power GitHub’s core features: repositories, issues, and pull requests. This process was created as a risk-mitigation strategy to have multiple, independent tools at our disposal. In addition, because of factors like deployment topology and read-your-writes support, we didn\u2019t choose Vitess as the tool to move database tables in every case. We anticipate the opportunity to use it for the majority of data migrations in the future though.<\/p>\n
      Results<\/span><\/a><\/h2>\n
      The main database cluster mysql1<\/code>, mentioned in the introduction, housed a large portion of the data used by many of GitHub’s most important features, like users, repositories, issues, and pull requests. Since 2019 we achieved the ability to scale this relational database with the following results:<\/p>\n