Experiencing degraded throughput on KV lookup compared to benchmarks

1

Here is my setup

Mgm Node:
1x (r5.xLarge)

Data Nodes:
2x (r5.4XLarge)

Mysql Nodes:
2x c5n.9XLarge

--ndb-cluster-connection-pool=4 
--max-connections=16000

Table i am running my KV lookup on: (the table has 150 million rows)

CREATE TABLE `my_table` (
  `ID` int NOT NULL,
  `column1` double DEFAULT NULL,
  `column2` int DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `col2_idx` (`column2`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

on generating load:

Key lookup requests similar to this: (select id,column1 from my_table where id = ?)

The load hits and plateaus at: 20Million requests per minute (333k req/sec)

DataNode CPU hits 95-97%%+
Mysql nodes CPU hits 98-99%%

not able to generate any more load due to CPU going this high.

i am running a rest service with 20 nodes which create a connection pool with the mysql instance using Connector/J. Each node creates a pool with mac 30 connections at the moment.

the p90 latency i am getting is ~2ms which is perfect but given the benchmarks i was expecting this to go beyond 2 million req/sec at bare minimum :slightly_smiling_face:

@mikaelronstrom could this be some sort of a misconfiguration? :thinking:

i am using the default setup … the only additional params i have are on the mysql side :

–ndb-cluster-connection-pool=4
–max-connections=16000

any help would be appreciated :smiley:

i am just looking forward to seeing the benchmark numbers as close as possible

let me know if you need anymore info from my end.

2

If you compare the performance numbers with my benchmarks of Sysbench they
are pretty much in line with the expected performance. The problem is that you
are expecting performance from the native NDB API using MySQL. Unfortunately
the MySQL Server has a high overhead for each query.

To improve throughput the following efforts can be taken:

  1. Use the ClusterJ interface instead.
    This decreases the overhead on the client side by a large factor. Probably enough
    to increase performance by 2-3x even still using single record lookups
  2. Use batching, but still using SQL
    In this case the queries would instead be:
    SELECT id, column1 from my_table where id IN (?, ?, ?, ?, ?)

In my blog posts I have shown how this can get number of row lookups
to go all the way up to 5.5M key lookups per second when having
100 entries in the IN-list.

In this case we handle about 55.000 SQL queries per second, but each
query fetches 100 rows.

  1. Use batching and ClusterJ
    In this case the number of rows is likely to be about the same as
    using SQL with batching. But the CPU usage on the client side should
    be smaller.

It is likely that you can add performance also without batching and
without ClusterJ by simply adding a MySQL Server. I am pretty
sure that the data nodes can go much higher. The reason for their
high CPU usage is due to the default configuration of CPU spinning
to achieve low latency. This spinning is achieved with special CPU
instructions that enables spinning to happen at low power usage.

1 Like
3

the use case we have is for pointed single record lookups by multiple users… it becomes tough to batch requests in this case :thinking:

will surely try out the ClusterJ interface and compare the results as well.

4

Long-term we are considering developing a REST API for this type of
application where we can handle batching under the radar. I’ve written
a blog a few years ago about how to achieve this and our ndb_import
program is written using this approach.

5

Long-term we are considering developing a REST API for this type of
application where we can handle batching under the radar. I’ve written
a blog a few years ago about how to achieve this and our ndb_import
program is written using this approach.

6

@mikaelronstrom can you share the link to the blog you mentioned.

will take a look at it and see if we can get inspired by it to build something

7