Hi,
I’m using single instance of Aerospike 5.6, holding approximately 25 millions of records (new records are constantly arriving, but they are set to expire in 30 minutes) in memory. Each record contains 30 bins and occupies 500-1000 bytes.
In my test environment I use Aerospike expressions with .NET client to execute search request similar to
SELECT *
FROM ns.set_name
WHERE
FilterBin=F
AND BinA BETWEEN A1 AND A2
AND BinB BETWEEN B1 AND B2
AND BinC=C1
Every bin involved has index set up: string index for FilterBin and numeric ones for BinA, BinB, BinC.
This configuration works, I can get data from server, with each test request returning 0-150000 records depending on input params. The problem is, queries take far longer than I’d expect from in-memory storage, up to 50 seconds. With test client configured to constantly send parallel requests in 10 threads, I can see the following latency values
Is this situation expected? Can index search actually take that long? I tried to utilize stream UDF, and it works almost two times faster than expressions.
The fact that you have built Secondary Index (btree-s) on FilterBin, BinA, BinB and BinC gives me a clue that you are doing something wrong.
A Filter Expression works by first reading a record and then applying a filter condition. If you have defined just a filter expression using bins FilterBin && BinA && BinB && BinC, effectively you will be reading each of those 25 million records and then applying the filter expression to the data in each record, which then decides whether the record qualifies to be returned back to the client or not. You are not leveraging any Secondary Index btree in this scenario.
A secondary index search is done (specified in Java client via the Statement object) by using the btree to first select a subset of the 25 million records. In a given query, you can only use one SI (you can build 4 SIs, as you have done, you can employ/use/exploit only one of them in a given query). Lets say you used FilterBin==F condition in the Statement object - setFilter(). btree for FilterBin is memory - will be looked up for matching “F” and qualifying records returned for further processing in the server.
If you pass BinA && BinB && BinC, additional conditions using a Filter Expression - (via the QueryPolicy - filetExp in Java) - then all the “F” qualifying records that are read from storage, will be additionally checked for satisfying the filterExp condition. Those that satisfy, will be returned. There is no need/benefit to build SI on BinA, BinB and BinC.
Given this mechanics, you can decide which Bin is best to do the first SI search - the one that will result in the smallest number of candidate records - rest you down-select via the appropriate filterExp.
Without looking at your code, I am assuming you may have just put a filterExp on all 4 bins, not really utilizing any SI, even though you built all 4 and hence turning your query into a scan - reading every record and applying the filter. Even then though, this should be faster than a stream UDF since a stream UDF is doing pretty much the same thing. (Unless you added an SI filter in the stream UDF!). However, lets rule out this situation first.
Thank you for quick reply! The code in question (with names modified to match initial “FilterBin+BinA” pattern) is
C#
var statement = new Statement();
statement.SetNamespace(_settings.Namespace);
statement.SetSetName(_settings.Set);
var key = GetSearchKey(request);
statement.SetFilter(Filter.Equal(BinNames.FilterBin, key));
var queryPolicy = new QueryPolicy(_client.queryPolicyDefault);
queryPolicy.filterExp = Exp.Build(
Exp.And(
Exp.GE(Exp.IntBin(BinNames.BinA), Exp.Val(request.AMin)),
Exp.LE(Exp.IntBin(BinNames.BinA), Exp.Val(request.AMax)),
Exp.GE(Exp.IntBin(BinNames.BinB), Exp.Val(request.BMin)),
Exp.LE(Exp.IntBin(BinNames.BinB), Exp.Val(request.BMax)),
Exp.EQ(Exp.IntBin(BinNames.BinC), Exp.Val(request.CValue))
)
);
using (var queryResult = _client.Query(queryPolicy, statement))
...
So I expected this query to first leverage string index over FilterBin (chosen as the most selective), and then apply filterExp to resulting subset of rows. Didn’t know indices over bins A, B and C can’t help in this case, though. Is it what actually happens?
Yes, you have the right mechanics in place. I am not a C# API expert but the constructs look correct. SI btrees point to record Primary Indices - so only used in first down select. Expressions do not look up the SI btree - they work on the specific record data. So, indices over bins A, B and C are just eating memory for nothing. In your comparative stream UDF, are you using SI Filter as well? I assume yes?
Yes, I use SI filter over FilterBin in both cases.
My UDF is common filter → map → aggregate pipeline, which filters records, as I understand now, much like filterExp does. It returns slightly less data (lua list with 24 fields instead of full row + metadata), which could possibly explain speed difference
In Java, we have Statement.setBinNames() that lets you return selected bins of the record only from the query. I am sure C# has equivalent, if you want that.
I would recommend filter expression based query over aggregations with future server upgrades in mind.
Thank you, I’ll try it out!
As to my initial question - are tens-of-seconds latencies expected to be seen in my scenario? I did suspect Aerospike server runs lots of scans on row subsets (which may contain from zero to, let’s say, two or three millions of items), I’m just not sure whether this process can really take 50 seconds or it is just me configuring AS server horribly wrong
If latter is the case - where would you suggest I start looking? Maybe tuning parameters like service_threads (currently I use pretty much default config, with query-in-transaction-thread, query-threads and query-priority being most notable exceptions for UDF)?
And if expressions are not expected to be blazingly fast, what else could be done? According to Aerospike use cases I’ve seen, it would be best to utilize PI somehow, but I honestly can’t find suitable data model. Maybe building a cluster with 2-3 additional servers would correspondingly improve search speed?
Hi! I changed the code to retrieve only necessary bins, and search times improved a bit (about 7-10% faster now), but UDF+SI still beats expressions+SI. Guess I’ll have to add second server next
