Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are looking at developing a tool to capture and analyze netflow data, of which we gather tremendous amounts of. Each day we capture about ~1.4 billion flow records which would look like this in json format:

{
   "tcp_flags": "0",
   "src_as": "54321",
   "nexthop": "1.2.3.4",
   "unix_secs": "1352234521",
   "src_mask": "23",
   "tos": "0",
   "prot": "6",
   "input": "105",
   "doctets": "186",
   "engine_type": "0",
   "exaddr": "2.3.4.5",
   "engine_id": "2",
   "srcaddr": "9.8.7.6",
   "dst_as": "12345",
   "unix_nsecs": "752265174",
   "sysuptime": "2943529544",
   "dst_mask": "24",
   "dstport": "80",
   "last": "2943523241",
   "srcport": "52672",
   "dpkts": "4",
   "output": "111",
   "dstaddr": "6.5.4.3",
   "first": "2943517993"
}

We would like to be able to do fast searches (less than 10 seconds) on the data set, most likely over narrow slices of time (10 - 30 mintes intervals). We also want to index the majority of the data points so we can do searches on each of them quickly. We would also like to have an up to date view of the data when searches are executed. It would be great to stay in the open source world, but we are not opposed to looking at proprietary solutions for this project.

The idea is to keep approximately one month of data, which would be ~43.2 billion records. A rough estimate that each record would contain about 480 bytes of data, would equate to ~18.7 terabytes of data in a month, and maybe three times that with indexes. Eventually we would like to grow the capacity of this system to store trillions of records.

We have (very basically) evaluated couchbase, cassandra, and mongodb so far as possible candidates for this project, however each proposes their own challenges. With couchbase the indexing is done at intervals and not during insertion of the data so the views are not up to date, cassandra's secondary indexes are not very efficient at returning results as they typically require scanning the entire cluster for results, and mongodb looks promising but appears to be far more difficult to scale as it is master/slave/sharded. Some other candidates we plan to evaluate are elasticsearch, mysql (not sure if this is even applicable), and a few column oriented relational databases. Any suggestions or real world experience would be appreciated.

share|improve this question
4  
Seems like a job for a big SQL Server to me. Other than the number of records this is straight-up SQL. –  Max Vernon Mar 28 '13 at 16:25
5  
A nice article about data types and "how I managed to have 27K transactions per second": SCALING THE DATABASE: DATA TYPES –  ypercube Mar 28 '13 at 16:36
6  
How are you arriving at the 480 bytes/row number? Are you going to store these as strings (insanity!)? My rough count using either 4 or 8 byte integers to store these values is 116 bytes/row; this could probably be narrowed down even more. –  Jon Seigel Mar 28 '13 at 16:58
9  
I guess the last question probably should of been the first... have you got a $1M+ budget? –  Mark Storey-Smith Mar 28 '13 at 20:29
6  
On reflection, I've VtC'd. The answer to "Which database could handle storage of billions/trillions of records?" is "all of them" but your question is something else. Following the comments conversation it appears you're looking for a tier 1 network analysis tool, which is likely cheaper to buy than build. Might be better off closing this and posting that question at ServerFault. –  Mark Storey-Smith Mar 29 '13 at 18:24
show 17 more comments

5 Answers

up vote 22 down vote accepted

In a company I work for we are dealing with similar amount of data (around 10 TBs of realtime searchable data). We solve this with Cassandra and I would like to mention couple of ideas that will allow you to do O(1) search on a multi TBs database. This is not specific to Cassandra db though, you can use it with other db as well.

Theory

  • Shard your data. There is no way a single server will reliably and realistically hold such volume of data
  • Be ready for hardware faults and whole node failures, duplicate the data
  • Start using many back-end servers from the begining
  • Use many cheaper comodity servers, compared to top-end high performance ones
  • Make sure data is equaly distributed accross shards
  • Spend a lot of time planning your queries. Derive API from the queries and then carefully design tables. This is the most important and prolonged task
  • In Cassandra, you can design a composite column key and get access to that key in O(1). Spend time working on them. This will be used to access searchable records instead secondary index
  • Make use of wide rows. They are useful for storing time-stamped events
  • Never perform full-scan or in fact any operation more than O(Log N) on such volume. If you require anything more than O(Log N), offload such operations to Map-Reduce algorithms

