Jira (PDB-3526) Add online index rebuild capability to PuppetDB
Title: Message Title Claudia Petty updated an issue PuppetDB / PDB-3526 Add online index rebuild capability to PuppetDB Change By: Claudia Petty Labels: new-feature Add Comment This message was sent by Atlassian Jira (v8.20.21#820021-sha1:38274c8) -- 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.192668.1495584674000.2507.1687359483078%40Atlassian.JIRA.
Jira (PDB-3526) Add online index rebuild capability to PuppetDB
Title: Message Title Nick Walker commented on PDB-3526 Re: Add online index rebuild capability to PuppetDB While not native to PuppetDB, we will have pg_repack in Puppet Enterprise with 2018.1.7 and 2019.02 which provides the ability to rebuild indexes online. Closing this as won't fix since we can use a 3rd party tool to solve the issue. Add Comment This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-3526) Add online index rebuild capability to PuppetDB
Title: Message Title Nick Walker updated an issue PuppetDB / PDB-3526 Add online index rebuild capability to PuppetDB Change By: Nick Walker Labels: cstop10 Add Comment This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-3526) Add online index rebuild capability to PuppetDB
Title: Message Title Adam Bottchen updated an issue PuppetDB / PDB-3526 Add online index rebuild capability to PuppetDB Change By: Adam Bottchen Labels: cstop10 Add Comment This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-3526) Add online index rebuild capability to PuppetDB
Title: Message Title Owen Rodabaugh updated an issue PuppetDB / PDB-3526 Add online index rebuild capability to PuppetDB Change By: Owen Rodabaugh CS Priority: Major CS Impact: Maintenance of Postgresql is one of the most common solutions to performance issue at large scale customers. Having this capability built into the product would increase customer satisfaction and reduce the support burden. CS Severity: 4 - Major CS Business Value: 5 - $$ CS Frequency: 2 - 5-25% of Customers Add Comment This message was sent by Atlassian JIRA (v7.0.2#70111-sha1:88534db)
Jira (PDB-3526) Add online index rebuild capability to PuppetDB
Title: Message Title
Nick Walker updated an issue
PuppetDB / PDB-3526
Add online index rebuild capability to PuppetDB
Change By:
Nick Walker
h1. BackgroundRebuilding indexes is a common maintenance recommendation for PostgreSQL databases. However, rebuilding an index is a blocking activity so customers that require little to no downtime don't like to rebuild indexes and are likely giving up performance because of it. This is one of the main reasons that pg_repack exists in addition to it's ability to reduce table bloat. h1. PuppetDB suggestionSince rebuilding indexes is a good idea but we don't want downtime the internet generally recommends creating a new index with the CONCURRENTLY flag which is non-blocking then dropping the existing index and renaming the new one to match the existing name. For example: the alter constraint page in the postgresql docs recommends exactly what I do in the script. Scroll to the very bottom of this page https://www.postgresql.org/docs/9.4/static/sql-altertable.htmlAnother example: https://www.keithf4.com/cleaning-up-postgresql-bloat/We should have a facility for accomplishing the above task and I have two queries that produce that for the indexes in any postgresql database. Assuming you place the attachments in /tmp you can run them and execute their output as follows. {code}su - pe- postgres -s /bin/bash -c " /opt/puppetlabs/server/bin/ psql -d pe-puppetdb -f /tmp/online_rebuild_index_script -qt" > /tmp/rebuild_indexes.sqlsu - pe- postgres -s /bin/bash -c " /opt/puppetlabs/server/bin/ psql -d pe-puppetdb -f /tmp/rebuild_indexes.sql -q"su - pe- postgres -s /bin/bash -c " /opt/puppetlabs/server/bin/ psql -d pe-puppetdb -f /tmp/online_rebuild_unique_constraints.sql -qt" > /tmp/rebuild_unique_constraints.sqlsu - pe- postgres -s /bin/bash -c " /opt/puppetlabs/server/bin/ psql -d pe-puppetdb -f /tmp/rebuild_unique_constraints.sql -q"{code}h1. Caveats The first script rebuilds all indexes that don't enforce constraints. The second script rebuilds all unique constraints that don't have further dependencies. In my counting that currently leaves the certnames_transform_certname_key unique index out because numerous FKs depend on it to make sure that certname is unique. Otherwise all indexes are rebuilt by the combination of the two scripts.
Add Comment
Jira (PDB-3526) Add online index rebuild capability to PuppetDB
Title: Message Title
Nick Walker updated an issue
PuppetDB / PDB-3526
Add online index rebuild capability to PuppetDB
Change By:
Nick Walker
h1. BackgroundRebuilding indexes is a common maintenance recommendation for PostgreSQL databases. However, rebuilding an index is a blocking activity so customers that require little to no downtime don't like to rebuild indexes and are likely giving up performance because of it. This is one of the main reasons that pg_repack exists in addition to it's ability to reduce table bloat. h1. PuppetDB suggestionSince rebuilding indexes is a good idea but we don't want downtime the internet generally recommends creating a new index with the CONCURRENTLY flag which is non-blocking then dropping the existing index and renaming the new one to match the existing name. For example: the alter constraint page in the postgresql docs recommends exactly what I do in the script. Scroll to the very bottom of this page https://www.postgresql.org/docs/9.4/static/sql-altertable.html Another example: https://www.keithf4.com/cleaning-up-postgresql-bloat/ We should have a facility for accomplishing the above task and I have two queries that produce that for the indexes in any postgresql database. Assuming you place the attachments in /tmp you can run them and execute their output as follows. {code}su - postgres -s /bin/bash -c "psql -d pe-puppetdb -f /tmp/online_rebuild_index_script -qt" > /tmp/rebuild_indexes.sqlsu - postgres -s /bin/bash -c "psql -d pe-puppetdb -f /tmp/rebuild_indexes.sql -q"su - postgres -s /bin/bash -c "psql -d pe-puppetdb -f /tmp/online_rebuild_unique_constraints.sql -qt" > /tmp/rebuild_unique_constraints.sqlsu - postgres -s /bin/bash -c "psql -d pe-puppetdb -f /tmp/rebuild_unique_constraints.sql -q"{code}h1. Caveats The first script rebuilds all indexes that don't enforce constraints. The second script rebuilds all unique constraints that don't have further dependencies. In my counting that currently leaves the certnames_transform_certname_key unique index out because numerous FKs depend on it to make sure that certname is unique. Otherwise all indexes are rebuilt by the combination of the two scripts.
Add Comment
Jira (PDB-3526) Add online index rebuild capability to PuppetDB
Title: Message Title
Nick Walker updated an issue
PuppetDB / PDB-3526
Add online index rebuild capability to PuppetDB
Change By:
Nick Walker
h1. BackgroundRebuilding indexes is a common maintenance recommendation for PostgreSQL databases. However, rebuilding an index is a blocking activity so customers that require little to no downtime don't like to rebuild indexes and are likely giving up performance because of it. This is one of the main reasons that pg_repack exists in addition to it's ability to reduce table bloat. h1. PuppetDB suggestionSince rebuilding indexes is a good idea but we don't want downtime the internet generally recommends creating a new index with the CONCURRENTLY flag which is non-blocking then dropping the existing index and renaming the new one to match the existing name. For example: the alter constraint page in the postgresql docs recommends exactly what I do in the script. Scroll to the very bottom of this page https://www.postgresql.org/docs/9.4/static/sql-altertable.html We should have a facility for accomplishing the above task and I have two queries that produce that for the indexes in any postgresql database. Assuming you place the attachments in /tmp you can run them and execute their output as follows. {code}su - postgres -s /bin/bash -c "psql -d pe-puppetdb -f /tmp/online_rebuild_index_script -qt" > /tmp/rebuild_indexes.sqlsu - postgres -s /bin/bash -c "psql -d pe-puppetdb -f /tmp/rebuild_indexes.sql -q"su - postgres -s /bin/bash -c "psql -d pe-puppetdb -f /tmp/online_rebuild_unique_constraints.sql -qt" > /tmp/rebuild_unique_constraints.sqlsu - postgres -s /bin/bash -c "psql -d pe-puppetdb -f /tmp/rebuild_unique_constraints.sql -q"{code}h1. Caveats The first script rebuilds all indexes that don't enforce constraints. The second script rebuilds all unique constraints that don't have further dependencies. In my counting that currently leaves the certnames_transform_certname_key unique index out because numerous FKs depend on it to make sure that certname is unique. Otherwise all indexes are rebuilt by the combination of the two scripts.
Add Comment
Jira (PDB-3526) Add online index rebuild capability to PuppetDB
Title: Message Title Nick Walker created an issue PuppetDB / PDB-3526 Add online index rebuild capability to PuppetDB Issue Type: New Feature Assignee: Unassigned Attachments: online_index_rebuild_script.sql, online_rebuild_unique_constraints.sql Created: 2017/05/23 5:11 PM Priority: Normal Reporter: Nick Walker Background Rebuilding indexes is a common maintenance recommendation for PostgreSQL databases. However, rebuilding an index is a blocking activity so customers that require little to no downtime don't like to rebuild indexes and are likely giving up performance because of it. This is one of the main reasons that pg_repack exists in addition to it's ability to reduce table bloat. PuppetDB suggestion Since rebuilding indexes is a good idea but we don't want downtime the internet generally recommends creating a new index with the CONCURRENTLY flag which is non-blocking then dropping the existing index and renaming the new one to match the existing name. We should have a facility for accomplishing the above task and I have two queries that produce that for the indexes in any postgresql database. Assuming you place the attachments in /tmp you can run them and execute their output as follows.
