We have previously seen that optimising database CPU usage requires a solid foundation in computer science and algorithms. To properly scale queries - you must master single threaded, classical algorithms as well as modern concurrency and high speed locking.
Today, we take a break from the heavy computer science algorithms and zoom into transaction logs - a crucial component of every database. Our journey is to the land of I/O and hardware knowledge.
On the surface, the problem we try to solve appears simple and straightforward. But at this point, you know that this isn't how we travel together.
In our previous blogs, we have visited the idea that "databases are just loops". At this point, my dear readers may rightfully ask: "if those database are indeed just loops - why is it they are so hard to make?" "Why have we not nailed databases once and for all?" The answer is complicated - but I want to take a stab at it.
Today, we look at the CPU usage of databases - delving deeper into what those loops actually look like. We will see how you use "just loops" thinking to make predictions about what database you want to deploy. You will learn how to sanity existing database platforms - to cut through all the bullshit in the market.
Our database journey makes a brief stop. We need to appreciate an important design decision every database must make: Should I use row or batch execution?
Depending on the database vendor - you may get one or the other - or even both. What are the trade-offs? Let's take a look.
I recommend reading my previous posts before exploring this one, if you have not done so already:
Part 1: Joins
Part 2: Aggregates
In preparation for exploring the design space, we must now consider a slightly more complicated query than those we've seen so far:
SELECT a.x, SUM(b.y)
FROM a
INNER JOIN b ON a.x = b.y
GROUP BY a.x
This query does two operations:
Join a.x = b.y
Aggregate function SUM on b.y using grouping column a.x
There are a few more operations in play here that you may not think about as operations yet:
Finding rows in a for the join
Finding rows in b for the join
Sending rows to the client
Depending on the access method (=algorithm) chosen for the join, what exactly happens in that "finding" of rows depends on the database. For our example, I will assume a hash join is used.
As you can see at this point, writing out what a query does and how it does it, gets tedious and wordy. We are only looking at a simple query so far - and using plain English to explain what it does is getting complicated.
Because database people live with databases every day, the good database vendors have come up with another way to render what happens in queries. With a bit of training, you can easily learn how to read this.
In my previous post - I introduced the idea that you can think of database queries
as a series of loops. Let me take this ideas even further - introducing more complex database concepts in terms more
familiar to computer programmers.
Databases can do this cool thing called "aggregation". You may think of it like GROUP BY. You will have seen queries
like this:
SELECT x, SUM(v) AS agg_y
FROM a
GROUP BY x
What is going on here? And how can we think of this like a loop?
Aggregate or GROUP BY?
To establish some terminology: You will hear me refer to GROUP BY as "aggregation" in the following passages.
We will call SUM(v) above an "aggregate function" and we will call x a "grouping column". We will refer to the
entire loop as the "aggregation loop"
