RE: Database performance question (MySQL or PostgreSQL?)

[JP Vossen <vossenjp () netaxs com>]

> Subject: RE: [Snort-users] Database performance question (MySQL or PostgreSQL?)
> Date: Fri, 26 Sep 2003 10:24:20 -0500
> From: "Kreimendahl, Chad J" <Chad.Kreimendahl () umb com>
> To: "Jyri Hovila" <jyri.hovila () iki fi>, <snort-users () lists sourceforge net>
>
> 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.

<snip>

> -----Original Message-----
> From: Jyri Hovila [mailto:jyri.hovila () iki fi]=20
> Sent: Friday, September 26, 2003 2:24 AM
> To: snort-users () lists sourceforge net
> Subject: [Snort-users] Database performance question (MySQL or
> PostgreSQL?)
>
> Please let's not let this turn this into SQL wars. =3D)
>
> 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 don't have an answer, but I can add some data points.  I have the following:

Dell PE 500SC, CERC IDE RAID5 (Not very speedy)
Memory = 512M
CPU = Pentium(R) III CPU family 1133MHz
Purpose: general purpose "services", Samba, and all kinds of Other Stuff
MySQL: ACID, FW logs

RH 8 running mysql-server-3.23.56-1.80 on httpd-2.0.40-11.7 with
php-4.2.2-8.0.8, adodb-290, jpgraph-1.10 and ACID v0.9.6b23.

1,744,830 events in DB (honeypot), main page takes about 150 seconds to load,
while adding ~100-200 "alert(s) to the Alert Cache." (So "auto-updating of the
event cache" is on.)  DB is about 837M on disk.

Optimizing all tables makes no difference (I don't delete events), and I just
implemented a slightly modified version of
/usr/share/doc/mysql-server-3.23.56/my-large.cnf which seems to have made no
difference either.

I tried switching from persistant database connection to standard with no
result (see the db_connect_method varible in acid_conf.php).

But from what I've read on the 'Net, MySQL is prety speedy.  As far as I can
tell, ACID just does a bunch of expensive queries (e.g. full table scans on
joins as mentioned above).  I'm not qualified to look at the code to see if
the queries are well written and optimised or not...

There were also some notes about index creation a year or two ago.  As far as
I can tell, those indexes are present in the latest create script (v0.9.6b23).

FWIW,
JP

PS--In the time it took me to write this, I added 660 events.
------------------------------|:::======|--------------------------------
JP Vossen, CISSP              |:::======|         jp{at}jpsdomain{dot}org
My Account, My Opinions       |=========|       http://www.jpsdomain.org/
------------------------------|=========|--------------------------------
You used to have to reboot the Windows 9.x series every couple of days
because it would crash.  Now you have to reboot Windows 200x or XP every
couple of days because of a patch.  How is that better or more stable?




-------------------------------------------------------
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