Snort mailing list archives
RE: Database performance question (MySQL or PostgreSQL?)
From: "Kreimendahl, Chad J" <Chad.Kreimendahl () umb com>
Date: Fri, 26 Sep 2003 10:24:20 -0500
There is quite a bit of tuning that can be done to increase the performance... However your problem likely lies in MySQL doing fulltable scans for its JOINs. You will probably be able to get it running reasonably up to 200k records. I would suggest, if this is NOT a production system for a corporation, that you delete all records within a certain timeframe. A query that deletes data from the other tables that subqueries for the primary key (sid,cid) based on timestamp from the event table... Just make sure you get the event table last.. Example for Oracle: (<days> = total number of days of data to chop off the end) DELETE FROM [table-to-delete-from (data, opt, iphdr, tcphdr, udphdr, icmphdr, event)] WHERE (sid,cid) IN ( SELECT sid,cid FROM event WHERE event.timestamp <= (min(event.timestamp)+<days>) ) Please note, that based on using the timestamp to delete records, this is the only reliable way to make sure you're deleting the exact same data. Using the alternate that I've seen (NOW()-<days>), you're likely to end up with events out there in other tables that aren't in event... Or vice versa. -----Original Message----- From: Jyri Hovila [mailto:jyri.hovila () iki fi] Sent: Friday, September 26, 2003 2:24 AM To: snort-users () lists sourceforge net Subject: [Snort-users] Database performance question (MySQL or PostgreSQL?) Howdy! Please let's not let this turn this into SQL wars. =) I'm sure this issue has been discussed several times before but I'm unable to find anything about it in the snort-users archives. I'm currently running several Snort sensors with a central MySQL database. Recently the database speed has become a problem. When the number of alerts is starting to reach 100 000, ACID is starting to get slow. Add another 100 000 alerts and ACID is almost unusable. My database server is not doing anything else but running MySQL and ACID. Here are the specs: - Pentium II 450 MHz (normally almost totally idle, jumps to 80% when making SQL queries) - 384 RAM (about 50% used, jumps to 60-70% when making queries) - 7200 RPM IDE HD (yes, I know...) As CPU and RAM utilization is almost never higher than 80% and still the queries take awfully long to finish, could the HD be a problem? I remember seeing discussions about differences between MySQL and PostgreSQL performance. If I remember correctly, PostgreSQL was believed to be somewhat faster. Could anybody with some real life experience on this issue share hers/his knowledge? Thanks! - Jyri ------------------------------------------------------- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf _______________________________________________ Snort-users mailing list Snort-users () lists sourceforge net Go to this URL to change user options or unsubscribe: https://lists.sourceforge.net/lists/listinfo/snort-users Snort-users list archive: http://www.geocrawler.com/redir-sf.php3?list=snort-users ------------------------------------------------------- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf _______________________________________________ Snort-users mailing list Snort-users () lists sourceforge net Go to this URL to change user options or unsubscribe: https://lists.sourceforge.net/lists/listinfo/snort-users Snort-users list archive: http://www.geocrawler.com/redir-sf.php3?list=snort-users
Current thread:
- Database performance question (MySQL or PostgreSQL?) Jyri Hovila (Sep 26)
- Re: Database performance question (MySQL or PostgreSQL?) Demetri Mouratis (Sep 26)
- <Possible follow-ups>
- RE: Database performance question (MySQL or PostgreSQL?) Kreimendahl, Chad J (Sep 26)
- RE: Database performance question (MySQL or PostgreSQL?) JP Vossen (Sep 27)