Re: Mysql Cluster performance

[Mikael Ronström]

From what I can see the following happens when executing this
query.

NDB runs a scan operation over 1 fragment of the table.
It fetches 786k rows and there are two fields of 20 bytes each
to fetch. So presumably each result row is sent in a message of
around 60-70 bytes. So the single MySQL server thread has to
receive about 50 MBytes of rows and process them.

Given that select count(result) takes 22 seconds, presumably most
of this time is spent in the MySQL Server processing the data.

You can check this by looking at top -H. Most likely you have a
thread from mysqld at the top that is close to 100% CPU bound.
If this is the case then there is not much to do other than as
you mentioned to possibly change data type.

If there isn't any thread that stands out like that, the most
likely scenario is that you have many other queries competing
for CPU resources and thus the single thread takes longer time.

In this case you could create special MySQL servers that are
used for special queries that have higher priority and ensure
that those use their own CPUs.

NDB can pushdown the evaluation of which rows to execute down
to the data nodes to parallelise the query. But it cannot
parallelise the counting and summing at this point in time.
So this query is bound by the fact that it is single threaded.

As you mentioned this query is executed on the data nodes
in a single ldm thread since it is bound to one partition
through the search_field.

You can check if this is the bottleneck by using top -H and check
if any of the ndbmtd threads is running at close to 100% load.

If this is the case you could gain some benefits by changing the
partitioning.

My best guess is that the MySQL server is the bottleneck.