Ingest data using Telegraf

To create a Telegraf configuration that exports data to a hypertable in your service:

1. Setup your service connection string

   This variable hold the connection information for the target Timescale Cloud service.

   In Terminal on the source machine, set the following:

   export TARGET=postgres://tsdbadmin:<PASSWORD>@<HOST>:<PORT>/tsdb?sslmode=require

   Copy

   You find the connection information for your Timescale Cloud service in the configuration file you downloaded when you created the service.

2. Generate a Telegraf configuration file

   In Terminal, run the following:

   telegraf --input-filter=cpu --output-filter=postgresql config > telegraf.conf

   Copy

   telegraf.conf configures a CPU input plugin that samples various metrics about CPU usage, and the PostgreSQL output plugin. telegraf.conf also includes all available input, output, processor, and aggregator plugins. These are commented out by default.

3. Test the configuration

   telegraf --config telegraf.conf --test

   Copy

   You see an output similar to the following:

   2022-11-28T12:53:44Z I! Starting Telegraf 1.24.3
   2022-11-28T12:53:44Z I! Available plugins: 208 inputs, 9 aggregators, 26 processors, 20 parsers, 57 outputs
   2022-11-28T12:53:44Z I! Loaded inputs: cpu
   2022-11-28T12:53:44Z I! Loaded aggregators:
   2022-11-28T12:53:44Z I! Loaded processors:
   2022-11-28T12:53:44Z W! Outputs are not used in testing mode!
   2022-11-28T12:53:44Z I! Tags enabled: host=localhost
   > cpu,cpu=cpu0,host=localhost usage_guest=0,usage_guest_nice=0,usage_idle=90.00000000087311,usage_iowait=0,usage_irq=0,usage_nice=0,usage_softirq=0,usage_steal=0,usage_system=6.000000000040018,usage_user=3.999999999996362 1669640025000000000
   > cpu,cpu=cpu1,host=localhost usage_guest=0,usage_guest_nice=0,usage_idle=92.15686274495818,usage_iowait=0,usage_irq=0,usage_nice=0,usage_softirq=0,usage_steal=0,usage_system=5.882352941192206,usage_user=1.9607843136712912 1669640025000000000
   > cpu,cpu=cpu2,host=localhost usage_guest=0,usage_guest_nice=0,usage_idle=91.99999999982538,usage_iowait=0,usage_irq=0,usage_nice=0,usage_softirq=0,usage_steal=0,usage_system=3.999999999996362,usage_user=3.999999999996362 1669640025000000000

   Copy

4. Configure the PostgreSQL output plugin

   1. In telegraf.conf, in the [[outputs.postgresql]] section, set connection to the value of $TARGET.

      connection = "<VALUE OF $TARGET>"

      Copy

   2. Use hypertables when Telegraf creates a new table:

      In the section that begins with the comment ## Templated statements to execute when creating a new table, add the following template:

      ## Templated statements to execute when creating a new table.
      # create_templates = [
      #   '''CREATE TABLE {{ .table }} ({{ .columns }})''',
      # ]
      #  table_template=`CREATE TABLE IF NOT EXISTS {TABLE}({COLUMNS}); SELECT create_hypertable({TABLELITERAL},by_range('time', INTERVAL '1 week'),if_not_exists := true);`

      Copy

      The by_range dimension builder was added to TimescaleDB 2.13.

1. Collect system metrics using Telegraf

   Run the following command for a 30 seconds:

   telegraf --config telegraf.conf

   Copy

   Telegraf uses loaded inputs cpu and outputs postgresql along with global tags, the intervals when the agent collects data from the inputs, and flushes to the outputs.

2. View the metrics

   1. Connect to your Timescale Cloud service:

      psql $TARGET

      Copy

   2. View the metrics collected in the cpu table in tsdb:

      SELECT*FROM cpu;

      Copy

      You see something like:

      time         |    cpu    |               host               | usage_guest | usage_guest_nice |    usage_idle     | usage_iowait | usage_irq | usage_nice | usage_softirq | usage_steal |    usage_system     |     usage_user
      ---------------------+-----------+----------------------------------+-------------+------------------+-------------------+--------------+-----------+------------+---------------+-------------+---------------------+---------------------
      2022-12-05 12:25:20 | cpu0      | hostname |           0 |                0 | 83.08605341237833 |            0 |         0 |          0 |             0 |           0 |   6.824925815961274 |  10.089020771444481
      2022-12-05 12:25:20 | cpu1      | hostname |           0 |                0 | 84.27299703278959 |            0 |         0 |          0 |             0 |           0 |   5.934718100814769 |   9.792284866395647
      2022-12-05 12:25:20 | cpu2      | hostname |           0 |                0 | 87.53709198848934 |            0 |         0 |          0 |             0 |           0 |   4.747774480755411 |   7.715133531241037
      2022-12-05 12:25:20 | cpu3      | hostname|           0 |                0 | 86.68639053296472 |            0 |         0 |          0 |             0 |           0 |    4.43786982253345 |   8.875739645039992
      2022-12-05 12:25:20 | cpu4      | hostname |           0 |                0 | 96.15384615371369 |            0 |         0 |          0 |             0 |           0 |  1.1834319526667423 |  2.6627218934917614

      Copy

      To view the average usage per CPU core, use SELECT cpu, avg(usage_user) FROM cpu GROUP BY cpu;.