Pivotal Engineering Blog

> I don't understand why SQL on LLVM?

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).

Apache Impala - SQL to LLVM IR (C++)

https://llvm.org/devmtg/2013-11/slides/Wanderman-Milne-Cloudera.pdf [PDF]

Gandiva - SQL to LLVM IR (Java)

https://github.com/dremio/gandiva

MemSQL - SQL to MPL to LLVM IR

http://highscalability.com/blog/2016/9/7/code-generation-the-inner-sanctum-of-database-performance.html

Greenplum - SQL to LLVM IR

http://engineering.pivotal.io/post/codegen-gpdb-qx/

Postgres 9.4 Vitesse - SQL to LLVM IR

<https://www.postgresql.org/message-id/CAJNt7%3DZ6w5%2BwyeTKKryWavMcWOenc6ZhPaaaddwuZ6HtcJWxcg@mail.gmail.com>

Postgres 11 LLVM - SQL to LLVM IR

https://www.postgresql.org/docs/11/jit-reason.html

SparkSQL - SQL to Java source (Janino + javac)

https://jaceklaskowski.gitbooks.io/mastering-spark-sql/spark-sql-whole-stage-codegen.html

And as far as I know Redshift's horrible query compilation performance is derived from using C++ codegen which forks…

For those familiar with BOSH, Pivotal engineers built "Tornado" for load testing. It's a BOSH release of JMeter with some convenient dials.

Since it's BOSH, you can deploy to AWS, GCP and Azure without fuss. The difference between running one worker, ten workers or a thousand workers is a line of YAML and how much you're prepared to pay.

There are some blog posts:

"Half a Million Concurrent/Distributed JMeter Users with BOSH ... in 10 Minutes" (http://engineering.pivotal.io/post/jmeter-bosh-usecase/)

"BOSH + Apache JMeter(TM) = Tornado for Apache JMeter(TM)" (http://engineering.pivotal.io/post/jmeter-bosh-release/)

The release itself: https://github.com/jamlo/jmeter-bosh-release

Disclosure: I work for Pivotal, BOSH is definitely one of our favouritest things.

AFAIK, there are only a handful of DBMSs that do complete query compilation with the LLVM:

* MemSQL (http://highscalability.com/blog/2016/9/7/code-generation-the-inner-sanctum-of-database-performance.html)

* Tableau/TUM HyPer (https://blog.acolyer.org/2016/05/23/efficiently-compiling-efficient-query-plans-for-modern-hardware/)

* CMU Peloton (http://db.cs.cmu.edu/papers/2017/p1-menon.pdf)

* Vitesse (https://www.youtube.com/watch?v=PEmVuYjhQFo)

I think Greenplum was talking about doing this too, but that was about a year ago (http://engineering.pivotal.io/post/orca-profiling/)

Most systems just compile the predicates (Impala, SparkSQL).

A lot of companies are talking about adding this now. The performance gains are significant.

I've also benchmarked GCP vs. AWS [0], and, for the tests that I ran, found that GCP outperformed AWS by a factor of 3:1. Specifically, a GCP instance n1-highcpu-8 with a 256GB pd-ssd disk, clocked in at 11,728 IOPS vs an AWS c4.xlarge with a 256GB gp2 disk, clocking in at 3,634 IOPS.

To put that in context of the blog post, it means your setup can drastically affect your results. Using local NVMe disk, for example, yields excellent results at the expense of increased risk. Also, AWS's io1 disk is very expensive—after my first io1 bill from AWS, I never used that disk type again.

[0] http://engineering.pivotal.io/post/gobonniego_results/

Not a perfect choice of terms I freely admit. In my own experience, I last used non-virtual, mutable, long-lived servers over 8 years ago. Since then it's been a progression further away from that style: PaaS, immutable infrastructure, Heroku, and currently a mix of AWS Lambda and Docker.

I'm excited to try Elixir but from what I've read (e.g. https://medium.com/mint-digital/elixir-deployments-on-aws-ee787aa02a9d, http://engineering.pivotal.io/post/how-to-set-up-an-elixir-cluster-on-amazon-ec2/), much of my recent experience in running production apps at scale won't apply to Elixir/Phoenix.

I googled that and was pleasantly surprised to learn something new.[1]

But if I'm reading that right I would probably want to do both. Because overcommit settings seem to be a memory saving technique. To avoid needing swap by decreasing the allowed overcommit memory.

Anyways, in the many years that I've been changing vm.swappiness I've never had any issues and I've always had a minimum of 8G RAM so I don't think this will be a big issue.

1. http://engineering.pivotal.io/post/virtual_memory_settings_in_linux_-_the_problem_with_overcommit/