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"
I decided to write this post in response to my recent discussions with Matt Martin. Matt and I have been sparring lately over software performance. During one of these discussions, Matt said the magic words in the title of this post. I hypothesise that this quote carries of lot of explaining power for those who are not fully familiar with databases.
Let us play with this idea a bit! I'm going to use Python to show examples of "database loops". Obviously, no one in their sane mind would implement a database in Python. But Python has the great advantage of being readable to nearly everyone. Let's go...
When designing database schemas, you may find yourself needing to create a property bag table that is in a 1-n
relationship with an entity table. It isn't always viable to just add more columns to a table because you may not know
all the properties you want in advance. The problem with these property bags is that they can be hard to query.
In this blog entry, we will explore the property bag data modelling use case and provide some tricks that will make it
very simple to query those bags.
When writing code for analytical databases, there comes a point where you learn that window aggregates often allow you to express complex joins in new ways. Sometimes, they even allow you to get rid of a join entirely. In this blog, we look at a clever way to look for next and previous values in a log file stored in a SQL database table.
These days, columnar storage formats are getting a lot more attention in relational databases. Parquet, with its superior compression, is quickly taking over from CSV formats. SAP Hana, Vertica, Yellowbrick, Databricks, SQL Azure and many others all promote columnar representations as the best option for analytical workloads.
It is not hard to see why. But there are tradeoffs.
A central value add of modeling databases for analytical purposes and speed is that you can restore the sanity, often lost in the source system, that comes from using good keys in each table.
But what exactly does it mean for a key to be "good"?
If you ever looked at query plan from a SQL databases - you are likely to have come into something called "cost". You
may even have heard that most advanced database use "cost based optimisers" But what exactly is cost and how does the
database use it?
Most relational databases are both by human SQL query writers and tools that automatically generate queries. Query tools frequently add joins into the queries, even when those joins are not needed. This results in unnecessary query slowdowns and increase CPU usage.
For example, consider the classic Star Schema model with a fact table in the middle and dimension tables surrounding it. From a reporting tool perspective, it is useful to have a view on top of that schema which joins all the tables and presents the user with the illusion of a single table. This approach of creating large join views has some drawbacks that can only be offset by query optimisers if the database designer has correctly declared key constraints in the data model.
In this final instalment of the synchronisation series, we will look at fully scalable solutions to the problem first
stated in Part 1: adding monitoring to a high speed app where that monitoring is
scalable and minimally intrusive.
Thus far, we have seen how there is an upper limit on how fast you can access cache lines shared between multiple cores.
We have tried different synchronisation primitives to get the best possible scale.
Throughput this series, Henk van der Valk has generously lent me his 4
socket machine and been my trusted lab manager and reviewer. Without his help, this blog series would not have been
possible.
And now, as is tradition, we are going to show you how to make this thing scale!
In the previous instalments (Part 1 and Part 2) of this series, we have
drawn some conclusions about both .NET itself and CPU architectures. Here is what we know so far:
When there is contention on a single cache line, the lock() method scales very poorly and you get negative scale the
moment you leave a single CPU core.
The scale takes a further dip once you leave a single CPU socket
Even when we remove the lock() and do racy operations, scalability is still poor
Going from a standard struct to a padded struct provides a scale boost, though not enough to get linear scale
The maximum theoretical scale we can get with the current technique is around 90K operations/ms.
In this blog entry, I will explore other synchronisation primitives to make the implementation un-racy again.
We will be looking at spinlock and Interlocks.
