What InfluxDB schema is suitable for these measurements?

Question

I have data about the status of my server collected over the years: temperatures, fan speeds, cpu load, SMART data. They are stored in a SQLite database under various tables, each one specific for each type of data.

I'm switching to InfluxDB for easier graphing (Grafana) and future expansion: the data will include values from another server and also UPS data (voltages, battery, ...).

I read the guidelines about schemas in InfluxDB but still I'm confused because I have no experience on the topic. I found another question about a schema recommendation but I cannot apply that one to my case.

How should I approach the problem and how to design an appropriate schema for the time series? what should I put in tags and what in fields? should I use a single "measurement" series or should I create multiple ones?

These are the data I am starting with:

CREATE TABLE "case_readings"(date, sensor_id INTEGER, sensor_name TEXT, Current_Reading)

CREATE TABLE cpu_load(date, load1 REAL, load2 REAL, load3 REAL)

CREATE TABLE smart_readings(date, disk_serial TEXT, disk_long_name TEXT, smart_id INTEGER, value)

Examples of actual data:

case_readings:
"1478897100"    "4"     "01-Inlet Ambient"  "20.0"
"1478897100"    "25"    "Power Supply 1"    "0x0"

cpu_load:
"1376003998"    "0.4"   "0.37"  "0.36"

smart_readings:
"1446075624"    "50026B732C022B93" "KINGSTON SV300S37A60G"  "194"   "26 (Min/Max 16/76)"
"1446075624"    "50026B732C022B93" "KINGSTON SV300S37A60G"  "195"   "0/174553172"
"1446075624"    "50026B732C022B93" "KINGSTON SV300S37A60G"  "196"   "0"
"1446075624"    "50026B732C022B93" "KINGSTON SV300S37A60G"  "230"   "100"

This is my idea for a InfluxDB schema. I use uppercase to indicate the actual value and spaces only when a string actually contains spaces:

case_readings,server=SERVER_NAME,sensor_id=SENSOR_ID "sensor name"=CURRENT_READING DATE

cpu_readings,server=SERVER_NAME load1=LOAD1 load2=LOAD2 load3=LOAD3 DATE

smart_readings,server=SERVER_NAME,disk=SERIAL,disk="DISK LONG NAME" smart_id=VALUE DATE

FarO FarO · Accepted Answer · 2017-01-12T23:52:50

I found the schema used by an official Telegraph plugin for the same IPMI readings I have:

ipmi_sensor,server=10.20.2.203,unit=degrees_c,name=ambient_temp \
 status=1i,value=20 1458488465012559455

I will convert my old data into that format, I have all the required fields stored in my old SQLite DB. I will modify the plugin to save the name of the server instead of the IP, that here at home is more volatile than the name itself. I will also probably reduce the precision of the timestamp to simple milliseconds or seconds.

Using that one as example, I understand that the one I proposed for CPU readings could be improved:

cpu,server=SERVER_NAME,name=load1 value=LOAD1 DATE
cpu,server=SERVER_NAME,name=load2 value=LOAD2 DATE
cpu,server=SERVER_NAME,name=load3 value=LOAD3 DATE

However I am still considering the one I proposed, without indexing of the single values:

cpu,server=SERVER_NAME load1=LOAD1 load2=LOAD2 load3=LOAD3 DATE

For SMART data my proposal was also not optimal so I will use:

smart_readings,server=SERVER_NAME,serial=SERIAL,name=DISK_LONG_NAME",\
 smart_id=SMART_ID,smart_description=SMART_DESCRIPTION \
 value=VALUE value_raw=VALUE_RAW DATE

What InfluxDB schema is suitable for these measurements?

1 Answers