Skip to content

How to optimize KDB-X performance

This page gives a measurement-first workflow for optimizing KDB-X. Use it to define the workload, find the limiting resource, and decide whether to change the data layout, hardware, operating system, or q code.

A query on a partitioned database reads column files from storage through the operating system's page cache, processes the data on the CPU, and sends a result over the network. The slowest stage limits the query. Making another stage faster does not improve the result and can make it worse: compression consumes CPU to save I/O, and parallelism consumes more cores and memory bandwidth to reduce elapsed time.

q has no query planner that reorders constraints or rewrites an expression for you. It evaluates the query in the form you write, while individual primitives and partitioned queries can use internal optimizations. This makes measurement and the shape of the code important.

Use this order:

  1. Define the target and workload.
  2. Establish a repeatable baseline.
  3. Find the bottleneck.
  4. Use the evidence to change the data layout, q code, operating system, or hardware. Prefer removing unnecessary work before adding capacity.
  5. Repeat the same test, and verify latency and throughput under representative concurrency.

Define the target and workload

"Make it faster" is not a measurable target. Decide which outcome matters before changing the system:

Goal Measure
Interactive query latency Median and tail latency, such as p50, p95, and p99
Query throughput Queries or rows processed per second at a stated concurrency
Ingestion Sustained rows or bytes per second, including logging and end-of-day work
Batch work Completion time and whether it overlaps the next scheduled run
Resource efficiency CPU time, peak resident memory, storage I/O, and network bytes per unit of work

Optimizing one goal can hurt another. Giving one query every core can reduce its latency but lower the throughput of a server with many clients. Batching updates raises throughput but adds delay to each update. Record both the primary target and the limits that must not regress.

Build a representative workload with:

  • production data volumes, types, cardinalities, skew, and order;
  • the usual date ranges, symbols, columns, joins, and aggregations;
  • the expected number of simultaneous clients and updates; and
  • both frequently accessed data and data that is not already in the page cache.

Establish a baseline

Keep the test conditions constant. Record the KDB-X version, command-line options, secondary-thread count, host type, storage and file system, data snapshot, and whether the data was warm or cold. Warm up code and data that are warm in production, run enough repetitions to expose variation, and check that every candidate returns the same result.

For an isolated expression, read the timer. For an end-to-end service, measure at the client so that queueing, IPC serialization, network transfer, and result deserialization are included. Report a distribution or percentiles, not only an average: a good average can hide long pauses.

Change one variable at a time. Compare the result with the baseline and retain the change only if it improves the target without violating another limit.

Find the bottleneck

Measure q and the operating system at the same time. A slow query with a busy CPU needs a different change from one whose CPU is idle while storage requests queue.

Time an expression

\t reports elapsed time in milliseconds. \ts reports elapsed time and the bytes allocated by the calling thread:

q)\ts:100 til 1000000         / total for 100 runs
9 8395184

The timer has millisecond resolution. Repeat a short expression with \t:n or \ts:n until the total is large enough to compare. Do not time console display as part of the expression unless display performance is the target.

The allocation number is not the retained size of the result. .Q.ts also returns the result, and is useful inside a function.

\ts reports the calling thread only

Memory allocated by secondary threads during peach does not appear in \ts or the main thread's .Q.w. Measure the resident set of a parallel computation from the operating system. See memory in secondary threads.

Check process memory

.Q.w reports the memory statistics of the calling thread, including memory in use, heap size, peak heap size, the workspace limit, and mapped files:

q).Q.w[]
used| 546992
heap| 67108864
peak| 67108864
wmax| 0
mmap| 0
mphy| 38654705664
syms| 925
symw| 43966

Use operating-system metrics for the total process, including secondary-thread heaps and resident mapped pages. See check memory usage for the meaning and limits of each q statistic.

Profile a running process

To find hot q functions without stopping the process, sample its call stack from another q process with .Q.prf0. At the same time, use operating-system tools to identify the saturated resource:

Resource Linux tools Evidence to look for
CPU top -H, htop, pidstat, perf top One thread continuously busy indicates a serial limit; many busy cores indicate compute or memory-bandwidth work.
Memory vmstat, free, numastat -p Swap activity, major page faults, allocation failures, or remote NUMA memory.
Storage iostat -xz, pidstat -d, iotop Rising request latency and queue depth while throughput stops increasing. %util alone is not a reliable saturation measure for parallel devices.
Network sar -n DEV, ss -ti Link throughput, retransmissions, drops, or growing socket queues.

Low CPU use is not proof that a process has spare capacity: it can be blocked on storage, the network, a lock, or another q process. Likewise, all cores being busy does not distinguish useful computation from memory stalls. Correlate several metrics over the same interval.

Compare cold and warm reads

The first query on a column can fault pages in from storage. A repeated query can find the data in the operating system's page cache and run much faster. Measure both when both occur in production:

  • Cold or storage-backed latency shows the cost of data that is not cached.
  • Warm latency shows the cost of computation and memory access after the working set is resident.

On Linux, dropping the page cache affects every process on the host and requires root access:

sync; echo 3 | sudo tee /proc/sys/vm/drop_caches

Use a dedicated benchmark host

Do not drop caches on a production or shared host. It evicts useful data for unrelated processes and can cause a large, immediate I/O load. Prefer a fresh benchmark instance or a data set larger than the available cache when you cannot isolate the host.

Interpret the result

Use the evidence to choose the next page:

Observation Likely next step
Many partitions or columns opened for a selective query Fix partitioning, sort order, attributes, and column selection.
Storage queueing on cold reads Improve the layout, compression, storage, or page-cache capacity.
One core is busy Vectorize or remove serial work, or split independent work safely.
Several cores are busy and scaling stops Measure memory bandwidth, storage, and thread scaling.
Heap or resident memory spikes Reduce intermediate results, copies, concurrency, or secondary-thread working sets.
Requests wait behind a long query Change the concurrency architecture.
Large results saturate the connection Aggregate on the server, return fewer columns or rows, and review network and IPC.
Latency varies by CPU socket Review NUMA placement.

Benchmark the system

Benchmarks answer different questions:

  • nano measures low-level CPU, memory, storage, and file-system operations from a q process. Use it to compare hosts and configurations.
  • NYSE TAQ benchmarks run representative financial queries on public TAQ data. Use them to compare query execution, attributes, and secondary-thread counts. KX publishes results at benchmark.kx.com.
  • STAC-M3 is an audited industry benchmark for time-series analytics on market data. Its reports are useful when comparing complete hardware and storage solutions.

These benchmarks complement, but do not replace, the representative workload defined above. A configuration that wins a sequential scan can lose a selective, latency-sensitive query.

Next steps