How does GitHub improve website performance by migrating MySQL databases?

How does GitHub improve website performance by migrating MySQL databases?

GitHub, as a widely used open-source code library and version control system, has a significant impact on the performance of its database MySQL. Next we will follow the pace of the GitHub infrastructure team to review a major MySQL update made in last August and see how to make GitHub run smoothly.

Task Description

Since last year, we have successively migrated the main architecture of GitHub to a new data center, supporting a world-class hardware and network environment. We hope that this promotion will also improve the performance of MySQL, the cornerstone of the backend system. However, it is not easy to re-build a new server cluster and hardware platform in a new environment. We must make plans and tests to ensure the smooth completion of the migration.

Preparations

Whenever similar major upgrades are required, strict requirements are imposed on each measurement and indicator measurement step. After the operating system is installed for the new machine, you need to test according to different configurations. To obtain the actual load test data, we use tcpdump to capture packets for SELECT queries executed from the old cluster system to the new system.

MySQL performance adjustment is a matter of detail that determines success or failure. For example, the well-known innodb_buffer_pool_size setting has a significant impact on the whole. To fully control the upgrade process, we also analyze and study innodb_thread_concurrency, innodb_io_capacity, innodb_buffer_pool_instances, and other parameters.

During each test, we only change a certain parameter and then keep the system running for at least 12 hours. In this process, we constantly observe the statistical information brought about by show engine innodb status. The SEMAPHORES column can well reflect the workload competition. After the related settings are tested, we will try to migrate one of the largest data tables to a separate cluster. As part of the preliminary test, such migration can provide guidance for larger and more core changes in the future.

In addition to upgrading the basic hardware, we have also optimized the flow process and topology. For example, delayed replication, faster and more frequent backup, and improved the Reading Capability of the backup tape. When everything is ready, it will enter the final upgrade phase.

Create upgrade project list for secondary check

As a platform serving millions of users every day, any errors will be devastating. Before the actual switchover, we listed a list of tasks to ensure the orderly execution of each task:

 

• Ensure that the buffer pool is successfully preheated in the new cluster.
• Announcement and maintenance on Twitter and other social platforms began
• Change the website to maintenance wait Mode
• Wait until all communications with the old MySQL Server are terminated
• Set the old server to read-only
• Remove primary and replicated VIPs data from the old cluster
• Confirm all write operations have been terminated
• Terminate ter1 Replication
• Obtains the ter1 replication location and notifies the current thread
• Reset the replication of cluster1
• Disable the read-only mode of cluster1
• Connect the old cluster to the new cluster1 Cluster
• Deploy applications according to cluster1 connection Configuration
• Make sure that the new connection can pass through the new cluster
• Check the backend tool resque task (workers)
• Perform a phase check and make sure everything works.
• Convert the website to normal mode
• Announcement maintenance on Twitter and other social platforms is completed
• Integrate https://github.com/github/xxxx/pull/xxxto the main page

 

Migration day

At five o'clock A.M. Pacific time on Saturday), after the team members are in place, the migration will officially begin. When you access the website during this period, you will receive the following prompt:

13 minutes later, the new cluster will start to work normally. We terminated the website maintenance mode and told the public that the website will return to normal.

Performance Evaluation

Over the next few weeks, we have been paying close attention to changes in overall performance and response time. The results are gratifying, and the page loading time is reduced by nearly 2/3:

Experience summary:

1. function division

In this operation, we put a large history data record table into a separate cluster. It turns out that this is a wise practice-the storage space and the buffer pool space are released. At the same time, more resources can be placed on active data processing, and the division of the connection logic also allows the program to query between multiple clusters. In the future, we will adopt this method for upgrade.

2. Continuous testing

Rome was built in a non-day manner. The whole process requires continuous acceptance and regression testing to avoid unexpected occurrence.

3. Team strength

Such a major architecture upgrade requires many partners to work together. We mainly use the pull request function on GitHub for interactive communication. Deployment Teams from around the world:

When a pull request is enabled, we will communicate in real time, such as error processing and regression processing. A URL is generated for each communication link to facilitate Historical query and feedback.

One year later ......

Lu yaozhi Ma, a year later, proved that this was a successful operation. The continuous performance of MySQL is as expected, and the system reliability is further improved. There is also an additional benefit: the scalability of the new system is improved, and more large-scale upgrade and transformation will be performed in the future.

GitHub Tutorials:

GitHub tutorials

How to create an organization on GitHub

Usage in GitHub Linux

How to build a GitHub development environment using Eclipse in Windows

Source code for R language 3.0.1 has been submitted to GitHub

Import Maven project to Eclipse4.2 from GitHub

Git tag management details

Git branch management

Git remote repository details

Git local Repository (Repository) Details

Git server setup and Client installation

Git Overview

GitHub details: click here
GitHub: click here

This article permanently updates the link address: