Diagnosing Slow InfluxDB Queries for Tick Data
Summary
A trader describes processing roughly four years of financial tick data in InfluxDB to build candles. A single large query exhausted available memory, so they split the work into monthly queries, processed each chunk, and accumulated the output. Their timings show that querying the data took hours per chunk, while candle creation took much less time, prompting a question about whether the database performance was normal on their setup.
The reply argues that the observed rate is unexpectedly slow for candle calculations and recommends first measuring how long a simple, unaggregated query takes to transfer data from InfluxDB. This helps distinguish database retrieval or transfer bottlenecks from the candle-processing step. The document offers a diagnostic suggestion rather than a measured explanation or a tested fix. Its timings come from one user’s hardware, software versions, workload, and query approach, so they do not establish a general performance benchmark.
Key ideas
- Splitting a large historical tick query into monthly chunks can avoid memory exhaustion during processing.
- The reported workload took substantially longer to retrieve than to turn into candles.
- Measure a simple query without aggregations to isolate data-transfer time.
- A single hardware and software setup cannot establish normal InfluxDB performance.
Tags
Full text
# TS Database performance issue # TS Database performance issue I've just found out about this forum while searching for answers for an InfluxDB performance "issue". I'm using it to store financial tick data (7 fields per row), query it and process it into candles. At the moment I'm querying around 1 billion ticks, about 4 years of data, for a total weight of about 28GB. The first problem was InfluxDB loads the entire query on Ram every time, so my computer would crash; I had to solve this by subdividing the query into chunks manually (month by month), and iterating through them while appending processed data to a DataFrame, exporting it at the end of the last query. When I got it to work, those were my query times (number of rows on the left, query time in hours, minutes and seconds in the center, processing time for creating the candles, on the right): 9480222, 2:07:46, 38:26 12839124, 3:06:02, 24:48 17256737, 4:19:54, 26:07 13716707, 3:28:37, 18:54 12671435, 2:35:27, 20:58 11112483, 2:15:53, 26:53 17055181, 3:34:21, 52:18 21232810, 6:29:42, 1:33:52 16935780, 4:47:56, 1:05:11 Those numbers seem a bit off. The average is around 60-70k rows per minute, 1k rows per second. Since this is my first experience with TS Databases and with Influx, would you consider this performance normal? I'm running InfluxDB 1.7.9, Influx Python client 5.2.3, Python 3.7, running from Pycharm, on a MacBook Pro with 16GB Ram. Any tip is welcomed! I also read posts were a lot of people were advising on not using a database at all, just chunking data and storing it in files. That's something I intended to do for heavier data such as Order Book data, but I've already spent quite some time setting this up and building my infrastructure around it and I'd like to make it work decently at least for the time being. Thanks in advance ## Answer by chrisaycock (score 1) https://quant.stackexchange.com/a/54196 A modern computer can perform billions of calculations per second, so 60k rows per minute for OHLC is ridiculous. That implies millions of clock cycles per row! The first thing I would check is data-transfer times using a simple query (no aggregations) from Influx.
Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.