Time series and OpenTSDB

OpenTSDB is a distributed, scalable Time Series Database (TSDB) written on top of HBase. OpenTSDB was written to address a common need: store, index and serve metrics collected from computer systems (network gear, operating systems, applications) at a large scale, and make this data easily accessible and graphable.

This article will help you gather metrics and send it to OpenTSDB server. The detailed instructions on setting up OpenTSDB can be found here.

First, register the metric.

./tsdb mkmetric order.count.1m

A simple MySQL collector.

cat > orders.sh <<\EOF
#!/bin/bash
set -e
while true; do
  echo "Querying orders...."
  mysql -u user -ppassword -Ddatabase --batch -N --execute "select count(orderdate) from ORDERS where orderdate > date_sub(now(), INTERVAL 1 MINUTE)" \
  | awk -F"\t" -v now=`date +%s` -v host=`hostname` '{ print "put order.count.1m " now " " $1 " host=" host }' \
  | nc -w 5 tsdb.host.name tsdb.host.port
  || exit  # To handle SIGPIPE properly.
  sleep 60
done
EOF

chmod +x orders.sh
nohup ./orders.sh &

This shell script will poll the MySQL database every 1 minute, get the order count for the last 1 minute and publish it to the TSDB server. Open the TSDB screen, select "order.count.1m " as metric and you should see a graph like the following:

Most of data collection work can be accomplished by writing simple shell or python scripts. Here is a code snippet that can help you publish metrics using Java.

public static void main(String[] args) throws Exception {

   Socket echoSocket = null;
   PrintWriter out = null;

   String host = null;
   int port = 0;

   while (true) {
       try {
           if (echoSocket == null) {  // Only open the connection if necessary.
                echoSocket = new Socket(host, port);
                out = new PrintWriter(echoSocket.getOutputStream(), true);
           }

           int orderCount = getOrderCount(60);//Orders in last 60 sec
           out.println("put order.count.1m " + (new Date().getTime() / 1000) + " 100");
           out.flush();
       } catch (UnknownHostException e) {
           System.err.println("Don't know about host: " + host);
           return;
       } catch (IOException e) {
           System.err.println("Couldn't get I/O for the connection to: " + host);
           out.close();
           echoSocket.close();
           out = null;
           echoSocket = null;
       }

       System.out.println("Sleeping for 60 s");
       Thread.sleep(60000);
   }
}

public int getOrderCount(int interval){
   .........
   return orderCount;
}

More collectors are available on github.

There are many good things about TSDB like:

• Simple to setup and get started.
• Easy to build dashboards. Plot and compare multiple metrics on the same graph.
• Simple HTTP API to query the time series database using "/q" option. It can produce results in HTML, JSON, PNG or plain text. This way, you can use the data for numerical analysis or use a different plotting library than the default Gnuplot used by OpenTSDB.

It will be great to see some enhancements like:

• More aggregation functions like percentiles etc.
• Better support for counters, sampling and debugging.
• User guide.
• Export to more formats like CSV, Excel etc.

StatsD is another great tool for metrics aggregation and plotting. More about it in my future blog post.