MySQL Reference Architectures & Sample Data on Dell PowerEdge Systems

Enterprise Solutions

Enterprise Solutions
Enterprise Solutions

Enterprise Solutions - Wiki

MySQL Reference Architectures & Sample Data on Dell PowerEdge Systems

Enterprise Solutions - Wiki

MySQL Reference Architectures & Sample Data on Dell PowerEdge Systems

Applies to:

Window64 bit Operating System

Author:

Carthik Annayan

Problem:

MySQL Reference Architectures & Sample Data on Dell PowerEdge Systems

Solution:

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.

SL No

Component

Details

1

Server

Dell PowerEdge R620

2

Processor

1 Intel Sandy Bridge 2.2Ghz

3

Memory

16GB

4

Storage

Dell EqualLogic PS6100

5

Networking

Dell Power Connect 6248 1Gb Switch

6

Operating System

Windows Server 2008R2

7

Benchmark Tool

Quest Benchmark Factory 6.1

8

Database

MySQLServer 5.5


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.   

  • MySQL Server Nodes:  Enables access& sends request to the clustered data nodes.
  • Data Nodes : Stores all data belonging to MySQL Cluster (48-Nodes Per Cluster max)
  • Management Server Nodes: Handles system configuration at startup and are leveraged when there is a change to the cluster. Maintains cluster configuration information

The Table below shows the Servers used at each layer for this setup.

Component

Server Used

Server Nodes

PowerEdge R620

Data Nodes

PowerEdge R720XD

Management Server

PowerEdge R420

 

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.

 Never:

• 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.

0