TL;DR InfluxDB Tech Tips - Limit Series in Database & Continuous Query Configuration

Navigate to:

In this weekly post we recap the most interesting InfluxDB and TICK-stack related issues, workarounds, how to limit series in a database, continuous query configuration, and the FILL() function within the continuous query results and Q&A from GitHub, IRC and the InfluxDB Google Group that you might have missed in the last week or so.

Limit the number of series per database

Q: Is there a way to limit the number of series written to InfluxDB?

A: Yes. The max-series-per-database configuration setting allows you to limit the number of series written per database. By default, max-series-per-database is set to 1,000,000, and you can find it in the [data] section of the configuration file.

If a point causes the number of series in a database to exceed max-series-per-database InfluxDB will not write the point and it returns a 500 with the following error:

{"error":"max series per database exceeded: <series>"}

Configure a Continuous Query's schedule

Q: My CQ runs every hour at the start of every hour. Is there a way to make it run every hour but thirty minutes into the hour? So, instead of running at 00:00 and then 01:00 the CQ runs at 00:30, then at 01:30, then at 2:30, and so on.

A: The CQ syntax supports using an offset interval to alter the default execution time of the CQ. For example, the following CQ executes 30 minutes (30m) after the default execution time:

CREATE CONTINUOUS QUERY "thirty_is_the_new_00" [...] GROUP BY time(1h,30m)

Note that the offset interval will also shift forward the time range covered by each query. At 00:30 the CQ will have the time range between 23:30 and 00:30 instead of the default 23:00 and 00:00.

Fill() Continuous Query results

Q: I included fill(100) in my CQ to fill any data-less intervals with a value but it doesn’t seem to be working; if an interval has no data it continues to be missing from the CQ results. I saw no errors when I created the CQ and the CQ seems to be running when I check out the logs. Is this the expected behavior?

My CQ:

CREATE CONTINUOUS QUERY "fruit_rollup" ON "mydb" BEGIN
SELECT mean("usage_idle") INTO "average_idle" FROM "cpu" GROUP BY time(30m),cpu fill(100)
END

A: This is the expected behavior for your CQ. The basic syntax simply ignores fill() if it’s included in the CQ query.

CQs with the advanced syntax (that is, CQs that include the RESAMPLE clause) do support fill(). Note that at least at least one data point must fall within RESAMPLE’s FOR interval for fill() to operate. If no data fall within the FOR interval the CQ writes no points to the destination measurement.

For example, the following CQ runs a single query every half hour. When it runs, the query covers the time range between now() and two hours prior to now(). If  the cpu measurement has at least one data point within that time range, the CQ will write data to the average_idle measurement, filling any data-less 30m intervals with 100. If, however, the cpu measurement has no data within that two-hour time range, the CQ writes no results to the average_idle measurement.

CREATE CONTINUOUS QUERY "fruit_rollup" ON "mydb" RESAMPLE FOR 2h BEGIN
SELECT mean("usage_idle") INTO "average_idle" FROM "cpu" GROUP BY time(30m),cpu fill(100)
END

For more InfluxDB tips, see our Frequently Asked Questions page and feel free to post your questions in the InfluxDB users group!

What's next?

  • Download and get started with InfluxDB!
  • Schedule a FREE 20 minute consultation with a Solutions Architect to review your InfluxDB project.
  • Attend one of our FREE virtual training seminars.
  • Got a question and need an immediate answer from the InfluxData Support team? Support subscriptions with unlimited incidents start at just $399 a month. Check out all the support options here.