Troubleshoot query performance

This guide covers the two most common performance problems in Deephaven queries: a high update-cycle ratio and insufficient memory. On ticking tables, both problems show up as results that refresh late or a server that slows down as data arrives. It pays to catch them early. The guide first shows how to tell which problem a query has. It then explains the concepts the troubleshooting steps rely on, followed by the steps that address each problem.

Identify the problem

Before you change a query, find out which problem it has. Deephaven's performance tables record what the engine does over time:

  • The Server State table samples memory use and update-cycle durations. server_state builds it from the Server State Log.
    • Its UGPTimeRatio column is the ratio, measured as the fraction of each sampling interval that the engine spent running update cycles. A ratio close to 1 means the engine spends almost all of its time running update cycles, with little idle time between them. If the ratio stays close to 1, see High update-cycle ratio.
    • Its UsedMemRatio column is the fraction of the server's maximum memory in use. If it approaches 1, see Insufficient memory.
  • The Update Performance Log records how long each operation spends processing updates. When the ratio is high, the find slow operations example shows how to calculate each operation's share of the time and find the slowest operations.

Concepts

It's important to understand some Deephaven-specific concepts before continuing. Each subsection below describes a concept used in the troubleshooting steps that follow.

Update-cycle ratio

Deephaven performs calculations in update cycles. By default, the engine targets a cycle length of one second. A cycle can take longer when there is more work than fits in that time.

The ratio is the fraction of each update cycle that the engine spends processing data, such as adding columns and computing aggregations. For example, if the engine spends 200 ms processing data during a one-second update cycle, the ratio is 0.2 (20%).

Identify the problem shows where Deephaven reports the ratio for the whole engine and for each operation.

In-memory, formula, and memoized columns

Table operations create three common column types:

  • In-memory
    • The engine computes all of an in-memory column's values immediately and stores them in memory.
  • Formula
    • A formula column stores only the formula and computes values on demand.
  • Memoized
    • A memoized column caches the results of calculations so that subsequent requests for the same input values do not require recalculation.

Each type trades memory against computation:

  • In-memory columns store every value, so downstream operations read them without recalculating. The High update-cycle ratio section of this guide recommends them.
  • Formula columns store almost nothing and recalculate values on every read. The Insufficient memory section of this guide recommends them.
  • Memoized columns store one result for each distinct set of input values. They use less memory than in-memory columns only when a column has few distinct inputs. With many distinct inputs, the cache can use more memory than an in-memory column.

The Select and create columns guide covers these column types and the operations that produce them in more depth.

Row change notification

In Deephaven, a row change notification occurs when data in a row is modified. When that happens, the engine re-evaluates the operations that depend on the modified columns, such as formulas in update and aggregations in agg_by.

Static tables never produce row change notifications, because the table does not change. However, in ticking tables, row change notifications trigger regularly. Performant queries on ticking tables minimize the number of row change notifications that occur.

Tick amplification

Tick amplification occurs when an operation's downstream update reports more modified cells than the upstream update it processes. It has two causes:

  • Some operations, such as grouping and ungrouping, can't tell which cells changed. They must mark every cell that could have changed as modified, even when only one value changed.
  • Other operations, such as joins, really do change more cells. One changed right table row changes every left table row that pairs with it.

Avoid tick amplification lists the common sources, shows an example, and describes how to address each.

Troubleshooting steps

High update-cycle ratio

When the ratio nears 1, update cycles run longer than their target, so ticking results refresh less often. The following subsections describe some steps to reduce the ratio in your queries.

Create in-memory columns

In-memory columns trade memory for computation. When calculations are complex or expensive, perform them once and store the results. Downstream operations then read the stored values instead of recalculating them. Two selection operations create in-memory columns:

An in-memory column stores a value for every row, so its memory use grows with the table. See Choose the right column selection method for more information.

Reduce tick frequency

You can use snapshot_when to reduce the frequency at which a table ticks. Keep in mind that snapshotting a table pulls all of its data into memory. How to dial down the update frequency of ticking tables shows examples.

Reorder join operations

In queries on ticking tables, join the tables that tick most often last.

Each tick of a joined table sends row change notifications to every operation downstream of the join, even operations whose inputs did not change.

Consider three tables:

  • static_table is a static table.
  • table_a ticks once every 5 seconds.
  • table_b ticks every update cycle (once per second by default).

