Home > Workload Solutions > Oracle > White Papers > MySQL InnoDB Cluster on Dell EMC PowerStore T > Architecture design
This section reviews the major components of the MySQL stack that this solution uses.
The InnoDB Engine is the default for MySQL version 5.6 and later and offers advantages in providing high performance and reliability. Applications that use primary and foreign keys in tables benefit from the InnoDB engine because it arranges table data for key relationships. A column within a table that is designated as a primary key must have only unique data in the column. A column or columns in another table that reference a primary key are called foreign keys. The foreign key can reference the primary key from another table.
Many applications require the use of primary and foreign keys to ensure referential integrity constraints and allow fast updates to specific records. When a row within a table is being updated, only that row is locked. This capability provides very granular locking and reduces lock conflicts. These benefits combined with others such as ACID transactions and automatic crash recovery make the MySQL InnoDB Storage Engine an optimal choice for many applications.
MySQL InnoDB Cluster version 8.x uses group replication to provide fault tolerance for database services. Group replication works by coordinating transaction commits across all the members of the group. Two group replication configurations can be deployed by customers. The first configuration uses a single-primary node, where one server coordinates database updates. The other nodes in the replication group facilitate reads, but not updates. If the primary server fails, an automatic primary election promotes another member in the group to the primary server. The single-primary node group replication was used in this solution.
The second and more advanced group replication mode is a multi-primary node, where all database commits are accepted across all members of the group. In multi-primary node replication, all database servers must uniformly accept a database update for all members of the group to remain consistent. In contrast the single-primary node of group replication depends only on the primary node for accepting database updates.
In the MySQL InnoDB cluster created for this solution, three PowerEdge R740 servers comprised the physical compute infrastructure. PowerEdge R740 servers were selected for the MySQL nodes because they have the processing power and memory capacity to accelerate databases. Each of the three R740s was identically configured with two Intel Xeon Gold 6254 processors, with 18 physical cores and 36 virtual cores for each processor and 1.5 terabytes of memory per server.
The Group Membership service manages the state of each MySQL node in the cluster. In the case of a voluntary or involuntary loss of a node, the Group Membership service initiates a dynamic group reconfiguration. One example of a voluntary exit from the cluster is when the server is temporarily pulled offline for maintenance or upgrading. During the voluntary removal of a node, any database sessions are transparently transferred to the remaining online nodes.
An involuntary removal of a node involves the unexpected loss of a node in the MySQL InnoDB cluster. An example is the failure of network services in one of the database nodes. In this case the group replication detects the removal of the node. The Group Membership service then surveys the remaining nodes to gain agreement on the reconfiguration of the group. If the majority of the nodes agree on the new cluster configuration, MySQL database services continue. If the remaining nodes cannot dynamically change the cluster configuration, the cluster services are suspended until an administrator resolves the issue.
All the PowerEdge servers in the MySQL InnoDB cluster are configured in the same way. Therefore, if a single primary node becomes unavailable, one of the online nodes is promoted to the primary role and it will provide a consistent level of performance. However, if the cluster has one less node, the overall cluster performance will be impacted until the failed node is replaced.
Developers require a separate MySQL database for testing updates as part of the lifecycle management. The MySQL development server in this solution uses less hardware because of its lower fault-tolerance requirements. This solution used a single PowerEdge R640 with two Intel Gold 6240 processors, each featuring 18 physical cores and 36 virtual cores for a total of 1.5 TB of memory for the development server. On this development server, we provisioned a copy of the MySQL InnoDB Cluster as a single database without clustering.
To ensure optimal performance across all the PowerEdge R740 and R640 servers, we set the System Profile BIOS setting to ’Performance.’ When set to a mode other than ‘Custom,’ BIOS will pre-set each option accordingly. The ‘Performance’ setting automatically configures many parameters to maximize performance for databases, including:
For more information about the BIOS settings for Dell EMC PowerEdge Servers, see Setting up BIOS on 14th Generation (14G) Dell EMC PowerEdge Servers.
We used VMware vSphere 7.0 to virtualize each MySQL group replication member. VMware virtualization provided the MySQL team at Dell Technologies with the ability to easily deploy the database nodes and the agility to move virtual machines from server to server using vMotion. VMware vCenter management facilitated the deployment and management activities for the MySQL InnoDB Cluster.
We used only a subset of the available compute and memory resources for each virtualized MySQL group member. Each R740 server had a total of 36 physical cores and, with hyperthreading, 72 virtual cores and 1.5 TB of memory. The three MySQL virtual machines were configured identically with 12 vCPUs and 265 GB of memory. With the minimal configuration used for each MySQL virtual machine, the enterprise has the compute resources to expand performance.
To optimize storage performance, we used paravirtual SCSI controllers. VMware’s paravirtual SCSI controller technology (PVSCSI) optimizes storage performance by enabling more I/O operations and reducing the load on the host processor.Using the PVSCSI when configuring a virtual machine for storage performance has been a long-standing best practice for databases.
With the advent of faster storage technologies such as NVMe and Persistent Memory (PMEM), the default recommendation of using PVSCSI depends on whether the storage array uses these new storage devices. The advantage of the NVMe Controller is that it significantly reduces software overhead for processing guest operating system I/O. A study called “VMware Performance Comparison SCSI Controller and NVMe Controller” shows that, for databases, the NVMe Controller increased IOPS and throughput (MB/s) and lowered latency (ms). If the PowerStore system has NVMe or Intel Optane Storage Class Memory (PMEM), it is recommended to use the new NVMe Controller.
To further optimize VMware for PowerStore performance, we updated the ESXi multipathing configuration. Multipathing uses multiple physical connections between a server and storage array to send data. Multipathing improves performance and delivers greater consistency in the use of storage. Our vSphere native multipathing (NMP) configuration included:
The following table shows the VMware storage configuration for the MySQL nodes. All MySQL nodes were identically configured for storage. For example, each MySQL Node has a DATA disk with the VMware File System (VMFS) using PVSCSI controllers. Disks were not shared across the MySQL nodes.
Controller |
Purpose |
Disk type |
Type |
SCSI 0 |
Guest operating system |
VMFS |
VMware Paravirtual |
SCSI 1 |
MySQL REDO, BINLOG |
VMFS |
VMware Paravirtual |
SCSI 2 |
MySQL DATA |
VMFS |
VMware Paravirtual |
SCSI 3 |
MySQL TEMP, UNDO |
VMFS |
VMware Paravirtual |
The following table provides a high-level description of the software stack used in the MySQL solution. No significant issues were encountered in deploying MySQL on Red Hat Enterprise Linux inside a VMware virtual machine. The companion Deployment Guide to this paper describes the installation steps for setting up MySQL.
Component Name |
Details |
Oracle MySQL |
8.0.21-commercial MySQL Enterprise Server - Commercial |
Oracle MySQL Components |
MySQL Shell 8.0.21-commercial MySQL Enterprise Backup Ver 8.0.21-commercial |
Red Hat Enterprise Linux |
RHEL 8.2, Kernel Version 4.18.0-193.14.3.el8_2.x86_64 |
VMware vSphere |
7.0.0 |
The following table shows a summary of the infrastructure as it relates to the MySQL architecture. The table shows that two PowerEdge R740 servers and one PowerEdge R640 server were used for the MySQL InnoDB Cluster, and one R640 server was used for the MySQL developer database. The PowerStore 1000T supported all the databases in this solution.
Software |
Infrastructure component |
Details |
Oracle MySQL InnoDB Cluster |
PowerEdge R740 |
2 x Intel Xeon Gold 6254 CPU @ 3.10GHz 18C/36T and 1.5 TB of memory |
PowerEdge R740 |
2 x Intel Xeon Gold 6254 CPU @ 3.10GHz 18C/36T and 1.5 TB of memory |
|
PowerEdge R640 |
2 x Intel Xeon Gold 6254 CPU @ 3.10GHz 18C/36T and 1.5 TB of memory |
|
Oracle MySQL Development |
PowerEdge R640 |
Intel Xeon Gold 6240 CPU @ 2.60GHz 18C/36T and 1.5 TB of memory |
MySQL storage |
PowerStore 1000T |
22 NVMe SSD drives each 1.92TB and a total of 28.3 TB of storage |