Skip to main content

Explicit transaction support in SQL

The SQL destination in syslog-ng so far assumed that databases automatically start a new transaction for each INSERT statement that syslog-ng issues. This works fine, however there is a significant overhead of starting new transactions, with sqlite I've measured about 20 times performance increase on my development notebook and my debug build.

With explicit-commits:
bazsi@bzorp:~/.zwa/install/syslog-ng-ose-3.2$ loggen -x -r 1000000 -I 10 -S log
average rate = 9377.28 msg/sec, count=93776, time=10.003, msg size=256, bandwidth=2344.32 kB/sec

With per-statement (automatic) commits:
bazsi@bzorp:~/.zwa/install/syslog-ng-ose-3.2$ loggen -x -r 1000000 -I 10 -S log
average rate = 529.46 msg/sec, count=5299, time=10.083, msg size=256, bandwidth=132.36 kB/sec

So this really seem to matter.

In order to configure it you can use the following options in an SQL destination:
  • flush_lines/flush_timeout controls how much messages get into the same transaction, similar to what these parameters mean for standard log files
  • flags("explicit-commits") enables explicit transaction handling
Also an option named "session_statements" was added where you can list initial SQL commands, issued right after the connection is established.

This work has been started by Patrick Hemmer (thanks again Patrick). I had to do some work on it though, since in order to avoid races the timer code of the main loop couldn't be used.

You can find all this in the syslog-ng OSE 3.2 branch. I'd love to hear success/failure stories and performance numbers you can measure with your favourite database.
Labels:

Comments

Post a Comment

Popular posts from this blog

syslog-ng fun with performance

I like christmas for a number of reasons: in addition to the traditional "meet and have fun with your family", eat lots of delicious food and so on, I like it because this is the season of the year when I have some time to do whatever I feel like. This year I felt like doing some syslog-ng performance analysis. After reading Ulrich Deppert's series about stuff "What every programmer should know about memory" on LWN, I thought I'm more than prepared to improve syslog-ng performance. Before going any further, I'd recommend this reading to any programmer, it's a bit long but every second reading it is worth it. As you need to measure performance in order to improve it, I wrote a tool called "loggen". This program generates messages messages at a user-specifyable rate. Apart from the git repository you can get this tool from the latest syslog-ng snapshots. Loggen supports TCP, UDP and UNIX domain sockets, so really almost everything can be me
17 comments
Read more

syslog-ng roadmap 2.1 & 2.2

We had a meeting on the syslog-ng roadmap today where we decided some important things, and I thought I'd use this channel to tell you about it. The Open Source Edition will see a 2.1 release incorporating all core changes currently in the Premium Edition and additionally the SQL destination driver. We are going to start development on the 2.2 PE features, but some of those will also be incorporated in the open source version: support for the latest work of IETF syslog protocols unique sequence numbering for messages support for parsing message contents Previously syslog-ng followed the odd/even version numbering to denote development/stable releases. I'm going to abandon this numbering now: the next syslog-ng OSE release is going to have a 2.1 version number and will basically come out with tested code changes only. The current feature set in PE were developed in a closed manner and I don't want to repeat this mistake. The features that were decided to be part of the Open
12 comments
Read more

syslog-ng 3.0 and SNMP traps

Last time I've written about how syslog-ng is able to change message contents. I thought it'd be useful to give you a more practical example, instead of a generic description. It is quite common to convert SNMP traps to syslog messages. The easiest implementation is to run snmptrapd and have it create a log message based on the trap. There's a small issue though: snmptrapd uses the UNIX syslog() API, and as such it is not able to propagate the originating host of the SNMP trap to the hostname portion of the syslog message. This means that all traps are logged as messages coming from the host running snmptrapd, and the hostname information is part of the message payload. Of course it'd be much easier to process syslog messages, if this were not the case. A solution would be to patch snmptrapd to send complete syslog frames, but that would require changing snmptrapd source. The alternative is to use the new parse and rewrite features of syslog-ng 3.0. First, you need to f
2 comments
Read more