Points per shard group

1

Hello,
I read the blog post Simplifying InfluxDB: Retention Policy Best Practices. One of the recommendations is “The shard group duration should be set so that each shard group ends up with at least 100,000 points per group—you want more data per shard, but not too much data.”

It would be great if you can help me calculate / query the number of points per shard group.

Prior to that, I already tried to play around with the _internal database. However, it is unclear what the fields, such as writePointsOk, mean.

Best,
Giang

2

Hi Giang ,
This link confirms WritePointsOk = The number of points written successfully.
Remark : after a stop/start of your database the counter is reset to 0 …

http://docs.influxdata.com/platform/monitoring/tools/measurements-internal/#writepointsok

Now let’s lookup the number of datapoints per shard in my telegraf database …

**> show shards**
name: _internal
id database  retention_policy shard_group start_time           end_time             expiry_time          owners
-- --------  ---------------- ----------- ----------           --------             -----------          ------
4  _internal monitor          4           2018-11-07T00:00:00Z 2018-11-08T00:00:00Z 2018-11-15T00:00:00Z
...
12 _internal monitor          12          2018-11-14T00:00:00Z 2018-11-15T00:00:00Z 2018-11-22T00:00:00Z

name: telegraf
id database retention_policy shard_group start_time           end_time             expiry_time          owners
-- -------- ---------------- ----------- ----------           --------             -----------          ------
3  telegraf autogen          3           2018-11-05T00:00:00Z 2018-11-12T00:00:00Z 2018-11-12T00:00:00Z
10 telegraf autogen          10          2018-11-12T00:00:00Z 2018-11-19T00:00:00Z 2018-11-19T00:00:00Z

There are 2 shards in my telegraf database with id 3 and id 10
To get the information about the shards I connect to the _internal database

> use _internal
Using database _internal

I select the last datapoints , depending on your situation you may have to change the timeframe
for example now() - 30m

select “database”,diskBytes,fieldsCreate, id,writePointsOk from “shard”
where “database”=‘telegraf’ and time > now() -10s

name: shard
time                database diskBytes fieldsCreate id writePointsOk
----                -------- --------- ------------ -- -------------
1542205570000000000 telegraf 8352489   0            10 36481
1542205570000000000 telegraf 8935075   0            3  0

After a few seconds I execute the same query and you see that the number of datapoints has increased.

select “database”,diskBytes,fieldsCreate, id,writePointsOk from “shard”
where “database”=‘telegraf’ and time > now() -10s;

name: shard
time                database diskBytes fieldsCreate id writePointsOk
----                -------- --------- ------------ -- -------------
1542205800000000000 telegraf 8423839   0            10 36987
1542205800000000000 telegraf 8935075   0            3  0

After a stop/start of your database the counter is reset to 0 …

select “database”,diskBytes,fieldsCreate, id,writePointsOk from “shard”
where “database”=‘telegraf’ and time > now() -10s;

name: shard
time                database diskBytes fieldsCreate id writePointsOk
----                -------- --------- ------------ -- -------------
1542206910000000000 telegraf 8722334   0            10 88
1542206910000000000 telegraf 8935075   0            3  0
4

This one is better :

> select id,max(writePointsOk) from "shard" 
where  "database"='telegraf' group by id ;

name: shard
tags: id=10
time                 id max
----                 -- ---
2018-11-14T09:37:30Z 10 457104

name: shard
tags: id=3
time                 id max
----                 -- ---
2018-11-12T00:00:10Z 3  1011953
1 Like