I gave up trying to insert my bi-daily 1.000.000 fw-1 logs in a mysql db. The first half of the day was inserted in 2 hours, the next one took almost 4 hours. The 3rd day (6th file) took almost 6 hours. The db was /usr/local/mysql/bin/mysql Ver 11.6 Distrib 3.23.28-gamma, for sun-solaris2.7 (sparc) The server was a sun U10 (400 Mhz) with 256 Mb. Inserts were done at night (no other resource intensive activity) as it was a development server during the days. I was using the raw insert command (which I forgot the real name) as it was supposed to be the fastest method. So, on a reasonably slow machine, I get something around 100 records per second. "Marcus J. Ranum" wrote: > Mike Lee wrote: > >I'm looking for ways to send multiple devices' syslog messages to a mysql > >database and be able to analyze logs via some sort of web gui. > > Which nicely raises the next thing I wanted to ask: > Does anyone have any statistics for how many records/second you can > insert into a MySql database for a reasonable set of assumptions? I.e.: > assuming one primary index and a reasonably fast machine (say 1Ghz > with 1GB RAM)* In order to be able to analyse my logs efficiently, I created a lot of indexes - 14! Way too much for a fast insert. I'll do some more test one day or an other will only the minimum usefull indexes. But I doubt I could efficiently analyse my logs with a single primary index. I'm definitely not a db wizard, so, please, don't laugh too loudly. My indexes where: KEY `idx1`(`whatisid`,`srcwhoisid`,`i_f_name`,`i_f_dir`,`src`,`proto`,`service`,`icmp_type`,`icmp_code`,`action`,`th_flags`), KEY `idx2`(`whatisid`,`dstwhoisid`,`i_f_name`,`i_f_dir`,`dst`,`proto`,`service`,`icmp_type`,`icmp_code`,`action`,`th_flags`), KEY `idx3`(`whatisid`,`proto`,`service`,`icmp_type`,`icmp_code`,`action`,`th_flags`), KEY `idx4`(`whatisid`,`proto`,`s_port`,`icmp_type`,`icmp_code`,`action`), KEY `idx5`(`whatisid`,`srcwhoisid`,`i_f_name`,`i_f_dir`,`src`,`proto`,`service`,`icmp_type`,`icmp_code`,`action`,`th_flags`), KEY `idx6`(`whatisid`,`srcwhoisid`,`i_f_name`,`i_f_dir`,`src`,`whatisid`,`dstwhoisid`,`dst`,`proto`,`service`,`user`,`th_flags`), KEY `idx7`(`whatisid`,`srcwhoisid`,`i_f_name`,`i_f_dir`,`src`,`proto`,`whatisid`,`dstwhoisid`,`dst`), KEY `idx8`(`whatisid`,`refsrcwhoisid`,`ref_src`,`whatisid`,`srcwhoisid`,`i_f_name`,`i_f_dir`,`src`,`ref_p`,`ref_dport`,`action`), KEY `idx9`(`whatisid`,`refdstwhoisid`,`ref_dst`,`whatisid`,`srcwhoisid`,`i_f_name`,`i_f_dir`,`src`,`ref_p`,`ref_sport`,`action`), KEY `idx10`(`user`,`src`), KEY `idx11`(`whenid`,`whatisid`), KEY `idx12`(`whenid`,`srcwhoisid`,`src`,`i_f_name`,`i_f_dir`), KEY `idx13`(`whenid`,`dstwhoisid`,`dst`), KEY `idx14`(`date`,`time`,`whenid`) The field names comes from the firewall-1 logs with some additions: whatisid: intended to mark some logs as part of a single incident. whenid: intended to mark some entries that were logged during some intervals known to be special (week-end, lunch time, last known network flaw,...) srcwhoisid,dstwhoisid: identifies the source and destination netblocks as found by whois whois.arin.net ref_*: info about the packet that caused the source of the icmp reply packets. I could easily drop a couple of indexes and made them simpler, but 4-5 indexes would probably be my minimum (or maybe should I read some db begginer's guide). At this time, I was wondering if a standard sql db could really meet the requirements for a security oriented log analisys. I also had a looked at framerd, but too hard for me. I'l do another test with a netra 440 - 758 Mb sooner or later. > If syslog is puking its guts out over loads in the thousands of records/second > I guess it's a moot point since the data will never get to the database anyhow, > but assuming syslog "works" will there be a huge bottleneck at the database > input stage? --------------------------------------------------------------------- To unsubscribe, e-mail: loganalysis-unsubscribeat_private For additional commands, e-mail: loganalysis-helpat_private
This archive was generated by hypermail 2b30 : Wed Jan 30 2002 - 09:47:40 PST