Re: [Prelude Hybrid IDS] #225: PostgreSQL base schema is (really) slow

I took a look at your sql scripts and tried them
I didn't notice a very significant change in the speed however i tested
against postgresql 8.2 not 7.4 which is the most commonly used version, and
there were a lot of enhancements in 8.x which could possibly account for my
results. here are a couple of comments about this change.

1) The change also needs to be made to the sed script which converts the
MySQL schema to the postgresql schema.

2) in most of the versions of postgresql the default postgresql.conf has
stats collection turned off so it runs more efficiently on systems with few
resources to spare. Ive found that enabling stats collection and increasing
the default stats target to the maximum of 1000 as opposed to 10 makes a
much bigger difference. also if you are using 8.x turning on auto vacuuming
helps as well.


3) the schema is not the big performance issue it fundamental differences in
ideologies between mysql and postgresql. This is the major source of flame
wars between mysql users and postgresql users they always claim the one they
use is faster and the truth of it is they are both right just on different
types of queries. mysql takes the stance that it is a data storage system
that returns tokens of data to an application which is responsible for
generating reports if necessary so it is optimized to process a lot of
queries that process a relatively small number of rows (by small i mean
preferably less than 10000 rows processed on the back end per query ) to
return the result. postgresql takes the approach that its the databases job
to generate the report and the application just reformats it for
presentation or  processing. both methods are valid and useful for their own
market however they require very different sql queries to produce the same
result in an optimal fastion namely mysql needs hundreds of short running
queries where postgresql needs less than 50 bigger queries.

There are three possible ways in which this could be handled

1) a change to libpreludedb and prewikka
add a new functions to libpreludedb to generate a reports of specific IDMEF
criteria including the grouping and any other relevant information for
generating desired results. depending on options passed in the configuration
file libpreludedb would decide whether using many small queries of a few big
queries is the better approach for querying the result and return a complete
report to prewikka to display. one good example is how min and max times are
queried MySQL does better with two queries one sorted ascending  limit 1 and
the other sorted descending limit 1 on the alert.createtime
postgresql however does much better returning the same query sorted by
ascending or descending with no limit and then letting the program grab the
first and last row from the results.

mysql returns the two queries quickly but doing it as one big query takes
way too long to even attempt on a well used database.
So the two query method is the only way to go for mysql.

postgresql takes the same amount of time per query whether you do the two
queries or one query.
So doing just the one query boosts the performance for postgresql users by
nearly 50%.






2) create a query engine daemon
a query engine daemon would have several advantages it could be made in such
a way that it could allow rapid prototyping of new interfaces, pool similar
or identical queries in order to reduce the amount of overhead on the
database, cache a users previous query so it can return updates based on
time differentials which would be useful for Ajax updates in the future.
using this method the daemon could decide how best to do the query

3) mixing the first two methods






On 5/16/07, Prelude Hybrid IDS <[email protected]> wrote:
>
> #225: PostgreSQL base schema is (really) slow
>
> -------------------------------------------------+--------------------------
> Reporter:  Pierre Chifflier <[email protected]>  |       Owner:  yoann
>      Type:  defect                               |      Status:  new
> Priority:  normal                               |   Milestone:
> Component:  libpreludedb                         |     Version:  0.9
> Severity:  normal                               |    Keywords:
>
> -------------------------------------------------+--------------------------
> The PostgreSQL schema definitly needs some love, performances are really
> bad.
>
> Some identified problems:
>   * type conversions preventing postgresql to use indexes (numeric(20),
> for
> example)
>   * some missing indexes
>
> --
> Ticket URL: <https://trac.prelude-ids.org/ticket/225>
> Prelude Hybrid IDS <http://www.prelude-ids.org>
> The Prelude Hybrid Intrusion Detection System suite
> _______________________________________________
> Prelude-devel site list
> [email protected]
> http://www.prelude-ids.org/mailman/listinfo/prelude-devel
>
_______________________________________________
Prelude-devel site list
[email protected]
http://www.prelude-ids.org/mailman/listinfo/prelude-devel