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
On Wed, 2009-02-18 at 16:12 -0500, Christopher Bland wrote:
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 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
It isn't good that you are swapping. Though it isn't much at this point. Is there a memory leaky application on the box? What is the uptime of the box? FWIW, I generally don't consider ext3 to be a *fast* FS. -- Matt Zagrabelny - mzagrabe@d.umn.edu - (218) 726 8844 University of Minnesota Duluth Information Technology Systems & Services PGP key 1024D/84E22DA2 2005-11-07 Fingerprint: 78F9 18B3 EF58 56F5 FC85 C5CA 53E7 887F 84E2 2DA2 He is not a fool who gives up what he cannot keep to gain what he cannot lose. -Jim Elliot
Matt, This box is only running Mysql, Syslog-ng, Apache2. We have a minimal set of packages installed. uptime 16:43:43 up 6 days, 23:11, 6 users, load average: 1.45, 1.29, 1.42 -Chris Matt Zagrabelny wrote:
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
It isn't good that you are swapping. Though it isn't much at this point. Is there a memory leaky application on the box?
What is the uptime of the box?
FWIW, I generally don't consider ext3 to be a *fast* FS.
------------------------------------------------------------------------
______________________________________________________________________________ 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
Hi Just a suggestion ... but : did you try to launch mysql without the ndbd function enabled ? I think, this one is dedicated to mysql cluster, in other words, when several servers need to access the node. bye 2009/2/18 Christopher Bland <chris@fdu.edu>
Matt,
This box is only running Mysql, Syslog-ng, Apache2. We have a minimal set of packages installed.
uptime 16:43:43 up 6 days, 23:11, 6 users, load average: 1.45, 1.29, 1.42
-Chris
Matt Zagrabelny wrote:
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
It isn't good that you are swapping. Though it isn't much at this point. Is there a memory leaky application on the box?
What is the uptime of the box?
FWIW, I generally don't consider ext3 to be a *fast* FS.
------------------------------------------------------------------------
______________________________________________________________________________
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
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
Jeff Dell wrote:
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.
Any ideas of what those might be just curious what the highend stuff is doing. If I was faced with more then 50hosts I would probably go back to flat files in a tree hostname->Month/year->day->rawlogs Then you just have to develop some search scripts that can handle digging down the tree and making the results pretty [for the VP's since many would just use grep off the commandline]. -Mike
disclaimer: this message contains stuff about our commercial offerings. do not read it if that bothers you. On Thu, 2009-02-19 at 07:36 -0800, Mike Tremaine wrote:
Jeff Dell wrote:
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.
Any ideas of what those might be just curious what the highend stuff is doing. If I was faced with more then 50hosts I would probably go back to flat files in a tree
hostname->Month/year->day->rawlogs
Then you just have to develop some search scripts that can handle digging down the tree and making the results pretty [for the VP's since many would just use grep off the commandline].
Our syslog-ng Store Box is using the "logstore" format of syslog-ng PE, which is compressed/encrypted file format, indexed by message ID and time. On top of this we have implemented an indexing engine, that: 1) tokenizes incoming messages (e.g. splits them into words) 2) every now and then writes the accumulated tokens into an index file, in sorted form. Then searching is really fast, since the index is a binary-searchable file format. SSB is able to index about 20-25k msg/sec on commodity hardware. -- Bazsi
Is it possible to use macro expansion in the program destination. I wanted to write something like destination mydest{ program("/some/path/to/filter/program -options >> /path/log.$S_DAY"));}; I was hoping that syslog-ng would not open this destination until the first message was ready for this destination. I was hoping that syslog-ng would open a new one when the $S_DAY changed, allowing me to have each day of logs processed by my program with outputs based on day. Does anyone know if macro expantion can be used on program destinations? If not, can someone explain what I am overlooking that makes this a bad thing? Thanks, -- Evan Rempel
participants (7)
-
Balazs Scheidler
-
Christopher Bland
-
Evan Rempel
-
fredzy padzy
-
Jeff Dell
-
Matt Zagrabelny
-
Mike Tremaine