[syslog-ng] Expected performance
Jeff Dell
jdell at activeworx.com
Thu Feb 19 04:56:45 CET 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 at lists.balabit.hu
[mailto:syslog-ng-bounces at 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
More information about the syslog-ng
mailing list