# How to fix metrics At the request of some instances, we have analyzed the issues related to metrics and looked for possible solutions. ## Problems We have identified two main problems: - Metrics generation crashing, which cause `MetricJob`s to run again and again. - Peaks in generated metrics, sudden changes from day to day when displaying metrics. ### Metrics generation crashing We have identified only one culprit here: "orphans" records, meaning records whose related component or participatory space cannot be found in the database. This is because in a previous decidim release `PartipatorySpaces` could be deleted but they were not deleted properly. So any application that has deleted a participatory space in the past, will probably have unrelated records that will make some metrics calculation crash. ### Peaks in generated metrics If somehow the metrics jobs fail to execute for a period of time, big differences can appear in metrics. So first make sure that you have metrics for every day, if not [generate them](https://github.com/decidim/decidim/blob/master/docs/advanced/metrics.md). If you have metrics generated for almost everyday and still see drastic changes from day to day, take into account that changing the visibility of a component or participatory space (making them private or unpublishing them) will naturally cause big differences in generated metrics. Finally, if you see that the differences in some days are multiples of a previous generated metric, meaning suddenly you have exactly the double or the triple of a calculated metric, it's very likely that you have duplicate generated metrics. We have only seen this problem with instances using Sidekiq, not Delayed Job. We do not know the cause of this, but it seems to be a known issue [Avoiding duplicate jobs in Sidekiq](https://blog.francium.tech/avoiding-duplicate-jobs-in-sidekiq-dcbb1aca1e20). ## Solutions We cannot offer a definitive solution for duplicate metrics, other than to delete old duplicate metrics and generate them again. If this problem persists, however, consider using Delayed Job. For a given metric type (`rake decidim:metrics:list`) that has duplicates: - Option 1: Remove individually each metric record per day. - Option 2: Delete all metric records and recalculate them. [CHANGELOG](https://github.com/decidim/decidim/blob/release/0.18-stable/CHANGELOG.md#participants-metrics) of decidim version 0.18 has an example for "participants". For orphan records, you can do the following: - Back up the database. - Delete orphan records fromt the console (code is below). - Delete "comments" metrics and recalculate them following the [aforementioned example](https://github.com/decidim/decidim/blob/release/0.18-stable/CHANGELOG.md#participants-metrics). ### Some queries that may help ```ruby GROUP_BY_FIELDS= %w( day metric_type decidim_organization_id participatory_space_type participatory_space_id related_object_type related_object_id decidim_category_id).join(', ') def remove_duplicates sql= <<~EOSQL.strip DELETE FROM decidim_metrics WHERE decidim_metrics.id NOT IN (SELECT id FROM ( SELECT DISTINCT ON (#{GROUP_BY_FIELDS}) * FROM decidim_metrics)); EOSQL end # DELETE FROM decidim_metrics WHERE decidim_metrics.id NOT IN \n (SELECT id FROM (\n SELECT DISTINCT ON (day, metric_type, decidim_organization_id, participatory_space_type, participatory_space_id, related_object_type, related_object_id, decidim_category_id) * FROM decidim_metrics)); def count_duplicates sql= <<~EOSQL.strip SELECT count(1), #{GROUP_BY_FIELDS} FROM decidim_metrics GROUP BY #{GROUP_BY_FIELDS} HAVING COUNT(1) > 1; EOSQL end ``` ### Delete orphan records "proposals", "meetings", "accountability", "debates", "pages", "budgets", "surveys" #### Proposals Delete proposals whose component does not have a participatory space and delete components of a proposal type that do not have a participatory space ```ruby Decidim::Component.where(manifest_name: "proposals").find_each(batch_size: 100) { |c| if c.participatory_space.blank? Decidim::Proposals::Proposal.where(component: c).destroy_all c.destroy end } ``` Delete proposals that do not have a component ```ruby Decidim::Proposals::Proposal.find_each(batch_size: 100) { |proposal| proposal.delete if proposal.component.blank? } ```` #### Meetings Delete meetings whose component has no participatory space and delete components of meeting type that do not have a participatory space ```ruby Decidim::Component.where(manifest_name: "meetings").find_each(batch_size: 100) { |c| if c.participatory_space.blank? Decidim::Meetings::Meeting.where(component: c).destroy_all c.destroy end } ``` Delete meetings that do not have a component ```ruby Decidim::Meetings::Meeting.find_each(batch_size: 100) { |meeting| meeting.delete if meeting.component.blank? } ```` #### Debates Delete debates that its component has no participatory space and the debate components that do not have a participatory space ```ruby Decidim::Component.where(manifest_name: "debates").find_each(batch_size: 100) { |c| if c.participatory_space.blank? Decidim::Debates::Debate.where(component: c).destroy_all c.destroy end } ``` Destroy debates that do not have a component ```ruby Decidim::Debates::Debate.find_each(batch_size: 100) { |debate| debate.delete if debate.component.blank? } ``` #### Posts Destroy posts whose component has no participatory space and blog components that do not have a participatory space ```ruby Decidim::Component.where(manifest_name: "blogs").find_each(batch_size: 100) { |c| if c.participatory_space.blank? Decidim::Blogs::Post.where(component: c).destroy_all c.destroy end } ``` Destroy posts that do not have a component ```ruby Decidim::Blogs::Post.find_each(batch_size: 100) { |post| post.delete if post.component.blank? } ``` #### Accountability Destroy results whose component has no participatory space and components of accountability type that do not have a participatory space ```ruby Decidim::Component.where(manifest_name: "accountability").find_each(batch_size: 100) { |c| if c.participatory_space.blank? Decidim::Accountability::Result.where(component: c).destroy_all c.destroy end } ``` Destroy results that do not have a component ```ruby Decidim::Accountability::Result.find_each(batch_size: 100) { |result| result.delete if result.component.blank? } ``` #### Pages Destroy page components that do not have a participatory space ```ruby Decidim::Component.where(manifest_name: "pages").find_each(batch_size: 100) { |c| if c.participatory_space.blank? c.destroy end } ``` #### Budgets Destroy projects whose component has no participatory space and budget components that do not have a participatory space ```ruby Decidim::Component.where(manifest_name: "budgets").find_each(batch_size: 100) { |c| if c.participatory_space.blank? Decidim::Budgets::Project.where(component: c).destroy_all c.destroy end } ``` Destroy results that do not have a component ```ruby Decidim::Budgets::Project.find_each(batch_size: 100) { |project| project.delete if project.component.blank? } ``` #### Surveys ```ruby Decidim::Component.where(manifest_name: "surveys").find_each(batch_size: 100) { |c| if c.participatory_space.blank? Decidim::Surveys::Survey.where(component: c).destroy_all c.destroy end } ``` Destroy surveys that do not have a component ```ruby Decidim::Surveys::Survey.find_each(batch_size: 100) { |survey| survey.delete if survey.component.blank? } ``` #### Comments Destroy comments whose commentable root is a proposal that does not have a participatory space. ```ruby proposal_ids = Decidim::Comments::Comment.where(decidim_root_commentable_type: "Decidim::Proposals::Proposal").pluck(:decidim_root_commentable_id) proposal_ids_without_space = Decidim::Proposals::Proposal.where(id: proposal_ids).find_all{|p| p.participatory_space.blank? }.pluck(:id) Decidim::Comments::Comment.where(decidim_root_commentable_type: "Decidim::Proposals::Proposal", decidim_root_commentable_id: proposal_ids_without_space).destroy_all ```