How to Build a Low-Cost, High-Performance MySQL Cloud Data Architecture

Cao Weiis a member of theTaobao Database R&D Team. Recently, he shared an internal analysis and exploration of a low-cost, high-performance MySQL cloud data architecture, covering its evolution process, system roles, and components. The article was reposted byChu Baon“Erlang Non-Amateur Research”.

At the beginning, Cao Wei pointed out:

Although NoSQL has developed rapidly in the past two years with new products emerging constantly, applying NoSQL in business scenarios demands relatively high expertise from developers. In contrast, MySQL boasts mature middleware and operational tools, having already formed a healthy ecosystem. Therefore, at the current stage, MySQL plays a dominant role, with NoSQL serving as a supplement.

Next, Cao Wei introduced their team’s work:

We (the core systems database team of Alibaba Group)……designed and implemented a UMP (Unified MySQL Platform) system, delivering low-cost and high-performance MySQL cloud data services. Developers apply for MySQL instance resources from the platform and access data through a single entry point provided by the platform. The UMP system internally maintains and manages resource pools, offering a series of services transparent to users, including master-slave hot standby, data backup, migration, disaster recovery, read-write splitting, and sharding. The platform reduces costs by running multiple MySQL instances on a single physical machine, achieves resource isolation, allocates and limits CPU, memory, and I/O resources on demand, and supports dynamic scaling and downsizing without affecting data services, based on the user’s business growth.

Cao Wei analyzed the architectural evolution of the system:

The first version was based on mysql-proxy 0.8, with several bug fixes. We made modifications to the state machine flow managing user connections and database connections within the proxy plugin, and simultaneously wrote Lua scripts to implement logic for fetching user authentication information and backend database addresses from a central database, authenticating users, establishing connections to backend databases, and forwarding data packets.

Figure: UMP System First Version Architecture

He mentioned several issues with the first version:

1. The mysql-proxy 0.8 version’s support for multi-threading was rather crude and brute-force, leading to several severe consequences:
   • Caused a “thundering herd” phenomenon, where multiple threads are awakened but only one thread needs to handle the task;
   • Poor CPU affinity for tasks, as events triggered on the same state machine would switch back and forth between multiple processors for execution;
   • mysql-proxy also uses a global Lua lock, allowing only one worker thread to execute Lua scripts at a time. As a result, the performance of mysql-proxy in multi-threaded mode is far from scaling linearly with the number of CPU cores; in fact, performance on 16 cores can be worse than on 4 cores.
   • Due to the reasons above, in single-process mode, multiple processes must be deployed on a single physical machine to effectively utilize its processing power, which complicates deployment, monitoring, and service upgrades.
2. Secondly, limited by the mysql-proxy framework, extending functionality is not easy. Implementing features such as user connection limits, QPS limits, master-slave switching, read-write splitting, and sharding is relatively difficult.
3. Finally, the mysql-proxy community has been rather inactive in recent years, and the C language demands a high level of expertise from developers, making it difficult to require all team members to collaboratively develop code that balances elegance and correctness.

Therefore, they decided to rewrite it in Erlang, for the following reasons: 

• Compared to operating system processes/threads, Erlang processes are also units of concurrent execution but are exceptionally lightweight. They are “green processes” managed and scheduled within the Erlang virtual machine, i.e., user-space processes.
• Erlang/OTP provides excellent abstractions of the essential elements for developing a distributed, highly fault-tolerant application, including: a network programming framework, serialization and deserialization, fault tolerance, and hot deployment.

When designing the current UMP system architecture, the team followed these principles:

• The system maintains a single entry point externally and a single resource pool internally.
• Ensure high availability of the service and eliminate single points of failure.
• Ensure the system is elastically scalable, allowing for dynamic addition and removal of computing and storage nodes.
• Ensure resources allocated to users are also elastically scalable and isolated from each other.

Figure: Current UMP System Architecture

The UMP system includes the following roles:

• Controller Server: Provides various management services to the UMP cluster, implementing features such as metadata storage, cluster member management, MySQL instance management, failure recovery, backup, migration, and scaling.
• Proxy Server: Provides users with access to MySQL databases, fully implementing the MySQL protocol. Beyond basic data routing, the Proxy Server also implements features like resource limiting, masking MySQL instance failures, read-write splitting, sharding, and recording user access logs.
• Agent Server: Deployed on the machines running MySQL processes, used to manage MySQL instances on each physical machine. It performs operations such as creation, deletion, backup, migration, and master-slave switching, and collects and analyzes MySQL process statistics, bin logs, and slow query logs.
• API/Web Server: Provides users with a system management interface. These are developed based on the open-source projects Mochiweb and Chicago Boss, with Mochiweb providing http/https services.
• Log Analysis Server: Stores and analyzes user access logs passed in from the Proxy server, and implements real-time indexing to allow users to query slow logs and statistical reports over a period of time.
• Statistics Server: Periodically collects user connection counts, QPS values, and MySQL instance process statuses, and uses RRDtool to generate statistics. This data can be displayed as charts on the Web interface and can provide a basis for future implementation of elastic resource allocation and automated MySQL instance migration.

The dependent open-source components include:

• Mnesia: Mnesia is a distributed database provided by OTP. It supports transactions, transparent data sharding, and uses a two-phase lock for distributed transactions, capable of scaling linearly to at least 50 nodes. Mnesia tends to sacrifice availability for strong consistency, but it also provides dirty read and dirty write operations to bypass transaction management when accessing data.
• LVS: Implements load balancing. After a user application reconnects, LVS will direct it to another proxy.
• RabbitMQ: Provides communication between various nodes in the UMP system (excluding the transmission of large data streams like SQL queries and logs, which still go directly over TCP).
• ZooKeeper: Primarily serves as a configuration server, distributed lock, and monitors all MySQL instances.

ForRegarding the system’s role, Cao Wei summarized:

Through the collaborative operation of multiple components, the entire system achieves disaster recovery, read/write splitting, and database/table sharding that are transparent to users. Internally, the system also virtualizes resources to reduce overall costs by having multiple small-scale users share a single MySQL instance, medium-scale users exclusively occupy one MySQL instance, and multiple MySQL instances share the same physical machine. In terms of resource isolation, the UMP system ensures resource virtualization while safeguarding user service quality by combining Cgroup-based MySQL process resource limits with QPS throttling on the proxy server side. Furthermore, the UMP system comprehensively employs technologies such as SSL database connections, data access IP whitelisting, user operation logging, and SQL interception to protect user data security.

Regarding the system’s application, Cao Wei pointed out:

Some components of the UMP system, such as the proxy server and log analysis server, are already being used on Tmall’s Jushita platform, providing secure data cloud services for e-commerce merchants and ISVs. In addition, the UMP system is also used in Taobao’s shop decoration platform, offering data services to developers. In the next phase, we hope the UMP system can contribute to further reducing the internal data storage costs within the group.