[tor-bugs] #17488 [ExoneraTor]: ExoneraTor hangs forever on old known-positive test

[Tor Bug Tracker & Wiki]

#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