Tuning Workloads

The preserve_insertion_order Option

When importing or exporting datasets (from/to the Parquet or CSV formats), which are much larger than the available memory, an out of memory error may occur:

console

Out of Memory Error: failed to allocate data of size ... (.../... used)

In these cases, consider setting the preserve_insertion_order configuration option to false:

SQL

SET preserve_insertion_order = false;

This allows the system to re-order any results that do not contain ORDER BY clauses, potentially reducing memory usage.

Filter Conditions That Can Fail

Conditions joined by AND are checked one after another, and the rows one condition rejects are never passed to the next, so SereneDB checks the cheapest ones first. Many conditions can raise an error at run time, though: arithmetic, which fails on overflow, a cast that can overflow (such as BIGINT to SMALLINT), error(), or a call to an external service. Moving one of those changes which rows it sees, and so whether the query fails. The filter_reorder option sets how far conditions may move around the ones that can fail:

Value	What moves
never	Nothing moves, even when no condition can fail.
safe	Conditions that can fail stay where they are; the conditions between them are ordered by cost. Errors are raised exactly as with never.
fast (default)	A condition that cannot fail may also move ahead of the ones that can, but a condition that came before one that can fail stays before it. A row that such a moved condition rejects never reaches the condition that can fail, so an error it would have raised on that row is skipped.
always	Everything is ordered by cost, so a condition that can fail may run before the condition that guards it.

PostgreSQL orders conditions by estimated cost alone, as always does, and its documentation suggests a CASE expression when the order must hold.

The order the rules start from is not always the written one: a comparison of a column with a constant that is not pushed into the table scan is checked after the other conditions. With fast, the cheap conditions shrink the rows that an expensive condition that can fail runs on. Set safe when a query must fail on the rows the written order gives it. In this query the cast to TINYINT fails from 128 on. With fast, range < 100 runs first and the query succeeds; with safe, the cast runs first and the query fails:

SQL

SELECT count(*) FROM range(1000) WHERE range < 100 AND range::TINYINT < range + 1;
SET filter_reorder = 'safe';
SELECT count(*) FROM range(1000) WHERE range < 100 AND range::TINYINT < range + 1;

 count
-------
   100

db error: ERROR: Type INT64 with value 128 can't be cast because the value is out of range for the destination type INT8 when casting from source column range

The option applies to the current session, like other SET options.

Parallelism (Multi-Core Processing)

The Effect of Row Groups on Parallelism

SereneDB parallelizes the workload based on row groups, i.e., groups of rows that are stored together at the storage level. The default row group size in SereneDB's database format is 122,880 rows. Parallelism starts at the level of row groups, therefore, for a query to run on k threads, it needs to scan at least k * 122,880 rows.

The row group size can be specified as an option of the ATTACH statement:

SQL

ATTACH '${__TEST_DIR__}/db151.db' AS db151 (ROW_GROUP_SIZE 16384);

The performance considerations when choosing ROW_GROUP_SIZE for Parquet files apply verbatim to SereneDB's own database format.

Too Many Threads

Note that in certain cases SereneDB may launch too many threads (e.g., due to HyperThreading), which can lead to slowdowns. In these cases, it's worth manually limiting the number of threads using SET threads = X.

Larger-than-Memory Workloads (Out-of-Core Processing)

A key strength of SereneDB is support for larger-than-memory workloads, i.e., it is able to process datasets that are larger than the available system memory (also known as out-of-core processing). It can also run queries where the intermediate results cannot fit into memory. This section explains the prerequisites, scope, and known limitations of larger-than-memory processing in SereneDB.

Spilling to Disk

Larger-than-memory workloads are supported by spilling to disk. With the default configuration, SereneDB creates the ⟨database_file_name⟩.tmp temporary directory (in persistent mode) or the .tmp directory (in in-memory mode). This directory can be changed using the temp_directory configuration option, e.g.:

SQL

SET temp_directory = '/path/to/temp_dir.tmp/';

Blocking Operators

Some operators cannot output a single row until the last row of their input has been seen. These are called blocking operators as they require their entire input to be buffered, and are the most memory-intensive operators in relational database systems. The main blocking operators are the following:

• grouping: GROUP BY
• joining: JOIN
• sorting: ORDER BY
• windowing: OVER ... (PARTITION BY ... ORDER BY ...)

SereneDB supports larger-than-memory processing for all of these operators.

Limitations

