ABOUT
Posts Tagged ‘sql’
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.
GStaticMutex and AIX
If you use GLib on non-Linux platforms such as AIX and think that G_STATIC_MUTEX_INIT does nothing but zero-initialize the mutex, think twice. Although quite clearly stated in the documentation, I thought I was smarter and used a GStaticMutex embedded in a structure that was zero initialized.
If you look at the definition of G_STATIC_MUTEX_INIT on most platforms (Linux, Solaris, BSDs), it contains nothing but zeroes. This lead me to the impression that zero filling a GStaticMutex instance is enough to initialize it.
In reality it isn’t. On AIX this renders the mutex to be entirely useless without warnings or aborts. The results are of course bugs that are difficult to track down and fix.
This took me an entire day to figure out, as the SQL driver in syslog-ng had this problem. This was fixed since, but if you are running syslog-ng on AIX with the SQL driver, be sure to have this patch applied.