Practise

  • Don't spend time building OS images or installing servers on physical machines. Use cloud based providers for quick prototyping. I worked with Amazon EC2 and can highly recommend it for its simplicity, reliability and speed of prototyping
  • Windows machines tend to be slower during boot time and take considerably more resources being in Idle state. Consider using Unix-based OS. Personally, I found Ubuntu server to be a reliable OS, but moreover there is a pretty good community at askubuntu
  • Think about networking, nodes shall ideally be close to each other to allow fast gossipping and meta-data exchange
  • Do not go into extreme cases: really wide column rows or exceptionally long column families (tables). Best performance is achieved in the sane boundaries - if db supports that many N rows by design, it doesn't mean it performs well
  • Our search takes about 3-5 seconds, much is due to the intermediate nodes between UI and the database. Consider how to bring requests closer to the database.
  • Use a network load balancer. Chose an established one. We use HAProxy, which is simple, but dead fast. Never had problems with it
  • Prefer simplicity to complex solutions
  • Look for free open-source solutions, unless you are backed up by a corporation's size budget. Once you go more than several servers, the costs of infrastructure might go sky high

I do not work for Amazon and have no relations with HAProxy and Ubuntu teams. This is a personal opinion rather than any sort of promotion

share|improve this answer
4  
I'm pretty sure an O(1) search is impossible aside from extremely trivial/useless cases. –  Fitzsimmons Mar 28 '13 at 21:30
1  
Please take no offense, but tell that to Google. O(1) search is possible on PB scale under careful design. –  oleksii Mar 28 '13 at 21:36
8  
@oleksii Billion dollar Google budgets are not a reasonable comparison to draw. –  Mark Storey-Smith Mar 28 '13 at 22:56
2  
I can connect the 3 previous comments with O(1) search <=> unbounded storage space <=> unlimited supply of cash –  ypercube Mar 28 '13 at 23:02
3  
O(1) search for a single record can be done with a linear hash table.. However, this doesn't give you any efficiencies in searching sequentially (for ranges). For this you need some variant of a BTree structure, which is O(log n) for a single item. –  ConcernedOfTunbridgeWells May 24 '13 at 17:07
show 3 more comments

If I was going to put this into SQL Server, I would suggest a table something like:

CREATE TABLE tcp_traffic
(
    tcp_traffic_id bigint constraint PK_tcp_traffic primary key clustered IDENTITY(1,1)
    , tcp_flags smallint    /* at most 9 bits in TCP, so use SMALLINT */
    , src_as int        /* Since there are less than 2 billion A.S.'s possible, use INT */
    , netxhop bigint    /* use a big integer for the IP address instead of storing
                             it as dotted-decimal */
    , unix_secs bigint  
    , src_mask int      /* an assumption */
    , tos tinyint       /* values are 0-255, see RFC 791 */
    , prot tinyint      /* values are 0-255, see RFC 790 */
    , input int         /* an assumption */
    , doctets int       /* an assumption */
    , engine_type int   /* an assumption */
    , exaddr bigint     /* use a big integer for the IP address instead of storing
                             it as dotted-decimal */
    , engine_id int     /* an assumption */
    , srcaddr bigint    /* use a big integer for the IP address instead of storing
                             it as dotted-decimal */
    , dst_as int        /* Since there are less than 2 billion A.S.'s possible, use INT */
    , unix_nsecs bigint /* an assumption */
    , sysuptime bigint  /* an assumption */
    , dst_mask int      /* an assumption */
    , dstport smallint  /* ports can be in the range of 0 - 32767 */
    , [last] bigint     /* an assumption */
    , srcport smallint  /* ports can be in the range of 0 - 32767 */
    , dpkts int         /* an assumption */
    , output int        /* an assumption */
    , dstaddr bigint    /* use a big integer for the IP address instead of storing
                            it as dotted-decimal */
    , [first] bigint    /* an assumption */
);

This results in a total estimated storage requirement for the single table, with no further indexes of 5.5 TB for 43.2 beeellion records (your specified requirement). This is calculated as 130 bytes for the data itself, plus 7 bytes per row of overhead, plus 96 bytes per page of overhead. SQL Server stores data in 8KB pages, allowing for 59 rows per page. This equates to 732,203,390 pages for a single month of data.

SQL Server likes writing to disk in 8-page chunks (64KB), which equates to 472 rows per physical I/O. With 16,203 flow records being generated every second, you will need a minimum I/O rate of 34 IOps, guaranteed each and every second. Although this by itself is not a huge amount, other I/O in the system (SQL Server and otherwise) needs to never infringe on this necessary rate of IOps. Therefore you'd need to design a system capable of at least an order-of-magnitude more IOps, or 340 sustained IOps - I would tend to estimate that you need 2 orders of magnitude more sustainable IOps to guarantee throughput.

