It's easier to answer, why every other system is slow.
I will tell some principles, ideas, and facts...
... but mostly do some handwaving.
Use the Best Algorithms?
— but there is no such thing as the best algorithms!
How not to write a DBMS:
We will write code in C++ / Rust / Asm (it will be fast!) and use simdjson (isn't it fast?) and Apache Arrow (shall be fast)
will use mmap (I heard it's fast) or no... in-memory database (RAM is fast!) and it will work on asynchronous Seastar framework (fast?)
... and it will be faster than ClickHouse!
Use the Best Algorithms?
Daniel Lemire, the author of simdjson library
But it is not enough :(
Example: so you want to parse and process JSON data, a lot of it.
The fastest library to parse JSON is simdjson. And ClickHouse is using it.
"The fastest command-line tools for querying large JSON datasets"
feat.: jq, dsq, trdsql, spark-sql, miller, spyql, octosql and ClickHouse.
* — with the same seed; but seeding not always help, e.g. CityHash is flawed.
Use the Best Algorithms?
What is the best hash function? How the author tested it?
Typical mistake:
— the author tested the hash function on every input size;
— the inputs of 1 byte, 2 bytes, ... 111 bytes, etc;
... but did not test in a loop with a random distribution of input sizes.
— using the same input size in a loop makes CPU branch predictor works too good;
... but did not test in a loop with a not so random distribution of input sizes.
— what if a real dataset has 90% strings of size zero, 9% strings of size 1..50 and 1% of strings around 1KB?
Always Dig Into Details
Malcolm in the Middle S03E06 - Health Scare, Fox Network, 2001
Always Dig Into Details
Example: you load a website and it's loaded in 5 seconds :(
You check a few more times and it loads instantly.
Wrong:
— it was a temporary glitch; ⌛ — it does not reproduce; ⏱ — the problem fixed by itself;
— why even bother?
Right:
— my precious request was processed slowly!!!
— let's find it in the logs.
I don't care if millions of other requests are processed quickly,
I want to know why that request was slow.
Why some queries are slower?
WITH
query_id = 'benchmark_16760_29_2' AS first,
query_id = 'benchmark_16760_29_3' AS second
SELECT
PE.Names AS metric,
anyIf(PE.Values, first) AS v1,
anyIf(PE.Values, second) AS v2
FROM clusterAllReplicas(
default, system.query_log)
ARRAY JOIN ProfileEvents AS PE
WHERE (first OR second)
AND (event_date = today()) AND (type = 2)
GROUP BY metric
HAVING v1 != v2
ORDER BY
(v2 - v1) / (v1 + v2) DESC,
v2 DESC,
metric ASC
Recipes: Always-on profiling. Telemetry and introspection.
What every thread on the server is doing now? SELECT arrayStringConcat(arrayMap(
x -> demangle(addressToSymbol(x)), trace), '\n')
FROM system.stack_trace
Where the query spend most of the time? SELECT arrayStringConcat(arrayMap(
x -> demangle(addressToSymbol(x)), trace), '\n')
FROM system.trace_log WHERE query_id = '...'
How many resources the query consumed? SELECT ProfileEvents FROM system.query_log
WHERE query_id = '...'
Always Dig Into Details
If you feel bored on Friday night, what to do:
1. Run your program under strace -f and explain the output.
2. Attach gdb to your program and write thread apply all backtrace.
3. Run sudo perf top on production. Look at the annotated asm.
4. If a query runs in 1 second, ask why not 500 ms.
5. If a query runs in 1 ms ask why it took a whole millisecond.
Design For Production Workloads
You cannot design a fast DBMS sitting in your auditorium at University of Something
... if you do, your DBMS will be fast on paper or on a few synthetic benchmarks (it's time to forget about TPC)
What to do:
— optimize for real production workloads;
— the more diverse workloads the better;
— do quick development iterations.
BusTub — educational DBMS from CMU with no production usage intent, and I like the logo.
You may not use the Offerings:
7. in order to benchmark Offerings or to build similar or competitive products or services.
— Snowflake Acceptable Use Policy
Do benchmarks. Don't prevent others from doing benchmarks.
Learn and improve. Don't do benchmarketing. Let your users do the marketing for you!
Always Continue Experimenting
If your system is heavily optimized, most of the new ideas are not going to work.
But you still have to collect ideas and try all of them!
And select one idea out of hundreds.
Examples:
— optimize hash table lookup with __builtin_prefetch; did not work;
— optimize hash table lookup with SIMD instructions; did not work;
— do instruction-parallel computation of hash functions; did not work;
— use Robin Hood hash table algorithm for GROUP BY; did not work;
— use Swiss Table hash table algorithm for GROUP BY; did not work;
— allocating more memory to avoid wraparound; did not work; — predicting hash table sizes for the query; worked! #33439
Always Continue Experimenting
Don't be afraid of changes. Rewrite the code all the time.
Automated CI tests will do the job.
Must have: functional and integration tests, fuzzing, and sanitizers.
370 billion records, 1 billion time series 2015..2022. I've loaded them to ClickHouse.
I've loaded them again. I've loaded them in 10 different ways while testing ClickHouse Cloud.
Now ClickHouse eats this dataset for breakfast (with 40 million rows/second insert speed per node).
Do Weird Things
Source: Aleksandr Kryukov, 2013, https://www.youtube.com/watch?v=ahQIQxq8JY4
Do Weird Things
Always challenge known beliefs.
ClickHouse is probably not good at large BLOBs?
— but what if I load 600 GB of HTML pages into ClickHouse?
Yes, I did it. And it went alright. #18842
ClickHouse is probably not good at direct inserts from web pages?
— but what if I build a pastebin service on top of this and persuade all my team to use it?
Yes, I did it. Welcome https://pastila.nl/
What will happen if I do 10 million HTTP requests with the url table function?
— it did not go alright, but we've fixed it! #29696
Do Weird Things
Always challenge known beliefs.
ClickHouse is probably not good
— as a key-value database?
— as a streaming data processing system?
— as a multi-tenant service with workload isolation?
— for vector search in embeddings?
— for full text search?
Hold my beer...
... or read the ClickHouse roadmap 2022.
Do whatever you want if you can make observations and derive conclusions.
Example: I've loaded the first 1 trillion prime numbers into ClickHouse:
CREATE TABLE primes (n UInt64 CODEC(Delta, ZSTD))
ENGINE = ReplicatedMergeTree ORDER BY n
The idea has no direct application: it's possible to generate the numbers on the fly inside the subranges at decent speed.
But this is how I've tested the reliability of
ClickHouse Keeper, MergeTree over S3, and Replicated databases.
For the curious readers: prove analytically that ZSTD compression is better than LZ4 on this dataset; prove that increasing ZSTD level does not make sense; estimate the size of the dataset on disk.
Have Fun
What is it?
clickhouse-local --query "
WITH number + 1 AS x
SELECT x % 15 = 0 ? 'FizzBuzz'
: (x % 3 = 0 ? 'Fizz'
: (x % 5 = 0 ? 'Buzz' : x::String))
FROM system.numbers" | pv >/dev/null
Is it fast?
clickhouse-local --query "
WITH number * 15 AS x
SELECT format('{}\n{}\nFizz\n{}\nBuzz\nFizz\n{}\n{}\nFizz\nBuzz\n{}\n
Fizz\n{}\n{}\nFizzBuzz\n',
(x + 1)::String, (x + 2)::String, (x + 4)::String, (x + 7)::String,
(x + 8)::String, (x + 11)::String, (x + 13)::String, (x + 14)::String)
FROM system.numbers FORMAT RawBLOB" | pv > /dev/null
— use good algorithms in a good context;
— dig into details;
— measure everything;
— be close to production;
— do benchmarks, more of them;
— never stop experimenting;
— test at scale;
— do weird stuff;
— have fun!
