> -------- Original Message --------
> Subject: Re: Slow session (connection) set-up using MySQLD API
> From: Johan Andersson <johan@stripped>
> Date: Thu, October 20, 2011 3:51 pm
> To: paul@stripped
> Cc: cluster@stripped
> Hi Paul,
> Do you run with one connection to MySQL? Are there any other request
> going on at the same time?
No, I have tested with 20 concurrent threads connecting to MySQL. Both
in "isolated" operation (i.e. no other clients connected to the
cluster), and in "full load" (40,000 update queries per second). The
difference is only marginal. Curiously, with only one thread doing these
connect-select-disconnect cycles, I achieve the "best" performance of 80
per second (while doing 40,000 update queries on a persistent
connection) and 140 per second (no updates at the same time).
> If not (or too few of the other requests) the following might happen:
> * What comes into play with MySQL Cluster is network latency.
> * If you have one connection doing SELECT, you will do only one SELECT
> at a time.
> * Network latency then determines how long time it takes to perform a
If I use the same client to do the same select queries on a persistent
connection (i.e. not each time doing connect and disconnect), it can do
about 1000 queries per second. Still slow compared to the performance of
a "normal" MySQL Server on the same hardware (reaching 5000 queries per
second on a persistent connection).
> Moreover, the data nodes when receiving the request will try to be smart
> and buffer data before sending it on/back.
> It tries to buffer 32KB of data, IIRC. If it cannot fill up 32KB , it
> will wait for more data to process, and if no more data is coming it
> will send send the contents in its send buffer. This extra "wait for
> more data" can take 10ms (unless Oracle has changed this) and the
> results you get sounds like what you describe.
> To overcome this you must simply drive more load on the cluster.
> MySQL Cluster likes a lot of short transaction, happening in parallel.
> If you can have 10 threads to the mysqld doing these SELECTs you will
> most likely see completely other results, unless you have a slow network
> or are swapping, or a wrongly configured cluster.
Network between my test servers are Gigabit, and the test program uses a
configurable number of threads. What puzzles me is that
1) While update queries on a persistent connection on MySQL Cluster
clearly outperform MySQL Server, select queries on a persistent
connection show the reverse.
2) The number of queries per second drops dramatically when the client
disconnects after each query. On a normal MySQL Server there is also a
penalty for the overhead of establishing a session, but it is much
smaller than what I observer on MySQL Cluster. Being able to handle less
than 100 queries per second is not acceptable for our purposes.
Note that I have migrated the privilege tables of MySQL to NDBCLUSTER
(using the appropriate procedures), but even if I start the MySQLD API
nodes with "skip-grant-tables", performance stays as bad as reported
above. Also, the MySQLD's are configured with "skip-name-resolve" to
avoid a DNS lookup bottleneck. Finally, all the tests were performed on
the same hardware, using the same test programs.
Thanks again, Paul
> Johan Andersson
> Severalnines AB
> twitter : http://twitter.com/#!/severalnines
> blog: http://www.severalnines.com
> web: http://www.severalnines.com
> On 2011-10-20 15.31, paul@stripped wrote:
> > Hello,
> > I'm evaluating MySQL Cluster as a replacement for our current "normal"
> > MySQL Server. The current server is processing many "update" queries per
> > second, sent by a client application that always stays connected to the
> > server. On the other hand, the server is serving "select" queries on the
> > same table, using clients that disconnect after each query (i.e.,
> > requests coming from a web server, without connection pooling).
> > In my evaluation I have observed that MySQL Cluster with two data nodes
> > offers a superior "update" performance compared to a MySQL Server
> > instance running on identical hardware. However, performance for the
> > "select" queries is much worse on MySQL Cluster (using MYSQLD as API).
> > Apparently MySQL Cluster has much more "overhead" setting up a session
> > (connection). The difference between MySQL Server and MySQL Cluster is
> > quite dramatic: In our test set-up MySQL Cluster could only server about
> > 65 connect-select-disconnect cycles, whereas MySQL Server can easily
> > handle 1000 and more cycles per second. This is quite a show-stopper for
> > our purposes...
> > Note that all queries are done using the primary key, and all updates
> > and selects work on exactly one row. In total our test table contains
> > 100,000 rows. The MySQL Server version used to test was 5.1 and MySQL
> > Cluster was version 7.2.1 with MySQLD 5.5.
> > Does anyone else has the same experience with MySQL Cluster? Is there
> > any way to improve this connect-select-disconnect cycles?
> > Thanks for your help,
> > Paul