On Thu, 2011-03-10 at 09:21 +0100, Zoltán Pallagi wrote:

Hi,

First of all, it seems that syslog-ng receives logs faster than your mssql server can process them that's the reason for dropping.

However, you are using udp source for receiving logs, it's not the best solution if you don't want to lose logs (UDP is not a lossless protocol and perhaps the kernel will also drop the unprocessed messages). You should use TCP instead of UDP.

If you use TCP, you can use flags(flow-control) in your server configuration. If the senders are also syslog-ng, you can use it on their configurations, too. flow-control will slow down (or block) receiving logs if syslog-ng cannot process (write out, forward and so on) the messages in time. It can prevent losing logs.

For example: log { source(s_gms); flags(flow-control); destination(d_mssql); };

Also, by increasing the buffer size, you may be able to process peaks, if otherwise your SQL server is fast enough to process at least the average load. The OSE features a memory based buffer that you can set using log_fifo_size(), the Premium one also has a disk based one. Also, the 3.2 release of syslog-ng contained a change that improves SQL performance a lot. (by enabling explicit-commits, instead of commit-by-insert which the older versions used by default). -- Bazsi

Please see my response on your earlier thread regarding writing to file and using bcp. On Thu, Mar 10, 2011 at 6:17 AM, Shawn Cannon <shawn@shawncannon.com> wrote:

Unfortunately the product we use only supports sending the syslog over UDP. I will try the latest version and the option you mentioned.

Shawn Cannon

On Mar 10, 2011 3:21 AM, "Zoltán Pallagi" <pzolee@balabit.hu> wrote:

...

Hi,

First of all, it seems that syslog-ng receives logs faster than your mssql server can process them that's the reason for dropping.

However, you are using udp source for receiving logs, it's not the best solution if you don't want to lose logs (UDP is not a lossless protocol and perhaps the kernel will also drop the unprocessed messages). You should use TCP instead of UDP.

If you use TCP, you can use flags(flow-control) in your server configuration. If the senders are also syslog-ng, you can use it on their configurations, too. flow-control will slow down (or block) receiving logs if syslog-ng cannot process (write out, forward and so on) the messages in time. It can prevent losing logs.

For example: log { source(s_gms); flags(flow-control); destination(d_mssql); };

For more details about flow-control:

http://www.balabit.com/sites/default/files/documents/syslog-ng-ose-v3.2-guid...

On 2011-03-10 06:28, Shawn Cannon wrote:

...

I really need help on this. I am collecting tons of SYSLOG data from over 500 firewalls. I have these syslogs going to a Barracuda Load Balancer first which will then send messages to 2 syslog-ng open source servers. Here is how I have my config file setup:

@version: 3.0 #Default configuration file for syslog-ng. # # For a description of syslog-ng configuration file directives, please read # the syslog-ng Administrator's guide at: # #

http://www.balabit.com/dl/html/syslog-ng-admin-guide_en.html/bk01-toc.html #

options { use_dns(no); };

###### # sources source s_gms { # message generated by Syslog-NG # internal(); # standard Linux log source (this is the default place for the syslog() # function to send logs to) # unix-stream("/dev/log"); # messages from the kernel #file("/proc/kmsg" program_override("kernel")); # messages destined to udp514 udp(ip(0.0.0.0) port(514)); }; source s_syslogng { internal(); };

###### # destinations destination d_messages { file("/var/log/messages"); }; destination d_mssql { sql(type(mssql) host("rawsql.abcdefg.net <http://rawsql.abcdefg.net>") port("1785") username("username") password("password") database("Syslog") table("syslogng")columns("datetime varchar(16)", "host varchar(32)", "program varchar(32)", "pid varchar(8)", "message varchar(4096)") values("$R_DATE", "$HOST", "$PROGRAM", "$PID", "$MSGONLY") indexes("datetime", "host", "program", "pid")); };

log { source(s_gms); destination(d_mssql); };

