Opened 8 years ago

Closed 8 years ago

#4673 closed enhancement (fixed)

Partition the 67,000,000 row statusentry table in the metrics database

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

Description

Thomas Termin suggested splitting the huge statusentry table in the metrics database into multiple tables to solve some of our metrics website/database performance problems. Tim Wilde chimed in saying they're already doing that for large tables down to the hour level.

There was some more discussion about splitting the whole table covering 3+ years of data into 36+ month tables and adding a new month table every month. Another suggestion was to move old data into a history table of some kind using a cron-job-like stored procedure. I later saw Tim explain something about using a year table, month tables, etc. down to hour tables and deciding in the application which table(s) to query. Basically, there was some discussion whether to do the splitting and merging in the database or in the application.

A good next step would be to look at the PostgreSQL documentation for partitioning tables. I'm also going to look more at the schema and Java code to find the places which are affected by such a change.

Child Tickets

Attachments (2)

task4673-migration.txt (4.1 KB) - added by karsten 8 years ago.
Migration notes for partitioning the statusentry table
GenerateQueries.java (4.4 KB) - added by karsten 8 years ago.
Java script to generate performance test queries

Download all attachments as: .zip

Change History (4)

comment:1 Changed 8 years ago by karsten

Sebastian and I worked on partitioning the statusentry table on Sebastian's laptop and external hard drive. The changes to the SQL schema is in the statusentry branch of my public repository. The migration notes are attached to this ticket.

The next step will be to finish the migration on Sebastian's laptop and do a lot more performance testing on it. And then we do the same thing on yatei!

Changed 8 years ago by karsten

Attachment: task4673-migration.txt added

Migration notes for partitioning the statusentry table

comment:2 Changed 8 years ago by Sebastian

Resolution: fixed
Status: newclosed

We did it, woot. So far everything looks much improved :)

Changed 8 years ago by karsten

Attachment: GenerateQueries.java added

Java script to generate performance test queries

Note: See TracTickets for help on using tickets.