[tor-bugs] #2922 [Metrics]: Improve searching for relays in metrics database

[Tor Bug Tracker & Wiki]

#2922: Improve searching for relays in metrics database
-------------------------+--------------------------------------------------
 Reporter:  karsten      |          Owner:  karsten
     Type:  enhancement  |         Status:  new    
 Priority:  normal       |      Milestone:         
Component:  Metrics      |        Version:         
 Keywords:               |         Parent:         
   Points:               |   Actualpoints:         
-------------------------+--------------------------------------------------
 Our [https://metrics.torproject.org/relay-search.html relay search]
 function on the metrics website has serious performance problems.  Some
 searches return after under a second, but some searches take 2 minutes or
 longer.  It's okay for a search to take a few seconds, but there shouldn't
 be a variance this high.

 All searches are based on a single (very large) table that contains one
 row per relay listed in a network status consensus.  Our current
 assumption why searches are slow is that indexes have grown too large.

 Sebastian and I tried to create separate tables for the fields that users
 can search for, which looked promising.  But after one of the steps to
 populate these helper tables did not finish after five days, we gave up.

 Someone should brainstorm about redesigning our
 [https://gitweb.torproject.org/metrics-web.git/blob/HEAD:/db/tordir.sql
 database schema] and try out a couple of approaches to search for relays
 with a couple months of data.  Once it turns out that one approach is
 better than the current one, we also need a migration strategy to convert
 our database to the new schema.

-- 
Ticket URL: <https://trac.torproject.org/projects/tor/ticket/2922>
Tor Bug Tracker & Wiki <https://trac.torproject.org/>
The Tor Project: anonymity online