Jira (PDB-5589) Investigate pdb upgrade times with migration 81/82
Title: Message Title Joshua Partlow commented on PDB-5589 Re: Investigate pdb upgrade times with migration 81/82 Same issue for resource_events, except it's just the old primary key 'resource_events_hash_*' that is left over in the migrated partitions. Effectively replaced by the new pkey on event_hash, timestamp. Add Comment This message was sent by Atlassian Jira (v8.20.11#820011-sha1:0629dd8) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.480704.1675468795000.1412.1680047640237%40Atlassian.JIRA.
Jira (PDB-5589) Investigate pdb upgrade times with migration 81/82
Title: Message Title
Joshua Partlow commented on PDB-5589
Re: Investigate pdb upgrade times with migration 81/82
Regarding the index differences between 7.12.1 and 7.x with migrations 81 and 82, it took a little bit for me to get up to speed here, but I think I'm now seeing the same thing you are, Austin Blatt. On 7.12.1 we have the following report partition indexes:
"reports_cached_catalog_status_on_fail_20230320z" btree (cached_catalog_status) WHERE cached_catalog_status = 'on_failure'::text
"reports_catalog_uuid_idx_20230320z" btree (catalog_uuid)
"idx_reports_certname_end_time_20230320z" btree (certname, end_time)
"idx_reports_producer_timestamp_by_hour_certname_20230320z" btree (date_trunc('hour'::text, timezone('UTC'::text, producer_timestamp)), producer_timestamp, certname)
"reports_end_time_idx_20230320z" btree (end_time)
"reports_environment_id_idx_20230320z" btree (environment_id)
"reports_job_id_idx_20230320z" btree (job_id) WHERE job_id IS NOT NULL
"reports_noop_idx_20230320z" btree (noop) WHERE noop = true
"idx_reports_noop_pending_20230320z" btree (noop_pending) WHERE noop_pending = true
"idx_reports_prod_20230320z" btree (producer_id)
Jira (PDB-5589) Investigate pdb upgrade times with migration 81/82
Title: Message Title Austin Blatt commented on PDB-5589 Re: Investigate pdb upgrade times with migration 81/82 Please take these results very loosely, they were produced on a laptop. Migration results running migrations 81 and 82 0 reports - 81ms 6,720 reports - 135ms 35,500 reports - 185ms 67,200 reports - 188ms There is some impact in migration time when more data is added, but it may even tail off as you get more data in a partition. Even if we ignore the final run with 67k nodes, extrapolating from the first three data points linearly puts 67.2 million reports (100k nodes with a run interval of 30 minutes and a history of 14 days) at 172 seconds — or just under 3 minutes. Add Comment This message was sent by Atlassian Jira (v8.20.11#820011-sha1:0629dd8) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.480704.1675468795000.2715.1679516400029%40Atlassian.JIRA.
Jira (PDB-5589) Investigate pdb upgrade times with migration 81/82
Title: Message Title Austin Blatt commented on PDB-5589 Re: Investigate pdb upgrade times with migration 81/82 Noticed a 2 index difference between the new and old version. Partitions have a primary key, which is expected, but there also appears to be a duplicate index being created (at least by the migration). This was run against an brand new postgres 15.2, where the bug was supposed to have been fixed. "reports_20230318z_encode_producer_timestamp_idx" UNIQUE, btree (encode(hash, 'hex'::text), producer_timestamp) "reports_hash_expr_idx_20230318z" UNIQUE, btree (encode(hash, 'hex'::text)) the id index is also not removed in the migration, but does not continue into a newly created partition. "idx_reports_id_20230318z" UNIQUE, btree (id) These issues go away for new partitions, so I don't know how big of an issue they are. Add Comment
Jira (PDB-5589) Investigate pdb upgrade times with migration 81/82
Title: Message Title Austin Blatt assigned an issue to Austin Blatt PuppetDB / PDB-5589 Investigate pdb upgrade times with migration 81/82 Change By: Austin Blatt Assignee: Austin Blatt Add Comment This message was sent by Atlassian Jira (v8.20.11#820011-sha1:0629dd8) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.480704.1675468795000.1109.1678913880205%40Atlassian.JIRA.
Jira (PDB-5589) Investigate pdb upgrade times with migration 81/82
Title: Message Title Cas Donoghue updated an issue PuppetDB / PDB-5589 Investigate pdb upgrade times with migration 81/82 Change By: Cas Donoghue Sprint: Skeletor 03/29/2023 Add Comment This message was sent by Atlassian Jira (v8.20.11#820011-sha1:0629dd8) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.480704.1675468795000.970.1678904700241%40Atlassian.JIRA.
Jira (PDB-5589) Investigate pdb upgrade times with migration 81/82
Title: Message Title Austin Blatt commented on PDB-5589 Re: Investigate pdb upgrade times with migration 81/82 I'm not too worried about time to upgrade PE (famous last words...). I think all our operations are "move" operations, the alter table commands all look to be commands that are not table rewriting. But checking indexes and query performance after the upgrade would be good. I do not know if we still have the data around for Locust, but running all those queries would be a good start. Add Comment This message was sent by Atlassian Jira (v8.20.11#820011-sha1:0629dd8) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.480704.1675468795000.809.1678894560073%40Atlassian.JIRA.
Jira (PDB-5589) Investigate pdb upgrade times with migration 81/82
Title: Message Title Joshua Partlow created an issue PuppetDB / PDB-5589 Investigate pdb upgrade times with migration 81/82 Issue Type: Task Assignee: Unassigned Components: PuppetDB, PuppetDB Enterprise Created: 2023/02/03 3:59 PM Priority: Normal Reporter: Joshua Partlow Double check how much time the index regeneration from pdb migrations 81/82 takes (these migrations rejigger the reports/resource_events partitions to use declarative partitioning). Time to upgrade In versions of PG 11.17 the reports_noop_idx indexes generated on the child partitions is named differently than in 11.18 (for example, reports_20230130z_noop_idx instead of reports_noop_idx_20230130z, see: https://github.com/puppetlabs/puppetdb/pull/3748/commits/133c3771e84d8b389bb4eca74907f7b03a96e389). It would be good to validate what the indexes look like after upgrading on =11.18 (possibly easiest to test this with PE) Add Comment
