Monday, April 12, 2010

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.

No comments: