[Author Prev][Author Next][Thread Prev][Thread Next][Author Index][Thread Index]
Re: [tor-bugs] #17488 [ExoneraTor]: ExoneraTor hangs forever on old known-positive test
#17488: ExoneraTor hangs forever on old known-positive test
------------------------+--------------------------
Reporter: starlight | Owner: karsten
Type: defect | Status: accepted
Priority: Very High | Milestone:
Component: ExoneraTor | Version:
Severity: Major | Resolution:
Keywords: | Actual Points:
Parent ID: | Points:
Sponsor: |
------------------------+--------------------------
Comment (by karsten):
I tried out something different: we include hex representations of /24's
(or /48's in case of IPv6) in the database, and I tried to query for those
instead of the exact IP address. And it seems that the database can
optimize those queries much better. 0.3 seconds instead of 54 seconds is
much more reasonable:
{{{
exonerator=> EXPLAIN ANALYZE
exonerator-> SELECT statusentry.rawstatusentry,
exonerator-> statusentry.descriptor,
exonerator-> statusentry.validafter,
exonerator-> statusentry.fingerprint,
exonerator-> HOST(statusentry.oraddress),
exonerator-> HOST(exitlistentry.exitaddress),
exonerator-> -- Pick only the last scan result that took place in
the 24 hours
exonerator-> -- before the valid-after time.
exonerator-> MAX(exitlistentry.scanned)
exonerator-> FROM statusentry
exonerator-> JOIN exitlistentry
exonerator-> ON statusentry.fingerprint = exitlistentry.fingerprint
exonerator-> WHERE exitlistentry.exitaddress24 = '627195'
-- ONLY CHANGE --
exonerator-> -- Focus on a time period from 1 day before and 1 day
after the
exonerator-> -- given date. Also include a second day before the
given date
exonerator-> -- for exit lists, because it can take up to 24 hours
to scan a
exonerator-> -- relay again. We shouldn't miss exit list entries
here.
exonerator-> AND DATE(exitlistentry.scanned) >= '2011-04-27'
exonerator-> AND DATE(exitlistentry.scanned) <= '2011-04-30'
exonerator-> AND DATE(statusentry.validafter) >= '2011-04-28'
exonerator-> AND DATE(statusentry.validafter) <= '2011-04-30'
exonerator-> -- Consider only exit list scans that took place in the
24 hours
exonerator-> -- before the relay was listed in a consensus.
exonerator-> AND statusentry.validafter >= exitlistentry.scanned
exonerator-> AND statusentry.validafter - exitlistentry.scanned <=
exonerator-> '1 day'::INTERVAL
exonerator-> GROUP BY 1, 2, 3, 4, 5, 6;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=11181.55..11181.56 rows=1 width=342) (actual
time=333.717..333.810 rows=57 loops=1)
-> Nested Loop (cost=0.01..11181.53 rows=1 width=342) (actual
time=0.513..333.262 rows=84 loops=1)
Join Filter: ((statusentry.validafter - exitlistentry.scanned) <=
'1 day'::interval)
-> Index Scan using exitlistentry_exitaddress24_scanneddate on
exitlistentry (cost=0.01..8.83 rows=1 width=56) (actual time=0.121..0.273
rows=4 loops=1)
Index Cond: ((exitaddress24 = '627195'::bpchar) AND
(date(scanned) >= '2011-04-27'::date) AND (date(scanned) <=
'2011-04-30'::date))
-> Index Scan using statusentry_fingerprint_validafter on
statusentry (cost=0.00..11172.68 rows=1 width=327) (actual
time=0.106..83.038 rows=60 loops=4)
Index Cond: ((fingerprint = exitlistentry.fingerprint) AND
(validafter >= exitlistentry.scanned))
Filter: ((date(validafter) >= '2011-04-28'::date) AND
(date(validafter) <= '2011-04-30'::date))
Total runtime: 334.174 ms
(9 rows)
}}}
I have an implementation here that contains necessary SQL changes and that
also converts IP addresses on the Java side, but I'll have to run some
more tests first. So far I'm optimistic.
--
Ticket URL: <https://trac.torproject.org/projects/tor/ticket/17488#comment:8>
Tor Bug Tracker & Wiki <https://trac.torproject.org/>
The Tor Project: anonymity online
_______________________________________________
tor-bugs mailing list
tor-bugs@xxxxxxxxxxxxxxxxxxxx
https://lists.torproject.org/cgi-bin/mailman/listinfo/tor-bugs