[tor-bugs] #27356 [Metrics/ExoneraTor]: Reduce database size and variance of query response times

[Tor Bug Tracker & Wiki]

#27356: Reduce database size and variance of query response times
--------------------------------+------------------------------
 Reporter:  karsten             |          Owner:  metrics-team
     Type:  enhancement         |         Status:  needs_review
 Priority:  Medium              |      Milestone:
Component:  Metrics/ExoneraTor  |        Version:
 Severity:  Normal              |     Resolution:
 Keywords:                      |  Actual Points:
Parent ID:                      |         Points:
 Reviewer:                      |        Sponsor:
--------------------------------+------------------------------

Comment (by karsten):

 So, while testing, I found that the query for the first loop takes almost
 forever. I aborted it after 1.5 days and ran `EXPLAIN ANALYZE` on it:

 {{{
 exonerator=> EXPLAIN ANALYZE SELECT * FROM statusentry ORDER BY
 fingerprint, validafter;
                                                                  QUERY
 PLAN
 --------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=330924653.28..331942841.44 rows=407275264 width=335) (actual
 time=150799260.447..160427618.123 rows=407470751 loops=1)
    Sort Key: fingerprint, validafter
    Sort Method: external merge  Disk: 132839640kB
    ->  Seq Scan on statusentry  (cost=0.00..22111634.64 rows=407275264
 width=335) (actual time=120.830..4751991.603 rows=407470751 loops=1)
  Planning time: 37.125 ms
  Execution time: 160482588.895 ms
 (6 rows)
 }}}

 Admittedly, this looks okay. Just to be clear, that's over 1 day and 20
 hours before we even start copying the first row!

 I tweaked the logging to make that clearer. I also made another trivial
 code style change that shouldn't have any effect. Please find commits
 [https://gitweb.torproject.org/user/karsten/exonerator.git/commit/?h=task-27356&id=2cb74cd9bb6858b5442442f70667cdf5db75013f
 2cb74cd] and
 [https://gitweb.torproject.org/user/karsten/exonerator.git/commit/?h=task-27356&id=deec83da277e5921977a17dd38262f7f91d93e7d
 deec83d] in the same branch mentioned above.

 I'll hold off running more tests until the SSD that I ordered yesterday
 arrives. I figured that if the query alone takes almost 2 days, that the
 rest of the migration might take many more days or even weeks. Might have
 been a bit optimistic that an HDD would do the job. Hopefully that will be
 tomorrow, so I might start another test run this weekend or early next
 week at the latest.

 Still in need for review. :)

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