XML

Word

Printable

Details

Type: Improvement
Status: Resolved
Priority: Normal
Resolution: Fixed
Affects Version/s: PDB 4.0.0
Fix Version/s: PDB 6.8.0
Component/s: PuppetDB
Labels:

Epic Link:
Puppet 5 PDB Grab Bag
Team:
PuppetDB

CS Priority:
Reviewed
CS Frequency:
2 - 5-25% of Customers
CS Severity:
3 - Serious
CS Business Value:
4 - $$$$$
CS Impact:

Hide
This impacts mostly large customers or those which run everything in noop. This inflates the size of the table in PDB which can use up disk space and grow the table to a size where the queries from event inspector no longer complete.

There is a terrible workaround using a cron job with a delete statement directly against this table.

Show
This impacts mostly large customers or those which run everything in noop. This inflates the size of the table in PDB which can use up disk space and grow the table to a size where the queries from event inspector no longer complete. There is a terrible workaround using a cron job with a delete statement directly against this table.
Zendesk Ticket IDs:
32574,35133
Zendesk Ticket Count:
2

Release Notes:
Enhancement
Release Notes Summary:

Hide
A configuration parameter, resource-events-ttl, has been added. This parameter rounds up to the nearest day (i.e. 14h rounds up to 1d)

When the TTL is expired, the table containing that day's events is dropped so that there is no need to vacuum the resource_events table.

Show
A configuration parameter, resource-events-ttl, has been added. This parameter rounds up to the nearest day (i.e. 14h rounds up to 1d) When the TTL is expired, the table containing that day's events is dropped so that there is no need to vacuum the resource_events table.

Description

Currently we store report-ttl days of events in the resource_events table in the puppetdb database.

Some customers have performance issues using the API endpoints that read from resource_events that could be mitigated or resolved by reducing the number of days of events that are stored in that table.

In cases where customers would like to store more reports like 30-60 days of reports they may not desire storing that many days of events as events tend to be more useful for watching things that changed recently.

I would be good to have an option to seperate these concerns and allow customers to tune in production for their own preferences.

Side note: I'm not tied to the name "resource-events-ttl" I just figured people would know what I meant by reading that.

Delete Query:

DELETE FROM resource_events

WHERE timestamp < NOW() - INTERVAL '1 days';

Bash code:

echo "DELETE FROM resource_events WHERE timestamp < NOW() - INTERVAL '1 days';" > /tmp/delete_resource_events.sql

su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -f /tmp/delete_resource_events.sql"

Another thought

If implemented would resource-events-ttl have it's own GC API command or would it just fall under report-ttl? It could probably just be under the report-ttl but should run before the delete from reports does.

Attachments

Issue Links

relates to

PDB-4420 SPIKE: Partitioning of tables

Resolved

PDB-4466 Partitioning of resource_events: GC by dropping expired partitions

Resolved

PDB-4467 Partitioning of resource_events: Migrate existing events to partitioned tables

Resolved

PDB-4468 Partitioning of resource_events: Migration test for initial creation of partitions

Resolved

PDB-2477 Refactor GC processes to avoid cascades

Closed

PDB-4420 SPIKE: Partitioning of tables

Resolved

PDB-4466 Partitioning of resource_events: GC by dropping expired partitions

Resolved

PDB-4467 Partitioning of resource_events: Migrate existing events to partitioned tables

Resolved

PDB-4468 Partitioning of resource_events: Migration test for initial creation of partitions

Resolved

PDB-4280 Ensure PE upgrade will work properly with reports migration

Closed

PDB-3635 Add ability to disable storage of resource events

Resolved

PUP-7454 The schedule metaparameter should allow for suppressing skip events

Closed

(7 relates to)

Activity

People

Assignee:: Robert Roland

Reporter:: Nick Walker

Votes:: 7 Vote for this issue

Watchers:: 30 Start watching this issue

Dates

Created:: 2016/03/01 9:12 AM

Updated:: 2022/01/17 12:48 PM

Resolved:: 2020/02/04 10:51 AM

Allow for a "resource-events-ttl" to reduce the number of days of events that are stored