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)
|
1 |
sudo yum install mariadb-server |
|
1 |
sudo mysql -u root -p |
Let’s create a Database and create a table to store the data we need:
|
1 2 3 |
create database hotel_app; use hotel_app; create table orders (order_id bigint auto_increment primary key, order_amount bigint, time TIMESTAMP); |
|
1 |
* * * * * 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:
|
1 |
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:
|
1 |
select order_id, order_amount from orders where time >= DATE_SUB(NOW(), INTERVAL 60 SECOND); |
|
1 2 3 4 5 6 7 |
LoadPlugin logfile <Plugin "logfile"> LogLevel "notice" File "/var/log/collectd.log" Timestamp true </Plugin> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
<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:
|
1 |
AmountTrend value:ABSOLUTE:0:U |
CollectD : Building Plugin
Now let’s restart the CollectD service and see how things go. Oh crap! We have some errors:
|
1 2 3 4 5 |
{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 |
After installing the SO to intended location we also need to install libdbi libraries – this library is used for mysql specific calls.
|
1 |
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:
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:
|
1 |
SELECT sum("value") FROM "dbi_" WHERE $timeFilter GROUP BY time(1m) fill(none) |
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.
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?
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.
Gotcha!
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.
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.hotel_app’.[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
[2016-12-13 13:05:48] read-function of plugin
dbi:hotel_app' failed. Will suspend it for 20.000 seconds.AmountTrend’ is not known by the daemon. See types.db(5) for details.[2016-12-13 13:06:08] db query utils: udb_result_prepare_result: Type
[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'.dbi:hotel_app’ failed. Will suspend it for 40.000 seconds.[2016-12-13 13:06:08] read-function of plugin
[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.hotel_app’.[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
[2016-12-13 13:06:48] read-function of plugin `dbi:hotel_app’ failed. Will suspend it for 80.000 seconds.
@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?
@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?
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’.
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
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.
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
@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.
not able to post the reply , getting a page not found error
That’s strange, paste it on http://pastebin.com or something and share link.
http://pastebin.com/WsNXyxwi
@Santoshkumar – my guess is you can not have multiple blocks. Instead in a single block – put multiple Query and Database blocks and link them by query field as shown in example in article. I have not tested this but should work IMO
I mean multiple plugin blocks in above comment. The code is getting truncated
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
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.
Hey Soumyajit, you can use the Exec plugin to achieve the same: https://collectd.org/wiki/index.php/Plugin:Exec
I have created my own types (user defined) but are not picking up by the collectd. have you guys seen this issue?
I have added below line in type.db and also tried to create types.db.custom, nothing worked.
oracle_inst value:GAUGE:0:U
[2018-04-14 14:57:10] db query utils: udb_result_prepare_result: Type `oracle_inst’ is not known by the daemon. See types.db(5) for details.
[2018-04-14 14:57:10] oracle plugin: o_read_database_query (db014205, database_name): udb_query_prepare_result failed.
Hello,
I have created my own types (user defined) but are not picking up by the collectd. have you guys seen this issue?
I have added below line in type.db and also tried to create types.db.custom, nothing worked.
oracle_inst value:GAUGE:0:U
[2018-04-14 14:57:10] db query utils: udb_result_prepare_result: Type `oracle_inst’ is not known by the daemon. See types.db(5) for details.
[2018-04-14 14:57:10] oracle plugin: o_read_database_query (db014205, database_name): udb_query_prepare_result failed.