I use a c++ library influxdb-cxx which uses curl to insert data into the database. In my c++ program, I will write 4 measurements and each of them has dozens of pieces points to insert per second. I use UDP to send data
sensor_->influxdb_ = influxdb::InfluxDBFactory::Get("udp://localhost:8089?db=udp");
One of insert c++ code looks:
sensor_->influxdb_->write(influxdb::Point("/controller/data")
.addField("angular", sensor_->angular())
.addField("speed_left_rear", sensor_->speedl()
.addField("speed_right_rear", sensor_->speedr()
.addField("voltage", sensor_->voltage()));
It would insert a point to measurement "/controller/data". This point has four fields and has no tag.
When I query the data in the last half-second in the database, a problem occurs. I open a terminal and enter
influx
use udp
create retention policy "1h" on udp duration 1h replication 1 shard duration 1h default
precision rfc3339
SELECT * FROM "udp".."/controller/data" WHERE time >= now() - 500ms
Expected behavior: I can get points for the last half-second, all fields are not null and data in the same row have the same timestamp. Actual behavior: Sometimes filed value in is null. For example:
> SELECT * FROM "udp".."/controller/data" WHERE time >= now() - 500ms
name: /controller/data
time angular speed_left_rear speed_right_rear voltage
---- ------- --------------- ---------------- -------
2021-08-02T08:47:41.17175053Z 99 0 0 23152
2021-08-02T08:47:41.189857794Z -49 0 0 23152
2021-08-02T08:47:41.203743539Z 282 0 0 23152
2021-08-02T08:47:41.219689002Z 299 0 0 23152
2021-08-02T08:47:41.235739755Z 249 0 0 23223
2021-08-02T08:47:41.251701843Z 299 0 0 23311
2021-08-02T08:47:41.267655473Z 133 0 0 23311
2021-08-02T08:47:41.284691461Z 382 0 0 23311
2021-08-02T08:47:41.299605898Z 116 0 0 23240
2021-08-02T08:47:41.315760775Z 23240
2021-08-02T08:47:41.331771862Z 23240
2021-08-02T08:47:41.347798407Z 23240
2021-08-02T08:47:41.363746204Z 23364
2021-08-02T08:47:41.380132952Z 23364
2021-08-02T08:47:41.395660171Z 23364
2021-08-02T08:47:41.411623426Z 23364
2021-08-02T08:47:41.427676221Z 23241
2021-08-02T08:47:41.443633642Z 23241
2021-08-02T08:47:41.459634521Z 23241
2021-08-02T08:47:41.475580859Z 23241
2021-08-02T08:47:41.491627529Z 23400
2021-08-02T08:47:41.507684407Z 23400
2021-08-02T08:47:41.523670412Z 23400
2021-08-02T08:47:41.539542668Z 23400
2021-08-02T08:47:41.556554739Z 23400
2021-08-02T08:47:41.571867004Z 23329
2021-08-02T08:47:41.587669261Z 23329
2021-08-02T08:47:41.603685306Z 23329
2021-08-02T08:47:41.619762893Z 23329
2021-08-02T08:47:41.636273305Z 23293
2021-08-02T08:47:41.651680944Z 23293
2021-08-02T08:47:41.667774571Z 23293
2021-08-02T08:47:41.683901448Z 23293
2021-08-02T08:47:41.699780996Z 23417
2021-08-02T08:47:41.715881623Z 23417
2021-08-02T08:47:41.732012Z 23417
2021-08-02T08:47:41.747715092Z 23417
2021-08-02T08:47:41.763705763Z 23205
2021-08-02T08:47:41.779796182Z 23205
2021-08-02T08:47:41.795692354Z 23205
and
> SELECT * FROM "udp".."/controller/data" WHERE time >= now() - 500ms
name: /controller/data
time angular speed_left_rear speed_right_rear voltage
---- ------- --------------- ---------------- -------
2021-08-02T08:49:30.314298081Z 649 0 0 23293
2021-08-02T08:49:30.330174725Z 316 0 0 23293
2021-08-02T08:49:30.346282365Z 798 0 0 23187
2021-08-02T08:49:30.362351798Z 732 0 0 23187
2021-08-02T08:49:30.378310691Z 282 0 0 23187
2021-08-02T08:49:30.39422Z 549 0 0 23293
2021-08-02T08:49:30.410205143Z 615 0 0 23329
2021-08-02T08:49:30.42628595Z 149 0 0 23329
2021-08-02T08:49:30.442316883Z 282 0 0 23329
2021-08-02T08:49:30.45822561Z 582 0 0 23329
2021-08-02T08:49:30.474172836Z 133 0 0 23364
2021-08-02T08:49:30.490214269Z 233 0 0 23364
2021-08-02T08:49:30.506210203Z 549 0 0 23364
2021-08-02T08:49:30.522200303Z -399 0 0 23364
2021-08-02T08:49:30.538234736Z -366 0 0 23364
2021-08-02T08:49:30.554288127Z 0 23364
2021-08-02T08:49:30.570351727Z 0 23364
2021-08-02T08:49:30.586195413Z 0 23364
2021-08-02T08:49:30.602191055Z 0 23241
2021-08-02T08:49:30.618195155Z 0 23241
2021-08-02T08:49:30.634950578Z 0 23241
2021-08-02T08:49:30.650237772Z 0 23223
2021-08-02T08:49:30.666386828Z 0 23276
2021-08-02T08:49:30.682325013Z 0 23276
2021-08-02T08:49:30.698237823Z 0 23276
2021-08-02T08:49:30.714291797Z 0 23276
2021-08-02T08:49:30.730265564Z 0 23099
2021-08-02T08:49:30.746241957Z 0 23099
2021-08-02T08:49:30.762230307Z 0 23099
2021-08-02T08:49:30.778286615Z 0 23099
2021-08-02T08:49:30.794283132Z 0 22993
2021-08-02T08:49:30.819896051Z 0 23134
2021-08-02T08:49:30.826242917Z 0 23134
2021-08-02T08:49:30.84235989Z 0 23134
2021-08-02T08:49:30.86443611Z 0 23134
2021-08-02T08:49:30.874357007Z 0 23170
2021-08-02T08:49:30.890441315Z 0 23170
2021-08-02T08:49:30.906395249Z 0 23170
2021-08-02T08:49:30.922352392Z 0 23170
2021-08-02T08:49:30.942798927Z 0 23187
2021-08-02T08:49:30.954241427Z 0 23187
2021-08-02T08:49:30.970329818Z 0 23187
2021-08-02T08:49:30.986202379Z 0 23240
2021-08-02T08:49:31.002118397Z 0 23046
2021-08-02T08:49:31.018028873Z 0 23046
2021-08-02T08:49:31.034310053Z 0 23046
As you can see, there are many null values.
The left column has a better chance of being null, and the rightmost column of filed key voltage never has a null value.
So I guess: The filed value in the same row correspond to the same timestamp, but they are not written into the database at the same time, some of they may be written in, but others are not. Is that right?
If there is a method to let different fields in the same raw to write at the same time? So when I query each field in the same row is not null.
Or how to query the most recent point whose all fields are not null? I actually want to query the last point in the measurement:select * from "udp".."/controller/data" order by time desc limit 1, but it sometimes returns a point with some fields having a null value.
And I find that last() doesn't help the problem for me.
Because if I use SELECT last(*) FROM "udp".."/controller" WHERE time > now() - 1s(I use WHERE clause because I think it may let it query faster by specifying a time range), it just merge the result from last(angular), last(speed_left_rear), last(speed_right_rear), last(voltage) into one point. But the field value may not corespond to the same timestamp.
Environment info:
I run all these programs on an embedding device nanopi which is similar to raspberryPi.
Config: my influx.conf
[[udp]]
enabled = true
batch-size = 50
batch-pending = 5
batch-timeout = "1s"
And I tried modifying UDP batch-size to see if it makes any difference:
If I modify batch-size to 1 in influxdb.conf
[[udp]]
enabled = true
batch-size = 1
batch-pending = 5
batch-timeout = "1s"
It would add a lot CPU load, and I can only get point whose time > now -22s
select * from "udp".."/controller/data" where time >= now() - 22s
and it returns the following result:
> SELECT * FROM "udp".."/controller/data" WHERE time >= now() - 21s
name: /controller/data
time angular speed_left_rear speed_right_rear voltage
---- ------- --------------- ---------------- -------
1627894525069581596 599 0 0 23276
1627894525115397757 366 0 0 23116
1627894525147390263 782 0 0 23258
1627894525163375140 216 0 0 23258
1627894525179457142 449 0 0 23258
1627894525211368273 249 0 0 23382
1627894525227548857 532 0 0 23382
1627894525259374239 133 0 0 23382
1627894525275378367 249 0 0 23169
1627894525307387789 665 0 0 23169
1627894525323413499 499 0 0 23169
1627894525355366338 599 0 0 23311
1627894525371388841 382 0 0 23311
1627894525387508467 499 0 0 23311
1627894525403456887 1148 0 0 23223
1627894525420205921 549 0 0 23205
1627894525436119925 149 0 0 23205
1627894525452726045 366 0 0 23205
1627894525468630132 183 0 0 23223
1627894525484748008 -99 0 0 23223
1627894525500814260 249 0 0 23223
1627894525516629098 499 0 0 23223
1627894525533157344 848 0 0 23205
1627894525548644353 366 0 0 23205
1627894525564793437 16 0 0 23205
1627894525627496298 66 0 0 23347
1627894525675488264 83 0 0 23240
1627894525723471189 -33 0 0 23418
1627894525755489652 166 0 0 23418
1627894525772796929 332 0 0 23418
1627894525803355328 23152
It still sometimes returns a null value. So changing the batch size doesn't work for this problem, and it seems that batch-size is a property of one column, it pack a certain amount of column data and then write into the database.
I would not use InfluxDB to work with data from last 500ms. This software is not for solutions like that. InfluxDB has no priority to put all values in defined time. It is not a real time system. If you need something like that I would suggest PLC type device which are real time. But... If you want to use it that way so much I can suggest following tricky solutions:
SELECT last(*) FROM "udp".."/controller/data" WHERE time >= now() - 10s and time<=now()-400ms
Assuming that after 400ms you have values in all fields. 3) Ask for data with safe delay, for example 500ms:
SELECT first(*) FROM "udp".."/controller/data" WHERE time >= now() - 500ms
Query above should give you first value before 500ms from now. Assuming that after little less than 500ms is needed to have all field values written.
SELECT * FROM "udp".."/controller/data" WHERE time >= now() - 5s and "angular">-9999999 and "speed_left_rear">-9999999 and "speed_right_rear">-9999999"> and "voltage">-9999999 limit 1
All above assuming that you cannot read values lower than -9999999. This way you should receive only series that has all values written and is not older than 5s. Values you would get are the newest that have the same timestamp with all values written.
and 2) solution may work or not work in some conditions with assumed delays (it depends on system workload etc.). 3) might be the best solution if you need values all with the same timestamp.
One more way is to make 4 independent queries where you query for last values:
SELECT last(angular) FROM "udp".."/controller/data" SELECT last(speed_left_rear) FROM "udp".."/controller/data" SELECT last(speed_right_rear) FROM "udp".."/controller/data" SELECT last(voltage) FROM "udp".."/controller/data"
Select * from (SELECT last(angular) as "angular" FROM "udp".."/controller/data"),(SELECT last(speed_left_rear) as "speed_left_rear" FROM "udp".."/controller/data"),(SELECT last(speed_right_rear) as "speed_right_rear" FROM "udp".."/controller/data"),(SELECT last(voltage) as "voltage" FROM "udp".."/controller/data")
It will give you the latest values but they might not have the same timestamp.