log { source(s_syslogng); destination(d_messages); };

When messages are coming in, I am showing the following in the local syslog-ng messages:

Log statistics; processed='source(s_gms)=2155636', dropped='dst.sql(d_mssql#0,freetds,rawsql.abcdefg.net <http://rawsql.abcdefg.net>,1785,Syslog)=1717472', stored='dst.sql(d_mssql#0,freetds,rawsql.abcdefg.net <http://rawsql.abcdefg.net>,1785,Syslog)=0',

I assume this is telling me that I am dropping the majority of my messages instead of them getting inserted into my MS SQL database? The MS SQL Database runs on a very beefy server with plenty of memory. I am trying to determine why this is being dropped. Please help as I am huge newbie when it comes to syslog-ng.

Thanks!

______________________________________________________________________________ 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

______________________________________________________________________________ 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

Feel free to contradict, but in my experience, if you have more than around 2k messages/second sustained, logging to any database directly puts you at very high risk of message drops. Flow control and other burst control mechanisms will not help if you have an unsustainable message rate. On Thu, Mar 10, 2011 at 9:33 AM, John Kristoff <jtk@cymru.com> wrote:

On Thu, 10 Mar 2011 09:21:56 +0100 Zoltán Pallagi <pzolee@balabit.hu> wrote:

...

If you use TCP, you can use flags(flow-control) in your server configuration. If the senders are also syslog-ng, you can use it on their configurations, too. flow-control will slow down (or block) receiving logs if syslog-ng cannot process (write out, forward and so on) the messages in time. It can prevent losing logs.

The one caveat with this approach seems to be that if you have multiple destinations, then all destinations will block until the one stalled destination is free. So for instance if the SQL destination is too slow, and you're also logging to a file, using flow-control may cause the file-based log to lose messages as well.

John ______________________________________________________________________________ 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

On Thu, 2011-03-10 at 11:23 -0500, Shawn Cannon wrote:

Thanks for all the info. The current method that our firewall management program uses to log messages into the current database is by multiple open connections to the database. syslog-ng is making one connection and trying to force everything down that one connection. So, my question is this: can syslog-ng be configured to make multiple connections to the SQL database to insert the data? Just so you have a comparison, our current product (which changes in the new version and why we need a different syslog product) has182 open connections open and that is from 8 agents. It stays up to speen by doing that. Thanks....

I somehow doubt that injecting messages via multiple connections would help the message rate. Did you enable explicit-commits? An even more high performance solution is to use batched inserts that syslog-ng currently doesn't support with its sql() destination. (e.g. LOAD FROM FILE and friends).

On Thu, Mar 10, 2011 at 11:06 AM, Martin Holste <mcholste@gmail.com> wrote: Feel free to contradict, but in my experience, if you have more than around 2k messages/second sustained, logging to any database directly puts you at very high risk of message drops. Flow control and other burst control mechanisms will not help if you have an unsustainable message rate.

On Thu, Mar 10, 2011 at 9:33 AM, John Kristoff <jtk@cymru.com> wrote: > On Thu, 10 Mar 2011 09:21:56 +0100 > Zoltán Pallagi <pzolee@balabit.hu> wrote: > >> If you use TCP, you can use flags(flow-control) in your server >> configuration. If the senders are also syslog-ng, you can use it on >> their configurations, too. >> flow-control will slow down (or block) receiving logs if syslog-ng >> cannot process (write out, forward and so on) the messages in time. >> It can prevent losing logs. > > The one caveat with this approach seems to be that if you have multiple > destinations, then all destinations will block until the one stalled > destination is free. So for instance if the SQL destination is too > slow, and you're also logging to a file, using flow-control may cause > the file-based log to lose messages as well. > > John > ______________________________________________________________________________ > 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 > > ______________________________________________________________________________ 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

______________________________________________________________________________ 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

-- Bazsi

On Thu, 2011-03-10 at 13:33 -0500, Shawn Cannon wrote:

