Обсуждение: Any tips for this particular performance problem?
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
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
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
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 managementsystem, 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