If you have time, take a quick look at

http://acidlab.sourceforge.net/perf/acid_perf.html

PostgreSQL has serious scalability problems with snort + acid. Any
advices?

(Now I'm using MySQL with my SNORT/ACID setup, but I'm willing to
change to PostgreSQL if more tests are needed)

Sergio Bruder

-- 
Coordenaï¿½ï¿½o de Desenvolvimento - Projetos Especiais, Conectiva
http://www.conectiva.com.br, http://sergio.bruder.net, http://pontobr.org
-----------------------------------------------------------------------------
pub  1024D/0C7D9F49 2000-05-26 Sergio Devojno Bruder <bruder@conectiva.com.br>
     Key fingerprint = 983F DBDF FB53 FE55 87DF  71CA 6B01 5E44 0C7D 9F49
sub  1024g/138DF93D 2000-05-26

Tue, 10 Jul 2001 09:40:21 -0300
Sergio Bruder <bruder@conectiva.com.br>
Any tips for this particular performance problem?
On Tue, Jul 10, 2001 at 04:04:43PM +0200, Hannu Krosing wrote:
> Sergio Bruder wrote:
> > 
> > If you have time, take a quick look at
> > 
> > http://acidlab.sourceforge.net/perf/acid_perf.html
> > 
> > PostgreSQL has serious scalability problems with snort + acid. Any
> > advices?
> 
> Usually porting from MySQL to PostgreSQL needs some rewrite of 
> queries and process logic if good performance is required.

ACID is using ADODB SQL interface, thus using the same queries for
MySQL and PostgreSQL.

> 
> > (Now I'm using MySQL with my SNORT/ACID setup, but I'm willing to
> > change to PostgreSQL if more tests are needed)
> 
> Are these tests run using BSDDB backend (for transaction support) or 
> the old/fast MySQL storage ?

Dunno (These tests arent executed by me), but probably using the
old transaction-less format of MySQL.

Sergio Bruder

-- 
Coordenaï¿½ï¿½o de Desenvolvimento - Projetos Especiais, Conectiva
http://www.conectiva.com.br, http://sergio.bruder.net, http://pontobr.org
-----------------------------------------------------------------------------
pub  1024D/0C7D9F49 2000-05-26 Sergio Devojno Bruder <bruder@conectiva.com.br>
     Key fingerprint = 983F DBDF FB53 FE55 87DF  71CA 6B01 5E44 0C7D 9F49
sub  1024g/138DF93D 2000-05-26

Tue, 10 Jul 2001 11:01:59 -0300
Sergio Bruder <bruder@conectiva.com.br>
Re: Any tips for this particular performance problem?
Sergio Bruder wrote:
> 
> If you have time, take a quick look at
> 
> http://acidlab.sourceforge.net/perf/acid_perf.html
> 
> PostgreSQL has serious scalability problems with snort + acid. Any
> advices?

Usually porting from MySQL to PostgreSQL needs some rewrite of 
queries and process logic if good performance is required.

> (Now I'm using MySQL with my SNORT/ACID setup, but I'm willing to
> change to PostgreSQL if more tests are needed)

Are these tests run using BSDDB backend (for transaction support) or 
the old/fast MySQL storage ?

-----------------
Hannu

Tue, 10 Jul 2001 16:04:43 +0200
Hannu Krosing <hannu@tm.ee>
Re: Any tips for this particular performance problem?
Hannu Krosing wrote:
> Sergio Bruder wrote:
> >
> > If you have time, take a quick look at
> >
> > http://acidlab.sourceforge.net/perf/acid_perf.html
> >
> > PostgreSQL has serious scalability problems with snort + acid. Any
> > advices?
>
> Usually porting from MySQL to PostgreSQL needs some rewrite of
> queries and process logic if good performance is required.
>
> > (Now I'm using MySQL with my SNORT/ACID setup, but I'm willing to
> > change to PostgreSQL if more tests are needed)
>
> Are these tests run using BSDDB backend (for transaction support) or
> the old/fast MySQL storage ?

    I'm  not  familiar  with SNORT/ACID, but I assume that the DB
    schema and queries are basically all identically for all  the
    databases.

    Well,  how  such analyzis software can be implemented without
    views and stored procedures is a mystery to me, but at  least
    it must do a whole lot of PHP-aerobics.

    I'd  say  as  long  as  the database is used as a stupid data
    container and not as a relational database management system,
    just  don't use Postgres.  It's not designed to be stupid, so
    it doesn't work well if used stupid.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Tue, 10 Jul 2001 10:54:54 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Any tips for this particular performance problem?
On Tue, 10 Jul 2001, Sergio Bruder wrote:

