Re: Im having trouble with writing a patch to prewikka

I found a work around
Im not happy with it but it works and it does improve the speed of prewikka
using postgresql. The problem seem to be that there seems to be no way for
DbResultValues() in IDMEFDatabase.py to handle seaking through the rows but
a for loop though not the most efficent way to do it but it works please
find the attached revised patch
i will now create a ticket for the patch on the wiki



On 6/13/07, Paul Robert Marino <[email protected]> wrote:
>
> I am working on a patch to make the selection of the min and max time of
> an alers groupong faster in pgsql
> I came up with a way to do this a while ago but it had a negitive effect
> on the preformance on mysql
> I made a new version of my original patch which will not have any effect
> on mysql
>
> see the attached patch file
>
> now I have a new problem
>
>
> time_list = self.env.idmef_db.getValues(["alert.create_time/order_asc"],
> criteria2)
> time_min = time_list [0][0]
> time_max = time_list [0][-1]
>
> does not return the output i expect. in the past when i tested the it
> worked but now time_list [0][-1] returns null and furrther testing i found
> time_list [0][2] returns null and time_list [0][3] returns out of index even
> though i know the select returned more than 700 rows
>
> is this a bug or  was the way self.env.idmef_db.getValues() returns rows
> changed for reason
> if the change was intentional how can i access the rows now.
>
>
>
>
> Here are my benchmarks of a select made these where produced by taking the
> two versions of the selects one before my patch and one after from the
> postgresql log grabing the same information from the database. i wrote a
> simple script to bencmark the queries
> NOTE: I have obscures the IP addresses in the queies but they were
> identical during my test
>
> BEFORE
>
> pmarino@Zippy ~/prelude $ ./dbtest.sh "SELECT t0.time, t0.gmtoff, t0.usecFROM Prelude_Alert AS top_table LEFT JOIN Prelude_CreateTime AS t0 ON
> (t0._parent_type='A' AND t0._message_ident=top_table._ident) LEFT JOIN
> Prelude_Address AS t1 ON (t1._parent_type='S' AND
> t1._message_ident=top_table._ident AND t1._parent0_index = 0 AND t1._index =
> 0) LEFT JOIN Prelude_Address AS t2 ON (t2._parent_type='T' AND
> t2._message_ident=top_table._ident AND t2._parent0_index = 0 AND t2._index =
> 0) WHERE (( t1.address IS NULL AND t2.address = 'xxx.xxx.xxx.xxx') OR (
> t1.address = '' AND t2.address = 'xxx.xxx.xxx.xxx')) ORDER BY 1 ASC LIMIT
> 1"
>         time         | gmtoff |  usec
> ---------------------+--------+--------
>  2006-11-04 20:04:39 | -18000 | 963038
> (1 row)
>
>
> real    0m2.672s
> user    0m0.000s
> sys     0m0.044s
> pmarino@Zippy ~/prelude $ ./dbtest.sh "SELECT t0.time, t0.gmtoff, t0.usecFROM Prelude_Alert AS top_table LEFT JOIN Prelude_CreateTime AS t0 ON
> (t0._parent_type='A' AND t0._message_ident=top_table._ident) LEFT JOIN
> Prelude_Address AS t1 ON (t1._parent_type='S' AND
> t1._message_ident=top_table._ident AND t1._parent0_index = 0 AND t1._index =
> 0) LEFT JOIN Prelude_Address AS t2 ON (t2._parent_type='T' AND
> t2._message_ident=top_table._ident AND t2._parent0_index = 0 AND t2._index =
> 0) WHERE (( t1.address IS NULL AND t2.address = 'xxx.xxx.xxx.xxx') OR (
> t1.address = '' AND t2.address = 'xxx.xxx.xxx.xxx')) ORDER BY 1 DESC LIMIT
> 1"
>         time         | gmtoff |  usec
> ---------------------+--------+--------
>  2007-01-30 23:21:09 | -18000 | 238353
> (1 row)
>
> real    0m2.516s
> user    0m0.012s
> sys     0m0.040s
>
>
>
>
> AFTER
> NOTE: Ive removed some of the output in the middle because only the first
> and last row are important to the results
>
> pmarino@Zippy ~/prelude $ ./dbtest.sh "SELECT t0.time, t0.gmtoff, t0.usecFROM Prelude_Alert AS top_table LEFT JOIN Prelude_CreateTime AS t0 ON
> (t0._parent_type='A' AND t0._message_ident=top_table._ident) LEFT JOIN
> Prelude_Address AS t1 ON (t1._parent_type='S' AND
> t1._message_ident=top_table._ident AND t1._parent0_index = 0 AND t1._index =
> 0) LEFT JOIN Prelude_Address AS t2 ON (t2._parent_type='T' AND
> t2._message_ident=top_table._ident AND t2._parent0_index = 0 AND t2._index =
> 0) WHERE (( t1.address IS NULL AND t2.address = 'xxx.xxx.xxx.xxx') OR (
> t1.address = '' AND t2.address = 'xxx.xxx.xxx.xxx')) ORDER BY 1 ASC"
>         time         | gmtoff |  usec
> ---------------------+--------+--------
>  2006-11-04 20:04:39 | -18000 | 963038
>  2006-11-04 20:04:39 | -18000 | 976354
>  2006-11-04 20:05:42 | -18000 | 855951
>  2006-11-04 20:05:42 | -18000 | 845850
>  2006-11-04 20:06:55 | -18000 | 181885
> ......
>  2007-01-30 23:21:07 | -18000 | 404495
>  2007-01-30 23:21:08 | -18000 | 405161
>  2007-01-30 23:21:09 | -18000 | 241936
>  2007-01-30 23:21:09 | -18000 | 238353
> (768 rows)
>
>
> real    0m2.445s
> user    0m0.020s
> sys     0m0.028s
>
>
>
> the results are fairly clear if you look at the real time on the two
> queries in the before and the one query in the after
> the net result is a savings of aproximitly 2.4 seconds per grouping with
> 50 grouping on the screen this results in a saving of 120 seconds
>
>
>
>
>
>
>
>

_______________________________________________
Prelude-devel site list
[email protected]
http://www.prelude-ids.org/mailman/listinfo/prelude-devel