Friday, October 29, 2010

Pushed down JOINs - Webinar

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.

Monday, October 11, 2010

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

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)