Snel is still under development, it won’t be publicly available yet, but we hope to improve code base, add support to multi-tenant over a distributed network and more cool features, so it will be open-sourced in the future.
We called this new engine “SNEL” as an acronym for “SQL Native Execution for LLVM”. Well, that’s the excuse, actually we chose Snel because that means “fast” in Dutch and the name seemed right to us. from https://arxiv.org/pdf/2002.09449.pdf#page=6
Ooh this seems really cool, wonder if you can take SQLite bytecode and convert it to LLVM bytecode and execute natively. Edit: oh that's what they do haha
Actually this is quite a bit different. This is more a columnar-store version of SQLite, basically an embedded OLAP database, which is pretty cool. I’m not aware of other column-stores in this niche, most are distributed systems meant for big data and so are much more complicated to setup and manage.
since llvm is so slow you really have to validate if jit does work for your queries (obviously like for anything... duh) in my case i managed to slow the DB to a crawl with queries that were estimated to be super expensive but 95% of the plan wasn't actually ever executed.
Yea, we really need to improve the handling of those cases. I think there's four (was three) major angles:
1) I'd hoped to get caching for JITed queries into 13 (or at least the major prerequisite), but that looks like it might miss the mark (job changes are disruptive, even if they end up allowing for more development time). The nicest bit is that that the necessary changes also result in significantly better generated code.
2) Background JIT compilation. Right now the JIT compilation happens in the foreground. We really ought to only do the IR generation in foreground, and then do the compilation in the background, while continuing with interpreted execution. Only once codegen is done, we'd redirect to the JITed program (there'd be a bit higher overhead during the interpreted phase, rechecking whether to now redirect, but not that large).
3) Improve costing logic. E.g. we don't take the size of the necessary generated code into account at the moment, and we should. The worker count isn't taken into account either.
4) Improve optimization pipeline. There's plenty cases where we don't run beneficial and cheap-ish optimization passes, and there's plenty cases where we run unlikely to be helpful and really expensive optimization passes.
Because an RDBMS need not be I/O-bound. It might be compute bound (e.g., if the dataset fits in memory), so optimizing the compute-side can help. RDBMes generally compile queries into a "plan" that is then interpreted to execute it (SQLite3 compiles queries into bytecode, while PostgreSQL compiles them into AST-like tree structures). JITting certain portions of a query plan can help it go faster.
In fact, I'd say it's extremely common for database workloads to be compute bound on modern hardware.
For one, storage bandwidth has increased massively (my laptop's NVMe drives can do 2 x 3.2GB/s reads, you can get quite a few into even small servers). While memory latency and CPU throughput have not increased to the same degree.
To benefit from the increases in CPU throughput, one needs to take advantage of superscalar execution. Which isn't significantly possible with interpreted execution.
> SQLite3 compiles queries into bytecode, while PostgreSQL compiles them into AST-like tree structures
FWIW, expressions are compiled into bytecode in PostgreSQL as well. While there'd plenty benefit of doing that for query trees as well, there are not quite as much raw execution speed reason for it as there is for expressions (as the individual "steps" are much coarser, so the tree walk overhead is proportionally smaller).
Being compute-bound is pretty standard for analytic queries (i.e. computing aggregate things over larger data sets). A lot of workloads do have high reuse rates of data so you'll get a lot of data cached in memory, and a lot of the processing is pretty CPU-intensive. Columnar data formats can also achieve very high compression rates, so a relatively small amount of data read off disk turns into a large number of rows. Plus, real queries often have insanely complex expressions (giant case statements, for example), that can burn a lot of compute.
It's very different from a OLTP workload where a query will read 10s or 100s of rows via a btree index.
Actually for the case it was on SSD. Most of the access is to compressed columnar data. The main table had 500+ columns of ~140m rows but with very low entropy (e.g. gender, city/district). Each column had its own file. And queries were relatively simple. At 300MB/s (at the time) it was pretty fast. Data was mmap since as in most real life scenarios, the access to the columns had a power law distribution. Most queries could resolve with RAM data (~24GB/s at the time). Data size was over 10x the size of RAM (~128GB). IO is fast if you do mostly sequential access. Note server NVMe today is ~8x faster so the principle still applies.
The main thing here is the simplicity of the architecture and combining HyPer, careful mmap usage, and having SQLite engine do all the other non-critical work. So this way this system could be implemented on time by 1 main developer (rockstar dev, Marcelo) and a tiny little help from me.
(Actual author of the idea/architecture here, not mentioned on the paper, but that's life, haha)
That's pretty sweet information. For columnar db, LLVM definitely helps as columnar wants to cram as much small type data as possible in memory and runs the CPU over them. I would imagine the condition evaluation in the where-clause would be benefited the most since it's mostly an evaluation of dynamic expression.
It's not completely uncommon for SQL code execution, especially in complicated legacy systems, to become a performance bottleneck. You normally think of data access being the resource the server is trying to manage, but occasionally it needs to churn on that data as its querying it (because the computed values can't be stored for some crazy reason). In such situations, it would surely be nice to have SQL code itself able to access low level optimizations.
Most SQL engines implement the query engine as a virtual machine that traverses the result on a row by row basis, under the presumption that I/O is the bottleneck. In other words, this is very slow on modern hardware.
SQL is mostly "do the same thing a billion times", which aligns very closely with generating code for the inner loop, particularly when the inner loop can be generated without branches.
SQL codegen is basically an industry standard for large-scale sql query processing on top of a large-scale column store. I work one of the few engines at this scale which does not do go query stage codegen & instead uses source codegen vectorized primitives instead of query time (Apache Hive, specifically & the only other one in the same style is Google's supersonic engine).
And as far as I know Redshift's horrible query compilation performance is derived from using C++ codegen which forks gcc inside (not sure why this is so slow though, almost feels like untuned gcc flags or something).
