MySQL reference architecture from small websites to ultra-large scale websites

Oracle released the MySQL reference architecture for large-scale scalable Website Infrastructure White Paper, which provides a recommended topology for websites of different types and sizes that use MySQL as data storage.

Provides four types of services: user and session management, e-commerce, analysis applications (Multi-structure data), and CMS (metadata) -- the scale and availability requirements of the website (as shown in the following table). This White Paper provides four reference architectures.

Note that the guidelines provided here are only basic suggestions. in actual application, you must adjust the rules based on factors such as the read/write mode, load balancing, and cache mechanism used.

Small (Small) website reference architecture

This reference architecture can be used for all small implementations of the above four types of websites. You can use MySQL Replication to create copies of data to support backup and analysis.

Medium website reference architecture

In this case, we recommend that you select an independent infrastructure for different types of activities. Each MySQL Server supports a maximum of eight application servers. If the number of application servers increases due to scalability requirements, add more MySQL slave servers.

To meet the high availability requirements of session management websites and e-commerce websites, you can use Heartbeat and semi-sync replication in Linux. CMS websites generally have higher requirements for outward scaling of read operations. Assuming that each MySQL slave server can process a maximum of 3000 concurrent users, we recommend that you add 20-30 slave servers to each MySQL master server. The CMS system can store data in a SAN or a distributed device connected to the server.

Memcached is recommended for session management websites and CMS websites, which helps ease the burden on application servers and MySQL servers.

The topology of analysis websites is simpler. One master server and three slave servers can solve the problem.

Large-scale (Large) website reference architecture

For large-scale websites, we recommend that you use MySQL Geographic Replication to replicate databases across data centers. This method supports asynchronous Replication across geographically isolated clusters.

The cluster should be used for session management websites and e-commerce websites. The White Paper claims that "four data nodes can support 6000 sessions in one second (click on a page ), click Generate 8-12 database operations on each page ". The configuration of a large-scale CMS website is similar to that of a medium-sized website, but you need to add more slave servers when necessary. For analysis applications, a Data Refinery unit is introduced for Data cleaning and organization.

Ultra-Large scale (Extra Large) website reference architecture

The White Paper also provides suggestions for social networking websites. It claims that "nine of the 10 websites with the largest network traffic have deployed MySQL, including Google, Facebook, and YouTube", but it does not explain what MySQL is used for these websites, however, we all know that LinkedIn has successfully applied MySQL.

The topology of social networking websites utilizes the concepts implemented in medium-sized and large-scale websites, including dedicated application servers, Memcached, and data extraction units, however, Shard is introduced for external extensions that support write operations ). The MySQL cluster is used for user authentication and search. When "more than one key is used for search", the corresponding Shard is read and written directly.

The recommended specifications for the MySQL master server and slave server are as follows:

• 8-16 x86-64-bit CPU cores (MySQL 5.5 and above ).
• 4-8 x86-64-bit CPU cores (MySQL 5.1 and earlier versions ).
• 3-10 times more memory than the active data.
• Linux, Solaris, or Windows operating system.
• At least 4 disks and 8-16 disks can increase the performance of I/O-intensive applications.
• Supports battery-powered high-speed cache hardware RAID.
• RAID 10 is recommended. If the load is read-intensive, RAID 5 is also suitable.
• Two NICs and two power supply units are used for redundancy.

In addition, the White Paper also provides some suggestions for MySQL clusters and data storage devices, followed by solutions for monitoring, backup, and cluster management.

View Original English text:MySQL Reference ubuntures for Small to Extra Large Websites