OPTIMIZING THE TICK STACK

Optimizing the TICK
Stack
Dave Patton
February 14, 2018

Dave Patton
Director of Sales
Engineering, InfluxData
Optimizing the TICK Stack
In this session you will learn how to tune your queries for
performance plus strategies for effective schema design.

Agenda
• InfluxDB Data Model
• Tradeoff of storing data as a tag vs field
• Schema design best practice

Things to
remember
● Tags are Indexed
● Fields are not
● All points are indexed by
time

© 2018 InfluxData. All rights reserved.7
DON'T ENCODE DATA INTO THE MEASUREMENT NAME
• Measurement names like:
• Encode that information as tags:
cpu.server-5.us-west value=2 1444234982000000000
cpu.server-6.us-west value=4 1444234982000000000
mem-free.server-6.us-west value=2500 1444234982000000000
cpu,host=server-5,region=us-west value=2 1444234982000000000
cpu,host=server-6,region=us-west value=4 1444234982000000000
mem-free,host=server-6,region=us-west value=2500 1444234982000000

What if my plugin sends data like that to InfluxDB?
Write something that sits between your plugin and InfluxDB to sanitize the data OR
use one of our write plugins:
Example - Telegraf’s Graphite input plugin: Takes input like…
…and parses it with the following template…
…resulting in the following points in line protocol hitting the database:
sensu.metric.net.server0.eth0.rx_packets 461295119435 1444234982
sensu.metric.net.server0.eth0.tx_bytes 1093086493388480 1444234982
sensu.metric.net.server0.eth0.rx_bytes 1015633926034834 1444234982
["sensu.metric.* ..measurement.host.interface.field"]
net,host=server0,interface=eth0 rx_packets=461295119435 1444234982
net,host=server0,interface=eth0 tx_bytes=1093086493388480 1444234982
net,host=server0,interface=eth0 rx_bytes=1015633926034834 1444234982

DON’T OVERLOAD TAGS
• BAD
• GOOD: Separate out into different tags:
cpu,server=localhost.us-west value=2 1444234982000000000
cpu,server=localhost.us-east value=3 1444234982000000000
cpu,host=localhost,region=us-west value=2 1444234982000000000
cpu,host=localhost,region=us-east value=3 1444234982000000000

DON’T USE THE SAME NAME FOR A FIELD AND A TAG
• BAD: This significantly complicates queries.
• GOOD: Differentiate the names somehow:
login,user=admin user=2342,success=1 1444234982000
SELECT user::field, user::tag FROM login
login,user_type=admin user_id=2342,success=1 1444234982000

DON'T USE TOO FEW TAGS
• BAD
• Problems you might run into:
Fields are not indexed, so queries with field conditions have to
scan every point.
GROUP BY <field> is not valid.
cpu,region=us-west host="server1",value=4,temp=2 1444234982000
cpu,region=us-west host="server2",value=1,other=14 1444234982000

DON'T WRITE DATA WITH THE WRONG PRECISION
• Bad things can happen
Writing data using second precision when you need millisecond precision
– Timestamps can collide and you'll lose data.
– The timestamp may be interpreted as 1970 instead of today.
• Not Optimal for performance
Writing data using nanosecond precision when you need only second precision
– More data over the wire.
– Decreased write throughput.
– Larger size on disk.
– The timestamp may be interpreted far in the future instead of today.
• Even if your data points are about 1 sec apart, make sure they are at least 1
sec apart to avoid collisions after rounding.

DON'T CREATE TOO MANY LOGICAL CONTAINERS
Or rather, don’t write to too many databases:
• Dozens of databases should be fine
• hundreds might be okay
• thousands probably aren't without careful design
Too many databases leads to more open files, more query
iterators in RAM, and more shards expiring. Expiring shards have
a non-trivial RAM and CPU cost to clean up the indices.

The Last Writes Wins!
InfluxDB only stores one value for a given series + field

You have 10,000 sensors
• You have 10,000 sensors
• They measure air quality at different points
• The sensors emit data to InfluxDB every 10 seconds

Sensor emissions:
•
zip_code Zipcode of the sensor location
city Name of the city
lat Latitude of the sensor
lng Longitude of the sensor
device_id UUID of the device
smog_level Smog level measurement
co2_ppm CO2 parts per million measurement
lead Atmospheric lead level measurement
so2_level Sulfur Dioxide level measurement

Exercise
Why would it be a bad idea to make lat or lng a tag instead of a
field?

Solution
• Why would it be a bad idea to make lat or lng a tag instead of a
field?
– Numeric Property: We probably care about doing math on lat and lng.
That can only work if they are fields.

Exercise
Why would it be a good idea to make lat or lng a tag instead of a
field?

Solution
• Why would it be a good idea to make lat or lng a tag instead of a
field?
– We probably care about filtering or grouping by lat and lng. Filters are
faster with tags, and only tags are valid for grouping.
– If our devices don't move, lat and lng are dependent tags on device_id.
Storing them as tags won't increase series cardinality.
• Keep in mind that you can’t do any of the numeric computations

The following queries are important
• SELECT median(lead) FROM pollutants
WHERE time > now() - 1d GROUP BY city
SELECT mean(co2_ppm) FROM pollutants
WHERE time > now() - 1d AND city='sf' GROUP BY device_id
SELECT max(smog_level) FROM pollutants
WHERE time > now() - 1d AND city='nyc' GROUP BY zipcode
SELECT min(so2_level) FROM pollutants
WHERE time > now() - 1d AND city='nyc' GROUP BY zipcode

Question
How can we organize our data to support the queries that we want?

Schema 1 for Pollutants
• measurement: pollutants
tags: city device_id zipcode
fields: lat lng smog_level co2_ppm lead so2_level
Examples in Line Protocol
pollutants,
city=richmond,device_id=12,zipcode=23221
lat=37.5333,lng=77.4667,smog_level=2.4,co2_ppm=404i,lead=2.3,so2_level=3i
142309324834700
pollutants,
city=bozeman,device_id=37,zipcode=59715
lat=45.6778,lng=111.0472,smog_level=0.9,co2_ppm=398i,lead=1.3,so2_level=1i
142309324834700

Schema 2 for Pollutants
• measurement: pollutants
tags: lat lng city device_id zipcode
fields: smog_level co2_ppm lead so2_level
Examples in Line Protocol
pollutants,
city=richmond,device_id=12,zipcode=23221,lat=37.5333,lng=77.4667
smog_level=2.4,co2_ppm=404i,lead=2.3,so2_level=3i
142309324834700
pollutants,
city=bozeman,device_id=37,zipcode=59715,lat=45.6778,lng=111.0472
smog_level=0.9,co2_ppm=398i,lead=1.3,so2_level=1i
142309324834700

Telegraf
• Don’t use a homegrown collector
– Maintenance will become troublesome
– Telegraf has near optimal schemas for InfluxDB
– Telegraf already handles: scheduling, retries, formatting, and batching - no
need to add these features to your homegrown collector
• If you required a custom collector, you can run that in Telegraf via the exec plugin
and get those benefits
• Don’t have 1,000s of independent Telegraf collectors writing to InfluxDB
– Use a queuing system
– Or layering
Plus, there are over 140+ plugins available!

Chronograf & Kapacitor
• Chronograf
– not much can happen here to make your TICK stack inefficient
• Kapacitor
– There are many things to consider - please join us in our training on
Advanced Kapacitor

OPTIMIZING THE TICK STACK

Recommended

More Related Content

What's hot (20)

Similar to OPTIMIZING THE TICK STACK (20)

More from InfluxData (20)

Recently uploaded (20)

OPTIMIZING THE TICK STACK