How is hash aggregation implemented in MariaDB?

Hash aggregation is a type of aggregation algorithm used in MariaDB to efficiently calculate aggregate functions such as SUM, COUNT, MAX, and MIN on large data sets. It works by dividing the data into groups based on a grouping key, hashing each group into a separate memory buffer, and then calculating the aggregate functions on each buffer.

Here’s how hash aggregation is implemented in MariaDB:

  1. The query optimizer first identifies the grouping columns in the query and determines whether hash aggregation is an appropriate algorithm to use.
  2. If hash aggregation is selected, the query optimizer creates an intermediate result set that contains the grouping columns and the columns used in the aggregate functions.
  3. The intermediate result set is then partitioned into multiple smaller partitions based on the hash value of the grouping key. Each partition is stored in a separate memory buffer.
  4. For each partition, the aggregate functions are calculated and the results are stored in memory.
  5. Once all partitions have been processed, the results are combined into a final result set and returned to the user.

Hash aggregation is very efficient for large data sets because it can parallelize the computation across multiple CPU cores and memory buffers. However, it requires a significant amount of memory to store the intermediate results, so it may not be appropriate for queries that involve a large number of groups or aggregate functions.

In MariaDB, you can monitor hash aggregation performance and memory usage using the SHOW STATUS LIKE ‘Handler_read%’ and SHOW STATUS LIKE ‘Handler_tmp%’ commands. These commands will display statistics about the number of read and write operations performed by the hash aggregation algorithm, as well as the amount of memory used for temporary tables and buffers.

Example

Here’s an example of how hash aggregation works in MariaDB.

Consider the following table named “sales” containing sales data for a company:

Let’s populate the table with some sample data:

Now, let’s say we want to calculate the total sales amount by product and category. We can achieve this using hash aggregation as follows:

The output of the explain plan would be:

In this query, the GROUP BY clause groups the sales data by product and category. The SUM function computes the total sales amount for each group. Since the dataset is large, hash aggregation is used instead of sorting-based aggregation to compute the results efficiently.

In hash aggregation, the data is partitioned into buckets based on the grouping keys (product and category in this case) using a hash function. The aggregate function is then computed for each bucket, and the results are combined to produce the final output. This method is more efficient than sorting-based aggregation as it avoids the expensive sorting operations and only requires a single pass over the data.

The output of this query would be:

In summary, hash aggregation is a powerful technique used in database systems to efficiently compute aggregate functions over large datasets. In MariaDB, hash aggregation can be used to compute aggregate functions like SUM, COUNT, MAX, MIN, and AVG, and it is particularly useful when dealing with large datasets where sorting-based aggregation is too costly.

About Shiv Iyer 437 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.