Jira (PDB-2487) Allow for a "resource-events-ttl" to reduce the number of days of events that are stored

[Nick Walker (JIRA)]

Nick Walker created an issue

PuppetDB / PDB-2487 Allow for a "resource-events-ttl" to reduce the number of days of events that are stored Issue Type: Improvement Affects Versions: PDB 4.0.0 Assignee: Unassigned Created: 2016/03/01 9:12 AM Labels: tcse Priority: Normal Reporter: Nick Walker Currently we store report-ttl days of events in the resource_events table in the puppetdb database. Some customer 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. Add Comment

This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc)

[Nick Walker (JIRA)]

Nick Walker updated an issue

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

Change By: Nick Walker

Currently we store report-ttl days of events in the resource_events table in the puppetdb database.   Some customer 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.

{code} DELETE FROM resource_events  WHERE timestamp < NOW() - INTERVAL '4 days'; {code}

Add Comment

[Nick Walker (JIRA)]

Nick Walker updated an issue

PuppetDB / PDB-2487 Allow for a "resource-events-ttl" to reduce the number of days of events that are stored Change By: Nick Walker

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

Some  customer  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.   {code} DELETE FROM resource_events  WHERE timestamp < NOW() - INTERVAL '4 days'; {code}

Add Comment

[Wyatt Alt (JIRA)]

Wyatt Alt updated an issue

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

Change By: Wyatt Alt Sprint: Hopper

Add Comment

This message was sent by Atlassian JIRA (v6.4.13#64028-sha1:b7939e9)

[Ryan Senior (JIRA)]

Ryan Senior updated an issue

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

Change By: Ryan Senior Sprint: Hopper

Add Comment

[Nick Walker (JIRA)]

Nick Walker updated an issue

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

Change By: Nick Walker

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.   {code} DELETE FROM resource_events  WHERE timestamp < NOW() - INTERVAL '4 days'; {code}

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

Add Comment

This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe)

[Russell Mull (JIRA)]

Russell Mull updated an issue

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

Change By: Russell Mull Fix Version/s: PDB 5.0.0

Add Comment

[Russell Mull (JIRA)]

Russell Mull updated an issue

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

Change By: Russell Mull Sprint: Hopper

Add Comment

[Rodney Treweek (JIRA)]

Rodney Treweek commented on PDB-2487

Re: Allow for a "resource-events-ttl" to reduce the number of days of events that are stored Adding another Support example: https://puppetlabs.zendesk.com/agent/tickets/25801

Add Comment

[Kenn Hussey (JIRA)]

Kenn Hussey updated an issue

PuppetDB / PDB-2487

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

Change By: Kenn Hussey Team: Systems Engineering

Add Comment

[Karen Van der Veer (JIRA)]

Karen Van der Veer updated an issue

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

Change By: Karen Van der Veer Fix Version/s: PDB 5.0.0 Fix Version/s: PDB 5.1.0

Add Comment

[Julia Ramer (JIRA)]

Julia Ramer updated an issue

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

Change By: Julia Ramer Labels: backup-restore tcse

Add Comment

[Erik Hansen (JIRA)]

Erik Hansen commented on PDB-2487

Re: Allow for a "resource-events-ttl" to reduce the number of days of events that are stored Fannie Mae hitting this in: https://puppetlabs.zendesk.com/agent/tickets/26371

Add Comment

[Kenn Hussey (JIRA)]

Kenn Hussey updated an issue

PuppetDB / PDB-2487

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

Change By: Kenn Hussey Team: Data Platform

Add Comment

[Charlie Sharpsteen (JIRA)]

Charlie Sharpsteen updated an issue

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

Change By: Charlie Sharpsteen CS Priority: Needs Priority

Add Comment

[Russell Mull (JIRA)]

Russell Mull updated an issue

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

Change By: Russell Mull Fix Version/s: PDB 5.2.0

Add Comment

[Russell Mull (JIRA)]

Russell Mull updated an issue

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

Change By: Russell Mull Fix Version/s: PDB 5.1.0

Add Comment

[Owen Rodabaugh (JIRA)]

Owen Rodabaugh updated an issue

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

