Skip to content

RonSQL#

RonSQL supports pushdown aggregation of queries, including queries with joins and CTEs. The reason for developing this support is that AI inferencing is often built on features that constitute aggregates of many events. These aggregates are often calculated by offline systems that goes through historical data.

However real-time AI inferencing requires the features to be calculated on the current events as well as historical events. Thus RonDB supports storing event data in time windows, using RonSQL to calculate aggregates based on data in those tables and getting rid of this data when it is too old using the TTL feature.

To benchmark this we used a very simple benchmark consisting of 1 query in TPC-H. Q1 in TPC-H does a scan on all rows in the lineitem table. There are about 6M rows per scale factor in this table. Using the MySQL server this query requires most of this table to be sent up to the MySQL server to calculate the aggregates there. This takes about 1 second with scale factor 1.

With RonSQL the calculation happens where the data resides and it happens on all partitions in parallel. This means that in a standard setup with 6 partitions we can handle this query 10x faster in 100 ms. By doubling the number of partitions we can improve this to 20x improvement and this query can execute in 50ms instead.

Single-query latency#

This benchmark compares the latency of single aggregation queries executed by RonSQL with the same queries executed by a MySQL Server. Both read the same tables, stored in the RonDB data nodes. The benchmark varies the number of rows and the number of groups in the result.

The benchmark used the following AWS EC2 instances:

  • 2 data nodes, c6i.4xlarge (16 vCPUs, 32 GB memory)

  • 1 RonDB REST API server (RDRS), c6i.4xlarge

  • 1 MySQL Server, c6i.4xlarge

  • 1 client machine, c6i.4xlarge

The client sends one query at a time. Each query runs once as a warmup, which is not counted, and then 3 more times. The tables below show the median of these 3 runs. Three times are recorded for each run:

  • MySQL: the wall-clock time of one call of the mysql client. It includes starting the client process, the round trip over TCP, and the query in the MySQL Server, which reads the rows from the data nodes and computes the aggregates.

  • RonSQL end-to-end: the wall-clock time of one curl POST request to the /ronsql endpoint of RDRS. It includes starting the curl process, the HTTP round trip and the query in RDRS.

  • RonSQL server: the increase of the RDRS Prometheus metric http_request_duration_seconds_sum{endpoint="ronsql"} during the query. It covers only the work in RDRS: parsing the query, the aggregation in the data nodes and formatting the result.

The RonSQL end-to-end time is 6 to 7 ms longer than the RonSQL server time in all tests. This is the time to start curl and make the HTTP round trip. The MySQL time includes the start of the mysql client process in the same way.

In these benchmarks the MySQL Server computes the aggregates itself. The MySQL Server in RonDB 26.10 can also push single-table aggregation down to the data nodes when the session variable ndb_pushdown_aggregate is on. It is off by default and was not used here.

All tables use the following schema, with N rows and G groups. The tables are created and loaded before any query runs.

CREATE TABLE bench_<N>_g<G> (
    id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    group_key   INT NOT NULL,          -- uniformly distributed over G groups
    val1        BIGINT NOT NULL,       -- random 0..999999
    val2        BIGINT NOT NULL,       -- random 0..999999
    val3        DOUBLE NOT NULL,       -- random 0..1000.0
    val4        DOUBLE NOT NULL,       -- random 0..1000.0
    filter_date DATE NOT NULL,         -- random date in 2024
    padding     VARCHAR(100) NOT NULL, -- 80 bytes of filler
    INDEX idx_group (group_key),
    INDEX idx_date  (filter_date)
) ENGINE=NDB;

The four queries are:

-- Q1: COUNT, SUM and AVG per group
SELECT group_key, COUNT(*), SUM(val1), AVG(val3)
FROM <table> GROUP BY group_key;

-- Q2: filtered, about half of the rows match the WHERE clause
SELECT group_key, COUNT(*), SUM(val1), MIN(val3), MAX(val4)
FROM <table> WHERE filter_date >= '2024-07-01' GROUP BY group_key;

-- Q3: arithmetic expressions inside the aggregate functions
SELECT group_key, SUM(val1 * val3), AVG(val3 + val4), COUNT(*)
FROM <table> GROUP BY group_key;

-- Q4: the whole table, no GROUP BY, one result row
SELECT COUNT(*), SUM(val1), SUM(val2), AVG(val3), MIN(val4), MAX(val4)
FROM <table>;

Number of rows#

