[Snort-users] a user experience w/ Snort, ACID & (Postgre|My) SQL

Kevin Brown Kevin.M.Brown at ...1022...
Wed Oct 3 08:28:05 EDT 2001

> I am very new to Snort & practical ID though I've read like many the
> books from Nortcutt & co. I have installed my first Snort sensors 4/5
> weeks ago and before continuing any further, I'd like to thank Marty &
> the crew for such a good system. I am writing this to share my
> experience on the subject if anyone is interested. If no one gives a
> heck about it, then sorry for the bandwidth noise :p
> Since I am working on a project for my current employer for
> small-to-wide deployments of Snort, I choosed for my first install
> PostgreSQL as the DB backend on an OpenBSD platform. I am not as
> knowledgeable w/ RDBMS as I am w/ OSes in general. My OpenBSD 
> kernel is
> as optimized as I can make it & I applied every trick I found about
> increasing PostgreSQL performance but still, the 
> ACID/PostgreSQL couple
> is *extremely* slow. The hardware I am using is very standard. I have
> been in touch w/ Chris Kuethe & Roman & others about this 
> very subject,
> read the archives ... to no avail. Looked into DNS bottlenecks, fs
> performance ...etc. After a while, I switched the RDBMS to MySQL. Same
> hardware, just 'mv PostgreSQL MySQL'. And the performance sky 
> rocketed.
> Literally. While it took ages to load the ACID main page w/ 
> 5000 alerts
> w/ PostgreSQL as the backend, it showed in a snap w/ MySQL. I am
> stumped. The system is not *that* loaded (19%sys, 34%user at 
> most & for
> very short times) in either case. The system is not swapping (or very
> little). But ACID/MySQL is much faster than ACID/PostgreSQL.
> Please, I do not want to start a PostgreSQL vs. MySQL flame war. I am
> just saying that in my particular case, MySQL saves the day. The only
> problem I am having now is w/ persistent connections & httpd gobbling
> memory but that's another story.

I have 1 sensor on the network here (more will be added later) that monitors
200Mb/s of bandwidth to the net (50Mb/s commercial, 155Mb/s I2).  Snort runs
on a Netra T1 AC200 (500MHz Sparc IIe).  Snort connects to a remote db for
logging through a private vlan.

The db runs on a Quad PII450 Xeon.  We tried Mysql at first, but ran into
problems due to the database getting large ( > 2,000,000 alerts in less than
a month) which slowed down the inserts to mysql and as a consequence slowed
snort down as well.  We switched to PostgreSQL and while the size of the db
didn't seem to matter to snort any more (no performance hit of the sensor
doing inserts), the ACID frontend was ungodly slow (4 - 8 minutes minimum to
load any page).  Another problem that was encountered was that the portscan
alerts were never logged with the correct time, I had some that were showing
from the year 2041, and anytime between now and then.  Other alerts, while
less frequently having this happen, also showed up with incorrect
timestamps.  Both machines have their clocks synced to an NTP server every
night, so it wasn't that the time on the machine was off, but more likely
due to the differences between a Sun/Solaris and Intel/Linux machines.

We have since switched back to Mysql and I have the database rotated out
once a week to prevent it from growing too large.  Switching back also fixed
the timestamp issues, so I can only assume that the problem is with the db
output plugin and postgres.

All in all this has been a good program, now if upper management would allow
us to do something about the alerts we'd be all set.

More information about the Snort-users mailing list