SereneDB strives to always complete workloads even if they are larger-than-memory. That said, there are some limitations at the moment:

• If multiple blocking operators appear in the same query, SereneDB may still throw an out-of-memory exception due to the complex interplay of these operators.
• Some aggregate functions, such as list() and string_agg(), do not support offloading to disk.
• Aggregate functions that use sorting are holistic, i.e., they need all inputs before the aggregation can start. As SereneDB cannot yet offload some complex intermediate aggregate states to disk, these functions can cause an out-of-memory exception when run on large datasets.
• The PIVOT operation internally uses the list() function, therefore it is subject to the same limitation.

Profiling

If your queries are not performing as well as expected, it's worth studying their query plans:

• Use EXPLAIN to print the physical query plan without running the query.
• Use EXPLAIN ANALYZE to run and profile the query. This will show the CPU time that each step in the query takes. Note that due to multi-threading, adding up the individual times will be larger than the total query processing time.

Query plans can point to the root of performance issues. A few general directions:

• Avoid nested loop joins in favor of hash joins.
• A scan that does not include a filter pushdown for a filter condition that is later applied performs unnecessary IO. Try rewriting the query to apply a pushdown.
• Bad join orders where the cardinality of an operator explodes to billions of tuples should be avoided at all costs.

Prepared Statements

Prepared statements can improve performance when running the same query many times, but with different parameters. When a statement is prepared, it completes several of the initial portions of the query execution process (parsing, planning, etc.) and caches their output. When it is executed, those steps can be skipped, improving performance. This is beneficial mostly for repeatedly running small queries (with a runtime of < 100ms) with different sets of parameters.

Note that it is not a primary design goal for SereneDB to quickly execute many small queries concurrently. Rather, it is optimized for running larger, less frequent queries.

Querying Remote Files

SereneDB uses synchronous IO when reading remote files. This means that each SereneDB thread can make at most one HTTP request at a time. If a query must make many small requests over the network, growing SereneDB's thread pool with SET GLOBAL threads to larger than the total number of CPU cores (approx. 2-5 times CPU cores) can improve parallelism and performance.

Avoid Reading Unnecessary Data

The main bottleneck in workloads reading remote files is likely to be the IO. This means that minimizing the unnecessarily read data can be highly beneficial.

Some basic SQL tricks can help with this:

• Avoid SELECT *. Instead, only select columns that are actually used. SereneDB will try to only download the data it actually needs.
• Apply filters on remote Parquet files when possible. SereneDB can use these filters to reduce the amount of data that is scanned.
• Either sort or partition data by columns that are regularly used for filters: this increases the effectiveness of the filters in reducing IO.

To inspect how much remote data is transferred for a query, EXPLAIN ANALYZE can be used to print out the total number of requests and total data transferred for queries on remote files.

Caching

Starting with version 1.3.0, SereneDB supports caching remote data. To inspect the content of the external file cache, run:

SQL

FROM sdb_external_file_cache();

Best Practices for Using Connections

SereneDB will perform best when reusing the same database connection many times. Disconnecting and reconnecting on every query will incur some overhead, which can reduce performance when running many small queries. SereneDB also caches some data and metadata in memory, and that cache is lost when the last open connection is closed. Frequently, a single connection will work best, but a connection pool may also be used.

Using multiple connections can parallelize some operations, although it is typically not necessary. SereneDB does attempt to parallelize as much as possible within each individual query, but it is not possible to parallelize in all cases. Making multiple connections can process more operations concurrently. This can be more helpful if SereneDB is not CPU limited, but instead bottlenecked by another resource like network transfer speed.

Persistent vs. In-Memory Tables

SereneDB supports lightweight compression techniques. By default, compression is only applied on persistent (on-disk) databases and not on in-memory tables.

In some cases, this can result in counter-intuitive performance results where queries are faster on on-disk tables compared to in-memory ones. Let's take Q1 of the TPC-H workload for example on the SF30 dataset:

SQL

CALL dbgen(sf = 30);
.timer onPRAGMA tpch(1);

We run this script using three SereneDB prompts:

Database setup	SereneDB prompt	Execution time
In-memory DB (uncompressed)	serened shell	4.22 s
In-memory DB (compressed)	serened shell -cmd "ATTACH ':memory:' AS db (COMPRESS); USE db;"	0.55 s
Persistent DB (compressed)	serened shell tpch-sf30.db	0.56 s

We can observe that the compressed databases are about 8x faster compared to the uncompressed in-memory database.