Very high RAM usage when using SELECT INTO

Hi,

I’m copying all points from a database into another, using something like this:

select * into sourcedb..:MEASUREMENT FROM /.*/ GROUP BY *

The InfluxDB instance is running on a VM with 32GB RAM.

The sourcedb database in the filesystem takes up 9.2GB

Memory consumption of InfluxDB is currently 92% and still growing, without any other activity going on. I suspect OOM will be the final result.

Is this normal / expected?

Thanks.

Hi,

Influxdb does use a high amount of memory. I’ve had two nodes (200gb each) that have been averaging 90 - 95 % at times and eventually it will fall over.

You could try changing the setting in your influxDB to ‘tsi1’ which is meant to help alleviate memory. It’s under the [data] section of InflxDB config. the default is ‘inmem’ try changing to ‘tsi1’

Caution!! depending what version of Influx you are using there are or were different update processes to allow the tsi part to work. I think if you’re using Influxdb version 1.3 or before there are steps you need to take otherwise it will break. you should read this first if you decide to update.

Another thing is to copy your data over in batches and only write X amount of data points each time. I think the limit in the influxdb config is 10000 points.

Hope that helps

Two other thing to be careful with too:

Don’t do a “select * into” loop (source db = dest db), that will definitely result in OOM, as I noticed the hard way :slight_smile:

Also, subscription loops will give the same result. If you had setup a subscription on the old db to stream to the new db, then backup/restore with the standard tools, that subscription will be configured again in the new db, and influxdb will try to stream new data points of the db into itself.

Thanks for the tips @philb. I do need to switch to TSI on my 1.6 instances.

Thats a good point @voiprodrigo i’d not considered the select * part of the query.

No probs hope it helps fix your issue.

1 Like