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

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

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

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.

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

"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