RE: ACID - Which Database?

["Hutchinson, Andrew" <Andrew.Hutchinson () Vanderbilt edu>]

As a general rule MySQL is faster for simple SELECTs.  However, in my
experience, once your queries start to get more complex (JOINing 5+
tables, etc.), the fact that PostgreSQL has a much more capable
optimizer results in faster query response times.

I started using MySQL a couple of years ago, and it worked very well as
a snort backend, since most of the queries I was issuing were very
simple and I wasn't trying to extend the capabilities of the Snort
Frontends that I was using.  Then, however, I began putting together a
custom frontend to the SnortDB, and found that MySQL was lacking some
features that I felt were very necessary.  In particular, MySQL did not
have built in support for transactions, triggers, rules, or foreign key
constraints.  Additionally, there was no support for stored procedures,
which was a big downer.  I also found that if I had multiple people
using my app and issuing queries, the huge performance advantage of
MySQL went down the tubes due to the lack of row level locking.  This
became a big problem when I had > 12 very active snort sensors logging
events to the database simultaneously, and users querying the db at the
same time.  Because of those reasons, I switched to PostgreSQL, and I
consider it a very good move.

That being said, here is my $.02 on which db to use:

If you're in an environment with a few (<5) light-medium loaded sensors,
and want to run ACID or something like that w/o making extentions and/or
modifications, and you're only going to have 1 or 2 people hitting the
database, I'd use MySQL with the MyISAM engine.  It'll be faster.

If you have more sensors, more users, or need more advanced database
features (transactions, stored proc's, triggers, fk constraints, etc.),
I would use PostgreSQL.

I understand that MySQL now has support for transactions and fk
constraints if you use the InnoDB engine.  However, my testing shows
that when you switch from MyISAM (which is lightweight but blazingly
fast) to InnoDB (which has features like transactions, row-level
locking, etc.), your performance becomes very PostgreSQL-like, but you
still can't have stored procs, triggers, etc...  The main reason why
PostgreSQL is slower is because _everything_ is wrapped in a transaction
- even a simple SELECT statement is wrapped in an implied BEGIN...END
transaction wrapper.  You take a performnce hit for this, but if you've
ever had a machine crap out while you're halfway through an operation
that modifies 500,000 rows, you'll understand why transactions are a
good thing.  :-)

Hope this helps,

Andrew

Andrew Hutchinson
Vanderbilt University Medical Center
Informatics / NCS / Network Security
(615) 936-2856

-----Original Message-----
From: Paul B. Poh [mailto:paul () paulpoh com] 
Sent: Tuesday, February 11, 2003 10:46 AM
To: Yaakov Yehudi
Cc: snort-users () lists sourceforge net
Subject: Re: [Snort-users] ACID - Which Database?


Maybe it's me being confused. But according to my interpretation of the 
charts doesn't the lower time mean MySQL is faster?

Yaakov Yehudi wrote:

> I use the PostgreSQL database with ACID.  According to some tests,
> http://www.andrew.cmu.edu/~rdanyliw/snort/perf/acid_perf.html,  it 
> seems that PostgreSQL is significantly faster than MySQL.
>
> Observing the messages in this group, it seems to me that most people
> running ACID are using MySQL.  Why?  Does MySQL have some other 
> compelling feature which influences people to use it?
>
> Best Regards, Yaakov



-------------------------------------------------------
This SF.NET email is sponsored by:
SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See!
http://www.vasoftware.com
_______________________________________________
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:
SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See!
http://www.vasoftware.com
_______________________________________________
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