Querying Database with CollectD, InfluxDB & Grafana

In an earlier post we covered basics of setting up CollectD along with InfluxDB and Grafana to monitor system metrics. Real world though has more complex use cases and we are going to tackle one today. In the process we are going to touch on configuring custom plugin, custom types in CollectD DB and then pushing this data to InfluxDB for Grafana to consume. It is highly recommended that you digest earlier post and keep setup ready to play along but brave souls can continue at their own risk

Use case:

Let’s pick up a sample hypothetical application which manages the restaurant orders. Each order is entered with a unique order number and total order amount amount in a DB table. The requirement is that we need to plot total orders placed per minute. So we should eventually see a trend of order amount over period of a time.

Setup

I am going to use one of collected agent boxes from setup outlined in previous blog post. Let’s first install MySQL there (Available as MariaDB package in CentOS 7)
sudo yum install mariadb-server

For connecting to MySQL you can use usual command. Password is going to be blank since this is a fresh installation, so just hit enter when prompted for password:

sudo mysql -u root -p
Let’s create a Database and create a table to store the data we need:
create database hotel_app;
use hotel_app;
create table orders (order_id bigint auto_increment primary key, order_amount bigint, time TIMESTAMP);

So our table stores order amount and time when it was processed and maps it to a order ID. Now let’s setup a simple cron which will keep adding random  entries to this table which we can consume later.

* * * * * sudo mysql -u root < /home/vagrant/test.sql
I added following SQL line in test.sql 5 times so that I get more random entries, you can add as per your taste:
INSERT into hotel_app.orders set order_amount = FLOOR(RAND() * 4001) + 100;

Configuration: CollectD

We have now setup our source of data so let’s get on to querying it and passing it via CollectD. We want to query for order amounts for last one minute and pass them on, so the SQL query looks like:

select order_id, order_amount from orders  where time >= DATE_SUB(NOW(), INTERVAL 60 SECOND);

Before we configure the database query in CollectD plugin, do verify that logfile plugin with following configuration is in place. This plugin will enable us to get log data in case we run into any errors. This config will go in /opt/collectd/etc/collectd.conf

LoadPlugin logfile
 
<Plugin "logfile">
  LogLevel "notice"
  File "/var/log/collectd.log"
  Timestamp true
</Plugin>

In order for CollectD to connect to a database and query we need dbi plugin – which is a short form for Database Interface. Database interface allows us to connect to database in a neutral manner and gets the data. Let’s quickly look at config and run through details:

<Plugin dbi>
    <Query "amount_per_order">
      Statement "select order_id, order_amount from orders  where time >= DATE_SUB(NOW(), INTERVAL 60 SECOND);"
      # Use with MySQL 5.0.0 or later
      MinVersion 50000
      <Result>
        Type "AmountTrend"
        InstancePrefix "amount_per_order"
        InstancesFrom "order_id"
        ValuesFrom "order_amount"
      </Result>
    </Query>
    <Database "hotel_app">
      Driver "mysql"
      DriverOption "host" "localhost"
      DriverOption "username" "root"
      DriverOption "password" ""
      DriverOption "dbname" "hotel_app"
      SelectDB "hotel_app"
      Query "amount_per_order"
    </Database>
  </Plugin>
 
<LoadPlugin dbi>
  Interval 60
</LoadPlugin>
So some important points from above config:
  • The query block configures the SQL query and results. We could have configured multiple result blocks from same query – but for now let’s keep it simple
  • The query block’s name must match “Query” config in Database block.
  • The result block reads the results and creates data instances based on column configured in “InstancesFrom” – and must be only one per result set. There can be multiple “ValuesFrom”  which are associated with a given data instance. So let’s say one order ID can have other details/values like order total, order details etc.
  • Finally a small quirk – the Query block must be before Database block – as the data is interpreted from top to bottom.

In result block above we have mentioned “Type” of result as “AmountTrend” – which is a type that CollectD must know the schema/definition for.  It by default supports 4 standard (ABSOLUTE, COUNTER, DERIVE, GUAGE know more about them here) types and some more custom types defined in types.db. For our use case we are going to define a new type and add following line to /opt/collectd/share/collectd/types.db:

AmountTrend     value:ABSOLUTE:0:U

