QA (public) » openQA Project (public) » openQA Infrastructure (public)

isn't this time series database meant to store data with less granularity over time?

By the way, creating a new default retention policy as suggested by the documentation would (to my understanding) not affect any existing data (which is still kept under the current default retention policy which is "autogen"¹). We could of course just use e.g. DELETE WHERE time < '2020-11-08' to delete everything older than a year (from now) but then we'd still needed to repeat that process until all data from the "autogen" retention period has been deleted.

Alternatively we could likely also alter the current "autogen" retention policy like this (as documented):

ALTER RETENTION POLICY "autogen" ON "telegraf" DURATION 720d

I suppose then we wouldn't need to worry about removing existing data.

¹

> SHOW RETENTION POLICIES ON "telegraf"
name    duration shardGroupDuration replicaN default
----    -------- ------------------ -------- -------
autogen 0s       168h0m0s           1        true

ok, so I understand it's not as simple as defining a default downsampling policy that affects all data. That's what I originally thought influxdb would work like. Reading your comments and pages like https://www.influxdata.com/blog/downsampling-influxdb-v2-0/ and https://www.influxdata.com/influxdb-templates/downsampling-influxdb/ and https://hackernoon.com/influxdb-continuous-downsampling-optimize-your-tsdb-today-q3lm2fhg and https://influxdb.narkive.com/AQM2b071/automatically-downsample-all-data#post7 I think it might be better if we identify which measurements have the biggest impact and provide explicit specific retention+downsampling per measurement. Can you identify according to Pareto principle which measurements are the biggest offenders?

It is at least possible to determine the used disk space by measurement, e.g.:

martchus@openqa-monitor:~> sudo influx_inspect report-disk -detailed /var/lib/influxdb/data > influx-disk-usage.json
martchus@openqa-monitor:~> jq -r '["measurement","size_gb"], (.Measurement | sort_by(.size) | reverse[] | [select(.db == "telegraf")] | map(.measurement,.size / 1024 / 1024 / 1024)) | @tsv' influx-disk-usage.json
measurement     size_gb
apache_log      59.99271512031555
cpu     46.17899715155363
procstat        20.055298353545368
disk    7.618690362200141
ping    5.069371770136058
diskio  3.353263510391116
mem     2.891846708022058
net     2.7090410608798265
system  0.9844627007842064
apache  0.5020766351372004
ntpq    0.48354801069945097
kernel  0.4041093336418271
procstat_lookup 0.3167126877233386
chrony  0.30627269111573696
processes       0.209840452298522
postgresql      0.10544836893677711
http_response   0.06108146067708731
swap    0.059172035194933414
openqa_jobs_by_group    0.04418153967708349
openqa_jobs_by_worker   0.039369286969304085
openqa_jobs_by_arch     0.028374306857585907
openqa_minion_jobs      0.024388955906033516
openqa_minion_workers   0.015562820248305798
openqa_jobs     0.014018760062754154
systemd_failed  0.005923120304942131
lab_temperature 0.00429917685687542
httpjson_worker_minion_stats    0.0014895927160978317
my_log  0.00015092454850673676
systemd_failed_test     8.108559995889664e-05
telegraf        2.9811635613441467e-06
openqa-websockets       2.8098002076148987e-06
openqa-scheduler        2.5257468223571777e-06
cron    2.4391338229179382e-06
smb     2.387911081314087e-06
rsyncd  2.343207597732544e-06
openqa-livehandler      2.3096799850463867e-06
salt-minion     2.255663275718689e-06
salt-master     2.255663275718689e-06
vsftpd  2.2351741790771484e-06
openqa-webui    1.8579885363578796e-06
openqa-gru      1.8207356333732605e-06
apache2 1.6884878277778625e-06
postfix 1.6335397958755493e-06
sshd    1.5506520867347717e-06

So I could try it for the fields of the top measurements. Note that some of the field types are string so computing an average like in the previously mentioned example isn't possible. It wouldn't also make much sense for some fields with number types, e.g. http_version¹. I suppose I'll just use last in such cases.

