1219417 - Socorro Postgres in need of maintenance

Status: RESOLVED INVALID

(Socorro :: Database, task)

Description

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

Attachment: PG largest tables

the Socorro production PG Database needs maintenance, is VACUUM even being run periodically?

attached are the largest tables in our database.  the heaviest is 55GB in size,  The raw_adi table is 15GB along with the raw_adi_log.  the associated index is 8GB.  

eventually this will bring PG down...

Comment 1

[Peter Bengtsson [:peterbe]]

Here's the same table in Gb numbers: https://gist.github.com/peterbe/9d7ded613578a461b1cc

* The VACUUM periodic job is usually run by autovacuum. My personal ubuntu server doesn't have anything in /etc/cron.* but I know the autovacuum runs every now and then because I can see it in /var/log/postgresql/*.log

* The raw_adi table is being read from by that graphics team and from the stored procedure that generates product_adu. The raw_adi_logs table should most definitely be possible to clean up. Like always. 

* What the heck is pg_toast_6335168 ??!

* The exploitability_reports has curious. We have, on stage, 45m rows in there but in the report (https://crash-stats.mozilla.com/report/exploitability/products/Firefox) it ALWAYS just filter on the last 7 days! Meaning that we only ever look at about the last 100,000 rows! Also, perhaps this is best done in a single SuperSearch query. Adrian?

Comment 2

[Peter Bengtsson [:peterbe]]

Lars, can you sum all the signature summary tables too please? I suspect, that together they weigh a lot too. And the webapp never looks at older than 28 days worth of data.

Comment 3

[[DEACTIVATED] Adrian Gaudebert]

It seems easy to rewrite the exploitability report using Super Search's newest features. Here's an API call that gives the essential data (permissions needed): https://crash-stats.mozilla.com/api/SuperSearchUnredacted/?_results_number=0&_aggs.signature=exploitability

Also, TCBS might become useless in a few, since we have built a new version using Super Search.

Comment 4

[Peter Bengtsson [:peterbe]]

Attachment: pg_sizes.txt

Here's an interesting analysis too. It sums together tables across partitions. Basically, it sums signature_summary_device_20130729 and signature_summary_device_20130730 etc together all under signature_summary_device as one. 

What it means is that ALL the signature summaries weigh 35Gb together!

Comment 5

[Peter Bengtsson [:peterbe]]

We're changing direction entirely. With the "Deprecation Rampage" effort we're trying to vastly reduce our dependency on Postgres for crashes and other accumulating data. We're still going to use Postgres but only for storing much smaller and more manageable datum like product versions, django auth users, etc.

Comment 6

[Peter Bengtsson [:peterbe]]

Attachment: pg_size_2017-09-06.txt

Here's the latest after having recently deleted all the signature_summary_* tables. Clearly, still a lot of work to do.