Opened 8 years ago

Closed 8 years ago

#2922 closed enhancement (implemented)

Improve searching for relays in metrics database

Reported by: karsten Owned by: karsten
Priority: Medium Milestone:
Component: Metrics/Website Version:
Severity: Keywords:
Cc: Actual Points:
Parent ID: Points:
Reviewer: Sponsor:

Description

Our 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 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.

Child Tickets

Change History (3)

comment:1 Changed 8 years ago by karsten

Component: MetricsMetrics Website

comment:2 Changed 8 years ago by karsten

See #3836 for a recent example of this problem.

comment:3 Changed 8 years ago by karsten

Resolution: implemented
Status: newclosed

Looks like we have this under control with Postgres' table partitioning feature. Closing.

Note: See TracTickets for help on using tickets.