8 ms·

I actually just migrated 20 million rows of Magic: the Gathering price data from influxDB to postgres this week. For a few days of effort, I decreased my query

by Everlag 11y ago

I actually just migrated 20 million rows of Magic: the Gathering price data from influxDB to postgres this week. For a few days of effort, I decreased my query latency by an order of a magnitude; a full set query, roughly 270 cards, went from 30 to 3 seconds with a cold cache. The migration was prompted by influxDB 0.8 eating 50% of the VPS' cpu and 77% of the ram while idling. It had no capability to index along anything but time so every query, for my use case, required a full table scan. 0.9 was supposed to fix every issue I had with it but it was due to be 'production ready' months ago. Unless you're dealing with ingesting an absolutely insane amount of data indexed along time, I'd have to say that postgres or comparable sql database should be more comfortable, more stable, and much more mature. EDIT: I don't want to come off as shitting all over influxDB, to its credit it barely moved beyond idle resource usage when I was stuffing it full of data.

bane 11y ago
> 20 million rows > 30 seconds Ugh, that's terrible. grepping the file or reading and parsing a csv file is probably faster.
- Everlag 11y ago
  It was pretty awful watching the vps choke to death when I tried implementing any feature using full set prices. Pegged at 99% cpu usage with the go garbage collector frantically trying to not let the process crash... that was not an environment I wanted to take to production.
  - martindale 11y ago
    > vps There's your problem. If you're at the "make it fast" part of "make it work, make it right, make it fast", you should almost certainly be on dedicated hardware.
    - Everlag 11y ago
      That was actually the "make it work" portion: I was attempting to grab some 10 full set prices at a time for a gallery feature and managed to crash the influxdb.
  - bane 11y ago
    Could it be the environment didn't have enough memory to do things? I've seen frantic swapping and garbage collection before in VMs without enough RAM.