I have not enabled explicit commits. That requires the latest version right? Also, where do I enable this in the config file?

Well, depends what you call latest :) I'm working on releasing 3.3alpha2 right now. But yes, it does require syslog-ng 3.2. But we do have a binary of that for Linux/amd64 on our website, and a number of - recent - distributions carry that. I'd recommend you trying the binary / rolling out a box with a recent enough distribution and evaluate if it's enough for your performance requirements. Alternatively request a PE evaluation for your platform, which almost certainly supports your production environment (it supports 40 different UNIX/Linux versions x CPU combinations). If it is, then you can have a plan going forward: * if that way the performance problem is resolved, you only need to solve how you get the latest version * if it's not, then you have to work out an alternative solution, like BCP that Martin has mentioned. You can use explicit-commits this way: sql(...various-sql-options... flags(explicit-commits) flush_lines(100)); e.g. you need to tell how much messages you want to group into the same transaction with flush_lines, and explicit-commits tells syslog-ng to use explicit BEGIN/COMMIT TRANSACTION commands.

On Thu, Mar 10, 2011 at 1:26 PM, Balazs Scheidler <bazsi@balabit.hu> wrote: On Thu, 2011-03-10 at 11:23 -0500, Shawn Cannon wrote: > Thanks for all the info. The current method that our firewall > management program uses to log messages into the current database is > by multiple open connections to the database. syslog-ng is making one > connection and trying to force everything down that one connection. > So, my question is this: can syslog-ng be configured to make multiple > connections to the SQL database to insert the data? Just so you have > a comparison, our current product (which changes in the new version > and why we need a different syslog product) has182 open connections > open and that is from 8 agents. It stays up to speen by doing that. > Thanks.... >

I somehow doubt that injecting messages via multiple connections would help the message rate. Did you enable explicit-commits?

An even more high performance solution is to use batched inserts that syslog-ng currently doesn't support with its sql() destination. (e.g. LOAD FROM FILE and friends).

> On Thu, Mar 10, 2011 at 11:06 AM, Martin Holste <mcholste@gmail.com> > wrote: > Feel free to contradict, but in my experience, if you have > more than > around 2k messages/second sustained, logging to any database > directly > puts you at very high risk of message drops. Flow control and > other > burst control mechanisms will not help if you have an > unsustainable > message rate. > > > On Thu, Mar 10, 2011 at 9:33 AM, John Kristoff <jtk@cymru.com> > wrote: > > On Thu, 10 Mar 2011 09:21:56 +0100 > > Zoltán Pallagi <pzolee@balabit.hu> wrote: > > > >> If you use TCP, you can use flags(flow-control) in your > server > >> configuration. If the senders are also syslog-ng, you can > use it on > >> their configurations, too. > >> flow-control will slow down (or block) receiving logs if > syslog-ng > >> cannot process (write out, forward and so on) the messages > in time. > >> It can prevent losing logs. > > > > The one caveat with this approach seems to be that if you > have multiple > > destinations, then all destinations will block until the one > stalled > > destination is free. So for instance if the SQL destination > is too > > slow, and you're also logging to a file, using flow-control > may cause > > the file-based log to lose messages as well. > > > > John > > > ______________________________________________________________________________ > > 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 > > > > > ______________________________________________________________________________ > 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 > > > > ______________________________________________________________________________ > 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 >

-- Bazsi

______________________________________________________________________________ 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

______________________________________________________________________________ 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

-- Bazsi

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.

No, LOAD DATA is a MySQL command within MySQL. The command-line version is mysqlimport. The MS-SQL equivalent is bcp.exe. For MongoDB, it's mongoimport. I believe it has an in-Mongo command as well, but I can't recall of the top of my head. And yes, this method is ridiculously fast. On Thu, Mar 10, 2011 at 3:43 PM, Shawn Cannon <shawn@shawncannon.com> wrote:

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