> If you have time, take a quick look at
> 
> http://acidlab.sourceforge.net/perf/acid_perf.html
> 
> PostgreSQL has serious scalability problems with snort + acid. Any
> advices?
> 
> (Now I'm using MySQL with my SNORT/ACID setup, but I'm willing to
> change to PostgreSQL if more tests are needed)

It might be handy to see schema and query examples for the system.
There may be obvious things in the queries such that we'll at least
be able to tell you why things seem to be slow.



Tue, 10 Jul 2001 09:46:07 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Any tips for this particular performance problem?
On Tue, Jul 10, 2001 at 09:46:07AM -0700, Stephan Szabo wrote:
> On Tue, 10 Jul 2001, Sergio Bruder wrote:
> 
> > If you have time, take a quick look at
> > 
> > http://acidlab.sourceforge.net/perf/acid_perf.html
> > 
> > PostgreSQL has serious scalability problems with snort + acid. Any
> > advices?
> > 
> > (Now I'm using MySQL with my SNORT/ACID setup, but I'm willing to
> > change to PostgreSQL if more tests are needed)
> 
> It might be handy to see schema and query examples for the system.
> There may be obvious things in the queries such that we'll at least
> be able to tell you why things seem to be slow.


The web page says:

Host: Intel Mobile 800Mhz, 256 MB RAM
OS: Linux 2.2.16-22
Apache: 1.3.19
PHP: 4.0.5
MySQL: 3.23.32 (MyISAM tables, Unix socket)
PostgreSQL: 7.1.2 (Unix socket, fsync disabled, vacuum analyzed between runs)
DB schema: v102 (indexed as per create_mysql/postgresl in Snort v1.8b5 build 24)
ACID: 0.9.6b10 - 0.9.6b13

All I can find online are v. 1.7 and 1.8-RELEASE. In 1.7, the mysql script
has a lot more indices than the postgresql one. In the 1.8-RELEASE,
they both seem to have the same set. If those indices went in between
b5 and release, there's your problem!

Hmm, I've pulled the appropriate file from CVS, now. Seems that v102
has most the indices, so Stephan's request of example queries is the only
way we're going to be able to help.

Hmm, on third look, I've grovelled through the PHP for ACID 0.9.6b11
(since that was in the snort CVS) and I see that ACID creates some tables,
as well, one of which is missing an index that MySQL gets:

MySQL:

CREATE TABLE acid_ag_alert( ag_id               INT           UNSIGNED NOT NULL,
                            ag_sid              INT           UNSIGNED NOT NULL,
                            ag_cid              INT           UNSIGNED NOT NULL, 

                            PRIMARY KEY         (ag_id, ag_sid, ag_cid),
                            INDEX               (ag_id),
                            INDEX               (ag_sid),
                            INDEX               (ag_cid),
                            INDEX               (ag_sid, ag_cid));

Pgsql:

CREATE TABLE acid_ag_alert( ag_id               INT8 NOT NULL,
                            ag_sid              INT4 NOT NULL,
                            ag_cid              INT8 NOT NULL, 

                            PRIMARY KEY         (ag_id, ag_sid, ag_cid) );

CREATE INDEX acid_ag_alert_id_idx ON acid_ag_alert (ag_sid, ag_cid);


This isn't as extreme as it looks, since pgsql knows how to use the
multi-key indices in place of some of the single key indices the MySQL
table has, so the only one completely missing, from the pgsql point of
view, is an index on ag_cid alone. From grepping the PHP sources, it
seems that this this a common join key, so missing that index might hurt.

If ag_id is used a lot, having only a triplekey isn't the best, since
the index entries will be much larger, so fewer will fit in a page.

As Stephan said, the only way to know for sure what's happening is to see the
actual queries (and explains on them for the actual test dataset). Turn
on logging, and grab the queries from the postgresql logs, seems the
way to go.

Ross

Tue, 10 Jul 2001 17:40:43 -0500
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Any tips for this particular performance problem?
"Ross J. Reedstrom" wrote:
> 
> Hmm, I've pulled the appropriate file from CVS, now. Seems that v102
> has most the indices, so Stephan's request of example queries is the only
> way we're going to be able to help.
> 
> Hmm, on third look, I've grovelled through the PHP for ACID 0.9.6b11
> (since that was in the snort CVS) and I see that ACID creates some tables,
> as well, one of which is missing an index that MySQL gets:

Also, do they run VACUUM ANALYZE after filling the table ?

PostgreSQL could choose very poor plans without it.

-------------
Hannu

Wed, 11 Jul 2001 14:11:26 +0200
Hannu Krosing <hannu@tm.ee>
Re: Any tips for this particular performance problem?