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

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 contributions redefined

syslog-ng has been around for about 12 years now, but I think the biggest change in the project's life is imminent: with the upcoming release of syslog-ng OSE 3.2, syslog-ng will become an independent entity. Until now, syslog-ng was primarily maintained & developed by BalaBit, copyrights needed to be reassigned in order to grant BalaBit special privileges. BalaBit used her privileges to create a dual-licensed fork of syslog-ng, named "syslog-ng Premium Edition". The value we offer over the Open Source Edition of syslog-ng are things that larger enterprises require: support on a large number of UNIX platforms (27 as of 3.1), smaller and larger feature differences (like the encrypted/digitally signed logfile feature) better test coverage and release management longer term support Although perfectly legal, this business model was not welcome in various Free Software communities, and has caused friction and harm, because BalaBit has enjoyed a privilege that no others cou...
Post a Comment
Read more

syslog-ng message parsing

Earlier this month, I announced the new syslog-ng 3.0 git tree, adding a lot of new features to syslog-ng Open Source Edition. I thought it'd be useful to describe the new features with some more details, so this time I'd write about message parsing. First of all, the message structure was a bit generalized in syslog-ng. Earlier it was encapsulating a syslog message and had little space to anything beyond that. That is, every log message that syslog-ng handled had date, host , program and message fields, but syslog-ng didn't care about message contents. This has changed, a LogMessage became a set of name-value pairs , with some "built-in" pairs that correspond to the parts of a syslog message. The aim with this change is: new name-value pairs can be associated with messages through the use of a parsing. It is now possible to parse non-syslog logs and use the columns the same way you could do it with syslog fields. Use them in the name of files, SQL tables or c...
1 comment
Read more