Record Layer query performance benchmarking against traditional RDBMS

Using Layers Record Layer
1

I’ll start-off with a disclaimer that I know it is not an apples to apples comparison between FDB(with Record Layer) and a RDBMS like DB2 for eg, i just want to understand what kind of performance i should expect.

Background
I’m working on moving one of our systems off of DB2 into a record layer fronted FDB cluster.
We have been asked to do a performance evaluation in terms of read and write times that an equivalent query in FDB will take in comparison to a predefined ( somewhat suboptimal ) DB2 query will take.

Schema
Let me start off by defining the table and corresponding proto definition -

Table - Person
ID(int32) | NAME (varchar) | AGE(int32) | GENDER(char)

Primary Key = ID

proto -

message Person {
    int32 id = 1[(com.apple.foundationdb.record.field).primary_key = true];
    string name = 2; //consider this to be a large string field ~30-40 bytes
    int32 age = 3;
    uint32 gender = 4; //sigh, no single byte datatypes in proto
}

Indexes
RDBMS -

Index1(id, Name)
Index2(id, Gender)

Record-Layer -

builder.addIndex("Person", new Index("pk_name", field("name"));
builder.addIndex("Person", new Index("pk_name", field("gender"));

Query
Now for the fun part, the actual querying -
Let’s suppose there are ~50 million records in both.

If i were to for eg: fetch 10 million records , where each record size from the proto is ~50 bytes, in a 10 MB transaction i can then fetch at max - 10 * 2^10 * 2^10 / ~50 = ~200,000 records
which means for 10e7 records i’d need 50 transactions to be run against the database, whereas DB2 imposes no such limits.

Opening a transaction, has some cost associated with getting record store , doing a metadata read adn then the actual query( or ideally a scanIndex) in this case, which is evident in the performance drops we see while querying.

The above only either needs a scanIndex or a primaryKey lookup at most.
The situation gets a lot trickier when comparing JOINS. Well-defined( i should say Well-written ) joins are blazingly fast in RDBMS, whereas i have seen some not so efficient query-planning in the record layer space .

Would you say that we should be prepared to expect a performance hit when moving from a relational database for a model like the one which i described above ?
Also, would the performance degradation be more evident in case of JOINS ?

Lastly, do we expect to see any improvements in the query planner anytime soon?

Sorry for the long post!

1 Like
2

@alloc tagging you bump this up :slight_smile:

3

I’m sure others can chime in with additional details, but I’ll take a shot at pointing a few things out:

which means for 10e7 records i’d need 50 transactions to be run against the database, whereas DB2 imposes no such limits.

Correct. Of course, this limit is coming from FoundationDB so there isn’t much the Record Layer can do about it. With the Redwood engine coming, there will be support for longer lived transactions so this may no longer be necessary. There may be tricks you can play for more efficiency, such as parallelizing your scans over multiple threads and caching read versions between them, but it is a bit of a delicate game to play.

Would you say that we should be prepared to expect a performance hit when moving from a relational database for a model like the one which i described above ?
Also, would the performance degradation be more evident in case of JOINS ?

Remember, Record Layer is designed to be stateless and streaming. This means that common join strategies that involve memory (hash joins) are not an acceptable approach, despite the fact that they can lead to large performance gains. Additionally, an RDBMS will have other buffer pools to cache recently accessed data which, again, Record Layer does not have due to its stateless nature.

The tradeoff here is that while an individual request will not perform as well (in some cases) as your DB2 query, Record Layer + FoundationDB should provide for significantly better scale out across concurrent requests.

The planner will continue to evolve and get smarter, and there is no reason why operators and plan strategies that involve memory and state in the server cannot be built, but it is most likely that this sort of thing would be a sister/parallel project on top of Record Layer rather than within Record Layer itself. It is a project I’d love to see, btw, so call to arms to whomever would like to get involved in that! :slight_smile:

4

I’m not sure if the 10 MB number here is just arbitrary, but there’s also a possibility this is referencing the 10 MB transaction size limit. It’s worth noting that that number is for the size of a committed transaction, which for most use cases should be dominated by the key and value bytes written (and while keys read can affect it, values read will not). So read-only transactions, like these queries, shouldn’t need to worry about it.

There are other limitations that can affect a read-only transaction, most notably the 5 second transaction time limit. How long it would take to read 50 million records depends on quite a few things (including network latencies), and may take some experimentation to get right.

I think @scgray summarized it pretty well, but yeah, it wouldn’t surprise me if traditional RDBMSes outperformed the Record Layer in many dimensions, but the Record Layer is trying to help users scale out, and the query planner is undergoing a rewrite, and that while somewhat limited today, hopefully will become more capable as that work progresses.