You will notice I am not storing the IP addresses in their dotted-decimal form. This saves a huge amount on storage (7 bytes per address), and also makes indexing, retrieval, sorting, and comparing IP addresses far, far more efficient. The downside here is you need to convert the dotted-decimal IPs into 8-byte integers before storing them, and back to dotted-decimal IPs for display. The code to do so is trivial, however your row-rate this will add a substantial amount of processing overhead to each flow row being processed - you may want to do this conversion process on a physically different machine from SQL Server.

Discussing the indexes you require is a totally separate matter since you have not listed any specific requirements. The design of this table will store flow rows in the physical order they are received by SQL Server, the tcp_traffic_id field is unique for each record, and allows sorting rows by the order they were recorded (in this case most likely relating one-to-one to the time of the flow event).

share|improve this answer
3  
I would probably use binary(4) or binary(16), respectively. 4 bytes/row adds up to a lot of storage when multiplied by 1,000,000,000,000. –  Jon Seigel Mar 28 '13 at 21:46
1  
And port numbers have a 0-65535 range, so you can use SMALLINT but there has to be a conversion routine there, too. –  ypercube Mar 28 '13 at 22:50
7  
@MrTelly I disagree. To do it in SQL Server is expensive only if you need HA or big failover stuff. For a solid data store, that is really easy to live with, SQL Server is great for this. All the systems get very expensive (and complicated) if HA is needed. –  samsmith Mar 29 '13 at 7:09
1  
IMO, SQL Server can definitely store the data; I'm still unsure if it's the right solution to solve the analytics portion of the project, mostly because I'm not familiar enough with the other systems being considered. –  Jon Seigel Mar 29 '13 at 14:53
3  
@MrTelly There are two expenses: a) Disk storage (for 5-8 tb, depending on space used by indexes) b) RAM (to support queries, index caching). To do this monolithically would usually be done with a big RAID10 array or SAN. However, note that sharding can certainly be done, and could let you use application level logic to shard the workload over multiple SQL Servers. This could allow you to use cheap servers, with 0.5-2tb each, and perhaps even use the free SQL Server edition. (Note that sharding is a generic concept, is often done at the app level, and applies to any persistence method) –  samsmith Mar 31 '13 at 6:07
show 4 more comments

I would recommend HBase. You can store all the raw data in one or more HBase tables, depending on what you need to query. HBase can handle large data-sets and does auto-sharding through region splits.

In addition, if you design row keys well, you can get extremely fast, even O(1) queries. Note that if you are retrieving a large data set, that is still going to be slow since retrieving data is an O(n) operation.

Since you want to query across each field, I would recommend creating a unique table for each of them. Example for the src_address data, have a table that looks like this:

1.2.3.4_timestamp1 : { data }
1.2.3.4_timestamp2 : { data }

So if you want to query for all data across 1.2.3.4 starting from Mar 27 12:00 AM to Mar 27 12:01 AM, you can do a range scan with the start and stop rows specified.

IMHO, the row key design is the most critical part of using HBase - if you design it well, you will be able to do fast queries AND store large volumes of data.

share|improve this answer
add comment

Said this :

...we are not opposed to looking at proprietary solutions for this project

I suggest consider IBM Informix database + TimeSeries datablade. Opposite what some people says, Informix is alive and going very well. The last version was released last month (March/2013 ,version 12.10).

TimeSeries is like a "plugin" (no-cost) able to deal with situations like yours.
And you can use it in production with the free version of Informix database (edition Innovator-C). (off course, only to evaluate the technical parts since the free version have lot of limited resources)

Here you can check a PDF of benchmark what can be used as reference. Here two presentations with more technical examples : dummies guide and other tips

I do not have personal experience with TimeSeries , so I can't agree it will be "the solution" , just a suggestion to evaluate.

share|improve this answer
add comment

I second the recommendation to look at Informix TimeSeries. IBM literature claims TimeSeries can store this kind of information in 1/5th the space and perform 5 times as fast as traditional relational tables.

Additional benefits would be the Virtual Table Interface that can make TimeSeries data appear like traditional relational tables to the end user (simplifying application development while still getting the benefits of TimeSeries), simple HA with HDR nodes that now support TimeSeries data in version 12.1 and the integration of TimeSeries data into the Informix Warehouse Accelerator that can be used to speed up complicated data warehouse reports and the ability to prototype a TimeSeries solution in Informix using the free Informix Developer or Innovator-C editions.

share|improve this answer
add comment

protected by Community Apr 9 at 18:58

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.