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 OSE 2.1 released

I have just uploaded the first release in the syslog-ng Open Source Edition 2.1 branch to our website. It is currently only available in source format at this location: http://www.balabit.com/downloads/files/syslog-ng/sources/2.1/src This release synchronizes the core of syslog-ng to the latest PE version and adds the SQL destination driver. This is an alpha release and thus might be rough around the edges, but it basically only contains code already tested in the context of the Premium Edition. The SQL functionality requires a patched libdbi package, which is available at the same link. We're going to work on integrating all our libdbi related patches to the upstream package. If you want to know how the SQL logging works, please see the Administrator's Guide or our latest white paper Collecting syslog messages into an SQL database with syslog-ng. The latter describes the Premium Edition, but it applies to the Open Source one equally well.
6 comments
Read more

syslog-ng 3.2 changes

I've just pushed a round of updates to the syslog-ng 3.2 repository, featuring some interesting stuff, such as: SQL reorganization: Patrick Hemmer sent in a patch to implement explicit transaction support instead of the previous auto-commit mode used by syslog-ng. I threw in some fixes and refactored the code somewhat. Configuration parser changes: the syntax errors produced by syslog-ng became much more user-friendly: not only the column is displayed, but also the erroneous line is printed and the error location is also highlighted. Additional plugin modules were created: afsql for the SQL destination, and afstreams for Solaris STREAMS devices. Creating a new plugin from core code takes about 15 minutes. I'm quite satisfied. With the addition of these two modules, it is now possible to use syslog-ng without any kind of runtime dependency except libc. The already existing afsocket module (providing tcp/udp sources & destinations) is compiled twice: once with and once withou...
4 comments
Read more