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