Re: [syslog-ng] Expected performance

19 Feb 2009

      The problem is MySQL and other traditional relational databases can't handle
that amount of data. That is why log management companies have moved away
from the traditional relational database engines and use other means to
store/query the data. 

Your MySQL memory settings are really low, they could be increased, but
MySQL really needs to store the index in memory or it will be start to
crawl. So... if you are indexing on msg it is going to start being really
slow in less then an hour with that system and amount of log data. If you
aren't indexing on msg and you try to query on msg it will do a table scan
to find the events of interest and will be very slow as well. 

Cheers,
Jeff

-----Original Message-----
From: syslog-ng-bounces@lists.balabit.hu
[mailto:syslog-ng-bounces@lists.balabit.hu] On Behalf Of Christopher Bland
Sent: Wednesday, February 18, 2009 4:13 PM
To: Syslog-ng users' and developers' mailing list
Subject: [syslog-ng] Expected performance

Hey guys,

I know it's hard to compare apples to apples when you start talking 
about performance and hardware but I would appreciate some feedback.  I 
am currently have a 32Bit HP Proliant DL380 with 2 2.8Ghz cpus and 4G of 
memory running Fedora 10.  At present I have 150 hosts generating 
between 10-15G worth of logs per day.  I dump all of my logs to a Mysql 
database so that I can use php-syslog-ng.  The OS is build on a 1.2T 
raid 5 disk array.  To  cut down on I/O I have the database writing to a 
ext3 filesystem while the rest of the box uses LVM.  I have mounted the 
database partition with noatime and implemented all of the mysqltuner 
suggestions.  My box is performing slow like molasses. 

My config looks like this:

options {
    sync (0);
    time_reopen (10);
    log_fifo_size (1000);
    chain_hostnames(no);
    long_hostnames (off);
    keep_hostname(no);
    use_dns (yes);
    dns_cache(yes);
    use_fqdn (no);
    create_dirs (no);
    keep_hostname (yes);
};

source s_sys {
    file ("/proc/kmsg" log_prefix("kernel: "));
    unix-stream ("/dev/log");
    internal();
};

source s_everything {
    file ("/proc/kmsg" log_prefix("kernel: "));
    unix-stream ("/dev/log");
    internal();
    udp();
};

destination d_database { pipe("/tmp/mysql.pipe" template("INSERT INTO 
logs (host, facility, priority, level, tag, datetime, program, msg) 
VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG', 
'$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC', '$PROGRAM', '$MSG' );\n") 
template-escape(yes)); };

destination d_cons { file("/dev/console"); };
destination d_mesg { file("/var/log/messages"); };
destination d_auth { file("/var/log/secure"); };
destination d_mail { file("/var/log/maillog" sync(10)); };
destination d_spol { file("/var/log/spooler"); };
destination d_boot { file("/var/log/boot.log"); };
destination d_cron { file("/var/log/cron"); };
destination d_mlal { usertty("*"); };

destination d_asa { 
file("/var/log/syslog-ng/network/asa/$YEAR/$MONTH/$YEAR-$MONTH-$DAY"
                        owner(root) group(staff) perm(0650) 
dir_perm(0750) create_dirs(yes)); };

#filter f_filter1   { facility(kern); };
filter f_filter2   { level(info..emerg) and not 
facility(mail,authpriv,cron); };
filter f_filter3   { facility(authpriv); };
filter f_filter4   { facility(mail); };
filter f_filter5   { level(emerg); };
filter f_filter6   { facility(uucp) or (facility(news) and 
level(crit..emerg)); };
filter f_filter7   { facility(local7); };
filter f_filter8   { facility(cron); };

filter f_asa            { host("asain-temp1"); };

log { source(s_sys); filter(f_filter2); destination(d_mesg); };
log { source(s_sys); filter(f_filter3); destination(d_auth); };
log { source(s_sys); filter(f_filter4); destination(d_mail); };
log { source(s_sys); filter(f_filter5); destination(d_mlal); };
log { source(s_sys); filter(f_filter6); destination(d_spol); };
log { source(s_sys); filter(f_filter7); destination(d_boot); };
log { source(s_sys); filter(f_filter8); destination(d_cron); };

log { source(s_everything); filter(f_asa); destination(d_asa); };
log { source(s_everything); destination(d_database); };

