MySQL Reference Architectures & Sample Data on Dell PowerEdge Systems
Window64 bit Operating System
In this Article we will discuss the reference architectures of single and clustered MySQL Server Databases; Fig1 explains the reference architecture of a single Database Instance. At each layer we used different Dell components. The components used in each Layer are listed below.
Dell PowerEdge R620
1 Intel Sandy Bridge 2.2Ghz
Dell EqualLogic PS6100
Dell Power Connect 6248 1Gb Switch
Windows Server 2008R2
Quest Benchmark Factory 6.1
Fig1: Reference Architecture of a MySQL Server in a Single Instance environment
For the purpose of this reference architecture and tests, a LUN of Size 500 GB was carved out and data was populated for about 250GB using Quest Benchmark factory. The storage attached to this configuration was Dell EqualLogic PS 6100 XV. The network switch used in the configuration is Dell PowerEdge 6248 which is a 1 GB Switch. Graph’s 1&2 displays the Transactions per second and Response time of InnoDB storage engine as a sample reference. The Test Methodology used here is to run a TPC-C test, using Quest Benchmark Factory. Quest benchmark factory is a database performance testing tool that allows you to conduct database workload replay, industry-standard benchmark testing, and scalability testing. Graph’s 1&2 represents the sample Average Response Time and Transactions per second for 1800 concurrent Users. The Response time at 1800 concurrent Users is 0.44ms. You can observe a raise in the response time after 900 users due to the increase in user load and memory usage. The Transactions per second increases proportionate to the user load as expected. In the testing process, we also tested the other storage engine such as the MyISAM storage engine. Note: - The Data Shown in the graph is a sample reference and can vary based on hardware Infrastructure.
Graph1:- Average Response time
Graph2:- Transactions per Second
Now let’s discuss the MySQL Cluster reference architecture. Fig2 below, show the typical reference architecture for MySQL Cluster architecture.
Fig2: Reference Architecture of a MySQL Server in a Cluster environment
MySQL Cluster works on shared nothing architecture, the Storage Engine Used here is NDB or NDBCluster. The MySQL Cluster architecture is split into 3 Layers namely:
a) The MySQL Server Node(mysqld is the server process running on this node)
b) The Data Nodes (ndbd is the server process running on the data node)&
c) The Management Server Nodes (ndb_mgmd is the server process running on the data node).
Since the MySQL Cluster works on a Shared Nothing Architecture, It makes the entire solution a Server Centric, for storing data, running the MySQL Servers and the Management. While you can have all the 3 components in a single server, it is not recommended to have such architecture. However, Such Architecture does not provide any kind of High Availability, or data redundancy. Now let’s discuss the functionality of each of the nodes.
The Table below shows the Servers used at each layer for this setup.
Minimum Configuration Needed: -
4 Computers having:
• 2 Data nodes
• 2 MySQL Servers + • 2 Management Nodes
For a HA in all the layers, Make sure that you have the management servers on separate nodes.
• Co-locate data node and management node
How Does Dell Power Edge Server play a key role, in building a clustered solution such as MySQL Cluster? The answer to this question is, The Dell Power Edge 12th Generation Server R720XD offers huge internal storage (38TB) space, and Memory (786 GB) of memory capacity, suits the Data Nodes. For the MySQL Server Nodes, both the 11th Generation and 12th Generation of Servers such as R620, R720, R610 and R710 are perfect fits for huge user loads. For the Management server nodes, a low end PowerEdge Server should be Serve the purpose. For this deployment we used the Dell PowerEdge R720XD for the Data Nodes, the R620’s for the Server Nodes and Dell PowerEdge R420 server for the Management Node. To summarize, the solutions required to implement a MySQL Single instance Vs. MySQL Cluster varies, due to architectural differences. In Total, the Dell 12th Generation PowerEdge Servers suits the solution/fit for the MySQL Cluster Database as a whole. To conclude, a Step by Step instruction on how to implement a MySQL Cluster will be discussed in the following WikiArticle. Overall, the wiki article, and the Blog should help customers quickly Deploy MySQL Database.
Disclaimer: - Though Dell doesn’t support MySQL on Dell platform but this article should help anyone to quickly deploy MySQL database with Dell Platform.