Feature flag performance concerns
While investigating some other performance problems, I stumbled across this query:
SELECT "features"."key" FROM "features"
This query returns, on a per-second, basis, far-and-away, the most rows-per-query on GitLab.com, by 58x!
topk(10, sum(rate(pg_stat_statements_rows{environment="gprd", datname="gitlabhq_production"}[1h])) by (queryid))
Here's how queryid="2993739592" looks in pg_stat_statements
:
userid | 16384
dbid | 16385
queryid | 2993739592
query | SELECT "features"."key" FROM "features"
calls | 1666383779
total_time | 159782955.263373
min_time | 0.044
max_time | 587.82
mean_time | 0.0958860482518107
stddev_time | 0.0864549211285121
rows | 365005599776
shared_blks_hit | 12401845738
shared_blks_read | 57
shared_blks_dirtied | 6
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_dirtied | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
blk_read_time | 6.551
blk_write_time | 0
There are 240 rows in this table, but the problem appears to be the rate at which it is called.
😱
At it's peak time of day, this SQL query is being called 2300 times per second
Considering this table changes very rarely, and, with caching we could probably afford to only query the underlying table once a minute (or ~100000 times less frequently!) we should probably investigate ways to improve the feature flag middleware in GitLab.