The first test uses 100 groups and varies the number of rows from 100,000 to 1,000,000. All times are in milliseconds.

Rows Query MySQL RonSQL end-to-end RonSQL server Speedup end-to-end Speedup server
100K Q1 141 42 35 3.4x 4.0x
100K Q2 61 25 18 2.4x 3.4x
100K Q3 151 41 34 3.7x 4.4x
100K Q4 68 16 10 4.2x 6.8x
500K Q1 635 181 175 3.5x 3.6x
500K Q2 181 100 94 1.8x 1.9x
500K Q3 662 171 164 3.9x 4.0x
500K Q4 212 53 46 4.0x 4.6x
1M Q1 1,204 351 344 3.4x 3.5x
1M Q2 329 197 191 1.7x 1.7x
1M Q3 1,326 358 352 3.7x 3.8x
1M Q4 387 100 94 3.9x 4.1x

The MySQL time grows in proportion to the number of rows, since the data nodes send every matching row to the MySQL Server. RonSQL scans the same rows, but the data nodes aggregate them where they are stored and return at most 100 result rows. Per 1,000 rows, the time grows by about 1.18 ms for MySQL and 0.34 ms for the RonSQL server time in Q1, 1.31 ms and 0.35 ms in Q3, and 0.35 ms and 0.09 ms in Q4.

With RonSQL, Q1, Q3 and Q4 are 3.4 to 4.2 times faster end-to-end. Q2 gains the least, 1.7 to 2.4 times. Its WHERE clause removes about half of the rows before they reach the MySQL Server, which lowers the MySQL time more than the RonSQL time.

Number of groups#

The second test uses 500,000 rows and varies the number of groups from 1 to 10,000. All times are in milliseconds.

Groups Query MySQL RonSQL end-to-end RonSQL server Speedup end-to-end Speedup server
1 Q1 483 52 45 9.3x 10.7x
1 Q2 182 35 28 5.2x 6.5x
1 Q3 499 56 49 8.9x 10.2x
1 Q4 211 53 46 4.0x 4.6x
100 Q1 618 177 170 3.5x 3.6x
100 Q2 181 99 92 1.8x 2.0x
100 Q3 649 185 178 3.5x 3.6x
100 Q4 209 53 46 3.9x 4.5x
10,000 Q1 683 348 340 2.0x 2.0x
10,000 Q2 204 217 210 0.9x 1.0x
10,000 Q3 714 356 349 2.0x 2.0x
10,000 Q4 212 53 46 4.0x 4.6x

With one group, RonSQL is 9.3 times faster end-to-end for Q1 (10.7 times in RDRS) and 8.9 times faster for Q3 (10.2 times in RDRS). The MySQL Server still receives all 500,000 rows to compute one result row, while the data nodes send only partial results, which RDRS merges. With more groups, RonSQL returns more rows and its advantage shrinks. At 10,000 groups, Q1 and Q3 are 2.0 times faster. Q2 at 10,000 groups is the only one of the 24 measurements where MySQL is faster: 204 ms compared with 217 ms end-to-end. In RDRS alone the two take about the same time.

Q4 has no GROUP BY, so the number of groups does not affect it: it takes 53 ms end-to-end and 46 ms in RDRS for all three group counts. The rows for 500,000 rows and 100 groups appear in both tables. They come from separate runs and differ by less than 9%.

Q3 shows the same gains as Q1. The data nodes evaluate the arithmetic expressions inside the aggregate functions, so the expressions do not cause rows to be sent to RDRS.

Throughput under concurrency#

This benchmark measures how many aggregation queries per second (QPS) the cluster handles with 32 concurrent client threads. The client calls NdbAggregator directly. NdbAggregator is the C++ NDB API interface that RonSQL uses for pushdown aggregation: it defines the aggregation program that the data nodes run as part of a scan, and it merges the partial results that the data nodes return. Calling it directly leaves out the parsing in RonSQL and the HTTP handling in RDRS, so the results show what the data nodes can do. The baseline sends the same queries to a MySQL Server through the MySQL C API. As in the latency benchmark, the MySQL Server computes the aggregates itself.

The benchmark used the following AWS EC2 instances:

  • 2 data nodes, c6i.4xlarge (16 vCPUs, 32 GB memory)

  • 1 MySQL Server, c6i.4xlarge

  • 1 client machine, c6i.8xlarge (32 vCPUs, 64 GB memory)

