Skip to content

Insert statements on p_ci_finished_build_ch_sync_events results in prolonged locks and freezes Sidekiq intermittently

Ultimate users who don't have ClickHouse installed will see their p_ci_finished_build_ch_sync_events table grow for a whole year. If they have a large number of daily builds, the table could grow to millions of records. Some customers are seeing long table locks whenever a record is inserted.

We use the p_ci_finished_build_ch_sync_events table to keep track of the finished builds that we'll want to sync over to ClickHouse for analytics. We do this even if ClickHouse is not set up, so that customers immediately get historic data if they end up setting up ClickHouse at a future point in time. I assume the customers in question don't have ClickHouse set up, and therefore the finished builds keep accumulating in the table.

The code is written in such a way that historic builds are dropped if they are older than 1 year, even if they haven't been synced to ClickHouse. There are two workarounds:

  1. Lower the PARTITION_CLEANUP_THRESHOLD value to something like 3 months. This will cause the old partitions containing old build sync events to be dropped, leading to a more manageable table size.
  2. Disable the creation of records of p_ci_finished_build_ch_sync_events records altogether, if ClickHouse is not enabled. This means that in the eventuality that customers enable ClickHouse in their installation later on, they'll see empty analytics dashboards initially, until sufficient data is collected.