¹

> show field keys from apache_log
name: apache_log
fieldKey         fieldType
--------         ---------
agent            string
auth             string
client_ip        string
http_version     float
ident            string
referrer         string
request          string
resp_bytes       integer
response_time_us integer

The default retention policy can only be set on database level. At least I couldn't find a way to set the default retention policy per measurement. So I suppose I cannot follow the documentation examples when only covering a certain measurement like apache_log as autogen should still be the default retention policy in general. So I need to find a way to move (and not just copy) raw data from one retention policy to another via a continuous query. It looks like this isn't possible, though. All commands/examples I've found only copy the data (and rely on the retention period for the cleanup).

The actual down sampling would look like this by the way:

CREATE CONTINUOUS QUERY "cq_downsample_apache_log" ON "telegraf" BEGIN
    SELECT
        last("agent") AS "agent",
        last("auth") AS "auth",
        last("client_ip") AS "client_ip",
        last("http_version") AS "http_version",
        last("ident") AS "ident",
        last("referrer") AS "referrer",
        last("request") AS "request",
        mean("resp_bytes") AS "resp_bytes",
        mean("response_time_us") AS "response_time_us"
    INTO "two_years"."downsampled_apache_log"
    FROM "half_year"."apache_log"
    GROUP BY time(1h)
END

I've tested the SELECT (without INTO and some time constraint like WHERE time >= '2021-11-22T00:00:00Z') and it worked. The SELECT (with INTO) could also be run manually to cover existing data although one might need to split the query into different time boundaries (see https://docs.influxdata.com/influxdb/v1.8/query_language/explore-data/#rename-a-database).

I've now changed the default retention period to delete everything older than 2 years. I also immediately deleted everything older than 2 years (via delete where time < '2019-11-22'). /var/lib/influxdb shrunk from 185 GiB to 152 GiB.

I've also deleted all data associated with the apache_log measurement older than 1 year (via delete from "apache_log" where time < '2020-11-22') which shrunk it to 150 GiB.

So in the file system the effect isn't significantly visisble. However, report-disk shows a more the savings:

measurement     size_gb
apache_log      32.94414313044399
cpu     28.99280675780028
procstat        18.21759712137282
disk    3.7574257869273424
ping    3.290378055535257
net     2.468618060462177
diskio  1.9918824285268784
mem     1.8506945176050067
system  0.5679652551189065
apache  0.45197963155806065
ntpq    0.4201518790796399
chrony  0.3121297275647521
procstat_lookup 0.30364393070340157
kernel  0.2221601502969861
processes       0.13334486354142427
postgresql      0.10715502314269543
swap    0.04125824477523565
http_response   0.03266702499240637
openqa_jobs_by_group    0.02366778813302517
openqa_minion_jobs      0.022559572011232376
openqa_jobs_by_worker   0.020542572252452374
openqa_jobs_by_arch     0.0148719921708107
openqa_minion_workers   0.014549209736287594
openqa_jobs     0.006770698353648186
systemd_failed  0.005970432423055172
lab_temperature 0.0044286223128438
httpjson_worker_minion_stats    0.0014895927160978317
systemd_failed_test     8.223019540309906e-05

Not sure how to tell InfluxDB to free up space in the file system. Maybe the effect would have been more visible with a smaller shard duration.

Note that the size reported by report-disk decreased from 151.48 GiB to 96.1605 GiB.

Ok. To fulfill AC1 what you could do is compress /var/lib/influxdb/dumps/postgresql or move it elsewhere (or delete). I am trying my luck with compression right now. Called nice -n 1 xz -3 --threads=0 postgresql in a root screen session on openqa-monitor.qa . As that file is text with a lot repeated I guess that should be quite efficient.

compression finished. file is down to 381 MiB and usage in /var/lib/influxdb is 101G so AC1 fulfilled :)

I recommend you either follow-up with downsampling policies for specific measurements or record the individual ideas for improvements in separate tickets if you want to get rid of the work for now :)