The two clients work as follows:

  • The NDB API client runs 32 worker threads, spread round-robin over 4 Ndb_cluster_connection objects, 8 threads per connection.

  • The MySQL client runs 32 sessions, each with its own connection through the MySQL C API.

  • For each query and table size, a 5-second warmup is not counted, and then a 15-second window is measured. The client measures the latency of each query with std::chrono::steady_clock.

Five tables have the following schema, with 100, 500, 10,000, 100,000 and 500,000 rows. Both clients read the same NDB tables.

CREATE TABLE bench_qps_<N> (
  id          INT NOT NULL AUTO_INCREMENT,
  val1        BIGINT NOT NULL,       -- random 0..999999
  val2        BIGINT NOT NULL,       -- random 0..999999
  val3        DOUBLE NOT NULL,       -- random 0..1000
  val4        DOUBLE NOT NULL,       -- random 0..1000
  filter_date DATE NOT NULL,         -- random date in 2024
  padding     VARCHAR(100) NOT NULL, -- 80 random characters
  PRIMARY KEY (id),
  INDEX idx_date (filter_date)
) ENGINE=NDB;

A row is about 130 bytes. The queries aggregate the whole table, without GROUP BY:

-- Q1: simple aggregation
SELECT COUNT(*), SUM(val1), SUM(val3), COUNT(val3) FROM <table>;

-- Q2: seven aggregate functions
SELECT COUNT(*), SUM(val1), SUM(val2), SUM(val3), COUNT(val3),
       MIN(val4), MAX(val4) FROM <table>;

-- Q3: arithmetic expressions inside the aggregate functions
SELECT COUNT(*), SUM(val1 * val3), SUM(val3 + val4), COUNT(val3 + val4)
FROM <table>;

In the tables below, the ratio is the QPS of NdbAggregator divided by the QPS of MySQL, and P50 is the median latency.

Q1: simple aggregation#

Rows MySQL QPS NdbAggregator QPS Ratio MySQL P50 NdbAggregator P50
100 24,452 38,813 1.6x 1.4 ms 0.9 ms
500 13,619 24,528 1.8x 2.5 ms 1.5 ms
10,000 1,393 2,274 1.6x 24.3 ms 17.3 ms
100,000 125 177 1.4x 258.8 ms 217.8 ms
500,000 25.6 45.1 1.8x 1,301 ms 658 ms

NdbAggregator handles 1.4 to 1.8 times as many queries per second at every table size. At 500,000 rows its median latency is half that of MySQL: 658 ms compared with 1,301 ms.

Q2: seven aggregate functions#

Rows MySQL QPS NdbAggregator QPS Ratio MySQL P50 NdbAggregator P50
100 22,149 40,623 1.8x 1.5 ms 0.8 ms
500 12,233 23,228 1.9x 2.7 ms 1.6 ms
10,000 808 2,057 2.5x 40.7 ms 18.9 ms
100,000 77 148 1.9x 418.9 ms 267.6 ms
500,000 16.0 32.3 2.0x 2,119 ms 1,212 ms

The gain is larger than for Q1: 1.8 to 2.5 times, and largest at 10,000 rows. The MySQL Server receives every row and computes all seven aggregates, while the data nodes compute them in one pass over the rows they store.

Q3: expressions#

Rows MySQL QPS NdbAggregator QPS Ratio MySQL P50 NdbAggregator P50
100 24,026 40,949 1.7x 1.4 ms 0.8 ms
500 13,236 23,073 1.7x 2.5 ms 1.6 ms
10,000 1,340 2,116 1.6x 25.1 ms 18.6 ms
100,000 123 142 1.2x 263.5 ms 273.4 ms
500,000 25.6 31.1 1.2x 1,316 ms 1,266 ms

The data nodes evaluate the arithmetic expressions. The gain is 1.6 to 1.7 times up to 10,000 rows, and 1.2 times at 100,000 and 500,000 rows, where the median latency of the two is about the same.

Across Q1 to Q3, NdbAggregator handles 1.2 to 2.5 times as many queries per second as the MySQL Server at every table size, and 39,000 to 41,000 queries per second on the 100-row table.

Latency distribution#

At 10,000 rows the latency percentiles of Q1 and Q2 are:

Query and client P50 P95 P99
Q1, MySQL 24.3 ms 29.4 ms 32.9 ms
Q1, NdbAggregator 17.3 ms 21.6 ms 23.9 ms
Q2, MySQL 40.7 ms 47.4 ms 51.6 ms
Q2, NdbAggregator 18.9 ms 23.2 ms 26.4 ms