Change By: Owen Rodabaugh CS Priority: Needs Priority Normal CS Impact: 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. CS Severity: 3 - Serious CS Business Value: 4 - $$$$$ CS Frequency: 2 - 5-25% of Customers

Add Comment

[Nick Walker (JIRA)]

Nick Walker updated an issue

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

Change By: Nick Walker

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.   {code} DELETE FROM resource_events

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

{code} h1.  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.

Add Comment

[Nick Walker (JIRA)]

Nick Walker updated an issue

PuppetDB / PDB-2487 Allow for a "resource-events-ttl" to reduce the number of days of events that are stored Change By: Nick Walker

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: {code} DELETE FROM resource_events  WHERE timestamp < NOW() - INTERVAL '1 days'; {code} Bash code:  {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" {code}

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

Add Comment

[Charlie Sharpsteen (JIRA)]

Charlie Sharpsteen commented on PDB-2487

Re: Allow for a "resource-events-ttl" to reduce the number of days of events that are stored Wyatt suggested that a better resolution to this issue might be to add a filter or TTL specifically for noop or skip events. Supposedly the report and report_events tables are no longer duplicating data, so adding a TTL specifically for events would impact the ability to retrieve a full report-ttl of complete reporting data if necessary. This probably ties into a larger epic around making the whole TTL system more flexible. For example, allowing TTLs to be set per node or per event type.

Add Comment

This message was sent by Atlassian JIRA (v7.0.2#70111-sha1:88534db)

[Russell Mull (JIRA)]

Russell Mull updated an issue

PuppetDB / PDB-2487

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

Change By: Russell Mull Fix Version/s: PDB 5.2.0

Add Comment

This message was sent by Atlassian JIRA (v7.5.1#75006-sha1:7df2574)

[Russell Mull (JIRA)]

Russell Mull commented on PDB-2487

Re: Allow for a "resource-events-ttl" to reduce the number of days of events that are stored removed the fixversion On Mon, Feb 5, 2018 at 7:27 AM, Kenn Hussey (JIRA) <

Add Comment

[Rob Browning (JIRA)]

Rob Browning updated an issue

PuppetDB / PDB-2487

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

Change By: Rob Browning Sprint: Hopper/Triage

Add Comment

This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93)

[Robert Roland (JIRA)]

Robert Roland assigned an issue to Robert Roland

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

Change By: Robert Roland Assignee: Robert Roland

Add Comment

[Austin Blatt (JIRA)]

Austin Blatt updated an issue

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

Change By: Austin Blatt Fix Version/s: PDB 6.0.2

Add Comment

[Austin Blatt (JIRA)]

Austin Blatt updated an issue

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

Change By: Austin Blatt Fix Version/s: PDB 5.2.7

Add Comment

[Robert Roland (JIRA)]

Robert Roland updated an issue

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

Change By: Robert Roland Fix Version/s: PDB 6.0.2 Fix Version/s: PDB 5.2.7

Add Comment

[Kyle Hansel (JIRA)]

Kyle Hansel commented on PDB-2487

Re: Allow for a "resource-events-ttl" to reduce the number of days of events that are stored Federal Reserve IT https://puppetlabs.zendesk.com/agent/tickets/35317

Add Comment

[Jarret Lavallee (JIRA)]

Jarret Lavallee updated an issue

PuppetDB / PDB-2487

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

Change By: Jarret Lavallee CS Priority: Normal Needs Priority

Add Comment

[Rob Browning (JIRA)]

Rob Browning commented on PDB-2487

Re: Allow for a "resource-events-ttl" to reduce the number of days of events that are stored Yes, at least that should still be the plan.

Add Comment

[Rob Browning (JIRA)]

Rob Browning commented on PDB-2487

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

Correction - it's not in Lovejoy, but the current plan for it to still end up in the LTS via the next Y release.

Add Comment

[Thomas Kishel (JIRA)]

Thomas Kishel commented on PDB-2487

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

Does that mean in a .z of Lovejoy? Because this is a high-impact problem for the above PE users.

Add Comment

[Thomas Kishel (JIRA)]

Thomas Kishel commented on PDB-2487

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

The workaround used by Support is a single: su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -c 'TRUNCATE resource_events'" and then a scheduled: su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -c \"DELETE FROM resource_events WHERE timestamp < NOW() - INTERVAL '2 days'\""'; like: cron { 'sudo_resource_events_ttl': ensure => 'present', command => 'su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -c \"DELETE FROM resource_events WHERE timestamp < NOW() - INTERVAL \'2 days\'\""', hour => ['8'], minute => ['0'], target => 'root', user => 'root', }

Add Comment

[Rob Browning (JIRA)]

Rob Browning commented on PDB-2487

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

The plan is for it to make it in to the next "Y" release which I believe is now planned after Lovejoy, and before the LTS, and so the change would be in the LTS that Lovejoy's feeding in to, just not in Lovejoy proper, assuming I understand the current arrangements correctly.

Add Comment

[Dylan Ratcliffe (JIRA)]

Dylan Ratcliffe commented on PDB-2487

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

Rob Browning Is there nothing we can do to get this moving faster? The ticket's been open for three years, there are at least 45 customers affected (the ones linked here) and it's pretty crippling for those that it does affect, plus it seems that the code is done. Though there is a workaround it's not normally a quick thing to diagnose and the implementation is pretty ugly. Plus if we were to be aggressive and set the ttl to say 2 days (since the console only ever uses one day for the event inspector) then we would likely mean that nobody ever needs to actually raise the ticket in the first place.

Add Comment

[Rob Browning (JIRA)]

Rob Browning commented on PDB-2487

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

Oh, and perhaps worth noting that the relevant work has actually now been folded in to this PR https://github.com/puppetlabs/puppetdb/pull/3027

Add Comment

[Maheswaran Shanmugam (JIRA)]

Maheswaran Shanmugam commented on PDB-2487

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

Another incident from DBS today. https://puppetlabs.zendesk.com/agent/tickets/36552  db_name | relation | pg_size_pretty ---------------------------------------------------------------------------------- #{database} | public.resource_events | 130 GB #{database} | public.resource_events_resource_timestamp | 61 GB #{database} | public.resource_events_unique | 52 GB #{database} | public.resource_events_resource_title_idx | 33 GB #{database} | public.resource_events_containing_class_idx | 16 GB #{database} | public.resource_events_timestamp_idx | 7739 MB #{database} | public.resource_events_reports_id_idx | 7104 MB #{database} | public.resource_events_status_idx | 7047 MB #{database} | public.resource_events_property_idx | 7042 MB {{}}

Add Comment

[Jarret Lavallee (JIRA)]

Jarret Lavallee updated an issue

PuppetDB / PDB-2487

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

Change By: Jarret Lavallee CS Priority: Needs Priority Reviewed

Add Comment

[Robert Roland (JIRA)]

Robert Roland updated an issue

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

There is a dev doc here: https://github.com/puppetlabs/puppetdb/blob/master/dev-docs/partitioning.markdown if that helps with updating the release notes or documentation. Change By: Robert Roland Release Notes Summary: 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. Release Notes: Enhancement

Add Comment

[Austin Boyd (JIRA)]

Austin Boyd updated an issue

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

Change By: Austin Boyd Zendesk Ticket IDs: 32574 Zendesk Ticket Count: 1

Add Comment

[Austin Boyd (JIRA)]

Austin Boyd updated an issue

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

Change By: Austin Boyd Zendesk Ticket IDs: 32574 ,35133 Zendesk Ticket Count: 1 2

Add Comment

[Austin Blatt (JIRA)]

Austin Blatt updated an issue

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

Change By: Austin Blatt Fix Version/s: PDB 6.8.0

Add Comment

[Heston Hoffman (JIRA)]

Heston Hoffman updated an issue

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

Change By: Heston Hoffman Labels: backup-restore resolved-issue-added tcse

Add Comment

[Thomas Kishel (JIRA)]

Thomas Kishel commented on PDB-2487

Re: Allow for a "resource-events-ttl" to reduce the number of days of events that are stored Nick Walker resource_events_ttl has been added to PuppetDB 6.8.0 via https://github.com/puppetlabs/puppetdb/pull/3027 So this can be closed, yes? PE 2019.3 ships with PuppetDB 6.8.1. I will submit a PR for PE-28222 to express this setting in PE. Could you close or merge https://github.com/npwalker/pe_databases/pull/32

Add Comment