432450 - Design and deploy new partitioning scheme and associated scripts

Status: RESOLVED FIXED

(Socorro :: General, task)

Description

[Michael Morgan [:morgamic]]

For housecleaning and automation, we need to write a script that:
* detects partition size and automatically creates a new partition when the reports partition reaches a reasonable size -- like 250000 or 500000 records or 15gb -- whichever comes first (something like that, it'll change based on actual results)
* deletes reports that are older than 3 months or at least creates a constraint to exclude old partitions from all queries
* adds new date constraints on old partitions

Comment 3

[K Lars Lohn [:lars] [:klohn]]

The new partition scheme makes new partitions weekly.  Inserts redirect automatically to the appropriate partitions of the reports, dumps, frames (and extensions) tables.

There's a script to by run by cron weekly to create the new partitions.

Just waiting for a testing environment...

Comment 4

[K Lars Lohn [:lars] [:klohn]]

targeting this for the WebDev on site Nov 11-15

Comment 5

[Michael Morgan [:morgamic]]

Update - we're staging Wednesday Dec 3, pushing within next couple of weeks.

Comment 7

[Marc Bejarano]

morgamic: status?

Comment 8

[K Lars Lohn [:lars] [:klohn]]

this merged to trunk today in preparation for staging and deployment - expect deployment within a few days

Comment 9

[K Lars Lohn [:lars] [:klohn]]

finally completed after several false starts and rollbacks.  

The database did not have the capacity to run the algorithm that took the current data and repartition it into smaller chunks.  Tests on smaller databases indicated that scaling would happen with O(n).  Reality on the full dataset showed a non linear scaling that did not work in our favor.

This was finally finished using a "from this point forward" scheme.  The old data was installed as a giant partition.  All new data arriving as of Monday, January 9 started going into new weekly partitions.  The full benefits of partitioning will not be felt for several weeks as the new partitions fill and the old giant partition ages into irrelevancy.