Note: This bug is displayed in read-only format because
the product is no longer active in Red Hat Bugzilla.
Red Hat Satellite engineering is moving the tracking of its product development work on Satellite to Red Hat Jira (issues.redhat.com). If you're a Red Hat customer, please continue to file support cases via the Red Hat customer portal. If you're not, please head to the "Satellite project" in Red Hat Jira and file new tickets here. Individual Bugzilla bugs will be migrated starting at the end of May. If you cannot log in to RH Jira, please consult article #7032570. That failing, please send an e-mail to the RH Jira admins at rh-issues@redhat.com to troubleshoot your issue as a user management inquiry. The email creates a ServiceNow ticket with Red Hat. Individual Bugzilla bugs that are migrated will be moved to status "CLOSED", resolution "MIGRATED", and set with "MigratedToJIRA" in "Keywords". The link to the successor Jira issue will be found under "Links", have a little "two-footprint" icon next to it, and direct you to the "Satellite project" in Red Hat Jira (issue links are of type "https://issues.redhat.com/browse/SAT-XXXX", where "X" is a digit). This same link will be available in a blue banner at the top of the page informing you that that bug has been migrated.
Description of problem: There are multiple duplicate indexes present in foreman and candlepin database. Having identical indexes means the DB is spending more time updating two indexes when only one needs to be updated. Duplicate indexes should be cleaned up and there should be a workaround to cleanup indexes in the current version.
We should remove the unwanted indexes during the upgrades
Customer DB these are the duplicate indexes are present in the DB
Foreman
--------
size | idx1 | idx2
--------+----------------------------------------------+------------------------------------
246 MB | dynflow_steps_execution_plan_uuid_id_index | dynflow_steps_pkey
158 MB | dynflow_actions_execution_plan_uuid_id_index | dynflow_actions_pkey
141 MB | dynflow_execution_plans_pkey | dynflow_execution_plans_uuid_index
4696 kB | index_environment_classes_on_environment_id_puppetclass_id | index_environment_classes_on_environment_id_and_puppetclass
4696 kB | index_lookup_values_lookup_key_id | index_lookup_values_on_lookup_key_id
Candlepin
---------
size | idx1 | idx2
--------+---------------------------------------+--------------------------
262 MB | cp_content_override_key_id_for_cs_idx | cp_content_override_idx1
576 kB | cp_pool_source_stack_pool_ukey | idx_sourcestack_pool_fk
32 kB | cp_dist_version_pkey | cp_dist_version_id_idx
In local environment Satellite 6.5(upgraded from 6.4) having below duplicate indexes
Foreman
----
#su - postgres -c "psql -d foreman -c 'SELECT indrelid::regclass AS TableName,array_agg(indexrelid::regclass) AS Indexes FROM pg_index GROUP BY indrelid,indkey HAVING COUNT(*) > 1;;'"
tablename | indexes
-------------------------+---------------------------------------------------------------------
dynflow_execution_plans | {dynflow_execution_plans_pkey,dynflow_execution_plans_uuid_index}
dynflow_actions | {dynflow_actions_pkey,dynflow_actions_execution_plan_uuid_id_index}
dynflow_steps | {dynflow_steps_pkey,dynflow_steps_execution_plan_uuid_id_index}
katello_pools | {index_pools_on_cp_id,index_katello_pools_on_cp_id}
Candlepin
---
#su - postgres -c "psql -d candlepin -c 'SELECT indrelid::regclass AS TableName,array_agg(indexrelid::regclass) AS Indexes FROM pg_index GROUP BY indrelid,indkey HAVING COUNT(*) > 1;;'"
tablename | indexes
----------------------+----------------------------------------------------------
cp_dist_version | {cp_dist_version_id_idx,cp_dist_version_pkey}
cp_pool_source_stack | {cp_pool_source_stack_pool_ukey,idx_sourcestack_pool_fk}
(2 rows)
Version-Release number of selected component (if applicable): 6.5
How reproducible: Always
Steps to Reproduce:
1. Try to find duplicate indexes using SQL query as per summary
Actual results: There are multiple duplicates indexes
Expected results: Duplicate indexes should not be present
Comment 5Lukáš Hellebrandt
2020-07-17 11:16:04 UTC
Dynflow indexes are solved by bug 1807946. Katello index is reported in 1858237.
BZ 1807946, BZ 1858237 and BZ 1745516 have been opened fixing the specific indexes found to be duplicates. Closing this BZ as WONTFIX, though the indexes will be fixed by the other BZs (since it isn't possible to close as duplicate of multiple BZs).
Description of problem: There are multiple duplicate indexes present in foreman and candlepin database. Having identical indexes means the DB is spending more time updating two indexes when only one needs to be updated. Duplicate indexes should be cleaned up and there should be a workaround to cleanup indexes in the current version. We should remove the unwanted indexes during the upgrades Customer DB these are the duplicate indexes are present in the DB Foreman -------- size | idx1 | idx2 --------+----------------------------------------------+------------------------------------ 246 MB | dynflow_steps_execution_plan_uuid_id_index | dynflow_steps_pkey 158 MB | dynflow_actions_execution_plan_uuid_id_index | dynflow_actions_pkey 141 MB | dynflow_execution_plans_pkey | dynflow_execution_plans_uuid_index 4696 kB | index_environment_classes_on_environment_id_puppetclass_id | index_environment_classes_on_environment_id_and_puppetclass 4696 kB | index_lookup_values_lookup_key_id | index_lookup_values_on_lookup_key_id Candlepin --------- size | idx1 | idx2 --------+---------------------------------------+-------------------------- 262 MB | cp_content_override_key_id_for_cs_idx | cp_content_override_idx1 576 kB | cp_pool_source_stack_pool_ukey | idx_sourcestack_pool_fk 32 kB | cp_dist_version_pkey | cp_dist_version_id_idx In local environment Satellite 6.5(upgraded from 6.4) having below duplicate indexes Foreman ---- #su - postgres -c "psql -d foreman -c 'SELECT indrelid::regclass AS TableName,array_agg(indexrelid::regclass) AS Indexes FROM pg_index GROUP BY indrelid,indkey HAVING COUNT(*) > 1;;'" tablename | indexes -------------------------+--------------------------------------------------------------------- dynflow_execution_plans | {dynflow_execution_plans_pkey,dynflow_execution_plans_uuid_index} dynflow_actions | {dynflow_actions_pkey,dynflow_actions_execution_plan_uuid_id_index} dynflow_steps | {dynflow_steps_pkey,dynflow_steps_execution_plan_uuid_id_index} katello_pools | {index_pools_on_cp_id,index_katello_pools_on_cp_id} Candlepin --- #su - postgres -c "psql -d candlepin -c 'SELECT indrelid::regclass AS TableName,array_agg(indexrelid::regclass) AS Indexes FROM pg_index GROUP BY indrelid,indkey HAVING COUNT(*) > 1;;'" tablename | indexes ----------------------+---------------------------------------------------------- cp_dist_version | {cp_dist_version_id_idx,cp_dist_version_pkey} cp_pool_source_stack | {cp_pool_source_stack_pool_ukey,idx_sourcestack_pool_fk} (2 rows) Version-Release number of selected component (if applicable): 6.5 How reproducible: Always Steps to Reproduce: 1. Try to find duplicate indexes using SQL query as per summary Actual results: There are multiple duplicates indexes Expected results: Duplicate indexes should not be present