Wow, it sounds like logging to files and then bulk loading into SQL is going to be the best route to go. Is LOAD DATA a syslog-ng command? On Thu, Mar 10, 2011 at 3:49 PM, Martin Holste <mcholste@gmail.com> wrote:
Perhaps it is adding necessary complexity that can be done in a separate script, but perhaps after writing X lines to a log file or simply if it can be detected that a new file is created, as suggested by Martin's 1-minute log file example, a command could be spawned that will do the bulk load of the old one? Might need to be careful to limit the number of spawned bulk loads is all.
That could be useful and it would be fairly simple if it were implemented as a command to run on file close, like on_file_close() or something. That would be a lot fewer moving parts than running a script as a daemon or from cron. I think the tough part for syslog-ng is that it would have to know whether it's going to reopen the file or not, which wouldn't necessarily be obvious unless you're using time-based filenames.
BTW, if you use /dev/shm on Linux as your directory for temp files for the bulk loading, the logs never hit local disk and everything stays in RAM. There are of course some dangers in doing that, but file(/dev/shm/$MINUTE) + bulk load really can't be beat for DB speed. I get around 100k logs/sec inserted into MySQL using LOAD DATA. I would expect similar performance from MS-SQL, depending on the number of indexes on the table you're loading into, which is actually where your bottleneck is.
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html