Pushed down JOINs - Webinar

Tweet On Thursday, November 04, at 0900PST/1700CET/1600GMT there is a webinar about Pushed Down Joins. This webinar will explain how Pushed Down Joins works, and some performance numbers.
Register here: http://mysql.com/news-and-events/web-seminars/display-583.html.

NDB Pushed JOINs means query shipping instead of data shipping and it reduces drastically the network hops between the MySQL Server and data nodes, which in turn gives a tremendous performance improvement. For particular queries a 180x improvement has been measured.

MySQL Cluster - Performance (UPDATE on PK) - >120K tx/sec

Tweet This post follows on the previous post on SELECT performance. In this post I want to show three things:

1. How many single row UPDATEs per second you can do on on a Cluster with two data nodes (updating 64B data by the PRIMARY KEY, no batching)
   
2. Show how MySQL Cluster scales with threads and mysql servers
3. How ndb_cluster_connection_pool affects performance

Next post will be what happens to INSERTs.

Setup

• two data nodes
• one to four mysql servers
• interconnected with Gig-E (single NIC)
  

deployed on six computers (of varying quality, but not really modern, see below). www.severalnines.com/bencher was co-located with each mysql servers to drive the load. The inserts are non batched and looks like:

UPDATE t1 SET data1='64B of data' WHERE id=[random];

Table looks like:

CREATE TABLE `t1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data1` varchar(512) DEFAULT NULL,
`data2` varchar(512) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster

The configuration was generated using www.severalnines.com/config with the following options:

• MySQL Cluster 7.1.7 (src)
• Cluster Usage: Option 3) High read/High write
• Cores: 8
• Multi-connection: 16 (so that i later could easily change between 1,8,16 connections in the pool)
• DataMemory=2500M

Hardware

• Data nodes deployed on: 2x4 cores Intel(R) Xeon(R) CPU E5345 @ 2.33GHz
  
• 2 mysql servers deployed on: 2x2 cores with Intel(R) Xeon(R) CPU 5160 @ 3.00GHz
• 2 mysql servers deployed on: 2x4 cores with Intel(R) Xeon(R) CPU E5345 @ 2.33GHz

Results

A few notes:

• conn= 8 means ndb_cluster_connection_pool=8
• 1 app means one pair of bencher + mysqld co-located one server
• With identical HW (2x4 cores) for the mysql server 130K updates per second should be possible.
• Data nodes are quite loaded at 4 applications (from 64 - 128 threads), and the TC peaks at 86% CPU util.
• The network was not saturated (about 200Mb/s each for TX and RX between the data nodes)
  
• Latency numbers - i have them if someone is interested.
  

How to improve this more:

• More cores, faster cores
• more mysql servers (to scale out more)
• eventually more data nodes
• inifiniband
• use ndbapi (nosql)