Skip to content

Optimize stats threshold for approximate counters

Alper Akgun requested to merge 210363-statistics-are-not-updated-in-time-for-approximate-counters-usage into master

What does this MR do?

Optimize statistics threshold for approximate counters usage by raising the vacuum threshold to 6 hours

  • Focused on lib/api/statistics.rb and app/controllers/admin/dashboard_controller.rb as the consumers of approximate counters
  • Sampled some random times NOW() - GREATEST(last_vacuum, last_autovacuum, last_analyze, last_autoanalyze) for them
  • Smaller tables already benefit from the exact counting,
  • With autovacuum freshness of 6 hours - we ensure large tables (in bold text in below table) are countable by approximate counters

Main #210363 (closed)

Details

The query to run on gprd

SELECT relname, NOW() - GREATEST(last_vacuum, last_autovacuum, last_analyze, last_autoanalyze)
FROM pg_class
  LEFT JOIN pg_stat_user_tables USING (relname)
WHERE COALESCE(last_vacuum, last_autovacuum, last_analyze, last_autoanalyze) IS NOT NULL
ORDER by 2 desc

Counted tables

# lib/api/statistics.rb
    COUNTED_ITEMS = [Project, User, Group, ForkNetworkMember, ForkNetwork, Issue, MergeRequest, Note, Snippet, Key, Milestone].freeze
#  app/controllers/admin/dashboard_controller.rb
   COUNTED_ITEMS = [Project, User, Group].freeze

The last vacuum times sampled at random times

fork_network_members fork_networks issues keys merge_requests milestones namespaces notes projects snippets users
677K rows 239K 26M 3.3M 46M 873K 7M 230M 13.5M 267K 5.7M
1 day 15:14:36.18398 01:33:23.480876 00:15:14.048715 00:34:30.499133 00:57:29.871334 03:29:42.807546 08:34:12.731717 01:05:28.37489 00:21:12.763856 1 day 13:24:33.548441 00:13:23.083537
08:18:28.005338 1 day 08:41:22.423043 04:20:16.416995 01:03:10.109857 01:09:25.791988 21:10:05.577099 17:58:41.610463 01:45:43.307895 01:06:10.397281 03:31:12.272541 00:38:02.780475
09:47:01.854185 1 day 10:09:56.27189 00:57:44.702285 00:33:34.76174 00:24:47.646116 22:38:39.425946 19:27:15.45931 03:14:17.156742 00:14:53.793086 04:59:46.121388 00:20:18.590149
11:27:22.118453 1 day 11:50:16.536158 01:22:21.780109 01:24:28.771957 02:05:07.910384 1 day 00:18:59.690214 00:45:37.291667 04:54:37.42101 00:41:42.03946 06:40:06.385656 00:46:39.529035
19:08:44.683072 1 day 19:31:39.100777 02:34:18.13216 00:13:17.482397 02:33:45.531753 06:35:56.854189 00:47:44.542452 04:15:32.856635 00:35:17.54295 14:21:28.950275 00:08:18.377043
21:58:50.167302 00:21:44.788435 01:41:36.788163 00:09:13.315575 00:49:59.581529 01:34:21.082656 02:12:08.13806 02:15:52.178052 00:21:45.776284 17:11:34.434505 00:22:27.059771
23:21:41.77971 01:44:36.400843 00:35:31.348439 00:29:47.547332 00:20:39.737163 02:57:12.695064 03:34:59.750468 03:38:43.79046 00:24:36.340937 18:34:26.046913 00:08:42.857309

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team
Edited by Alper Akgun

Merge request reports