Open-Source MySQL Reference Architectures

Scenarios

Open-source MySQL is a typical OLTP database widely used in Internet database applications. By virtue of its stability and reliability, it has become the most popular open-source relational database in the world. The InnoDB storage engine supports high-concurrency OLTP systems.

OLTP has the following characteristics:

Data generated in the system
Transaction-based processing system
Small amount of data involved in each transaction
Short response time
Large user populations, especially operators
Index-based database operations

Architecture Principles

Figure 1 shows the MySQL architecture.

Figure 1 MySQL system architecture

Table 1 describes the MySQL components.

**Table 1** MySQL components
Component	Description
Connectors	Interact with SQL statements in other programming languages, such as PHP and Java.
Management services and utilities	Implement system management and control.
Connection pool	Manages cache requirements such as user connections and thread processing.
SQL interface	Receives SQL commands from users and returns query results. For example, SELECT ... FROM ... invokes the SQL interface.
Parser	Verifies and parses the SQL commands received. Major functions of the parser are as follows: Decomposes the received SQL statements into a format that can be understood by the MySQL engine and transfers the data for subsequent processing. Then, the SQL statements will be transferred and processed based on this format. If an error occurs in the decomposition process, the SQL statement is invalid and will not be executed.
Optimizer	Determines the most efficient means for executing queries. The database selects the optimal plan from multiple execution plans generated, and returns the results as soon as possible. The optimizer uses the select-project-join (SPJ) strategy to query data.
Caches and buffers	If the data queried is found in the cache, the query statement reads the data from the cache. A cache consists of a series of sub-caches, such as table caches, record caches, key caches, and permission caches.
Storage engines	Interact with files. MySQL uses a pluggable storage engine architecture. It customizes the file access mechanism based on the abstract interface on the file access layer provided by MySQL AB.

Typical Component Architectures

Figure 2 shows the MySQL reference architectures.

Figure 2 MySQL reference architectures

Common reference architectures include the following:

Source-replica replication solution
Linux Virtual Server (LVS) + keepalive + MySQL HA solution
MaxScale read/write splitting and load balancing

When multiple instances are deployed or the distributed architecture is used, you are advised to bind multiple instances to CPUs on each server. This can improve resource utilization and the overall performance.

Parent topic: Architecture