Download Android App


Alternate Blog View: Timeslide Sidebar Magazine

Friday, December 30, 2011

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.

4 comments:

  1. In your shell script and in your Java code, it would be better to keep the connection to the TSD persistently open.

    In the shell script, this can be accomplished simply by moving the call the netcat out of the loop.

    In the Java code, simply remove the finally clause.

    I put the (untested) code up at https://gist.github.com/1540383

    ReplyDelete
  2. Java code updated as per comments above. Thanks @tsuna

    ReplyDelete
  3. Hiii,
    Sorry if I am troubling you. After installing HBase and OpenTSDB, I want to insert some data to OpenTSDB and fetch it. I am using the below code to add metric and fetch metric from OTSDB:
    Adding metric:
    "otsdb.post.test",
    "timestamp" => "1342888882",
    "value" => "23",
    "tags" => array(
    "host" => "otsdb",
    "method" => "post",
    )
    );
    //print_r($data);
    $data_string = json_encode($data);
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $service_url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $data_string);
    curl_setopt($ch, CURLOPT_HEADER, true);
    curl_setopt($ch, CURLOPT_HTTPHEADER, array(
    'Content-Type: application/json',
    'Content-Length:' .strlen($data_string)
    ));
    $output = curl_exec($ch);
    //echo($output) .PHP_EOL;
    echo '';
    print_r($ch);
    print_r($output);
    echo '';
    curl_close($ch);

    ?>
    Fetching metric:


    I am not getting any output and errors. I think I am using a url but here hostadrress:4242 is cloud host followed by OTSDB port. And after saving php file in a right place and running http:/hostadrress/test.php. I am not getting any o/p. Do I need to create any table before adding metric to OTSDB rather than some tables like table,table-uid,table-tree and one more which are created during installation.
    Please help me to figure out the mistake I am doing. Once again sorry if am troubling you.
    Thanks,
    Sri Kodali.

    ReplyDelete
  4. Hiii,
    Sorry if I am troubling you. I am using the below code to add metric and fetch metric from OTSDB:
    Adding metric:
    "otsdb.post.test",
    "timestamp" => "1342888882",
    "value" => "23",
    "tags" => array(
    "host" => "otsdb",
    "method" => "post",
    )
    );
    //print_r($data);
    $data_string = json_encode($data);
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $service_url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $data_string);
    curl_setopt($ch, CURLOPT_HEADER, true);
    curl_setopt($ch, CURLOPT_HTTPHEADER, array(
    'Content-Type: application/json',
    'Content-Length:' .strlen($data_string)
    ));
    $output = curl_exec($ch);
    //echo($output) .PHP_EOL;
    echo '';
    print_r($ch);
    print_r($output);
    echo '';
    curl_close($ch);

    ?>
    Fetching metric:


    I am not getting any output and errors. I think I am using a url but here hostadrress:4242 is cloud host followed by OTSDB port. And after saving php file in a right place and running http:/hostadrress/test.php. I am not getting any o/p. Do I need to create any table before adding metric to OTSDB rather than some tables like table,table-uid,table-tree and one more which are created during installation.
    Please help me to figure out the mistake I am doing. Once again sorry if am troubling you.
    Thanks,
    Srilakshmi Kodali.

    ReplyDelete