1

I'm using rsyslog to write dns data to postgres. The messages are always quite short and similar to each other: <ip of host making dns request> <dns request> I use a regex to pull ip from requestor and FQDN from request.

rsyslog is constantly 40%-55% cpu usage while postgres is only 0.5%-2.5% cpu. (rsyslog and postgres are on the same host)

Is there any way to bring the rsyslog cpu usage down by shifting more of the workload to postgres? I've tried changing queue.size from 50,000 to 1,000 but it doesn't seem to make a difference.

template(name="sql-syslog" type="list" option.sql="on") {
  constant(value="INSERT INTO tablename (request_time, server, ip, request) values ('")
  property(name="timereported" dateformat="pgsql")
  constant(value="','")
  property(name="fromhost")
  constant(value="','")
  property(name="msg"
    regex.type="ERE"
    regex.submatch="1"
    regex.expression="([0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}) (.*\\.) (.*)$"
    regex.nomatchmode="DFLT"
  )
  constant(value="','")
    property(name="msg"
    regex.type="ERE"
    regex.submatch="2"
    regex.expression="([0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}) (.*\\.) (.*)$"
    regex.nomatchmode="DFLT"
    )
  )
  constant(value="')")
}

# load module
module(load="ompgsql")

action(type="ompgsql" server="dbhost"
       user="dbuser" pass="dbpass"
       db="dbname"
       template="sql-syslog"
       queue.size="1000"
       queue.type="LinkedList"
       queue.filename="dbq"
       queue.workerthreads="5"
       queue.workerthreadMinimumMessages="500"
       queue.timeoutWorkerthreadShutdown="1000"
       queue.timeoutEnqueue="10000")
2

Since the messages go through syslog to PostGreSQL you have to tune rsyslog.

Complex regex need processing - they are processed sequential - line by line - until the last match is done.

Your regex there looks quite complicated - try to make it easier - perhaps you can redirect DNS-logs to an own syslog-device (local2?) and log that complete device to PostGreSQL instead.

OR let DNS log directly to PostGreSQL - if possible.

| improve this answer | |
  • I tried tuning rsyslog but it didn't seem to affect performance. I agree that simplifying the regex is a good idea. The legacy system was just a python script that would read the log file line by line and perform an insert for each row. Surprisingly, its performance was about the same as rsyslog which is written in C and designed specifically for these kind of workloads. – sheepdog Nov 28 '18 at 1:39
  • Would you be willing to update your answer to mention that regex's in general are expensive. (especially compared to other options like field based extraction). If so, I'd be happy to accept your answer. I changed the regex stuff to field.number="..." and field.delimiter="..."and cpu usage went down by a factor of 10. – sheepdog Nov 29 '18 at 8:34
  • @sheepdog I updated the answer accordingly. Did you try a different syslog-device, too, or is that not configurable on your DNS? – Nils Nov 29 '18 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.