#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
_______________________________________________ tor-bugs mailing list tor-bugs@lists.torproject.org https://lists.torproject.org/cgi-bin/mailman/listinfo/tor-bugs