All this is saying is that we have one value of type ABSOLUTE and the lower bound being 0 and upper bound is unknown/infinite. If we expect some limits on data range – we can configure those for example 0:500

CollectD : Building Plugin

Now let’s restart the CollectD service and see how things go. Oh crap! We have some errors:

{From sudo service collectd status}
Mar 29 05:27:30 agent1.hs.com collectd[12935]: plugin_load: Could not find plugin "dbi" in /opt/collectd/lib...ectd

{From Collectd Log file}  
[2016-03-29 05:28:55] plugin_load: Could not find plugin "dbi" in /opt/collectd/lib/collectd

The way collectD works is all plugins we configure in conf file use underlying “SO libraries” to make the calls. If you navigate to directory /opt/collectd/lib/collectd you will notice quite a few of those files. What we are missing is a dbi.so – which will help make the underlying calls. The best way is to compile the plugin from source for your target platform. I used this StackOverflow conversation to compile the dbi.so – follow instructions there. Except for don’t checkout the version mentioned of source code from git, simply checkout master.

After installing the SO to intended location we also need to install libdbi libraries – this library is used for mysql specific calls.
sudo yum install -y libdbi-dbd-mysql

Data & Graphs

After this if you restart collectd service and go to InfluxDB UI, choose collectd as DB and query for data, you should see incoming data:

Querying data in InfluxDB

Data coming to InfluxDB is in “dbi_” namespace

Finally if we can form the query in Grafana and see results. I have pasted the query from Grafana editor below for reference:

SELECT sum("value") FROM "dbi_" WHERE $timeFilter GROUP BY time(1m) fill(none)
Grafana UI with data

Final graph plotted in Grafana

The restaurant owner is now happy that he can track total orders placed per minute 😉

Conclusion

 We used database plugin to query and report results in this post but this logic can be extended to a variety of plugins and the kind of things that can be monitored is literally endless. Would love to hear your CollectD stories and thoughts.

Vishal Biyani

Vishal Biyani has worked across the whole spectrum of SDLC from developing code to deploying code and supporting customer. Vishal's roles have spanned from being a consultant to Fortune 500 companies to hands on platform building for Internet scale companies. Vishal is a DevOps practitioner, likes to work in Agile environments with a focus on Test Driven Development. Vishal's interests span continuous delivery, Kubernetes, containers and security. Before founding InfraCloud he worked in companies like HCL and AudienceScience building multiple Cloud and DevOps solutions.