manigandham 11y ago
Most SQL databases can scale and handle massive amounts of time series data, especially if they have columnstore features which make scans incredibly fast... while still giving all the advantages of adhoc SQL queries. Most purpose-built time-series stuff isn't really necessary for much of what I see people trying to use it for.
philsnow 11y ago
> influxDB 0.8 eating 50% of the VPS' cpu and 77% of the ram while idling > to [InfluxDB's] credit it barely moved beyond idle resource usage when I was stuffing it full of data. solution: always be cramming it full of data ? I don't see how it could idle when you're bulk writing, but when it's not serving any traffic it's taking up 50% of cpu (unless it's doing some kind of indexing or cleanup or something in the background, but you would expect that to eventually quiesce).
- scott_karana 11y ago
  I think he meant that idle CPU (50%) and full throttle usage (51%) were hardly different? Who knows. :P
olviko 11y ago
Same here. InfluxDb has a pretty nice DSL. I wish they switched their backend to something usable and mature instead of re-inventing the bicycle. Operationally, it is expensive to support specialized databases like Influx, unless it is your core business, I guess...
tsdbase 11y ago
I've hit the same problem and I would like to move back to a SQL data store. However none of the nice dashboards / visualizations support postgres or any SQL database (for now)... My question (to everyone): what do you use as replacement for kibana or grafana?
- Mahn 11y ago
  Did you consider a hybrid solution? You could store the most recent data in time series database for visualization purposes and dump the rest into a traditional SQL data store. Other than that, IIRC Grafana had plans for PostgresSQL but it's not there yet.
- robochat 11y ago
  I've just implemented a custom backend for graphite-api which seems to be working ok although I don't have crazy requirements. https://github.com/brutasse/graphite-api https://github.com/brutasse/graphite-api is a cleaned up fork of graphite (which is much easier to install). I'm using grafana as the front-end and my data is in a postgresql database and graphite-api is linking them together.
  - pierluca 11y ago
    Hello, I find myself having the same need. Would you agree to share your implementation or point me to it? Thank you!
- sleepydog 11y ago
  If I decided to move to using a SQL data store (I use graphite now), I would re-implement the graphite API as a listener process, or write a graphite backend. The biggest strength of Graphite is how simple it is to upload and query metrics, and I wouldn't want to lose that even if the backend were to change.
- jrv 11y ago
  If you don't need to keep data forever, but only several weeks or months, and you only need numeric time series data and not raw event logs, Prometheus (http://prometheus.io/ http://prometheus.io/) is your friend. Since it's optimized towards purely numeric time series (with arbitrary labeled dimensions), it currently uses an order of magnitude less disk space than InfluxDB for this use case, and I've also heard a few reports of people's CPU+IO usage dropping drastically when they switched from InfluxDB to Prometheus for their metrics. As dashboards for Prometheus, you can currently use PromDash (http://prometheus.io/docs/visualization/promdash/ http://prometheus.io/docs/visualization/promdash/), Console HTML templates (http://prometheus.io/docs/visualization/consoles/ http://prometheus.io/docs/visualization/consoles/), or Grafana (http://prometheus.io/docs/visualization/grafana/ http://prometheus.io/docs/visualization/grafana/). Durable long-term storage is still outstanding. Although replication into OpenTSDB and InfluxDB is experimentally there.
gtrubetskoy 11y ago
I've found PostgreSQL to be extremely fast if you store time series in arrays (http://www.postgresql.org/docs/9.4/static/arrays.html http://www.postgresql.org/docs/9.4/static/arrays.html) in a round-robin fashion. You can also limit the array size, so that you have a fixed number of points per table row (thereby splitting your series across multiple rows), and if you adjust it such that it fits on one PG page it is quite performant.
- ddorian43 11y ago
  I don't think you can limit them: However, the current implementation ignores any supplied array size limits, i.e., the behavior is the same as for arrays of unspecified length.
  - gtrubetskoy 11y ago
    By "limit" I mean your code would do it, not Postgres. E.g. your series is 86400 datapoints long (seconds in a day), you would store it as 100 rows of 864-element-long arrays.
Mahn 11y ago
Just for the record, InfluxDB 0.9 seems to actually be production ready now. Though it doesn't look there's an easy way to migrate to it yet from 0.8.
- Kirth 11y ago
  "Seems to be", sure. Sinks like a tanker when you try to actually use it, though. None of the software libraries have been updated for 0.9 yet.
  - Mahn 11y ago
    > Sinks like a tanker when you try to actually use it Can you name some examples of this besides the CPU authentication bug? Honest question, I'm considering adding InfluxDB to our stack so I'm genuinely curious.
    - Kirth 11y ago
      We've had issues with Influx being a massive resource hog. Not properly persisting data to disk. They openly admit that Influx isn't built to gracefully recover from crashes, and that you will lose data. This by itself wouldn't be a problem for us. But when you're inserting data points, the entire damn thing seems to become unresponsive. Admin UI freezing, http endpoint no longer responding to queries, ... I know this product is relatively young and your mileage may vary. They have a long way to go.
- simonpantzare 11y ago
  Authentication in 0.9 has a high CPU penalty right now: https://news.ycombinator.com/item?id=9810538 https://news.ycombinator.com/item?id=9810538.
- sciurus 11y ago
  Based on the changes in 0.9.1, there's no way that 0.9 was production ready and I'd be very cautious about using it. https://influxdb.com/blog/2015/07/02/InfluxDB-0_9_1-and-Telegraf-0_1_2-released-with-new-docs.html https://influxdb.com/blog/2015/07/02/InfluxDB-0_9_1-and-Tele...
rdtsc 11y ago
I evaluated InfluxDB for an advanced packet capture and processing application and it couldn't handle things very well. Namely expiry of old data, blocking too much on inserts. So I wrote my own in Python + C extensions. It turned out well. Has been going non-stop for year and a half now.
- rw 11y ago
  What data structures does it use?
  - rdtsc 11y ago
    For the main data and index just a plain sorted list of tuples. Main data file is [ (timestamp,data), ...] and index is [ (timestamp,offset_in_datafile), ... ]. There is a requirement that ntp server must be running and the machine synchronized to it. Then all the files have a prefix that look like <startsec>_<startmicrosec>_<metric>. A binary search is perfomed first on files in order to pick the right files only. Then the index is read into memory, then binary search performed on the index to get the offset range and then the main data file is read. When switching to new file time chunk, a cleanup action is peformed where previous old files are removed.
LukeHoersten 11y ago
It'd be great to see a more detailed guide to using and tuning PGSQL for use as a TS DB.
- 15155 11y ago
  As an addendum, I'd like to be able to do time-based append-only operations in Postgres, a la Datomic. It pains me to UPDATE and DELETE, thereby destroying useful data.
  - simonb 11y ago
    Would Timetravel module work for your use-case: http://www.postgresql.org/docs/9.1/static/contrib-spi.html#AEN137462 http://www.postgresql.org/docs/9.1/static/contrib-spi.html#A...
andyl 11y ago
Curious if anyone has experienced problems like this in InfluxDB 0.9 ?? My 0.9 implementation is performing well, but has only small amounts of metrics for testing - not yet rolled out to production.
- simonpantzare 11y ago
  Authentication in 0.9 has a high CPU penalty right now: https://news.ycombinator.com/item?id=9810538 https://news.ycombinator.com/item?id=9810538.
simonpantzare 11y ago
For those of you reading this that are interested in getting on InfluxDB 0.9. If you intend to use authentication and have a somewhat high request rate, I advise you to wait until the fixes related to #3102 (https://github.com/influxdb/influxdb/issues/3102 https://github.com/influxdb/influxdb/issues/3102) are included. Without that fix your CPU is killed because InfluxDB is bcrypting on every request.
- CSDude 11y ago
  That explains why my updates on 100ms intervals seemed like hogging resources.
pauldix 11y ago
On the performance problem, my guess is that you wrote in a bunch of columns and had those in where clauses. I hope the documentation made it clear that you'd be range scanning over data and that you probably wouldn't get desirable performance. In 0.8 and before, the preferred way to model your data was to create many separate series names. This method is currently giving many users great performance. As with all databases, how you model your schema has a significant impact on performance. That being said, it might be that Influx wasn't right for your use case and Postgres is just a better thing to go with. Also, we're not supporting anything prior to the 0.9 series of releases. 0.8 is deprecated and we'll be pushing everyone to move over to 0.9 as we put out more point releases and fix bugs and ensure that it works for their use case over the coming months.

by Everlag 11y ago

bane 11y ago

> 20 million rows > 30 seconds Ugh, that's terrible. grepping the file or reading and parsing a csv file is probably faster.

Everlag 11y ago
It was pretty awful watching the vps choke to death when I tried implementing any feature using full set prices. Pegged at 99% cpu usage with the go garbage collector frantically trying to not let the process crash... that was not an environment I wanted to take to production.
- martindale 11y ago
  > vps There's your problem. If you're at the "make it fast" part of "make it work, make it right, make it fast", you should almost certainly be on dedicated hardware.
  - Everlag 11y ago
    That was actually the "make it work" portion: I was attempting to grab some 10 full set prices at a time for a gallery feature and managed to crash the influxdb.
- bane 11y ago
  Could it be the environment didn't have enough memory to do things? I've seen frantic swapping and garbage collection before in VMs without enough RAM.

manigandham 11y ago

Most SQL databases can scale and handle massive amounts of time series data, especially if they have columnstore features which make scans incredibly fast... while still giving all the advantages of adhoc SQL queries. Most purpose-built time-series stuff isn't really necessary for much of what I see people trying to use it for.

philsnow 11y ago

> influxDB 0.8 eating 50% of the VPS' cpu and 77% of the ram while idling > to [InfluxDB's] credit it barely moved beyond idle resource usage when I was stuffing it full of data. solution: always be cramming it full of data ? I don't see how it could idle when you're bulk writing, but when it's not serving any traffic it's taking up 50% of cpu (unless it's doing some kind of indexing or cleanup or something in the background, but you would expect that to eventually quiesce).

scott_karana 11y ago
I think he meant that idle CPU (50%) and full throttle usage (51%) were hardly different? Who knows. :P

olviko 11y ago

Same here. InfluxDb has a pretty nice DSL. I wish they switched their backend to something usable and mature instead of re-inventing the bicycle. Operationally, it is expensive to support specialized databases like Influx, unless it is your core business, I guess...

tsdbase 11y ago

I've hit the same problem and I would like to move back to a SQL data store. However none of the nice dashboards / visualizations support postgres or any SQL database (for now)... My question (to everyone): what do you use as replacement for kibana or grafana?

Mahn 11y ago
Did you consider a hybrid solution? You could store the most recent data in time series database for visualization purposes and dump the rest into a traditional SQL data store. Other than that, IIRC Grafana had plans for PostgresSQL but it's not there yet.
robochat 11y ago
I've just implemented a custom backend for graphite-api which seems to be working ok although I don't have crazy requirements. https://github.com/brutasse/graphite-api https://github.com/brutasse/graphite-api is a cleaned up fork of graphite (which is much easier to install). I'm using grafana as the front-end and my data is in a postgresql database and graphite-api is linking them together.
- pierluca 11y ago
  Hello, I find myself having the same need. Would you agree to share your implementation or point me to it? Thank you!
sleepydog 11y ago
If I decided to move to using a SQL data store (I use graphite now), I would re-implement the graphite API as a listener process, or write a graphite backend. The biggest strength of Graphite is how simple it is to upload and query metrics, and I wouldn't want to lose that even if the backend were to change.
jrv 11y ago
If you don't need to keep data forever, but only several weeks or months, and you only need numeric time series data and not raw event logs, Prometheus (http://prometheus.io/ http://prometheus.io/) is your friend. Since it's optimized towards purely numeric time series (with arbitrary labeled dimensions), it currently uses an order of magnitude less disk space than InfluxDB for this use case, and I've also heard a few reports of people's CPU+IO usage dropping drastically when they switched from InfluxDB to Prometheus for their metrics. As dashboards for Prometheus, you can currently use PromDash (http://prometheus.io/docs/visualization/promdash/ http://prometheus.io/docs/visualization/promdash/), Console HTML templates (http://prometheus.io/docs/visualization/consoles/ http://prometheus.io/docs/visualization/consoles/), or Grafana (http://prometheus.io/docs/visualization/grafana/ http://prometheus.io/docs/visualization/grafana/). Durable long-term storage is still outstanding. Although replication into OpenTSDB and InfluxDB is experimentally there.

gtrubetskoy 11y ago

I've found PostgreSQL to be extremely fast if you store time series in arrays (http://www.postgresql.org/docs/9.4/static/arrays.html http://www.postgresql.org/docs/9.4/static/arrays.html) in a round-robin fashion. You can also limit the array size, so that you have a fixed number of points per table row (thereby splitting your series across multiple rows), and if you adjust it such that it fits on one PG page it is quite performant.

ddorian43 11y ago
I don't think you can limit them: However, the current implementation ignores any supplied array size limits, i.e., the behavior is the same as for arrays of unspecified length.
- gtrubetskoy 11y ago
  By "limit" I mean your code would do it, not Postgres. E.g. your series is 86400 datapoints long (seconds in a day), you would store it as 100 rows of 864-element-long arrays.

Mahn 11y ago

Just for the record, InfluxDB 0.9 seems to actually be production ready now. Though it doesn't look there's an easy way to migrate to it yet from 0.8.

Kirth 11y ago
"Seems to be", sure. Sinks like a tanker when you try to actually use it, though. None of the software libraries have been updated for 0.9 yet.
- Mahn 11y ago
  > Sinks like a tanker when you try to actually use it Can you name some examples of this besides the CPU authentication bug? Honest question, I'm considering adding InfluxDB to our stack so I'm genuinely curious.
  - Kirth 11y ago
    We've had issues with Influx being a massive resource hog. Not properly persisting data to disk. They openly admit that Influx isn't built to gracefully recover from crashes, and that you will lose data. This by itself wouldn't be a problem for us. But when you're inserting data points, the entire damn thing seems to become unresponsive. Admin UI freezing, http endpoint no longer responding to queries, ... I know this product is relatively young and your mileage may vary. They have a long way to go.
simonpantzare 11y ago
Authentication in 0.9 has a high CPU penalty right now: https://news.ycombinator.com/item?id=9810538 https://news.ycombinator.com/item?id=9810538.
sciurus 11y ago
Based on the changes in 0.9.1, there's no way that 0.9 was production ready and I'd be very cautious about using it. https://influxdb.com/blog/2015/07/02/InfluxDB-0_9_1-and-Telegraf-0_1_2-released-with-new-docs.html https://influxdb.com/blog/2015/07/02/InfluxDB-0_9_1-and-Tele...

rdtsc 11y ago

I evaluated InfluxDB for an advanced packet capture and processing application and it couldn't handle things very well. Namely expiry of old data, blocking too much on inserts. So I wrote my own in Python + C extensions. It turned out well. Has been going non-stop for year and a half now.

rw 11y ago
What data structures does it use?
- rdtsc 11y ago
  For the main data and index just a plain sorted list of tuples. Main data file is [ (timestamp,data), ...] and index is [ (timestamp,offset_in_datafile), ... ]. There is a requirement that ntp server must be running and the machine synchronized to it. Then all the files have a prefix that look like <startsec>_<startmicrosec>_<metric>. A binary search is perfomed first on files in order to pick the right files only. Then the index is read into memory, then binary search performed on the index to get the offset range and then the main data file is read. When switching to new file time chunk, a cleanup action is peformed where previous old files are removed.

LukeHoersten 11y ago

It'd be great to see a more detailed guide to using and tuning PGSQL for use as a TS DB.

15155 11y ago
As an addendum, I'd like to be able to do time-based append-only operations in Postgres, a la Datomic. It pains me to UPDATE and DELETE, thereby destroying useful data.
- simonb 11y ago
  Would Timetravel module work for your use-case: http://www.postgresql.org/docs/9.1/static/contrib-spi.html#AEN137462 http://www.postgresql.org/docs/9.1/static/contrib-spi.html#A...

andyl 11y ago

Curious if anyone has experienced problems like this in InfluxDB 0.9 ?? My 0.9 implementation is performing well, but has only small amounts of metrics for testing - not yet rolled out to production.

simonpantzare 11y ago
Authentication in 0.9 has a high CPU penalty right now: https://news.ycombinator.com/item?id=9810538 https://news.ycombinator.com/item?id=9810538.

simonpantzare 11y ago

For those of you reading this that are interested in getting on InfluxDB 0.9. If you intend to use authentication and have a somewhat high request rate, I advise you to wait until the fixes related to #3102 (https://github.com/influxdb/influxdb/issues/3102 https://github.com/influxdb/influxdb/issues/3102) are included. Without that fix your CPU is killed because InfluxDB is bcrypting on every request.

CSDude 11y ago
That explains why my updates on 100ms intervals seemed like hogging resources.

pauldix 11y ago

On the performance problem, my guess is that you wrote in a bunch of columns and had those in where clauses. I hope the documentation made it clear that you'd be range scanning over data and that you probably wouldn't get desirable performance. In 0.8 and before, the preferred way to model your data was to create many separate series names. This method is currently giving many users great performance. As with all databases, how you model your schema has a significant impact on performance. That being said, it might be that Influx wasn't right for your use case and Postgres is just a better thing to go with. Also, we're not supporting anything prior to the 0.9 series of releases. 0.8 is deprecated and we'll be pushing everyone to move over to 0.9 as we put out more point releases and fix bugs and ensure that it works for their use case over the coming months.