Okay everyone, sit down, have a look at the next session, so I'm delighted to introduce
have you here, Jeremy, who actually picked that up because this room exists because of
the great work that this guy did, so I'm glad we're brought to have you here.
We have got accelerating, question the lessons from 6x query performance groups, so I
better to give you guys.
Who?
Yeah, thank you so much, actually we are finalizing the room, not me, but yeah, it was my idea,
so yeah, that might be there, there's him.
Okay, you know, in every team that is one doing the board and the rest, I'm the rest.
Next year we'll see, anyway, so we are both part of the quest to be a project, another
work on a book, so I don't really contribute much to the code, so in this talk I
want to do an intro the first seven minutes, I'm going to be speaking about you know,
the performance, what we did, and then Jeremy, who is actually occurring in the company,
he will show you what we did, the changes in code and so on, to get the optimizations.
By the way, we are open source database, when I say, when I say we are that six times faster
today that we were at last for them, since times is an average, some queries today are
40, 50 times faster than before, so not pretty much the same, but yeah, on average we see
many queries are about six times faster.
So first of all, we are open source database, of course, we are here as for them, a
party to zero license, we have about 150 contributors, if you are into databases and you
want to contribute, you will really welcome your helping the project, we are all about performance,
because it is used for time series, that means it's often used for real time, for example,
tracking market data and putting a bus board or doing payments and analytics when you are
taking contact with a tracking and energy data, since that you need to have data pretty fast,
we optimize very much for ingestion, on a single instance you can ingest four or five million
records per second, so we try to be quite fast for ingestion, of course we use combinary storage
and partition and everything by time and so on and so forth, the code is mostly written in Java
with some C++ and lately also some Rust, and before you ask me yes, we have to do
lot of things to make Java be that performant, and one of the things we have to do was to write
our own version of the standard library, so we can have much more control about memory management,
we don't advocate or use the garbage collector on the code path, we try to parallelise and
betterise every operation, we wrote our own just in time compiler for SQL queries, and when it
comes to SQL which is how you query QuasDB, since we are time series database, we have some
extensions in SQL to make working with time a bit nicer, you might not be seeing much here in this
light, but that's fine, besides there are some sample queries, I want you to use a station of
this specific SQL we have, for example here we are doing account, but we are sampling in what we
can sample by any like interval you want, so it's like a group by just sampling by time,
or here is another query we are getting a sum of some value for its different value of the
symbol and the site columns, and we are sampling in one of the intervals, but if at any particular
minutes we don't have any value, we are going to interpolate, we are going to be getting rows with
null values, it could be with a previous value, could be with a linear interpolation, different
values for that, we have also some as of join, which is a specific type of join, and we do have
two tables, and for its record of the left table, we are going to find the closed setting time
from the right table, so maybe a table is getting data every few seconds, and the other is getting
two records per hour, as of join will match the closer in time from one and the other, so that's
kind of like the extensions we have in QuasDB, and we have tens of thousands of users which are
very happy with the performance, for this type of queries, when you do queries, which are about time,
we are very performant, but we notice that users very often, even if the main use case is about
time serious, they often want to do queries in which it is not a time component, they don't want
to down sample, they don't want to query a chunk of time, they just want to do a plane of group by,
have everything, group by whatever, without any time filters, without any aggregations, and for those queries,
we were not very fast, we were not bad, I mean we were still faster than a conversational database,
but we were not fast for the queries that were not time serious, and we wanted to be fast also for those
queries, because even if our focus is on time serious and we optimize everything for the use case,
we also want to allow people to be fast in the use cases, so we decided to start having improvements
to the code to make it perform better also for other queries, and before we wanted to
made of changes, we wanted to have a baseline, so like how do you know if you are getting faster,
if you don't know how fast you are, so basically we took a look at some benchmark in the industry
and it's like, oh we want to see benchmarks about not a serious, but about other type of analytics,
and in the end we decided to use clickbench, clickbench is a benchmark by click house,
click house is a different database, they actually have a session here in this room today,
and they are very much about generally, it's an awesome database for genetic analytics,
and they have this benchmark, the clickbench benchmark, which was released three years and a half ago,
and it is very nice for us, because most of the queries on the benchmark
are not having to do with time, they are just group buys and sample buys and lot of aggregations,
a lot of conditions, but not in a lot of time, which is pretty good for us, because that is actually
what we wanted to improve, the rate asset on clickbench is about 80 gigabyte of
uncompressed data, it has almost 100 million rows of 100 columns each, so it's not a super big
data set, but it's good enough for testing and for benchmark in this type of queries, and this
type of queries that you have on that benchmark, there are 40, 23 different queries, some of them
very simple, like select whatever from the table, or then they are starting adding filters
in a quality, a quality contesting, then we start getting more interesting things like
order buy the sending, which is challenging, like on a database, or here we have a case combined
with group buys and with conditions, and with limits, and with order buy, and then you have
some crazy queries with a lot of aggregations, so this was pretty good for us, because it's the
kind of thing we wanted to improve, and the first thing we wanted to run the benchmark
three years and a half ago, we wanted to appoint it, and then not really, but
because it is down here somewhere, we were about 33 times as low as the top performers,
it's not, I mean it hurts, but you know we wanted to improve, on time series, we are very fast,
but in other queries, a year later, we were already at the top, no we are not in the top,
and we are here, we are here, we are, oh 15, only 15 times as low as on the top performers,
I mean 15 times as low as is but, okay, but we were 33 times as low as a year, so it's like,
this is getting somewhere, the things we are doing, they are making their integral quality faster,
so you know, you said, we are happy, we are happy, it's like pretty cool, one year later,
September, but here, hey, I don't need to scroll, we are right here, we are only
3.1 times as low as that the top guys, actually click house, which is here,
everything on this benchmark, of course, is optimized for click house, because it doesn't have
is to 15, and actually the top one is the one is one, it is one point five, why,
because in this benchmark, what they do is they take the best run of each query, if you have
like the ideal database, that will perform faster in all the queries, you will have a one,
but the database at the top here, it performed better than the others, only in 15 queries,
in other queries, they are all the others that are faster, so the ideal database will be one,
but the first one in the ranking is actually one point five, and we are here 3.10,
it's not too bad, and actually last week, within that release, it has nothing to do with
performance, but still, we got a bit faster than before, and now we are 3.0, and if you notice,
I'm finishing already, if you notice here, we have like mix hardware, metal machine,
for X machines, blah blah blah, if you, only compare with the same instance, we are actually
still like the other people are faster than us for identical queries, because we are about time
serious, but we are already only, where we are, so we are already only 2.6 times in this benchmark,
the total one is 1.3, so you know, it was about improvement from the 3 years ago, that's kind of
the ideal, so how we got here, that's the talk, how we made this happen, so well, it's took
over 3 years of time, of course we've been doing other things, we haven't been just
focused, obsessing about benchmarking, we've been developing, that's how it's replication,
what a hitbox, a park, a JSON integration, a lot of different things, adding window functions
we haven't had 3 years ago, so we've been doing other things, many of the contributions have
come from the community, most from the core team, we tried many things, and we fell at some of
these things, and now I'm going to pass it together with, sorry, took the money, I have this one,
so she'll be enough, so however one, so we have about 20 minutes to cover these 80 patches,
have we ever been talking about in 2 years of progress, so let's go, so I cherry picked just a few
of them and it will start with something simple, we were missing some functions for sometimes,
so the example of a simple optimization with a big impact is just implementing a new function
of count distinct for integers, because we two years ago we were lacking this, so to be able
to even execute the test queries, we had to cast that integer to our chart and then aggregate that,
well that's not going to be any fast, so that was an example of one of this like small
effort, big impact optimizations, but let's do something more interesting, so we have just
in time compile for queries, so quasi be a little bit of an intro, it's a project mostly in Java,
but it has some C++, C and Rust parts, and this is one of them, so basically when you have an
SQL query, we've learned predicate, then we do our best if we can to take that predicate,
turn it into machine code for your architecture, if you are in on Intel and the query has a good
fit, then we do synth instructions for vectorization to execute it as fast as the CPU can and
really our goal is to not obstruct the CPU, but how it works, we receive your query, we parse it,
this produce AST, then from this AST we produce some kind of intermediate representations,
you can think of, I don't know, maybe if you know LVM bit code or something something similar,
and then we store this bit code to some of heap slash native memory and then we call our C++
backhand, which will read that intermediate representations and we'll spit out the machine code
for the target architecture, right now covered, it's Intel only, but we are looking whether to
implement this for arm as well or not, and yeah, the output of this is basically a function,
which we then call from Java via GNI, and on the input you can see here is the address of our data,
and things like where the output should be located, yeah, yeah, yeah, yeah, yeah, yeah, yeah, yeah, yeah,
little detour, we don't use typicrafing, I believe postgres can use LVM for GIT, we don't use LVM,
we use really low level library called ASMGIT, and sometimes sometimes QuestDB, the Java part,
people say that it's Java in the name only because it looks more like C, so when, when,
when a QuestDB developer writes Java, it looks like C, and this is what, how it looks like,
when QuestDB developers writes C, then it looks like assembler, that's the API of the ASMGIT library
if you use, and that's also the reason why we don't support arm right now, because we need basically
to write the same for, for the arm instructions said a neon and things like that, yeah, yeah, yeah,
by Intel, I mean, I mean, the X86 or 64 bits, with with with with AFX to instruction set, yeah, so we implemented
the, we implemented the, the first version of the GIT compiler for some filters, it cannot compile
all filter filters, and then we did a bunch of improvements like the URL in the clickbench is a
varchar, and originally if, if the filter included varchar, the, the, the, the, it would not be
eligible for that, so we did some improvements like, okay, if the, if the filter includes varchar,
and it's checking if it's now or not, well, that's still easy, then now it's eligible,
so minor improvements there as well. So, like, so that's pretty working or not, but you don't
go to, so it, I thought that you would show the example, yeah, yeah, yeah, yeah, one, so that,
that's quite for example, what, what I did is like, I, I started multiple instances, of course,
they be here, so this is, this is quite the, be, absolutely on the latest version, we will release
on Wednesday, and this quick takes, it's a difference between Google, the, the call path and the
whole path, but still it was like three seconds to execute this query, so if I go to,
quiz with the five years ago, and the same query which will be this one, let's see how much it picks,
so you took on the, oh, sorry, let me, let me run again, no, it happens, it happens, there is somebody
of it is sometimes, okay, let's see, is the same query, so three seconds, well, that was interesting,
and there's not, no, that's cool, that's cool, I mean, it's all good, oh, no, I know what,
this is, this is the same, sorry, I, I switched inversions here, so inversions six, actually,
let me just see what's happening here, this is the same query, because the inversions six,
actually, I see in this one is taken half a second, let me see, this is the one, yeah,
anyway, it looks same, that's interesting, okay, one second now, so as you see where it's
lower than before, I'm going to try in the version of year ago, okay, so yeah, in that, that's,
I know what's happening here, we have the, the code one, okay, so now this, this makes sense,
this is working now in 1.8 seconds, inversions of year ago is taken about three seconds,
oh yeah, microphone, I have the microphone over there, sorry for the, I forgot the people on the
stream in, sorry it was just me doing a failed demo, and then on the version of three years ago
two years ago, okay, so actually this is faster, oh yeah, and we knew this, and I'll tell you
what happened, this is kind of a stupid, I'll tell you what happened, so first I was checking the
code run and the code run, the code run means we have to go to the disk, on the second we are
already memory, so when we are in the code run in the first version of the second, the first one was faster,
why the one of two years and a half ago was actually faster in this particular test,
and we talk about that the other day, it's because we've been adding new data types, so on the version
six, the table, was you seen only, uh, integers, and in version seven, we are actually using short
force on columns, and actually in sort, we don't have optimizations, the ones that are
me were saying, so not optimizations are at the block for some type, but we still don't have
for others, I was having this particular query, so actually the new version is slower because
they changed the type of the column, and for that particular column, we don't have the optimizations,
so yeah, that happens, there's something to it, so at least we can explain it, but that
was happening, we have another one later that's only faster in all the versions, but this was
a good one, yeah, you think they're a good query, okay, so next optimizations, so two years ago,
we would store string types as a UTF-16 encoded, that was one of our legacies of Java,
which is not great on its own, but it gets worse, so the way how it was stored and how it was
organized on a disk is that we had two files, the first one is similar to as a very storing
integer, so the physical representation for integers is really simple, it's basically you have one
flat file, you store arrow, you write the number for that particular column, you store another
arrow, you write the integer, and it's easy because all integer they have fixed size, so if you go
to, if you want to read from ends, and for all, does it work in the microphone? If you read,
if you want to read ends, you just multiply end by the size, and you know, do offset in the column
file, that's easy, but it doesn't work for things like varchar, right, because string can be
showed long, so they don't have fixed size, so you know, what they say about computer science,
everything can be solved with another layer of indirection, so that's exactly what's going on here,
so we have two files for varchar, one is just fixed set offsets into another file where the actual
data are stored, prefix with the size, this is just conceptual, because if you think about it
is UTF16, so each chart here would be 16 based and what not, but that's the concept, it's easy
to implement, it's easy to grasp, but it's not very fast, one reason is, well it's UTF16,
so it's also not very compact on this, can in memory, but the reason why it's slow, if you are
doing full-table scans, and for every error, you need to jump into another file and even, you know,
to check size, because the size is of each string that's stored in the second in the data file,
so it's really slow, because there is a lot of pointer chasing and, you know, not good,
so in the new, about a year ago, I think, how we are, is it a year ago, going to be introduced,
this new new implementation of varchar, I don't know, I lost track anyway,
and my out-farmary motivation was to replace UTF16 with UTF8 for obvious reasons,
and while we were doing this, we were like, well, maybe we can do even better, so we change
things how they are represented, so the image in this is the first column, which is fixed size,
and it doesn't include now just offsets, but it also includes some kind of flags and size,
and prefix, so first few bytes from the actual string, right, so now when we want to check
if particle, if particle string starts with something, or has some size, or even equal,
something we, you know, what our predicted says, we can consult just the prefix in the, in the,
in the first file with, with, with fixed width, and if it doesn't match, then we don't need to go
to the other file at all, and that, that saves a lot of, what of time and point of chasing.
Now, the actual need, the other thing in the room is that even two years ago, we could run
some kind of aggregation queries on multiple threats, but just tiny, tiny subsets of
something we had specialized path using, same instructions for simple aggregations like
a minimum maximum average, but that was really simplistic, we wanted to have something more general,
and, you know, in a, in a, this is the naive or single-traded implementation of group by
aggregation for this query, so we have a table with user ID and, and we want to count
a pair of user ID, we want to, we want to, we want to group by the user ID, so the implementation
is, it's pretty simple, right? So this is our input table, we read it, we read it, we read it,
okay, user ID 142, we put it into hash map, if it's not there, we set count to one, if it's already
there, we increase the counter. It's easy, single-traded implementation, nothing to write home about.
The disadvantage is clear, it's single-traded, right? If I have a CPU, if I have a computer,
if 1208 cores, only one will be used, not good. So the one way to parallelize this is
simple, right? You just split the data set into, let's say, I have only two cores, so let's say,
in this case two, and then each worker thread, I spun task, and each worker thread will get, you know,
half of the data set, so they will produce partial tables with partial results, and once they are
done, I have these two tables, and I manage it, and this is my result. So I was able to distribute
the work and utilize more cores, somehow, but the problem is, there is still a bottleneck,
that the bottleneck is in this merging part, right? Because think of it, if I have many, many users,
this tables will be the individual tables, will be still huge, and the merging is single-traded,
in the knife case, right? Because I, you know, the workers are done, I have a bunch of
intermediate tables with partial results, and I need to merge it into the final final final result,
and that single-traded, this will be my scalability bottleneck, and I'm done, so we can do better,
we can do what we can do, the improved strategy, we employ right now, in the current version of
SDB, is that each worker will have a split its table further into charts, let's say, and then,
per each key, this is my user ID, it uses the hashes of that key, because it needs to know the
hash anyway, because the results are stored in hash tables, so it takes some bits, some bits,
from the key, based on this on this bits, it selects a sub-table in its own partial results table,
and story there, it means that all workers will calculate, will select the same sub-table for
the same key, it means that if one worker calculated that the key 142 belongs to the partition
174, and then there is another worker, which is processing the same key, it will also calculate
that it belongs to this partition, so by the end of this process, I will each worker will have
a bunch of this partial results, and once they are done, I am here, so we filter and aggregated,
now we have the partial results, then we need to do merging of the partial results, but that
this can be done in parallel, so now I only need to submit a task, where we merge partial results,
partition 0 from all workers, then another task, which will, in parallel, merge partition
results from partition 1 from all worker, and at the end of the day, and this can be done in all
in parallel, so, you know, the problem with my high cardinality user ID is gone, because this
merging now is no longer bottleneck, okay, some small optimization we did, I keep talking about
hash tables, and the example I gave you was fairly simplistic, right, it was just use the ID,
it was the only key, but in reality, in clear is that the key can be more complicated, right,
it can be multiple columns, it can be, it can be some columns, it can be fixed, fixed size,
some can be like large R and others, and we realized that at some level we get a lot of
performance boost by using specialized data structures, so if we are running aggregation,
like this one, where the key is only one integer, then we have specialized data structure for
that, then if we have table with two integers, then we have specialized data structure for that,
and you know for this we have data structures for this common cases, and then if your
query doesn't fit it, then it uses something more generic. Yeah, so what we have learned,
we wanted to thank to Clickhouse, I think, for for for for the Clickbench, because it was really
instrumental in in this journey, because, you know, we are still rather small team,
and coming up with a good, good benchmark is hard, it's really hard, and then Clickhouse did a lot of work
for us here, and actually it's pretty cool, because in Clickhouse there are a lot of
in Clickbench there are a lot of further databases now, so we have in Clickbench there are a lot
of databases, you can actually compare with all the evolution of further databases, which is
which is super interesting, and it's very easy to run, it's another thing compared with all the
benchmarks. So yeah, if you want, so yeah, the first thing we learned that if you want to do
for a good time series database, you also need to be a good analytical database, so we have to
apply the same things, doing improvements, it's not, it's not like one we are going to do this,
we are adding like various more things, we saw the queries, we run today, we have
temas lot of things, but there are some data types, for which we don't optimize to be with
further, most of our users don't use the sorted data type, it's on the benchmark, so you know,
it's that query, but it's not one of the ones we see in production, so we optimize when users
complain mostly, it's like we saw, we see a query, that's it's a lot for users, we investigate,
we add things, but yeah, requires a lot of a lot of work to do small things, and eventually
we get to something, and then you get, when you improve some things, you actually improve
others, so when we improve a group by, we had this sample by, which is specifically for us,
sample by time, and that was also faster, because we were improving one thing, they said,
effects, you are improving another thing, and because we're improving identical queries,
we're improving tensile queries, which is pretty interesting, so you know, it's good to see,
and the other thing is like now we know many things that we need to improve, for example,
adding more data types, or adding more functions to parallelize, which is something we
still don't have, and we have a lot of ideas for those kind of things, and I don't want to finish
without showing you a query, which is faster, so actually this group by, that Jeremy was saying,
it is one of the things we improve lately, so that one, for sure, so we faster, without any
without any shadow of a doubt, so this will be latest quest debate, so in the hot run, which is
the second one, in the hot run is taking now 2.6 seconds, in the version of a year ago,
that's the same query, on the second run not the first one, because the first one might be
going to this, I know, after this is just the thing just like, so yeah, on the, well, as you can
see, the older series is a slower, for sure, so it's still still there, okay, so now the second,
the second run of this query, on this version, it should still be around 10 seconds,
so yeah, it's there, so yeah, it's 10 seconds on the, on the, a year ago, 3 seconds now,
and as you go to the 2, 2, 1, something years ago, this query will be less slower than both
the one a year ago and 2 years ago, because of the depreciation, in this case, there is no,
there is nothing there, so I wanted to show you that for real, some queries, they do run faster
in this, in this version that they used to run in the past days, that's all we have, we don't have
more time, so basically, I just want to thank you again, so you're sorry, because you want to
collaborate to collaborate to the project, and yeah, 150 contributors, you want to be 151,
actually it's 153 or something, but whatever, so yeah, just go to get help and contribute, thank you very
much, thank you, thank you, thank you, thank you, thank you, thank you, thank you,
thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you,