You need to do two things with these three tables:

  • Create a new column, NewColumn, from the OldColumn column that table_a contributes.
  • Join all three of them together.

You could do this in two ways. Option 1 joins table_b first:

Option 2 joins table_b last:

Option 2 is much more performant. table_b ticks five times as often as table_a, and in option 2 fewer operations process those ticks:

  • In option 1, every table_b tick passes through the second natural_join, the update operation, and everything downstream of them. Each of these operations processes the update. None of them recomputes NewColumn, because its input OldColumn comes from table_a and didn't change.
  • In option 2, the first natural_join and the update operation sit upstream of the table_b join. They process updates only when table_a ticks, once every 5 seconds. Each table_b tick notifies only the final join and the operations downstream of it.

Use dynamic filters

Dynamic filters reduce computational load by filtering a table based on the values in another table. where_in and where_not_in apply dynamic filters.

Define a table that holds the values of interest, such as the highest-volume stocks or all stocks in a specific sector, and filter on it to reduce the number of rows your query processes. When the values in that table change, the filtered result updates, and the changes propagate to downstream operations.

Avoid tick amplification

Tick amplification happens when an operation's update reports more modified cells than the update it processed. Common sources include:

  • Grouping and ungrouping
    • Adding or changing one row marks every existing row in its group as modified after ungroup.
  • Cross joins without the on parameter
    • Even when the left table is static, a change to one right table row changes one result row for every row in the left table.
  • Keyed joins, such as join with the on parameter and natural_join
    • A change to one right table row changes every left table row with a matching key.

For grouping and ungrouping, you can typically avoid tick amplification with partitioned tables. The following example adds a column Y to the ticking table t1 in two ways:

  • Group/ungroup: group_by groups the rows by A, update adds Y to each group, and ungroup expands the groups back into rows.
  • Partition/merge: partition_by splits t1 into a partitioned table with one constituent table for each value of A, proxy applies update to each constituent, and merge combines the constituents back into one table.

A listener on each table prints the number of rows added or modified in each update:

Initially, the output shows one change for each approach:

After the query runs for a while, the group/ungroup listener reports a growing number of changes while the partition/merge listener stays at one:

The group/ungroup version amplifies ticks because ungroup marks every row of a changed group as modified. The partition/merge version produces the same rows without amplification. The engine updates each constituent independently, so adding or changing one row affects only that row in the merged result.

For joins, the extra changes are real changes to the result, so partitioning the tables cannot avoid them without changing the result. Instead, reduce how often the right table ticks and filter the left table before the join.

Insufficient memory

Every Deephaven server has a configured maximum amount of memory. When memory usage approaches that maximum, query performance can degrade significantly. If a query requires more memory than is available, it fails with a Java OutOfMemoryError, and the server can become unstable.

When working with live data in ticking tables, the amount of memory a query requires typically grows over time. The extent of this growth depends on several factors, such as the growth rate of the data itself, the operations involved, and the table types used in the query. High memory usage is often unavoidable with large datasets, but the following steps can reduce it.

Create formula columns

Formula columns trade computation for memory. Deephaven calculates values when they are needed, such as when a downstream operation reads them. Deephaven offers two operations to create formula columns in tables:

Formula columns have trade-offs:

  • They let you create tables far larger than available memory could otherwise hold.
  • They lead to slower performance if formulas are complex or downstream operations read their values often.
  • They can lead to inconsistent results if a formula is not deterministic.

See Choose the right column selection method for more information.

Create memoized columns

Memoized columns work best when a calculation has a small number of distinct input values. Only lazy_update creates memoized columns. When an input value repeats, the engine returns the cached result instead of recalculating it. The column stores one result for each distinct set of input values, so its memory use grows with the number of distinct inputs, not with the number of rows. The cache never shrinks, so on a ticking table, memoized columns save memory only while the number of distinct inputs stays small.

Deephaven can vectorize a query-string call to a Python function, so that one call processes a chunk of rows instead of one row. lazy_update doesn't cache vectorized calls. For those calls, the formula runs for every cell, as it does in update_view. See Cache results with lazy_update.

Filter before joins

Filtering tables before joining them can significantly reduce memory overhead. The size of a join operation's state depends on the number of rows in the respective left and right tables, as well as the number of unique keys in the key columns.

Filtering the left table first also lowers the ratio for keyed joins. Fewer left table rows match each changed right table row, so each right table tick changes fewer result rows.