my.cnf looks like this:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
skip-innodb
skip-bdb
skip-name-resolve
table_cache = 128
tmp_table_size = 256M
max_heap_table_size = 256M
query_cache_size = 128M
query_cache_limit = 4M
read_rnd_buffer_size = 1M
thread_cache_size = 8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[ndbd]
connect-string="nodeid=2;host=localhost:1186"

[ndb_mgm]
connect-string="host=localhost:1186"

My load looks like this while inserting data:

Tasks: 177 total,   1 running, 176 sleeping,   0 stopped,   0 zombie
Cpu(s): 20.4%us,  2.3%sy,  0.1%ni, 75.9%id,  0.7%wa,  0.0%hi,  0.6%si,  
0.0%st
Mem:   3634412k total,  3507692k used,   126720k free,    20224k buffers
Swap:  8388600k total,       64k used,  8388536k free,  3246292k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  
COMMAND                                                              
 6868 mysql     20   0  165m  20m 5088 S 91.9  0.6   1488:41 
mysqld                                                                
 7679 root      20   0  4140 1932  948 S 15.6  0.1 158:18.24 
syslog-ng                                                             
 7705 root      20   0  8456 1756 1384 S  5.9  0.0  79:18.84 
mysql                                                                 
21296 root      20   0  2556  996  740 R  2.0  0.0   0:00.01 
top                                                                   
    1 root      20   0  2012  780  568 S  0.0  0.0   0:01.62 
init                                                                  
    2 root      15  -5     0    0    0 S  0.0  0.0   0:00.00 
kthreadd                                                              
    3 root      RT  -5     0    0    0 S  0.0  0.0   0:00.14 
migration/0                                                           
    4 root      15  -5     0    0    0 S  0.0  0.0   0:17.46 
ksoftirqd/0                                                           
    5 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 
watchdog/0                                                            
    6 root      RT  -5     0    0    0 S  0.0  0.0   0:00.18 
migration/1                                                           
    7 root      15  -5     0    0    0 S  0.0  0.0   0:26.76 
ksoftirqd/1                                                           
    8 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 
watchdog/1                                                            
    9 root      RT  -5     0    0    0 S  0.0  0.0   0:00.13 
migration/2                                                           
   10 root      15  -5     0    0    0 S  0.0  0.0   0:22.02 
ksoftirqd/2                                                           
   11 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 
watchdog/2                                                            
   12 root      RT  -5     0    0    0 S  0.0  0.0   0:00.11 
migration/3                                                           
   13 root      15  -5     0    0    0 S  0.0  0.0   0:11.79 
ksoftirqd/3                                                           
   14 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 
watchdog/3                                                            
   15 root      15  -5     0    0    0 S  0.0  0.0   0:04.69 
events/0                                                              
   16 root      15  -5     0    0    0 S  0.0  0.0   0:02.86 
events/1                                                              
   17 root      15  -5     0    0    0 S  0.0  0.0   0:02.80 
events/2                                                              
   18 root      15  -5     0    0    0 S  0.0  0.0   0:02.91 
events/3                                                              
   19 root      15  -5     0    0    0 S  0.0  0.0   0:00.00 
khelper                                                               
   95 root      15  -5     0    0    0 S  0.0  0.0   0:00.00 
kintegrityd/0                                                         
   96 root      15  -5     0    0    0 S  0.0  0.0   0:00.00 
kintegrityd/1                                                         
   97 root      15  -5     0    0    0 S  0.0  0.0   0:00.00 
kintegrityd/2                                                         
   98 root      15  -5     0    0    0 S  0.0  0.0   0:00.00 
kintegrityd/3                                                         
  100 root      15  -5     0    0    0 S  0.0  0.0   0:00.20 
kblockd/0                                                             
  101 root      15  -5     0    0    0 S  0.0  0.0   0:00.14 
kblockd/1                                                             
  102 root      15  -5     0    0    0 S  0.0  0.0   0:00.33 
kblockd/2                                                             
  103 root      15  -5     0    0    0 S  0.0  0.0   0:12.47 
kblockd/3                                                             
  105 root      15  -5     0    0    0 S  0.0  0.0   0:00.00 
kacpid                                                                
  106 root      15  -5     0    0    0 S  0.0  0.0   0:00.00 kacpi_notify

The box gets almost unusable when I do a query to retrieve data from the 
database?  Again, I would appreciate any thoughts or suggestions .

-Chris Bland
____________________________________________________________________________
__
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

Re: [syslog-ng] Expected performance

Jeff Dell