MySQL Cluster Database 7: Performance Benchmark

(Note that this is a copy of the original article from Hasham Pathan posted on 21st April 2009).

Summary of Results:

We recently conducted a Performance Benchmark of the newly released version of MySQL Cluster database software version 7.0 using the DBT2 Benchmark. The results are very impressive.

Highlight of the results:

  • For 4 Node Cluster, we achieved 251,000 Transactions per minute which is more than 4X improvement over the MySQL Cluster 6.3 release.
  • For 2 Node Cluster, we achieved 143,000 Transactions per minute which is more than 4X improvement over the MySQL Cluster 6.3 release.
  • Whether a user is looking to take advantage of the latest multi-core, multi-thread server processors, or is seeking to reduce space and energy consumption of their Cluster hardware, MySQL Cluster 7.0 provides a higher throughput, more efficient solution than previous generations of the MySQL Cluster database.

Note that these results were obtained using 1GB Gigabit Ethernet. We expect improved performance for high speed cluster interconnect like InfiniBand and Dolphinics interconnect solutions. Testing using these interconnects is currently underway.

Some of the KEY features of MySQL cluster 7.0 include “ability to add nodes and node groups online” and “Data node multithreading support” You can look at the list of new feature available in MySQL cluster 7.0 here.

Deployment Architecture and Configuration Details:

The topology diagram for 2 Node Scenario

In the case of a 2 node scenario, the data node processes were running on TWO Sun Fire x4450 system with 8 processor cores per data node. The MySQL server nodes were running a combination of Sun Fire x4450 systems and Sun Fire x4600 systems as shown in the deployment diagram below.

 

 

 

 

 

The topology diagram for 4 Node Scenario

In the case of a 4 node scenario, FOUR x4450 system were used to deploy the Data Nodes, each data node using 8 cores. The MySQL Server nodes were running on a combination of TWO Sun Fire x4600, ONE Sun Fire x4240 and FOUR Sun Fire x4450 systems.(co-located with data node systems). Other relevant details are captured in the deployment topology diagram.

 

 

 

 

 

Detailed Results:

Four Data Node Results

Two Data Node Results

Important Notes:

  1. Each MySQL server was bound to 3 processor cores using “processor set” feature(to accommodate more MySQL Servers on the available hardware). It is observed that we get increased TPM/MySQL Server and slight decrease in the response time with 4 processor cores per MySQL server). An example: With 3 processor cores assigned for the MySQL server process, we get the TPM of 12988 for 1 Server, 20 Warehouses. With 4 processor cores, the TPM increases to 19113 with decrease in the response time from 52ms to 34ms.
  2. TPM – New-order transactions per minute. It should be noted that the each transaction comprises of about 30 queries, so average response time per query if calculated would be ~3ms. More details on the “transaction” are available in the TPC-C specification document and DBT2 code base.
  3. The MySQL 6.3 Cluster saturate with fewer number of MySQL servers. For comparison purpose, we ran the tests with equal number of MySQL servers for both MySQL Cluster 6.3 and 7.0

Benchmark Description

Database Test 2 (DBT-2) : DBT2 is an open source benchmark developed by OSDL (Open Source Development Labs ). Significant updates to the benchmark were made to simplify its ability to run with a clustered database such as MySQL Cluster Carrier Grade Edition. DBT2 simulates a typical OLTP (Online Transaction Processing) application that performs transactions with around ten to twenty SQL statements per transaction within five distinct transaction types. The DBT2 benchmark can be used as a good indicator in regards to the expected performance of any application that performs fairly simple transactions and executes these transactions in a repetitive fashion.

For the benchmark results above, DBT2 and MySQL Cluster were configured as an “in memory” database to simulate typical “real-time” database configurations. It should be noted that performance results are being measured as new-order transactions per minute (TPM). The changes made to the DBT2 benchmark are documented and can be found on the SourceForge page for DBT2 downloads. It can also be downloaded from ww.iclaustron.com.

Additional Information:

More information on MySQL Cluster





4 comments

  1. Jahanzaib says:

    Hi Andrew,

    Recently we are going to deploy MySQL 5.5 but we have lack of knowledge and understanding about this new Product.I have some questions about this if you can

    -Is this MySQL Cluster works as Oracle RAC or Microsoft SQL Server

    -What do you suggest against if we go for the Red Hat Cluster vs MySQL Cluster

    -Is this easily Manage as Microsoft Cluster

    Thanks in advanced

    Gratitude,
    Syed Jahanzaib

    • andrew says:

      MySQL Cluster is very different from most clustering solutions in that it’s a distributed, shared nothing architecture and so there’s no need for shared storage.

      While fairly easy to manage, it doesn’t have a management GUI an so you have some typing to do. It is further simplified if you use MySQL Cluster Manager (available for a free 30 day trial from edlivery.oracle.com).

      Andrew.

  2. snake says:

    When testing innodb sysbench-test shows about 800 transactions per second, ndb cluster (running on 3 nodes, 3 VM) gives about 300 transactions per second with complex queries. Why ndb cluster shows lower performance than innodb?

    • andrew says:

      InnoDB has all of the data held within a single machine and further, all of the working data set might be cached in memory – that makes it pretty efficient to run complex queries. On the other hand, MySQL Cluster distributes the data over multiple machines and so there is messaging that has to happen between those machines to satisfy queries – if not optimised correctly then the more complex the query, the more messaging and the higher the latency. This can result in queries being slower on Cluster than on InnoDB. Note that Cluster is more focussed on being able to scale out throughput by adding extra nodes vs. the scale-up approach with InnoDB.

      As with any storage engine, there are of course techniques to improve throughput and latency – here are a couple of good resources to get you started…

      Andrew.

Leave a Reply

Your email address will not be published. Required fields are marked *