MySQL Single-host multi-instance Solution

Author: skatetime: 2013/03/14 MySQL Single-host multi-instance solution refers to running multiple MySQL database instances on a physical PC server. Why? What are the benefits of doing so?
1. The rapid development of storage technology, Io is no longer a bottleneck
The CPU and I/O resources of ordinary PC servers are not balanced, because the I/O capabilities of disks are very limited. To meet the needs of applications, a large number of servers are often required, in this way, a large amount of CPU resources are wasted. However, the emergence of flash storage technology has changed all of this. The IO capability of a single machine is no longer a bottleneck. You can run multiple MySQL instances on a single machine to improve CPU utilization.
2. MySQL has low CPU usage for multiple cores
MySQL's low utilization of multi-core CPUs has always been a problem. MySQL versions earlier than MySQL 5.1 cannot be linearly expanded when the CPU exceeds 4 cores. Although MySQL has been improving this problem in subsequent versions, including InnoDB plugin and percona xtradb, both of which have improved the utilization of multi-core CPUs, the performance still cannot be improved as the CPU core increases. We currently use dual-channel Xeon servers. A single CPU has 4-8 cores. On the operating system, we can see 16-32 CPUs (each core has two threads ), four-way servers can reach 64-core or even more, so improving MySQL's CPU utilization for multiple cores is an important means to improve performance. Is a test data of percona:

3. Impact of NUMA on MySQL Performance
The PC servers we are using are all NUMA-based and Intel 5600 CPU-based:

there are four NUMA memory allocation policies:
1. default: always allocated on the local node (allocated to the node running the current process);
2. BIND: It is forcibly allocated to a specified node.
3. interleave: distributes data on all or specified nodes.
4. preferred: distribute data on a specified node. If it fails, it is distributed on another node.
the default NUMA memory allocation policy is to prioritize the allocation in the local memory of the CPU where the process is located. This will result in unbalanced memory allocation between CPU nodes, when the memory of a CPU node is insufficient, swap will be generated instead of allocating memory from the remote node. This is the so-called swap insanity phenomenon.
MySQL adopts the thread mode and does not support the NUMA feature. If a single machine runs only one MySQL instance, we can disable NUMA. There are three methods to disable NUMA: 1. hardware layer, which is disabled in BIOS; 2. OS kernel, set NUMA = off at startup; 3. you can use the numactl command to change the Memory Allocation Policy To interleave. Some hardware can be set in BIOS.
if a single machine runs multiple MySQL instances, we can bind MySQL to different CPU nodes and use the bound memory allocation policy to forcibly allocate memory to the current node, this not only makes full use of the NUMA feature of the hardware, but also avoids the low CPU utilization of a single instance of MySQL for multiple cores.

Resource isolation Solution
1. CPU, memory
Numactl-cpubind = 0-localalloc. This command binds MySQL to different CPU nodes. cpubind is a CPU node in the NUMA concept and can be viewed using numactl-hardware, the localalloc parameter specifies the memory as the local allocation policy.
2. Io
We have built a fusionio card (320 GB) in our machine. With the flashcache technology, single-host I/O is no longer a bottleneck, So I/O is shared by multiple instances, i/O resources are not limited. Multiple MySQL instances use the same physical device and are differentiated by different directories.
3. Network
Because a single machine runs multiple instances and the network must be optimized, we use multiple IP addresses to bind multiple MySQL instances to different NICs, this improves the overall network capability. Another more advanced approach is to bind the interrupts of different NICs to the CPU, which can greatly improve the efficiency of the NIC.
4. Why not use virtual machines?
Virtual opportunities consume additional resources. MySQL is an I/O-type application. Using Virtual opportunities greatly reduces I/O performance, and the management cost of virtual machines is relatively high. Therefore, our databases do not use virtual machines.
5. Performance
Is the test data of percona. We can see that the upgrade of running two instances is very obvious.

High Availability Solution
Because a single machine runs multiple MySQL instances, the host-level HA policy, such as heartbeat, cannot be used. Because when a MySQL instance encounters a problem, the entire machine cannot be switched. Therefore, we must change the HA policy at the MySQL instance level. We use the MySQL access layer developed by ourselves to solve the HA problem. When an instance fails, only one instance is switched. For applications, this layer is transparent.
Advantages of MySQL Single-host multi-instance Solution
1. Cost saving. Although flash storage is relatively expensive, if you can reduce the number of machines, considering the cost of saving power and data center usage, it is still cheaper than the single-host single-instance solution.
2. Improve the utilization. By using the NUMA feature, you can bind a MySQL instance to different CPU nodes, which not only improves the CPU utilization, but also solves the problem of MySQL's CPU usage for multiple cores.
3. Improve user experience. Flash storage technology is used to greatly reduce Io response time and improve user experience.

Turn: http://www.hellodb.net/tag/numa

 

--- End ---