20 Responses

  1. Sajeesh says:

    If it is a website, can we not get the Orders per unit time from Access Logs? (provided we have access to Access Logs and we know the URL for Order Submission).
    Or did I get the objective of your exercise wrong?

    • Vishal Biyani says:

      Yes – there are multiple ways to do the same thing and getting info from logs is perfectly logical. The article just wanted to demonstrate use of CollectD to query DB and put graphs in Grafana through InfluxDB. There might be use cases where what you are looking for is not in logs or other use potential use cases.

  2. Sajeesh says:

    Gotcha!

  3. shaikibrahim04 says:

    Hi.
    I was trying to set this up but found this problem when starting collectd “Starting collectd: /usr/sbin/collectd: symbol lookup error: /usr/lib64/collectd/dbi.so: undefined symbol: ssnprintf_alloc”

    I followed all the instructions in SO and compiled to have the dbi files in ldd src/.libs/dbi.so

    Steps that i performed
    1) Installed collectd from yum repository by yum -y install collectd ( collectd-4.10.9-3.el6.x86_64)
    2) All the configuration changes done and also followed SO to have the dbi compiled.
    3) I get this error when i make changes to etc/collectd.conf for instance when changing the in conf file.
    4) also installed sudo yum install -y libdbi-dbd-mysql

    Can you please help me on this.

  4. shaikibrahim04 says:

    Just to add on to that.. I uninstalled the collectd which was installed from yum and did a make all install from the master check out of collectd..
    I see the following in the error log
    [2016-12-13 13:05:48] db query utils: udb_result_prepare_result: Type `AmountTrend’ is not known by the daemon. See types.db(5) for details.
    [2016-12-13 13:05:48] dbi plugin: cdbi_read_database_query (hotel_app, amount_per_order): dbi_result_first_row failed: dbi_conn_error failed with status -6. Maybe the statement didn’t return any rows?
    [2016-12-13 13:05:48] dbi plugin: All queries failed for database `hotel_app’.
    [2016-12-13 13:05:48] read-function of plugin `dbi:hotel_app’ failed. Will suspend it for 20.000 seconds.
    [2016-12-13 13:06:08] db query utils: udb_result_prepare_result: Type `AmountTrend’ is not known by the daemon. See types.db(5) for details.
    [2016-12-13 13:06:08] dbi plugin: cdbi_read_database_query (hotel_app, amount_per_order): dbi_result_first_row failed: dbi_conn_error failed with status -6. Maybe the statement didn’t return any rows?
    [2016-12-13 13:06:08] dbi plugin: All queries failed for database `hotel_app’.
    [2016-12-13 13:06:08] read-function of plugin `dbi:hotel_app’ failed. Will suspend it for 40.000 seconds.
    [2016-12-13 13:06:48] db query utils: udb_result_prepare_result: Type `AmountTrend’ is not known by the daemon. See types.db(5) for details.
    [2016-12-13 13:06:48] dbi plugin: cdbi_read_database_query (hotel_app, amount_per_order): dbi_result_first_row failed: dbi_conn_error failed with status -6. Maybe the statement didn’t return any rows?
    [2016-12-13 13:06:48] dbi plugin: All queries failed for database `hotel_app’.
    [2016-12-13 13:06:48] read-function of plugin `dbi:hotel_app’ failed. Will suspend it for 80.000 seconds.

  5. Vishal Biyani says:

    @shaikibrahim04 The first error you are getting around collectd can probably solved with: http://stackoverflow.com/questions/20189823/collectd-how-to-install-dbi-plugin

    The second error you are getting on around types looks like an misconfiguration in your types.db. Possible to show contents of types.db?

  6. shaikibrahim04 says:

    Hi i forgot to add the argument in types.db and after adding i get this exception
    dbi plugin: cdbi_read_database_query (hotel_app, amount_per_order): dbi_result_first_row failed: dbi_conn_error failed with status -6. Maybe the statement didn’t return any rows?
    [2016-12-13 13:16:02] dbi plugin: All queries failed for database `hotel_app’.

  7. shaikibrahim04 says:

    And yes i did follow the steps mentioned in the link http://stackoverflow.com/questions/20189823/collectd-how-to-install-dbi-plugin . I do have the files in ldd src/.libs/dbi.so directory.
    Also installed the sudo yum install -y libdbi-dbd-mysql. But i still have that error

  8. shaikibrahim04 says:

    Hi. this worked fine and i was able to see the data in influx and finally plot in grafana. Have a question do you know what type is supported from created_date. I get this error and i guess its something to do with datatype
    [2016-12-14 12:02:17] dbi plugin: Column `created_date’: Don’t know how to handle source type 5.
    [2016-12-14 12:02:17] dbi plugin: cdbi_read_database_query (heartbeat, amount_per_order): cdbi_result_get_field (8) failed.

  9. Santhoshkumar Nair says:

    Hi Vishal

    Wanted to know if this is possible , I am collecting data from multiple mysql databases using a single data collection host where collectd installed and configured . I can collect data from individual dabases using a configuration file but when i use multiple configurations for multiple databases , only one of them works , the subsequent one error out , individually all of them work. Looks like we can connect only to a single remote database

    Any idea

    • Vishal Biyani says:

      @Santoshkumar – I would imagine from documentation that it should work for multiple DBs and queries, if you can show your configuration I can probably say something more concrete.

  10. Santhoshkumar Nair says:

    not able to post the reply , getting a page not found error

  11. Vishal Biyani says:

    I mean multiple plugin blocks in above comment. The code is getting truncated

  12. Santhoshkumar Nair says:

    Hi Vishal , I tried both but apparently didn’t work .

    I was running Collectd 5.6 , I just rebuild collectd using latest code from 5.7 chain and it seems to be working . May be this could have been a BUG and fixed now , anyway , good info

  13. Soumyajit says:

    i want to configure collect.d for a custom shell script which checks port accessibility every 5 mins, any idea how that can be achieved? As I don’t see how to run any custom script where no plugin is available.

Leave a Reply

Your email address will not be published. Required fields are marked *

Enjoy this blog? Please spread the word :)