I'm looking for some help choosing a database backend for a iptables ulogd setup.

We are going to swap out our current proprietary firewall solution with iptables and one of the requirements is to log all traffic being forwarded by the firewall.

Looking at our current logs, we log about 4000000-5000000 entries pr 24 hours so the database should be fast on inserts.

The server is a Debian Wheezy setup so we prefer to use a database which can be maintained via aptitude.

We are currently looking at the following:

  • Mysql 5.5 (debian repository)
  • MariaDB 5.5
  • Percona 5.6
  • SQLite 3

After choosing a database, there is also the question of which storage engine fits our workload?

I'm been playing a little with the idea of having ulogd insert into a in-memory engine and then move the data to a on-disk based storage engine database in intervals, has anyone done something similar before?

  • 1
    It's one thing logging all data being forwarded through your firewall, it's another thing making sense of it. The data you're logging is only as useful as the programs used to analyse it. – Matt Dec 12 '13 at 0:32
  • 1
    What about Elasticsearch? It can store Terabyte per day and very friendly for search. en.wikipedia.org/wiki/Elasticsearch – Kindule Dec 12 '13 at 0:45

I think the answer would depend on what you need to do with that data. If it's real-time processing (search) you're looking for, then you probably need a (scalable) search engine, like Elasticsearch or Solr. Here are two links that might get you started on those two:

This is with Elasticsearch: http://blog.sematext.com/2013/09/24/presentation-on-centralizing-logs/

This is with Solr: http://blog.sematext.com/2013/11/11/presentation-solr-for-indexing-and-searching-logs/

If it's batch processing that you need, you'll probably be better off with Hadoop. Though you can have a design that can do both. And the search engines are including more and more analytics capabilities, like Elasticsearch does with aggregations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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